【Java架构师入门到精通提升】mysql优化二

3.1. 简介
使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。
EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的……等等，等等。
挑一个你的SELECT语句（推荐挑选那个最复杂的，有多表联接的），把关键字EXPLAIN加到前面。
EXPLAIN
SELECT * FROM userinfo u INNER JOIN jobinfo j ON u.jobinfoId=j.id;
查看执行计划：

参数解释：
id：查询的序号
select_type:select类型，simple表示简单的查询
table：引用的表
type：链接类型，all表示全表扫描，没有使用索引。
possible_keys：查询时可以使用的索引
key：查询时正在使用的索引
key_len：索引的长度
rows：查询的行数，乘积即为笛卡尔积
Extra：该列包含MySQL解决查询的详细信息。

3.1.1. 参数详解
id：这是SELECT的查询序列号。
select_type：SELECT类型，可以为以下任何一种：
SIMPLE：简单SELECT(不使用UNION或子查询)
PRIMARY：最外面的SELECT
UNION：UNION中的第二个或后面的SELECT语句
DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
UNION RESULT：UNION的结果。
SUBQUERY:子查询中的第一个SELECT
DEPENDENT SUBQUERY:子查询中的第一个SELECT，取决于外面的查询
DERIVED:导出表的SELECT(FROM子句的子查询)
table:输出的行所引用的表。
type:联接类型。下面给出各种联接类型，按照从最佳类型到最坏类型进行排序：
system表仅有一行(=系统表)。
const表最多有一个匹配行，它将在查询开始时被读取。
eq_ref比较的时候，“=”前后的变量都加了索引。
ref:前面的表加了索引。
index：该联接类型与ALL相同，只是索引树被扫描。
ALL：全表扫描。
possible_keys：possible_keys列指出MySQL能使用哪个索引在该表中找到行。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。想学习交流HashMap,nginx、dubbo、Spring MVC,分布式、高性能高可用、MySQL，redis、jvm、多线程、netty、kafka、的加尉xin（同英）：1253431195 扩列获取java进阶资料学习，无工作经验不要加哦！
key:显示MySQL实际决定使用的索引。如果没有选择索引，键是NULL。
key_len:显示MySQL决定使用的索引长度。如果索引是NULL，则长度为NULL。
ref：显示使用哪个列或常数与key一起从表中选择行。
rows：显示MySQL认为它执行查询时必须检查的行数。
Extra：该列包含MySQL解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串：
Distinct：MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。
Not exists：MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #)：MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。
Using filesort：MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行
Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。
Using temporary：为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。想学习交流HashMap,nginx、dubbo、Spring MVC,分布式、高性能高可用、MySQL，redis、jvm、多线程、netty、kafka、的加尉xin（同英）：1253431195 扩列获取java进阶资料学习，无工作经验不要加哦！
Using sort_union(…), Using union(…), Using intersect(…)：这些函数说明如何为index_merge联接类型合并索引扫描。
3.2. 优化方案
3.2.1. 查看匹配的列类型和长度是否相同
查看两张表链接的列的类型和长度是否相同，不同改为相同
ALTER TABLE 表名 MODIFY 列名 BIGINT(20);
3.3. 为相关联的列设置索引
查看索引：
SHOW INDEX FROM tbl_name;
创建索引：
ALTER TABLE 表名 ADD INDEX 索引名 (索引列) ;
删除索引：
drop index 索引名 on 表名;
显示使用索引：
USE INDEX
在你查询语句中表名的后面，添加 USE INDEX 来提供你希望 MySQ 去参考的索引列
表，就可以让 MySQL 不再考虑其他可用的索引。
Eg:SELECT * FROM mytable USE INDEX (mod_time, name) …
IGNORE INDEX
如果你只是单纯的想让 MySQL 忽略一个或者多个索引，可以使用 IGNORE INDEX 作
为 Hint。
Eg:SELECT * FROM mytale IGNORE INDEX (priority) …
FORCE INDEX
为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 Hint。
Eg:SELECT * FROM mytable FORCE INDEX (mod_time) …

3.4. 不使用索引的情况
下列情况下，Mysql 不会使用已有的索引：
1.如果 mysql 估计使用索引比全表扫描更慢，则不使用索引。例如：如果 key_part1
均匀分布在 1 和 100 之间，下列查询中使用索引就不是很好：
SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90
2.如果使用内存表并且 where 条件中不用＝索引列，其他> 、<、 >=、 <=均不使用
索引；
3.如果 like 是以％开始；
4.对 where 后边条件为字符串的一定要加引号，字符串如果为数字 mysql 会自动转为
字符串，但是不使用索引。
3.5. 查看索引使用情况
语法：
mysql> show status like ‘Handler_read%’;
如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数， 很低的值表明增加索引得到的性能改善不高， 因为索引并不经常使用 。
Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描，
该值较高。通常说明表索引不正确或写入的查询没有利用索引。
4. 其他优化