A few weeks ago, we had a query optimization request from one of our customer.
The query was very simple like:
CODE:
-
SELECT * FROM `table` WHERE (col1='A'||col1='B') ORDER BY id DESC LIMIT 20 OFFSET 0
This column in the table is looks like this:
CODE:
-
`col1` enum('A','B','C','CD','DE','F','G','HI') default NULL
The table have 549252 rows and of course, there is an index on the col1. MySQL estimated the cardinality of that index as 87, though what was of course misleading as index cardinality in this case can't be over 9, as there is only 8(+ NULL) different possible values for this column.
CODE:
-
+----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-
+----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
-
| 1 | SIMPLE | table | range | col1 | col1 | 2 | NULL | 549252 | Using where; Using filesort |
-
+----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
This query took more than 5 minutes (the rows are large and table does not fit in cache well)
When you want to run this query mysql first will try to find each row where col1 is A or B using index. Then its going to order by the ID using file sort and then send first 20 rows ignoring the rest.
In this case MySQL has 2 indexes where one is usable to find rows, while other is usable to return them in the right order. MySQL can chose only one of them to execute the query - use index to find rows. This is sensible strategy if there is no LIMIT, however it is poor chose if there is one - it is often a lot faster to retrieve rows in order checking WHERE clause for them until required number of rows were returned. Especially in the cases when WHERE clause is not very selective.
So I tried this:
CODE:
-
select * from table where id in (SELECT id FROM `table` WHERE (col1='A'||col1='B')) ORDER BY id DESC LIMIT 20 OFFSET 0;
In this case we forcing MySQL to do retrieve rows in sorted order and checking if it matches our original WHERE clause with subselects. It looks scary if we look at EXPLAIN but in reality the dependent subquery is only executed enough times to produce 20 rows in result set.
CODE:
-
+----+--------------------+-------+-----------------+---------------+---------+---------+------+--------+-------------+
-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-
+----+--------------------+-------+-----------------+---------------+---------+---------+------+--------+-------------+
-
| 1 | PRIMARY | table | index | NULL | PRIMARY | 4 | NULL | 765105 | Using where |
-
| 2 | DEPENDENT SUBQUERY | table | unique_subquery | PRIMARY,col1 | PRIMARY | 4 | func | 1 | Using where |
-
+----+--------------------+-------+-----------------+---------------+---------+---------+------+--------+-------------+
The result is a lot better result time:
CODE:
-
(20 rows in set (0.01 sec))
So by rewriting query using subqueries we actually improved it performance 100 times. So subqueries are
not always slowing things down.
Even though proving subqueries are not always slow this way is not the most optimal. We do not really need separate subselect to make MySQL check WHERE clause while scanning table in index order. We can just use FORCE INDEX hint to override MySQL index choice:
CODE:
-
mysql> explain select * from table FORCE INDEX(PRIMARY) where (col1='A'||col1='B') order by id desc limit 20 offset 0;
-
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
-
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
-
| 1 | SIMPLE | table | index | NULL | PRIMARY | 4 | NULL | 549117 | Using where |
-
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
-
-
mysql> select * from table FORCE INDEX(PRIMARY) where (col1='A'||col1='B') order by id desc limit 20 offset 0;
-
...
-
20 rows in set (0.00 sec)
This approach works well if WHERE clause is not very selective, otherwise MySQL may need to scan very many rows to find enough matching rows. You can use another trick Peter
wrote. about couple of years ago.
分享到:
相关推荐
以SQL Server为工具,讲解SQL语言的应用,提供了近500个曲型应用,读者可以随查随用,深入讲解SQL语言的各种查询语句,详细介绍数据库设计及管理,详细讲解存储过程、解发器和游标等知识,讲解了SQL语言在高级语言中...
ii. Subselect 当访问内存中的一个代理容器时,一次将所有的set容器用一个子查询,全部初始化 iii. Join 1. 对OID检索方式有用 2. 对hql检索方式没有用 b) 在hql中要使用迫切左外连接时,必须加 left join fetch ...
The next example shows the definition of a sequence generator in a class scope: @Entity @javax.persistence.SequenceGenerator( name="SEQ_STORE", sequenceName="my_sequence" ) public class Store ...
正确理解hibernate fetch 的作用
hibernate配置参数详解hibernate配置参数详解hibernate配置参数详解hibernate配置参数详解
(400KB)<END><br>78,AdvancedPrev.zip A simple class that helps provide faster Print Preview within MFC Doc/View applications(38KB)<END><br>79,mditab.zip A dockable bar containing a tabbed list ...
Using subselect fetching 19.1.7. Using lazy property fetching 19.2. The Second Level Cache 19.2.1. Cache mappings 19.2.2. Strategy: read only 19.2.3. Strategy: read/write 19.2.4. Strategy: nonstrict ...
Hibernate关联关系hbm.xml中的相关属性many-to-one/one-to-many/many-to-many 中所有属性值以及其用法。
使用子查询抓取(Using subselect fetching) 19.1.7. 使用延迟属性抓取(Using lazy property fetching) 19.2. 二级缓存(The Second Level Cache) 19.2.1. 缓存映射(Cache mappings) 19.2.2. 策略:只读...
使用子查询抓取(Using subselect fetching) 19.1.7. 使用延迟属性抓取(Using lazy property fetching) 19.2. 二级缓存(The Second Level Cache) 19.2.1. 缓存映射(Cache mappings) 19.2.2. 策略:只读...
16.1.6. 使用子查询抓取(Using subselect fetching) 16.2. 二级缓存(The Second Level Cache) 16.2.1. 缓存映射(Cache mappings) 16.2.2. 策略:只读缓存(Strategy: read only) 16.2.3. 策略:读/写缓存...
16.1.6. 使用子查询抓取(Using subselect fetching) 16.2. 二级缓存(The Second Level Cache) 16.2.1. 缓存映射(Cache mappings) 16.2.2. 策略:只读缓存(Strategy: read only) 16.2.3. 策略:读/写缓存...
19.1.5. 使用子查询抓取(Using subselect fetching) 19.1.6. 使用延迟属性抓取(Using lazy property fetching) 19.2. 二级缓存(The Second Level Cache) 19.2.1. 缓存映射(Cache mappings) 19.2.2. 策略...
19.1.5. 使用子查询抓取(Using subselect fetching) 19.1.6. 使用延迟属性抓取(Using lazy property fetching) 19.2. 二级缓存(The Second Level Cache) 19.2.1. 缓存映射(Cache mappings) 19.2.2. 策略...
目录 前言 1.... 2.... 1. 在Tomcat中快速上手 ... 1.1.... 1.2.... 1.3.... 1.4.... 1.5.... 2.1.... 2.2.... 2.2.1.... 2.2.2.... 2.2.3.... 2.2.4.... 2.2.5.... 2.2.6.... 2.3.... 2.3.1.... 2.3.2.... 2.3.3.... 2.3.4.... 2.3.5.... 2.3.6.... 2.4.... 3.1.... 3.2....
select id=”subSelect” name=”rawfoodBasic.absorb” onchange=”[removed]alert(this.value);” > //要3及菜单还可以在次写相应的时间 ”0″>–请选择二级菜单– </option> </select> js代
使用子查询抓取(Using subselect fetching) 20.1.7. 使用延迟属性抓取(Using lazy property fetching) 20.2. 二级缓存(The Second Level Cache) 20.2.1. 缓存映射(Cache mappings) 20.2.2. 策略:...
使用子查询抓取(Using subselect fetching) 20.1.7. 使用延迟属性抓取(Using lazy property fetching) 20.2. 二级缓存(The Second Level Cache) 20.2.1. 缓存映射(Cache mappings) 20.2.2. 策略:只读...
使用子查询抓取(Using subselect fetching) 19.1.7. 使用延迟属性抓取(Using lazy property fetching) 19.2. 二级缓存(The Second Level Cache) 19.2.1. 缓存映射(Cache mappings) 19.2.2. 策略:只读...
使用子查询抓取(Using subselect fetching) 19.1.7. 使用延迟属性抓取(Using lazy property fetching) 19.2. 二级缓存(The Second Level Cache) 19.2.1. 缓存映射(Cache mappings) 19.2.2. 策略:只读...