MySQL学习笔记(一) 索引原理

一、索引类型

索引可以提升查询速度，会影响 where 查询，以及 order by 排序。

1. 1 普通索引

最基本的索引，基于普通字段建立的索引，没有任何限制

创建方法：

create index 索引的名字 on tableName(字段名);
alter table tableName add index 索引的名字 (字段名);
create table tableName ([...], index 索引名 (字段名));

1.2 唯一索引

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

创建方法：

create unique index 索引的名字 on tableName(字段名);
alter table tableName add unique index 索引的名字 (字段名);
create table tableName ([...], unique index 索引名 (字段名));

1.3 主键索引

它是一种特殊的唯一索引，不允许有空值，每张表只能有一个主键

创建方法：

alert table tableName add primary key (字段名);
create table tableName ([...], primary key (字段名));

1.4 复合索引

单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上。用户可以在多个列上建立索引，这种索引叫做复合索引(组合索引)。复合索引在数据库操作期间所需的开销更小，可以代替多个单一索引

索引同时有俩个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引。原则是用窄不用宽，因为窄索引往往比组合索引更有效

创建方法：

create index 索引名 on tableName (字段1,字段2...);
alert table tableName add index 索引名 (字段1,字段2...);

1.5 全文索引

数据量小时可以使用 like，数据量大的时候，效率很低。如果使用全文检索，查询比 like 快很多倍。5.6 MyISAM 存储引擎支持，5.7都支持

创建方法：

create fulltext index 索引名 on tableName (字段);

查询方法:

select * from tableName where match(字段) against('aaa');

索引删除方法:

# 普通索引删除
drop index 索引名 on tableName;
# 主键索引删除
alter table tableName modify 主键名 int; # 去除自增策略，如没有则不需要此步
alter table tableName drop primary key;

二、索引原理

2.1 二分查找法

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

1. 首先定位 left 和 right 指针
2. 计算（left + right）/ 2
3. 判断除2后索引位置与目标值的大小
4. 索引位置值大于目标值就 left =索引位置 + 1，小于目标值 right = 索引位置 - 1

2.2 Hash 结构

Hash 底层实现是由 Hash 表实现

2.3 B+Tree 结构

MySQL 数据库索引采用的是 B+Tree，在 B+Tree 上做了优化

2.4 聚簇索引和辅助索引

聚簇索引（聚集索引）

主键索引就是聚集索引。

InnoDB 的表要求必须有聚集索引：

1. 如果表定义了主键，则主键为聚簇索引
2. 如果表没有定义主键，则第一个非空 unqiue 列为聚簇索引
3. 否则 InnoDB 会重建一个隐藏的 row-id 作为聚簇索引

辅助索引

InnoDB 辅助索引，也叫做二级索引，是根据索引列构建 B+Tree 结构，但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多，通常创建辅助索引就是为了提升查询效率。一个表 InnoDB 只能创建一个聚簇索引，但可以创建多个辅助索引

非聚簇索引

与 InnoDB 不同，MyISAM 数据表的索引文件和数据文件是分开的，称为非聚簇索引

聚簇索引和非聚簇索引的区别

B+Tree 的叶子节点存放主键索引值和行记录就属于聚簇索引，如果索引值和行记录分开存放就属于非聚簇索引

三、索引分析和优化

3.1 Explain

MySQL 提供了一个 Explain 的命令，它可以对 select 语句进行分析，并输出 select 执行的详细信息，供开发人员有针对性的优化，例如：

explain select * from user where id < 3;
# id：标识符
# select_type：查询类型，普通或子查询
# table：表名
# partitions：分区
# type：连接类型
# possible_keys：可能用到的索引
# key：用到的索引
# key_len：索引多少个字节
# ref：引用了哪个字段
# rows：扫描了多少行，越大性能越差
# fittered：过滤的百分比
# extra：额外的信息

select_type

表示查询的类型。常用的值如下：

simple：最常见，表示查询语句不包含自查询和 union
primary：表示此查询是最外层的查询
union：表示此查询是 union 的第二个或后续的查询
dependent union：表示 union 中的第二个或后续的查询语句，使用了外面查询结果
union result：union 的结果
subquery：select 子查询语句
dependent subquery：select 子查询语句以来外层查询的结果

type

表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用的值如下：

all：表示全表扫描，性能最差
index：表示基于索引的全表扫描，先扫描索引再扫全表数据
range：表示使用索引范围查询。使用>，<，in 等等
ref：表示使用非唯一索引进行单值查询
eq_ref：一般出现在多表 join 中，表示前表每一条记录，唯一匹配后表的一条记录
const：表示使用主键或唯一索引做等值查询，常量查询
null：表示不用访问表

从上往下，效率越来越好

possible_keys

表示查询时能够使用到的索引，但并不一定真正使用

key

表示查询时真正使用到的索引，显示的是索引名称

rows

MySQL 查询优化器会根据统计信息，估算 SQL 要查询到结果需要扫描多少行记录。原则上 rows 越少效率越高，可以直观的了解到 SQL 效率的高低

key_len

表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引，或只用到索引最左部分的字段值

key_len 的计算规则如下：

