Mysql高级（索引及优化）

1.存储引擎

1.1 在创建表时，指定存储引擎

1.2 查看当前数据库支持的存储引擎

1.3 存储引擎的特点

InnoDB

1.DML操作遵循ACID模型，支持事务

2.行级锁，提高并发访问性能

3.支持外键FOREIGN KEY约束，保证数据的完整性和正确性

MyISAM

1.不支持事务，不支持外键

2.支持表锁，不支持行锁

3.访问速度快

Memory

1.内存存放

2.hash索引

2. 索引

索引： 加快数据查询速率

1.优缺点：

2.1 索引的结构

二叉树

B-Tree

B+Tree:所有的数据都会出目前叶子节点，并且叶子节点形成一个单向链表

MySQL索引数据结构对BTree进行了优化，在其基础上，增加了一个指向相邻叶子节点的链表指针，形成了带有顺序指针的B+Tree，提高区间访问的性能

Hash索引:采用hash算法，将键值的hash值算出，然后映射到对应的hash槽位上去，然后存储数据在hash表中，会有hash冲突。

特点：

1. Hash索引只能用于对等比较，不支持范围查询

2. 无法利用索引完成排序操作

3. 查询效率高，一般只需要一次检索就可以了，效率一般要高于B+Tree索引

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

相对于二叉树，层级更少，搜索效率高

相对于B-Tree，无论叶子节点还是非叶子节点，都会保存数据，导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低,相对于Hash索引，B+tree支持范围匹配和排序操作

2.2索引的分类

索引分类：

聚集索引选取规则：

1.主键索引为聚集索引

2.不存在主键，则将使用第一个唯一索引作为聚集索引

3.如果都没有，则会自动生成一个 rowid 作为隐藏的聚集索引

2.3 索引语法

1.创建索引

CREATE [UNIQUE | FULLTEXT] INDEX INDEX_NAME ON TABLE_NAME(字段)

2.查看所有

SHOW INDEX FROM TABLE_NAME

3.删除索引

DROP INDEX INDEX_NAME ON TABLE_NAME

2.4 SQL 性能分析

2.4.1 SQL 执行频率

MySQL客户端连接成功后，通过 show [session|global] status 命令可以提供服务器

状态信息。

通过如下指令，可以查看当前数据库的 增删改查的访问频率

show global status like 'sys_';

2.4.2 慢日志查询

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

#开启mysql慢日志查询开关
slow_query_log =1
#设置慢sql时间，sql语句超过设置的时间，就视为慢sql
long_query_time =1

查看慢查询日志的开启状态:

show variables like ‘slow_query_log’;

2.4.3 profile 详情

show profiles 能够在做SQL优化时间协助我们了解消费的时间，通过 have_profiling 参

数，能够查看当前MySQL是否支持 profile操作

show variables like '%profiling%';

或者

select @@have_profiling;

#查看每一条sql的耗时基本情况
show profiles;

#查看指定query_id 的sql语句各个阶段的耗时情况
show profile for query 260;
#查看指定的query_id的sql语句cpu的使用情况
show profile cpu,block io,memory,swaps for query 260;

2.4.4 执行计划

直接在select 语句前加上关键字 explain / desc

desc select * from test;

或者

explain select * from test;

执行计划各个字段的含义

1.id

select 查询的序列号，标识执行的顺序

id 一样，执行顺序由上至下

id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

2. select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等。

SIMPLE：简单查询,不包含子查询与union

PRIMARY：查询中包含子查询,最外层查询则被标记为PRIMARY

UNION：使用union连接select时,从第二个select开始都是UNION

SUBQUERY：select或者where后面的子查询(非from之后)都可能是SUBQUERY

DERIVED：在from中包含的子查询被标记为DERIVED(派生表)

DEPENDENT SUBQUERY：依赖外部查询的SUBQUERY ，如使用exists

UNION RESULT：UNION的结果,对应ID为NULL

DERIVED： 查询使用内联视图

