MySQL数据库调优
目录
测试数据准备
SQL查询慢原因分析
开启SQL慢查询分析
分析SQL执行计划
数据库诊断
索引
索引创建原则
索引失效分析及解决办法
特定查询语句分析
JOIN语句
分页语句
统计Count语句
排序Order By语句
分组Group By语句
测试数据准备
本文基于Mysql官网employees数据库进行模拟测试，环境准备如下：
1、https://github.com/datacharmer/test_db 下载工程代码到本地
2、安装employees数据库数据：mysql -u root -p123456 <employees.sql
3、查看安装结果：mysql -u root -p123456 <test_employees_md5.sql
SQL查询慢原因分析
开启SQL慢查询分析
set global slow_query_log = 'on'; #开启慢查询
set global log_output = 'file,table';#慢查询日志记录到文件和表中
set global long_query_time = 0.1; #慢查询时间超过100毫秒则记录
set global log_queries_not_using_indexes = 'on';#开启查询未使用索引
show variables like '%slow_query_log_file%'; #显示慢查询日志记录的文件位置
select * from `mysql`.slow_log; #慢查询记录的结果数据
分析SQL执行计划
explain可用来分析SQL的执行计划，示例如下：
1）id相同，执行顺序从上往下
2）id不同，id值越大，优先级越高，越先执行
输出结果重点字段说明：
1）type 联接类型，显示查询使用了何种类型，查询性能从最好到最坏排序如下（一般保证查询至少达到range级别，最好能达到ref）：
system：表中仅有一行（系统表）这是const联结类型的一个特例。
const：针对主键或唯一索引的等值查询扫描,最多只返回一行数据.const 查询速度非常快,因为它仅仅读取一次即可。
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描。
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体。
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描。
index：index 与all区别为index类型只遍历索引树，通常比all快，因为索引文件比数据文件小很多。
all：遍历全表以找到匹配的行。
2）possible_keys：可能的索引选择
3）key：实际选择的索引
4）rows：估计要扫描的行数
5）filtered：表示符合查询条件的数据百分比
6）Extra 重要的额外信息
Using filesort：MySQL中无法利用索引完成排序操作称为“文件排序”。
Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by
Using where :表明使用where过滤
数据库诊断
常用的诊断命令如下：
| 命令 | 功能 |
| show processlist ; | #查看当前正在运行的线程信息 |
| show status ; | #查看数据库服务器相关信息 |
| show variables; | #查看mysql变量 |
| show table status from employees; | #查看表的状态 |
| show index from employees; | #查看表的索引信息 |
| show create table salaries | #查看salaries建表语句信息，包含表所用的引擎及字符集信息，如ENGINE=InnoDB DEFAULT CHARSET=latin1 |
| select version() | #查看数据库版本 |
索引
MySQL中，InnoDB存储引擎使用B+树（B+ Tree）的数据结构来存储索引。
主键索引（Primary Key Index）：基于主键创建的索引，通常是表中唯一的标识符，叶子节点存储主键及数据。
非主键索引(二级索引、辅助索引)：基于表中的其他列创建的索引，叶子节点存储索引以及主键。例如where 二级索引 = xxx，系统先根据二级索引找到主键，然后根据主键找到数据。
MySQL中的索引左前缀原则（Leftmost Prefix Principle）是设计索引以提高查询效率的重要规则。在MySQL中，当你为表中的列创建复合索引时，MySQL会按照索引列的顺序从左到右依次使用这些列。这意味着，如果一个查询的条件能够匹配索引的最左边的列，那么这个索引就会被使用。
【示例】：表employees 新建了索引：first_name ，默认index-type=BTREE，观察以下执行计划：
在某张表中，建立了复合索引index(name,age,sex)，以下查询说明如下：
where age = 10 and sex = 1;#查询条件没有最左侧name,无法使用复合索引
索引创建原则
以下情况适合建立索引：
1、select语句，频繁作为where条件的字段
2、update/delete语句的where条件
3、需要分组、排序的字段
4、distinct所使用的字段
5、字段的值有唯一性约束
6、对于多表查询，联接字段应创建索引，且类型务必保持一致
以下情况不适合建立索引：
1、表中记录很少
2、查询条件很少用到的字段
3、字段数据重复，通过索引选择不到很少的数据，比如sex(0:男，1女）之类的字段
索引失效分析及解决办法
索引失效场景如下：
1、索引字段参与表达式计算
解决办法：前台传递数据库明确的值
2、索引字段参与表达式计算--当成函数的参数，解决方法同1
3、索引字段使用了左模糊，不符合索引左前缀原则
解决办法：避免使用左模糊查询
4、where使用or条件，部分字段没有索引导致有索引的字段也失效
解决方法：last_name 也建立索引
5、基于字符串字段建立的索引，查询时没有用引号
解决方法：编写准确的sql
6、不符合左前缀原则的查询，建立了复合索引，查询时不符合左前缀原则
解决方法：根据实际业务调整索引顺序，将last_name字段放在复合索引的第一个字段，再次查询。
特定查询语句分析
JOIN语句
Sql中常见的join语句分为：inner join，left join，right join。使用join语句查询建议遵循如下原则：
1、表join的字段尽量创建索引，并且字段类型要保持一致
2、join的表不建议太多，尽量不要超过3张以上
3、尽量减少扫描的行数，通过explain可以获取
分页语句
如果表数据量很大，需要查询很大页数数据的需求，可以采用 覆盖索引+Join的方式提高查询效率，其中select emp_no from employees limit 300000,10 查询的是覆盖索引，示例如下：
-- 改造前查询需要 193毫秒
select * from employees limit 300000,10;
-- 改造后：查询需要 70毫秒
select * from employees e inner join (select emp_no from employees limit 300000,10) t
on e.emp_no = t.emp_no
统计Count语句
1、count(*)和count(1)含义一样
2、count(*)不会排除统计null的行，count(字段）会排除统计字段=null的行
排序Order By语句
使用order by最高效的做法是：利用索引自带的默认排序，进而不要排序。基于B+Tree创建的索引天然就有顺序，如果order by字段就是索引字段，查询就很高效，不会出现Extra = filesort的低效情形，所以对于排序子句的优化就是防止出现filesort，如果实际业务filesort情形无法避免，考虑通过增大设置缓冲区大小（sort_buffer_size)，进而减少临时文件的产生，进而提高查询效率，示例如下：
-- 按照salary升序排序就产生了Using filesort，耗时：707毫秒
explain
select * from salaries order by salary
-- 按照emp_no升序排序就不会产生Using filesort，耗时：34毫秒
explain
select * from salaries order by emp_no
-- 默认sort_buffer_size = 262144
show variables where Variable_name = 'sort_buffer_size';
-- 设置sort_buffer_size = 1024*1024 = 1m
set sort_buffer_size = 1024*1024;分组Group By语句
分组语句尽量避免出现临时表（临时表性能较差），示例如下：
