Mysql查询与慢查询日志分析

1.SQL性能下降的原因

1.1 等待时间长：

锁表导致查询一直处于等待状态

1.2 执行时间长：

1.查询语句问题
2.索引失效
3.关联查询太多join
4.服务器调优及各个参数的设置

2.SQL遵守的主要优化原则

2.1 只返回所需的结果：

1.尽可能带上 WHERE 条件，过滤掉不需要的数据行
2.避免使用 select * from

2.2 确保查询使用了正确的索引：

1.经常出现在 WHERE 条件中的字段建立索引,可以避免全表扫描；
2.将 ORDER BY 排序的字段加入到索引中，可以避免额外的排序操作；
3.多表 连接查询的 关联字段 建立索引，可以提高连接查询的性能；
4.将 GROUP BY 分组操作字段加入到索引中，可以利用索引完成分组。

2.3 避免让索引失效：

1.在 WHERE 子句中对索引字段进行 表达式运算 或者使用 函数 都会导致索引失效
2.使用 LIKE 匹配时，如果通配符出现在左侧无法使用索引
3.如果 WHERE 条件中的字段上创建了索引，尽量设置为 NOT NULL

3.SQL的执行顺序

3.1 编写的SQL如下:

	SELECT 
	DISTINCT <select_list>
	FROM <left_table> <join_type>
	JOIN <right_table> ON <join_condition>
	WHERE <where_condition>
	GROUP BY <group_by_list>
	HAVING <having_condition>
	ORDER BY <order_by_condition>
	LIMIT<limit_number>

MySQL执行的SQL:

	1  FROM <left_table> <join_type>
	2  ON <join_condition>
	3  <join_type> JOIN <right_table>
	4  WHERE <where_condition>
	5  GROUP BY <group_by_list>
	6 HAVING <having_condition>
	7 SELECT
	8 DISTINCT <select_list>
	9 ORDER BY <order_by_condition>
	10 LIMIT <limit_number>

1.FORM 子句：左右两个表的笛卡尔积
2.ON ：筛选满足条件的数据
3.JOIN ：如果是 inner join 那就正常,如果是 outer Join则会添加回来上面一步过滤掉的一些行
4.WHERE：对不满足条件的行进行移除,并且不能恢复
5.GROUP BY：分组后只能得到每组的第一行数据,或者聚合函数的数值

3.2 例子:

	SELECT
		id, sex, 
		COUNT(*) AS num 
	FROM employee 
	WHERE name IS NOT NULL
	GROUP BY sex 
	ORDER BY id

上面的SQL执行执行顺序如下:

1. 首先执行 FROM 子句, 从 employee 表组装数据源的数据
2. 执行 WHERE 子句, 筛选 employee 表中所有name不为 NULL 的数据
3. 执行 GROUP BY 子句, 按 “性别” 列进行分组
4. 执行 SELECT 操作，获取需要的列
5. 最后执行 ORDER BY，对最终的结果进行排序

4.JOIN查询得七种方式

JOIN查询可分为四类：内连接 、左连接 、右连接、 全连接

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个SELECT 语句会删除重复的数据。

5.慢查询日志分析

5.1 慢查询

1.默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。
2.如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。
3.慢查询日志支持将日志记录写入文件和数据库表。

5.2 慢查询参数

5.2.1 执行下面的语句

SHOW VARIABLES LIKE “%query%” ;

5.2.2 执行下面的语句

1.slow_query_log：是否开启慢查询日志， 1 表示开启， 0 表示关闭
2.slow_query_log_file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。
3.long_query_time： 慢查询阈值，当查询时间多于设定的阈值时，记录日志。

5.2.3 慢查询配置方式

