MySQL性能优化
优化查询
启用慢查询日志
执行如下语句看是否启用了慢查询日志，ON为启用，OFF为未启用，默认为OFF。
SHOW VARIABLES LIKE '%slow_query_log%';
如果没有开启，使用如下两种方式来开启慢查询。
- 修改配置文件
修改配置文件 vim /etc/my.cnf，在 [mysqld] 段落在加入如下配置：
[mysqld] slow_query_log=1 # 是否启用慢查询日志，ON 为启用，OFF 为未启用，默认为 OFF。开启会影响性能，MySQL 重启会失效。 slow_query_log_file=/var/lib/mysql/data/slow.log # 指定慢查询日志文件的路径和名字，缺省文件名 host_name-slow.log。 long_query_time=3 # 执行时间超过该值才记录到慢查询日志，单位为秒，默认为 10。 log_output=FILE,TABLE # 日志输出位置，默认为 FILE，即保存为文件，若设置为 TABLE，则将日志记录到 mysql.show_log 表中，支持设置多种格式。需要重启 MySQL 才可以生效，命令为 service mysqld restart。
- 设置全局变量
如下打开慢查询日志，设置超时时间为 3 秒，并且将日志记录到文件以及 mysql.show_log 表中。
SET GLOBAL slow_query_log = 1; SET GLOBAL slow_query_log_file='/var/lib/mysql/data/slow.log'; SET GLOBAL long_query_time=3; SET GLOBAL log_output='FILE,TABLE';想要永久生效得用上面那个配置文件里配置，否则数据库重启后，这些配置失效。
- 获取慢SQL信息
查看慢查询日志记录数
SHOW GLOBAL STATUS LIKE '%Slow_queries%';模拟语句
select sleep(5);查看日志
cat /var/lib/mysql/data/slow.log
分析慢查询日志
- explain执行计划分析慢SQL
EXPLAIN 查询语句; # EXPLAIN 语句的语法结构explain 执行后输出的结果集包含 12 列，分别是 id、select_type、table、partitions、type、possible_keys、key、 key_len、ref、rows、filtered 和 Extra，下面对这些字段进行解释。
| 字段 | 含义 |
|---|---|
| id | Query Optimizer 所选定的执行计划中查询的序列号 |
| select_type | 显示本行是简单或复杂 select，如果查询有任何复杂的子查询，则最外层标记为PRIMARY、DERIVED.、UNION、UNION RESUIT 等 |
| table | 显示这一步所访问的数据库中的表的名称 |
| partitions | 查询时匹配到的分区信息，对于非分区表值为 NULL，当查询的是分区表时，partitions 显示分区表命中的分区情况 |
| type | 数据访问、读取操作类型（ALL、index、range、ref、eq_ref、const、system）等 |
| possible_keys | 该查询可以利用的索引，如果没有任何索引可以使用，就会显示成 null，这一项内容对于优化时候索引的调整非常重要 |
| key | MySQL Query Optimizer 从 possible_keys 中所选择使用的索引 |
| key_len | 被选中使用索引的索引键长度 |
| ref | 列出是通过常量（const），还是某个表的某个字段（如果是 join）来过滤（通过 key）的。 |
| rows | MySQL Query Optimizer 通过系统收集到的统计信息估算出来的结果集记录条数 |
| filtered | 表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例 |
| Extra | 查询中每一步实现的额外细节信息，如 Using filesort、index 等 |
- mysqldumpslow
MySQL 内置了 mysqldumpslow 这个工具来帮我们分析慢查询日志。
#得到返回记录集最多的10个SQL mysqldumpslow -s r -t 10 /var/lib/mysql/data/slow.log #得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 /var/lib/mysql/data/slow.log #得到按照时间排序的前10条里面含有左连接的查询语句 mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/data/slow.log #结合| more使用，防止爆屏情况 mysqldumpslow -s r -t 10 /var/lib/mysql/data/slow.log | more s：表示按何种方式排序 c：访问次数 l：锁定时间 r：返回记录 t：查询时间 al：平均锁定时间 ar：平均返回记录数 at：平均查询时间 t：返回前面多少条的数据 g：后边搭配一个正则匹配模式，大小写不敏感 -
Show Profile 分析慢SQL Show Profile 也可以分析慢 SQL，比 explain 获取的信息更详细，比如能分析当前会话中语句执行的资源消耗情况， 能分析这条 SQL 整个生命周期的耗时。但没有上面 pt-query-digest 那款慢查询日志分析工具强大， 但 pt-query-digest 是外置的需要单独下载，如果你想用内置的话，能够满足你的需求的话，选择 Show Profile 就行。
- pt-query-digest pt-query-digest 是一款很强大的慢查询日志分析工具，可以分析 MySQL 数据库的 binary log 、 general log 日志， 同时也可以使用 show processlist 或从 tcpdump 抓取的 MySQL 协议数据来进行分析。
使用索引查询
- 应用关键字 LIKE 优化索引查询
- 查询语句中优化多列索引查询
- 查询语句中使用关键字 OR 优化索引查询
