SQL 性能优化，牛刀小试。

Kang强
283 阅读4分钟

前言

平时疏忽了SQL性能优化的学习，现在要补补功课了。

explain

待提交。

案例1

*************************** 1. row ***************************
       Table: buy_log
Create Table: CREATE TABLE `buy_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `playerid` bigint(20) NOT NULL DEFAULT '0' COMMENT '玩家游戏id',
  `openid` varchar(255) NOT NULL DEFAULT '' COMMENT '平台玩家账号',
  `device` tinyint(1) DEFAULT '0' COMMENT '设备类型， 0表示web，1表示手机，默认为0',
  `payitem` varchar(100) NOT NULL DEFAULT '' COMMENT '请使用x*p*num的格式.x表示物品ID，p表示单价（以Q点为单位，1Q币=10Q点，num表示建议的购买数量。',
  `token` varchar(255) NOT NULL DEFAULT '' COMMENT '单次交易的token，又平台返回',
  `status` tinyint(3) NOT NULL DEFAULT '0' COMMENT '购买步骤。1：调用qz_buy_goods成功；2：平台回调发货成功，游戏未开发货；3：游戏已成功发货',
  `user_gold` int(11) NOT NULL DEFAULT '0' COMMENT '充值的元宝数目',
  `sys_gold` int(11) NOT NULL DEFAULT '0' COMMENT '充值赠送的系统金币',
  `amt` int(11) NOT NULL DEFAULT '0' COMMENT '支付的总金额（注意，这里以0.1Q点为单位。即如果总金额为18Q点，则这里显示的数字是180。请开发者关注，特别是对账的时候注意单位的转换）。',
  `billno` varchar(255) DEFAULT NULL COMMENT '支付流水号（64个字符长度。该字段和openid合起来是唯一的）。',
  `server` varchar(32) NOT NULL DEFAULT '' COMMENT '发起的服务器ip port 格式:ip:port',
  `packageid` tinyint(3) DEFAULT '0',
  `platform` varchar(10) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL COMMENT '记录创建时间',
  `last_update_time` datetime DEFAULT NULL COMMENT '记录最后更新时间',
  `serverName` varchar(255) DEFAULT '' COMMENT '服务器名字',
  `playerLevel` int(11) DEFAULT '0',
  `vipLevel` int(11) DEFAULT '0',
  `openkey` varchar(255) DEFAULT '',
  `pfkey` varchar(255) DEFAULT '',
  `payamt_coins` varchar(255) DEFAULT NULL,
  `pubacct_payamt_coins` varchar(255) DEFAULT NULL,
  `playerName` varchar(100) NOT NULL DEFAULT '' COMMENT '玩家角色名',
  PRIMARY KEY (`id`),
  KEY `index_token` (`token`),
  KEY `time` (`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=15747155 DEFAULT CHARSET=utf8 COMMENT='QQ平台购物日志记录表'

SQL： select case when max(id) is null then 0 else max(id) end as maxid frombuy_logWHERE status>1

异常：Sending Data 时长 13.7s

如图所示：

  • type 为 ALL，执行了全表扫描，导致效率极低。type的类型包括有：system > const > eq_ref > ref > range > index > ALL

优化方案

  • 为 status 增加索引。

案例2

  • 语句说可能使用了索引，但事实并没有。为什么？因为MySQL在使用索引status时，认为回表数量会超过20%，所以采取全表扫描来进行。
mysql> explain select last_update_time, platform, amt from buy_qpoints where  status > 1;
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | buy_qpoints | ALL  | status_index  | NULL | NULL    | NULL |  721 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+

案例3

  • filesort 是什么？filesort是通过相应的排序算法将取得的数据在内存中进行排序，如果内存不够则会使用磁盘文件作为辅助。所以要避免。
  • temporary 临时表是什么？
  • Using temporary; Using filesort 是什么原因？因为SQL需要 group by date, platform，这个要临时结果集。
  • group by 后的字段一定是要添加索引的，否则还是临时表进行排序

参考

mysql> explain select date(last_update_time) as date, platform, sum(amt)/100 as sum from buy_qpoints where last_update_time > DATE_ADD(now(),INTERVAL -3 MONTH) and status>1 GROUP BY date, platform;
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | buy_qpoints | ALL  | status_index  | NULL | NULL    | NULL |  721 | Using where; Using temporary; Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------------------------------------+
  1. 步骤1：在where条件中选择增加索引。alter table buy_qpoints add index last_update_time_index (last_update_time);，执行类型优化到range
