MySQL数据库之索引

索引（Index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。索引提高了数据查询和排序效率，但是索引需占用更多存储空间，降低了表新增，修改和删除操作的速度

一，索引结构

MySQL索引是在存储引擎层实现的，不同的存储引擎支持不同的索引结构

结构	描述	InnoDB	MyISAM	Memory
B+Tree索引	最常见的索引结构，大部分引擎都支持B+树索引	支持	支持	支持
Hash索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询	不支持	不支持	支持
R-tree（空间索引）	空间索引是MyISAM引擎的一个特殊索引，主要用于地理空间数据类型	不支持	支持	不支持
Full-text（全文索引）	是一种通过建立倒排索引匹配文档的方式	5.6版本之后支持	支持	不支持

二，索引分类

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
单列索引	快速定位特定数据，一个索引只包含单个列	可以有多个
联合索引	快速定位特定数据，一个索引包含多个列	可以有多个
全文索引	查找的是文本中的关键字，而不是比较索引中的值	可以有多个	FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种

分类	含义	特点
聚集索引（Clustered Index）	将数据存储和索引放到一起，索引结构的叶子结点保存了行数据	只有一个，如果存在主键，主键索引就是聚集索引，如果不存在主键，将使用第一个唯一索引作为聚集索引，如果没有主键也没有唯一索引，InnoDB会自动生成一个rowid作为隐藏的聚集索引
二级索引（Secondary Index）	将数据和索引分开存储，索引结构的叶子结点关联的是对应的主键	可以存在多个

三，索引的使用

1.查询，创建，删除索引

-- 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 ON 表名(字段1,...);
-- 查看索引
SHOW INDEX FROM 表名;
-- 删除索引
DROP INDEX 索引名称 ON 表名;

2.分析和显示SQL查询的执行计划

-- 获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接顺序
EXPLAIN 查询语句;

执行计划各字段含义

字段	含义	说明
id	SELECT查询的序列号，表示查询中执行SELECT子句或者是操作表的顺序	id相同，执行顺序从上到下；id不同，值越大，越先执行
select_type	标识select的类型	SIMPLE表示简单表，即不使用表连接或子查询、PRIMARY（主查询，即外层查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询语句）
type	表示连接类型	性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all
possible_key	显示可能应用在这张表上的索引	一个或多个
key	实际使用的索引	如果为NULL，则没有使用索引
key_len	表示索引中使用的字节数	该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
rows	MySQL认为必须要执行查询的行数	在Innodb引擎的表中，是一个估计值，可能并不总是准确的
filtered	返回结果的行数占需读取行数的百分比	值越大越好

3.SQL索引提示

在MySQL数据库中，如果在一个字段上建立了多个索引，在查询该字段时MySQL数据库会默认选择一个索引。当默认选择的索引不是最优选择时，可以通过提示来指定特定索引

-- 建议使用索引
SELECT 字段列表 FROM 表名 USE INDEX(索引名) WHERE 条件列表;
-- 忽略索引
SELECT 字段列表 FROM 表名 IGNORE INDEX(索引名) WHERE 条件列表;
-- 强制使用索引
SELECT 字段列表 FROM 表名 FORCE INDEX(索引名) WHERE 条件列表;

4.覆盖索引

查询使用了索引，并且需要返回的列，在该索引中已经能全部找到

5.前缀索引

（1）定义

前缀索引是一种索引，其中索引的键值只包含索引列的前缀部分。这种索引可以显著减小索引的大小，从而提高索引的性能。前缀索引通常用于索引较长的列（如VARCHAR或TEXT类型），因为索引整个列可能会导致索引过大，影响性能。

-- 建立前缀索引
CREATE INDEX 索引名 ON TABLE 表名(字段名(前缀长度));

（2）索引选择性

前缀索引的有效性取决于前缀的唯一性。如果前缀不够独特，索引的性能可能会受到影响。选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高。唯一索引的选择性是1，性能是最好的

-- 计算选择性
SELECT COUNT(DISTINCT 字段名)/COUNT(*) FROM 表名;
SELECT COUNT(DISTINCT SUBSTRING(字段名, 起始下标, 截取长度))/COUNT(*) FROM 表名;

四，索引设计原则

1.针对于数据量较大，且查询比较频繁的表建立索引
2.针对于常作为查询条件（where），排序（order by）、分组（group by）操作的字段建立索引
3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
4.如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引
5.尽量使用联合索引，减少单列索引，查询时，联合索引可以覆盖索引，节约存储空间，避免回表查询，提高查询效率
6.要控制索引数量，索引越多，维护索引结构的代价也会越大，会影响增改删的效率
7.如果索引列不能存储NULL值，在创建表时使用NOT NULL约束，以便MySQL优化器知道每列是否包含NULL值时，可以更好的确定使用哪个索引进行查询

五，常见索引失效场景

1.针对联合索引，查询从索引的最左列开始，并且不跳过索引中的列。如果查询条件跳过了某一列，索引将部分失效
2.针对联合索引，出现>、<范围查询，则范围查询右侧索引失效
3.用OR分隔开的条件，如果OR前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
4.在索引上进行运算操作，索引失效
5.如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效
6.如果MySQL评估使用索引比全表更慢，则不使用索引