最全面的mysql索引知识大盘点

最新推荐文章于 2023-06-27 07:11:17 发布

布道

最新推荐文章于 2023-06-27 07:11:17 发布

阅读量1.2w

点赞数 5

分类专栏： oracle|sqlserver|mysql 文章标签： InnoDB 索引

本文链接：https://blog.csdn.net/alex_xfboy/article/details/82804104

版权

oracle|sqlserver|mysql 专栏收录该内容

29 篇文章 0 订阅

订阅专栏

在我们日常的工作中，索引可谓无处不在，如果不懂索引，就会给人一种盲人摸象的印象。下面主要以mysql为基准来系统的介绍。

1. 为何需要索引，索引是什么？

索引是对数据库表中一列或多列的值进行排序的一种结构。一个非常恰当的比喻就是书的目录页与书的正文内容之间的关系，为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

2. 有哪些分类?

2.1. B+数索引

主键索引：数据列不允许重复，不允许为NULL.一个表只能有一个主键。
唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引，如：ALTER TABLE table_name ADD UNIQUE (column);
唯一组合索引：顾名思义多列唯一，如：ALTER TABLE table_name ADD UNIQUE (column1,column2);
普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。如：ALTER TABLE table_name ADD INDEX index_name (column);
前缀索引：超过20 个长度的字符串，请考虑创建前缀索引，因为较小的索引涉及的磁盘 I/O 较少，更为重要的是，对
于较短的键值，索引高速缓存中的块能容纳更多的键值。前缀索引的长度，一般略大于本列的平均长度，
例如 colname的平均长度为 23，那么前缀取稍大一点（如24）就可以，或者根据选择性和实际访问量。前
缀索引兼顾索引大小和查询速度，但是其缺点是不能用于 ORDER BY 和GROUP BY操作，也不能用于覆盖索引。如：alter table tabname add key(colname(24));
组合索引：ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);

注：如果你对B+不了解，在另一章中专门介绍

2.2.hash索引

InnoDB在内部利用哈希索引来实现其自适应哈希索引功能，我们干预不了。哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，而节点逐级查找带来的问题就是多次的IO访问，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

通过SHOW VARIABLES LIKE '%ap%hash_index';查看是否支持，具体情况看官方支持，MySQL5.6中的体系结构更改使其比早期版本更适合禁用自适应哈希索引功能。

适用场景：对于一些比较长的的字段，比如存储 url 的字段，这时可以使用 crc32做哈希，再在这个 hash值上建索引，性能会大幅提升。如当字段url=http://www.baidu.com时，这时可以取crc32(“http://www.baidu.com”)=110365867(建索引而非url上直接建)。

2.3. 全文索引

全文索引是搜索引擎使用的一种关键技术。如：ALTER TABLE table_name ADD FULLTEXT (column);

2.4 B+树索引和哈希索引的区别

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据。
如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索。
哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）。
哈希索引也不支持多列联合索引的最左匹配规则
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

3. 适合及不适合创建的索引？

适合的场景：

where后面使用频繁的搜索列，但不一定是所有where 后面的列都要建索引；
虽然数据库上没有维护主外关系，但如果该字段相当于某张表的外键，一般要在该字段加索引；
两表join时，所匹配 on和where 的字段应建立合适的索引；
group by, order by 后面字段，要考虑建索引，但不一定是全部建索引；
索引要建在选择性高，重复记录少的字段上（当索引列有大量数据重复时，SQL优化可能不会去利用索引）；
索引尽量建在小的字段上，如果碰到大的字段(超过20字符)，考虑使用前缀索引。
尽量避免NULL：应该指定列为NOT NULL，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。

不适合的场景：

唯一性太差的字段不适合建立索引，如标识性字段 is_delete，或者表示状态类型等一类的字段；
只出现在select中，不会出现在 where 或者order by 条件中的字段不要建索引；
一般不要出现，同一字段既是单字段索引，又出现在复合索引中；
同一张表，不要建过多复合索引；
'%xxx'或者'%xxx%'一类的模糊查询，不要创建普通索引，可以考虑建全文索引，但'xxx%'可以用到索引；
不要过度索引，索引会影响 DML(insert、delete、update)的性能，对于 DML 操作频繁的表，一般一张表
不要超过6个索引，具体还需视表的大小和业务情况适当调整；
尽量避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序。

4. 最左匹配原则

更多参阅另一篇认识mysql组合索引的最左匹配原则

6. 如何分析及优化SQL语句的性能问题

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划。

ID:

id相同执行顺序由上至下。
id不同，id值越大优先级越高，越先被执行。
id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中

select_type:

SIMPLE: 不包含任何子查询或union等查询

PRIMARY: 包含子查询最外层查询就显示为 PRIMARY

SUBQUERY: 在select或 where字句中包含的查询

DERIVED: from字句中包含的查询

UNION: 出现在union后的查询语句中

UNION RESULT: 从UNION中获取结果集

table: 查询的数据表

partitions: 执行计划表分区、表创建的时候可以指定通过那个列进行表分区

type:

CREATE TABLE `employee` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `no` VARCHAR(10) NOT NULL,
  `name` VARCHAR(20) NOT NULL,
  `position` VARCHAR(20) NOT NULL,
  `age` VARCHAR(2) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

