MySQL 索引
1. MySQL 索引及优化实战(一)
2. MySQL 索引及优化实战(二)(本文)
MySQL索引优化规则(接上篇文章)
11. 使用短索引(又叫前缀索引)来优化索引
前缀索引,就是用列的前缀代替整个列作为索引key,当前缀长度合适时,可以做到既使得前缀索引的区分度接近全列索引,同时因为索引key变短而减少了索引文件的大小和维护开销,可以使用 count(distinctleft(列名, 索引长度))/count(*)的来计算前缀索引的区分度。
前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于ORDER BY和GROUP BY操作,也不能用于覆盖索引(Covering index,即当索引本身包含查询所需全部数据时,不再访问数据文件本身),很多时候没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。
例如对于下面的sql语句:
SELEC * FROM employees.employees WHEREfirst_name='Eric' AND last_name='Anido';
我们可以建立索引:(first_name, last_name(4))
12. 建立索引的列,不允许为null
单列索引不存null值,复合索引不存全为null的值,如果列允许为null,可能会得到“不符合预期”的结果集,所以,请使用notnull约束以及默认值。
13. 利用延迟关联或者子查询优化超多分页场景
MySQL并不是跳过offset行,而是取offset+N行,然后返回放弃前offset 行,返回N行,那当offset特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行SQL改写。
示例如下,先快速定位需要获取的 id 段,然后再关联:
select a.* from 表1 a, (select id from 表1 where 条件 limit 100000,20 ) b wherea.id=b.id