频道栏目
首页 > 资讯 > MySQL > 正文

Mysql的一条SQL优化(一)

15-06-20        来源:[db:作者]  
收藏   我要投稿
开发反应做压测时并发上不去,有条SQL执行非常慢,于是explain下:
MariaDB [db_vip]>
MariaDB [db_vip]> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword,status from m_db_vip where (msource_id='xx1391434680574433' and msource_type=1 ) or ( mobile='1391434680574433' and msource_type=1);
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | m_db_vip | ALL | NULL | NULL | NULL | NULL | 86987 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
MariaDB [db_vip]> show status like '%cost%';
+-----------------+--------------+
| Variable_name | Value |
+-----------------+--------------+
| Last_query_cost | 18006.399000 |
+-----------------+--------------+
1 row in set (0.00 sec)
从执行计划中可以看出,这个SQL对表这个表m_db_vip进行了全表扫描,而表m_db_vip共有约10万行记录,Mysql优化器评估出的成本为18006,
where条件的三列中msource_id与mobile都是选择性很强的列,msource_type的选择性弱,


首先对列msource_id创建一个索引:
MariaDB [db_vip]> create index i_m_db_vip_1 on m_db_vip(msource_id);
Query OK, 0 rows affected (1.18 sec)
Records: 0 Duplicates: 0 Warnings: 0


再次执行SQL,发现Mysql并没有用上msource_id的索引(还是Oracle的CBO智能呀!)
MariaDB [db_vip]> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword,status from m_db_vip where (msource_id='xx1391434680574433' and msource_type=1 ) or ( mobile='1391434680574433' and msource_type=1);
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | m_db_vip | ALL | i_m_db_vip_1 | NULL | NULL | NULL | 86987 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.01 sec)


MariaDB [db_vip]> show status like '%cost%';
+-----------------+--------------+
| Variable_name | Value |
+-----------------+--------------+
| Last_query_cost | 18006.399000 |
+-----------------+--------------+
1 row in set (0.00 sec)
再创建mobile上的索引:
MariaDB [db_vip]> create index i_m_db_vip_2 on m_db_vip(mobile);
Query OK, 0 rows affected (0.96 sec)
Records: 0 Duplicates: 0 Warnings: 0


MariaDB [db_vip]> explain select ext_id, mid, msource_id, msource_type, referee, mobile, tel, mpassword,status from m_db_vip where (msource_id='xx1391434680574433' and msource_type=1 ) or ( mobile='1391434680574433' and msource_type=1);
+------+-------------+-----------+-------------+-----------------------------+-----------------------------+---------+------+------+-------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+-------------+-----------------------------+-----------------------------+---------+------+------+-------------------------------------------------------+
| 1 | SIMPLE | m_db_vip | index_merge | i_m_db_vip_1,i_m_db_vip_2 | i_m_db_vip_1,i_m_db_vip_2 | 98,99 | NULL | 2 | Using union(i_m_db_vip_1,i_m_db_vip_2); Using where |
+------+-------------+-----------+-------------+-----------------------------+-----------------------------+---------+------+------+-------------------------------------------------------+
1 row in set (0.02 sec)


MariaDB [db_vip]> show status like '%cost%';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 6.826060 |
+-----------------+----------+
1 row in set (0.00 sec)


MariaDB [db_vip]>

SQL的执行成本已经大幅降低了,经开发已测试,效果相当不错,并发从10几个冲到2000以上。
相关TAG标签
上一篇:Nginx反向代理与负载均衡
下一篇:使用sqlmapapi.py批量化扫描实践
相关文章
图文推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站