MySQL索引与优化-优快云博客

本文链接：https://blog.youkuaiyun.com/handapeng1/article/details/128478060

MyISAM存储引擎

索引文件和数据文件是分离的（非聚集），frm表结构，MYD表数据，MYI索引

InnoDB存储引擎

主键是聚集（聚簇）索引，叶子节点存放了完整的数据记录

其它的索引是非聚集（稀疏），索引叶子节点存放的是主键值

表数据文件本身是按B+Tree组织的一个索引结构文件，frm是表结构，ibd是表数据加索引文件

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

因为InnoDB需要创建一个唯一的索引，叶子节点用于存放所有的数据，如果建表的时候没有指定，InnoDB会找表中有没有不重复的字段，将其作为主键索引，如果都没有，会创建一个伪例（相当于rowid）作为自增的主键索引
因为整型相对于字符串，占用的字节数少，页签中可以存放更多的索引数据，自增是因为索引是排好序的，比如在2和6中突然插入了一个5，InnoDB会去重新排序索引，影响数据库性能

为什么非主键索引结构叶子节点存储的是主键值？

保证表数据的一致性，修改数据只需要维护主键索引叶子节点里的数据

只有主键索引叶子节点存储所有的数据，可以节省存储空间，其余的索引的叶子节点存放主键值，在找到结果之后，重新回主键索引，获取这个主键的所有数据

explain中的列

id列

id列的编号是 select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。id列越大执行优先级越高，id 相同则从上往下执行，id为 NULL最后执行。

select_type列

select_type表示对应行是简单还是复杂的查询。

simple

简单查询。查询不包含子查询和union

primary

复杂查询中最外层的select

subquery

包含在select中的子查询（不在from子句中）

derived

包含在from子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表

union

在union中的第二个和随后的select

table列

这一列表示explain的一行正在访问哪个表

当from子句中有子查询时，table列是 <derivenN>格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询。

当有union时，UNION RESULT的table列的值为<union1,2>， 1和2表示参与union的 select行id。

type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围

依次从最优到最差分别为：system>const>eq_ref>ref>range>index>ALL

key_len列

显示了 mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

key_len计算规则如下：

字符串

char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节

char(n)：如果存汉字长度就是3n字节
varchar(n)：如果存汉字则长度是3n+2字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

数值类型

tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节

时间类型

date：3字节
timestamp：4字节
datetime：8字节

如果字段允许为NULL，需要1字节记录是否为NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

Extra列

Using index

mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；
覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

Using where

使用where语句来处理结果，并且查询的列未被索引覆盖

Using index condition

查询的列不完全被索引覆盖，where条件中是一个前导列的范围；(创建复合索引（name，salary，dept），就相当于创建了（name，salary，dept）、（name，salary）和（name）三个索引，这被称为复合索引前导列特性,因此在创建复合索引时应该将从常用作为查询条件的列放在最左边，依次递减)

Using temporary

mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

Using filesort

将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序（效率极低）。这种情况下一般也是要考虑使用索引来优化的。

索引最佳实践

全值匹配

最左前缀法则

创建索引，根据最左前缀，将等号查询的范围越大的越往左边放置，范围查询的往右边放置

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

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

因为索引树上没有进行了这些操作的索引，mysql没有这些优化

存储引擎不能使用索引中范围条件右边的列

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select*语句

like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

常见sql深入优化

Order by与Group by优化

1、MySQL支持两种方式的排序 filesort和index， Using index是指MySQL 扫描索引本身完成排序。 index效率高， filesort效率低。
2、order by满足两种情况会使用Using index。
1) order by语句使用索引最左前列。
2) 使用 where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果 order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、 group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上 order by null禁止排序。注意， where高于having，能写在 where中的限定条件就不要去having限定了。

Using filesort文件排序原理详解

单路排序

是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >

双路排序（又叫回表排序模式）

是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到 sort_mode信息里显示 < sort_key, rowid >

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式

如果字段的总长度小于max_length_for_sort_data ，那么使用单路排序模式；
如果字段的总长度大于max_length_for_sort_data ，那么使用双路排序模式。

索引设计原则

代码先行，索引后上

联合索引尽量覆盖条件

不要在小基数字段上建立索引

比如你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。

长字符串我们可以采用前缀索引

尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。

对于这种 varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。

此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。
但是假如你要是 order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。

where与order by冲突时优先where

让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。
因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多

分页查询优化

根据非主键字段排序的分页查询

让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键（使用覆盖索引，联合索引数包含了主键id ），然后根据主键查到对应的记录

select * from employees e inner join ( select id from employees order by name limit 90000,5) ed on e.id = ed.id;

原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序。

Join关联查询优化

嵌套循环连接 Nested-Loop Join(NLJ) 算法

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集

优化器一般会优先选择小表做驱动表，用where条件过滤完驱动表，然后再跟被驱动表做关联查询。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表是驱动表，左表是被驱动表，当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。
使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。Extra 中的 Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。

对于关联sql的优化

关联字段加索引

让mysql做join操作时尽量选择NLJ算法，驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描，总之，能走索引的过滤条件尽量都走索引

小表驱动大表

写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

in和exsits优化

当B表的数据集小于A表的数据集时，in优于exists

select * from A where id in (select id from B)
#等价于：
  for(select id from B){
      select * from A where A.id = B.id
    }

exists

当A表的数据集小于B表的数据集时，exists优于in

select * from A where exists (select 1 from B where B.id = A.id)
#等价于:
    for(select * from A){
      select * from B where B.id = A.id
    }

**count(*)查询优化**

字段有索引

count(*)≈count(1)> count(字段)>count(主键 id) //字段有索引， count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以 count(字段)>count(主键 id)

字段无索引

count(*)≈count(1)> count(主键 id)>count(字段) //字段没有索引 count(字段)统计走不了索引， count(主键 id)还可以走主键索引，所以 count(主键 id)>count(字段)

count(1)

跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。

**count(*)**

mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count(*)。

对于count(id)，mysql最终选择辅助索引而不是主键聚集索引，因为二级索引相对主键索引存储数据更少，检索性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)

常见优化方法

查询mysql自己维护的总行数

对于 myisam存储引擎的表做不带where条件的count查询性能是很高的，因为 myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算

对于 innodb存储引擎的表mysql 不会存储表的总记录行数，查询 count需要实时计算

show table status

如果只需要知道表总行数的估计值可以用 show table status like 'test' ，性能很高

将总数维护到Redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

首页的数量统计

增加数据库计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

MySQL数据类型选择

（1） 确定合适的大类型：数字、字符串、时间、二进制；
（2） 确定具体的类型：有无符号、取值范围、变长定长等。

在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用NULL（如果字段允许为NULL，需要1字节记录是否为NULL ）。

数值类型

如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
建议使用TINYINT代替ENUM、BITENUM、SET。
避免使用整数的显示宽度(参看INT显示宽度)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。
DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。
建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

INT显示宽度

字段的类型长度TINYINT(2)、INT(11)不会影响数据的插入，只会在使用ZEROFILL时有用，让查询结果前填充0。

日期和时间

MySQL能存储的最小时间粒度为秒。
建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。
TIMESTAMP是UTC时间戳，与时区相关。
DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。
有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它。

字符串

字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。
尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。聚簇索引，这个字段字节数很大都放在一个表里面影响效率
BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
BLOB和TEXT都不能有默认值。