ALL	这便是所谓的“全表扫描”,非常的耗时而且低效。
index	另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据）但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。 mysql> explain select * from employee order by no;//全表 +----+-------------+----------+------+---------------+------+---------+------+------+----------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +----+-------------+----------+------+---------------+------+---------+------+------+----------------+ \| 1 \| SIMPLE \| employee \| ALL \| NULL \| NULL \| NULL \| NULL \| 5 \| Using filesort \| +----+-------------+----------+------+---------------+------+---------+------+------+----------------+ mysql> explain select * from employee order by id ;//按索引列扫描 +----+-------------+----------+-------+---------------+---------+---------+------+------+-------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +----+-------------+----------+-------+---------------+---------+---------+------+------+-------+ \| 1 \| SIMPLE \| employee \| index \| NULL \| PRIMARY \| 4 \| NULL \| 5 \| NULL \| mysql> explain select id from employee ; //extra列中的值为‘Using index’，那么称这种情况为索引覆盖； +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+ \| 1 \| SIMPLE \| employee \| index \| NULL \| PRIMARY \| 4 \| NULL \| 5 \| Using index \|
range	有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及'>','<'外，in和or也是索引范围扫描。
ref	查找条件列使用非唯一性索引或者唯一索引的前缀扫描。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。 mysql> alter table employee add key I_EMPLOYEE_NAME(`name`); mysql> EXPLAIN SELECT * FROM employee WHERE NAME= '张三'; +----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+ \| id \| select_type \| table \| type \| possible_keys \| key \| key_len \| ref \| rows \| Extra \| +----+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+ \| 1 \| SIMPLE \| employee \| ref \| I_EMPLOYEE_NAM \| I_EMPLOYEE_NAM \| 62 \| const \| 1 \| \|
eq_ref	ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。
ref_or_null	类似REF，只是搜索条件包括：连接字段的值可以为NULL的情况，比如 where col = 2 or col is null
fulltext	使用全文索引
index_subquery	在子查询中使用基于除唯一索引之外的索引进行扫描
unique_subquery	在子查询中基于唯一索引进行扫描( eq_ref)

possible_keys: 可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。
key: 实际使用的索引，若没有使用索引，显示为NULL
key_length: 索引长度
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows: 返回估算的结果集数目，并不是一个准确的值。
extra: 见覆盖索引

7. 常见优化

理论上sql优化，应尽量避免全表扫描、文件排序等，尤其是要警惕以下用法（会导致索引失效）：

where子句中使用!=或<>操作符
where 进行 null 值判断（where num is null），它的问题已有补充mysql中Null 与空值的区别
where 子句中使用 or 来连接条件，可以使用union all方案代替
where 中in 和 not in 也要慎用，能用 between 就不要用 in
where like 不能前置百分号
where 子句中使用参数（where num=@num）
where 子句中对字段进行表达式操作（where num/2=100）
where子句中对字段进行函数操作（where substring(name,1,3)=’abc’）
order by 涉及的列无索引

当然了，上面都是属于比较初中级，基于Mysql对查询SQL的自动优化来提高性能的。但mysql还提供了更高级的东西，打破这些，主要有：

我们将两个或多个表连接起来进行查询时，我们并不用关心MySQL先连哪个表，后连哪个表。而这一切都是由MySQL内部通过一系列的计算、评估，最后得出的一个连接顺序决定的。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就需要人为地干预连接的顺序，直接使用STRAIGHT_JOIN关键字。通常是用小表驱动大表

         限制使用索引的范围
        有时我们在数据表里建立了很多索引，当MySQL对索引进行选择时，我们希望MySQL只考虑几个索引，而不是全部的索引，就需要用到USE INDEX对查询语句进行设置。
        限制不使用索引的范围
      如果我们要考虑的索引很多，而不被使用的索引又很少时，可以使用IGNORE INDEX进行反向选取（选择不被考虑的索引）。
          强迫使用某一个索引
         有时我们希望MySQL必须要使用某一个索引(由于 MySQL在查询时只能使用一个索引，因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能。

         当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁(这样其它的SQL语句就可以对这些记录进行查询了)，并且可以长时间地为客户端提供大记录集。
         SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …
         和SQL_BUFFER_RESULT.选项类似的还有SQL_BIG_RESULT，这个选项一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。
        SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1

STRAIGHT_JOIN:强制连接顺序
干预索引使用
使用临时表

也可以通过查询缓冲提高查询速度，它已超出我们本章节谈论的范围，暂时不深入研究了。

8. in 和exists 优化专题

mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

最后，不总结了，上面的基本上都是用法的，后面会再补充一遍原理《mysql索引底层原理分析》

布道

关注

5
点赞
踩
22

收藏

觉得还不错? 一键收藏
2
评论
最全面的mysql索引知识大盘点

在我们日常的工作中，索引可谓无处不在，如果不懂索引，就会给人一种盲人摸象的印象。下面主要以mysql为基准来系统的介绍。目录1. 为何需要索引，索引是什么？2. 有哪些分类?2.1. B+数索引2.2.hash索引2.3. 全文索引2.4B+树索引和哈希索引的区别3. 适合及不适合创建的索引？4.最左匹配原则6. 如何分析及优化SQL语句的性能问题7...
复制链接

扫一扫