接着上一篇sql优化来说
1. 定位执行效率较低的sql 语句
通过两种方式可以定位出效率较低的sql 语句。
(1) 通过上篇讲的慢日志定位，在mysqld里写一个包含所有执行时间超过 long_query_time秒的sql语句的日志文件，后面具体介绍。 (2) 通过show processlist 实时定位线程状态，是否锁表等，下面简单演示下show processlist：先模拟会话1表锁，再会话2更新该表city的数据，由于会话1表锁没有释放，会话2更新会一直会等待尝试去获取更新锁，再通过show processlist查看
-- 会话 1获取city 表锁LOCK TABLE city READ;-- 会话2更新city表UPDATE city SET citycode='001'
查看发现: 状态列中找到waiting for table metadata lock(等待 table元数据锁)，当前线程的info 信息 如下所示：
2. 通过explain 来分析sql执行计划
通过上篇的慢日志定位和processlist 找出效率低的sql语句后，可以通过explain或者desc命令获取mysql 如何执行查询语句的信息。
-- 查看执行计划（二种方式一样） DESC SELECT LedRecycleInfoLogID FROM LedLogInfo WHERE LedRecycleInfoLogID=2; EXPLAIN SELECT LedRecycleInfoLogID FROM LedLogInfo WHERE LedRecycleInfoLogID=2;
| 类型 | 说明 |
| 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) ;
索引可具体参考：