MySQL 索引

已于 2024-02-27 18:14:14 修改
阅读量1.9k 收藏 1
点赞数 1
分类专栏： java 文章标签： java
于 2022-04-27 22:56:58 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yy139926/article/details/124443946
版权

文章目录

前言

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

1、索引是什么

  • 索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。
  • 索引一般存储在磁盘的文件中，它是占用物理空间的。

2、索引类型有哪些

在这里插入图片描述

3、索引的优缺点

优点 ：

  • 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量），这也是创建索引的最主要的原因。
  • 通过创建唯一性索引或者主键索引，可以保证数据库表中每一行数据的唯一性。

缺点 ：

  • 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
  • 索引需要使用物理文件存储，也会耗费一定空间。

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

4、索引的基本语法

  1. 添加主键索引

ALTER TABLE table_name ADD PRIMARY KEY (column)

  1. 添加唯一索引

ALTER TABLE table_name ADD UNIQUE (column)

  1. 添加全文索引

ALTER TABLE table_name ADD FULLTEXT (column)

  1. 添加普通索引

ALTER TABLE table_name ADD INDEX index_name (column )

  1. 添加组合索引

ALTER TABLE table_name ADD INDEX index_name (column1, column2, column3)

  1. 删除索引

drop index indexname on table_name

  1. 查看索引

show index from table_name

  1. 导出表的索引
SELECT
	CONCAT(
	'ALTER TABLE ',
	TABLE_NAME,
	' ADD ',
IF
	(
	NON_UNIQUE = 1,
CASE
	UPPER( INDEX_TYPE ) 
	WHEN 'FULLTEXT' THEN
	'FULLTEXT INDEX ' 
	WHEN 'SPATIAL' THEN
	'SPATIAL INDEX ' ELSE CONCAT( 'INDEX ', INDEX_NAME ) 
	END,
IF
	( UPPER( INDEX_NAME ) = 'PRIMARY', CONCAT( 'PRIMARY KEY' ), CONCAT( 'UNIQUE INDEX ', INDEX_NAME ) ) 
	),
	'(',
	GROUP_CONCAT( DISTINCT COLUMN_NAME ORDER BY SEQ_IN_INDEX ASC SEPARATOR ',' ),
	');' 
) AS 'index' 
FROM
	information_schema.STATISTICS 
WHERE
	TABLE_SCHEMA = '数据库' 
	AND TABLE_NAME IN ( '表名' ) 
GROUP BY
	TABLE_NAME,
	INDEX_NAME 
ORDER BY
	TABLE_NAME ASC,
	INDEX_NAME ASC

拓展：全文索引

全文索引是将存储于数据库中的整本书或整篇文章中任意内容信息查找出来的技术。从InnoDB1.2.x版本开始便开始支持全文索引。

全文索引通常用倒排索引来实现，倒排索引和BTree一样，是一种索引结构，它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射，其拥有两种表现形式：

  • inverted file idnex：{单词，单词文档所在ID}
  • full inverted index：{单词，（单词文档所在ID，具体文档中的位置）}

例如有如下表 A：

DocumentIdText
1red, blue, white
2black, yellow
3yellow, orange
4pink, red, blue,

用 inverted file idnex 方式存储内容如下所示：

DocumentIdText
red1, 4
blue1, 4
white1
black2
yellow2, 3
orange3
pink4

用 full inverted index 方式存储内容如下所示：

DocumentIdText
red（1, 1）, （4, 2）
blue（1, 2）, （4, 3）
white（1, 3）
black（2, 1）
yellow（2, 2）, （3, 1）
orange（3, 2）
pink（4, 1）

相比之下，full inverted index会占用更多空间，但能更好的定位数据，并扩充其他一些搜索特性。InnoDB全文检索采用full inverted index方式。

5、索引的底层数据结构选择B+树

下面是一张数据库的表，有两列数据，分别是Col1和Col2，存储的都是数字
在这里插入图片描述
我们来查询一下数字为 91 的数据，mysql语句如下：

select * from yang where Col2 = 91

普通模式下，查询的规则是从上往下查询：34 77 5 91（查询到 91 ）

如果数据表很大，查询的数据又是在表尾的话，那么需要花费非常多的计算时间。索引是一种能提高数据库查询效率的数据结构，查询庞大数据的情况下，极大的提高了效率。

