MySQL索引
索引概述
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
每种搜索引擎支持的索引是不同的，具体如下：
- MyISAM ，InnoDB支持btree索引
- Memory 支持 btree和hash索引
索引的优势如下：
- 加快查询速度
- 创建唯一索引来保证数据表中数据的唯一性
- 实现数据的完整性，加速表和表之间的连接
- 减少分组和排序的时间
增加索引也有很多不利，主要表现在以下几个方面：
- 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
- 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
索引的分类
按表列属性分类
唯一索引和普通索引
普通索引：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
主键索引：是一种特殊的唯一索引，不允许有空值。
单列索引和组合索引
单列索引：即一个索引只包含单个列，一个表可以有多个单列索引；
组合索引：指在表的多个字段组合上创建的索引。只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
全文索引（ fulltext）
全文索引类型为FULLTEXT，在定义索引的列上支持值得全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL 5.7.xx之前只有MyISAM存储引擎支持全文索引。
空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4中，分别是：geometry、point、linstring和polygon 。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建空间索引的列，必须将其声明为NOT NULL，同样，在MySQL 5.7.xx之前，空间索引只能在存储引擎为MyISAM的表中创建。
按数据结构分类
B+树索引
B+树基于平衡二叉树的一种多路平衡查找树，所有记录都按照顺序存放在叶子节点中，各个叶子节点直接通过链表相连。
与B树不同的是：
- 非叶子节点只存储键值信息。
- 所有叶子节点之间都有一个链指针。
- 数据记录都存放在叶子节点中。
hash索引
基于hash表结构实现的索引，mysql中只有MEMORY/HEAP和NDB存储引擎支持；
InnoDB引擎支持自适应hash索引，但是是数据库自身创建使用的，而不能进行人为定义。当二级索引被频繁的访问时，便会自动创建自适应哈希索引；
通过 命令SHOW ENGINE INNODB STATUS可查看自适应hash索引的使用情况；
通过 命令SHOW VARIABLES LIKE '%ap%hash_index' 查看是否打开自适应hash索引。
与B+树对比，不足之处：
- 由于hash索引是比较其hash值，hash索引只能进行等值查找而不能进行范围查找；
- hash索引无法进行排序；
- 不支持最左匹配原则，复合索引时合并一起计算hash值；
- hash索引的检索效率很高可以一次定位，但是当发生大量hash碰撞的时候，链表变长，hash索引效率上是不如b+tree的；
- 由于存在hash碰撞的问题，当需要获得总数时候，hash 索引在任何时候都不能避免表扫描；
按存储结构分类
聚簇索引(聚集索引)
InnoDB的聚簇索引实际上是在同一个B+树结构中同时存储了索引和整行数据，通过该索引查询可以直接获取查询数据行。
聚簇索引不是一种单独的索引类型，而是一种数据的存储方式，聚簇索引的顺序，就是数据在硬盘上的物理顺序。
在mysql通常聚簇索引是主键的同义词，每张表只包含一个聚簇索引（其他数据库不一定）。
聚簇索引的优点：
- 可以把相关数据保存在一起，如：实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少量的数据页就能获取某个用户全部邮件，如果没有使用聚集索引，则每封邮件都可能导致一次磁盘IO。
- 数据访问更快，聚集索引将索引和数据保存在同一个btree中，因此从聚集索引中获取数据通常比在非聚集索引中查找要快。
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
聚簇索引的缺点：
- 聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没有那么重要了，聚集索引也没有什么优势了
- 插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到innodb表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表。
- 更新聚集索引列的代价很高，因为会强制innodb将每个被更新的行移动到新的位置。
- 基于聚集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题，当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间。
- 聚集索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
- 二级索引可能比想象的更大，因为在二级索引的叶子节点包含了引用行的主键列。
- 二级索引访问需要两次索引查找，而不是一次。
辅助索引(非聚集索引，次级索引，二级索引)
非聚集索引在B+树的叶子节点中保存了索引列和主键。如果查询列不在该索引内，只能查到其主键值，还需要回表查询聚簇索引进行查询。
创建索引的规则
- 创建索引并非是越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响
- insert、delete、update等语句的性能。因为当表中的数据更改时，索引也会进行调整和更新；
- 数据量小的表最好不要创建索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要长；
- 避免对经常更新的数据创建索引。而对经常用于查询的字段应该创建索引；
- 在条件表达式中经常用到的不同值较多的列创建索引；
- 当唯一性是某种数据本身的特征时，我们创建唯一性索引；
- 在频繁进行排序或分组的列上建立索引，如果排序的列有多个，可以创建组合索引；
创建表的同时创建索引
创建普通索引
create table book(
bookid int,
bookname varchar(255),
authors varchar(255),
info varchar(255),
comment varchar(255),
year_publication year,
index(year_publication) -- 创建year_publication列为索引列
);查看索引创建情况：show create table book;
CREATE TABLE `book` (
`bookid` int(11) DEFAULT NULL,
`bookname` varchar(255) DEFAULT NULL,
`authors` varchar(255) DEFAULT NULL,
`info` varchar(255) DEFAULT NULL,
`comment` varchar(255) DEFAULT NULL,
`year_publication` year(4) DEFAULT NULL,
KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4创建唯一索引
唯一索引主要原因是减少查询索引列操作的执行时间。尤其是对比比较庞大的数据表。与普通索引类似，不同点在于：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
create table t1(
id int not null,
name char(30),
unique index Uniqidx(id)
);查看索引创建情况：
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`name` char(30) DEFAULT NULL,
UNIQUE KEY `Uniqidx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4创建单列索引
单列索引：是在数据表中的某一字段上创建的索引，一个表中可以创建多个单列索引。
create table t2(
id int not null,
name char(50) null,
index singleidx(name)
);查看索引创建情况：
CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`name` char(50) DEFAULT NULL,
KEY `singleidx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4创建组合索引
组合索引：是在多个字段上创建一个索引。遵循最左前缀原则。最左前缀 索引最左边的列来匹配行。
create table t3(
id int not null,
name char(30) not null,
age int not null,
info varchar(255),
index multiidx(id,name,age)
);查看索引创建情况
CREATE TABLE `t3` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
KEY `multiidx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4注：组合索引可以起几个索引的作用，但是使用时并不是随意查询哪个字段都是可以使用索引。而是遵循最左前缀：利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
创建全文索引
全文索引：FULLTEXT，可以用于全文搜索，支持为CHAR\VARCHAR和TEXT 列。索引总是对整个列进行，不支持局部索引，适合大型数据的表创建。
-- 如果是5.7之前的版本需要在创建表的时候加上ENGINE=MyISAM
create table t4(
id int not null,
name char(30) not null,
age int not null,
info varchar(255),
FULLTEXT INDEX FullIdx(info(100))
);查看索引创建情况：
CREATE TABLE `t4` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
`age` int(11) NOT NULL,
`info` varchar(255) DEFAULT NULL,
FULLTEXT KEY `FullIdx` (`info`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4如何使用：
-- 全文索引的使用方法和其他索引不一样，具体使用方法如下（先插入测试数据）
create table test8 ( id int not null, title varchar(255), body varchar(255),fulltext index(body) );
insert into test8 values(1,'shujuku','In MySQL 8.0.17, we made an observation in the well-known TPC-H benchmark for one particular query');
insert into test8 values(2,'shujuku','WORDS OF WISDOM: Like they say in Asia, nobody should use a fork. Tradition evven dictates to “chop” all your forks and “stick” to the origiinal. ');
-- 测试是否用到了索引，并且可以搜索到数据
explain select * from test8 where match(body) against('MySQL')查看到的数据如下：
注：在上面创建全文索引的方式中，可以实现英文的全文索引，每个单词以空格分隔来匹配，若想实现中文的全文索引，那么需要在创建表的同时，加上“with parser ngram”来带上中文解析器。
创建空间索引
空间索引：必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。
create table tb5(
g geometry not null,
spatial index spaidx(g)
)engine=myisam;查看索引创建情况：
CREATE TABLE `tb5` (
`g` geometry NOT NULL,
SPATIAL KEY `spaidx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4在已经存在的表上创建索引
-- 创建一个表用于测试
CREATE TABLE book1 (
bookid INT NOT NULL,
bookname VARCHAR(255) NOT NULL,
authors VARCHAR(255) NOT NULL,
info VARCHAR(255) NULL,
comment VARCHAR(255) NULL,
year_publication YEAR NOT NULL
);添加唯一索引
alter table book add unique index uniqidx(bookid); -- uniqidx为索引名，bookid为列名添加单列索引
alter table book add index bkidex(comment(50));添加全文索引
alter table t6 add fulltext index infofulidx(info);添加组合索引
alter table book add index abc(authors(20),info);添加空间索引
create table t7 (g geometry not null);
alter table t7 add spatial index spatidx(g);删除索引
删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，那么整个索引将被删除。
查看某些表上有哪些索引：
show index from book;用alter table删除索引：
alter table book drop index uniqidx;注：添加AUTO_INCREMENT 的约束字段的唯一索引不能删除
用drop index删除：
drop index spaidx on t7;最左前缀原则
（1）减少开销：建一个联合索引 (a,b,c) ，实际相当于建了 (a)、(a,b)、(a,b,c) 三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销。
（2）覆盖索引：MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机 io 操作。
（3）效率高
假设有3个过滤条件：c1,c2,c3 每一个条件可以过滤10%的数据。100W的数据
单列索引：100W*10%过滤后，需要回表继续过滤c2,c3
联合索引：100W*10%*10%*10%=1000
官方文档解释说：
如果表拥有一个联合索引, 任何一个索引的最左前缀都会被优化器用于查找列。比如，如果你创建了一个三列的联合索引包含(col1, col2, col3), 你的索引会生效于(col1),(col1, col2), 以及(col1, col2, col3)
如果查询的列不是索引的最左前缀, 那MySQL不会将索引用于执行查询。假设你有下列查询语句：
① SELECT * FROM tbl_name WHERE col1=val1;
② SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
③ SELECT * FROM tbl_name WHERE col2=val2;
④ SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;如果索引存在于(col1, col2, col3), 那只有头两个查询语句用到了索引。第三个和第四个查询包含索引的列, 但是不会用索引去执行查询. 因为(col2)和(col2, col3) 不是(col1, col2, col3)的最左前缀。
通俗的讲，有以下两点：
- 最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
- =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
- 要包含前缀索引，比如b = 2 and c = 3 建立(a,b,c)索引，则不会用到索引。
索引面试附录
什么是数据库索引？
数据库索引是数据库系统中一个重要的概念，索引也叫做 key ，是一种用于提升数据库查询效率的数据结构，我们可以把索引理解成一本书的目录，通过目录我们可以快速找到对应章节的内容，同样的，通过数据库索引，我们可以快速找到数据表中对应的记录。
总而言之，索引就像给数据表建了一个目录一样。
为什么要使用索引？
- 使用索引大大减少了存储引擎需要扫描的数据量，如果没有使用索引的话，每查询一行数据都要对数据表进行扫描，这样的话会非常慢。
- 由于索引已经排好序的，所以对数据表进行 ORDER BY 和 GROUP BY 等操作时，可以很快得到结果。
- 索引可以将随机的 I/O 转为顺序的 I/O ，避免高昂的磁盘 IO 成本，提升查询效率。
MySQL索引在哪个模块中实现的？
MySQL 的索引是在存储引擎这一层实现的，因此每一种存储引擎都有不同的实现方式，对同一种索引的处理方式也完成不同。
为什么设置了索引却不起作用？
在非覆盖索引下，如果使用以 % 开头的 LIKE 语句进行模糊匹配，则无法使用索引：
SELECT * FROM users WHERE name LIKE '%小张%';不过以 % 为结尾则可以使用索引（注：是否使用索引与数据的离散性有关），如：
SELECT * FROM users WHERE name LIKE '张%';OR 语句前后没有同时使用索引，比如下面的语句， 字段id 有索引，而字段name 没有创建索引，那么下面的语句只能全表扫描，无法用到索引：
SELECT * FROM users id = 10 or name='test';MySQL索引底层使用什么数据结构？
在 MySQL 中，大部分情况下，索引都是使用 B-Tree 作为底层数据结构， B-Tree 只是一种泛称，实际上不同的存储引擎使用 B-Tree 时，有不同的变种，比如 InnoDB 使用的是 B+Tree 。
另外也有一些特殊的索引结构，比如哈希索引，哈希索引底层则使用的是哈希表，在 MySQL中，只有 Memory 存储引擎支持哈希索引。
什么情况下数据表不适合创建索引？
- 对于用于存储归档历史数据的且很少用于查询的数据表，不建议创建索引。
- 数据量比较小的数据表，而且未来数据也不会有太大增长的数据，不应该建索引，比如用于保存配置的数据表。
- 修改频繁，且修改性能远大于查询性能时，不应该再创建索引。
什么是回表？
回表是对Innodb存储引擎而言的，在 InnoDB 存储引擎中，主键索引的叶子节点存储的记录的数据，而普通索引的叶子节点存储的主键索引的地点。
当我们通过主键查询时，只需要搜索主键索引的搜索树，直接可以得到记录的数据。
当我们通过普通索引进行查询时，通过搜索普通索引的搜索树得到主键的地址之后，还要再使用该主键对主键搜索树进行搜索，这个过程称为回表。
聚簇索引与非聚簇索引的区别？
- 聚簇索引：聚簇索引的顺序就是数据的物理存储顺序，并且索引与数据放在一块，通过索引可以直接获取数据，一个数据表中仅有一个聚簇索引。
- 非聚簇索引：索引顺序与数据物理排列顺序无关，索引文件与数据是分开存放。
MySQL主键索引、唯一索引与普通索引的区别？
- 设置为主键索引的字段不允许为 NULL ，而且一张数据表只能有一个主键索引。
- 设置为唯一索引的字段，其字段值不允许重复。
- 普通索引可以包含重复的值，也可以为 NULL 。
索引可以提高查询性能，那是不是索引创建越多越好？
索引作为一个数据表的目录，本身的存储就需要消耗很多的磁盘和内存存储空间。并且在写入数据表数据时，每次都需要更新索引，所以索引越多，写入就越慢。尤其是糟糕的索引，建得越多对数据库的性能影响越大。
MyISAM与InnoDB在处理索引上有什么不同？
MyISAM 存储引擎是非聚族索引，索引与数据是分开存储的，索引文件中记录了数据的指针。
而 InnoDB 存储引擎是聚族索引，即索引跟数据是放在一块的， InnoDB 一般将主键与数据放在一块，如果没有主键，则将 unique key 作为主键，如果没有 unique key ，则自动创建一个 rowid 作为主键，其他二级索引叶子指针存储的是主键的位置。
什么是索引的最左前缀原则？
MySQL 数据库不单可以为单个数据列创建索引，也可以为多个数据列创建一个联合索引，比如：创建(a,b,c)索引，只能是(a)、(a,b)、(a,b,c)可以使用到索引，其中a、b、c的顺序可以任意。
什么是覆盖索引？
如果一个索引中包含查询所要的字段时，此时不需要再回表查询，我们就称该索引为覆盖索引。
比如下面的查询中，字段id是主键索引，所以可以直接返回索引的值，显著提升了查询的性能。
SELECT id FROM users WHERE id BETWEEN 10 AND 20;
浙公网安备 33010602011771号