MySQL优化
一、优化方向
- 存储引擎的选择：INnoDB MyISAM
- 设计数据库的设计，尽量的遵循三范式
- 适当的建立索引
- 查询数据的时候，注意 ... 这些
- 数据量大的时候，主从分离、分库分表、垂直/水平分割
- 尽量采用 贵的，SSD硬盘。不选择机械硬盘
二、MySQL优化常问的问题有哪些
- 什么是MySQL的慢查询？
- MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是记录运行10秒以上的语句。
资料来源：https://blog.csdn.net/qq_40884473/article/details/89455740
- MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是记录运行10秒以上的语句。
- 如何去分析 慢查询的日志？
- 查询slow log的状态，如示例代码所示，则slow log已经开启
-
mysql> show variables like '%slow%'; +---------------------+------------------------------------------+ | Variable_name | Value | +---------------------+------------------------------------------+ | log_slow_queries | ON | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /mysqllog/slow_log/slow_queries_3306.log | +---------------------+------------------------------------------+ rows in set (0.00 sec)
-
-
- slow log的日志相关参数详解
-
slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。 log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。 log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。 log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。
MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，
又需要能够获得更高的系统性能，那么建议优先记录到文件。如何在线安全的清空慢查询日志
- 停止slow log
mysql> set global slow_query_log=0; Query OK, 0 rows affected (0.27 sec)
mysql> show variables like '%slow%'; +---------------------+------------------------------------------+ | Variable_name | Value | +---------------------+------------------------------------------+ | log_slow_queries | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /mysqllog/slow_log/slow_queries_3306.log | +---------------------+------------------------------------------+ 4 rows in set (0.00 sec)
#检查慢查询日志的状态- 为慢查询日志重新设置path路径
mysql> set global slow_query_log_file='/mysqllog/slow_log/slow_queries_3306_new.log'; Query OK, 0 rows affected (0.03 sec)
- 开启慢查询日志，并设置long_query_time。
mysql> set global slow_query_log=1; Query OK, 0 rows affected (0.01 sec) mysql>set global long_query_time=1;
#检查状态是否成功开启
mysql> show variables like '%slow%'; +---------------------+----------------------------------------------+ | Variable_name | Value | +---------------------+----------------------------------------------+ | log_slow_queries | ON | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /mysqllog/slow_log/slow_queries_3306_new.log | +---------------------+----------------------------------------------+ 4 rows in set (0.00 sec)- 检查slow sql 在新的日志文件中
mysql> select sleep(10) as a, 1 as b; +---+---+ | a | b | +---+---+ | 0 | 1 | +---+---+ 1 row in set (10.00 sec) mysql> [mysql@xxx-xxx ~]$ more /mysqllog/slow_log/slow_queries_3306_new.log ...... Time Id Command Argument # Time: 140213 6:44:24 # User@Host: root[root] @ localhost [] # Query_time: 10.000365 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1392273864; select sleep(10) as a, 1 as b;
- 备份之前的慢查询日志
mv /mysqllog/slow_log/slow_queries_3306.log /mysqlbackup/slow_log/slow_queries_3306.log.bak.20140213
-
- 遇到了慢查询如何解决？（最好有故事）
-
0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE 1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高 2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询） 3.order by limit 形式的sql语句让排序的表优先查 4.了解业务方使用场景 5.加索引时参照建索引的几大原则 6.观察结果，不符合预期继续从0分析
-
- 索引的建立是越多越好？为什么不是越多越好？
- 当然不是，
关于建立索引的几个准则：
1、合理的建立索引能够加速数据读取效率，不合理的建立索引反而会拖慢数据库的响应速度。
2、索引越多，更新数据的速度越慢。
不要在选择的栏位上放置索引，这是无意义的。应该在条件选择的语句上合理的放置索引，比如where，order by。
例子：
SELECT id,title,content,cat_id FROM article WHERE cat_id = 1;
上面这个语句，你在id/title/content上放置索引是毫无意义的，对这个语句没有任何优化作用。但是如果你在外键cat_id上放置一个索引，那作用就相当大了。
- 当然不是，