字符串类型：
字符串长度跟字符集有关：latin=1，gbk=2，utf8=3，utfmb4=4
char（n）：n * 字符集长度 + 2字节
varchar（n）：n * 字符集长度 + 2字节

数值类型：
tinyint：1个字节
smallint：2个字节
mediumint：3个字节
int，float：4个字节
bigint，double：8个字节

时间类型：
date：3个字节
timestamp：4个字节
datetime：8个字节

字段属性：
null 属性 占用1个字节，如果一字段值设置了 not null ，则无此项

Extra

Extra 表示很多额外的信息，各种操作会在 Extra 提示相关信息，常见如下：

Using where：表示查询需要通过索引回表查询数据
Using index：表示查询需要通过索引，索引就可以满足所需数据
Using filesort：表示查询出来的结果需要额外排序。建议优化
Using temprorary：查询使用到了临时表，一般去重、分组等操作。建议优化

3.2 回表查询

先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询

3.3 覆盖索引

只需要在一颗索引树上就能获取 SQL 所需的索引列数据，无需回表，速度更快，叫做索引覆盖。Extra ->Using index 就表示覆盖索引。

3.4 最左前缀原则

复合索引使用时遵循最左前缀原则。最左前缀原则顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

3.5 like 查询

MySQL 在使用 like 模糊查询时，索引能不能起作用？
答：MySQL 在使用 like 模糊查询时，索引是可以被使用的，只有把 % 字符放在后面才会使用到索引。

'%字段%" 不起作用
‘%字段’ 不起作用
‘字段%’ 起作用

3.6 NULL查询

如果 MySQL 表中的某一列含有 NULL 值，那么索引是否有效？
答：是有效的。NULL 是一个特殊是的值，不能使用<, >, = 这样的运算符，对 NULL 进行算数运算返回都是 NULL， count 时不包含 NULL 行，NULL比空串需要更多的存储空间。

3.7 索引与排序

MySQL 查询支持 filesort 和 index 俩种方式的排序，filesort 是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。使用 index 是指索引自动实现排序，不需另做排序操作，效率会比较高

filesort 有俩种排序算法：双路排序和单路排序

双路排序：需要俩次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序，第二次去读取其他字段数据。

单路排序：从磁盘查询所需的所有列的数据，然后在内存或缓存排序后将结果返回。如果缓存 sort_buffer 小于查询数据，会导致多次磁盘读取操作，并且创建临时表，最后产生多次 IO。解决方法：
少使用 select *
增加 sort_buffer_size 容量和 max_length_for_date 容量

以下几种情况，会出现 index 方式的排序：

order by 子句索引列组合满足索引最左前缀

explain select id from user order by id;

where 子句 + order by 子句索引列满足索引最左前缀

explain select id from user where age = 18 order by id; # （age,name）索引

以下几种情况，会使用 filesort 方式的排序

对索引同时使用了 asc 和 desc

explain select id from user order by age asc, name desc; # （age, name）索引

where 子句和 order by 子句满足最左前缀，但 where 子句使用了范围查询（例如 <, >, =）

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 age,name; #（name）,（age）索引

where 子句与 order by 子句使用了不同的索引

explain select id from user where name = 'xx' order by age; #（name），（age）索引

where 子句或者 order by 子句中索引列使用了表达式，包括函数表达式

explain select id from user order by abs(age); #（age）索引

四、查询优化

4.1 慢查询定位

开启慢查询日志

查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令如下：

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_indexs = ON;
set long_query_time = 10; # 慢查询阀值，单位秒

查看慢查询日志

time：日志记录的时间
User@Host：执行的用户与主机
query_time：执行时间
lock_time：锁表时间
rows_sent：发送给请求方的记录数，结果数量
rows_examined：语句扫描的记录条数
set timestamp：语句执行的时间点
select…：执行的 SQL 语句

4.2 慢查询优化

索引和慢查询

如何判断是否为满查询？
SQL 语句的执行时间 > long_query_time 则会记录到慢查询日志中，视为慢查询

如何判断是否应用了索引？
explain 命令检查结果中 key 值不为 NULL 则使用了索引

应用了索引是否一定快？
不一定快，看是否还是进行了全表扫描，扫描的数据行数，过滤性是否好

提高索引的过滤性

选择过滤性好的字段建索引，建议不要给例如性别这样的列建索引

慢查询原因总结

全表扫描
全索引扫描
索引过滤性不好
频繁的回表查询开销

4.3 分页查询优化

一般性分页

一般的分页查询使用简单的 limit 子句就可以实现。limit 格式如下：

select * from tableName limit pageNumber, pageSize;

第一个参数指定第一个返回记录行的偏移值，从0开始
第二个参数指定返回记录行的最大数目
如果只给一个值，表示返回最大的记录行数目

分页优化方案

利用覆盖索引优化

select * from user limit 10000, 10;
select id from user limit 10000, 10;

利用子查询优化

select * from user limit 10000, 10;
select * from user where id >= (select id from user limit 10000,1) limit 10;

pageSize 的值越大，查询时间越长

查看SQL执行时间

show variables like 'profiling';
set profiling = 1;
# 执行 SQL 后调用 show profiles;
show profiles;

记录学习，如有错误，欢迎指出😬