mysql 索引优化 Explain的使用

分手后的思念是犯贱 2022-12-22 04:42 231阅读 0赞

索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本

MySQL提供了Explain，用于显示SQL执行的详细信息，可以进行索引的优化。

一、导致SQL语句执行慢的原因

1.硬件问题。如： 网络速度，内存不足，I/O吞吐量小，磁盘空间满了等。

2.没有索引 或者 索引失效。（表中某些数据被硬删除，会影响到索引，需要重建索引）

3.数据过多

4.服务器参数小，（重新设置my.cnf 表参数）

注意:  索引失效的情况有---> 条件中有 or ，like查询以 开头。

强制索引 force index(idx\_order\_levelDate)

explain select \* from itdragon\_order\_list force index(idx\_order\_levelDate) order by order\_level,input\_date;

**索引的创建**

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名，可要可不要；如果不要，当前的索引名就是该字段名。 
    ALTER TABLE `table_name` ADD UNIQUE (`column_list`) 
    ALTER TABLE `table_name` ADD PRIMARY KEY (`column_list`) 
    ALTER TABLE `table_name` ADD FULLTEXT KEY (`column_list`)

二、分析原因,找到切入点

1. 先观察， 开启慢查询日志，设置相应的阈值，在生产环境跑一天，（超过3s就是慢sql）

2.Explain和慢SQL分析； 如： SQL语句烂，索引没有或失效。关联查询过多等等。

3.Show Profile 是比 Explain更细节点

4.mysql 服务器参数调优，

三、Explain 分析

![41c451fac1cab2609484ea86a35ca969.png][]

索引使用情况在  possible\_keys、key和key\_len三列 。

**id**  值越大越先被执行

**table** 查询涉及的表或者衍生表

**select\_type :**

*  SIMPLE： 表示此查询不包含 UNION 查询或子查询
 *  PRIMARY： 表示此查询是最外层的查询
 *  SUBQUERY： 子查询中的第一个 SELECT
 *  UNION： 表示此查询是 UNION 的第二或随后的查询
 *  DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询
 *  UNION RESULT, UNION 的结果
 *  DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
 *  DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询）

### type: ###

system: 表中只有一条数据， 这个类型是特殊的 const 类型。  
　　const: 针对主键或唯一索引查询，只返回一条数据。速度非常快  
　　eq\_ref: 多表的 join 查询,效率比较高，两边表的数据量一样，一条对应一条。  
　　ref: 多表的 join 查询,针对于非唯一或非主键索引，  
　　range： 表示使用索引范围查询  
　　index: 表示全索引扫描  
　　ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。

**性能关系**：  ALL < index < range ~ index\_merge < ref < eq\_ref < const < system

**possible\_keys   **mysql 在查询时，可能使用到的索引

**key    **mysql 在查询时　, 真正使用到的索引

**key\_len** 查询优化器使用了索引的字节数

**ref ** 索引的哪个列被使用了

**rows   **查询结果需要扫描读取的数据行数  （越小越好）

优化：

explain  查询后，查看SQL语句的type类型 ， 索引是否使用。

![fee9313c1cf4d15a127db0ab16a9670a.png][]

![7c6cbe59012fc7a605053c7de92c29bd.png][]

[41c451fac1cab2609484ea86a35ca969.png]: /images/20221120/2a7a326f65b9463188373040a444ee82.png
[fee9313c1cf4d15a127db0ab16a9670a.png]: /images/20221120/c03bbb0985174245a03f52234a9c1d85.png
[7c6cbe59012fc7a605053c7de92c29bd.png]: /images/20221120/9075b06516b5452495cc743754ab8ee2.png