Mysql的一条SQL优化(一)_MySQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql的一条SQL优化(一)_MySQL,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4114字,纯文字阅读大概需要6分钟。
内容图文
![Mysql的一条SQL优化(一)_MySQL](/upload/InfoBanner/zyjiaocheng/580/276481ba07c840d49558009a3dd9eb33.jpg)
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以上。
内容总结
以上是互联网集市为您收集整理的Mysql的一条SQL优化(一)_MySQL全部内容,希望文章能够帮你解决Mysql的一条SQL优化(一)_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。