07 索引

1.索引 概述

1. 简介

索引是一种帮助数据库高效获取数据的数据结构；通过索引可以快速获取到符合条件的数据的内存地址，避免全表扫描

2. 索引的优缺点

优点：

1. 可以快速地检索数据，降低数据IO成本
2. 通过索引可以对数据进行排序，降低数据排序时的cpu成本

缺点：

1. 索引也是需要占据空间的
2. 索引提高了查询的效率，但是insert、update、delete的时候需要同时维护索引，降低了效率

2. 索引结构

Mysql的索引是在引擎层实现的，不同的存储引擎支持的索引类型也不同

思考：为什么Innodb引擎使用B+树数据结构作为索引？

1. 相比较二叉树，解决了顺序插入或大数据量下层级过深导致查询效率慢的问题
2. 相比较B树，由于B树的根节点和叶子节点都存放有数据，占用空间过高，层数增加；B+树只有叶子节点才存放数据，非节点存放的是键值和指针
3. 相比较hash索引……

思考：一颗B+树最多存放多少条数据？

以二阶树为例：

1. B+树中最小存储单元为页，每一个节点都在一个页中；

2. 已知页的大小为16k，即16*1024=16384字节；

3. 非叶子节点只存放key和指针；若key为bigint，占用大小为8字节；指针在mysql中默认6字节；加在一起共14字节；

4. 那么非叶子节点一共可以存储16384/14=1170个指针，指向1170个叶子节点；

5. 假设一条记录大小为1k（实践中也确实如此），那么一个叶子节点大约可以存放16条数据；

所以一个二阶树大约可以存放1170*16=18720行记录，同理，一个三阶树大约可以存放1170\*1170\*16=21902400条

思考：使用Hash索引时如果出现Hash冲突会怎样

Hash索引的结构：假设对emp表的name列创建索引

1. 计算出每一行的Hash值；
2. 通过Hash算法，计算出name的hash值，映射到对应的插槽上，并将行的hash值带过去（行的hash值可以当作内存地址指针理解，实际上并不是）；
3. 如果两个name计算出的hash值相同，那么就会发生hash冲突，产生链表，类似Java中的HashMap

附上二叉树、B树、B+树结构图片；B树和B+树记住是满阶向上分裂

3. 索引分类

1. 根据功能划分

根据索引的功能可以分为：主键索引、唯一索引、普通索引、全文索引

2. 根据存储形式划分

同时在innodb引擎中根据索引的存储形式又可以分为以下两种：

聚集索引选取规则：

1. 如果存在主键，主键索引就是聚集索引
2. 如果没有主键，将使用第一个唯一索引作为聚集索引
3. 如果没有主键，也没有聚集索引，那么InnoDB将会自动生成一个rowid作为隐藏的聚集索引

由于聚集索引中存放着完整的行数据，所以只需要通过一次查询索引即可获取数据；而二级索引中只存放了对应的主键，所以必须要先查到对应的主键，再通过聚集索引去查询，需要进行两次，这个过程称为回表查询

回表查询示意图

4. 语法

# 查看该表的索引
show index from table_name;
# 创建索引
create [unique|fulltext] index index_name on table_name(col1, ...);
# 删除索引
drop index index_name on table_name;

# 示例
show index from tb_user;
create unique index test_unique_index on tb_user(id);
create unique index test_unique_index2 on tb_user(id, name);
create index test_common_index on tb_user(id);
drop index test_common_index on tb_user;
1
2
3
4
5
6
7
8
9
10
11
12
13

5. 使用工具进行性能分析

1. 查看sql执行频率

可以查看当前数据库的增删改查执行次数，如果当前数据库查询为主，则可以进行优化

# 查看（全局/当前窗口）状态
show [global|session] status;
# 查看sql执行次数相关状态
show global like 'Com_______';
1
2
3
4

2. 使用慢查询日志

慢查询日志默认为关闭

该配置位于配置文件my.cnf中；下面是关于该项配置的命令

# 查询慢查询日志记录是否打开；结果为ON即为打开
show variables like 'show_query_log';
# 查看慢查询时长，默认为10s，达到10s即为慢日志会被记录下来
show variables like 'long_query_time';
1
2
3
4

# 编辑配置文件，设置show_query_log=ON,设置long_query_time=10
vi /etc/my.cnf
# 编辑完后进行重启
systemctl restart mysqld
# 重启完后慢查询将会被记录到/var/lib/mysql/localhost-slow.log日志文件中
1
2
3
4
5

3. 使用profile查看sql执行概况

profile工具默认关闭，通过have_profiling参数可以看到当前mysql是否支持profile操作；可以通过set profiling = 1开启

# 查看是否支持profile操作
select @@have_profiling;
# 查看profiling是否开启
show variables like 'profiling';
# 设置profiling为开启状态
set [global|session] profiling=1;

# 查看执行概况，返回多个sql的query_id和执行用时
show profiles;
# 查看单个sql的执行各阶段用时详情
show profile for query query_id;
# 查看单个sql的执行各阶段用时详情和cpu占用
show profile cpu for query query_id
1
2
3
4
5
6
7
8
9
10
11
12
13

4. 查看explain执行计划(最重要)

可以使用explain或者desc命令获取MySQL如何执行select语句的执行计划

# 查看后面的select语句执行计划
explain select 字段列表 from 数据源 where 条件;
# 同上
desc select 字段列表 from 数据源 where 条件;
1
2
3
4

explain执行计划中各字段含义：

