MySQL添加索引原则

简介

MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库。一般的WEB系统,通常是读大于写,所以一般插入和更新操作很少出现性能问题,出现问题的往往是一些复杂的查询操作,高并发访问的系统,往往经常会被一些slow query拖垮整个系统,这不是危言耸听,我所在的公司项目中就出现过多次这种事故,显然查询语句的优化非常重要。下面就来介绍下查询优化中常用的通过索引的优化,重点介绍建索引原则。

索引

索引的目的在于提高查询效率,例如一本字典,你要查一个单词”word”,你一般是通过目录,找到W,再找到o,然后是r,再然后是d,找到word单词所在页码之后,再翻到那一页查看word。而如果没有索引,你需要翻遍整本字典去查找word这个单词,效率非常低。

索引的结构

MySQL的InnoDB引擎默认的索引结构类型是B+Tree。InnoDB存储引擎的数据存储结构是聚簇索引,其主键索引树的叶子节点存放着数据,而其他索引则是通过二级索引的方式,另开一个索引树,叶子节点存放主键。查找的时候,通过二级索引找到主键,再通过主键查找主键索引树找到需要的数据。

建索引的几大原则

1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。

3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录

4.索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2015-08-03’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2015-08-03’);

5.尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可

举例

我们有一张用户表,有几个字段 id、name、age、sex、create_time,我们经常会用到的查询语句是:

select * from user where age=18 and name='张三' and sex='男';

根据最左匹配原则,我们应该加的索引是(name, age, sex),但其实由于age和sex列的区分度不高,所以可以根据具体情况,这两列不加也可以,只加一个(name)的索引。

查询优化 - explain

explain命令可以帮助我们优化分析SQL语句所使用到的索引,以及要扫描多少行,具体用法和字段含义可以参考官网explain-output,一般rows是核心指标,绝大部分rows小的语句执行一定很快。所以优化语句基本上都是在优化rows。

最后推荐两本书:

  • 《高性能MySQL》
  • 《MySQL技术内幕 InnoDB存储引擎》