MySQL学习笔记-索引
索引
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
-
无索引的查找：全表扫描(将整张表遍历一遍)，性能极低。
-
有索引的查找：数据库系统在存储数据的同时会维护一种数据结构(如二叉树)，当需要查找时，利用该数据结构进行查找，性能较高。
-
索引的优缺点
一. 索引结构
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构。
- 索引在存储引擎中的支持情况
- 平常所说的索引，如果没有特别指明，一般都是说B+树结构组织的索引。
1. B+树
1.1 二叉树
-
一种经典的数据结构。
-
二叉树的两个缺点：
- 顺序存储二叉树时，会形成一条链表，二叉树的深度很大，效率很低。
- 二叉树的度不大于2，在数据库中存有大量数据的时候深度很大，效率很低。
1.2 红黑树
-
一种自平衡的特殊二叉树。
-
可以解决二叉树可能形成链表的缺点，但是依旧存在数据量大时深度很大的问题。
1.3 B树(平衡多路查找树)
- 一种自平衡的树。可以解决二叉树的两个缺点。
- 一个节点可以拥有两个以上的子节点。
1.4 B+树
- B树的变种。
- B+树与B树的区别：
- 所有的元素都会出现在叶子节点。
- 叶子节点形成了一条单向链表。
1.5 MySQL中的B+树
- 在MySQL中，对B+树进行了优化。在原有基础上，叶子节点改为了双向循环链表，提高区间访问的性能。
2. Hash
哈希索引就是采用一定的Hash算法，将键值换算成新的Hash值，映射到对应的槽位上，然后存储在Hash表中。
如果两个(或多个)键值映射到同一个槽位上，产生了Hash冲突，可以通过链表解决。
2.1 Hash索引的特点
- 只能用于对等比较(=，in)，不支持范围查询(between，>，<，...)。
- 无法利用索引完成排序操作。
- 查询效率高，通常只需要一次检索(不出现hash冲突)，效率高于B+树。
2.2 存储引擎支持
在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+树索引在指定条件下自动构建的。
二. 索引分类
- 在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
- 聚集索引的选取规则：
- 如果存在主键，主键索引就是聚集索引。
- 如果不存在主键，将适用第一个唯一(UNIQUE)索引作为聚集索引。
- 如果不存在主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
- 聚集索引和二级索引的示意图：
- 在查找时，先走二级索引，找到对应的主键后，再走聚集索引，找到对应的整个行。（回表查询）
三. 索引语法
1. 创建索引
create [unique|fulltext] index {索引名} on {表名} ({字段名},...);
- unique 唯一索引 ｜fulltext 全文索引 ｜不加这两个则说明是常规索引。
- 一个索引可以关联多个字段，如果一个索引只关联一个字段，叫单列索引，如果关联多个字段，叫联合索引（组合索引）。
- 联合索引的字段顺序是有讲究的。
- 索引名一般的命名规则：idx _ 表名 _ 字段名
2. 查看索引
show index form {表名};
3. 删除索引
drop index {索引名} on {表名};
四. SQL性能分析
做性能分析是为了做SQL优化，SQL主要是做查询优化，因为查询操作比增删改多，查询优化的关键在于索引。
1. SQL执行频率
# 查看当前数据库的增删改查的访问频次
show global status like 'Com_______';
# 模糊匹配'Com'后面是7个下划线
- 根据执行频率来判断SQL优化需要在哪方面进行，也就是说这个数据库哪个操作频率高就优化哪个操作。
2. 慢查询日志
慢查询日志记录了所有执行时间超过制定参数 (long_query_time，单位：秒，默认10秒) 的所有sql语句的日志
慢查询日志用于找到执行慢的sql语句，进行针对性优化。
2.1 开启慢查询日志
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf) 中配置。
- 查询是否开启
show variables like 'slow_query_log';
- 开启慢查询日志
在MySQL的配置文件(/etc/my.cnf) 中配置如下信息：
# 开启MySQL慢查询日志开关
show_query_log = 1
# 设置慢查询日志的时间为2秒，SQL语句执行时间超过2秒就会被记录
long_query_time = 2
配置完毕后，需要重启服务器。
# 重启服务器
systemctl restart mysqld
2.2 查看慢查询日志
# 慢查询日志存放地址 Linux下
/var/lib/mysql/localhost-slow.log
3. profile详情
Show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
3.1 查看MySQL是否支持profile操作
select @@have_profiling;
3.2 打开profile开关
# 查看是否打开
select @@profiling;
# 打开profile开关
set [session|global] profiling = 1;
-
profile默认是关闭的。
-
[session|global] 可以指定是会话级别的还是全局的。
3.3 查看profile详情
# 查看每一条SQL的耗时基本情况
show profiles;
# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的SQL语句CPU的使用情况
show profile cup for query query_id;
- query_id指的是在profiles中的某一条指令的id，可以在show profiles中看到。
4. explain执行计划
explain 或者 desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
# 直接在select语句之前加上关键字explain/desc
explain select {字段列表} from {表名} where {条件};
- explain执行计划各字段含义
五. 索引使用
1. 最左前缀法则
-
如果索引了多列(联合索引)，要遵循最左前缀法则。最左前缀法则是指查询从索引的最左列开始，并且不跳过索引中的列。
-
如果跳跃某一列，索引将部分失效（后面的字段索引失效）。
-
查询时左边字段存在即符合最左前缀法则，不管它在代码中的位置。
2. 范围查询
- 联合索引中，出现范围查询（> , <），范围查询右侧的列索引失效。
- 用（>= , <=）不会出现失效情况。
3. 索引列运算
- 不要在索引列上进行运算操作，否则索引将失效。
4. 字符串不加引号
- 字符串类型字段使用时，不加引号，索引将失效。
5. 模糊查询
- 如果是尾部进行模糊查询，索引不会失效；如果是头部进行模糊查询，索引会失效。
6. or连接的条件
- 用or分割开的条件，如果or前的条件中的列有索引，二后面的列没有索引，那么涉及的索引都不会被用到。
- 只要把没有索引的建立一个索引就可以解决失效问题。
7. 数据分布影响
- 如果MySQL评估使用索引比全表更慢，则不使用索引。
8. SQL提示
SQL提示是优化数据库的一个重要手段。在SQL语句中加入一些人为的提示来达到优化操作的目的。
8.1 use index
- 使用指定索引(建议)
select * from {表名} use index({索引名}) where...;
8.2 ignore index
- 不使用某个索引
select * from {表名} ignore index({索引名}) where...;
8.3 force index
- 使用指定索引(必须)
select * from {表名} force index({索引名}) where...;
9. 覆盖索引
-
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少使用select *。
-
使用覆盖索引和没有使用覆盖索引，在explain中的Extra列有不一样的提示：
- （没使用）using index condition : 查找使用了索引，但是需要回表查询数据。
- （使用了）using where; using index : 查找使用了索引，但是需要的数据都在索引列中能够找到，所以不需要回表查询数据。
-
覆盖索引直接在二级索引中获取了返回所需的所有数据，所以不需要回表查询，查询速度快。
-
如果不是覆盖查询，在二级索引中查询到数据后，还需要拿到对应数据的主键，到聚焦索引中查询行数据，这就叫回表查询，所以速度慢。
10. 前缀索引
当字段类型为字符串时，有时候需要存储很长的字符串，如果建立索引，索引会变得很大，浪费大量磁盘IO，影响查询效率。
此时可以只用字符串的一部分前缀来建立索引（前缀索引），可以大大节约索引空间，从而提高效率。
10.1 创建前缀索引
create index {索引名} on {表名}({字段名}({前缀的字符数}));
10.2 前缀长度的选择
-
可以根据索引的选择性来决定。
-
选择性：不重复的索引值和数据表的记录总数的比值。索引选择性越高，效率越高。唯一索引的选择性是1，是性能最好的。
-
求选择性：
select count(distinct substring({字段名},1,{截取长度}))/count(*) from {表名};
11. 单列索引和联合索引
-
单列索引：一个索引只包含单个列
-
联合索引：一个索引包含了多个列
-
在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议使用联合索引。
-
联合索引的存储结构：
六. 索引设计原则
