mysql索引使用

索引的优缺点

• 优点

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数据库入门到大牛