MATERIALIZED：子查询物化，表出目前非相关子查询中 并且需要进行物化（物化视

图）

3.table

查询涉及到的表。直接显示表名或者表的别名

<unionM,N> 由 ID 为 M，N查询union后产生的结果

由 ID 为 N查询产生的结果

4.type

system：当表里面只有一行数据的时候就会这样，而且不管有没有索引都一样，这

是const连接类型的特殊情况

如：explain select * from test

const：常量连接

条件： 命中主键（primary key）或者唯一（unique）索引连接的部分是一

个常量值

如：explain select * from test where id =1

eq_ref：条件：主键索引（primary key）或者非空唯一索引（unique not null）等

值扫描。

条件：join 查询、命中主键（primary key）或

者非空唯一（unique not null）索引、等值连接（mysql中为

inner join）；

如：explain select * from test a inner join test b on a.id
= b.id

ref：非主键非唯一索引等值扫描(非聚集索引的常量查询)

条件：1.单表普通索引；2.join 查询、命中普通索引、等

值连接（mysql中为inner join）

如：explain select * from test where a=1 ;

range：范围扫描 （索引上的范围查询）

条件：1.使用索引

如： explain select * from test a where id in(1,2,3);

ALL：全表扫描（full table scan）

条件：1.筛选条件未使用索引

fullText：

条件：使用fulltext索引查询

例如：explain select * from test where match(a)

index：

条件：无条件count全表,单表查询排序等

explain select count(1) from invoice_basic_info_test

5. type总结

性能排序：null->system->const->eq-ref->ref->fulltext->ref_or_null-

>index_merge->unique_subquery- >index_subquery->range->index->ALL

常见的扫描类型有：system>const>eq_ref>ref>range>index>ALL，其扫描速度由

快到慢；

各类扫描类型的要点是：

system 最快：不进行磁盘 IO

const：PK 或者 unique 上的等值查询

eq_ref：PK 或者 unique 上的 join 查询，等值匹配，对于前表的每一行，后表只

有一行命中

ref：非唯一索引，等值匹配，可能有多行命中

range：索引上的范围扫描，例如：between、in、>

index：索引上的全集扫描，例如：InnoDB 的 countALL 最慢：全表扫描

possible_keys

查询过程中有可能用到的索引。这只是列出MySQL在选择执行计划时的索引候选。对于SQL

调优，此列信息作用不是太大

key

实际使用的索引，如果为 NULL ，则没有使用索引。key列展示的是在执行查询时实际会使用

的索引，这个列输出的信息超级 重大。它的输出可能是NULL/PRIMARY/或者自定索引名。在

非索引合并的情况下，这个列输出的信息只会至多包含一个索 引，对于索引合并，则会是索

引合并用到的索引。

key_len

key_len展示了key中对应项在实际使用时用到了几个字节。这同样是一个很重大的信息。例

如对于使用多重索引的情况，可 以根据key_len来推断查询SQL使用多重索引几个前缀索引

列。值得注意的是MySQL对于可为NULL的字段需要额外的一个字 节存储，因此key_len在这

种情况下会比列类型本身的值多1。

5.7版本使用utf8编码时：datetime(5) int(4) char(长度3) vachar(长度3+2) null(+1)

explain select * from invoice_basic_info_301 where c_saletaxnum = '339901999999142'

and dt_adddate = '2016-07-01 00:02:44'

rows

显示MySQL认为它执行查询时必须检查的行数，根据表统计信息或者索引选用情况，大致估

算出找到所需的记录所需要读取 的行数。 此数字是估计值，可能并不总是准确的。

filtered

条件：where条件中出现的列上建有索引，且执行计划不走索引的range或ref扫描，

表明存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例(百分比),返

回结果的行数占需读取行数(rows) 的百分比， filtered列的值依赖于统计信息，filtered 的值越

大越好。条件苛刻filtered列几乎无用。

Extra

extra是一个超级具有参考价值的列。官方doc也有专门的小节来讲解extra列中的值。

Using filesort（多了额外查找或排序）

