携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第21天，点击查看活动详情
⭐️前面的话⭐️
✉️坚持和努力一定能换来诗与远方！
💭推荐书籍：📚《王道408》，📚《深入理解 Java 虚拟机-周志明》，📚《Java 核心技术卷》
💬算法刷题：✅力扣🌐牛客网
🎈Github
🎈码云Gitee
8. 索引index ⭐️
索引概述、优缺点
索引index是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这
些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
最典型的例子就是查新华字典，通过查找目录快速定位到查找的字。
B + Tree
红黑树
二叉树
B - Tree
备注：上述二叉树索引结构的只是一个示意图，并不是真实的索引结构。
优点：
- 提高数据检索的效率，降低数据库的IO成本。
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
缺点：
- 索引列也是要占用空间的。
- 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。
索引结构
我们平常所说的索引，如果没有特别指明，都是指B+树结构组织的索引。
| 索引 | InnoDB | MyISAM | Memory |
|---|---|---|---|
| B+Tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-tree索引 | 不支持 | 支持 | 不支持 |
| Full-text索引 | 5.6版本之后支持 | 支持 | 不支持 |
二叉树
二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。 大数据量情况下，层级较深，检索速度慢。
红黑树：大数据量情况下，层级较深，检索速度慢。
B-Tree（多路平衡查找树）
以一颗最大度数（max-degree）为5(5阶)的b-tree为例(每个节点最多存储4个key，5个指针)：
知识小贴士: 树的度数指的是一个节点的子节点个数。
插入 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 数据为例。
具体动态变化的过程可以参考网站: www.cs.usfca.edu/~galles/vis…
B+Tree
以一颗最大度数（max-degree）为4（4阶）的b+tree为例：
插入 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 数据为例。
相对于B-Tree区别:
①. 所有的数据都会出现在叶子节点
②. 叶子节点形成一个单向链表
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺
序指针的B+Tree，提高区间访问的性能。
Hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。
Hash索引特点：
- Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，...）
- 无法利用索引完成排序操作
- 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引
存储引擎支持：
在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件
下自动构建的。
为什么InnoDB存储引擎选择使用B+tree索引结构?（即和另外3种进行对比）
- 相对于二叉树，层级更少，搜索效率高；
- 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
- 相对Hash索引，B+tree支持范围匹配及排序操作；
索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | primary |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | unique |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | fulltext |
（1）主键索引
我们建表的时候，例如下面这个建表语句
CREATE TABLE `t_blog_sort` (
`uid` varchar(32) NOT NULL COMMENT '唯一uid',
`sort_name` varchar(255) DEFAULT NULL COMMENT '分类内容',
`content` varchar(255) DEFAULT NULL COMMENT '分类简介',
`create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',
`status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '状态',
`sort` int(11) DEFAULT '0' COMMENT '排序字段，越大越靠前',
`click_count` int(11) DEFAULT '0' COMMENT '点击数',
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='博客分类表';
这里面有使用到 PRIMARY KEY (uid)，这就是主键索引
（2）唯一索引
唯一索引 类似于普通索引，索引列的值必须唯一
唯一索引和主键索引的区别就是，唯一索引允许出现空值，而主键索引不能为空
create unique index index_name on table(column)
或者创建表时指定
unique index_name column
（3） 普通索引
当我们需要建立索引的字段，既不是主键索引，也不是唯一索引
那么就可以创建一个普通索引
create index index_name on table(column)
或者创建表时指定
create table(..., index index_name column)
（4）全文索引
lunce、solr和ElasticSearch就是做全文检索的，里面涉及到了倒排索引的概念，mysql很少使用全文索引。
要用来查找文本中的关键字，不是直接与索引中的值相比较，像是一个搜索引擎，配合 match against 使用，现在只有char，varchar，text上可以创建索引，在数据量比较大时，先将数据放在一个没有全文索引的表里，然后在利用create index创建全文索引，比先生成全文索引在插入数据快很多。
（5）组合索引
目前，在业务不是特别复杂的时候，可能使用一个列作为索引，或者直接采用主键索引即可，但是如果业务变得复杂的时候，就需要用到组合索引，通过对多个列建立索引。
组合索引的用处，假设我现在表有个多个字段：id、name、age、gender，然后我经常使用以下的查询条件
select * from user where name = 'xx' and age = xx
这个时候，我们就可以通过组合 name 和 age 来建立一个组合索引，加快查询效率，建立成组合索引后，我的索引将包含两个key值
在多个字段上创建索引，遵循最左匹配原则
alter table t add index index_name(a,b,c);
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引(Clustered Index) | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有,而且只有一个 |
| 二级索引(Secondary Index) | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
- 如果存在主键，主键索引就是聚集索引。
- 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
主键构建的主键索引就是聚集索引；叶子结点挂的数据就是这一行的数据。
如果要针对name字段再建立一个索引，因为聚集索引只会有一个，所以name字段建的索引称为二级索引。叶子结点下面挂的是name
值对应的这一行的的主键值
第七分钟
select * from user where name = 'arm';
-- 不会走聚集索引，会走二级索引
-- A比L小，到Geek，再到Arm，拿到10.
-- 要获取所有数据，再根据聚集索引查找。这一过程称为回表查询：先走二级索引找到对应的主键值，再根据主键值到聚集索引中拿到这一行的行数据。
聚集索引和二级索引的结构一定要理解，这个在SQL优化中是非常重要的。很多SQL优化策略的底层原理。
看2道思考题：
- 一下SQL语句，哪个执行效率高？为什么？（1）
-- id为主键，name字段创建的有索引
select * from user where id =10;
select * from user where name = 'Arm';
- InnoDB主键索引的B+tree高度为多高呢?
假设: 一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。
高度为2：
n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为 1170
1171* 16 = 18736
高度为3：
1171 * 1171 * 16 = 21939856
2千多万条记录，B+树高度也才为3。效率很高。
索引语法
-- 创建索引
create [unique | fulltext] index index_name on table_name(index_col_name,...);
-- 查看索引
show index from table_name;
-- 删除索引
drop index index_name on table_name;
案例：按照下列的需求，完成索引的创建
-- 1. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
-- 2. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
-- 3. 为profession、age、status创建联合索引。
-- 4. 为email建立合适的索引来提升查询效率。
create index idx_user_name on tb_user(name);
create unique index idx_user_phone on tb_user(phone);
create index idx_user_profession_age_status on tb_user(profession,age,status);
create index idx_user_email on tb_user(email);
-- 删除索引
drop index idx_user_email on tb_user;
SQL性能分析
SQL执行频率（查询为主还是增删改为主？）
MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前
数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：
show global status like 'Com_______';
慢查询日志（优化哪些select语句）（定位执行效率低的SQL）
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
# 查看当前慢查询日志开关
show variables like 'slow_query_log';
# 开启MySQL慢查询日志开关
slow_query_log=1
# 设置慢日志的时间为 2秒， 语句执行时间超过 2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。
# mac开启慢查询日志: https://www.cnblogs.com/woods1815/p/11829659.html
mysql> set global slow_query_log='on';
SET GLOBAL long_query_time = 3; # 这里需要注意下，long_query_time参数设置后需要下次会话后才生效，当前会话查询还是原来的数值
profile详情（每一条sql的耗时，以及耗时在哪一个阶段）
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持
profile操作：
select @@have_profiling;
默认profiling是关闭的，可以通过set语句在session/global级别（会话/全局级别）开启profiling：
set profiling=1;
执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：
-- 查看每一条 SQL 的耗时基本情况
show profiles;
-- 查看指定query_id的 SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的 SQL语句cpu的使用情况
show profile cpu for query query_id;
以上三种方式从执行时间的角度出发。并不能真正评判一条sql的性能。
explain执行计划（重要）
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
-- 直接在select语句之前加上关键字explain / desc
explain select 字段列表 from 表名 where 条件;
EXPLAIN 执行计划各字段含义：（重点字段是type、possible_key、key、key_len）
- Id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。
-
select_type：表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外
层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
-
type：表示连接类型，性能由好到差的连接类型为NULL、system、const（主键或唯一索引）、eq_ref、ref（非唯一性索引）、range、 index、all （全表扫描，性能低）。 （尽量往前优化！）
-
possible_key：显示可能应用在这张表上的索引，一个或多个。
-
Key：实际使用的索引，如果为NULL，则没有使用索引。
-
Key_len：表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。
-
rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
-
filtered：表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。
索引使用（如何正确使用、使用与否）
使用索引
MySQL每次只使用一个索引，与其说 数据库查询只能用一个索引，倒不如说，和全表扫描比起来，去分析两个索引 B+树更耗费时间，所以where A=a and B=b 这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。
- 主键，unique字段
- 和其他表做连接的字段需要加索引
- 在where 里使用 >, >=, = , <, <=, is null 和 between等字段。
- 使用不以通配符开始的like，where A like ‘China%’
- 聚合函数里面的 MIN()， MAX()的字段
- order by 和 group by字段
何时不使用索引
- 表记录太少
- 数据重复且分布平均的字段（只有很少数据的列）
- 经常插入、删除、修改的表要减少索引
- text，image 等类型不应该建立索引，这些列的数据量大（加入text的前10个字符唯一，也可以对text前10个字符建立索引）
- MySQL能估计出全表扫描比使用索引更快的时候，不使用索引
索引何时失效
- 组合索引为使用最左前缀，例如组合索引（A，B），where B = b 不会使用索引
- like未使用最左前缀，where A like "%China"
- 搜索一个索引而在另一个索引上做 order by， where A = a order by B，只会使用A上的索引，因为查询只使用一个索引。
- or会使索引失效。如果查询字段相同，也可以使用索引。例如 where A = a1 or A = a2（生效），where A=a or B = b （失效）
- 在索引列上的操作，函数upper()等，or、！ = （<>）,not in 等
验证索引效率（索引对查询效率提升极大）
-- 在未建立索引之前，执行如下SQL语句，查看SQL的耗时。-------------------21s
select * from tb_sku where id = 1;
select * from tb_sku where id = 1\G;
select * from tb_sku where sn = '100000003145001';
-- 针对字段创建索引(1千万数据构建B+树，耗时1分钟11秒)
create index idx_sku_sn on tb_sku(sn);
-- 然后再次执行相同的SQL语句，再次查看SQL的耗时。-------------------0.01s
select * from tb_sku where sn = '100000003145001';
想到面试题：sql优化之正确使用索引
1 最左前缀法则（针对联合索引）（索引失效或部分失效）
如果索引了多列（联合索引），要遵守最左前缀法则（查询从索引的最左列开始，并且不跳过索引中的列）
如果跳跃某一列，索引将部分失效(后面的字段索引失效)。
最左边的索引要存在，存在位置没有关系。
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';（符合最左前缀法则）
explain select * from tb_user where profession = '软件工程' and age = 31;（符合最左前缀法则）
explain select * from tb_user where profession = '软件工程';（符合最左前缀法则）
explain select * from tb_user where age = 31 and status = '0';（跳过了左边的列profession，不符合，索引失效，全表扫描）
explain select * from tb_user where status = '0';（同上）
explain select * from tb_user where profession = '软件工程' and status = '0';（索引status部分失效）
范围查询（业务允许的话，尽量使用>=）
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效
explain select * from tb_user where profession = '软件工程' and age > 31 and status = '0';（status部分索引失效）
explain select * from tb_user where profession = '软件工程' and age >= 31 and status = '0';（索引未失效）
索引列运算
不要在索引列上进行运算操作， 索引将失效。
-- 查询手机号最后2位是15
explain select * from tb_user where substring(phone,10,2) = '15';（索引失效，type=ALL，全表扫描）
字符串不加引号
字符串类型字段使用时，不加引号， 索引将失效。
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where phone = 17799990015;
模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工';
or连接的条件
用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990015' or age = 23;
由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。
数据分布影响（is null和is not null是否走索引取决于表中数据的分布）
如果MySQL评估使用索引比全表更慢，则不使用索引。
select * from tb_user where phone >= '17799990015';
select * from tb_user where phone >= '17799990015';
SQL提示（告诉MySQL用哪个索引）
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
-- use index：
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';
-- ignore index：
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';
-- force index：
explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';
覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。（因为 * 很容易出现回表扫描）
explain select id,profession from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select id,profession,age,status from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select id,profession,age,status,name from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
知识小贴士：
- using index condition ：查找使用了索引，但是需要回表查询数据
- using where; using index ：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
-- 面试题思考
-- 一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对以下SQL语句进行优化, 该如何进行才是最优方案:
select id, username, password from tb_user where username = 'itcast';
-- 建立username, password的联合索引
前缀索引（降低索引的体积，只针对前缀建立索引）
当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影
响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
create index idx_xxx on table_name(column(n));
-- 前缀长度: 可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
select count(distinct email)/count(*) from tb_user;
select count(distinct substring(email,1,5)/count(*) from tb_user);
单列索引与联合索引
单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
explain select id, phone, name from tb_user where phone = '17799990010' and name = '韩信';
-- 必然涉及覆盖索引的问题，会回表查询，因为phone的索引当中必然不包含name字段的值。
-- 所以，推荐创建联合索引。
多条件联合查询时， MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。
联合索引情况：（最终构建的B+树如下，节点存储phone和name的值）（这一个索引结构已经获取到了想要的数据，覆盖索引，不会回表查询）
索引设计原则
- 针对于数据量较大（超过100w），且查询比较频繁的表建立索引。
- 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
- 尽量选择区分度高（身份证号，反例是性别）的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
- 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
索引总结
-- 1.概述
索引是高效获取数据的数据结构 ;
-- 2.索引结构
B+ Tree: 所有的数据出现在叶子结点，而且叶子结点形成一个双向链表。
Hash
-- 3.索引分类
主键索引（主键默认会创建的）、唯一索引、常规索引、全文索引
聚集索引（必须存在，只能有一个。叶子结点挂的是一行row的数据。默认主键索引就是聚集索引。）、二级索引（叶子结点挂的是对应行的主键）
-- 4.索引语法（创建、查看、删除）
create [unique] index xxx on xxx(xxx);
show index from xxx;
drop index xxx from xxx;
-- 5.SQL性能分析
执行频次（查询为主还是增删改为主）、慢查询日志（定位执行比较耗时的sql语句）、profile（监控每一条sql语句的耗时以及具体的时间耗费在哪一个阶段）、explain（使用最多，查看sql语句的执行计划评判sql语句的性能）
-- 6.索引使用
联合索引（最重要的是遵循最左前缀法则，防止失效）
索引失效（索引列上运算、字符串不加引号、前面模糊匹配、or连接一侧没有索引、全表扫描更快）
SQL提示（指定使用哪个索引）
覆盖索引（涉及回表查询的问题。所谓覆盖索引是指查询返回的列在索引结构中都包含了。所谓回表查询是指先走二级索引再到聚集索引）
前缀索引（缩小索引的体积，提高查询的效率）
单列/联合索引（推荐使用联合索引。理由：性能较高；如果使用得当可以避免回表查询。）
-- 7.索引性能分析
表（数据量大，查询频率高）
字段（查询条件（where）、排序（order by）、分组（group by）后的字段）
建立设么索引（尽量建立唯一索引，区分度高）（尽量使用联合索引）（尽量使用前缀索引）
