MYSQL 索引
MYSQL 索引
1. MYSQL 索引介绍
索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)，而不是在select的字段中，实际上，索引也是一张“表”，该表保存了主键与索引字段，并指向实体表的记录，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。说白了索引就是用来提高速度的，但是就需要维护索引造成资源的浪费，所以合理的创建索引是必要的。
2. 索引的类别
图片来源：https://dev.mysql.com/doc/refman/8.0/en/create-index.html
- Primary Key
InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。
- 单列索引
单列索引即一个索引只包含单个列
- 组合索引
组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左匹配原则
- Unique
索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值
- Key
是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值
- FULLTEXT
全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建
- SPATIAL
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL
最左匹配原则示例：
order by使用索引最左前缀
- order by a
- order by a,b
- order by a,b,c
- order by a desc, b desc, c desc
如果where使用索引的最左前缀定义为常量，则order by能使用索引
- where a=const order by b,c
- where a=const and b=const order by c
- where a=const and b > const order by b,c
不能使用索引进行排序
- order by a , b desc ,c desc --排序不一致
- where d=const order by b,c --a丢失
- where a=const order by c --b丢失
- where a=const order by b,d --d不是索引的一部分
- where a in(...) order by b,c --a属于范围查询
3. 索引创建规则
- 经常查询的字段应该创建索引，避免对频繁更新的表进行过多的索引
- 数据量小的表最好不要使用索引
- 在条件表达式值较多的列可使用索引，值较少的无须使用索引
- 唯一性是某种数据本身的特征时，指定唯一索引。用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
- 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。
- 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度。例如，有一个CHAR(200)列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。
- 利用最左前缀原则
4. 索引的管理和使用
EXPLAIN
EXPLAIN SELECT * FROM student
具体字段含义：
- id ：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
- select_type ：查询类型 或者是 其他操作类型
- table ：正在访问哪个表
- partitions ：匹配的分区
- type ：访问的类型
- possible_keys ：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
- key ：实际使用到的索引，如果为NULL，则没有使用索引
- key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
- ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
- rows ：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
- filtered ：查询的表行占表的百分比
- Extra ：包含不适合在其它列中显示但十分重要的额外信息
4.1 EXPLAIN 字段含义
- id table
id 读取顺序是自上而下，table 读取顺序 id 越大的 table 读取越在前面
- select type
- SIMPLE 简单查询，不包括子查询和 union `查询
- PRIMARY 当存在子查询时，最外面的查询被标记为主查询
- SUBQUERY 子查询
- UNION 当一个查询在 UNION 关键字之后就会出现 UNION
- UNION RESULT 连接几个表查询后的结果
- DERIVED 在 FROM 列表中包含的子查询被标记为 DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中，MySQL5.7+ 进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率
- partions
该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。
- type
NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL
- NULL MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引
- SYSTEM 表只有一行记录（等于系统表），这是
const类型的特列 - CONST 表示通过索引一次就找到了，
const用于比较primary key或uique索引 - EQ_REF 用于联表查询的情况，按联表的主键或唯一键联合查询
- REF 可以用于单表扫描或者连接
- REF_OR_NULL 类似ref，但是可以搜索值为
NULL的行 - INDEX_MERGE 表示查询使用了两个以上的索引，最后取交集或者并集
- RANGE 索引范围查询
- INDEX
index只遍历索引树，通常比All快 - ALL 硬盘读
- possible_keys
这个表里面存在且可能会被使用的索引，可能会在这个字段下面出现，但是一般都以key为准。
- key
实际使用的索引，如果为null,则没有使用索引，否则会显示你使用了哪些索引，查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。
- ref 字段
显示哪些列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。
- rows 和 filter
rows是根据表的统计信息和索引的选用情况，优化器大概帮你估算出你执行这行函数所需要查询的行数。
Filter是查询的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。
- extra
Using filesort表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序，外部排序就不断的在磁盘和内存中交换数据Using tempporary表示在对MySQL查询结果进行排序时，使用了临时表，这样的查询效率是比外部排序更低的Using index表示使用了索引Using whereUsing join buffer表明使用了连接缓存,比如说在查询的时候impossible where筛选条件没能筛选出任何东西distinct优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
4.2 创建索引
4.2.1 基于创建表时建立索引
- 创建普通索引
CREATE TABLE test.`user2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`sex` varchar(5) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
`birthday` datetime NOT NULL,
PRIMARY KEY (`id`),
index idx1(username)
);
- 创建唯一索引
CREATE TABLE test.`user2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`sex` varchar(5) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
`birthday` datetime NOT NULL,
`score_num` int(11) NOT NULL UNIQUE,
PRIMARY KEY (`id`),
unique index idx1(username)
);
- 创建组合索引
CREATE TABLE test.`user2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`sex` varchar(5) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
`birthday` datetime NOT NULL,
`score_num` int(11) NOT NULL UNIQUE,
PRIMARY KEY (`id`),
index idx1(id,score_num,username)
);
- 创建全文索引
CREATE TABLE test.`user2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`sex` varchar(5) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
`birthday` datetime NOT NULL,
`score_num` int(11) NOT NULL UNIQUE,
PRIMARY KEY (`id`),
fulltext index idx1(username)
);
- 创建空间索引
create table test.test(
position geometry not null,
spatial index idx1(position)
);
- 创建前缀索引
CREATE TABLE test.t1 (
col1 VARCHAR(10),
col2 VARCHAR(20),
INDEX (col1, col2(10))
);
4.2.2 基于已创建好的表创建索引
- ALTER TABLE 创建索引
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]
- CREATE INDEX 创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC|DESC]
4.3 删除索引
- ALTER TABLE 删除索引
ALTER TABLE table_name DROP INDEX index_name
- DROP INDEX 删除索引
DROP INDEX index_name ON table_name;
4.4 聚集索引和二级索引
- 聚集索引
聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。每张表只能拥有一个聚集索引。聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。
- 二级索引
对于辅助索引（Secondary Index），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。
当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。
- 覆盖索引
InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