一般是由于查询SQL的order by没有合适的索引可以用。虽然名字是叫filesort不过实际上未必是在文件

中排序，可能是内存中也可能是在磁盘中-- 取决于排序缓冲区的大小。

对于出现Using filesort一般需要思考优化掉不必要的order by或者添加索引。

但是在实际情况中，也可能即使order by的列有对应索引，依旧会出现filesort。

举个例子来说对于。select * from test where ... order by col如果where条件中的筛选性比较低，列如

性别这样的列即便建立索引，其cardinality也会很小

alter table user_desc add index idx_sex(sex); -- 这样的索引不提议创建（可以类推发票种类，开票服

务器类型）

例如：explain select * from user_desc order by sex;

MySQL是有可能为了避免读取二级索引和聚集索引造成的I/O开销，而倾向于只使用聚集索引顺序访问过滤where条件中的记录，然后再进行filesort的。

问题：分页框架，往往需要查出原SQL可以在数据库表中能筛选出来的总记录数，比较简单的方式就是

在原SQL外面套上一个select count(1) from,将原来的SQL包为子查询。

这样的话由于原SQL往往带着order by语句，很可能会出现为了求一个total count而出现扫表+排序的情况。

解决方式：在分页拦截器中修改原SQL，将原来的语句最外层改写为select count(1)形式。这样MySQL容易优化掉不必要的order by。

Using temporary

例如：explain select * from user group by name order by sex;

Extra 为 Using temporary 说明，需要建立临时表（temporary table）来暂存中间结果。

这类 SQL 语句性能较低，往往也需要进行优化。典型的 group by 和 order by 同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

Using index

典型场景：查询列被索引覆盖，且where筛选条件是索引的前导列

例如：explain select cabinet_id,module from cabinet_module_info where cabinet_id = 'NA108-XCSXUUZXBN' -- type= ref

Using index，Using where

典型场景：查询列被索引覆盖，且where筛选条件不是索引的前导列

例如：explain select cabinet_id,module from cabinet_module_info where module = '5906' -- type =index

表明扫描了联合索引，无法直接通过索引查找来查询到符合条件的数据

NULL

典型场景：查询的列未被索引覆盖，并且where筛选条件是索引的前导列

例如：explain select * from cabinet_module_info where cabinet_id = 'NA108-XCSXUUZXBN' -- type= ref

表明用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引，Extra中为NULL(没有信息)

Using index condition（icp）

场景：当sql需要全表访问时，通过ICP的优化策略使用索引进行数据过滤。需要开启icp功能。

例如：explain select * from invoice_basic_info_301 where c_saletaxnum = '339901999999142' and dt_adddate BETWEEN '2020-01-01 00:00:00' and '2020-01-26 00:00:00'

Using where

典型场景：查询的列未被索引覆盖，where筛选条件(非索引的前导列 | 非索引列)

例如：explain select * from cabinet_module_info where module = '5906' -- type = all

using where 表明通过索引或者表扫描的方式进行where条件的过滤，反过来说，也就是没有可用的索引查找，当然这里也要思考索引扫描+回表与表扫描的代价。这里的type都是all，说明MySQL认为全表扫描是一种比较低的代价。

例如：explain select * from cabinet_module_info where disk_space = 3 -- type = all

带有where语句的SQL，MySQL有三种方式处理，从好到坏依次如下：

1.在存储引擎层用索引来过滤where条件中不匹配的记录;

2.在MySQL服务器层用索引覆盖(extra列会出现Using index)返回记录;

3.从数据表中返回数据后，过滤where中不匹配的条件(extra会出现Using where) 这也是MySQL服务器层完成的

2.5 索引使用

2.5.1 最左前缀法则

如果使用的是联合索引，要遵循最左前缀法则。查询从索引的最左列开始，并且不跳过索引中的列。如

果跳跃某一列， 索引将部分失效（后面的字段索引失效）

todo

2.5.2 索引失效

联合索引中，出现范围查询（