5.1、为什么不选择二叉树作为索引结构

  • 二叉树特点
    • 每个结点最多两个子树，分别称为左子树和右子树。
    • 左子节点的值小于当前节点的值，当前节点值小于右子节点值。
    • 顶端的节点称为根节点，没有子节点的节点值称为叶子节点。

插入上表七个数据 34 77 5 91 22 89 23
在这里插入图片描述
如果我们查询 91 的话，查询规则是 34 77 91（查询到 91 ）

相比于原始的循序查询来说，效率得到了极大的提高，减少很多不必要计算。
但是普通的二叉查找树有个致命缺点：极端情况下会退化为线性链表，二叉树变的极度不平衡，检索性能急剧下降。

例如依次从小到大插入这七个数据 5 22 23 34 77 89 91
在这里插入图片描述
如果我们再查询 91 的话，查询规则是 5 22 23 34 77 89 91（查询到 91 ）

这样查询就是顺着往下查就和原始查询没差别了，所以二叉查找树只在一定场景下有用。

5.2、为什么不选择平衡二叉树作为索引结构

二叉查找树存在不平衡问题，因此提出通过树节点的自动旋转和调整，让二叉树始终保持基本平衡的状态，就能保持二叉查找树的最佳查找性能了。基于这种思路的自调整平衡状态的二叉树有 AVL 树和红黑树。

1、红黑树

红黑树的性质

  • 节点是红色或黑色。
  • 根节点是黑色。
  • 所有叶子节点都是黑色的空节点。(叶子节点是NIL节点或NULL节点)。
  • 每个红色节点的两个子节点都是黑色节点。(从每个叶子节点到根的所有路径上不能有两个连续的红色节点)。
  • 从任一节点到其每个叶子节点的所有路径都包含相同数目的黑色节点。

红黑树变色

当前节点红色，父节点是红色且当前节点的叔叔节点也是红色

  1. 把父节点设置为黑色
  2. 把叔叔节点也设置为黑色
  3. 把爷爷节点设置为红色

红黑树左旋

当前节点是红色，父节点是红色，叔节点是黑色，且当前节点是右子树，
将父节点左旋，本节点向上，本节点的左子树，连接上左旋下来的父节点的右侧。

在这里插入图片描述
红黑树右旋

当前节点是红色，父节点是红色，叔节点是黑色，且当前节点是左子树，
将父节点右旋，本节点向上，本节点的右子树，连接上右旋下来的父节点的左侧。
在这里插入图片描述

同样依次从小到大插入七条数据 5 22 23 34 77 89 91
在这里插入图片描述
如果我们再查询 91 的话，查询规则是 22 34 89 91（查询到 91 ）

可以看到，红黑树不会退化为线性链表，查询次数明显减少，效率上得到显著的提升。但是依旧还存在问题。红黑树尽管没有二叉树倾斜的厉害，但是倾斜的幅度也很大，如果节点数据更多那么倾斜幅度更大，倾斜幅度带来的问题的查找的深度，对于查找性能来说是巨大的消耗。

2、AVL 树

相对于了红黑树来说，AVL树是严格意义上的绝对平衡二叉树

同样依次从小到大插入数据 5 22 23 34 77 89 91
在这里插入图片描述
如果我们再查询 91 的话，查询规则是 34 89 91（查询到 91 ）

通过对比发现，不会退化为线性链表而且不会严重倾斜，形态上保持了平衡，叶子节点层级减少，查询效率提升，大量顺序插入不会导致查询性能的降低，从根本上解决了红黑树遇到的问题。

但是平衡二叉树插入或者更新，需要左旋右旋维持平衡，维护代价大，如果数量多的话，树的高度会很高。因为数据是存在磁盘的，以它作为索引结构，每次从磁盘读取一个节点，操作IO的次数算为一次，也就是说树高度决定IO次数。

数据库查询数据的瓶颈在于磁盘 IO（数据的读写），如果使用的是 AVL 树，我们每一个树节点只存储了一个数据，我们一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，那比如查询 91 这个数据我们就要进行磁盘 IO 三次，这是多么消耗时间的。所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。

5.3、为什么不选择 B 树作为索引结构

磁盘 IO 有个有个特点，就是从磁盘读取 1B 数据和 1KB 数据所消耗的时间是基本一样的，我们就可以根据这个思路，我们可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就多加载点数据到内存，这就是 B 树，B+树的的设计原理。

