MySQL索引原理
索引类型
MySQL索引类型可以按不同纬度分为如下几种：
- 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
- 从应用层次划分：普通索引、唯一索引、主键索引、复合索引
- 从索引键值类型划分：主键索引、辅助索引（二级索引）
- 从数据存储和索引键值逻辑关系划分：聚集索引和非聚集索引
- 普通索引
指基于普通字段建立的索引。建立索引的方法如下：
CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );
- 唯一索引
与“普通索引”类似，不同的是：索引字段的值必须唯一，但允许有空值。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。
创建唯一索引的方法如下：
CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;
- 主键索引
它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。
创建主键索引的方法如下：
CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);
- 复合索引
单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。
创建组合索引的方法如下：
CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );
复合索引使用注意事项：
- 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
- 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。
- 全文索引
查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL5.6开始MyISAM和InnoDB存储引擎均支持。
创建全文索引的方法如下：
CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;
和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如
select * from user
where match(name) against('aaa');
索引原理
MySQL官方对索引定义：存储引擎用于快速查找记录的一种数据结构，需要额外开辟空间和数据维护工作。
- 索引是物理数据页存储，在数据文件中，利用数据页（page）存储
- 索引可以加快索引速度，但是同时也会降低增删改操作速度，索引维护需要代价。
索引涉及的理论知识：二分查找法、Hash、B+Tree
- 二分查找法
二分查找法也叫做折半查找法，它是在有序数组中查找指定数据的搜索算法。他的优点是等值查询、范围查询性能好，缺点是更新数据、新增数据、删除数据维护成本高。
查找步骤：
- 首先定位left和right两个指针
- 计算(left+right)/2，除不尽向下取余。并记为m
- 判断除2后索引位置值与目标值的大小比对
- 索引位置值大于目标值，right移动（right=m-1）；如果小于目标值，left移动(left=m+1)
举例：下面的有序数组有17个值，查找的目标值是7
......
依次类推，直到索引位置值=查找的目标值
2. Hash
Hash底层实现是由Hash表来实现的，是根据键值<key,value>存储数据的结构
- B+Tree结构
我们先来看看B树和B+树结构：
- B Tree结构
- 索引值和data数据分布在整棵树结构中
- 每个节点可以存放多个索引值及对应的data数据
- 树节点中的多个索引值从左到右升序排列
B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的节点点指针为空，或已经是叶子节点了才结束。
- B+Tree结构
- 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
- 叶子节点包含了所有的索引值和data数据
- 叶子节点用指针连接，提高范围查询性能
B+树范围查询时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针遍历即可。而B树需要遍历范围内所有的节点和数据。
聚簇索引、辅助索引、非聚簇索引
Mysql的索引普遍使用B+树做索引。
聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；索引值和行记录分开存放就属于非聚簇索引
主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段就属于主键索引；存放的是非主键就属于辅助索引（也叫二级索引）
InnoDB里的索引：
聚簇索引：
InnoDB的聚簇索引是按照主键顺序构建B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值存在一块的。也就意味着InnoDB的主键索引就是数据表本身，它按照主键顺序存放了整张表的数据。通常来说主键索引就是聚簇索引。
辅助索引：
InnoDB辅助索引，是根据普通索引列构建B+Tree结构。在B+Tree叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多，但查询需要进行两遍检索，先从辅助索引处获得主键，然后再用主键去主键索引里获得行记录。
MyISAM里的索引：
非聚簇索引：
MyISAM数据表的索引文件和数据文件是分开的，它的索引文件保存的不是完整的数据记录而是数据记录的地址。在MyISAM中，主键索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复
索引分析和优化
1. Expain的使用
-
id列
id代表有几条查询，有几条就有几个id，id越大越先执行，id相等则从上到下执行 -
select_type 查询类型
- simple：简单查询
- primary：复杂查询最外层的select
- subquery：包含在select中的子查询
- derived：包含着from子句中的子查询。Mysql会将结果存在一个临时表（也叫派生表）中。
- union：在union中的第二个和随后的sselect
- union select：从union的临时表检索结果的select
- type
表示关联类型或访问类型，即决定如何查找表中的行
从优到差分别是：ststem > const > eq_ref > ref > range > index > ALL
一般来说，保证达到range，最好达到ref
system，const：一般就是只有一条记录，可以用常量代替的
eq_ref：使用的索引是主键索引或唯一索引
ref：使用的是普通索引
range：范围扫描通常出现在in(),bewteen,>,<,=
index：扫描全表索引，通常比ALL快一点
ALL：扫描全表
4. key
会用到的索引
-
possible_key 可能会用到的索引
-
key_len
索引长度，不同的类型长度计算方式不同
- rows
可能会扫描到的行数
8. Extra
额外信息。有以下情况
- Using index时，意思为覆盖索引，即能在索引中拿到数据，而不用在通过索引去查数据。
- Using Where：查询的列未被索引覆盖
- Using where Useing index：查询的列被索引覆盖，并且where筛选的条件是索引列之一，但不是前导列
- Using temporary：查询的时候使用了临时表
- Using filesort：文件排序
最佳实践：
- 全值匹配
如果用了联合索引，那么最好所有的值都使用上。比如某个表有一个3个字段组成的联合索引，那么查询时where里面这三个都带上
2. 索引最左前缀原理
建立的联合索引的顺序是什么样，使用时就要什么样。（虽然你不按照顺序写，但优化器可能会优化）
3. 不要在索引上做任何操作计算，函数，类型转换等
4. Mysql使用!=或<>, isnull,is not null,会导致全表扫描
5. like查询时，name like '%admi' 不走索引， name like 'adm%' 会走索引
6. 字符串不加单引号索引失效
7. 少用or或in，用他连接时索引可能会失效，跟数据量有关
8. in和exists的区别
//当A表数据大于B表时，in优于exists
select * from A where id in(select id from B)
- 索引与排序
MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率比较高。
以下两种情况用index的方式排序：
- ORDER BY 子句索引列组合满足索引最左前列
explain select id from user order by id; //对应(id)、(id,name)索引有效
- WHERE子句+ORDER BY子句索引列组合满足索引最左前列
explain select id from user where age=18 order by name; //对应
(age,name)索引
以下几种情况，会使用filesort方式的排序：
- 对索引列同时使用了ASC和DESC
explain select id from user order by age asc,name desc; //对应
(age,name)索引
- WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询（例如>、<、in
等）
explain select id from user where age>10 order by name; //对应
(age,name)索引
- ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列
explain select id from user order by name; //对应(age,name)索引
- 使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引
explain select id from user order by name,age; //对应(name)、(age)两个索
引
- WHERE子句与ORDER BY子句，使用了不同的索引
explain select id from user where name='tom' order by age; //对应
(name)、(age)索引
2. 慢查询日志
- 开启慢查询日志
查询慢日志是否开启和文件位置的命令：
SHOW VARIABLES LIKE 'slow_query_log%'
开启慢查询日志命令：
SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON; #表示会记录没有使用索引的查询SQL
SET long_query_time = 10; #单位秒
- 查看慢日志
1）用文本编辑器打开
- time：日志记录的时间
- User@Host：执行的用户及主机
- Query_time：执行的时间
- Lock_time：锁表时间
- Rows_sent：发送给请求方的记录数，结果数量
- Rows_examined：语句扫描的记录条数
- SET timestamp：语句执行的时间点
- select....：执行的具体的SQL语句
2）使用mysqldumpslow查看
MySQL 提供了一个慢查询日志分析工具mysqldumpslow，可以通过该工具分析慢查询日志内容。（需要安装perl环境）
在MySQL的bin目录下执行命令：（注意慢日志文件目录不要带空格）
perl mysqldumpslow.pl -t 5 -s AT D:\DESKTOP-C1N48D3-slow.log
除了使用mysqldumpslow工具，也可以使用第三方分析工具，比如pt-query-digest、mysqlsla等。
3. 慢查询优化
- 增加索引
- 索引覆盖
- 提高索引过滤性
如下sql：
select * from student where age=18 and name like '张%';（全表扫
描）
优化1：我们可以增加索引
alter table student add index(age,name);
优化2：我们可以将name的第一个字取出来做一个虚拟列（first_name），然后创建一个联合索引(first_name,age)
alter table student add first_name varchar(2) generated always as
(left(name, 1)), add index(first_name, age);
