1.索引 概述
1. 简介
索引是一种帮助数据库高效获取数据的数据结构；通过索引可以快速获取到符合条件的数据的内存地址，避免全表扫描
2. 索引的优缺点
优点：
- 可以快速地检索数据，降低数据IO成本
- 通过索引可以对数据进行排序，降低数据排序时的cpu成本
缺点：
- 索引也是需要占据空间的
- 索引提高了查询的效率，但是insert、update、delete的时候需要同时维护索引，降低了效率
2. 索引结构
Mysql的索引是在引擎层实现的，不同的存储引擎支持的索引类型也不同
| 介绍 | Innodb | MyISAM | MEMORY | |
|---|---|---|---|---|
| b+Tree索引 | 最常见的索引结构，大部分引擎都支持该索引 | 支持 | 支持 | 支持 |
| Hash索引 | 底层使用Hash表实现，仅支持等值查询，不支持范围查询 | - | 不支持 | 支持 |
| R-Tree索引 | 空间引擎，主要用于地理空间数据类型，使用较少 | - | 支持 | - |
| Full-text索引 | 全文索引，通过建立倒排索引，快速匹配文档，类似于ES；，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题 | 支持（5.6开始） | 支持 | - |
思考：为什么Innodb引擎使用B+树数据结构作为索引？
- 相比较二叉树，解决了顺序插入或大数据量下层级过深导致查询效率慢的问题
- 相比较B树，由于B树的根节点和叶子节点都存放有数据，占用空间过高，层数增加；B+树只有叶子节点才存放数据，非节点存放的是键值和指针
- 相比较hash索引……
思考：一颗B+树最多存放多少条数据？
以二阶树为例：
B+树中最小存储单元为页，每一个节点都在一个页中；
已知页的大小为16k，即
16*1024=16384字节；非叶子节点只存放key和指针；若key为bigint，占用大小为8字节；指针在mysql中默认6字节；加在一起共14字节；
那么非叶子节点一共可以存储
16384/14=1170个指针，指向1170个叶子节点；假设一条记录大小为1k（实践中也确实如此），那么一个叶子节点大约可以存放
16条数据；所以一个二阶树大约可以存放
1170*16=18720行记录，同理，一个三阶树大约可以存放1170\*1170\*16=21902400条思考：使用Hash索引时如果出现Hash冲突会怎样
Hash索引的结构：假设对emp表的name列创建索引
- 计算出每一行的Hash值；
- 通过Hash算法，计算出name的hash值，映射到对应的插槽上，并将行的hash值带过去（行的hash值可以当作内存地址指针理解，实际上并不是）；
- 如果两个name计算出的hash值相同，那么就会发生hash冲突，产生链表，类似Java中的HashMap
附上二叉树、B树、B+树结构图片；B树和B+树记住是满阶向上分裂
3. 索引分类
1. 根据功能划分
根据索引的功能可以分为：主键索引、唯一索引、普通索引、全文索引
| 含义 | 特点 | 关键字 | |
|---|---|---|---|
| 主键索引 | 针对表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
| 唯一索引 | 避免表中某列数据重复 | 在添加唯一约束时会被创建，可以有多个 | UNIQUE |
| 普通索引 | 快速定位查找数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
2. 根据存储形式划分
同时在innodb引擎中根据索引的存储形式又可以分为以下两种：
| 含义 | 特点 | |
|---|---|---|
| 聚集索引 | 将数据存储与索引放到了一起，索引结构的叶子节点保存了行数据 | 必须有，而且只有一个 |
| 二级索引 | 将数据存储和索引分开，叶子节点不保存行数据，只保存了对应的主键 | 可以存在多个；也称为非聚集索引、辅助索引 |
聚集索引选取规则：
- 如果存在主键，主键索引就是聚集索引
- 如果没有主键，将使用第一个唯一索引作为聚集索引
- 如果没有主键，也没有聚集索引，那么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;
5. 使用工具进行性能分析
1. 查看sql执行频率
可以查看当前数据库的增删改查执行次数，如果当前数据库查询为主，则可以进行优化
# 查看（全局/当前窗口）状态
show [global|session] status;
# 查看sql执行次数相关状态
show global like 'Com_______';
2. 使用慢查询日志
慢查询日志默认为关闭
该配置位于配置文件my.cnf中；下面是关于该项配置的命令
# 查询慢查询日志记录是否打开；结果为ON即为打开
show variables like 'show_query_log';
# 查看慢查询时长，默认为10s，达到10s即为慢日志会被记录下来
show variables like 'long_query_time';
# 编辑配置文件，设置show_query_log=ON,设置long_query_time=10
vi /etc/my.cnf
# 编辑完后进行重启
systemctl restart mysqld
# 重启完后慢查询将会被记录到/var/lib/mysql/localhost-slow.log日志文件中
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
4. 查看explain执行计划(最重要)
可以使用explain或者desc命令获取MySQL如何执行select语句的执行计划
# 查看后面的select语句执行计划
explain select 字段列表 from 数据源 where 条件;
# 同上
desc select 字段列表 from 数据源 where 条件;
explain执行计划中各字段含义：
id：执行查询的序列号，表示查询中执行select子句或者是操作表的顺序；id相同，执行顺序从上往下，id不同，数值越大的越先执行select_type：表示查询的类型；常见的取值范围有SIMPLE（简单查询即不使用连接或者子查询）、PRIMARY（主查询即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY(select/where之后包含了子查询)；partitions：使用到的分区type：表示连接类型，性能从好到差依次为：NULL、system、const、eq_ref、ref、range、index、all；通常const表示使用了主键或者唯一索引，只有不查表的时候才会是NULL，system通常是查询系统表，ref表示使用了普通索引，all表示全表扫描possible_key：可能用到的索引，一个或者多个Key：实际用到的索引，如果为NULL表示没有用到索引Key_len：表示索引中使用的字节数；该值为索引字段最大可能长度，并非实际使用长度，在不损失精度的情况下，该值越小越好ref：指引用的东西，可以有多个，即某个表“=”号后的东西；如果是=别的表的某一列则是表名.列名，如果是一个常量则是CONSTrows：MySQL认为必须要执行查询的行数；在innodb引擎中这是一个估计值，可能不总是准确filtered：表示返回结果的行数占所需要读取的行数的百分比，filtered的值越大越好
查看执行计划
6. 索引的使用规则
1. 左侧原则（最左前缀法则）
如果是联合索引，则需要遵守左侧原则；查询从索引的最左列开始，并且不能跳过索引中的列，如果跳过索引中的列，那么跳过的列后的列将会失效（部分失效）
- 如果查询条件不包含最左侧的索引列，那么索引会完全失效（因为没有最左侧的列无法判断其余的列在索引中的位置，最左侧的列在索引中排序优先级最高）
- 如果查询条件包含最左侧的索引列，但是跳过了中间列，那么中间列后的索引将会失效（称为索引部分失效）
2. 索引的失效情况
- ‘>‘和’<‘会导致范围查询右侧的列索引失效，如果业务允许，尽量使用’>=‘和’<=’
- 不要在索引列上进行运算操作，否则索引将失效
- 字符串类型的列，在查询时如果不加单引号将会导致索引失效
- 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;
5. 覆盖索引
如果查询的列中在联合索引中都能够找到，不需要进行回表查询，那么就称之为覆盖索引
所以，使用select * 会导致回表查询，尽量避免使用回表查询
对于查询场景较为复杂的表，我们尽量使用联合索引，形成覆盖索引，避免回表查询
explain中关于
extra知识点：
- using index condition：查找使用了索引，但是需要回表查询补全需要查找的列
- 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));
截取的n根据索引的选择性决定，选择性指的是不重复的索引数量（基数）和表总的记录数的比值；选择性越接近1表示重复的索引key越少，效率越高；在选择性相同的情况下，n越小越好（节省空间）
# 计算选择性,不截取email
select count(distinct email)/ count(*) from users;
# 计算选择性，截取前5位作为索引的key
select count(distinct substring(eamil, 1, 5)) from users;
前缀索引的查询流程
7. 索引的设计原则
针对数据量较大，且多为DQL操作的表建立索引
DML操作时也需要维护索引
针对经常作为查询条件（where）、排序（order by）、分组（group by）的列建立索引
尽量选择区分度较高的列作为索引，区分度越高索引的效率越高；尽量建立唯一索引
例如身份证号和性别，身份证号更适合作为索引
如果字符串类型的字段长度较长，可以使用前缀索引
否则索引太冗余并且单个key占用内存太大增加树的高度，降低效率
尽量使用联合索引，减少单列索引
如果联合索引设计的较好那么很多时候可以覆盖索引，避免回表查询
要控制索引的数量，并不是多多益善
首先索引也需要占用磁盘的空间，其次DML操作也需要维护索引
如果索引列不能存储null值尽量在创建表时使用not null约束
这样子便于sql优化器知道每列是否存在null值，便于选择索引进行最有效的查询
扫一扫
213
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