假设每个节点可以存储三个值（不代表必须存三个），一个节点如果超过三个 key 就会自动分裂。比如下面这个存储了 7 个数据 B 树

同样依次从小到大插入数据 5 22 23 34 77 89 91
在这里插入图片描述
如果再查询 91 这个数据只需要查询两个节点就可以知道 91 这数据的具体位置，也就是二次磁盘 IO 就可以查询到指定数据，优于 AVL 树。

B树相对于平衡二叉树，就可以存储更多的数据，高度更低。但是最后为甚选择B+树呢？因为B+树是B树的升级版
在这里插入图片描述

B树的存储结构：每个节点可存储多条数据，且存储了对应的键值，指针和数据。

B+树的存储结构：上层节点只存储指针和键值，最底层叶子节点存储键值和数据，并且叶子节点之间是链式环结构。与B树相比，上层节点可以存储更多的数据，且叶子节点的范围查询或分页查询效率更高。

那么，三层的B+树能存储多少条数据？

在Innodb存储引擎里面，最小存储单元是页，而一个页的大小默认是16KB。 一个节点（叶子节点或非叶子节点）的大小就是一页。

① 非叶子节点：

（1）假设主键类型为bigint，占用8Byte，指针可以设置为占用6Byte，总共14Byte。这样就可以算出一个非叶子节点大概可以存放16KByte/14Byte=1170个“主键+指针”的组合。
（2）假设主键类型为int，占用4Byte，指针可以设置为占用6Byte，总共10Byte。这样就可以算出一个非叶子节点大概可以存放16KByte/10Byte≈1600个“主键+指针”的组合。

② 叶子节点

在B+树中，真正的数据是只存储在叶子节点中的。
这里我们假设我们的一行数据大小是1K，那么我们一个叶子节点就可以存16KByte/10Byte=16条（行）数据。

由此，可以推算出公式：

两层总数 = 非叶子节点(根) * 叶子节点。
三层总数 = 非叶子节点(根) * 非叶子节点 * 叶子节点。

主键为bigint（约2000w）：

2层B+树的话：可以存放1170个*16条=18720条（行）数据。
3层B+树的话：可以存放1170个*1170个*16条=21902400条（行）数据。

主键为int（约4000w）：

2层B+树的话：可以存放1600个*16条=25600条（行）数据。
3层B+树的话：可以存放1600个*1600个*16条=40960000条（行）数据。
所以三层B+树也就差不多2000w条或4000w条数据。

5.4、为什么不选择哈希作为索引结构

  • Hash 索引不能进行范围查询，而 B+ 树可以。这是因为 Hash 索引指向的数据是无序的，而 B+ 树的叶子节点是个有序的链表。
  • Hash 索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用），而 B+ 树可以。对于联合索引来说，Hash 索引在计算
    Hash 值的时候是将索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算 Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
  • Hash 索引不支持 ORDER BY 排序，因为 Hash 索引指向的数据是无序的，因此无法起到排序优化的作用，而 B+树索引数据是有序的，可以起到对该字段 ORDER BY 排序优化的作用。
  • 也无法用 Hash 索引进行模糊查询，而 B+ 树使用 LIKE 进行模糊查询的时候，LIKE 后模糊查询的话就可以起到优化作用。
  • 对于等值查询来说，通常 Hash 索引的效率更高，但是，索引列的重复值如果很多，效率就会降低。这是因为遇到 Hash冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

综上，Hash 索引存在着很多限制，相比之下在数据库中 B+ 树索引的使用面会更广，不过也有一些场景采用 Hash 索引效率更高，比如在键值型（Key-Value）数据库中，Redis 存储的核心就是 Hash 表。

6、聚簇索引与非聚簇索引

6.1、聚簇索引（聚集索引）

聚簇索引即索引结构和数据一起存放的索引，并不是一种单独的索引类型。InnoDB 中的主键索引就属于聚簇索引。

聚簇索引的优缺点

优点 ：

  • 查询速度非常快 ：聚簇索引的查询速度非常的快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。相比于非聚簇索引， 聚簇索引少了一次读取数据的 IO 操作。
  • 对排序查找和范围查找优化 ：聚簇索引对于主键的排序查找和范围查找速度非常快。