1. 查看慢查询日志是否开启： SHOW VARIABLES LIKE ‘%slow_query_log%’;
2. 设置slow_query_log的值来开启：set global slow_query_log=1;
3. 使用 set global slow_query_log=1 开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
添加如下内容：
①.slow_query_log =1
②.slow_query_log_file=/lib/mysql/slow_query.log（日志存放路径）
4. 那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数long_query_time 控制，默认情况下long_query_time的值为10秒.
①. show variables like 'long_query_time’
②. set global long_query_time=1
③.记得修改之后需要重新连接或新开一个会话才能看到修改值
5. log_output 参数是指定日志的存储方式。
①. log_output=‘FILE’ 表示将日志存入文件，默认值是’FILE’。
②. log_output=‘TABLE’ 表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log 表中。
③. 也同时支持以上两种存储方式.配置的时候以逗号隔开即可，如：log_output=‘FILE,TABLE’。
6. 系统变量 log_queries_not_using_indexes ：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。

6. MySQL索引优化

6.1 索引简介

6.1.1 什么是索引:

1.索引就是 排好序的 ,帮助我们进行快速查找的 数据结构。
2.索引是一个 排好序 的列表，在这个列表中存储着 索引的值 和包含这个值的数据所在行的 物理地址。在数据库十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先 通过索引表找到该行数据对应的物理地址然后访问相应的数据。
3.简单来讲，索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够显著地提高数据查询的效率，从而提升服务器的性能。

6.2 索引的种类:

6.2.1 普通索引

普通索引是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

创建普通索引的方式：
	CREATE INDEX <索引的名字> ON tablename (字段名); 
	ALTER TABLE tablename ADD INDEX [索引的名字] (字段名); 
	CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

6.2.2 唯一索引

唯一索引与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值 。

创建普通索引的方式：
	CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名); 
	ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
	CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

6.2.3 主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。

创建普通索引的方式：
	CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
	ALTER TABLE tablename ADD PRIMARY KEY (字段名);

6.2.4 复合索引

用户可以在多个列上建立索引，这种索引叫做 组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引 复合索引 所需的 开销更小。

创建普通索引的方式：
  CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
  ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
  CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

注意事项：

1. 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
2. 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索
   引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

6.2.5 全文索引

1.查询操作在数据量比较少时，可以使用 like 模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有 MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。

2.全文索引方式有自然语言检索 IN NATURAL LANGUAGE MODE 和布尔检索 IN BOOLEAN MODE 两种。和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字

创建普通索引的方式：
	  CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
	  ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
	  CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

注意事项：

1. 全文索引必须在字符串、文本字段上建立。
2. 全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）

6.3 索引的优势与劣势:

6.3.1 优缺点:

优点:
①.提高数据检索的效率,降低数据库的IO成本
②.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
缺点:
①.创建 索引和 维护 索引要耗费时间，这种时间随着数据量的增加而增加
②.索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
③.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

6.3.2 创建索引的原则:

①.在 经常 需要 搜索的列 上创建索引，可以加快搜索的速度；
②.在作为 主键的列 上创建索引，强制该列的 唯一性 和组织表中 数据的排列结构；
③.在经常用在 连接的列 上，这些列主要是一些 外键，可以 加快连接 的速度；
④.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
⑤.在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

6.4 索引原理:

MySQL中索引的采用数据结果有两种，一种是Hash，另一种是BTree。

6.4.1 HASH结构:

1. Hash底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构：
   对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。
   
2. Hash索引的缺点：
   ①.哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
   ②.哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
   ③.哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找。
3. Hash索引的优点：
   ①.只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引
   ②.访问哈希索引的数据非常快，除非有很多哈希冲突。

6.4.2 B+Tree结构:

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。

1. B+Tree结构：
   ①.非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
   ②.叶子节点包含了所有的索引值和data数据
   ③.叶子节点用指针连接，提高区间的访问性能
   
2. B树索引的应用：
   ①. 全键值查询 ： where x=123
   ②. 键值范围查询 ： where 45 < x < 123

6.5 EXPLAIN字段介绍【待完善】:

7.0 MySQL Explain命令详解：type列详解及案例分析:

可参考：https://zhuanlan.zhihu.com/p/358920539