随笔参考:
一. 什么是索引
索引对于表中的记录而言起到一个目录的作用,通过查询索引,我们可以快速的找到想要的记录,而不需要对表进行全面的扫描。就和查字典的原理类似。
二. 索引的优缺点
优点:
- 减少查找数据时需要扫描的数据量,从而大大加快数据的检索速度。(最主要原因)
- 避免查询时进行
排序
和创建临时表
。 - 可以将随机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:直接情况的描述和说明。
详情暂时省略,留待后续补充