缺点 ：

  • 依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。
  • 更新代价大 ： 如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且聚簇索引的叶子节点还存放着数据，修改代价肯定是较大的，所以对于主键索引来说，主键一般都是不可被修改的。

6.2、非聚簇索引（非聚集索引）

非聚簇索引即索引结构和数据分开存放的索引，并不是一种单独的索引类型。二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

非聚簇索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键，根据主键再回表查数据。

非聚簇索引的优缺点

优点 ：

  • 更新代价比聚簇索引要小 。非聚簇索引的更新代价就没有聚簇索引那么大了，非聚簇索引的叶子节点是不存放数据的。

缺点 ：

  • 依赖于有序的数据 ：跟聚簇索引一样，非聚簇索引也依赖于有序的数据。
  • 可能会二次查询(回表) ：这应该是非聚簇索引最大的缺点了。 当查到索引对应的指针或主键后，可能还需要根据指针或主键再到数据文件或表中查询。

6.3、非聚簇索引一定回表查询吗

非聚簇索引不一定回表查询。

试想一种情况，用户准备使用 SQL 查询用户名，而用户名字段正好建立了索引。

 SELECT name FROM table WHERE name='yang';

那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。

查到返回就行了。这种情况就称之为覆盖索引了。

7、什么是回表

如果执行以下的查询SQL，需要执行几次的树搜索操作？可以画下对应的索引结构图（id为主键）
在这里插入图片描述

select * from yang where age = 48;--age非主键，先进行非聚集索引再聚集索引
  1. 先画出idx_age索引的索引结构图，大概如下：
    在这里插入图片描述
  2. 再画出id主键索引，如下：

在这里插入图片描述

因此，这条 SQL 查询语句执行大概流程就是:

  1. 搜索idx_age索引树，将磁盘块1加载到内存，由于48大于43搜索右子树，到磁盘块4。
  2. 将磁盘块4加载到内存中，在内存继续遍历，找到age等于48的记录，取得id等于200。
  3. 拿到id等于200后，回到id主键索引树。
  4. 搜索id主键索引树，将磁盘块1加载内存，由于200小于300搜索左子树，到磁盘块2。
  5. 将磁盘块2加载到内存中，在内存继续遍历，找到id等于200，拿R2这一行的数据。

非聚集索引拿到主键再回到主键索引查询的过程，就叫做回表。

例如：在idx_age索引树找到主键id后，回到id主键索引搜索的过程，就称为回表。

8、覆盖索引

覆盖索引：在查询的数据列里面，不需要回表去查，直接从索引列就能取到想要的结果。换句话说，你SQL用到的索引列数据，覆盖了查询结果的列，就算上覆盖索引了。

例如（覆盖索引）

select id，age from yang where age = 48;--age非聚集索引上带有主键值，不需要回表

例如（覆盖索引）

select age from yang where age = 48;--SQL用到的索引列数据，覆盖了查询结果的列，不需要回表

回到idx_age索引树，你可以发现查询选项id和age都在叶子节点上了。因此，可以直接提供查询结果啦，根本就不需要再回表了。

例如（覆盖索引失效）

select id，age，sex from yang where age = 48;--sex需要回表查询出来

9、联合索引

使用表中的多个字段创建索引，就是 联合索引，也叫 组合索引 或 复合索引。

以 score 和 name 两个字段建立联合索引：

ALTER TABLE `cus_order` ADD INDEX id_score_name(score, name);

联合索引的好处

  • 减少开销。每多一个索引，都会增加写操作的开销和磁盘空间的开销。（当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引）
  • 效率高。索引列越多，通过索引筛选出的数据越少。

10、最左前缀匹配原则

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

CREATE INDEX index_age_name_sex ON yang (AGE,NAME,SEX)

如：（AGE， NAME，SEX）是一个联合索引，支持（AGE）（AGE， NAME）（AGE， NAME，SEX）查找。

EXPLAIN SELECT * FROM YANG WHERE AGE = 43--索引生效
EXPLAIN SELECT * FROM YANG WHERE AGE = 43 AND NAME = '1'--索引生效
EXPLAIN SELECT * FROM YANG WHERE AGE = 46 AND NAME = '1' AND SEX = 0--索引生效

那么（AGE，SEX）索引会不会生效呢，也是会生效的但是只有AGE走了索引

EXPLAIN SELECT * FROM YANG WHERE AGE = 43 AND SEX= 0--索引生效

