mysql 开发进阶篇系列 2 SQL优化(explain分析)

时间:2022-09-18 07:28:04

   接着上一篇sql优化来说

  1. 定位执行效率较低的sql 语句

  通过两种方式可以定位出效率较低的sql 语句。
  (1) 通过上篇讲的慢日志定位,在mysqld里写一个包含所有执行时间超过 long_query_time秒的sql语句的日志文件,后面具体介绍。
  (2) 通过show processlist 实时定位线程状态,是否锁表等,下面简单演示下show processlist。

-- 会话 1获取city 表锁
LOCK TABLE city READ;
-- 会话2更新city表
UPDATE city SET citycode='001'

  查看发现: 状态列中找到waiting for table metadata lock(等待 table元数据锁),当前线程的info 信息 如下所示:

mysql 开发进阶篇系列 2 SQL优化(explain分析)  

   2. 通过explain 来分析sql执行计划

  通过上面的慢日志定位和processlist 找出效率低的sql语句后,可以通过explain或者desc命令获取mysql 如何执行查询语句的信息。

--  查看执行计划
 DESC SELECT   LedRecycleInfoLogID FROM LedLogInfo WHERE LedRecycleInfoLogID=2;
 EXPLAIN SELECT   LedRecycleInfoLogID FROM LedLogInfo WHERE LedRecycleInfoLogID=2;

mysql 开发进阶篇系列 2 SQL优化(explain分析)

类型

说明

Select_type 表示select 的类型

取值有: simple:简单表不使用表连接或子查询

       Primary:主查询

       Union: union中的第二个或者后面的查询语句

       Subquery: 子查询中的第一个select 

Type 表示表的连接类型

性能由好到差依次是: system: 表中仅有一行。

 Const: 单表中最多有一个匹配行, 例如 primary key, unique index

                   Eq_ref: 多表连接下使用primary key 或者unique index

                   Ref: 与Eq_ref区别在于使用普通索引。

                   Ref_or_null: 与Ref区别在于条件中包含有null值的查询

                   Index_merge: 索引合并优化

                   Unique_subquery: in的后面是一个查询主键字段的子查询

  Index_subquery: 与 Unique_subquery区别在于in后面查询非唯一索引字段的子查询

                   Range: 单表中的范围查询

                   Index: 全表索引扫描

                   All :全表扫描

           Possible_keys

表示查询时,可能使用的索引

            key

表示实际使用的索引

           Key_len

索引字段的长度.  长度越短, 性能越好

           rows

扫描的行数

           extra

执行情况的说明和描述

  3. 确定问题采取优化措施

  通过上面的索引解释,可以对照sql语句进行问题确认,以及索引的优化。如重点查看 rows 扫描了多少行, type 取值对应的性能, key字段和extra描述都可以来确定该语句是否需要调优。下面是各种索引的创建:

-- 主键索引
ALTER TABLE city ADD PRIMARY KEY(city_id);
-- 唯一索引
ALTER TABLE city ADD UNIQUE  KEY(city_id);
-- 普通索引 或叫辅助索引
CREATE INDEX ixcityname ON city(cityname);
-- 前缀索引 cityname字段创建10个字节
CREATE INDEX ixcityname ON city(cityname(10));
-- 复合索引  创建city表的多列
CREATE INDEX ix1 ON city(cityname(10),citycode);
-- 外键索引
ALTER TABLE city ADD KEY idx_fk_country_id(country_id) ;

   具体参考:mysql 开发基础系列15 索引的设计和使用