个人中心
gpt4 book ai didi

MySQL的id关联和索引使用的实际优化案例

转载 作者：qq735679552 更新时间：2022-09-29 22:32:09 25 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章MySQL的id关联和索引使用的实际优化案例由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

昨晚收到客服MM电话，一用户反馈数据库响应非常慢，手机收到load异常报警，登上主机后发现大量sql执行非常慢，有的执行时间超过了10s 优化点一:

?
1
SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10;

表结构为:

?
1
2
3
4
5
6
CREATE TABLE `game_shares_buy_list` (
`tran_id` int (10) unsigned NOT NULL AUTO_INCREMENT,
`………..'
PRIMARY KEY (`tran_id`),
KEY `ind_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=3144200 DEFAULT CHARSET=utf8;

执行计划:

?
1
2
3
4
5
6
7
root@127.0.0.1 : sitevipdb 09:10:22> explain SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.00′ ORDER BY tran_id DESC LIMIT 10;
+—-+————-+———————-+——-+—————+———+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———————-+——-+—————+———+———+——+——+————-+
| 1 | SIMPLE | game_shares_buy_list | index | NULL | PRIMARY | 4 | NULL | 10 | Using where |
+—-+————-+———————-+——-+—————+———+———+——+——+————-+
1 row in set (0.00 sec)

分析该sql的执行计划，由于tran_id是表的主键，所以查询根据主键降序顺序扫描，这样就可以不用排序， 然后在过滤条件price>2.00的记录，看上去这个执行计划貌似非常好，如果查询扫描到了满足条件的10条记录，就会停止扫描； 但是这里有个问题，如果表中有大量的记录是不符合2.00的，意味查询就需要扫描非常多的记录，才能找到符合条件的10条:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
root@127.0.0.1 : sitevipdb 09:17:23> select price,count(*) as cnt from `game_shares_buy_list` group by price order by cnt desc limit 10;
+——-+——-+
| price | cnt |
+——-+——-+
| 1.75 | 39101 |
| 1.68 | 38477 |
| 1.71 | 34869 |
| 1.66 | 34849 |
| 1.72 | 34718 |
| 1.70 | 33996 |
| 1.76 | 32527 |
| 1.69 | 27189 |
| 1.61 | 25694 |
| 1.25 | 25450 |

可以看到表中有大量的记录不是2.00的，所以这个时候不能在根据主键顺序扫描，在过滤记录； 那么是否需要在price建立一个索引:

?
1
2
3
4
5
6
7
8
9
10
11
root@127.0.0.1 : sitevipdb 09:09:01> select count(*) from `game_shares_buy_list` where price>'2′;
+———-+
| count(*) |
+———-+
| 4087 |
+———-+
root@127.0.0.1 : sitevipdb 09:17:31> select count(*) from `game_shares_buy_list` ;
+———-+
| count(*) |
+———-+
| 1572100 |

从上面price的数据分布可以看出，price的分布相对还是比较集中的，如果在price建立索引，mysql也有可能认为由于需要回表的记录过多， 同时需要额外的排序，而不选择在price上的索引:

?
1
2
root@127.0.0.1 : sitevipdb 09:24:53> alter table game_shares_buy_list add index ind_game_shares_buy_list_price(price);
Query OK, 0 rows affected (5.79 sec)

MySQL的id关联和索引使用的实际优化案例

可以看到优化器虽然注意到了我们新加的索引，但是最终还是选择了primary来扫描； 所以这个时候我们加上去的索引没有产生效果，数据库负载依然很高，如果强制走price上的索引，效果会这样:

?
1
2
3
4
5
6
7
root@127.0.0.1 : sitevipdb 09:35:38> SELECT * FROM `sitevipdb`.`game_shares_buy_list` WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10;
。。。。。
10 rows in set (7.06 sec)
 
root@127.0.0.1 : sitevipdb 09:36:00> SELECT * FROM `sitevipdb`.`game_shares_buy_list` force index(ind_game_shares_buy_list_price) WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10;
。。。。
10 rows in set (1.01 sec)

可以看到如果强制走索引，时间已经明显下降了，但是还是有些慢，能不能在快一点？其实我们需要扫描的记录只有10条，但查询在取得这10条记录的时候需要扫描大量无效的记录 。

MySQL的id关联和索引使用的实际优化案例

怎么降低这个数据：其实只要改写一下sql就可以，我们先从索引中得到满足条件的10个id，在回表进行关联:

?
1
2
3
4
root@127.0.0.1 : sitevipdb 09:44:45> select * from game_shares_buy_list t1,
-> ( SELECT tran_id FROM sitevipdb.game_shares_buy_list WHERE price>='2.0′ ORDER BY tran_id DESC LIMIT 10) t2
-> where t1.tran_id=t2.tran_id;
10 rows in set (0.00 sec)

可以看到执行时间已经不在秒级别了，和客户电话沟通后，很愿意这样改写sql.

—这里看到是order by tran_id是要额外排序的，索引也可以这样来建立消除排序(tran_id,price)这样可以消除排序，同时可以利用order by desc/asc +limit M,N的优化.

优化点二:

?
1
2
3
4
5
6
7
8
CREATE TABLE `game_session` (
`session_id` varchar (255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ,
`session_expires` int (10) unsigned NOT NULL DEFAULT '0′,
`client_ip` varchar (16) DEFAULT NULL ,
`session_data` text,
…………………….
PRIMARY KEY (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询为select `session_data`, `session_expires` from `game_session` where session_id='xxx'出现大量等待情况 同时该表的insert，也有等待的现象； 可以看到这个表结构设计是有些问题的，咨询了客户后，可以改为下面结构:

?
1
2
3
4
5
6
7
8
9
CREATE TABLE `game_session` (
id int auto_increment,
`session_id` varchar (30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ,
`session_expires` int (10) unsigned NOT NULL DEFAULT '0′,
`client_ip` varchar (16) DEFAULT NULL ,
`session_data` varchar (200),
PRIMARY KEY (id),
key ind_session_id(session_id,session_data, session_expires)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

小结:

  1. 新增自增主键id作为表的主键，这样对插入的性能提升是很好的，同时也降低了表主键的大小；
  2. 将session_data由text改为了varchar(200),咨询了客户后，这个字段可以不用大字段存储，同时有text改为了varchar，就可以冗余到索引中；
  3. 由于查询可以使用覆盖索引来完成，所以将查询的3个字段冗余到索引中，查询通过索引完成，不用回表

最后此篇关于MySQL的id关联和索引使用的实际优化案例的文章就讲到这里了,如果你想了解更多关于MySQL的id关联和索引使用的实际优化案例的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

25 4 0
文章推荐： MongoDB索引使用详解
文章推荐： Mongodb 删除添加分片与非分片表维护
文章推荐： MySQL在关联复杂情况下所能做出的一些优化
文章推荐： MySQL索引优化的实际案例分析
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com