MySQL索引优化最佳实践

explain详解

1. id

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

2. select_type

   1. simple 简单查询，查询不包含子查询和union
   2. Primary 复杂查询最外层的select
   3. Subquery 包含在select中的子查询，不在from子句中
   4. Derived 包含在from子句中的子查询。Mysql会将结果存在一个临时表
   5. union 在union中的第二和随后的select

3. table

   1. 表示explain的一行正在访问哪个表

4. Partitions

   1. 如果是基于分区表Partitions字段会显示查询将访问的分区

5. type

   1. 表示关联类型或访问类型，即Mysql决定如何查找表中的行，查找数据行记录的大概范围。依次从最优到最差分别是：system>const>eq_ref>ref>range>index>ALL,一般来说保证查询达到range级别，最好达到ref
   2. NULL MySQL能够在优化阶段分解查询语句，在执行阶段不用再访问表或索引。
   3. Const,system mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system
   4. eq_ref primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
   5. Ref 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
   6. Range 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
   7. index 扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。
   8. ALL 全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了

6. Possible_keys

   1. 展示查询可能使用哪些索引来查找

   2. explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

      如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

7. key

   1. 展示MySQL实际采用哪个索引来优化对表的访问，如果没使用索引则该列是null。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

   2. key_len计算规则

      1. 字符串

         1. char(n)和varchar(n)，5.0.3以后版本中，**n均代表字符数，而不是字节数，**如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
         2. char(n)：如果存汉字长度就是 3n 字节
         3. varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

      2. 数值类型

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

      3. 时间类型

         1. date：3字节
         2. timestamp：4字节
         3. datetime：8字节

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

8. key_len

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

9. ref

   1. 这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

10. rows

    1. 这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

11. Filtered

    1. 该列是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

12. Extra 这一列展示的是额外信息。常见的重要值如下：

    1. Using index:使用覆盖索引

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

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

    3. Using index condition 查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

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

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

    6. select tables optimized away 使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是

索引最佳实践

1. 全值匹配
2. 最左前缀法则 --如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
3. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句
6. mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描
   < 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
7. is null,is not null 一般情况下也无法使用索引
8. like以通配符开头（‘$abc…’）mysql索引失效会变成全表扫描操作
9. 字符串不加单引号索引失效
10. 少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化
11. 范围查询优化
    

优化总结经验

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文件排序原理

1. 单路排序：一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
   2.双路排序（回表排序模式）：首先根据响应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在sort buffer中进行排序，排序完后需要再次取回其他需要的字段；
   3.MYSQL通过系统变量max_length_for_sort_data（默认1024字节）的大小和需要查询的字段总大小判断使用哪种排序模式

索引设计原则

1. 代码先行，索引后上
2. 联合索引尽量覆盖条件
3. 不要在小基数字段上建立索引
4. 长字符串可以采用前缀索引
5. where与order by冲突时优先where
6. 基于慢sql做优化

分页查询优化

1. 连续主键场景优化

select *from user limit 1000,5; 直接limit实际会查询1005条数据，然后抛弃前1000条拿后5条，效率低下
改写sql
select *from user where id >1000 limit 5;
只有在主键自增且连续并且结果是按主键排序的场景使用。

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

select *from user order by name limit 1000,5;
扫描整个索引并查找没索引的行，成本比扫描全表成本更高，所有优化器放弃使用索引。
优化关键在于让排序时返回的字段尽量少，所以让排序和分页操作先查出主键，然后根据主键查对应的记录
改写sql
select *from user u inner join (select id from user order by name limit 1000,5)ud on u.id=ud.id;
原sql使用的是filesort排序，优化后使用索引排序

join关联查询优化

1. mysql表关联常用算法

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

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

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

      1. 把驱动表数据读到join_buffer中，然后扫描被驱动表，把被驱动表每一行取出来跟join_buffer中的数据对比

对于关联sql的优化

1. 关联字段加索引
2. 小表驱动大表

in和exsits优化

1. 原则：小表驱动大表
2. 查询MySQL自己维护的总行数
3. show table status like ‘tableName’
4. 将总行数维护到Redis
5. 增加数据库计数表

阿里巴巴Mysql规范解读

1. 在mysql中，选择正确的数据类型，对于性能至关重要，一般遵循两步
   1. 确定合适的大类型：数字、字符串、时间、二进制
   2. 确定具体的类型：有无符号、取值范围、变长定长等

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 字节	(-128, 127)	(0, 255)	小整数值
SMALLINT	2 字节	(-32 768, 32 767)	(0, 65 535)	大整数值
MEDIUMINT	3 字节	(-8 388 608, 8 388 607)	(0, 16 777 215)	大整数值
INT或INTEGER	4 字节	(-2 147 483 648, 2 147 483 647)	(0, 4 294 967 295)	大整数值
BIGINT	8 字节	(-9 233 372 036 854 775 808, 9 223 372 036 854 775 807)	(0, 18 446 744 073 709 551 615)	极大整数值
FLOAT	4 字节	(-3.402 823 466 E+38, 1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0, (1.175 494 351 E-38, 3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 字节	(1.797 693 134 862 315 7 E+308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)	0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值
优化建议

1. 如果整型数据没有负数，如ID，建议指定为UNSIGEND无符号类型，容量可以扩大一倍
2. 建议使用tinyint代替enum,bitenum,set
3. 避免使用整型的显示宽度，不用int(10) 直接用int
4. decimal最适合保存精度要求高，而且用于计算的数据，使用时注意设置长度
5. 建议使用整型类型来运算和存储实数
6. 整数通常是最佳的数据类型，因为他速度快而且能使用AUTO_INCREMENT

日期类型

类型	大小(字节)	范围	格式	用途
DATE	3	1000-01-01 到 9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’ 到 ‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901 到 2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00 到 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00 到 2038-01-19 03:14:07	YYYYMMDDhhmmss	混合日期和时间值，时间戳
优化建议

1. MySQL能存储的最小时间粒度是秒
2. 建议用date数据类型来保存日期，MySQL中默认日期格式是yyyy-mm-dd
3. 用MySQL的内建类型date、time、datetime来存储时间而不是字符串
4. 当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。
5. DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。

字符串

类型	大小	用途
CHAR	0-255字节	定长字符串，char(n)当插入的字符数不足n时(n代表字符数)，插入空格进行补充保存。在进行检索时，尾部的空格会被去掉。
VARCHAR	0-65535 字节	变长字符串，varchar(n)中的n代表最大字符数，插入的字符数不足n时不会补充空格
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LONGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据
优化建议

1. 字符串的长度相差较大用varchar,字符串短且所有值都接近一个长度用char
2. char和varchar适用于人名、邮编、电话号码和不超过255个字符任意长度字母数字组合。那些要用来计算的数字不要用varchar类型保存。
3. 尽量少用blob和text,实在要用可以考虑把blob和text单独存在一张表用ID关联