那么（SEX，AGE，NAME）索引会不会生效呢，也是会生效的，这个属于匹配查询（where子句搜索条件顺序调换不影响索引使用，因为查询优化器会自动优化查询顺序 ），顺序可以颠倒

EXPLAIN SELECT * FROM YANG WHERE AGE = 43 AND NAME = '1' AND SEX = 0--索引生效
EXPLAIN SELECT * FROM YANG WHERE SEX = 0 AND NAME = '1' AND AGE = 43--索引生效
EXPLAIN SELECT * FROM YANG WHERE NAME = '1' AND SEX = 0 AND AGE = 43--索引生效

还有一种带有范围查询的时候，例如

select * from table where AGE = 1 and NAME > '2' and SEX = 0 这种类型的也只会有AGE与NAME走索引，SEX不会走

在这里插入图片描述
如图所示他们是按照a来进行排序，在a相等的情况下，才按b来排序。

因此，我们可以看到a是有序的1，1，2，2，3，3。而b是一种全局无序，局部相对有序状态! 什么意思呢？

从全局来看，b的值为1，2，1，4，1，2，是无序的，因此直接执行b = 2这种查询条件没有办法利用索引。

从局部来看，当a的值确定的时候，b是有序的。例如a = 1时，b值为1，2是有序的状态。当a=2时候，b的值为1,4也是有序状态。 因此，你执行a = 1 and b = 2是a，b字段能用到索引的。而你执行a > 1 and b = 2时，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b值不是有序的，因此b字段用不上索引。

综上所示，最左匹配原则，在遇到范围查询的时候，就会停止匹配。

所以根据联合索引的最左匹配原则，我们在构建联合索引的时候，要把区分度高的字段，放在最左侧。

MySQL一定是遵循最左前缀匹配的，这句话在以前是正确的，但是在MySQL 8.0出现了索引跳跃扫描。

11、索引下推

索引下推是 MySQL 5.6 版本中提供的一项索引优化功能，可以在非聚簇索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

select * from tuser where name like '张%' and age=10;

在MySQL 5.6之前，存储引擎根据通过联合索引找到name like ‘张%’ 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。可以看到需要回表两次，把我们联合索引的另一个字段age浪费了。
在这里插入图片描述
而MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到，由于联合索引中包含列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。可以看到只回表了一次。
在这里插入图片描述

12、正确使用索引的一些建议

12.1、被频繁更新的字段应该慎重建立索引

虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

12.2、限制每张表上的索引数量

索引并不是越多越好，建议单张表索引不超过 5 个！索引可以提高效率同样可以降低效率。

索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

12.3、尽可能的考虑建立联合索引而不是单列索引

因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

12.4、注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引。如（name，city ）和（name ）这两个索引就是冗余索引，能够命中前者的查询肯定是能够命中后者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

12.5、避免索引失效

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：

  • OR引起索引失效（OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的字段都加上索引，索引就不会失效）。
 --注：只给 AGE 和 NAME 字段加了索引，SEX 没有
 EXPLAIN SELECT * FROM YANG WHERE AGE = 43 OR NAME = '2' OR SEX = 0--索引失效
 EXPLAIN SELECT * FROM YANG WHERE AGE = 43 OR NAME = '2'--索引生效
 EXPLAIN SELECT * FROM YANG WHERE AGE = 43 OR AGE = 42--索引生效
  • LIKE模糊查询导致索引失效（但是并不是所有LIKE查询都会失效，只有在查询时字段最左侧加%和左右侧都加%才会导致索引失效）。
--注：给 NAME 字段加了索引
EXPLAIN SELECT * FROM YANG WHERE NAME like '%2'--索引失效
EXPLAIN SELECT * FROM YANG WHERE NAME like '%2%'--索引失效
EXPLAIN SELECT * FROM YANG WHERE NAME like '2'--索引生效
EXPLAIN SELECT * FROM YANG WHERE NAME like '2%'--索引生效
  • 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则索引失效。
--注：给 NAME 字段加了索引
EXPLAIN SELECT * FROM YANG WHERE NAME = 2;--索引失效
EXPLAIN SELECT * FROM YANG WHERE NAME = '2'--索引生效
  • 在索引列上使用内置函数和运算，索引失效。
