MySQL 索引篇

BTree索引结构(多路平衡查找树)：B-树索引是一种平衡树结构，它通过将索引键值和指针存储在树节点中，支持快速的查找、插入和删除。B-树索引适用于构建在内存或磁盘上的通用索引，如MySQL中的InnoDB存储引擎。
- 以一颗最大度数(max-dergree)为5(5阶)的BTree为例(每个节点最多存储4个key,5个指针)；

B+Tree索引结构：B+tree则是一种优化的Btree，它主要用于文件系统和数据库系统中。B+tree的节点只存储关键字和指向下一个叶节点的指针，而其他节点信息都存储在叶节点中。这种设计可以减小内存占用，同时提高查询效率。B+tree的叶节点形成一个有序链表，按照关键字的顺序排列。这样可以很容易地进行范围查询，而不需要进行全局的遍历。
- 在MySQL中对B+Tree进行了优化，在原B+Tree的基础上，增加有一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高了访问区间的性能。（循环双向链表）

B+Tree相对于BTree的区别：
①所有的数据都会出现在叶子节点
②叶子节点形成一个单向链表

R-Tree索引结构：R-Tree索引是一种用于空间数据类型的索引结构，可以用于存储GIS数据、地图数据等有空间属性的数据，在MySQL中主要用于存储InnoDB引擎表中的空间数据类型。
Fulltext索引结构：Fulltext索引是一种用于全文搜索的索引结构，主要用于存储MyISAM引擎表中的文本类型的数据，可以支持文本分词、模糊查询等功能。

这些索引结构各有优缺点，可以根据实际需求选择合适的索引类型和结构，以提高MySQL数据库的查询效率。
动态演化数据结构的网站：Data Structure Visualization (usfca.edu)

为什么InnoDB存储引擎选择使用B+tree索引结构？

相比于二叉树，层级更少，搜索效率高；
对于Btree ，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少要同样保存大量数据，只能增加树的高度，磁盘 IO 增加，导致性能降低；
相对Hash索引，B+tree支持范围匹配及排序操作;

比如：我们想要查询底层节点的数据，如果索引结构时 BTree 在非叶子节点中的数据会从磁盘加载到内存，但是这些记录数据是没用的，我们只想读取这些节点的索引数据来做比较查询，而非叶子节点里的数据对我们是麽用的，这样不仅增多磁盘 I/O 操作次数，也占用内存资源。

索引分类：

按功能划分：

主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
全文索引：它查找的是文本中的关键词，主要用于全文检索。

按索引的存储形式：

聚集索引(Clustered Index):
将数据存储与索引放到一块，索引结构的叶子节点保存了行数据必须有，而且只有一个。（默认主键索引是聚集索引）
二级索引(Secondary Index)也称为辅助索引或非聚集索引:

将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个。

聚集索引的选取规则：
如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

索引语法：

-- 创建索引：
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);
-- 查看索引：
SHOW INDEX FROM        table_name;
-- 删除索引：
DROP INDEX index_name ON table_name;

SQL性能分析

查看SQL的执行频率(为SQL优化提供支撑)

通过 show [session|global] status 命令可以提供服务器状态信息。
通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次

SHOW GLOBAL STATUS LIKE 'Com_______';

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位：秒，默认10秒)的所有SQL语句的日志。
MySQL 的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息：

--开启慢查询日志开关
slow_query_log = 1
--设置慢日志的时间为2秒，SQL语句的执行时间超过2秒，就视为慢查询，记录慢查询日志
long_query_time = 2

profile详情

show profile 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过hava_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;

explain执行计划：

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

EXPLAIN SELECT 查询语句；

EXPLAIN执行计划各字段含义：

