三、MySQL调优

一、调优金字塔

二、什么是慢查询

1、MySQL设置了一个【long_query_time参数，默认=10s】；

        --  执行时间超过这个阈值的sql都会被记录进日志；

        --  被记录的sql叫慢查询；

2、触发慢查询的原因：

        -- 数据量大；

3、请求了不需要的数据？

        --  查询不需要的条数；

        --  总是取出所有字段；

        --  重复查询相同的数据；可以做缓存；

4、是否在扫描额外的记录？

        --  SQL查询的相应时间：服务时间 + 排队时间；

        --  扫描的行数和返回的行数；

        --  扫描的行数和访问类型；

5、如何优化？

        --  使用覆盖索引，避免回表，获得第三星；

        --  改变表结构；可以将频繁的多表查询合并出一张汇总表；

        --  重写复杂的SQL；

三、慢查询及实战

1、设置慢查询的阈值：

--  关闭/开启慢查询记录：
set GLOBAL slow_query_log=0/1; #0-关闭 1-开启

--  慢查询的时间：
show variables like 'long_query_time';

--  设置时间：
set GLOBAL long_query_time = 20;

2、获取sql日志存储位置的绝对路径：

-- 里面存放了许多慢查询的sql记录；
show variables like '%slow_query_log_file'

3、【无索引】SQL记录：

-- 如果开启，那么认为没使用索引的SQL也是一个慢查询；

-- 查询是否开启：
show variables like '%log_queries_not_using_indexes%'

4、读取慢SQL记录：

mysqldumpslow -s c -t 10 slow.log

-- 解析：

mysqldumpslow：MySQL提供的固定命令，用来查询MySQL日志；

-s   *：启用排序；可以设置按哪个属性排序；

c：将（count多）的数据排到最前面；其他的还有：（r-->rows）

-t  10：限制返回的日志数量；这里表示返回10条日志；

slow.log：具体的日志文件；

四、什么是执行计划

1、执行计划的语法：

-- 在SQL查询语句前面加个关键字：explain；

explain select * from table；

五、执行计划详解

关键字	说明	包含哪些属性
id	每个select都有一个id；
table	被查询的表名称；
select_type	查询类型； 表示每个【子查询】在【大查询】中的角色；	SIMPLE：最简单的select查询，不使用union和子查询； PRIMARY：最外层的select查询； SUBQUERY：是个子查询，且不依赖外部查询结果； UNION：UNION关键字后面的select查询，不依赖外部查询的结果； UNION RESULT：UNION结果集，将要从这里去重；      --  这里有个特别的table：<union 1,2,3>，其中1，2，3表示前面查询的id； DEPENDENT SUBQUERY：子查询中的第一个select，依赖外部查询的结果； DEPENDENT UNION：子查询中的UNION后面的select语句； DERIVED：表示from后面的子查询；MySQL会将这些查询结果放入一个临时表；
partitions	分区
type	表示MySQL对表进行查询时，用的【访问方法/访问类型】； 查询表时用的方法； 这些方法有好有坏；	SYSTEM--系统：最好的方式，系统表查找； CONST--常量：可以实现唯一命中；使用主键/唯一索引查找；​​​​ rq_ref：在连接查询中，驱动表（主表）使用主键或索引列关联查找就会使用这种访问类型； ref：使用二级索引等值查询；where a = 2;或者where a is null; ref_or_null：使用二级索引的同时也查询条件为null的数据； index_merge：使用了合并后的索引； RANGE--范围：使用了范围查询； INDEX：走了覆盖索引；实现了索引的全覆盖； ALL：最坏的方式，全表扫描；
key_len	索引长度	单位：字节 byte； 用到的索引的列的【设定长度】； null占一个字节的长度； varchar额外使用2 byte记录长度；且每个字占3 byte；
ref	关联的查询类型	与type的参数一样；
filtered	预估有几成比例满足条件	与rows搭配使用； 10% * 5000（rows）；
extra	其他内容	展示一些非准确的内容，可以参考：用到了哪些东西

六、查询优化器

1、SQL执行过程

七、高性能的索引使用策略

序号	策略名称	注意事项
1	不在索引列上做任何操作	表达式、函数
2	尽量全值匹配； == 尽量把联合索引的列都利用起来；	与联合索引有关系； 搜索的【条件】和【索引的列】保持一致；
3	最左匹配原则； == 尽量使用联合索引的最左的字段；	与联合索引有关系； 搜索的【条件】尽量用联合索引【最左边】的字段； 如果没用到最左边的字段，那么走的是全表扫描； 排序字段也要用最左的索引字段；
4	范围条件尽量放最后	与联合索引有关系； 搜索条件里面出现的【第一次范围查询】会使用索引，然后后面的所有条件就都不会使用索引了； 【第一次范围查询】是个分水岭，前面的条件可能会用索引，后面的条件都不会用索引；
5	尽量使用覆盖索引 == 尽量不用*	减少回表
6	慎用不等于	基本是个全表扫描
7	Null和Not有影响	允许为null               不允许为null is null                走索引                      全表扫描 is not null          走索引                      走索引
8	字符类型加引号	where order_no = 6              索引失效 where order_no = '6'             走索引
9	使用like有讲究	like 'abc%' 好 like '%abc' 坏
10	使用or关键字要注意	条件全是索引列才走索引，一旦出现非索引列，那就全表扫描；
11	要注意排序	索引列尽量和order by 字段顺序保持一致；
12	尽可能按主键顺序插入数据	主键索引的B+树会按主键排序； 要减少B+树结构改变；
13	优化count查询	count(a) 的数量不算 null 的数量；
14	优化limit分页	常规：limit 10000，10；-- 扫描10010条，返回后面的10条；  优化：     ①select * from (select id from table limit 10000,10) a,table b where ​​b.id​​ = a.id；     ②select * from table where id > 10000 limit 10000,10；         -- 备注：与业务有关系，前端可以将上一页的最后一条数据的id传入；
15	关于null的特别说明	在mysql中认为null有3种理解； 1、select null = null ;  -->  此时null是一个不确定的值 2、null 是一个确定的值，代表 没有 3、count(1)  -->   null 不计入总数，此时null被忽略；