陈中正的网络日志

MySql 索引

详解索引原理 以及 索引应用实践。

1、索引原理 1

问题:什么数据结构?
答案:B+数

2、索引使用场景

  • where,order by ,group by
  • <,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)

3、单索引相关

3.1、普通索引和唯一索引

普通索引和唯一索引,只保留唯一索引即可。

因为如果在一个列上同时建唯一索引和普通索引的话,mysql会自动选择唯一索引。

3、组合索引相关

3.1 多个单列索引 vs 多列索引

多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。

3.2 最左前缀 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的顺序可以任意调整。

3.3 索引乱序 1

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

3.4 列的区分度 1

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

3.5 索引列不能参与计算 1

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

3.6 尽量的扩展索引,而非新建 1

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

3.7 组合索引顺序

多列索引中其实还有一个字段的先后顺序问题,一般是将区分度较高的放在前面,这样联合索引才更有效,例如:

select * from t_base_user where name="" and status=1;

像上述语句,如果建联合索引的话,
应该是:(name, status)
而不是:(status, name)

3.8 区分度与索引

答在区分度较小的字段上新建索引,基本无效,还会增加大量的索引文件,是得不偿失。

4、业务索引实践

4.1 自增主键 && 作为业务索引

  • 优点:
    • innodb 中性能较好[auto_incre_in_innodb]
  • 缺点
    • replaceinsert on duplicate时有坑[trouble_in_autoincr]
    • 分布式DB中存在问题,无法正确自增

4.2. UUID作为自增主键 && 业务索引

  • 优点
    • 分布式DB表现很好
  • 缺点
    • innodb性能较差

4.3 最佳实践

  • 一个自增id作为主键,保证innodb高效
  • 一个uuid作为业务索引,保证不睬replace的坑 和 后续分布式拓展

5、explain命令

参考:https://www.cnblogs.com/jalja/p/7670712.html

« Shadowsocks笔记 MyBatis读写分离类库 »