MYSQL索引学习

总结:

索引的原则:

1. 最左前缀匹配
2. 区分度高的列作为索引,索引并非建立的越多越好,适用就好
3. 索引的字段尽量的小
4. 尽量扩展索引,建立联合索引,不是建多个索引
5. 少用select *
6. not in,not exists,<> != 不等于会不触发索引
小于 > 大于 <= >= 这个根据实际查询数据来判断,如果全盘扫描速度比索引速度要快则不走索引 。

建索引的几大原则

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) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

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

https://tech.meituan.com/2014/06/30/mysql-index.html

索引总结

索引列不允许为空

索引列的数据长度能少则少

索引一定不是越多越好,越全越好,一定是建合适的

匹配列前缀可用到索引 like 9999%,用不到索引的例子 like %9999%、like %9999

where 条件中 not in 和 <> 操作无法使用索引

匹配范围值,order by 也可用到索引

多用指定列查询,只返回自己想到的数据列,少用 select *

联合索引中如果不是按照索引最左列开始查找,无法使用索引

联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引

联合索引中如果查询中有某个列的范围查询,则其右边的所有列都无法使用索引

索引优化的细节问题

in not in 触发索引的机制有什么不同

<> != mysql的不等于?

mysql sql 中如果使用了 not in , not exists , (<> 不等于 !=) 这些不走 < 小于 > 大于 <= >= 这个根据实际查询数据来判断,如果全盘扫描速度比索引速度要快则不走索引 。

in和or 都会走索引,如果没走可能是全盘扫描又比用索引快导致的。

参考资料https://www.cnblogs.com/liaokaichang/p/7516080.html

最左前缀匹配原则

非常重要的原则,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的顺序可以任意调整。

问题:c > 3这个索引会停止么 是,停止的意思并不是不用c索引,大于小于在返回结果是否超过表一定数量(有说法是20%),如果超过了那么可能不用c索引,如果不超过,就用到索引。

问题:explain是什么神器

explain select * from users where name like ‘j%’; 可以帮助我们看到具体的mysql查询是否触发索引等详细运行过程

https://www.cnblogs.com/yinjw/p/11829284.html


This entry was posted in 数据库 and tagged , , . Bookmark the permalink.
月小升QQ 2651044202, 技术交流QQ群 178491360
首发地址:月小升博客https://java-er.com/blog/mysql-index-opt/
无特殊说明,文章均为月小升原创,欢迎转载,转载请注明本文地址,谢谢
您的评论是我写作的动力.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*