--注：给 AGE 字段加了索引
EXPLAIN SELECT * FROM YANG WHERE AGE + 0 = 43--索引失效
EXPLAIN SELECT * FROM YANG WHERE IFNULL(NULL,AGE) = 43--索引失效
EXPLAIN SELECT * FROM YANG WHERE AGE = 43--索引生效
  • 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
--注：给 AGE,NAME,SEX 字段加了联合索引
--CREATE INDEX index_age_name_sex ON yang (AGE,NAME,SEX)
EXPLAIN SELECT * FROM YANG WHERE AGE = 43--索引生效
EXPLAIN SELECT * FROM YANG WHERE AGE = 43 AND NAME = '1'--索引生效
EXPLAIN SELECT * FROM YANG WHERE AGE = 43 AND SEX = 0--索引生效
EXPLAIN SELECT * FROM YANG WHERE AGE = 43 AND NAME = '1' AND SEX = 0--索引生效
--只有这四种情况索引生效，其它均失效，因为要遵循最左前缀原则
--例如联合索引（AGE,NAME,SEX）生效的只有 
--AGE 
--AGE,NAME 
--AGE,SEX 
--AGE,NAME,SEX 
--这样的组合，AGE要在最左面
  • 索引字段上使用is null， is not null，可能导致索引失（走和不走索引是和数据量或者和其他元素有关系）。
-- 注：给 AGE 字段加了索引
-- NOT NULL 7条 NULL 0条
EXPLAIN SELECT * FROM YANG WHERE AGE IS NOT NULL-- 不走索引
EXPLAIN SELECT * FROM YANG WHERE AGE IS NULL-- 走索引
-- NOT NULL 6条 NULL 1条
EXPLAIN SELECT * FROM YANG WHERE AGE IS NOT NULL-- 不走索引
EXPLAIN SELECT * FROM YANG WHERE AGE IS NULL-- 走索引
-- NOT NULL 5条 NULL 2条
EXPLAIN SELECT * FROM YANG WHERE AGE IS NOT NULL-- 不走索引
EXPLAIN SELECT * FROM YANG WHERE AGE IS NULL-- 走索引
-- NOT NULL 4条 NULL 3条
EXPLAIN SELECT * FROM YANG WHERE AGE IS NOT NULL-- 不走索引
EXPLAIN SELECT * FROM YANG WHERE AGE IS NULL-- 不走索引
-- NOT NULL 3条 NULL 4条
EXPLAIN SELECT * FROM YANG WHERE AGE IS NOT NULL-- 不走索引
EXPLAIN SELECT * FROM YANG WHERE AGE IS NULL-- 不走索引
-- NOT NULL 2条 NULL 5条
EXPLAIN SELECT * FROM YANG WHERE AGE IS NOT NULL-- 走索引
EXPLAIN SELECT * FROM YANG WHERE AGE IS NULL-- 走索引
-- NOT NULL 1条 NULL 6条
EXPLAIN SELECT * FROM YANG WHERE AGE IS NOT NULL-- 走索引
EXPLAIN SELECT * FROM YANG WHERE AGE IS NULL-- 不走索引
-- NOT NULL 0条 NULL 7条
EXPLAIN SELECT * FROM YANG WHERE AGE IS NOT NULL-- 走索引
EXPLAIN SELECT * FROM YANG WHERE AGE IS NULL-- 不走索引
  • 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效（需要看条件，比如数据量，mysql在执行的时候会判断走索引的成本和全表扫描的成本，然后选择成本小的那个）。
  • mysql估计使用全表扫描要比使用索引快，则不使用索引。
  • 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效。

12.6、删除长期未使用的索引

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗。

MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用。