1. id：执行查询的序列号，表示查询中执行select子句或者是操作表的顺序；id相同，执行顺序从上往下，id不同，数值越大的越先执行
2. select_type：表示查询的类型；常见的取值范围有SIMPLE（简单查询即不使用连接或者子查询）、PRIMARY（主查询即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY(select/where之后包含了子查询)；
3. partitions：使用到的分区
4. type：表示连接类型，性能从好到差依次为：NULL、system、const、eq_ref、ref、range、index、all；通常const表示使用了主键或者唯一索引，只有不查表的时候才会是NULL，system通常是查询系统表，ref表示使用了普通索引，all表示全表扫描
5. possible_key：可能用到的索引，一个或者多个
6. Key：实际用到的索引，如果为NULL表示没有用到索引
7. Key_len：表示索引中使用的字节数；该值为索引字段最大可能长度，并非实际使用长度，在不损失精度的情况下，该值越小越好
8. ref：指引用的东西，可以有多个，即某个表“=”号后的东西；如果是=别的表的某一列则是表名.列名，如果是一个常量则是CONST
9. rows：MySQL认为必须要执行查询的行数；在innodb引擎中这是一个估计值，可能不总是准确
10. filtered：表示返回结果的行数占所需要读取的行数的百分比，filtered的值越大越好

查看执行计划

6. 索引的使用规则

1. 左侧原则（最左前缀法则）

如果是联合索引，则需要遵守左侧原则；查询从索引的最左列开始，并且不能跳过索引中的列，如果跳过索引中的列，那么跳过的列后的列将会失效（部分失效）

1. 如果查询条件不包含最左侧的索引列，那么索引会完全失效（因为没有最左侧的列无法判断其余的列在索引中的位置，最左侧的列在索引中排序优先级最高）
2. 如果查询条件包含最左侧的索引列，但是跳过了中间列，那么中间列后的索引将会失效（称为索引部分失效）

2. 索引的失效情况

1. ‘>‘和’<‘会导致范围查询右侧的列索引失效，如果业务允许，尽量使用’>=‘和’<=’
2. 不要在索引列上进行运算操作，否则索引将失效
3. 字符串类型的列，在查询时如果不加单引号将会导致索引失效
4. or查询的时候，如果or前的条件有索引，or后的列没有索引，将会导致涉及的索引都不会被用到

3. 数据分布的影响

如果MySQL评估使用索引比全表扫描更慢，那么则不使用索引

如果根据索引查询到的结果较多（例如占记录总数的一半以上），那么将不会使用索引，直接全表扫描

例如一个表中的某个索引列大多数为null，那么根据索引列查询is not null的时候，索引依旧会生效；反之如果查询is null的时候会失效，因为大多数都为null不如直接全表扫描

4. 索引提示

即通过sql语法，提示MySQL使用或者不使用索引，或者强制MySQL使用某个索引来达到优化的目的

# 提示使用某个索引；有可能不听你的
select * use index(index_name) from table_name;
# 不使用某个索引
select * ignore index(index_name) from table_name;
# 强制使用某个索引
select * force index(index_name) from table_name;
1
2
3
4
5
6

5. 覆盖索引

如果查询的列中在联合索引中都能够找到，不需要进行回表查询，那么就称之为覆盖索引

所以，使用select * 会导致回表查询，尽量避免使用回表查询

对于查询场景较为复杂的表，我们尽量使用联合索引，形成覆盖索引，避免回表查询

explain中关于extra知识点：

1. using index condition：查找使用了索引，但是需要回表查询补全需要查找的列
2. using where, using index：查找的列在索引中都能够找到，不需要徽标查询

思考：

一张表有四个字段：id、name、password、status，那么如何建立索引才能增加下面SQL的执行效率？

select id, name, password from user where name = ‘赵四’；

针对name和password建立联合索引，key中包含name和password，挂载的有id，形成覆盖索引，避免了回表查询

覆盖索引示意图

6. 前缀索引

对于一些字符串类型的列，如果列的长度较长，那么对该列建立索引所耗费的磁盘空间较大，且由于单个key较大导致树的层数增加降低查找的效率；我们可以使用前缀索引来增加效率

# 建立前缀索引,截取列的前n位作为前缀索引的key
create index ids_xxx on tablename(column_name(n));
1
2

截取的n根据索引的选择性决定，选择性指的是不重复的索引数量（基数）和表总的记录数的比值；选择性越接近1表示重复的索引key越少，效率越高；在选择性相同的情况下，n越小越好（节省空间）

# 计算选择性,不截取email
select count(distinct email)/ count(*) from users;
# 计算选择性，截取前5位作为索引的key
select count(distinct substring(eamil, 1, 5)) from users;
1
2
3
4

前缀索引的查询流程

7. 索引的设计原则

1. 针对数据量较大，且多为DQL操作的表建立索引

   DML操作时也需要维护索引

2. 针对经常作为查询条件（where）、排序（order by）、分组（group by）的列建立索引

3. 尽量选择区分度较高的列作为索引，区分度越高索引的效率越高；尽量建立唯一索引

   例如身份证号和性别，身份证号更适合作为索引

4. 如果字符串类型的字段长度较长，可以使用前缀索引

   否则索引太冗余并且单个key占用内存太大增加树的高度，降低效率

5. 尽量使用联合索引，减少单列索引

   如果联合索引设计的较好那么很多时候可以覆盖索引，避免回表查询

6. 要控制索引的数量，并不是多多益善

   首先索引也需要占用磁盘的空间，其次DML操作也需要维护索引

7. 如果索引列不能存储null值尽量在创建表时使用not null约束

   这样子便于sql优化器知道每列是否存在null值，便于选择索引进行最有效的查询