Mysql的一条SQL优化(一)
来源:互联网 发布:2015年度十大网络剧 编辑:程序博客网 时间:2024/04/30 09:36
开发反应做压测时并发上不去,有条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以上。
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以上。
1 0
- Mysql的一条SQL优化(一)
- Mysql的一条SQL优化(二)
- 一条Mysql上的Sql优化经历
- MySQL优化之SQL优化(一)
- 一条SQL语句的优化过程
- 一条SQL语句的优化学习
- 记一条子查询的SQL优化
- 一条经典的SQL语句优化实例
- Oracle下一条SQL语句的优化
- 一条相关子查询的SQL优化
- Mysql优化(一)—Sql语句
- SQL的优化(一)
- 一条sql去除mysql的重复记录
- Mysql 千万以上数据优化方法(一,SQL优化)
- 【MySql】Sql优化(一)——Sql执行流程
- mysql 的sql优化
- mysql的sql优化
- 【MySQL】基于MySQL的SQL优化(一)——从用explain关键字分析SQL语句开始
- Swift项目中调用Objective-C的库
- 北大OJ_1007题:DNA Sorting
- java 值传递 引用传递(又叫地址传递,对象传递)
- 【少年の轨迹】cocos2d-x3.0中LayerColor的锚点的修改
- PHP类与对象,构造函数和析构函数,加上this绑定详解
- Mysql的一条SQL优化(一)
- java笔记22 IO流1(字符流及缓冲区)
- 【龙书答案】第三章解析(未完成)
- 解决升级Xcode6后插件不能用的问题
- 学习笔记:axure 高级教程 2 复选框(添加判断条件)
- 欢迎使用CSDN-markdown编辑器
- 从Code Review 谈如何做技术
- 内部排序(四)归并排序
- 文章标题