Mysql（一）索引类型和使用规则

troyqu
336 阅读11分钟

本文正在参加「技术专题19期 漫谈数据库技术」活动

Mysql索引

什么是索引

在了解Mysql索引之前，先来了解下什么是索引？ 索引1.png 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

以上是百度百科中对于索引的描述，其实简单来说索引是存储引擎用于快速找到记录的一种数据结构，索引对于查询性能来说非常关键，创建和使用好索引可以使查询效率有很大的提升，做到很好的查询效率，否则的话索引不合理或者没有索引，有可能会导致存储引擎在数据量大的情况下无法在我们可以接受的范围内返回查询结果，甚至严重可能会引起数据库服务的不可用。

那么为什么添加了索引就可以提高查询效率呢？举个例子来讲，我们要去仓库查找货物，如果仓库货物很多并且很凌乱那么我们要找到一件货物的时候，就要在整个仓库中来查找，可是如果我们将仓库中的货物，根据货物类型和货物上架时间进行分类之后，那么当我们需要查找一个获取的时候，那么我们就可以按照货架和时间来缩小我们的查找范围，这样我们就可以更快速的找到我想想要的货物。那么我们就可以将货架看作是索引，根据这种思路来理解我们可以将索引理解为通过缩小想要获取数据的查询范围，并且将原本没有规律或者无序的数据，变成有据可查的规律数据，从而提升了查找的效率。

需要注意一点的是：虽然索引可以提升查询效率，但是对于数据库和计算机而言，建立索引不是我们在仓库放货价，索引引起对应的IO操作，IO的开销是昂贵的，因此我们需要学习和了解索引，从而合理使用索引。

索引的数据类型

Mysql中用我们常用的InnoDB引擎来讲，索引的数据类型是B+树。至于为什么是B+树呢，我们要结合B+树的特点来看。

B+树特点：

  1. B+树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针；
  2. B+树的非叶子节点只存储索引不存储数据，这样可以简化索引所需要占用的磁盘，可以节省IO提高效率；
  3. B+树所有的叶子节点构成了一个含有数据的有序链表，这样在进行全表扫描的时候，只需要遍历所有叶子结点即可更利于全表扫描；

Mysql索引类型

mysql1.png

聚簇索引

聚簇索引：按照每个表的主键构造的索引，数据和索引主键保存在一起，数据按照主键顺序存储，这也就决定每个表只能有一个聚簇索引。一般表中的主键就是聚簇索引，通常都在创建表的过程中都指定了主键，因此很少会注意到我们去声明了聚簇索引，但其实我们已经在创建主键的过程中对表建造了聚簇索引。 索引1.png 聚簇索引的叶子结点存储的是所有主键和对应的数据。

非聚簇索引：除了聚簇索引之外的索引就是非聚簇索引。

二级索引

数据库通过二级索引查找到具体数据的过程。

按照二级索引查找数据的时候，先通过查询条件找到对应的二级索引，因为二级索引的特点保存的是对应记录的主键值，那么数据库在根据二级索引拿到对应的主键，再通过主键来查找到具体的数据。 索引2.png 二级索引的叶子节点存储的是二级索引和对应记录的主键索引的数据。

联合索引

其实联合索引理解起来也很容易，不管是主键还是二级索引我们都是建立在一个具体的列上，但是我们在实际的场景中，有时候我们的查询条件会由多个字段联合查询，因此不管是二级还是主键都不能很好的满足场景，那么如果可以使用几个字段联合起来构建索引的话这样查询起来效率可以提高，也不用创建过多索引。

联合索引:由多个字段构成的索引就是联合索引，联合索引由多个索引组成，我们要严格遵守索引列的创建顺序来使用索引（最左匹配原则），因为联合索引再查找数据的时候，先按照索引第一列排序，第一列相同在根据第二列排序。 索引3.png 联合索引的叶子结点存储的是联合索引和对应记录的主键索引的数据；

索引使用规则

索引可以帮助提升查询效率，但是使用索引也要按照索引的使用标准来进行，否则即使创建了索引，如果使用不当也会导致索引无法生效，这样不仅没有提升效率还会造成全表扫描，因此应该怎么使用索引也很重要。

索引创建规则

  • 索引不是越多越好，合理创建需要的索引一个表最好不要超过5个索引；
  • 单个索引字段数不要超过5个；
  • 创建联合索引的时候，列的顺序可以按照业务量区分度来排序，将区分度最高的列放到前面；
  • 字符串索引设置合理的长度(字符串列注意长度)；
  • 联合索引优于多列独立索引；
  • 索引覆盖（索引列包含要查询的结果字段）

索引使用规则

在了解到索引可以帮助做很多事情之后，我们会建立合理的索引来提升查询效率，但是还没有结束我们还要确保我们查询的语句中合理的用到了索引，如果我们建立了索引，但是在使用的时候没有合理用到索引，那么还是不能帮助我们很好的提高查询效率，其实我们换个角度考虑下，只要明白了索引失效的场景之后，那么我们只要避免掉这些场景，那我们的查询语句就不用在担心使用不到索引的情况了，以下就是索引失效的一些场景: image.png