跪在镜子前喊帅
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MYSQL索引
02-24
mysql中索引是在存储引擎层而不是服务器层实现的B-Tree索引B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。图中根节点没有画出来。B-Tree对索引列是顺序组织存储的，索引很适合查找...
Mysql索引详细介绍
09-10
MySQL索引是数据库管理系统中用于加速数据检索的重要工具。它们通过创建数据结构，使得数据库能够快速定位到所需的数据行，从而显著提高了查询性能。以下是关于MySQL索引的详细介绍： **一、索引概述** MySQL允许为...
MySQL基础(六)-索引（index）详解
five小点心的博客
12-11 9565
索引的创建：create index 索引名 on 表名(字段)
【MySQL】索引
zzg的学习博客
07-30 1757
本篇主要讲述了MySQL中的索引的相关知识
MySQL数据库 之 索引
恋梦轩
12-22 2757
索引用于快速找出在某个列中有一特定值的行。如果不使用索引，MySQL 必须从第一条记录开始读完整个表，直到找出目标行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL 能快速到达某个位置去搜寻数据文件，而不必查看所有数据。
8、索引类型详解
you can you up up up的博客
10-16 2624
索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。MySQL 索引可以从存储方式、逻辑角度和实际使用的角度来进行分类。 存储方式区分 根据存储方式的不同，MySQL 中常用的索引在物理上分为 B-树索引和 HASH 索引两类，两种不同类型的索引各有其不同的适用范围。 1) B-树索引 B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。 B-树索引是一个典型的数据结构，其包含的组件主要有以下几个： 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼
MySQL索引详解
m0_68226879的博客
05-19 970
聚集索引：也叫聚簇索引（ClusterIndex），一般来说是以主键创建的索引，一张表只能有一个聚集索引，而且只有InnoDB能够创建聚集索引。非聚集索引：也叫普通索引、辅助索引（Secondary Index），除了聚集索引外的索引都是非聚集索引。
快速了解MySQL 索引
12-14
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。 打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。 索引分...
php mysql索引问题
10-30
朋友问：今天我碰到个问题，我建个表，索引是REF，怎么才能写进一条信息，自动生成一个REF号码，记录到表中去呢？希望生成的REF号码是连续的，从00000开始。
Mysql索引分类及其使用实例
qq_45752401的博客
07-19 2455
前面已经知道，Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。任何事物都是有两面性的，Hash索引也一样，虽然Hash索引效率高，但是Hash索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。......
如何查看MySQL索引
zidieq的博客
09-19 1万+
f
[MySQL] MySQL中的索引
weixin_67596609的博客
12-13 1698
在MySQL中，索引是一种数据结构用于提高查询效率和加速数据检索。创建索引可以加快数据库中表的查询操作，减少查询所需的时间和资源消耗。但是天下没有免费的午餐，。所以它的价值，在于提高一个海量数据的检索速度。到这里你可能依然对索引的概念是一种模糊的状态，后文我们会详细解释。看会此篇文章再来回头看索引的概念，相信你就会理解了。
分享mysql创建索引的3种方法
热门推荐
xiaoweids的博客
06-07 3万+
到此这篇关于mysql创建索引的3种方法的文章就介绍到这了,微点阅读。
MySQL中的索引
daqi1983的博客
10-28 3654
索引
mysql（三） 索引-普通索引、复合索引、索引规则等
最新发布
DJyzh的博客
01-04 1770
mysql select语句执行流程 普通索引 复合索引 创建索引（三种） 1、使用INDEX建表的时候创建索引 2、使用CREATE INDEX语句可以创建普通索引。 3、使用ALTER语句创建（是给指定表、指定指端新增索引） 索引规则 (最常见的) Using Where（索引回表） 使用explain执行sql Using Index（索引覆盖） 最左匹配：讲了很多遍了、这次讲点不一样的 Using Index Condition（索引下推）
MySQL 索引设计
jyhhhhhhh的专栏
03-13 189
在单表上对索引进行设计其实还是非常容易的，只需要遵循固定的套路就能设计出一个理想的三星索引，在这里强烈推荐数据库索引设计与优化这本书籍，其中包含了大量与索引设计与优化的相关内容；在之后的文章中读者也会分析介绍书中提供的几种估算方法，来帮助我们通过预估问题设计出更高效的索引。如果对文章内容的有疑问，可以在博客下面评论留言。
mysql索引实例_mysql建立高效的索引实例分析
weixin_34791250的博客
01-19 190
本文实例讲述了mysql建立高效的索引。分享给大家供大家参考，具体如下：如何建立理想的索引？查询频繁度区分度索引长度覆盖字段区分度假设100万用户,性别基本上男/女各为50W, 区分度就低。长度小索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).区分度高，长度小问题：如果让区分度高，而长度小？答：可以针对列中的值,从左往右截取部分,来建索引(1)截的越短, 重复度...
mysql索引
05-29
MySQL索引是一种数据结构，可以帮助MySQL快速定位和访问表中的数据。使用索引可以提高查询效率，降低数据库的负载。下面是MySQL索引的一些基本概念和使用方法： 1. 索引类型 MySQL支持多种类型的索引，包括B树索引...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值