Mysql查询优化器
在多种情况下,可能会导致查询结果从缓存中清除,例如:.
数据可能已被修改
您可能运行了一条语句,其文本与缓存的语句略有不同(小写/大写,换行符,...)
缓存可能已达到其大小限制之一(内存,查询计数,块等),并决定逐出您的特定查询
高速缓存碎片过多,即使某些内存仍然可用,也会强制高速缓存修剪查询
等等...
mysql的缓存随时都有可能清空,长时间的sql还是会有 问题,这时候就会在对查询做优化
Mysql的查询优化器会优先查询缓存,若缓存没有命中,再去解析SQL,查询优化器还会优化SQL,即使where条件中的等值查询条件按照索引的顺序进行排序。
1.不在索引列上做任何操作
如果查询的列不是独立的,Mysql就不会走索引,不是独立的列是指索引的列不能是表达式或者是函数的参数。
例:索引中使用表达式,Mysql走的是全表扫描
优化:
- 将索引列单独放在比较符号的一侧
- 索引列尽量不使用函数
- 通过Java代码处理函数逻辑
2.尽量全值匹配
要查询的列尽量在联合索引中一一匹配,能在where条件中使用索引就尽量使用,这样可以提高key_len
例:要查询name,age,work,update_date,建立(name,age,work,update_date)的联合索引
去掉联合索引后
优化:
- 尽量在where中使用联合索引中的字段,查询中尽量只查联合索引字段,实现覆盖索引,减少回表
3.最佳左前缀
Mysql的索引底层是B+树,所有的索引都在B+树的叶子节点并且是连续的,叶子节点会按照索引建的顺序进行构建,即最左边的索引会在叶子节点的最高层并按照升序进行排序,当第一个索引的列相等时,第二个索引才会小范围排序,这样就导致查询索引会首先匹配叶子节点的第一层,如果跳过第一层直接查询第二层是不会走索引的。
意思就是不要打乱联合索引的顺序查询。
行数增加
优化
- 建立了联合索引,尽量按照联合索引的最左边的索引进行匹配
- 若需要跳过最左边的列进行匹配,可单独建立索引
4.范围条件放最后(索引定义顺序的最后)
当查询条件中间有范围查询时,会使后面的索引失效
Mysql的查询优化器会使where条件中的查询条件按照索引的顺序进行排序,因此将要范围查询的索引列放到联合索引的最后是最合适的,因为前面的等值条件已经筛选出了小部分数据,过滤掉了无用的数据,因此范围查询放最后效率也是很高的
优化:
- 将要范围查询的索引列放到联合索引的最后
5.尽量使用覆盖索引
之前有讲过
w字段是后加,不在联合索引中,导致查询出现全表查询
优化:
- 要查询的列全部建立联合索引
6.慎用不等于(!= <>)
Mysql的查询条件中用到了不等于(!= <>)会使索引失效,导致查询走全表扫描
7.Like要当心(%)
当Mysql的模糊查询 % 在前面时,会使索引列失效,走全表扫描
因为Mysql的底层是B+树,B+树叶子节点的索引数据是连续的(根据索引列升序排序),因此当 % 在前面会使B+树不知道怎样去使用索引查询,从而使索引失效
8.字符串类型加引号
Mysql的查询优化器会自动进行类型转换,因此当查询条件不加引号时,会使索引列失效
9.使用or要注意
当Mysql查询时使用到了or,且其中一个不是索引列时,Mysql不会走索引,会走全表扫描
优化:
- 使用union all代替or
- 使用覆盖索引
10.排序要小心
当Mysql的排序(ASC,DESC)后使用了两个列,且两个列来自不同的索引,是不会走索引的。
当Mysql的排序既用到了ASC,又用到了DESC,Mysql是不会走索引的
优化:
排序要么都用DESC,要么都用ASC
当需要对多个列排序时,将这两个列按照顺序建立联合索引
11.主键按照顺序进行插入
最好避免随机的(不连续且值的分布范围非常大)聚簇索引,特别是对于I/O密集型的应用。例如,从性能的角度考虑,使用UUID来作为聚簇索引则会很糟糕,它使得聚簇索引的插入变得完全随机,这是最坏的情况,使得数据没有任何聚集特性
最简单的方法是使用AUTO_INCREMENT自增列。这样可以保证数据行是按顺序写入,对于根据主键做关联操作的性能也会更好
12.优化分页(LIMIT)
Mysql的分页语句LIMIT会将偏移量前的数据全部查询出来,然后抛弃掉不需要的数据,返回需要的数据,因此当LIMIT的偏移量很大时,代价是很高的。
优化:
- 使用子查询,先查询到需要查询的id,然后进行关联查询