employees database
然后我注意到有一个查询没有使用覆盖索引,尽管我之前创建了一个对应的索引。只有当我加了一个
FORCE INDEX
查询的子句,
它使用了覆盖索引
.
我上传了两个文件,一个是
the executed SQL queries
the results
.
力指数
dept_no_from_date_idx
为了使自己适应SO的标准,我还在这里写了两个文件的内容:
SQL查询:
USE employees;
/* Creating an index for an index-covered query */
CREATE INDEX dept_no_from_date_idx ON dept_emp (dept_no, from_date);
/* Show `dept_emp` table structure, indexes and generic data */
SHOW TABLE STATUS LIKE "dept_emp";
DESCRIBE dept_emp;
SHOW KEYS IN dept_emp;
/* The EXPLAIN shows that the subquery doesn't use a covering-index */
EXPLAIN SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
/* The subquery should use a covering index, but isn't */
SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`);
/* The EXPLAIN shows that the subquery DOES use a covering-index,
thanks to the FORCE INDEX clause */
EXPLAIN SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
/* The subquery use a covering index */
SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp FORCE INDEX(dept_no_from_date_idx) WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`);
结果是:
--------------
/* Creating an index for an index-covered query */
CREATE INDEX dept_no_from_date_idx ON dept_emp (dept_no, from_date)
--------------
Query OK, 331603 rows affected (33.95 sec)
Records: 331603 Duplicates: 0 Warnings: 0
--------------
/* Show `dept_emp` table structure, indexes and generic data */
SHOW TABLE STATUS LIKE "dept_emp"
--------------
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| dept_emp | InnoDB | 10 | Compact | 331883 | 36 | 12075008 | 0 | 21544960 | 29360128 | NULL | 2010-05-04 13:07:49 | NULL | NULL | utf8_general_ci | NULL | | |
+----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.47 sec)
--------------
DESCRIBE dept_emp
--------------
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| dept_no | char(4) | NO | PRI | NULL | |
| from_date | date | NO | | NULL | |
| to_date | date | NO | | NULL | |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.05 sec)
--------------
SHOW KEYS IN dept_emp
--------------
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| dept_emp | 0 | PRIMARY | 1 | emp_no | A | 331883 | NULL | NULL | | BTREE | |
| dept_emp | 0 | PRIMARY | 2 | dept_no | A | 331883 | NULL | NULL | | BTREE | |
| dept_emp | 1 | emp_no | 1 | emp_no | A | 331883 | NULL | NULL | | BTREE | |
| dept_emp | 1 | dept_no | 1 | dept_no | A | 7 | NULL | NULL | | BTREE | |
| dept_emp | 1 | dept_no_from_date_idx | 1 | dept_no | A | 13 | NULL | NULL | | BTREE | |
| dept_emp | 1 | dept_no_from_date_idx | 2 | from_date | A | 165941 | NULL | NULL | | BTREE | |
+----------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.23 sec)
--------------
/* The EXPLAIN shows that the subquery doesn't use a covering-index */
EXPLAIN SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
/* The subquery should use a covering index, but isn't */
SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`)
--------------
+----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 50 | |
| 1 | PRIMARY | dept_emp | eq_ref | PRIMARY,emp_no,dept_no,dept_no_from_date_idx | PRIMARY | 16 | der.emp_no,der.dept_no | 1 | |
| 2 | DERIVED | dept_emp | ref | dept_no,dept_no_from_date_idx | dept_no_from_date_idx | 12 | | 21402 | Using where |
+----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+-------------+
3 rows in set (0.09 sec)
--------------
/* The EXPLAIN shows that the subquery DOES use a covering-index,
thanks to the FORCE INDEX clause */
EXPLAIN SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
/* The subquery use a covering index */
SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp FORCE INDEX(dept_no_from_date_idx) WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`)
--------------
+----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+--------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 50 | |
| 1 | PRIMARY | dept_emp | eq_ref | PRIMARY,emp_no,dept_no,dept_no_from_date_idx | PRIMARY | 16 | der.emp_no,der.dept_no | 1 | |
| 2 | DERIVED | dept_emp | ref | dept_no_from_date_idx | dept_no_from_date_idx | 12 | | 37468 | Using where; Using index |
+----+-------------+------------+--------+----------------------------------------------+-----------------------+---------+------------------------+-------+--------------------------+
3 rows in set (0.05 sec)
Bye
我注意到,最后两个查询的执行速度有很大的差异,结果摆在您面前:
SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`)
--------------
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 38552 | d001 | 1985-04-16 | 2000-10-20 |
... omitted ...
| 98045 | d001 | 1985-03-28 | 9999-01-01 |
+--------+---------+------------+------------+
50 rows in set (0.31 sec)
--------------
SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp FORCE INDEX(dept_no_from_date_idx) WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`)
--------------
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 38552 | d001 | 1985-04-16 | 2000-10-20 |
... omitted ...
| 98045 | d001 | 1985-03-28 | 9999-01-01 |
+--------+---------+------------+------------+
50 rows in set (0.06 sec)
但是,如果我更改执行顺序(使最后一个查询首先执行,第一个查询最后执行),那么执行速度是相同的:
--------------
SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp FORCE INDEX(dept_no_from_date_idx) WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`)
--------------
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 38552 | d001 | 1985-04-16 | 2000-10-20 |
... omitted ...
| 98045 | d001 | 1985-03-28 | 9999-01-01 |
+--------+---------+------------+------------+
50 rows in set (0.08 sec)
--------------
SELECT SQL_NO_CACHE * FROM dept_emp INNER JOIN (
SELECT SQL_NO_CACHE emp_no, dept_no FROM dept_emp WHERE dept_no="d001" ORDER BY from_date DESC LIMIT 20000,50
) AS `der` USING (`emp_no`, `dept_no`)
--------------
+--------+---------+------------+------------+
| emp_no | dept_no | from_date | to_date |
+--------+---------+------------+------------+
| 38552 | d001 | 1985-04-16 | 2000-10-20 |
... omitted ...
| 98045 | d001 | 1985-03-28 | 9999-01-01 |
+--------+---------+------------+------------+
50 rows in set (0.08 sec)
我认为执行速度的差异源于操作系统缓存和其他因素。当重复执行上述两个查询时,执行时间差可以忽略不计。我重复执行了3次以上2个查询,得到如下结果:
#1: 0.08 sec
#2: 0.03 sec
#1: 0.05 sec
#2: 0.05 sec
#1: 0.03 sec
#2: 0.05 sec