mysql> explain select date(last_update_time) as date, platform, sum(amt)/100 as sum from buy_qpoints where last_update_time > DATE_ADD(now(),INTERVAL -3 MONTH) and status>1 GROUP BY date, platform;
+----+-------------+-------------+-------+-------------------------------------+------------------------+---------+------+------+----------------------------------------------+
| id | select_type | table       | type  | possible_keys                       | key                    | key_len | ref  | rows | Extra                                        |
+----+-------------+-------------+-------+-------------------------------------+------------------------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | buy_qpoints | range | status_index,last_update_time_index | last_update_time_index | 9       | NULL |    1 | Using where; Using temporary; Using filesort |
+----+-------------+-------------+-------+-------------------------------------+------------------------+---------+------+------+----------------------------------------------+
1 row in set (0.02 sec)

案例4

mysql> explain select id from PlayerExtremityReport where attackerId=1000195000002907 and kind=9;
+----+-------------+-----------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table                 | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | PlayerExtremityReport | ALL  | NULL          | NULL | NULL    | NULL |  808 | Using where |
+----+-------------+-----------------------+------+---------------+------+---------+------+------+-------------+

没有任何所以索引，type 为 ALL，查询效率最差。

mysql> explain select id from PlayerExtremityReport where attackerId=1000195000002907 and kind=9;
+----+-------------+-----------------------+------+-----------------------+-----------------------+---------+-------------+------+--------------------------+
| id | select_type | table                 | type | possible_keys         | key                   | key_len | ref         | rows | Extra                    |
+----+-------------+-----------------------+------+-----------------------+-----------------------+---------+-------------+------+--------------------------+
|  1 | SIMPLE      | PlayerExtremityReport | ref  | attackerId_kind_index | attackerId_kind_index | 10      | const,const |    1 | Using where; Using index |
+----+-------------+-----------------------+------+-----------------------+-----------------------+---------+-------------+------+--------------------------+
1 row in set (0.00 sec)

增加一个联合索引，由于id为主键，这个查询甚至不用回表。

  • type：ref 表示 非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。

案例5

mysql> explain select id,armyId,attackerId,attackerName,defenderId,defenderName,result,attackerLevel,defenderLevel,attackerSoilder,defenderSoilder,fround,winp,createAt,reward,score,type,kind,checkpoint,layer from DieGuReport where result=1 and armyId=1205 and checkpoint=11 and layer=1 order by createAt desc limit 10;
+----+-------------+-------------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table       | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+-------------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | DieGuReport | index | NULL          | timeIndex | 9       | NULL |   10 | Using where |
+----+-------------+-------------+-------+---------------+-----------+---------+------+------+-------------+

问题：这里使用了order by，为 createAt 增加索引，可以稍微快速地获得排序数值。 优化方案：建立一个联合索引，利用覆盖索引，可以快速返回10个id值，回表计算即可。

mysql> alter table DieGuReport  add index muilt_index (result, armyId, checkpoint, layer, createAt);
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> explain select id,armyId,attackerId,attackerName,defenderId,defenderName,result,attackerLevel,defenderLevel,attackerSoilder,defenderSoilder,fround,winp,createAt,reward,score,type,kind,checkpoint,layer from DieGuReport where result=1 and armyId=1205 and checkpoint=11 and layer=1 order by createAt desc limit 10;
+----+-------------+-------------+------+---------------+-------------+---------+-------------------------+------+-------------+
| id | select_type | table       | type | possible_keys | key         | key_len | ref                     | rows | Extra       |
+----+-------------+-------------+------+---------------+-------------+---------+-------------------------+------+-------------+
|  1 | SIMPLE      | DieGuReport | ref  | muilt_index   | muilt_index | 13      | const,const,const,const |    1 | Using where |
+----+-------------+-------------+------+---------------+-------------+---------+-------------------------+------+-------------+

Q&A

  1. 为什么innodb-buffer-pool-size会比ram大？主要是操作系统虚拟内存的作用。

stackoverflow.com/questions/1…

avatar
文章
阅读
粉丝