侠客岛的含笑
2019-04-01 06:47:35浏览 3483
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;
- 子查询：查询出所有商品中订单的重复评论的最小评论ID(要保留，最早的) 关联商品评论表，删除，相同订单，相同商品，大的评论ID
需求：分区间统计
- 根据订单主表（order_master）查询出所有用户消费总金额
- 关联登陆日志表和订单主表
- CASE区间分隔
- COUNT 用户量统计
需求：捕获有问题的SQL-慢查日志
快速分析慢查询日志-mysqldumpslow