menu

Junjielee Blog

在平凡中坚持前行,总会遇见不凡的自己

如何选择Mysql索引

相信大家用得最多的存储引擎是InnoDB,用的最多的索引是B-Tree索引了,那么平时该怎么设计索引呢?

首先,如果数据量笔记少,表里只有几万条以及以下的,则可以忽略索引

独立列索引

如果存在单列索引,WHERE条件里的索引字段应该避免使用表达式函数

例如下面的 SQL不会使用到索引字段 age:

SELECT * from users WHERE age + 1 = 19;

组合索引

组合索引符合最左前缀匹配原则,并且只对< <= = > >= BETWEEN IN 部分LIKE操作有效,以通配符%_开头作查询的,MySQL不会使用索引。

巧用前缀索引

前缀索引针对的是字符串字段,因为部分字符串比较长,而例如使用前4位就可以过滤掉大部分数据,所以,可以使用更小的索引,达到相同的查询效果,甚至更快(因为字符串索引长度小很多)。 但是这个索引不能用于ORDER BYGROUP BY覆盖扫描

如何创建:

ALTER TABLE users ADD KEY (name(7));

所以,这个前缀索引,是根据实际数据量以及数据内容来设计的。

选择合适的多列索引的顺序(既能用于查询又能用于排序)

索引顺序的一个法则:将选择性最高的列放到索引最前列。具体还需要考虑数值的分布情况来决定使用索引

多列索引严格按照从最左列开始匹配使用的规则,来确实查询是否命中到索引。

例如现在存在表users,有(name, age, sex) 字段的联合索引

# 下面两个查询肯定是命中了索引的,(mysql优化器已经调整了第二个sql的查询顺序)
SELECT * FROM users WHERE name='test' AND age=18 AND sex=1;
SELECT * FROM users WHERE age=18 AND name='test' AND sex=1;

# 下面查询name使用了索引,name之后的不会使用索引,因为存在断点age
SELECT * FROM users WHERE name='test' AND sex=1;
# 可以修改一下查询条件,因为我们都知道age >= 0,所以可以这么写
SELECT * FROM users WHERE name='test' AND age>=0 AND sex=1;


# 关于排序有没用到索引,原理和条件查询一样:
# 下面这个排序使用了联合索引,因为name已确定,所以后面的age已经排好序了
SELECT * FROM users WHERE name='test' ORDER BY age;

# 相反下面这个排序没有使用到联合索引,因为存在断点age
SELECT * FROM users WHERE name='test' ORDER BY sex;

针对InnoDB,尽量使用聚簇索引

聚簇索引 = 索引 + 数据行

如果使用聚簇索引,可以直接从索引中获取字段信息,不然使用二级索引查询,本身二级索引不包含需要的字段,则需要再查询一次索引。

尽量让查询的字段覆盖索引

意思就是,控制查询的返回值,让匹配的索引值包含查询需要的字段,例如下面的查询

SELECT name, age FROM users WHERE name="test";

无法使用覆盖索引的情况:

  • 没有任何索引能够覆盖
  • 在索引中执行LIKE操作,最左匹配例外

前缀压缩索引

MyISAM使用的一种技术,默认只压缩字符串,也可以压缩整数。

例如,一个索引块中第一个值是“app”,第二个值是“apple”,那么第二个值的前缀压缩后的结果是“3,le"。这么做的好处是减少索引大小,让更多的索引读入内存,提升访问速度。缺点就是无法使用二分查找,倒序扫描也很吃力。所以,对于CPU密集型的应用,索引查找要慢很多。

重复索引和冗余索引

重复索引指在相同列上按照相同的顺序创建的相同类型的索引

重复索引,应该避免,浪费储存空间。

冗余索引,可以理解为存在(A,B)索引,再创建的(A)索引就是冗余索引。是否使用冗余索引,还是扩展索引,看实际查询情况而定。

有时候需要满足多个查询,而创建冗余索引。有时候创建了冗余索引,查询时间也差不多,可以去掉冗余索引。

注意事项

  1. 索引不会包含有NULL值的列

    所以查询某个字段不等于NULL不会用到索引。

  2. 如果可以,尽量使用短索引

    部分字段字符串,例如前10位就能确定唯一,那么可以使用短索引,或者叫前缀索引,提高查询效率也降低磁盘空间和I/O操作。

  3. 索引列排序

    MySQL查询只使用一个索引,如果WHERE中用到索引,ORDER BY 不会用其他索引。

  4. LIKE操作

    LIKE操作尽量使用最左匹配,一般不鼓励使用

  5. 不要在列上进行运算

    导致索引失效,进行全表扫描,例如select * from users where YEAR(adddate)<2007; 改成 select * from users where adddate<‘2007-01-01’;

  6. 尽量不使用 NOT IN<>操作

建立索引原则

  1. 最左前缀匹配原则
  2. =in 可以乱序,优化器会调整
  3. 选择区分度高的列作为索引,区分度公式count(distinct col) / count(*),表示字段不重复比例,一般需要join字段,我们都需要0.1以上
  4. 索引列不能参与计算
  5. 尽量扩展索引,不要重建索引

总结

总的来说,设计索引主要在 WHEREORDER BY中使用,当然还有SELECT ... 用到,即既能满足条件查询,命中索引,排序也用到索引,甚至查询的字段也直接能从索引中获取,这个索引就很优秀了

三星系统

另外还有一个三星系统用于评价索引是否适合:

索引将相关的记录放到一起 索引中的数据顺序和查找中的排列顺序一致 索引中的列包含了查询中需要的全部列

参考

《高性能MySQL》