高性能MySQL之索引

关于索引使用及限制

B-Tree索引

匹配条件（复合多列的索引）：

全值匹配：和索引中的所有列进行匹配
匹配最左前缀：只使用索引的第一列
匹配列前缀
匹配范围值
精确匹配第一列并范围匹配第二列【必须是第一第二列】
只访问索引的查询：查询只需要访问索引，无须访问数据行，即覆盖索引

限制条件（复合多列的索引）：

不按最左第一个开始查找，则无法使用索引。
必须是顺序条件，不能跳过中间索引列。【第二列排序是在精确匹配第一列情况下，第三列匹配是在精确匹配到第二列的情况下，生效的】
范围查询后的所有列都无法使用索引优化。

聚簇索引和二级索引

两者详细内容

聚簇索引，这个是InnoDB表的主键索引，其叶子结点中保存了数据行。╮(╯_╰)╭
二级索引，非聚簇索引，叶子结点中保存了对应的主键值

其他索引

哈希索引：Memory引擎表才支持，略过。【InnoDB有自适应哈希索引，自动的不管了】
空间数据索引R-Tree，MyISAM引擎支持，略过。
全文索引，特定需求下使用，另外再系统的学习。
覆盖索引，对InnoDB很有用的，需要索引覆盖到所有使用的数据，则会很大程度提升性能。【比如覆盖主键id，提高全表count的性能】【栗子：P171-P174】

使用策略

使用独立的列，不对列数据进行函数或其他计算处理。
对长数据做前缀索引，索引长度需要测试，限制是无法使用此索引排序与分组。
非主键顺序插入大量的数据后，使用OPTIMIZE TABLE进行一次重新组织表。
利用索引进行排序的时候，需要order by 的顺序与索引列顺序一致，即需要满足索引的最左前缀要求；若查询需要关联多张表，则order by子句引用的字段需全部为第一个表；前导列为常量时可以不满足最左前缀要求。

一个栗子

栗子对的是一张表【P183】

不要建立太多个索引，重复冗余的。【一个是空间的开销，一个是选择优化的开销】
范围查询列尽量放在索引的最后列，避免索引无效。
避免多个范围查询【书上的解决是使用逻辑绕过方案，我觉得直接保留筛选率最高的在前即可，因为业务上的范围查询可能很多，无法考虑到所有的绕过方案】
最后总结：都是没用的东东，完全不切实际，因为通常的查询条件都呀的是动态的，有N个。╮(╯_╰)╭
一些其他信息
MySQL在5.0+ 引入了“索引合并”的策略，好像可以使用多个索引的样子。【如果看到这个，其实说明需要优化索引了，据书上说，性能不高，使用关闭或忽略索引语句：optimizer_switch 或者 IGNORE INDEX】

关于查询性能优化【干货】

一些的建议

关联查询禁止使用*返回所有关联表字段。通常使用table.*。
大量关联查询可以进行拆分多个查询进行。【比如十几张表的关联╮(╯_╰)╭ ，最后在子查询可以有固定值的情况下拆分】
使用show full PROCESSLIST查看数据库线程状态
一个对in(…)大量数据查询的优化 (╯‵□′)╯︵┻━┻ 这个在5.6的版本已经优化了参考文章

select * from table_a where table_a.col in (select col from table_b where ...)
-- 以上这种写法，的效率会很低，因为mysql并不会先进行in内部的只查询，而是变成exists(),这样就是扫描table_a全表了。╮(╯_╰)╭  
-- 这里的优化可以有以下两种   
-- ****这个在5.6的版本已经优化了，已经优化为第二种写法了 (╯‵□′)╯︵┻━┻ *****
-- ****  亲测5.7.2的版本中是一样的效果了 就都没差了各种写法*****

-- 第一种这个方法据说效率更快，我还没试过
select * from table_a where table_a.col in (select group_concat(col) from table_b where ... )
-- 以上这种方法无法使用，in里面没办法使用group_concat，绕过的方法为分两次查询，或在以下方法
select * from table_a where find_in_set(table_a.col,(select group_concat(col) from table_b where ... ))

-- 第二种方法是常用的方法了
select * from table_a inner join table_b on table_b.col = table_a.col where ... 

-- group_concat的长度限制
 show variables like 'group_concat_max_len';
 SET GLOBAL group_concat_max_len=4294967295 ;
 SET SESSION group_concat_max_len=4294967295 ;

获取特定条件最小id

1
2
3

-- 第一个是正常的获取最小id值，第二个为调整后的，执行计划是一样的，真实使用的时候，确实会快一些的。如果有超大数据量测试的话，估计还是比较明显的吧。
EXPLAIN select MIN(id) FROM merchant_trade where merchant_name = '白**' 
EXPLAIN select id FROM merchant_trade use index(primary) where  merchant_name = '王**'  limit 1

查询优化器提示，有很多比如：使用或忽略特定索引，是否缓存排序，等等。如没有特殊需要不要使用，因为我不会╮(╯_╰)╭ 【主要这里的版本是5.5 ，现在用的都是5.6~~5.7+的了，谁知道有的是不是已经优化改了 ε=(´ο｀*)))唉】【P232~~P235】
关联查询的优化：确保ON或USING子句中的列上有索引，且要考虑关联的顺序，当表A 和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建上索引；当表A 和表B用列c关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建上索引。【原因看下方注意点中关联执行的策略】
Limit的优化：一个常见的问题是当偏移量非常大的时候，比如：LIMIT 10000 20这样的查询，MySQL需要查询10020条记录然后只返回20条记录，前面的10000条都将被抛弃，这样的代价非常高。优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时，这样做的效率会提升非常大。
在做地理位置范围查询的时候（经纬度），先过滤正方形最值条件，再计算具体的距离限制。

一些注意点

MySQL中的in查询会进行排序之后使用二分法进行判断，其复杂度为O(log n),而不是等价于or的写法O(n)，所以在in()列表中有大量值的时候，处理速度并不会下降很多，反而可能很快。
连接关联的方式：join … on ,join…using , table a,table b where a=b 这三种写法最终所有的查询都被转成了 Theta 风格。除了写法不同外，没什么区别。【文章参考】
当前MySQL关联执行的策略：mysql对任何关联都执行嵌套循环操作，即mysql先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻打匹配的行，依次下去，直到描述到所表表中匹配的行为止。然后根据各个表匹配的行，返回查询中需要的各个列。mysql会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行以后，mysql返回到上一层次关联表，看是否能够找到更多的匹配记录，依此类推迭代执行。按照这样的方式查找第一条表记录，再嵌套查询下一个关联表，然后回溯到上一个表。
MySQL没有全连接的语法，的原因可能就是，关联表使用的规则为“嵌套循环关联”，所有连接转换为左连接，进行循环递归嵌套查询，但外层循环值为空的情况下无法进行后续处理，所以无法完成全连接。【就是说关联必须有一个主表，保证主表是有值的】【P215】
关于COUNT：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以尽量使用 count(*)
UNION默认情况下会去重的，等效于distinct，而通常使用UNION ALL 。毕竟去重也是有性能开销的。

在手动查询一些数据的情况下，可以使用自定义变量进行复杂数据的查询，还是很好用的。【P247】【虽然看着挺好用的，但是在系统中是用是做不到的，限制太多了。P244】

-- 这里有个用法挺不错的 
-- union 查询，查询一个值的时候，当第一个没查到数据的时候 再查询第二个条件
SELECT GREATEST(@found := -1,id) FROM `app_user` where id = 666
union all
select id from app_user_his where @found is null and id = 666

原文链接：https://blog.csdn.net/xiaohangblog/article/details/106259662