索引类型

MySQL索引类型可以按不同纬度分为如下几种：

• 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
• 从应用层次划分：普通索引、唯一索引、主键索引、复合索引
• 从索引键值类型划分：主键索引、辅助索引（二级索引）
• 从数据存储和索引键值逻辑关系划分：聚集索引和非聚集索引

1. 普通索引

指基于普通字段建立的索引。建立索引的方法如下：

CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

1. 唯一索引

与“普通索引”类似，不同的是：索引字段的值必须唯一，但允许有空值。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。

创建唯一索引的方法如下：

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

1. 主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。

创建主键索引的方法如下：

CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
ALTER TABLE tablename ADD PRIMARY KEY (字段名);

1. 复合索引

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

创建组合索引的方法如下：

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)复合索引，对于查询有一定提高。

1. 全文索引

查询操作在数据量比较少时，可以使用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

1. 二分查找法

二分查找法也叫做折半查找法，它是在有序数组中查找指定数据的搜索算法。他的优点是等值查询、范围查询性能好，缺点是更新数据、新增数据、删除数据维护成本高。

查找步骤：

• 首先定位left和right两个指针
• 计算(left+right)/2，除不尽向下取余。并记为m
• 判断除2后索引位置值与目标值的大小比对
• 索引位置值大于目标值，right移动（right=m-1）；如果小于目标值，left移动(left=m+1)

举例：下面的有序数组有17个值，查找的目标值是7

......
依次类推，直到索引位置值=查找的目标值
2. Hash

Hash底层实现是由Hash表来实现的，是根据键值<key,value>存储数据的结构

1. 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的使用

1. id列
   id代表有几条查询，有几条就有几个id，id越大越先执行，id相等则从上到下执行
2. select_type 查询类型

- simple：简单查询
- primary：复杂查询最外层的select
- subquery：包含在select中的子查询
- derived：包含着from子句中的子查询。Mysql会将结果存在一个临时表（也叫派生表）中。
- union：在union中的第二个和随后的sselect
- union select：从union的临时表检索结果的select

1. type

表示关联类型或访问类型，即决定如何查找表中的行

从优到差分别是：ststem > const > eq_ref > ref > range > index > ALL

一般来说，保证达到range，最好达到ref

system，const：一般就是只有一条记录，可以用常量代替的

eq_ref：使用的索引是主键索引或唯一索引

ref：使用的是普通索引

range：范围扫描通常出现在in(),bewteen,>,<,=

index：扫描全表索引，通常比ALL快一点

ALL：扫描全表
4. key

会用到的索引

1. possible_key 可能会用到的索引
2. key_len

索引长度，不同的类型长度计算方式不同

1. rows

可能会扫描到的行数
8. Extra

额外信息。有以下情况

• Using index时，意思为覆盖索引，即能在索引中拿到数据，而不用在通过索引去查数据。
• Using Where：查询的列未被索引覆盖
• Using where Useing index：查询的列被索引覆盖，并且where筛选的条件是索引列之一，但不是前导列
• Using temporary：查询的时候使用了临时表
• Using filesort：文件排序

最佳实践：

1. 全值匹配

如果用了联合索引，那么最好所有的值都使用上。比如某个表有一个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)

1. 索引与排序

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. 慢查询日志

1. 开启慢查询日志

查询慢日志是否开启和文件位置的命令：

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. 查看慢日志

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);

书山有路勤为径，学海无涯苦作舟