>，<），范围查询右侧的列索引失效，所以范围查询尽可能的使用

>= 而不是 >，<

索引列运算

在索引列上进行运算操作，索引将会失效

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效

模糊查询，头部模糊匹配

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引将失效

or 连接的条件

用 or 分割开的条件，必须两个条件中列都有索引，否则索引失效

数据分布影响

如果MySQL 评估使用索引比全表更慢，则不使用索引

SQL提示

SQL提示，是优化数据库的一个重大手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的

use index：使用该索引

explain select * from test use index(test_e_index)where a=1 ;

ignore index：不使用该索引

explain select * from test ignore index(test_e_index)where a=1 ;

force index：必须使用该索引

explain select * from test force index(test_e_index)where a=1 ;

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中能够全部找到），减少 select * using index condition：查找使用了索引，但需回表查询数据

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

前缀索引

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

语法：create index 索引名 on 表名(字段名(n));n为截取前面 n 个字符

前缀长度：

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

2.6 索引设计原则

1. 针对数据量较大，且查询比较频繁的表建立索引。

2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4. 如果是字符串类型的字段，字段长度越长，可以针对于字段的特点，建立前缀索引。

5. 尽量使用联合索引，减少单列索引，查询时，联合索引许多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7. 如果索引列不能存储null 值，在创建表使用 not null约束它，当优化器知道每列是否包含null值时，它可以更好的确定哪个索引最有效地用于查询

3. SQL优化

3.1 插入数据

insert 优化

1. 批量插入

2. 手动提交事务

3. 主键顺序插入

大批量插入数据

如果一次性需要插入大批量数据，使用insert 插入性能较低，推荐使用 load 指令

3.2 主键优化

主键设计原则

1. 满足业务需求的同时，尽量降低主键的长度

2. 插入数据时，尽量选择顺序插入，选择 AUTO_INCREMENT自增主键

3. 尽量不要使用 UUID 做主键或者是其它自然主键，如身份证号

4. 尽量避免对主键的修改

3.3 order by优化

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sortbuffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 fileSort排序

2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index。不需要额外排序，操作效率高

创建索引时，指定索引的排序方式：

create index idx_user_age_phone on user(age desc,phone desc);

3.4 group by优化

1. 分组操作时，可以通过索引来提高效率

2. 索引的使用要满足最左前缀法则

3.5 limit优化

一个常见的问题：limit 2000000,10，此时需要MySQL排序前 200010 记录，仅仅返回2000000-2000010的记录，其它的记录丢弃，查询排序的代价超级大

优化思路：一般分页查询时，通过创建 覆盖索引 能够较好的提高性能，可以通过覆盖索引急+子查询形式进行优化

select s.* from stu s,(select id from stu order by id limit 2000000,10) a where s.id = a.id;

3.6 count优化

select count(*) from user;

1. MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count() 的时候直接返回这个数，效率很高

2. InnoDB 引擎执行 count() 的时候，需要把数据一行一行地从引擎中读出来，然后累加计数

count的几种用法及流程：

1. count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL ，累计值+1，最后返回累计值

2. 用法：count(*)、count(主键)、count(字段)、count(1)

3. 底层流程

1. count(主键)：

InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加

2. count(字段)：

没有 not null 约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加有 not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行累加

3. count(1)

InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字 “1” 进去，

直接按行进行累加

count(*) 推荐

不取值，服务层直接按行进行累加

3.7 update优化

更新的条件必须加索引，InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁

4.sql问题排查语句

4.1排查sql语句

## 查询进程
select * from information_schema.PROCESSLIST a where a.INFO is not null limit
0,1000;
##当前运行的所有事务
select * from information_schema.innodb_trx;
##查看数据库当前出现的锁
select * from information_schema.innodb_locks;
## 锁等待的对应关系
select * from information_schema.innodb_lock_waits;
##查看锁的情况
show status like 'innodb_row_lock_%';
##查询是否锁表
show OPEN TABLES where In_use > 0;
##查询进程
show processlist;