JavaEE：MySQL优化工具使用

一、MySQL慢查询Log：

说明：慢查询Log开启后，会将超出指定时间的查询记录在Log中，可以查看并分析Log找出缓慢的查询事件。

1.开启慢查询Log(vi /etc/my.cnf)：

log_output='FILE,TABLE'    #将log输出到文件和mysql.slow_log表中，可以只指定一种方式
slow_query_log=ON          #开启慢查询log
long_query_time=0.100      #指定超过此时间的查询记入log，单位秒，用小数表示毫秒

2.查看慢查询Log：

(1)FILE方式(mysqldumpslow --help查询更多命令参数)：

mysql> mysqldumpslow /var/lib/mysql/localhost-slow.log

(2)TABLE方式：

mysql> select * from mysql.slow_log;

二、创建索引：

1.索引分类：

(1)以功能分：

主键索引：某表只有一列主键索引/列的值不允许null
唯一索引：索引列的值唯一/值允许null
普通索引：无约束，只为提高查询效率
组合索引：在多列上创建索引，查询时条件列顺序必须与创建索引时列顺序一致(最左优先)
全文索引：搜索含某个关键字的列，MySQL5.6.24-只支持MyISAM、MySQL5.6.24及+支持MyISAM/InnoDB引擎

(2)以数据结构分：

B+树/Hash/空间数据索引(R-Tree)/全文索引

(3)以存储分：

聚簇索引(查效率高/增删改效率低)、非聚簇索引(查效率低/增删改效率高)

2.索引使用：

(1)创建唯一索引：

mysql> create unique index 索引别名 on 表名(列名(长度值))

(2)创建普通索引：

mysql> create index 索引别名 on 表名(列名(长度值))

(3)创建组合索引：

mysql> create index 索引别名 on 表名 (列名1, 列名2)

(4)全文索引：

创建：

mysql> create fulltext index 索引别名 on 表名 (列名(长度值))

查询：

mysql> select * from 表名 where match(列名) against ('匹配值')

三、MySQL Explain使用：

说明：

Explain用于查看SQL语句执行计划，比如是否使用了索引、排序情况等。

1.Explain使用：

(1)使用explain查看优化信息：

格式：

explain 查询语句;

例：

mysql> explain select * from 表名 where 列名 = '值';

(2)使用explain+show warnings查看更多扩展信息：

格式：

explain 查询语句;
show warnings;

例：

mysql> explain select * from 表名 where 列名 = '值';
mysql> show warnings;

2.输出列表说明：

(1)id: 标识
(2)select_type(查询类型，值如下): 
SIMPLE：简单查询，未使用union或子查询
PRIMARY：最外层的查询
UNION：union中第二个及后面的select被标记为union。如果union被from子句中子查询包含，第一个select会标记成derived
DEPENDENT UNION：union中第二个或后面查询依赖了外面的查询时
UNION RESULT：union的结果
SUBQUERY：子查询第一个select
DEPENDENT SUBQUERY：子查询第一个select依赖了外面的查询时
DERIVED：包含在from子句子查询中的select，会将结果放在派生表中
DEPENDENT DERIVED：派生表依赖了其他的表
MATERIALIZED：物化子查询
UNCACHEABLE SUBQUERY：子查询，结果无法缓存
UNCACHEABLE UNION：union属于uncacheable subquery第二个或后面的查询
(3)table:  表名
(4)partitions:  查询结果所属分区，未分区为null
(5)type(连接类型，值如下，性能从上往下变差):  
system和const：使用主键/唯一索引等值查询，如where id=1。system属于const的特例(表只有一行)
eq_ref：多表关联查询使用主键/唯一索引作为查询条件，如select * from 表1,表2 where 表1.主键或唯一索引列=表2.主键或唯一索引列
ref：单表查询/多表关联查询使用普通/组合索引作为查询条件，如select * from 表1,表2 where 表1.普通索引列=表2.普通索引列
fulltext：使用全文索引作为查询条件
ref_or_null：类似ref，额外查询含null的记录，如where name=xxx or name is null
index_merge：查询中使用了多个索引且索引合并优化
unique_subquery：类似eq_ref，使用in且子查询是主键/唯一索引，如value in (select 主键或唯一索引列 from 表名 where 条件)
index_subquery：类似unique_subquery，子查询是普通索引，如value in (select 普通索引列 from 表名 where 条件)
range：使用了between、like、in()、>、>=、<、<=、<=>等范围查询条件，例select * from 表名 where 索引列 > 值
index：查询时遍历全索引
All：查询时遍历全表
(6)possible_keys和key:  索引别名，主键索引别名为PRIMARY
(7)key_len:  索引字段长度
(8)ref:  被引用的索引列，如const
(9)rows:  估算遍历行数，数值越小性能越好
(10)filtered:  所有数据中符合查询条件的百分占比
(11)Extra(附加信息): 
const row not found：查询时表数据为空
Deleting all rows：使用了优化的delete语句
Distinct：找到第一行匹配时，停止为当前行组合搜索更多行
FirstMatch：使用半连接的FirstMatch策略
Full scan on NULL key：子查询优化方式，无法通过索引访问null时
Impossible HAVING：having子句始终无法匹配任何行
Impossible WHERE：where始终无法匹配任何行
LooseScan：使用半连接的LooseScan策略
No matching min/max row：min(...)与max(...)没有匹配行
no matching row in const table：表中没有匹配唯一索引条件的行
No matching rows after partition pruning：分区修剪后，delete/update时没有匹配的行
No tables used：select后面没有from子句或有from dual子句时
Not exists：找到符合left join的行后，不会再去组合中检查此表中的更多行
Plan isn't ready yet：使用了explain for connection，还未给执行的语句创建执行计划时
Range checked for each record：检查每个记录的范围
Recursive：出现递归查询
Scanned N databases：处理information_schema表查询时，扫描了N个目录
Select tables optimized away：返回只有1行且需要读取指定列时，如explain select min(列名) from 表名
Skip_op