索引的优缺点
- 优点
1、大大加快数据检索速度
2、将随机IO变成顺序IO(因为b+树的叶子是连接在一起的)
3、加速表与表之间的连接
- 缺点
1、从空间角度分析,建立索引需要占用物理空间
2、从时间角度分析,创建维护索引要花费时间
索引的设计原则
1、最适合索引的列是where后面的列或者连接句子中指定的列,而不是select中的列
2、索引列的基数越大、区分度越高,索引效果越好
3、尽量使用短索引,长数据使用前缀索引,因为磁盘IO较少,并且索引高速缓存中的块可以容纳更多的键值,使得查询速度更快
4、将区分度高的索引放在前面
索引的常用设计场景【11种场景】
索引失效的场景
-
字段的数值具有唯一性约束
eg. 学生的学号具有唯一性,索引本身就可以起到约束性作用,例如唯一索引、主键索引。
业务上具有唯一性的字段,即便是组合字段,也必须做成唯一索引 -
频繁作为where查询条件的字段
-
经常group by和order by中的列
索引就是让数据按照某种顺序进行存储或者检索,因为当使用这两种情况的时候,就需要对分组或者排序的字段进行索引。如果待排序的列有多个,可以在这些列上建立组合索引。
tips:当单独使用group by或者order by,就针对关键字后面出现的列建立单个索引或者联合索引。当同时使用时,建立索引的顺序要把group by中的放前面,order by的列放置在后面,因为在select查询时,先进行group by, 再对数据进行order by操作。
- update、delete的where条件的列
这个的原理是因为,在更新或删除操作时候,需要先查询检索出来这个列,然后执行更新操作。如果更新的时候,更新的是非索引字段,提升效率更加明显,因为非索引字段的更新不需要对索引进行重新维护。
- distinct中的列
这是因为索引会对数据按照某种顺序进行排序,所以会加速去重的速度
- 多表join连接操作时,创建索引的注意事项
连表数量不要超过3张,因为每一张表都是一次循环嵌套
对where条件创建索引
对于连接的字段创建条件,并且该字段对多张表的数据类型必须一致,类型不一致,可能会导致类型转换,类型转换底层其实使用了函数,而使用了函数会造成索引失效。
- 使用列的类型小的创建索引
小范围的数据类型,可以存储更多的数据,加快访问查询速度
- 使用字符串前缀创建索引
整个字符串作为索引需要很大的存储空间,在做条件比较的时候要占用更多的时间。所以,可以通过截取字符串的前一部分内容建立所以,即前缀索引。
前缀索引建立需要考量索引长度。长度的选择度计算:
count(distinct left(列名, 索引长度))/count().* [计算结果约接近1越好,说明区分度越高,索引效果越好,一般对字符串类型的数据长度为20的索引,区分度会高达90%以上。
- 区分度高(散列性高)的列适合作为索引
列的基数指某一列中不重复数据的个数,列的基数越大,该列的区分度就越高。可以使用如下公式计算区分度,越接近1越好,一般超过33%就是比较高效的索引了。
区分度公式:select count(distinct a)/count() from t*
- 使用频繁的列放到联合索引的左侧
由于最左前缀原则,可以增加联合索引的使用率
- 在多个字段都要建立索引的情况下,联合索引优于单值索引
借鉴:B站宋红康mysql数据库入门到大牛