mysql索引

 

什么是索引

一种数据结构，可以加速检索数据库中的数据

 

Mysql常用索引类型及相关SQL操作

 根据索引的用途，mysql主要的索引有5种：普通索引、唯一索引、主键索引、联合索引、全文索引。

1.普通索引

这是最基本的索引，没有任何限制，作用是加快系统对数据的访问速度。

1）直接创建

CREATE INDEX index_name ON table_name(column_name);

2）修改表结构的方式创建

ALTER TABLE table_name ADD INDEX index_name ON(column_name)

3) 创建表结构时创建

CREATE TABLE `table_name`(
 `name` varchar(32) NOT NULL,
 ......
 INDEX i_name(name)
);

4）删除索引

DROP INDEX index_name ON table_name;

2.唯一索引

与普通索引类似，不同的是创建唯一索引的目的还包括避免数据出现重复。

唯一索引使用关键字UNIQUE 标示。

1）直接创建

CREATE UNIQUE INDEX index_name ON table_name(column_name)

2) 修改表结构的方式创建

ALTER TABLE table_name ADD UNIQUE INDEX index_name ON(column_name)

3) 创建表结构时创建

CREATE TABLE `table_name`(
 `name` varchar(32) NOT NULL,
 ......
 UNIQUE INDEX udx_name(name)
);

3. 主键索引

主键索引是为主键字段创建的索引，一个表中只有有一个主键，主键子段每个值唯一且不能为空，在创建表结构时创建。

CREATE TABLE `table_name`(
 `id` unsigned int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(32) NOT NULL,
 ......
 UNIQUE INDEX udx_name(name),
 PRIMARY KEY (`id`)
);

4. 联合索引 

联合索引是在多个子段上创建的索引，类型可以是普通索引也可以是唯一索引，使用联合索引时遵循最左匹配规则 。

CREATE UNIQUE INDEX index_name ON table_name(column1_name, column2_name)

5. 全文索引

在mysql中 全文索引使用FULLTEXT关键字创建，只有MyISAM存储引擎支持全文索引，且只能在char、varchar、text类型的列上创建，全文索引跟其他索引不同，不是使用where语句进行参数匹配，而是配合match again使用，它更像一个搜索引擎。

CREATE FULLTEXT INDEX index_name ON table_name(content);

 

索引使用哪种数据结构

mysql中常见的有两种：Hash和B+Tree。Hash索引底层实现就是哈希表，哈希表是一种Key-value存储数据的结构，key值不能比较大小，不能排序。所以只适用于等值查询，不适合区间（范围）查询。B+Tree是一种多路平衡树，它的节点天然有序（左孩子小于父节点，右孩子大于父节点）适合范围查询。

另外：

哈希索引不支持联合索引的最左匹配规则，如果存在大量重复键值，会因为hash碰撞问题，导致效率降低。

 

高性能索引策略

1.聚簇索引

聚簇索引的数据分布

聚簇索引并不是一个单独的索引类型，而是一种数据存储方式。在InnoDB中，聚簇索引实际上在同一个结构中保存了B+Tree的索引和数据行。节点页只包含索引列，叶子页才包含全部数据。聚簇索引数据行和相邻的键值紧凑的存储在一起，因此由于数据行只有一份，聚簇索引在一个表中只有一个。

非聚簇索引：叶子结点存储了主键值的非主键索引。

InnoDB通过主键聚集索引；如果没有定义主键，则选择一个非空的唯一索引替代，如果还没有，则隐示定义一个主键作为聚簇索引。InnoDB只聚集同一个页面中的记录，包含相邻键值的页面可能会相距很远。

为什么要把数据进行聚集？

1.比如实现一个电子邮箱系统，可以根据用户ID做数据聚集，数据聚集后只需要从磁盘读取少数的数据页就能够读到用户的全部邮件。假设不使用聚簇索引，那么读取用户的每封邮件很可能都会导致一次磁盘IO。

2.相对非聚簇索引，使用聚簇索引可以减少一次回表，加快数据访问。

聚簇索引 的页分裂问题

聚簇索引表数据的插入是顺序插入，基于聚簇索引的表在插入新行，或者主键被更新时，可能面临页分裂问题。当行的主键值要求插入行在一个已满的页中时，存储引擎将页分裂成两个页面来容纳该行，页分裂会导致表占用更多的磁盘空间。

总之，相对二级索引，聚簇索引可以减少一次回表查询，通过把相关数据聚集、减少磁盘IO次数来保证查询的高效。

 

2.覆盖索引

通常大家都会依据查询条件WHERE来创建合适的索引，这样做没什么问题，但如果懂得设计索引时充分考虑到整个查询，包含需要返回的数据集字段，则更高明一些。覆盖索引指的就是索引的叶子节点已经包含了需要查询的数据，这样就没必要根据主键进行二次回表查询，显然覆盖索引效率更高一些。

例如：表test_index中有一个索引idx_key1_key2(kye1,key2).当我们通过sql select key2 from test_index where key1='key1test';时，就可以通过覆盖索引查询，无需回表。仔细看 其实key2 是索引idx_key1_key2的一部分，索引列已经包含了需要查询的数据。

1. 索引条目通常远小于数据行大小，如果只需要读取索引，那MySQL能够极大的减少数据访问量。如果没使用覆盖索引，响应时间可能大部分花在数据拷贝上了。
2. InnoDB可以减少根据主键的二次回表查询。

我们可以通过EXplain 来分析查询语句是不是覆盖索引。explain的结果Extra列 如果是using index，则说明使用覆盖索引。

这里很容易把Extra列的Using index和type列的index搞混淆，这两者完全不同，type用来说明MySQL查找行的方式

思考 为什么聚簇索引的查询更快？

因为主键索引树的叶子节点直接就是我们要查的数据，而非主键索引的叶子结点是主键的值，需要回表查询（根据主键的值再进行一次查询）。

思考 非主键索引一定会回表查询多次吗？

不是，使用覆盖索引的方式就不需要回表查询了。

联合索引、最左前缀匹配

思考：创建索引的时候都会考虑哪些因素？

 一般对于查询概率比较高，经常作为where条件的字段设置索引（包括单键索引和联合索引）。另外对于业务上的唯一键创建唯一索引防止数据冲突。

思考：创建联合索引多个字段之间的顺序如何选择？

最左匹配原则，将where子句中使用最频繁的一列放到最左边，mysq的索引查询会遵循最左前缀匹配的原则（最左优先），当我们创建一个联合索引时，相当于创建了多个索引，例如(k1,k2,k3) ---- 相当于(k1)、(k1,k2)、(k1,k2,k3)三个索引。

面试常见索引问题：

https://blog.csdn.net/weixin_42469083/article/details/113333197

 

参考：

https://zhuanlan.zhihu.com/p/78982303

https://zhuanlan.zhihu.com/p/165156832

https://zhuanlan.zhihu.com/p/78982303