id
select 查询的序列号，表示查询中执行select 字句或者是操作表的顺序(id 相同，执行顺序从上到下；id不同，值越大，越先执行)。
类似嵌套子查询先执行括号最里的语句，再向外展开
select_type
表示select的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(子查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
type
type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：All（全表扫描）、index（全索引扫描）、range（索引范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）、const（结果只有一条或唯一索引扫描）。const 性能最好，All 性能最差
possible_key
显示可能应用在这张表上的索引，一个或多个。
key
实际使用的索引，如果为NULL，则没有索引。
rows
MySQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不准确
filtered
表示返回结果的行数占需读取行数的百分比，filtered的值越大越好

索引使用

验证索引效率

耗时11.03秒

在未建立索引之前，执行以下SQL语句，查看SQL耗时。

SELECT * FROM tb_sku WHERE sn = '100000003145001';

针对字段创建索引

create index idx_sku_sn on tb_sku(sn);

然后再次执行相同的SQL语句，再次查看SQL的耗时

SELECT * FROM tb_sku WHERE sn = '100000003145001';

最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

如果不存在创建时的最左列，索引将全部失效 注意：与字段查询的位置无关
如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

索引失效的情况

范围查询
联合索引中，出现范围查询(>,<),范围查询右侧的列索引失效。
在不影响业务逻辑的条件下，使用 >= 或 <= 进行范围查询
索引列运算
索引列上进行运算操作，索引将失效
字符串不加引号
在单个索引中，索引失效
在联合索引中，索引将部分失效（符合最左前缀法则）
模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效
or连接的条件
用or 分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
如果一个表中的一组数据分布不均匀，比如其中一个值有大量重复，而其他值很少出现，那么使用该索引可能不会有效地缩小查询范围，导致索引失效，直接进行全表扫描。

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经能够全部找到），减少select * 。

explain select id,profession,age,status from tb_user where professino = '软件工程' and age =  31 and status = '0';

explain select id,profession,age,status,name from tb_user where professino = '软件工程' and age =  31 and status = '0';

注意这两个 sql 查询的字段，通过执行计划可知，第二个 sql 表示查询使用到了索引，但是也使用了回表查询。

这是因为 id、professino、age、status 都创建了索引，从二级索引 B+tree 的叶子节点中中可以直接查找到需要的信息。
在第二个查询语句中，因为 name 字段不在对应的二级索引中，需要通过二级索引(回表查询)找到 id(聚集索引)对应的row中，再在row中查找name字段。即查找的字段在二级索引中没有的，就需要回表查询。

using index condition : 查找使用了索引，但是需要回表查询数据
using where;using index : 查找使用了索引，但是需要的数据都能在索引列中能找到，所以不需要回表查询数据

举个例子: 分别执行以下三个SQL语句

select * from tb_user where id = 2;
select id,name from tb_user where name = 'Arm';
select id,name,gender from tb_user where name = 'Arm';

覆盖索引：直接对聚集索引查找返回row

覆盖索引：不需要回表，一次索引扫描就执行完成

在这条select语句中需要查询的gender不在辅助索引(二级索引)中，需要通过id值，进行回表查询得到row，再从row中找到name字段

前缀索引

当字段类型为字符串（varchar、text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO, 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

创建语法

--idx_xxx创建的前缀索引名称，column是创建索引的列名，length是截取的前缀长度。
create index idx_xxx on table_name(column(length));

前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

计算tb_user表中的email字段合适的前缀长度
```
select count(distinct email / count(*)) from tb_user;
```
在实际开发中，在处理较长的字符串时，根据业务需求，如果需要的选择性较高，选取选择性为1的前缀索引；如果选择性的要求不高，可以减少前缀的长度，节省空间，平衡选择性与前缀长度的关系
前缀索引查询流程

前缀索引查询叶子节点对应的id，再根据 id 回表查询对应的值。

索引设计原则

针对数据量较大，且查询比较频繁的表建立索引。
针对于常作为查询条件(where) 、排序(order)、分组(group by)操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表查询，提高查询效率。
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引不能存储NULL值，请在建表时使用NOT NULL约束。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引