什么是索引
索引（在mysql中也叫key）是存储引擎用于快速找到记录的一种数据结构。
索引的类型
不同引擎对不同类型的索引实现方式可能都会不同。
| 分类 | 名称 |
|---|---|
| 数据结构 | B+索引、Hash索引、R-tree索引等 |
| 存储层面 | 聚簇索引、非聚簇索引 |
| 逻辑层面 | 主键索引、普通索引、复合索引、唯一索引、空间索引 |
mysql支持的索引类型
- B-Tree索引
一般谈论的索引，若无特别说明，多半就是说的B-Tree索引，使用B-Tree数据结构来存储数据。Archive 5.1前不支持任何索引，直到5.1才支持单个自增列（AUTO_INCREMENT）的索引。NDB集群引擎使用T-Tree结构存储，InnoDB使用B+Tree。
假如有如下表，索引为last_name、first_name、dob 三列。
B-Tree 索引适合于全键值、键值范围或键前缀查找，其中键前缀查找只适用最左前缀查找,
索引列的顺序很关键，树节点是有序的，所以排序友好。- 全值匹配
如select * from people where last_name = ‘last’ and firs_name=‘first’ and dob=‘2018-01-01’ - 匹配最左前缀
- 匹配列前缀
- 匹配范围值
- 精确匹配某一列并范围匹配另一列
- 只访问索引的查询（覆盖索引）
- 全值匹配
- 哈希索引
哈希索引 （hash index） 基于哈希表实现，只有精确匹配索引所有列的查询才生效。对每一行数据，存储引擎都会对所有的索引列计算一个哈希码 hash code，存储在索引中，同时哈希表保存每个数据行指针。
哈希索引的限制- 哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引中的值来避免回表。不过访问内存中的行很快，所以这一点对性能影响不大。
- 哈希所以数据并不是按照索引值顺序存储的，所以无法用于排序。
- 哈希索引页不支持部分索引匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如建立（A,B）建立哈希索引，如果只查询数据列A，无法使用该索引。
- 哈希索引只支持等值比较的查询，包括 =、IN（）、<=>,也不支持范围查询，如where price >300
- 访问哈希索引的数据非常快，除非有很多哈希冲突（哈希碰撞）。当出现哈希冲突时候，存储引擎必须遍历链表中的所有行指针，逐行比较，直到找到所有符合条件的行。
- 如果哈希冲突很多的话，一些索引的维护操作代价很高。例如，在某个选择性很低（哈希冲突很多，字段重复值多）的列上建立哈希索引，那么当删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。
适用场景
因此哈希索引只适用于某些特定场合，儿一旦使用哈希索引，带来的性能提升很明显。
InnoDB-自适应哈希索引
当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样B-Tree索引也具有哈希索引的优点如快速的哈希查找。这是一个完全自动的，内部行为，用户无法控制或配置，但可以关闭。
- 空间数据索引（R-Tree）
Myisam支持空间索引，可以用作地理数据存储。 - 全文索引
–后续补充 - 其他索引类型
暂不做记录
索引的优点
索引可以让服务器快速定位到表指定位置，并且根据索引的数据结构不同，也有其他附加作用。
如B-Tree索引，
- 按照顺序存储数据，所以可以用来做order by 和 group by操作
- 因为索引中存储了实际的列值，无需回表
总结起来索引有三大优点：
- 索引大大的减少了服务器需要扫描的数据量
- 索引帮助服务器避免排序和临时表
- 索引可以将随机I/O变为顺序I/O
索引的缺点
- 需要占用额外空间
- 索引维护需要成本，插入数据后需要保持原有索引有序，所以会在一定程度上影响数据库性能
高性能索引策略
- 独立的列，独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。所以where 条件避免运算、函数
如 select * from user where age +1=10; - 前缀索引和索引选择性
有时候索引是很长的字符列，这会让索引变的很大很慢，解决方法
1）哈希索引 只支持等值比较查询
2） 前缀索引：通常索引开始的部分字符串，这样可以大大节约索引空间，从而提高索引效率。BLOB、TEXT和很长的varchar 必须使用前缀索引，因为Mysql不允许索引这些列的完整长度。
优缺点：前缀索引能使索引更快更小，但MySQL无法使用前缀索引做order by和group by，也无法使用前缀索引做覆盖扫描。 - 多列索引
多列索引需要特别注意索引顺序
实验是检验真理的唯一标准
** 一些基础语句和知识 **
--删除索引
mysql> alter table user_mi drop index name_age;
--创建索引
mysql> alter table user_mi add index name(name,age) ;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
--查看表索引语句
mysql> show index from user_mi \G;
*************************** 1. row ***************************
Table: user_mi 表名称
Non_unique: 0 用于显示该索引是否是唯一索引。若不是唯一索引，则该列的值显示为 1；若是唯一索引，显示为 0。
Key_name: PRIMARY 索引名称
Seq_in_index: 1 索引中的列序列号，从 1 开始计数。
Column_name: id 列名称
Collation: A 显示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类
Cardinality: 5 显示索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大
Sub_part: NULL 若列只是被部分编入索引，则为被编入索引的字符的数目。若整列被编入索引，则为 NULL
Packed: NULL 指示关键字如何被压缩。若没有被压缩，则为 NULL
Null: 用于显示索引列中是否包含 NULL。若列含有 NULL，则显示为 YES。若没有，则该列显示为 NO
Index_type: BTREE 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
Comment: 备注
Index_comment: 备注
** 多个列单独建索引 **
-- 测试表
mysql> show create table user_mi \G;
*************************** 1. row ***************************
Table: user_mi
Create Table: CREATE TABLE `user_mi` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) CHARACTER SET utf8mb4 NOT NULL,
`sex` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '性别1男2女 未知0',
`age` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `namge` (`name`),
KEY `age` (`age`)
) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=utf8
给测试表添加了两个独立列索引，试验表达式查询、函数、OR、AND
--非独立列 无法使用索引
mysql> explain select * from user_mi where age+1=13\G;
*************************** 1. row ***************************
id: 1
type: ALL
possible_keys: NULL
key: NULL 未使用索引
-- 函数参数 无法使用索引
mysql> explain select * from user_mi where CAST(age AS UNSIGNED)>3 \G
*************************** 1. row ***************************
id: 1
type: ALL
key: NULL 未使用索引
-- 出现了 隐式的字段类型强制转换
mysql> explain select * from user_mi where name=1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_mi
partitions: NULL
type: ALL
possible_keys: name_age_sex
key: NULL
-- OR条件查询 5.7 已经可以使用到索引
mysql> explain select * from user_mi where name='cdb1' or age=0 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_mi
partitions: NULL
type: index_merge
possible_keys: namge,age
key: namge,age 使用到了两个索引
建立联合索引
-- 建立 name+age+sex 三列联合索引
mysql> alter table user_mi add index name_age_sex(name,age,sex);
Query OK, 5 rows affected (0.03 sec)
mysql> show create table user_mi\G;
*************************** 1. row ***************************
Table: user_mi
Create Table: CREATE TABLE `user_mi` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`sex` tinyint(4) unsigned NOT NULL DEFAULT '1' COMMENT '性别1男2女 未知0',
`age` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `name_age_sex` (`name`,`age`,`sex`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=utf8
试验结果
| where语句 | explain索引使用情况 | 索引长度 | 判定使用到的索引列 | 说明 |
|---|---|---|---|---|
| name = ‘123’ | key: name_age_sex | key_len: 50 | name | 只使用到了name索引列 |
| name = ‘123’ and sex=2 或sex=2 and name=‘123’ | key: name_age_sex | key_len: 50 | name | 使用到了name，虽然顺序变了，mysql查询优化器自动调整了顺序，依旧是name列 |
| age = 12或者 sex=2 | key: NULL | key_len: NULL | 未使用到索引 | 违背最左匹配 |
| name=‘1’ and age=2 and sex=3 | key: name_age_sex | key_len: 52 | 使用到name、age、sex | |
| where name = ‘123’ and age>0 and sex=2 | key: name_age_sex | key_len: 51 | name、age | 使用到了name、age，sex不能用在范围之后 |
| name like’1c%’ and age=1 and sex=3 | key: name_age_sex | key_len: 52 | 使用到了name、age、sex索引 | |
| name like’c%’ and age=1 and sex=3 | key: NULL | key_len: NULL | 未使用索引 | 符合最左匹配规则，但未使用索引，原因猜测是由于数据name值大部分都是cd…字符串，所以查询优化器选择了全表扫描 |
| where name = ‘123’ | key: name_age_sex | key_len: 50 | name | |
| where name = ‘123’ | key: name_age_sex | key_len: 50 | name |
总结，建立了 name+age+sex 联合索引相当于建立了name 、name+age、namge+age+sex 三个索引，
有时候mysql优化器会根据情况放弃使用索引而全表扫描
聚簇索引
- 什么是聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行
下图展示了聚簇索引中的记录是如何存放如图叶子页包含了行的全部数据，但是节点只包含了索引列，InnoDB只通过主键聚集数据，也就是此图的节点是主键索引。
如果没有定义主键，InnoDB·会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式的定义一个主键作为聚簇索引 - 优点
- 可以把相关数据保存在一起，减少了磁盘I/O
- 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比费聚簇索引快。
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
- 缺点
- 聚簇数据最大限度的提高了I/O密集型应用的性能，但如果数据都在内存中，访问顺序没那么重要了，聚簇索引也就没什么优势了
- 插入速度严重依赖插入的顺序。按照主键的顺序插入式加载数据到InnoDB表中速度最快的方式。如果不是按照主键顺序加载数据，加载完成后最好使用 OPTIMIZE TABLE 命令重新组织一下表。
- 更新聚簇索引代价高，因为会强制InnoDB·将每个被更新的行移动到新位置。
- 基于聚簇索引的表插入新行，或者主键被更新导致需要移动时候，可能面临页分裂问题。当行的主键值要求必须将这一行插入到某个已满的页中时，，存储引擎会将该页分裂为两个页面来容纳改行，这就是一次页分裂操作，会导致占用额外的磁盘空间。
- 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
- 二级索引（非聚簇索引）肯能比想象中的要更大，因为二级索引的叶子节点包含了引用行的主键列。
- 二级索引访问需要两次索引查找，而不是一次，第一次通过二级索引叶子节点获取主键值，第二次通过这个主键值去聚簇索引查找数据，自适应哈希能减少这样的重复工作。
覆盖索引
- 什么是覆盖索引
如果一个索引包含（或者说覆盖）所有需要查询字段的值，我们就称之为覆盖索引 - 覆盖索引的 优点
- 索引条目通常远小于数据行的大小，可以极大的减少数据访问量
- 因为索引的是按照列值顺序存储的（至少单页如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多。
- 一些存储引擎如MyISam 在内存中只缓存索引，数据依赖操作系统，因此访问数据需要一次系统调用，可能会导致严重的性能问题。
- 由于InnoDB的聚簇索引，覆盖索引对InnoDB特别有用。InnoDB的耳机索引在叶子节点中保存了行的主键值，所以二级主键能够覆盖查询，则可以避免对主键索引的 二次查询。
当查询语句是覆盖索引查询则可以在explain的extra列看到 Using index
总结
索引是个非常复杂的话题！在选择索引和编写利用这些索引的查询时，如下三个原则需要始终记住
1. 单行访问时很慢的。特别是在机械硬盘存储中（SSD的随机I/O要快很多，但这一点仍成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那就浪费了很多工作。最好读取快中的能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。
2. 按照顺序访问范围数据是很快的，原因有两个，第一顺序I/O不需要多次磁盘寻道，所以比随机I/O快很多。第二，如果服务器能够按顺序读取数据那么不需要额外的排序操作，并且GROUP BY查询也无需再做排序和将行按组进行局和计算了。
3. 索引覆盖查询是很快的。如果一个索引包含了查询所需要的列，那么存储引擎就不需要再回表查询。这就避免了大量的单行访问，1已经写明单行访问很慢。
4. 对于范围查询，无法使用后边的索引列了，但多个等值条件查询不会出现这个情况
索引下推
在MySql 5.6版本中引入了一个新特性，叫做“索引条件推送（index condition pushdown)”，这也称为索引下推。那么索引下推是这个什么东东呢？其实从“索引条件推送”这个名字就可以表明，这个特性是可以在索引中的字段进行条件判断，然后过滤不满足条件的记录，减少回表的次数。
比如以上图中的数据为准，sql如下：
select * from person where name like ‘A%’ and age =19;
那么如果没有索引下推的情况下，首先会根据索引查询出名字以A开头的所有记录，然后查询出ID，然后回表去查询对应的ID记录，最后再判断age=19，返回满足条件的语句。因为满足A开头的记录有2条，所以这种情况下，会回表2次。
在索引下推情况下，InnoDB会在索引内部直接判断age=19是否满足条件，过滤掉不满足条件的记录，所以只返回了一条，也就是只需要回表一次。从而提高了性能。
https://zhuanlan.zhihu.com/p/77698098
高性能mysql 第三版 第五章
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
