MySQL索引
随笔参考：
一. 什么是索引
索引对于表中的记录而言起到一个目录的作用，通过查询索引，我们可以快速的找到想要的记录，而不需要对表进行全面的扫描。就和查字典的原理类似。
二. 索引的优缺点
优点：
- 减少查找数据时需要扫描的数据量，从而大大加快数据的检索速度。（最主要原因）
- 避免查询时进行
排序和创建临时表。 - 可以将随机IO变成顺序IO。
- 随机IO：数据存储位置不连续。因为数据库中的数据在磁盘上实际是随机存储。
- 顺序IO：数据存储位置连续。索引所需空间较小，可以申请一块连续的物理空间，减少磁盘读取时间。
- 索引可以加速表和表之间的连接。例如
join t1,t2 on ...，通过索引连接表。 - 唯一性索引可以保证表中每一行数据的唯一性。
- 索引可以加速分组和排序子句的速度。
- InnoDB、索引、锁：InnoDB中，二级索引（非主键索引）使用共享锁（读锁），主键索引使用排他锁（写锁）。
缺点：
- 索引的创建和维护需要耗费时间，且这种时间随着数据量的增加而增加。
- 索引要占用物理空间。如果是聚簇索引，那么占用的空间会更大（聚簇索引的叶子节点包含的该索引对应的记录）。
- 在对表中数据进行增、删、该时，该表的索引也要动态维护，这降低了维护的速度。（索引是B+树结构，主要作用是查询，修改的话需要修改数据和索引，速度较慢）
- 对于非常小的表，大部分情况下简单的全表扫描更加有效。
三. 索引的使用时机
1. 应该创建索引的列
- 经常需要查询的列。
- 作为主键的列。
- 经常作为连接依据的列（join操作），这些列一般是外键，可以加速连接的速度。
- 在经常需要进行范围判断、排序操作的列建立索引（<、<=、>、>=、between andin、order by）。因为索引是B+树结构，已经排好顺序了。
- where子句中经常使用的列创建索引，加快条件判断速度。
2. 不应该创建索引的列
- 查询中很少使用的列不应该创建索引。
- 经常修改的列不应当创建索引。
- 对于很少数据值或者重复值多的列也不应该创建索引。例如性别列，在查询时，结果集中的数据行占据了表中数据行很大一部分，此时增加索引并不能明显加快检索速度。
- 对于
text、image、bit类型的列不应当添加索引。这些列要么数量很大，要么取值很少。
四. 索引的结构
MySQL中索引主要有两种结构：
- B+树
- Hash表。
1. B+树
此处见博客园数据结构——B树、B+树：
B树、B+树 - Beasts777 - 博客园 (cnblogs.com)
2 Hash表
2.1 思想
通过hash算法，将索引作为关键字进行hash运算，对应的值就是数据。
通过使用Has表，我们不需要像B+树那样从叶子节点开始遍历，而是通过hash运算直接定位，速度极快。
Memory存储引擎使用的就是Hash作为索引结构。
2.2 使用场景
基于Hash本身的特性，Hash只能进行等值查询，例如=,in,<>。
而不能进行范围查询，这是因为原先是有序的键值，经过Hash算法后，很可能变成不连续的了。
因此凡是涉及范围运算的操作，Hash都会失效，例如>,<,between a and b,like 'xxx%'。（like操作实际上也是范围查询）
3. B+树索引和Hash索引的区别
- B+树索引可以进行范围查询，都要查询到叶子节点。而Hash只能进行等值查询。
- B+树索引的检索效率比较平均。而对于Hash索引而言，如果键值大都唯一，那么Hash索引查找速度极快，但如果有大量重复键值的情况下，Hash索引在找到该键所在位置需要对链表进行扫描，时间复杂度退化回\(O(n)\)
- B+树索引可以通过索引快速排序。而Hash索引不支持。
- B+树索引支持多列联合索引的最左匹配原则。而Hash索引不支持。
五. 索引的分类
根据不同的标准，索引有不同的划分：
1. 按照功能划分
- 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
- 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
- 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
- 全文索引：它查找的是文本中的关键词，主要用于全文检索。下面有介绍。
2. 按照列数划分
- 单列索引。
- 组合索引：一个索引由多列组成。查询时遵守Mysql的
最左前缀原则，也就是使用where时判断条件要按照建立索引时字段的排列方式放置索引，索引才会生效。
3. 按照物理实现划分
按照索引和数据的物理关系进行划分。
3.1 聚簇索引
3.1.1 定义：
依赖于B+树，将表的主键构造为一棵B+树，树的叶子节点存放的是表的行记录数据，那么该主键索引就是聚簇索引。
即：将数据和索引存放在了一棵树上，索引是非叶子节点，数据是叶子节点。找到了索引，也就找到了数据。
3.1.2 在引擎中的应用：
- InnoDB默认只有主键才是聚簇索引。其余都是非聚簇索引。
- 每张表最多只能有一个聚簇索引。
3.1.3 优缺点
优点：
- 数据访问更快。因为叶子节点就是行数据。
- 对于逐渐的排序查找和范围查找速度更快。
缺点：
- 插入速度严重依赖于插入顺序。按照主键的顺序插入是最快的方式，否则将会出现
页分裂现象，严重影响性能。因此对于InnoDB，我们一般会定义一个自增的ID列作为主键。 - 更新主键的代价很高。因为则还会导致被更新的行的移动。所以在InnoDB中，我们一般定义主键不可更新。
3.2 非聚簇索引
定义：
依赖于B+树，但是叶子节点存放的不是数据，而是该索引对应的该行记录的主键值。随后还需要通过该主键值在主键构成的聚簇索引B+树上再次进行查找才能找到数据。因此非聚簇索引又称为二级索引。
在引擎中的应用：
- InnoDB中除了主键都是非聚簇索引。
- MyISAM只支持非聚簇索引。
六. 索引实战
1. explain
作用：
通过explain命令，可以模拟优化器执行SQL语句的过程，从而了解MySQL是如何处理SQL语句的。
命令格式：
explain SQL语句
EG：
mysql> explain select * from test;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
其中的列含义为：
- id：选择标识符
- select_type：表示查询的类型
- table：输出结果集的表
- partitions：匹配的分区
- type：表示表的连接类型
- possible_keys：表示查询时，可能使用的索引
- key：表示实际使用的索引
- key_len：索引字段的长度。
- ref：列与索引的比较。
- rows：扫描出的行数（估算的行数）。
- filtered：按表条件过滤的百分比。
- Extra：直接情况的描述和说明。
详情暂时省略，留待后续补充