关键字or的使用

or语句前后查询字段都是索引列的时候索引才会生效，如果没有同时使用索引或者当or左右查询字段只有一个是索引时该索引失效。

查询条件中包含隐式类型转换

字符串列mobile结果查询条件中忘记传‘’，例如：WHERE mobile = ‘11222’写成WHERE mobile = 11222导致索引失效

索引列包含计算/函数

例如：WHERE name -20 = x，可以写成WHERE name = x + 20

索引列使用了not，<>, !=

索引列使用了IS NULl/IS NOT NULL

索引列中出现NULL值

解决方案：在数据插入的时候可以考虑使用默认值替代NULL

数据范围影响（数据范围影响（数据库觉得使用全表扫描更快的时候）

描述：

  1. 索引区分度过低；
  2. 因为数据分布问题导致查询条件过滤出得记录超出索引范围； 场景：

例如age列有索引，记录在10～100之间有很多数据，进行查询时候条件为age>0，那么也会进行全表扫描

解决方案：

  1. 结合业务合理规划索引，让索引可以起到过滤数据的效果；
  2. 可以考虑分页，减少数据量更容易匹配索引；

字符串索引不支持%开头的模糊查询

场景：索引在字符串类型的列name上，使用%开始的模糊查询

where name like ‘%abb’: 查询条件以%开头不会使用索引；

where name like ‘ab%b’: 查询条件中ab可以匹配索引字符串，%后面的字符串无法使用索引；

最左匹配原则

联合索引(a,b,c)的场景下分别分析一下几种场景:

场景1:缺少中/列的查询条件（a=x and c=x）

这种场景下，我们按照联合索引的特点来分析，那么第一列a肯定可以命中，但是因为缺少第二列b那么没法确定第二列的顺序之后，即使我们有第三列c数据库也无法继续使用c列来进行顺序比较，因此在这种情况下，只有第一个列a可以被用到，因此我们不能说这种场景下索引完全失效，因为a列还是可以起到数据过滤和查找效果，但是实际又不是全部索引列都可以被使用，因此我们不能笼统的说索引无效或者有效，而是更应该严谨点说只有a列可以使用索引，c列不会使用索引。

场景2:缺少首列（b=x and c=x）

根据场景1的分析来看，因此第一列也缺少，那么直接导致首列无法比较，那么后面的列也就不会被比较，因此这种场景下会导致索引失效。

场景3:缺少最后一列（a=x and b=x）

根据场景1的情况来分析，缺少最后一列，那么a和b列还是可以按照顺序来进行比较，因为缺少c列导致最后一列无法被比较，所以在这种场景下a和b列会使用到索引，c列不会使用到索引。

场景4:and条件下列顺序打乱（a=x and b=x and c=x 和 b=x and c=x and a=x的区别）

如果都是and条件的情况下，where a=x and b=x and c=x和where b=x and c=x and a=x的场景都是可以使用到联合索引的，这种情况不是说最左匹配原则失效了，而是数据库引擎对查询条件进行了优化，使其可以满足最左匹配原则。

场景5:某列使用范围查询，后面的列不能使用索引（a>x and b=x and c=x）

这种情况下因此a使用了范围查询导致a列可以使用索引a后面的列无法使用索引，因此b和c也会失效。

查看索引区分度评估

在上面我们提到了创建索引最好在区分度较高的列上，否则即使正确使用了索引由于区分度较低，查询效率也不会很好。

在实际中我们可以按照以下计算方式来评估索引的区分度，一般情况下区分度越高越好，区分度高于70%一般认为是对于查询帮助较大，如果区分度小于30%那么对于查询帮助则较少。

单列索引查看区分度

SELECT COUNT(DISTINCT your_column) AS unique_count FROM your_table; 

SELECT COUNT(*) AS total_rows FROM your_table; -- 然后计算辨识度比例 

SELECT (unique_count / total_rows) * 100 AS distinct_percentage;

多列索引查看区分度

由于多列索引需要多列同时进行评估，因此无法在原表中进行评估，可以按照一下sql来进行预估。

CREATE TEMPORARY TABLE temp_table AS SELECT CONCAT(field1, '_', field2, '_', ...) AS combined_key FROM your_table; 

SELECT COUNT(DISTINCT combined_key) AS unique_combinations FROM temp_table;

SELECT COUNT(*) AS total_rows FROM your_table;

-- 计算辨识度
SELECT (unique_combinations / total_rows) * 100 AS distinct_percentage;

总结

Mysql索引可以帮助我们来提升查询效率，但是也是建立在合理利用索引的前提下，如果索引使用不合理那么也会导致索引无法被使用的情况，在创建和使用索引的时候要结合索引创建的创建规则和索引失效的场景来综合考量决定。

avatar
文章
阅读
粉丝