快乐学习
前程无忧、中华英才非你莫属!

MySQL线上SQL优化

最近在线上发现很多性能有问题的sql,开发写sql语句的时候,没充分考虑是否用上索引了,所以这个坑得DBA来填,好了,废话不多说,把一些线上的优化经验跟大家分享。

由于是线上的表,所以就不公开具体的表结构了,请大家体谅,我会模拟一个类似的表来说明当时的性能问题:

当时的表结构类似此表:

mysql> show create table testG        
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aa_id` int(11) DEFAULT NULL,
  `dealername` varchar(45) DEFAULT NULL,
  `dealertype` int(2) DEFAULT NULL,
  `bb_id` int(11) NOT NULL,
  `membername` varchar(45) DEFAULT NULL,
  `createat` datetime DEFAULT NULL,
  `creator_id` int(11) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  `comp_id` int(11) DEFAULT NULL,
  `companyname` varchar(45) DEFAULT NULL,
  `cc_id` int(11) DEFAULT NULL,
  `level_id` int(2) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shopmember_unique` (`aa_id`,`bb_id`,`cc_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=301554 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

当时查看Lepus 的慢查询监控,看到大量的这类SQL语句,而且消耗时长有点长:

 

大量类似以下的SQL语句:

select aa_id,dealername,dealertype,membername from  test where level_id <=4 order by aa_id limit 243000, 100;

下面我们看一下SQL语句的执行计划:

mysql> explain select aa_id,dealername,dealertype,membername from  test where level_id <=4 order by aa_id limit 243000, 100;            
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL | 301508 | Using where; Using filesort |+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+1 row in set (0.00 sec)

很多人一看表结构,发现在列level_id没索引,添加一个普通索引就完事啦!下面我们来试试:

mysql> alter table test add key (level_id);
Query OK, 301508 rows affected (3.71 sec)
Records: 301508  Duplicates: 0  Warnings: 0

mysql> explain select * from  test where level_id <=4 order by aa_id limit 243000, 100;
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------------+| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows   | Extra                       |+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------------+|  1 | SIMPLE      | test  | range | level_id      | level_id | 4       | NULL | 301393 | Using where; Using filesort |+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------------+1 row in set (0.00 sec)

mysql> 

添加索引后,可以看到用上索引了,但效果相对之前并没有很大的提升,还有些人可能会说,在level_id和aa_id添加组合索引,性能可能就好了,我们再来看下:

mysql> alter table test add key (level_id,aa_id);    
Query OK, 301508 rows affected (3.75 sec)
Records: 301508  Duplicates: 0  Warnings: 0

mysql> explain select * from  test where level_id <=4 order by aa_id limit 243000, 100;
+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------------+| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows   | Extra                       |+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------------+|  1 | SIMPLE      | test  | range | level_id      | level_id | 4       | NULL | 301218 | Using where; Using filesort |+----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------------+1 row in set (0.00 sec)

mysql> 

可以看到,效果还是一样的差。为什么显示用上了索引,却还扫描了几十万行呢?

我们回顾一下不会用上索引的几种情况:(可以参考我的之前写的常用SQL语句优化

• 两个表关联字段类型不一样(也包括长度不一样)
• 通过索引扫描的记录数超过30%,变成全表扫描
• 联合索引中,第一个索引列使用范围查询,且返回数据超表总数据的30%
• 联合索引中,第一个查询条件不是最左索引列
• 模糊查询条件列最左以通配符 % 开始
• 内存表(HEAP 表)使用HASH索引时,使用范围检索或者ORDER BY
• 两个独立索引,其中一个用于检索,一个用于排序(只能用到部份)
• 使用了不同的 ORDER BY 和 GROUP BY 表达式

 

上面的SQL语句,符合了上面的联合索引中,第一个索引使用范围查询所以用不上索引,我们直接查询看看用时为多少:

mysql> select * from test where level_id <=4 order by aa_id limit 243000, 100;
100 rows in set (1.63 sec)

没用上索引,那我们应该怎么优化它呢?我们应该用延迟关联的思想,把sql语句修改为如下:

写法一:

mysql> reset query cache;
Query OK, 0 rows affected (0.00 sec)

mysql> explain SELECT a.* FROM  test a,(select id from test where level_id <=4 order by aa_id limit 243000, 100) b where a.id=b.id ;
+----+-------------+------------+--------+---------------+----------+---------+------+--------+-----------------------------+| id | select_type | table      | type   | possible_keys | key      | key_len | ref  | rows   | Extra                       |+----+-------------+------------+--------+---------------+----------+---------+------+--------+-----------------------------+|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL     | NULL    | NULL |    100 |                             ||  1 | PRIMARY     | a          | eq_ref | PRIMARY       | PRIMARY  | 4       | b.id |      1 |                             ||  2 | DERIVED     | test       | range  | level_id      | level_id | 4       | NULL | 301218 | Using where; Using filesort |+----+-------------+------------+--------+---------------+----------+---------+------+--------+-----------------------------+3 rows in set (0.30 sec)

mysql> SELECT a.* FROM  test a,(select id from test where level_id <=4 order by aa_id limit 243000, 100) b where a.id=b.id ;

100 rows in set (0.30 sec)

可以看到速度快了几倍,现在数据量只有几十万,如果几百万,效果会更明显,为什么这样写会比之前的效果好呢?因为延迟关联通过覆盖索引返回所需数据行的主键,再根据主键关联原表获得需要的数据,所以速度比之前快上不少。

写法二:

mysql> EXPLAIN SELECT * FROM `test` INNER JOIN ( SELECT id FROM `test` where level_id <=4 order by aa_id limit 243000, 100) t2 USING (id);
+----+-------------+------------+--------+---------------+----------+---------+-------+--------+-----------------------------+| id | select_type | table      | type   | possible_keys | key      | key_len | ref   | rows   | Extra                       |+----+-------------+------------+--------+---------------+----------+---------+-------+--------+-----------------------------+|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL     | NULL    | NULL  |    100 |                             ||  1 | PRIMARY     | test       | eq_ref | PRIMARY       | PRIMARY  | 4       | t2.id |      1 |                             ||  2 | DERIVED     | test       | range  | level_id      | level_id | 4       | NULL  | 301218 | Using where; Using filesort |+----+-------------+------------+--------+---------------+----------+---------+-------+--------+-----------------------------+3 rows in set (0.30 sec)

mysql> 

 

 

 

优化案例二:

表结构是:

mysql> show create table test2G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `code` varchar(32) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `status` int(2) DEFAULT '1',
  `createat` datetime DEFAULT NULL,
  `write_id` int(11) DEFAULT NULL,
  `creator_id` int(11) DEFAULT NULL,
  `dealer_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_index` (`code`) USING BTREE,
  KEY `dealer_id` (`dealer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7014142 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

slowlog里有大量这样的查询:

select count( id ) from `test2` where createat between '2015-05-26 00:00:00' and '2015-05-26 23:59:59'  and status not in(7) and creator_id=8774 and write_id=925;          

查看下执行计划:

mysql> explain select count( id ) from `test2` where createat between '2015-05-26 00:00:00' and '2015-05-26 23:59:59'  and status not in(7) and creator_id=8774 and write_id=925;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    | NULL | 5135067 | Using where |+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+1 row in set (0.00 sec)

mysql> 

没有索引,做了全表扫描,有些开发人员创建表的时候考虑得不周到,导致频繁出现影响性能的sql,我们添加组合索引看看效果(这里要注意一下,在线上如果是5.6以下的版本,对于一些大数据的表,别直接添加索引,因为这个过程会阻塞DML操作的,如果添加索引需要的时间是几个小时或者更多,这是很悲剧的一件事情,个人经验,小数据的表发现没索引,或者索引设置的不合理,直接alter修改,大数据的表,就要用pt工具了。5.6版本的MySQL虽然支持了Online DDL,但也添加索引的时候,要考虑是否处于业务的高峰期,尽量选择业务量不繁忙的时候添加):

mysql> alter table test2 add key (createat,status);
Query OK, 0 rows affected (1 min 36.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select count( id ) from `test2` where createat between '2015-05-26 00:00:00' and '2015-05-26 23:59:59'  and status not in(7) and creator_id=8774 and write_id=925;
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                    |+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+|  1 | SIMPLE      | test2 | range | createat      | createat | 14      | NULL |    1 | Using where; Using index |+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+1 row in set (0.00 sec)

mysql> 

可以看到已经用上索引了,Using index说明用上了覆盖索引,覆盖索引(只访问索引的查询,即查询只需要访问索引,而无须访问数据行,最简单的理解,比如翻开一本书,从目录页查找某些内容,但是目录就写的比较详细,我们在目录就找到了自己想看的内容)

而且返回在的行数,差距有多大,相信大家一眼就看到了。

例如:SELECT * FROM t WHERE col1 = ? AND col2 >= ? AND col3 = ?; 这时候,只能用到 idx 索引的最左2列进行检索,而col3条件则无法利用索引进行检索,所以上面只添加组合索引为前两列的值。

 

案例三

多表联接并且有排序时,排序字段必须是驱动表里的,否则排序列无法用到索引;

表os_diskio_history的表结构:

mysql> show create table os_diskio_history G
*************************** 1. row ***************************
       Table: os_diskio_history
Create Table: CREATE TABLE `os_diskio_history` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ip` varchar(50) NOT NULL,
  `tags` varchar(100) DEFAULT NULL,
  `fdisk` varchar(50) NOT NULL DEFAULT '0',
  `disk_io_reads` bigint(18) NOT NULL DEFAULT '0',
  `disk_io_writes` bigint(18) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `YmdHi` bigint(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_ymdhi` (`YmdHi`) USING BTREE,
  KEY `idx_ip_ymdhi` (`ip`,`YmdHi`),
  KEY `idx_io_reads` (`disk_io_reads`),
  KEY `idx_io_writes` (`disk_io_writes`)
) ENGINE=InnoDB AUTO_INCREMENT=3550043 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

表os_diskio的表结构:

mysql> show create table os_diskioG
*************************** 1. row ***************************
       Table: os_diskio
Create Table: CREATE TABLE `os_diskio` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `ip` varchar(50) NOT NULL,
  `tags` varchar(100) DEFAULT NULL,
  `fdisk` varchar(50) NOT NULL DEFAULT '0',
  `disk_io_reads` bigint(18) NOT NULL DEFAULT '0',
  `disk_io_writes` bigint(18) NOT NULL DEFAULT '0',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3550092 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> 

优化前的执行计划如下:

mysql> explain select a.* from  os_diskio_history a inner join os_diskio b on a.id=b.id order by a.id desc limit 10;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+----------------------------------------------+| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra                                        |+----+-------------+-------+--------+---------------+---------+---------+------------+------+----------------------------------------------+|  1 | SIMPLE      | b     | index  | PRIMARY       | PRIMARY | 4       | NULL       |   19 | Using index; Using temporary; Using filesort ||  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | lepus.b.id |    1 |                                              |+----+-------------+-------+--------+---------------+---------+---------+------------+------+----------------------------------------------+2 rows in set (0.00 sec)

mysql> 

sql重构后,把连接方式改成把连接方式改成了「STRAIGHT_JOIN」的执行计划如下:

mysql> explain select a.* from  os_diskio_history a straight_join os_diskio b on a.id=b.id order by a.id desc limit 10;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra       |+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+|  1 | SIMPLE      | a     | index  | PRIMARY       | PRIMARY | 4       | NULL       |   10 |             ||  1 | SIMPLE      | b     | eq_ref | PRIMARY       | PRIMARY | 4       | lepus.a.id |    1 | Using index |+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+2 rows in set (0.00 sec)

mysql> 

可以看到性能是提升的,由于数据量不大,这里只能举个例子说明,嘻嘻,期待大家探讨

 

 

总结:一、定期采集slow query log,用pt-query-digest工具进行分析,可结合Anemometer系统进行slow query管理以便分析slow query并进行后续优化工作;也可以用开源的Lepus监控系统,可以让你清楚明了的发现那些sql执行用的时长,执行的次数等

二、当发现表没有适当的索引时,要添加索引的时候,要考虑数据里的大小及业务的繁忙程度再添加

三、给开发适当的MySQL培训,让开发慢慢改善,毕竟DBA对业务逻辑这块没开发熟悉

 

 

参考资料:http://blog.itpub.net/22664653/viewspace-1176153/

[MySQL优化案例]系列 — 分页优化

http://t.cn/R2IcTMi

 

作者:陆炫志

出处:xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111

您的支持是对博主最大的鼓励,感谢您的认真阅读。本文版权归作者所有,欢迎转载,但请保留该声明。

打赏

未经允许不得转载:同乐学堂 » MySQL线上SQL优化

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

特别的技术,给特别的你!

联系QQ:1071235258QQ群:226134712
error: Sorry,暂时内容不可复制!