MYSQL基础优化展示一：
1、使用前需要：
1.1、explain的常用参数列
id:
id值不同时，从大到小执行。
id值相同时，一般按照顺序执行，但是如果同一个值内存在subquery(子查询)，则优先执行子查询，如果存在多个子查询，则按顺序从上到下执行。
type:
NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
从左到右最好到最差
possible_keys:
可能使用到的索引，一般在有多个索引时会用到
key:
最后使用到的索引
实际使用的索引，如果为NULL，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅出现在key列表中，也就是此时possible_keys是NULL
1.2、内容摘要
1、使用create_time字段对比加索引与不加索引的性能差距
2、LIMIT与LIMIT+索引过滤条件实现的翻页性能对比
3、
1.3、索引使用的场景
1）匹配全值，对索引中的所有列都指定具体的值。
2）匹配值的范围查询，对索引的值能够进行范围查询。
3）匹配最左前缀，仅仅使用索引中的最左边列进行查找。比如列A,B,C组合的联合索引（A,B,C），能包含（A）,(A,B),(A,B,C）的查询条件，但是无法实现（B），（B,C）的查询
4）仅仅对索引进行查询，当查询的列都在索引的字段中时，查询的效率更高
5）匹配列的前缀部分，仅仅使用索引中的第一列，并且只包含索引第一列的开头部分进行查找。如"name%"
6）能够实现索引匹配部分精确而其他部分进行范围查询。如： a="a" and create_time < "xxx" and create_time > "yyy"
7）如果列名是索引，那么使用column_name is null就会使用索引。
1.4、索引无法使用的场景
2、create_time查询中，添加索引与不添加索引的对比
create_time添加索引与不添加索引的执行效率对比。
参考表为：
一共有9216行数据
执行计划语句：
EXPLAIN SELECT count(*) FROM `cmd_log` WHERE create_time > "2021-11-14 04:43:05" and create_time < "2021-11-15 04:43:05";
没有添加索引时的查询
添加索引后执行
添加索引后：
1、rows列中显示的扫描行数不一样了，对应type值，一个ALL：全扫描，一个是range是范围查询
2、在Extra中记录不一样，一个是where查询，另一个是where查询加index索引。
3、在possible_keys和keys中记录了两种查询的使用的索引，如idx_create_time，
where只是启动过滤作用，加速查询还是要依靠索引
其它分析：
create_time < "2021-11-15 04:43:05" 时一共有7174多条数据，而create_time > "2021-11-14 04:43:05"一共有6186多条数据。
3、LIMIT与LIMIT + 索引过滤的翻页性能对比
limit全局扫描与limit +索引的查询对比，这里的索引一般加载where条件上
没有添加索引过滤时，执行查询的时间
select * from node_file_viruses limit 100000, 20
没有添加索引过滤时，执行计划语句：
EXPLAIN select * from node_file_viruses limit 100000, 20
添加索引过滤时，执行查询的时间
添加索引过滤时，执行计划语句：
查询对比：
1、添加索引后，查询数据时从记录的全部扫描(对应type=all)变成了范围查询（对应type=range），所以扫描行数下降了一半（从482782 -> 241391）
2、possible_keys和key中记录了，本次查询使用了主键（主键默认有索引，没有的话，由数据库后台自定义主键）做为索引。
一般说明：
在没有添加（索引过滤时）where条件时，执行语句会先扫描表中的前100000条数据，然后抛弃前面的100000条数据，选择后面的20条数据；而在添加（索引过滤时）where条件后，会先使用（索引过滤时）where条件找到第100000，然后在选择后面的20条数据。由于后者会使用到索引因此在速度上更快。
4、小表驱动大表性能对比
4.1、使用C语言来理解小表驱动大表的原理
for(int i = 0; i < 2; i++) {
for(int j = 0; j < 10000; j++) {
......
}
}
for(int i = 0; i < 10000; i++) {
for(int j = 0; j < 2; j++) {
......
}
}
如果小的循环在外层，对于数据库连接来说就只连接2次，进行20000次操作，如果10000在外，则需要进行10000次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。
GROUP BY使用效率：
JOIN的表不宜过多
根据阿里巴巴开发者手册规定，JOIN表数量不应该超出3个。
如果join太多，mysql在选择索引的时候会非常复杂，很容易选错索引。
阿里巴巴开发者手册要求的JOIN表数量不应该超出3个，也并非一成不变的，如果业务并发量不大，数据量级也在容忍中，也不是不可以调整的。
JOIN时的注意点
在涉及多表查询时，往往优先使用JOIN操作，
控制索引的数量
众所周知，索引能够显著的提升查询sql的性能，但索引数量并非越多越好。
因为表中新增数据时，需要同时为它创建索引，而索引是需要额外的存储空间的，而且还会有一定的性能消耗。阿里巴巴的开发者手册中规定，单表的索引数量应该尽可能控制在5个以内，并且单个索引中的字段数不超过5个。
mysql使用B+树的结构来存储索引的，在Insert，update和delete操作时，需要更新B+ 树索引，如果索引过多，会消耗很多额外的性能。
如果系统中并发量不高，表中的数据量也不多，其实超出5个也可以的，只要不超过太多即可。
对于高并发系统而言，务必遵守以上规则。因为索引查询也会涉及到mysql内部锁的问题
能够建立联合索引，就不建立单个索引，可以删除无用的单个索引。
避免重复查询
常见索引失效的原因：
1、不满足最左前缀原则
2、范围索引列没有放到最后
3、使用了select *
4、索引列上有计算
5、索引列上使用了函数
6、字符类型没有加引号
7、用is null和is not null没注意字段是否允许未空
8、like查询左边有%
9、使用or关键字时没有注意
浙公网安备 33010602011771号