Mysql索引的简单优化一

Caixr
364 阅读7分钟

根据索引简单优化

  • 联合索引第一个字段使用到范围查询不会走索引
EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

image.png 可以看到possible_key提示可能会使用到联合索引，但是type使用到的是all全表扫描，mysql内部判断第一个字段使用到范围查询，结果集会很大，可能需要回表，效率不高，就使用全表扫描。

  • 使用覆盖索引优化
  • in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';

image.png

  • like SS% 一般情况都会走索引 索引下推（Index Condition Pushdown，ICP）, like KK%其实就是用到了索引下推优化 什么是索引下推了？ 对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager' 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。 在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'LiLei' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。 MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

常见sql深入优化

  • order by和group by优化
EXPLAIN select * from employees where name = 'lilei' and position = 'dev' order by age

image.png 利用最左前缀原则：中间字段不能断，因此查询使用到了name索引，从key_len=74，可以知道age索引列在排序过程中使用到，因为Extra字段中没有using filesort。

EXPLAIN select * from employees where name = 'lilei' order by position;

image.png 从图中分析，key_len=74使用了name索引，因为使用了position进行排序，跳过了age，出现了using filesort。

EXPLAIN select * from employees where name = 'lilei' order by age,position;

image.png 查找只使用到了索引name,age和position用于排序，无using filesort。

EXPLAIN select * from employees where name = 'lilei' order by position,age;

image.png 因为position和age顺序颠倒了。

EXPLAIN select * from employees where name = 'lilei' and age=18 order by position,age;

image.png 发现并未使用到using filesort,因为age为常量，在排序过程中被优化，所以索引未被颠倒。

EXPLAIN select * from employees where name = 'lilei' order by age desc,position asc;

image.png 虽然排序字段和索引顺序一样，且order by默认升序，这里的position desc变成了降序，导致与索引的排序方式不同，从而产生了using filesort。

EXPLAIN select * from employees where name in('lilei','hanmeimei') order by age desc,position asc;

image.png 对于排序来说，多个相等条件也是范围查询。

  • join关联查询优化 对于多表join关联的查询优化，其实并没有什么优化的空间，在表之间的关联尽量在两张表之间进行关联，如果非要多表关联尽量在代码中处理。
    MySQL中表关联常见有两种算法：
    1）嵌套循环连接 nested-loop join (nlj)算法： 一次一行循环的从第一张表中（驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）中取出满足条件的行，然后取出这两张表的结果合集。
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

image.png 根据执行计划可以得知：
驱动表是t2，被驱动表是t1下。先执行的是驱动表；优化器一般会优先选择小表做驱动表。所以用inner join时，排在前面的不一定是驱动表。
当使用left join时，左表时驱动表，右表时被驱动表，当使用right join时，右表时驱动表，左表时被驱动表，当使用join时，mysql会选择数据库量比较小的作为驱动表，大表作为被驱动表。
使用了NLJ算法，一般join语句中，如果执行计划Extra未出现using join buffer则表示使用的join算法时NLJ。
如果被驱动表的关联字段没有索引，使用NLJ算法性能会比较低，mysql会选择Block Nested-Loop join 算法。
2）基于块的嵌套循环连接Block Nested-Loop join（BNL）算法
把驱动表的数据读入到join_buffer中，然后扫描被驱动表，把被驱动表每一行取出来跟join_buffer中的数据做对比。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

image.png sql的大致流程：
1.将t2的所有数据加载到join_buffer中(注意：并不是所有的列都会放到join_buffer中，只有查询和条件过滤的列才会放到join_buffer中，因此，尽量不要使用 select * )
2.把t1中每一行取出来，跟join_buffer中的数据做对比
3.返回满足join条件的数据
整个过程对表t1和t2都做了一次全表扫描，因此扫描的行数为10000（t1的数据总数）+100（t2的数据总数）=10100.并且join_buffer里的数据是无序的，因此对表t1中的每一行，都要做100次判断，所以内存中的判断次数是100 * 10000=100万次。
如果t2表的数据量很大，join_buffer放不下呢？
join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下所有的数据的话，会采用分段加载。
被驱动表的关联字段没有索引为什么选择的是BNL?
原因很简单，如果使用的是NLJ算法，那么扫描函数为100*10000=100万次，这个是磁盘扫描。磁盘扫描的效率低。
对于关联sql的优化

  • 关联字段加索引，让mysql做join的时候尽量选择NLJ算法。
  • 小表驱动大表。在知道表数据量的情况下，尽量使用数据量少的表关联数据量大的表。

in和exsits优化

原则：小表驱动大表

count(*)查询优化

count(*)的效率很高，并不需要做其他优化。没必要使用count(1)。

优化总结

  • MySQL支持两种排序index和filesort，index指的是扫描索引本身完成排序。index的效率高，filesort效率低。
  • order by满足两种情况会使用using index 1）order by语句满足索引最左前缀原则。
    2）使用where子句与order by字句条件列组合满足最左前缀原则
  • 尽量在索引列上完成排序，遵循**索引建立（索引创建的顺序）**时的最左前缀原则。
  • 如果order by的条件不在索引列上，会产生using filesort。
  • 能用覆盖索引尽量使用覆盖索引。
  • group by与order by类似，实质是先排序后分组，遵照索引创建顺序的最左前缀原则。对于group by的优化如果不需要排序可以加上order by null禁止排序。注意：在使用where的时候尽量不要去使用having。

using filesort文件排序原理详解

  • 单路排序：一次性取出满足条件行的所有字段，然后再sort buffer中进行排序。
  • 双路排序（回表排序）：根据条件取出排序字段和可以直接定位行数据的行ID，然后再sort buffer中进行排序，排序完成后需要再次取回其他需要的字段。 Mysql中通过比较系统变量max_length_for_sort_data(默认1024字节)的大小和需要查询字段总大小来判断使用哪种排序模式。 如果字段的总长度小于max_length_for_sort_data，那么使用单路排序模式；如果大于，那么使用双路排序模式。

索引设计原则

  • 代码先行，索引后上
  • 联合索引尽量覆盖条件
  • 不要在小基数字字段上建立索引。例如：性别字段。
  • 长字符串可以采用前缀索引。
  • where和order by冲突时优先where
avatar
文章
阅读
粉丝