乐趣区

关于mysql:MySQL-索引分析与优化

jiezi

Thresh

EXPLAIN

MySQL 提供了一个 EXPLAIN 命令，它能够对 SELECT 语句进行剖析，并输入 SELECT 执行的详细信息，供开发人员有针对性的优化。

EXPLAIN SELECT * from USERS where id >1 ;

select_type
示意查问的类型。罕用的值如下：

SIMPLE：示意查问语句不蕴含子查问或 union
PRIMARY：示意此查问是最外层的查问
UNION：示意此查问是 UNION 的第二个或后续的查问
EXPLAIN SELECT * from user WHERE id < 3;
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 的计算规定如下：

字符串类型
    字符串长度跟字符集无关：latin1=1、gbk=2、utf8=3、utf8mb4=4
    char(n)：n* 字符集长度
    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 filesort 倡议优化。Using temprorary
    查问应用到了长期表，个别呈现于去重、分组等操作。

回表查问

InnoDB 索引有聚簇索引 (主键索引) 和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB 必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无奈间接定位行记录，通常状况下，须要扫码两遍索引树。
先通过辅助索引定位主键值，而后再通过聚簇索引定位行记录，这就叫做回表查问，它的性能比扫一遍索引树低。

总结：通过索引查问主键值，而后再去聚簇索引查问记录信息

笼罩索引

即 explain 的输入后果 Extra 字段为 Using index 时，可能触发索引笼罩。

EXPLAIN SELECT e.* from EMPLOYEE e INNER JOIN COMPANY c on e.companyId = c.id

最左前缀准则

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

CREATE TABLE `user2` (`userid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL DEFAULT '',
  `password` varchar(20) NOT NULL DEFAULT '',
  `usertype` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`userid`),
  KEY `a_b_c_index` (`username`,`password`,`usertype`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

当存在 username 时会应用索引查问：

explain select * from user2 where username = '1' and password = '1';

当没有 username 时，不会应用索引查问：

explain select * from user2 where password = '1';

LIKE 查问

MySQL 在应用 like 含糊查问时，索引能不能起作用？

MySQL 在应用 Like 含糊查问时，索引是能够被应用的，只有把 % 字符写在前面才会应用到索引。

select * from user where name like '%o%'; // 不起作用
select * from user where name like 'o%'; // 起作用
select * from user where name like '%o'; // 不起作用

NULL 查问

如果 MySQL 表的某一列含有 NULL 值，那么蕴含该列的索引是否无效？

对 MySQL 来说，NULL 是一个非凡的值，从概念上讲，NULL 意味着“一个未知值”，它的解决形式与其余值有些不同。
比方：不能应用 =，<，> 这样的运算符，对 NULL 做算术运算的后果都是 NULL，count 时不会包含 NULL 行等，NULL 比空字符串须要更多的存储空间等

NULL 列须要减少额定空间来记录其值是否为 NULL。对于 MyISAM 表，每一个空列额定占用一位，四舍五入到最靠近的字节。

尽管 MySQL 能够在含有 NULL 的列上应用索引，但 NULL 和其余数据还是有区别的，不倡议列上容许为 NULL。最好设置 NOT NULL，并给一个默认值，比方 0 和‘’空字符串等，如果是 datetime 类型，也能够设置零碎以后工夫或某个固定的非凡值，例如 ’1970-01-01 00:00:00’。

索引与排序

MySQL 查问反对 filesort 和 index 两种形式的排序，filesort 是先把后果查出，而后在缓存或磁盘进行排序操作，效率较低。应用 index 是指利用索引主动实现排序，不需另做排序操作，效率会比拟高。

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

  • 双路排序：须要两次磁盘扫描读取，最终失去用户数据。第一次将排序字段读取进去，而后排序；第二次去读取其余字段数据。
  • 单路排序：从磁盘查问所需的所有列数据，而后在内存排序将后果返回。如果查问数据超出缓存 sort_buffer，会导致屡次磁盘读取操作，并创立长期表，最初产生了屡次 IO，反而会减少累赘。
  • 解决方案：少应用 select *；减少 sort_buffer_size 容量 max_length_for_sort_data 容量。

如果咱们 Explain 剖析 SQL，后果中 Extra 属性显示 Using filesort，示意应用了 filesort 排序形式，须要优化。如果 Extra 属性显示 Using index 时，示意笼罩索引，也示意所有操作在索引上实现，也能够应用 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)索引

WHERE 子句或者 ORDER BY 子句中索引列应用了表达式，包含函数表达式
    explain select id from user order by abs(age); // 对应 (age) 索引
退出移动版