若干sql语句分析
案例一：根据某字段判断是否下线/上线1）原SQL
update d_table set enabled = 1 where a_id in (select a_id from c_table where b_seq = 'xxx');
Query OK, 0 rows affected (46.69 sec)
Rows matched: 220 Changed: 0 Warnings: 0
2）优化后SQL
mysql> update d_table a, c_table b set a.enabled = 1 where a.a_id = b.a_id and b.b_seq = 'xxx';
Query OK, 7 rows affected (0.01 sec)
Rows matched: 220 Changed: 7 Warnings: 0
3）分析
首先是涉及的2个数据表的结构
CREATE TABLE `c_table` (
`xxx1` varchar(30) CHARACTER SET latin1 NOT NULL,
`b_seq` varchar(30) CHARACTER SET latin1 NOT NULL,
`b_id` varchar(20) CHARACTER SET latin1 NOT NULL,
`a_id` bigint(20) NOT NULL,
`xxx` varchar(128) DEFAULT NULL,
`xxx` varchar(512) DEFAULT NULL,
`xxx` int(1) NOT NULL DEFAULT '0',
`xxx` varchar(30) NOT NULL DEFAULT '',
PRIMARY KEY (`a_id`),
KEY `index1` (`b_seq`,`b_id`),
KEY `idx_b_id` (`b_id`),
KEY `ix_xxx1_b` (`xxx1`,`b_seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `d_table` (
`a_id` bigint(20) NOT NULL DEFAULT '0',
`enabled` char(1) NOT NULL,
`updated` datetime DEFAULT NULL,
PRIMARY KEY (`trans_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
原SQL的性能分析：
mysql> explain update d_table set enabled = 1 where a_id in (select a_id from c_table where b_seq = 'xxx');
************************** 1. row **************************
id: 1
select_type: PRIMARY
table: d_table
type: index //注释：仅仅快于ALL，区别在于只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 15045011 //注释：hotel_control表的总行数为：15377231，而本次的子查询的查询结果个数为223个。可见对于in语句，当其使用的结果集较大时，因为多次查询索引表，其性能比全量查询也好不到哪里去，甚至可能更差
Extra: Using where
************************** 2. row **************************
id: 2
select_type: DEPENDENT SUBQUERY
table: c_table
type: unique_subquery
possible_keys: PRIMARY,index1
key: PRIMARY
key_len: 8
ref: func
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
优化后SQL性能分析：
mysql> explain update d_table a, c_table b set a.enabled = 1 where a.a_id = b.a_id and b.b_seq = 'xxx';
************************** 1. row **************************
id: 1
select_type: SIMPLE
table: b
type: ref //注释：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。
possible_keys: PRIMARY,index1
key: index1
key_len: 32
ref: const
rows: 222
Extra: Using where; Using index
************************** 2. row **************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref //注释：比ref还要快
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: c_table.b.a_id
rows: 1
Extra: NULL
2 rows in set (0.00 sec)
4）结论
对于in语句，当其使用的结果集较大时，因为多次查询索引表，其性能比全量查询也好不到哪里去，甚至可能更差。
而且，对于版本低于4.0的mysql，in语句实际上是不支持索引的，目前网上关于in语句是否使用索引的答案不一，也正是这个原因，很多过时的文章给出的答案仍然为不使用索引。
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，ref_or_null及以上的类型为很理想的索引使用情况。实际应用中，得保证查询至少达到range级别。
案例二：查询每个配置文件的最大版本的所有信息
1）原SQL
SELECT * from d_config as a where version=(select MAX(version) from d_config as b where a.conf_name=b.conf_name );
30分钟内无结果
2）优化后SQL
SELECT * from d_config where id in (select max(id) from d_config group by conf_name);
410 rows in set (20.11 sec)
3）分析
直接使用explain查看执行计划，并不能从索引使用方面得到有效的结论。
而实际上从查询的数据集上可以得到如下的分析：
原SQL：
使用conf_name将d_conf数据表和自身进行了联查，查询的数据集为：
select count(*) from d_config as a, d_config as b where a.conf_name=b.conf_name;
+----------+
| count(*) |
+----------+
| 18190446 |
+---------------+
优化后的SQL：
查询的数据集即为d_conf表大小:
select count(*) from d_config;
+----------+
| count(*) |
+----------+
| 19978 |
+---------------+
4）结论
缩小查询的数据集可以有效的减小执行时间。
案例三：某系统的索引使用
很久前做的一个项目了，具体的表和sql语句不好找了，此处仅进行概述。
背景：
某系统有个表的数据量很大，我们该系统的一个查询sql的执行时间要40多分钟，该查询sql的where条件为大量的某字段（后评估：对于某些代理商，该值可达8w）。
解决策略：
首先，进行了查询拆分，即循环查询，每次只查询1k的此字段，从而避免对数据表的长时间锁表，造成业务阻塞。
拆分后的1k的此字段的执行时间仍然为几十秒
然后，针对sql的where条件为该数据表建立了索引，DB变更执行后，1k的此字段的执行时间缩短为毫秒级别，问题得到解决。
页:
[1]