mysqlwhere和order by mysqlwhere和order by索引

一：优化原则

原则1

WHERE + ORDER BY的索引优化，形如：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY[sort];
建立一个联合索引(columnX,sort)来实现order by优化。注意复合索引字段的顺序。

注意：如果columnX跟类似范围的查询(IN,LIKE,>,<,between …and)，如下面语句就无法利用索引来实现order by的优化
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN([value1],[value2],…) ORDER BY[sort];

SELECT[column1],[column2],…. FROM [TABLE] WHERE [columnX] like [value1] ORDERBY[sort];

 

原则2

表a 中id字段上面建有一个索引 

select * from a order by id   (不会走索引)

select id from a order by id (会走索引)

select * from a where id=XX order by id  (会走索引)

意思是说order by 要避免使用文件系统排序，要么只把order by的字段出现在select 后，要么使用order by字段出现在where 条件里，要么把order by字段与where 条件字段建立复合索引！

 

原则3

当只有order by，没有limit，且该字段没有出现在where条件里时，不走索引，会发生文件排序。当order by和limit m,n(m,n较小)连用时，即使该字段没有出现在where条件里，依然可能会走该索引。

示例（在lastUpdateDate上创建了索引）：

原则4

在非连续的复合索引键部分上做 ORDER BY无法使用索引排序：(key_part1,key_part2建立联合索引;key2建立索引)
SELECT * FROM t1 WHERE key2=constant ORDER BYkey_part2;

 

二：优化案例

下面是一个具体案例：

通过percona toolkit工具查看本周慢查询，发现有一条sql执行很慢：

# Profile

# Rank Query ID                        Response time     Calls       R/Call    V/M   Item

# ==== ================== ====================== ========== ===== =======

#   1 0xD7DA8CCE6DDA0C11 525428.4969 30.6% 3199   164.2477 48... SELECTpending_?

平均每次执行需要164秒，将近3分钟。

找到该查询：

为了不影响主库，这里在一个较空闲的从库上验证。

查看该sql执行计划，发现走的是这个索引lastUpdateDate：

mysql> explain select * from pending_22where pendingUserID = 'panyuwen' and pendingTitle like '%亲情卡%' orderby lastUpdateDate desc limit 0, 50;
+----+-------------+------------+-------+----------------------------+----------------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys              |key            | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+----------------------------+----------------+---------+------+------+-------------+
|  1| SIMPLE      | pending_22 | index |pendingUserID,index_search | lastUpdateDate | 51      | NULL | 6352 | Using where |
+----+-------------+------------+-------+----------------------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

查看该表索引：

mysql> show index from pending_22;
+------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name           | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type |Comment | Index_comment |
+------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| pending_22 |          0 | PRIMARY             |            1 | _id                 | A         |    1833466 |     NULL | NULL   |     | BTREE      |         |               |
| pending_22 |          1 | pendingStatus       |            1 | pendingStatus       | A         |          10 |     NULL | NULL   | YES | BTREE      |         |               |
| pending_22 |          1 | pendingUserID       |            1 | pendingUserID       | A         |        2501 |     NULL | NULL   | YES | BTREE      |         |               |
| pending_22 |          1 | collect             |            1 | collect             | A         |          10 |     NULL | NULL   | YES | BTREE      |         |               |
| pending_22 |          1 | pendingType         |            1 | pendingType         | A         |         10 |     NULL | NULL   | YES | BTREE      |         |               |
| pending_22 |          1 | lastUpdateDate      |            1 | lastUpdateDate      | A         |    1833466 |     NULL | NULL   | YES | BTREE      |         |               |
| pending_22 |          1 | pendingTitlePinyin  |           1 | pendingTitlePinyin  | A         |     166678 |     NULL | NULL   | YES | BTREE      |         |               |
| pending_22 |          1 | pendingSourcePinyin |            1 | pendingSourcePinyin | A         |      22918 |     NULL | NULL   | YES | BTREE      |         |               |
| pending_22 |          1 | index_search        |            1 | pendingUserID       | A         |      11179 |     NULL | NULL   | YES | BTREE      |         |               |
| pending_22 |          1 | index_search        |            2 | pendingTitle        | A         |    1833466 |     NULL | NULL   | YES | BTREE      |         |               |
+------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.62 sec)

该表在pendingUserID，lastUpdateDate上分别有一个索引，还有一个复合索引index_search(pendingUserID, pendingTitle).

由于where条件中pendingTitle like ‘%亲情%’,所以没有用到该复合索引。

该表有300万条数据。

mysql> use np002;

Database changed

mysql> select count(*) from pending_22;

+----------+

| count(*) |

+----------+

| 3038218 |

+----------+

1 row in set (0.71 sec)

在从库上手动执行下该sql，发现需要用时20秒左右，比在主库上用时较短。

给where条件和order by后的字段创建一个复合索引

mysql> create index ind_2 onpending_22(pendingUserID,lastUpdateDate);

Query OK, 0 rows affected (35.17 sec)

Records: 0 Duplicates: 0  Warnings: 0

从20秒缩短到了0.08秒，嘎嘎。

#注意，这里没有创建包含pendingTitle的复合索引(pendingUserID,pendingTitle,lastUpdateDate)是因为where pendingTitle中有like,这会导致范围条件后的lastUpdateDate无法使用，因此不会走该索引，会出现Using filesort。

 在从库做完实验，记得把该索引删掉。否则到时在主库上建索引，往从库上同步时，会出错。 

目前该表有个复合索引ind_2(pendingUserID, lastUpdateDate),因此需要删掉重复索引pendingUserID。