MySql数据库的索引

文章目录

前言 链接

~galles/visualization/Algorithms.html

基本语法

1.添加主键索引

ALTER TABLE table_name ADD PRIMARY KEY (column)

2.添加唯一索引

ALTER TABLE table_name ADD UNIQUE (column)

3.添加全文索引

ALTER TABLE table_name ADD FULLTEXT (column)

4.添加普通索引

ALTER TABLE table_name ADD INDEX index_name (column )

5.添加组合索引

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

6.删除索引

drop index indexname on table_name

7.查看索引

show index from table_name

8.导出表的索引

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

1、索引是什么 2、索引类型有哪些

3、为什么使用索引

下面是一张数据库的表，有两列数据，分别是Col1和Col2，存储的都是数字

我们来查询一下数字为 91 的数据，mysql语句如下：

select * from yang where Col2 = 91

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

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

4、为什么选择B+树作为索引结构 4.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 ）

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

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

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

4.2.1 红黑树

红黑树的性质

红黑树左旋

当前节点是红色，父节点是红色，叔节点是黑色，且当前节点是右子树，

将父节点左旋，本节点向上，本节点的左子树，连接上左旋下来的父节点的右侧。

红黑树右旋

当前节点是红色，父节点是红色，叔节点是黑色，且当前节点是左子树，

将父节点右旋，本节点向上，本节点的右子树，连接上右旋下来的父节点的左侧。

同样依次从小到大插入七条数据 5 22 23 34 77 89 91

如果我们再查询 91 的话，查询规则是 22 34 89 91（查询到 91 ）

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

4.2.2 AVL 树

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

同样依次从小到大插入数据 5 22 23 34 77 89 91

如果我们再查询 91 的话，查询规则是 34 89 91（查询到 91 ）

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

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

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

4.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树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树更低，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

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

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

5、B+ 树索引搜索过程

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

select * from yang where age = 48;--age非主键，先进行非聚集索引再聚集索引

先画出idx_age索引的索引结构图，大概如下：

再画出id主键索引，如下：

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

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

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

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

6、覆盖索引

覆盖索引：在查询的数据列里面，不需要回表去查，直接从索引列就能取到想要的结果。换句话说，你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需要回表查询出来

7、索引失效

 --注：只给 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--索引生效

--注：给 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要在最左面 

-- 注：给 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-- 不走索引

8、联合索引之最左前缀原则 8.1、联合索引是什么

联合索引指的是对一张表上的多个列进行索引。

8.2、联合索引的好处 8.3、最左前缀匹配原则

在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不会走

mysql会一直向右匹配直到遇到范围查询(>、3 and d = 4，如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

9、索引下推

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

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

在MySQL 5.6之前，存储引擎根据通过联合索引找到name likelike ‘张%’ 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。可以看到需要回表两次，把我们联合索引的另一个字段age浪费了。

而MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到，由于联合索引中包含列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。可以看到只回表了一次。

10、大表添加索引

为表增加索引是会对表进行加锁处理的。稍有不慎，可能会导致表被锁后，业务无法进行读写操作而产生事故影响，通常都是报错Waiting for meta data lock。在对表进行修改时，特别是生产上，我们首先要观察对应的表此时是否在高并发读写（选择操作时机）、表的量级信息。

先创建一张跟原表A数据结构相同的新表B。在新表B添加需要加上的新索引。把原表A数据导到新表B。新表B为原表的表名A，原表A换别的表名。

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!