mysql优化案例（14秒优化到不到1秒）

最新推荐文章于 2024-04-13 09:31:19 发布
最新推荐文章于 2024-04-13 09:31:19 发布
阅读量2.1k 收藏
点赞数 1
分类专栏： mysql 优化和具体demo 文章标签： 数据库 mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43882788/article/details/128291158
版权

优化案例

前面用过的 tbiguser 表有 10000000 条记录
创建 tuser1 表和 tuser2 表，并初始化若干的数据。
create table tuser1(
id int primary key auto_increment,
name varchar(255),
address varchar(255)
);
create table tuser2(
id int primary key auto_increment,
name varchar(255),
address varchar(255)
);
看到 tuser1 和 tuser2 表有重复的数据。
需求： tbiguser表按照地区分组统计求和，要求是在tuser1表和tuser2表中出现过的地区
按照需求写出 SQL ：
mysql> select * from tuser1 ;
+----+----------+-----------+
| id | name | address |
+----+----------+-----------+
| 1 | zhangfei | tianjin |
| 2 | zhaoyun | tianjin |
| 3 | diaochan | guangzhou |
| 4 | diaochan | xianggang |
| 5 | diaochan | hebei |
| 6 | diaochan | dongbei |
| 7 | diaochan | dongbei |
| 8 | diaochan | dongbei |
| 9 | diaochan | dongbei |
| 10 | diaochan | dongbei |
| 11 | 1 | 1 |
| 12 | 1 | 1 |
| 13 | 1 | 1 |
| 14 | 1 | 1 |
| 15 | 1 | 1 |
| 16 | 1 | 1 |
| 17 | 1 | 1 |
| 18 | 1 | 1 |
| 19 | 1 | 1 |
| 20 | 1 | 1 |
+----+----------+-----------+
20 rows in set (0.00 sec)
mysql> select * from tuser2;
+----+----------+-----------+
| id | name | address |
+----+----------+-----------+
| 1 | zhangfei | shanghai |
| 2 | zhaoyun | shanghai |
| 3 | diaochan | guangzhou |
| 4 | diaochan | xianggang |
| 5 | diaochan | hebei |
| 6 | diaochan | dongbei |
| 7 | diaochan | dongbei |
| 8 | diaochan | dongbei |
| 9 | diaochan | dongbei |
| 10 | diaochan | dongbei |
| 11 | 1 | 1 |
| 12 | 1 | 1 |
| 13 | 1 | 1 |
| 14 | 1 | 1 |
| 15 | 1 | 1 |
| 16 | 1 | 1 |
| 17 | 1 | 1 |
| 18 | 1 | 1 |
| 19 | 1 | 1 |
| 20 | 1 | 1 |
+----+----------+-----------+
20 rows in set (0.00 sec) 通过 explain 可以看到：
type: 为 ALL 说明没有索引，全表扫描
Using temporary ：说明使用了临时表
Using filesort ：说明使用了文件排序
Using where ：没有索引下推，在 Server 层进行了全表扫描和过滤
Using join buffer(Block Nested Loop) ：关联没有索引，有关联优化
第一次优化：
给 address 加索引
mysql> select count (id) num , address from tbiguser where address in ( select
distinct address from tuser1) group by address union select count (id) num ,
address from tbiguser where address in ( select distinct address from tuser2)
group by address ;
+-----+----------+
| num | address |
+-----+----------+
| 105 | tianjin |
| 100 | shanghai |
+-----+----------+
2 rows in set ( 14.43 sec)
mysql> explain select count (id) num , address from tbiguser where address in
( select distinct address from tuser1) group by address union select
count (id) num , address from tbiguser where address in ( select distinct
address from tuser2) group by address ;
+----+--------------+-------------+------+---------------+------+---------+-----
-+---------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+--------------+-------------+------+---------------+------+---------+-----
-+---------+----------------------------------------------------+
| 1 | PRIMARY | <subquery2> | ALL | NULL | NULL | NULL | NULL
| NULL | Using temporary; Using filesort |
| 1 | PRIMARY | tbiguser | ALL | NULL | NULL | NULL | NULL
| 9754360 | Using where ; Using join buffer (Block Nested Loop) |
| 2 | MATERIALIZED | tuser1 | ALL | NULL | NULL | NULL | NULL
| 20 | NULL |
| 3 | UNION | <subquery4> | ALL | NULL | NULL | NULL | NULL
| NULL | Using temporary; Using filesort |
| 3 | UNION | tbiguser | ALL | NULL | NULL | NULL | NULL
| 9754360 | Using where ; Using join buffer (Block Nested Loop) |
| 4 | MATERIALIZED | tuser2 | ALL | NULL | NULL | NULL | NULL
| 20 | NULL |
| NULL | UNION RESULT | <union1, 3 > | ALL | NULL | NULL | NULL |
NULL | NULL | Using temporary |
+----+--------------+-------------+------+---------------+------+---------+-----
-+---------+----------------------------------------------------+
7 rows in set ( 0.00 sec)
-- 给 address 加索引
alter table tbiguser add index idx_addr(address); type ： index ，说明用到了索引 ： 覆盖索引
Using temporary ：有临时表
Using where ：没有索引下推，在 Server 层进行了全表扫描和过滤
第二次优化：
alter table tuser1 add index idx_addr(address);
alter table tuser2 add index idx_addr(address);
-- 再次运行 SQL
select count (id) num , address from tbiguser where address in ( select
distinct address from tuser1) group by address union select count (id) num ,
address from tbiguser where address in ( select distinct address from tuser2)
group by address ;
+-----+----------+
| num | address |
+-----+----------+
| 105 | tianjin |
| 100 | shanghai |
+-----+----------+
2 rows in set ( 13.61 sec)
-- 查看执行计划
mysql> explain select count (id) num , address from tbiguser where address in
( select distinct address from tuser1) group by address union select
count (id) num , address from tbiguser where address in ( select distinct
address from tuser2) group by address ;
+----+--------------+-------------+--------+---------------+------------+-------
--+-----------------------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+--------------+-------------+--------+---------------+------------+-------
--+-----------------------+---------+--------------------------+
| 1 | PRIMARY | tbiguser | index | idx_addr | idx_addr | 768
| NULL | 9754360 | Using where ; Using index |
| 1 | PRIMARY | <subquery2> | eq_ref | <auto_key> | <auto_key> | 768
| demo .tbiguser.address | 1 | NULL |
| 2 | MATERIALIZED | tuser1 | index | idx_addr | idx_addr | 768
| NULL | 20 | Using index |
| 3 | UNION | tbiguser | index | idx_addr | idx_addr | 768
| NULL | 9754360 | Using where ; Using index |
| 3 | UNION | <subquery4> | eq_ref | <auto_key> | <auto_key> | 768
| demo .tbiguser.address | 1 | NULL |
| 4 | MATERIALIZED | tuser2 | index | idx_addr | idx_addr | 768
| NULL | 20 | Using index |
| NULL | UNION RESULT | <union1, 3 > | ALL | NULL | NULL | NULL
| NULL | NULL | Using temporary |
+----+--------------+-------------+--------+---------------+------------+-------
--+-----------------------+---------+--------------------------+
-- 修改 sql
select count (id) num , address from tbiguser where address in ( select distinct
address from tuser1) or address in ( select distinct address from tuser2) group
by address order by address;
+-----+----------+
| num | address | type ： index
没有了临时表
第三次优化：
从前面的执行计划可以看出，索引只是使用了覆盖索引， rows=9754360 ， 说明还是几乎扫描了全表的
行
利用 address 索引，先过滤数据
+-----+----------+
| 100 | shanghai |
| 105 | tianjin |
+-----+----------+
2 rows in set ( 3.54 sec)
-- 运行执行计划
explain select count (id) num , address from tbiguser where address in ( select
distinct address from tuser1) or address in ( select distinct address from
tuser2) group by address order by address;
+----+-------------+----------+-------+---------------+----------+---------+----
--+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+----------+-------+---------------+----------+---------+----
--+---------+--------------------------+
| 1 | PRIMARY | tbiguser | index | idx_addr | idx_addr | 768 |
NULL | 9754360 | Using where ; Using index |
| 3 | SUBQUERY | tuser2 | index | idx_addr | idx_addr | 768 |
NULL | 20 | Using index |
| 2 | SUBQUERY | tuser1 | index | idx_addr | idx_addr | 768 |
NULL | 20 | Using index |
+----+-------------+----------+-------+---------------+----------+---------+----
--+---------+--------------------------+
3 rows in set ( 0.00 sec)
mysql> select distinct b.* from tuser1 a,tbiguser b where a .address =b .address ;
+-----+----------+------------+------+------+--------+---------+
| id | nickname | loginname | age | sex | status | address |
+-----+----------+------------+------+------+--------+---------+
| 101 | zy101 | zhaoyun101 | 23 | 1 | 1 | tianjin |
| 102 | zy102 | zhaoyun102 | 23 | 1 | 1 | tianjin |
| 103 | zy103 | zhaoyun103 | 23 | 1 | 1 | tianjin |
| 104 | zy104 | zhaoyun104 | 23 | 1 | 1 | tianjin |
| 105 | zy105 | zhaoyun105 | 23 | 1 | 1 | tianjin |
| 106 | zy106 | zhaoyun106 | 23 | 1 | 1 | tianjin |
| 107 | zy107 | zhaoyun107 | 23 | 1 | 1 | tianjin |
| 108 | zy108 | zhaoyun108 | 23 | 1 | 1 | tianjin |
| 109 | zy109 | zhaoyun109 | 23 | 1 | 1 | tianjin |
| 110 | zy110 | zhaoyun110 | 23 | 1 | 1 | tianjin |
| 111 | zy111 | zhaoyun111 | 23 | 1 | 1 | tianjin |
| 112 | zy112 | zhaoyun112 | 23 | 1 | 1 | tianjin |
| 113 | zy113 | zhaoyun113 | 23 | 1 | 1 | tianjin |
| 114 | zy114 | zhaoyun114 | 23 | 1 | 1 | tianjin |
| 115 | zy115 | zhaoyun115 | 23 | 1 | 1 | tianjin |
| 116 | zy116 | zhaoyun116 | 23 | 1 | 1 | tianjin |
| 117 | zy117 | zhaoyun117 | 23 | 1 | 1 | tianjin |
| 118 | zy118 | zhaoyun118 | 23 | 1 | 1 | tianjin | | 119 | zy119 | zhaoyun119 | 23 | 1 | 1 | tianjin |
| 120 | zy120 | zhaoyun120 | 23 | 1 | 1 | tianjin |
| 121 | zy121 | zhaoyun121 | 23 | 1 | 1 | tianjin |
| 122 | zy122 | zhaoyun122 | 23 | 1 | 1 | tianjin |
| 123 | zy123 | zhaoyun123 | 23 | 1 | 1 | tianjin |
| 124 | zy124 | zhaoyun124 | 23 | 1 | 1 | tianjin |
| 125 | zy125 | zhaoyun125 | 23 | 1 | 1 | tianjin |
| 126 | zy126 | zhaoyun126 | 23 | 1 | 1 | tianjin |
| 127 | zy127 | zhaoyun127 | 23 | 1 | 1 | tianjin |
| 128 | zy128 | zhaoyun128 | 23 | 1 | 1 | tianjin |
| 129 | zy129 | zhaoyun129 | 23 | 1 | 1 | tianjin |
| 130 | zy130 | zhaoyun130 | 23 | 1 | 1 | tianjin |
| 131 | zy131 | zhaoyun131 | 23 | 1 | 1 | tianjin |
| 132 | zy132 | zhaoyun132 | 23 | 1 | 1 | tianjin |
| 133 | zy133 | zhaoyun133 | 23 | 1 | 1 | tianjin |
| 134 | zy134 | zhaoyun134 | 23 | 1 | 1 | tianjin |
| 135 | zy135 | zhaoyun135 | 23 | 1 | 1 | tianjin |
| 136 | zy136 | zhaoyun136 | 23 | 1 | 1 | tianjin |
| 137 | zy137 | zhaoyun137 | 23 | 1 | 1 | tianjin |
| 138 | zy138 | zhaoyun138 | 23 | 1 | 1 | tianjin |
| 139 | zy139 | zhaoyun139 | 23 | 1 | 1 | tianjin |
| 140 | zy140 | zhaoyun140 | 23 | 1 | 1 | tianjin |
| 141 | zy141 | zhaoyun141 | 23 | 1 | 1 | tianjin |
| 142 | zy142 | zhaoyun142 | 23 | 1 | 1 | tianjin |
| 143 | zy143 | zhaoyun143 | 23 | 1 | 1 | tianjin |
| 144 | zy144 | zhaoyun144 | 23 | 1 | 1 | tianjin |
| 145 | zy145 | zhaoyun145 | 23 | 1 | 1 | tianjin |
| 146 | zy146 | zhaoyun146 | 23 | 1 | 1 | tianjin |
| 147 | zy147 | zhaoyun147 | 23 | 1 | 1 | tianjin |
| 148 | zy148 | zhaoyun148 | 23 | 1 | 1 | tianjin |
| 149 | zy149 | zhaoyun149 | 23 | 1 | 1 | tianjin |
| 150 | zy150 | zhaoyun150 | 23 | 1 | 1 | tianjin |
| 151 | zy151 | zhaoyun151 | 23 | 1 | 1 | tianjin |
| 152 | zy152 | zhaoyun152 | 23 | 1 | 1 | tianjin |
| 153 | zy153 | zhaoyun153 | 23 | 1 | 1 | tianjin |
| 154 | zy154 | zhaoyun154 | 23 | 1 | 1 | tianjin |
| 155 | zy155 | zhaoyun155 | 23 | 1 | 1 | tianjin |
| 156 | zy156 | zhaoyun156 | 23 | 1 | 1 | tianjin |
| 157 | zy157 | zhaoyun157 | 23 | 1 | 1 | tianjin |
| 158 | zy158 | zhaoyun158 | 23 | 1 | 1 | tianjin |
| 159 | zy159 | zhaoyun159 | 23 | 1 | 1 | tianjin |
| 160 | zy160 | zhaoyun160 | 23 | 1 | 1 | tianjin |
| 161 | zy161 | zhaoyun161 | 23 | 1 | 1 | tianjin |
| 162 | zy162 | zhaoyun162 | 23 | 1 | 1 | tianjin |
| 163 | zy163 | zhaoyun163 | 23 | 1 | 1 | tianjin |
| 164 | zy164 | zhaoyun164 | 23 | 1 | 1 | tianjin |
| 165 | zy165 | zhaoyun165 | 23 | 1 | 1 | tianjin |
| 166 | zy166 | zhaoyun166 | 23 | 1 | 1 | tianjin |
| 167 | zy167 | zhaoyun167 | 23 | 1 | 1 | tianjin |
| 168 | zy168 | zhaoyun168 | 23 | 1 | 1 | tianjin |
| 169 | zy169 | zhaoyun169 | 23 | 1 | 1 | tianjin |
| 170 | zy170 | zhaoyun170 | 23 | 1 | 1 | tianjin |
| 171 | zy171 | zhaoyun171 | 23 | 1 | 1 | tianjin |
| 172 | zy172 | zhaoyun172 | 23 | 1 | 1 | tianjin |
| 173 | zy173 | zhaoyun173 | 23 | 1 | 1 | tianjin |
| 174 | zy174 | zhaoyun174 | 23 | 1 | 1 | tianjin |
| 175 | zy175 | zhaoyun175 | 23 | 1 | 1 | tianjin |
| 176 | zy176 | zhaoyun176 | 23 | 1 | 1 | tianjin | type ： ref
rows ： 2438590
说明使用了 address 索引做关联
同理：
| 177 | zy177 | zhaoyun177 | 23 | 1 | 1 | tianjin |
| 178 | zy178 | zhaoyun178 | 23 | 1 | 1 | tianjin |
| 179 | zy179 | zhaoyun179 | 23 | 1 | 1 | tianjin |
| 180 | zy180 | zhaoyun180 | 23 | 1 | 1 | tianjin |
| 181 | zy181 | zhaoyun181 | 23 | 1 | 1 | tianjin |
| 182 | zy182 | zhaoyun182 | 23 | 1 | 1 | tianjin |
| 183 | zy183 | zhaoyun183 | 23 | 1 | 1 | tianjin |
| 184 | zy184 | zhaoyun184 | 23 | 1 | 1 | tianjin |
| 185 | zy185 | zhaoyun185 | 23 | 1 | 1 | tianjin |
| 186 | zy186 | zhaoyun186 | 23 | 1 | 1 | tianjin |
| 187 | zy187 | zhaoyun187 | 23 | 1 | 1 | tianjin |
| 188 | zy188 | zhaoyun188 | 23 | 1 | 1 | tianjin |
| 189 | zy189 | zhaoyun189 | 23 | 1 | 1 | tianjin |
| 190 | zy190 | zhaoyun190 | 23 | 1 | 1 | tianjin |
| 191 | zy191 | zhaoyun191 | 23 | 1 | 1 | tianjin |
| 192 | zy192 | zhaoyun192 | 23 | 1 | 1 | tianjin |
| 193 | zy193 | zhaoyun193 | 23 | 1 | 1 | tianjin |
| 194 | zy194 | zhaoyun194 | 23 | 1 | 1 | tianjin |
| 195 | zy195 | zhaoyun195 | 23 | 1 | 1 | tianjin |
| 196 | zy196 | zhaoyun196 | 23 | 1 | 1 | tianjin |
| 197 | zy197 | zhaoyun197 | 23 | 1 | 1 | tianjin |
| 198 | zy198 | zhaoyun198 | 23 | 1 | 1 | tianjin |
| 199 | zy199 | zhaoyun199 | 23 | 1 | 1 | tianjin |
| 200 | zy200 | zhaoyun200 | 23 | 1 | 1 | tianjin |
| 201 | zy201 | zhaoyun201 | 23 | 1 | 1 | tianjin |
| 202 | zy202 | zhaoyun202 | 23 | 1 | 1 | tianjin |
| 203 | zy203 | zhaoyun203 | 23 | 1 | 1 | tianjin |
| 204 | zy204 | zhaoyun204 | 23 | 1 | 1 | tianjin |
| 205 | zy205 | zhaoyun205 | 23 | 1 | 1 | tianjin |
+-----+----------+------------+------+------+--------+---------+
105 rows in set ( 0.00 sec)
-- 查看执行计划
mysql> explain select distinct b.* from tuser1 a,tbiguser b where
a .address =b .address ;
+----+-------------+-------+-------+---------------+----------+---------+-------
---------+---------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+-------
---------+---------+-------------------------------------------+
| 1 | SIMPLE | a | index | idx_addr | idx_addr | 768 | NULL
| 20 | Using where ; Using index; Using temporary |
| 1 | SIMPLE | b | ref | idx_addr | idx_addr | 768 |
demo .a.address | 2438590 | NULL |
+----+-------------+-------+-------+---------------+----------+---------+-------
---------+---------+-------------------------------------------+
2 rows in set ( 0.00 sec)
mysql> select distinct b.* from tuser2 a,tbiguser b where a .address =b .address ; +-----+----------+------------+------+------+--------+----------+
| id | nickname | loginname | age | sex | status | address |
+-----+----------+------------+------+------+--------+----------+
| 1 | zy1 | zhaoyun1 | 23 | 1 | 1 | shanghai |
| 2 | zy2 | zhaoyun2 | 23 | 1 | 1 | shanghai |
| 3 | zy3 | zhaoyun3 | 23 | 1 | 1 | shanghai |
| 4 | zy4 | zhaoyun4 | 23 | 1 | 1 | shanghai |
| 5 | zy5 | zhaoyun5 | 23 | 1 | 1 | shanghai |
| 6 | zy6 | zhaoyun6 | 23 | 1 | 1 | shanghai |
| 7 | zy7 | zhaoyun7 | 23 | 1 | 1 | shanghai |
| 8 | zy8 | zhaoyun8 | 23 | 1 | 1 | shanghai |
| 9 | zy9 | zhaoyun9 | 23 | 1 | 1 | shanghai |
| 10 | zy10 | zhaoyun10 | 23 | 1 | 1 | shanghai |
| 11 | zy11 | zhaoyun11 | 23 | 1 | 1 | shanghai |
| 12 | zy12 | zhaoyun12 | 23 | 1 | 1 | shanghai |
| 13 | zy13 | zhaoyun13 | 23 | 1 | 1 | shanghai |
| 14 | zy14 | zhaoyun14 | 23 | 1 | 1 | shanghai |
| 15 | zy15 | zhaoyun15 | 23 | 1 | 1 | shanghai |
| 16 | zy16 | zhaoyun16 | 23 | 1 | 1 | shanghai |
| 17 | zy17 | zhaoyun17 | 23 | 1 | 1 | shanghai |
| 18 | zy18 | zhaoyun18 | 23 | 1 | 1 | shanghai |
| 19 | zy19 | zhaoyun19 | 23 | 1 | 1 | shanghai |
| 20 | zy20 | zhaoyun20 | 23 | 1 | 1 | shanghai |
| 21 | zy21 | zhaoyun21 | 23 | 1 | 1 | shanghai |
| 22 | zy22 | zhaoyun22 | 23 | 1 | 1 | shanghai |
| 23 | zy23 | zhaoyun23 | 23 | 1 | 1 | shanghai |
| 24 | zy24 | zhaoyun24 | 23 | 1 | 1 | shanghai |
| 25 | zy25 | zhaoyun25 | 23 | 1 | 1 | shanghai |
| 26 | zy26 | zhaoyun26 | 23 | 1 | 1 | shanghai |
| 27 | zy27 | zhaoyun27 | 23 | 1 | 1 | shanghai |
| 28 | zy28 | zhaoyun28 | 23 | 1 | 1 | shanghai |
| 29 | zy29 | zhaoyun29 | 23 | 1 | 1 | shanghai |
| 30 | zy30 | zhaoyun30 | 23 | 1 | 1 | shanghai |
| 31 | zy31 | zhaoyun31 | 23 | 1 | 1 | shanghai |
| 32 | zy32 | zhaoyun32 | 23 | 1 | 1 | shanghai |
| 33 | zy33 | zhaoyun33 | 23 | 1 | 1 | shanghai |
| 34 | zy34 | zhaoyun34 | 23 | 1 | 1 | shanghai |
| 35 | zy35 | zhaoyun35 | 23 | 1 | 1 | shanghai |
| 36 | zy36 | zhaoyun36 | 23 | 1 | 1 | shanghai |
| 37 | zy37 | zhaoyun37 | 23 | 1 | 1 | shanghai |
| 38 | zy38 | zhaoyun38 | 23 | 1 | 1 | shanghai |
| 39 | zy39 | zhaoyun39 | 23 | 1 | 1 | shanghai |
| 40 | zy40 | zhaoyun40 | 23 | 1 | 1 | shanghai |
| 41 | zy41 | zhaoyun41 | 23 | 1 | 1 | shanghai |
| 42 | zy42 | zhaoyun42 | 23 | 1 | 1 | shanghai |
| 43 | zy43 | zhaoyun43 | 23 | 1 | 1 | shanghai |
| 44 | zy44 | zhaoyun44 | 23 | 1 | 1 | shanghai |
| 45 | zy45 | zhaoyun45 | 23 | 1 | 1 | shanghai |
| 46 | zy46 | zhaoyun46 | 23 | 1 | 1 | shanghai |
| 47 | zy47 | zhaoyun47 | 23 | 1 | 1 | shanghai |
| 48 | zy48 | zhaoyun48 | 23 | 1 | 1 | shanghai |
| 49 | zy49 | zhaoyun49 | 23 | 1 | 1 | shanghai |
| 50 | zy50 | zhaoyun50 | 23 | 1 | 1 | shanghai |
| 51 | zy51 | zhaoyun51 | 23 | 1 | 1 | shanghai |
| 52 | zy52 | zhaoyun52 | 23 | 1 | 1 | shanghai |
| 53 | zy53 | zhaoyun53 | 23 | 1 | 1 | shanghai |
| 54 | zy54 | zhaoyun54 | 23 | 1 | 1 | shanghai |
| 55 | zy55 | zhaoyun55 | 23 | 1 | 1 | shanghai |
| 56 | zy56 | zhaoyun56 | 23 | 1 | 1 | shanghai |
| 57 | zy57 | zhaoyun57 | 23 | 1 | 1 | shanghai |
| 58 | zy58 | zhaoyun58 | 23 | 1 | 1 | shanghai |
| 59 | zy59 | zhaoyun59 | 23 | 1 | 1 | shanghai |
| 60 | zy60 | zhaoyun60 | 23 | 1 | 1 | shanghai |
| 61 | zy61 | zhaoyun61 | 23 | 1 | 1 | shanghai |
| 62 | zy62 | zhaoyun62 | 23 | 1 | 1 | shanghai |
| 63 | zy63 | zhaoyun63 | 23 | 1 | 1 | shanghai |
| 64 | zy64 | zhaoyun64 | 23 | 1 | 1 | shanghai |
| 65 | zy65 | zhaoyun65 | 23 | 1 | 1 | shanghai |
| 66 | zy66 | zhaoyun66 | 23 | 1 | 1 | shanghai |
| 67 | zy67 | zhaoyun67 | 23 | 1 | 1 | shanghai |
| 68 | zy68 | zhaoyun68 | 23 | 1 | 1 | shanghai |
| 69 | zy69 | zhaoyun69 | 23 | 1 | 1 | shanghai |
| 70 | zy70 | zhaoyun70 | 23 | 1 | 1 | shanghai |
| 71 | zy71 | zhaoyun71 | 23 | 1 | 1 | shanghai |
| 72 | zy72 | zhaoyun72 | 23 | 1 | 1 | shanghai |
| 73 | zy73 | zhaoyun73 | 23 | 1 | 1 | shanghai |
| 74 | zy74 | zhaoyun74 | 23 | 1 | 1 | shanghai |
| 75 | zy75 | zhaoyun75 | 23 | 1 | 1 | shanghai |
| 76 | zy76 | zhaoyun76 | 23 | 1 | 1 | shanghai |
| 77 | zy77 | zhaoyun77 | 23 | 1 | 1 | shanghai |
| 78 | zy78 | zhaoyun78 | 23 | 1 | 1 | shanghai |
| 79 | zy79 | zhaoyun79 | 23 | 1 | 1 | shanghai |
| 80 | zy80 | zhaoyun80 | 23 | 1 | 1 | shanghai |
| 81 | zy81 | zhaoyun81 | 23 | 1 | 1 | shanghai |
| 82 | zy82 | zhaoyun82 | 23 | 1 | 1 | shanghai |
| 83 | zy83 | zhaoyun83 | 23 | 1 | 1 | shanghai |
| 84 | zy84 | zhaoyun84 | 23 | 1 | 1 | shanghai |
| 85 | zy85 | zhaoyun85 | 23 | 1 | 1 | shanghai |
| 86 | zy86 | zhaoyun86 | 23 | 1 | 1 | shanghai |
| 87 | zy87 | zhaoyun87 | 23 | 1 | 1 | shanghai |
| 88 | zy88 | zhaoyun88 | 23 | 1 | 1 | shanghai |
| 89 | zy89 | zhaoyun89 | 23 | 1 | 1 | shanghai |
| 90 | zy90 | zhaoyun90 | 23 | 1 | 1 | shanghai |
| 91 | zy91 | zhaoyun91 | 23 | 1 | 1 | shanghai |
| 92 | zy92 | zhaoyun92 | 23 | 1 | 1 | shanghai |
| 93 | zy93 | zhaoyun93 | 23 | 1 | 1 | shanghai |
| 94 | zy94 | zhaoyun94 | 23 | 1 | 1 | shanghai |
| 95 | zy95 | zhaoyun95 | 23 | 1 | 1 | shanghai |
| 96 | zy96 | zhaoyun96 | 23 | 1 | 1 | shanghai |
| 97 | zy97 | zhaoyun97 | 23 | 1 | 1 | shanghai |
| 98 | zy98 | zhaoyun98 | 23 | 1 | 1 | shanghai |
| 99 | zy99 | zhaoyun99 | 23 | 1 | 1 | shanghai |
| 100 | zy100 | zhaoyun100 | 23 | 1 | 1 | shanghai |
+-----+----------+------------+------+------+--------+----------+
100 rows in set ( 0.00 sec)
-- 查看执行计划
mysql> explain select distinct b.* from tuser2 a,tbiguser b where
a .address =b .address ;
+----+-------------+-------+-------+---------------+----------+---------+-------
---------+---------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+-------
---------+---------+-------------------------------------------+ type ： ref
rows ： 2438590
说明使用了 address 索引做关联
合并结果集后再分组求和
| 1 | SIMPLE | a | index | idx_addr | idx_addr | 768 | NULL
| 20 | Using where ; Using index; Using temporary |
| 1 | SIMPLE | b | ref | idx_addr | idx_addr | 768 |
demo .a.address | 2438590 | NULL |
+----+-------------+-------+-------+---------------+----------+---------+-------
---------+---------+-------------------------------------------+
2 rows in set ( 0.00 sec)
select count (x .id ),x .address
from
( select distinct b.* from tuser1 a,tbiguser b where a .address =b .address union
all select distinct b.* from tuser2 a,tbiguser b where a .address =b .address ) x
group by x .address ;
+-------------+----------+
| count (x .id ) | address |
+-------------+----------+
| 100 | shanghai |
| 105 | tianjin |
+-------------+----------+
2 rows in set ( 0.00 sec)
-- 查看执行计划
mysql> explain select count (x .id ),x .address
-> from
-> ( select distinct b.* from tuser1 a,tbiguser b where a .address =b .address
union all select distinct b.* from tuser2 a,tbiguser b where
a .address =b .address ) x group by x .address ;
+----+--------------+------------+-------+---------------+----------+---------+-
---------------+----------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len |
ref | rows | Extra |
+----+--------------+------------+-------+---------------+----------+---------+-
---------------+----------+-------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL |
NULL | 97543600 | Using temporary; Using filesort |
| 2 | DERIVED | a | index | idx_addr | idx_addr | 768 |
NULL | 20 | Using where ; Using index; Using temporary |
| 2 | DERIVED | b | ref | idx_addr | idx_addr | 768 |
demo .a.address | 2438590 | Distinct |
| 3 | UNION | a | index | idx_addr | idx_addr | 768 |
NULL | 20 | Using where ; Using index; Using temporary |
| 3 | UNION | b | ref | idx_addr | idx_addr | 768 |
demo .a.address | 2438590 | Distinct |
| NULL | UNION RESULT | <union2, 3 > | ALL | NULL | NULL | NULL
| NULL | NULL | Using temporary |
+----+--------------+------------+-------+---------------+----------+---------+-
---------------+----------+-------------------------------------------+
6 rows in set ( 0.00 sec) DERIVED ：派生表
最终优化
将派生表写成视图
优化结果：从最初的将近 14 秒优化到不到 1 秒 。
优化总结：
开启慢查询日志，定位运行慢的 SQL 语句
利用 explain 执行计划，查看 SQL 执行情况
关注索引使用情况： type
关注 Rows ：行扫描
关注 Extra ：没有信息最好
加索引后，查看索引使用情况， index 只是覆盖索引，并不算很好的使用索引
如果有关联尽量将索引用到 eq_ref 或 ref 级别
复杂 SQL 可以做成视图，视图在 MySQL 内部有优化，而且开发也比较友好
对于复杂的 SQL 要逐一分析，找到比较费时的 SQL 语句片段进行优化
永远的HelloWorld
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL优化实例（详细）
rootlh的博客
10-06 1497
一、打开MySQL，输入show status命令查看数据库状态  主要查看当前连接数、当前运行的线程数、总共进行的查询数。 二、使用数据统计工具awk（Linux系统） 1awk '{printf("%s\n",$0)}' a.txt   //按行打印文件a.txt2    正则表达式+处理方式+文件名3    4mysqladmin -ur
MYSQL优化案例
ganjialin12的专栏
04-11 300
1、加索引 2、数字类型规范 3、加冗余字段 4、用in、union all 替换or 5、减少模糊查询字段 6、3张表以上SQL拆分 7、硬件扩展 8、其他1.MySQL优化案例-加索引 这个不用列举具体例子了，给大家看下TMS的两张张表，这些都是优化的时候加上的索引， 索引可以解决95%的sql性能问题！2. MySQL优化案例-数字类型规范 数字类型规范基本的有： 1
一次非常有意思的SQL优化经历:从30248.271s到0.001s
iteye_6274的博客
05-19 1252
场景 我用的数据库是mysql5.6，下面简单的介绍下场景 课程表 create table Course( c_id int PRIMARY KEY, name varchar(10) ) 数据100条 学生表: create table Student( id int PRIMARY KEY, name varchar(10) ) 数据70000条 ...
MySQL查询优化技巧和10个案例展示_mysql优化案例经验分享
最新发布
2401_83946570的博客
04-13 862
最近很多小伙伴找我要Linux学习资料，于是我翻箱倒柜，整理了一些优质资源，涵盖视频、电子书、PPT等共享给大家！
MySQL优化案例
AlbenXie的博客
04-12 437
写在前面：discuz!作为首屈一指的社区系统，为广大站长提供了一站式网站解决方案，而且是开源的（虽然部分代码是加密的），它为这个垂直领域的行业发展作出了巨大贡献。尽管如此，discuz!系统源码中，还是或多或少有些坑。其中最著名的就是默认采用MyISAM引擎，以及基于MyISAM引擎的抢楼功能，session表采用memory引擎等，可以参考后面几篇历史文章。本次我们要说说discuz!在应对热
MySQL索引优化的实际案例分析
09-10
MySQL索引优化是数据库性能提升的关键环节，特别是在处理大量数据时，正确的索引策略能够显著减少查询时间，提高系统响应速度。本文将通过实际案例深入探讨如何优化包含`ORDER BY DESC/ASC LIMIT M`的SQL语句。 ...
MySQL Index Condition Pushdown(ICP)性能优化方法1
08-03
例如，如果WHERE子句中的条件不能完全通过索引列进行评估，或者涉及到函数、计算表达式、非等值比较（如LIKE、BETWEEN等），ICP可能无法应用。此外，某些存储引擎可能不支持ICP，如MyISAM。 总结来说，理解并合理...
MySql更新优化策略
09-10
MySQL更新优化策略是数据库性能调优的关键环节，尤其是在处理大量数据时。以下是一些关键的知识点，可以帮助理解并提升MySQL的更新效率。 1. **索引优化**：索引是提高查询速度的重要工具，但在更新操作中，如果...
Mysql性能优化案例研究-覆盖索引和SQL_NO_CACHE
09-10
MySQL性能优化是一个重要的主题，尤其是在处理大量数据时。在这个案例中，我们主要关注两个关键概念：覆盖索引和SQL_NO_CACHE。首先，我们来看一下覆盖索引。 覆盖索引是MySQL性能优化的一种策略，它允许MySQL仅...
Solr优化案例分析
02-25
随着umc接入主机的数量越来越多，每天产生的syslog日志数量也在剧增，之前一天产生syslog数量才不到1W，随着整个集团的网络设备不端接入，导致现在每天产生的syslog数量大概180w左右，而这些syslog对网络和PE同学...
MySQL优化、问题案例分析
01-16
有实例的案例分析，深度解读sql 优化，开发测试运维都适用，你值得拥有！
MySQL之优化案例
会者定离，一期一祈
02-19 2164
准备工作 模拟数据表并插入数据 CREATE TABLE IF NOT EXISTS `article`( `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `author_id` INT (10) UNSIGNED NOT NULL, `category_id` INT(10) UNSIGNED NOT NULL , `views` INT(10) UNSIGNED NOT NULL , `comments` INT(10) UNSI
MySQL优化实例
weixin_30296405的博客
06-16 219
这周就要从泰笛离职了，在公司内部的wiki上，根据公司实际的项目，写了一些mysql的优化方法，供小组里的小伙伴参考下，没想到大家的热情很高，还专门搞了个ppt讲解了一下。 举了三个大家很容易犯错的地方，之前项目中也存在，后期修正了。 MySQL优化实例（一） 以我们项目中一个sql语句为例，展示sql语句优化的步骤。 webService项目中有个getWashXiaogeId的封...
mysql优化案例_mysql优化案例分析
weixin_32770697的博客
01-20 128
场景：获取某个卖家未读的消息。select count(*) from mc_msg where receiver='sun098' and status='UNREAD' and title is not null;问题：有时候db负载飙高，sql响应时间变慢。分析：导致db负载飙高的原因是多个大卖家并发查询的时，cpu和逻辑读增加，load飙高。由于receiver,status已有索引，sq...
mysql优化 案例_记一次MySQL的优化案例
weixin_42367947的博客
01-18 123
一 背景有赞的每个OLTP数据库实例上会设置一个sql-killer进程用于kill 掉执行时间超过一定阈值的sql。下午开发接收到sql被kill的报错，一起帮助开发排查，本文介绍该案例。二 场景分析表结构:CREATE TABLE `xxx_info` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',`user_i...
mysql优化案例分析
yimenglin的博客
08-10 331
      本文总结了一些工作常见的sql优化例子，虽然比较简单，但很实用，希望对大家有所帮助。sql优化一般分为两类，一类是sql本身的优化，如何走到合适的索引，如何减少排序，减少逻辑读；另一类是sql本身没有优化余地，需要结合业务场景进行优化。即在满足业务需求的情况下对sql进行改造，已提高sql执行速度，减少响应时间。 例子1： SELECT ID FROM SENDLOG WHERE TO_DAYS(NOW())-TO_DAYS(GMT_CREATE) &gt...
【MySQL优化】实际案例MySQL优化
咔咔博客
04-03 720
索引优化一直在MySQL中占着很重要的位置，但是刚刚入门的还没有接触到这块的知识。已经工作很久也不是很注重这块，只求功能不求性能。当然咔咔也包含在这其中。 这篇文章不会过深的探讨索引原理，只是针对于实际案例给予优化方案。咔咔在网上也搜索了很多的关于MySQL优化方面的文章，但是大多都是以原理为主。个别的排版还很难辨认， 于是在2020/4/2号的22:51咔咔开始撸这篇文章 大战SQL...
mysql优化的例子_MySql性能优化实战篇3-优化实例操作篇
weixin_35361584的博客
02-18 387
实例1：复合索引的最佳左前缀原则(where ... order by ...)创建如下表和索引CREATE TABLE test03(a1 INT(4) NOT NULL,a2 INT(4) NOT NULL,a3 INT(4) NOT NULL,a4 INT(4) NOT NULL);ALTER TABLE test03 ADD INDEX idx_a1_a2_a3_a4(a1,a2,a3,a...
帮我写一篇mysql索引优化的实战案例分析
08-10
本文将提供一个实战案例分析，分析MySQL索引优化的实际应用情况，主要包括索引设计、索引选择、添加索引等。 首先，在设计索引时，需要考虑索引字段和键值的大小，并确定索引类型。如果字段大小超过索引类型的最大...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值