首页手记 MySQL简单架构二（explain，索引优化，删除重...

MySQL简单架构二（explain，索引优化，删除重复数据，区间统计，满查询日志）

标签：

MySQL

explain

explain可以对select,update,insert,replace,delete进行sql分析

对评论进行分页展示

SELECT customer_id,title,content FROM `product_comment` HWERE audit_status = 1 AND product_id = 199726 LIMIT 0,5;

结果（检查是否正确执行索引）
SQL如何使用索引
关联查询的执行顺序（mysql优化器根据索引的信息，会自动的调整索引的顺序）
查询扫描的数据行数

ID列
- ID列中的数据为一组数字，表示执行SELECT语句的顺序
- ID值相同时，执行顺序由上至下
- ID值越大优先级越高，越先被执行

查询一组分类下商品的标题

查询最小分类的ID对应最大商品ID的一个评论标题（3个select语句）

SELECT_TYPE列
- UNION RESULT时，ID值为null.
- DERIVED 衍生表，用来表示包含在FROM字句中的字查询。myslq递归的执行，并将结果放在临时表中，临时表就是派生表。

值	含义
SIMPLE	不包含子查询或是UNION操作的查询
PRIMARY	查询中如果包含任何子查询，那么最外层的查询则被标记为PRIMARY
SUBQUERY SELECT	SELECT列表中的子查询
DEPENDENT SELECT	依赖外部结果的子查询
UNION	Union操作的第二个或是之后的查询的值为union
DEPENDENT UNION	当UNION做为子查询时，第二或是第二个后的查询的UNION select type值
UNION RESULT	UNION产生的结果集
DERIVED	出现在FROM子句中的子查询

TABLE列，执行计划中的数据是由哪个表输出的
- 输出数据行所在的表的名称（如果有别名就显示别名）
- <unionM,N>由ID为M，N查询union产生的结果集（临时表）
- <derivedN>/<subqueryN>由ID为N的查询产生的结果（临时表，衍生表）
PARTITIONS列,查询分区表
- 如果不按照分区键选择，就会显示全部的分区，因为是跨分区扫描
- 对于分区表，显示查询的分区ID
- 对于非分区表，显示为NULL
TYPE列,查询中使用的一个类型（mysql访问数据的方式）

性能	值	含义
高	system	这是const联接类型的一个特例，当查询的表只有一行时使用
	const	表中有且只有一个匹配的行时使用，如对主键或是唯一索引的查询，这是效率最高的联接方式
	eq_ref	唯一索或主键引查找，对于每个索引键，表中只有一条记录与之匹配。(常见于join查询)
	ref	非唯一索引查找，返回匹配某个单独值的所有行
	ref or null	类似于ref类型的查询，但是附加了对NULL值列的查询
	index_merge	该联接类型表示使用了索引合并优化方法
	range	索引范围扫描，常见于between、>、<这样的查询条件
	index	FULL index Scan全索引扫描，同ALL的区别是，遍历的是索引树
低	ALL	FULL TALBLE Scan 全表扫描，这是效率最差的联接方式

Extra列（扩展列，包含mysql如何执行查询的一些附加信息）

值	含义
Distinct	优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
Not exists	使用not exists来优化查询
Using filesort	使用额外操作进行排序，通常会出现在order by或 group by查询中。有可能是在内存中进行，也有可能是在临时文件进行。取决于结果集的大小出现该值通常说明查询效率比较差
Using index	使用了覆盖索引进行查询
Using temporary	mysql需要使用临时表来处理查询，常见于排序，子查询和分组查询
Using where	需要在MySQL服务器层使用WHERE条件来过滤数据
select tables optimized away	直接通过索引来获得数据，不用访问表

POSSIBLE_KEYS列
- 指出MySQL能使用那些索引来优化查询
- 查询列所涉及到的列上的索引都会被列出，但不一定会被使用
KEY列
- 查询优化器优化查询实际所使用的索引
- 如果没有可用的索引，则显示为NULL
- 如查询使用了覆盖索引，则该索引仅出现在Key列中
KEY_LEN列
- 表示索引字段的最大可能长度
- Key len的长度由字段定义计算而来，并非数据的实际长度
Ref列(当前表在利用key列中的索引进行查询时，所用到的列或者常量)
- 表示那些列或常量被用于查找索引列上的值
Rows列
- 表示MySQL通过索引统计信息，估算的所需读取的行数
- Rows值的大小是个统计抽样结果，并不十分准确
Filtered列
- 表示返回结果的行数占需读取行数的百分比
- Filtered列的值越大越好
- Filtered列的值依赖说统计信息
执行计划的限制
- 无法展示存储过程，触发器，UDF对查询的影响
- 无法使用EXPLAIN对存储过程进行分析
- 早期版本的MySQL只支持对SELECT语句进行分析。

优化评论分页查询（添加索引）

使用情况：中间结果集差距很小的情况，或者数据量很小的情况

首先，我们可以考虑对where条件添加索引，就是audit_status 和 product_id添加一个联合索引
问题：audit_status 和 product_id哪个放在最左侧？
根据索引设计规范，先计算一下这两列在表中的区分度，数据越接近1，区分度越高
缺点：越往后翻页，查询效率越来越差，时间也越来越长，尤其数据量很大

进一步优化:改写

数据库访问开销=索引IO+索引全部记录结果对应表数据的IO
数据库访问开销=索引IO+索引返回15条记录对应表数据的IO
- IO节约很多
- 在任意位置翻页的消耗都是相同的
- 使用情况：中间结果集差距很大的情况，或者ORDER BY,WHERE有对应的覆盖索引
该SQL使用前提：comment_id是主键，而且有覆盖索引（product_id和audit_status联合索引）

需求：删除重复数据

删除评论表中对同一订单同一商品的重复评论，只保留最早的一条
- 步骤一：意看是否存在对于一订单同一商品的重复评论
- 步骤二：备份product_comment表
- 步骤三：删除同一订单的重复评论
测试查询数据

第二步
CREATE TABLE bak_product_comment_161022 LIKE product_comment;
INSERT INTO bak_product_comment_161022 SELECT * FROM product_comment;
或者
CREATE TABLE bak_product_comment_161022 AS SELECT  * FROM product_comment;