MYSQL 排序 索引 小测

root@orphean 02:29:27>select count(*) from tmp_info where status = 1;
+----------+
| count(*) |
+----------+
|    32911 |
+----------+
1 row in set (0.02 sec)

root@orphean 02:29:40>select count(*) from tmp_info where status = 2;
+----------+
| count(*) |
+----------+
|    10005 |
+----------+
1 row in set (0.00 sec)

root@orphean 02:33:16>select count(*) from tmp_info
-> where status = 1 and outer_sys_code = 1 and
-> level <=10000 and appraise <=1000 and open_date >='2009-06-15 11:27:39' and category like '%,1048,%';
+----------+
| count(*) |
+----------+
|    32888 |
+----------+
1 row in set (0.19 sec)

--可以看到虽然加了很多查询条件，但是过滤性并不好，返回的数据量还是比较大，占了增加的表3/4
--看一下全表扫描和用索引的消耗
索引字段：`status`, `level`, `appraise`, `open_date`, `outer_sys_code`, `gmt_modified`

root@orphean 04:06:20>explain select count(*) from tmp_info
-> where status = 1 and outer_sys_code = 1 and
-> level <=10000 and appraise <=1000 and open_date >='2009-06-15 11:27:39' and category like '%,1048,%' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tmp_info
type: ref
possible_keys: IDX_RECRUIT_STATUS
key: IDX_RECRUIT_STATUS
key_len: 1
ref: const
rows: 1626
Extra: Using where
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|    32888 |
+----------+
1 row in set (0.17 sec)

=======================
root@orphean 04:07:08>explain select count(*) from tmp_info IGNORE INDEX (IDX_RECRUIT_STATUS)
-> where status = 1 and outer_sys_code = 1 and
-> level <=10000 and appraise <=1000 and open_date >='2009-06-15 11:27:39' and category like '%,1048,%' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tmp_info
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 42277
Extra: Using where
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|    32888 |
+----------+
1 row in set (0.14 sec)

可以看到全表扫描速度要快，其实这个也是可想而知的，用索引扫描返回了表70%多的数据，上面的是count语句，下面看一下分页list语句

root@orphean 04:12:49>explain select * from tmp_info   where status = 1 and outer_sys_code = 1 and
-> level <=10000 and appraise <=1000 and open_date >='2009-06-15 11:27:39' and category like '%,1048,%'
-> ORDER BY gmt_modified desc
-> LIMIT 1,20 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tmp_info
type: ref
possible_keys: IDX_RECRUIT_STATUS
key: IDX_RECRUIT_STATUS
key_len: 1
ref: const
rows: 21041
Extra: Using where
1 row in set (0.00 sec)

---------------------------+------------------------+---------------------+---------------------+
20 rows in set (0.00 sec)

root@orphean 04:13:19>explain select * from tmp_info   IGNORE INDEX (IDX_RECRUIT_STATUS)
-> where status = 1 and outer_sys_code = 1 and
-> level <=10000 and appraise <=1000 and open_date >='2009-06-15 11:27:39' and category like '%,1048,%'
-> ORDER BY gmt_modified desc
-> LIMIT 1,20 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tmp_info
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 42082
Extra: Using where; Using filesort
1 row in set (0.00 sec)

---------------------------+-------------
20 rows in set (0.33 sec)

这个非常明显，看到使用索引去掉filesort之后，sql的效率非常高，显示执行的时间是0.00s（基本在几个毫秒），而使用全表就需要非常330ms
如果我把索引中的gmt_modifid字段移后，是排序用不到索引，那是什么效果呢

ALTER TABLE `orphean`.`tmp_info` DROP INDEX `IDX_RECRUIT_STATUS`,
ADD INDEX `IDX_RECRUIT_STATUS` USING BTREE(`status`, `level`, `appraise`, `open_date`, `outer_sys_code`， `gmt_modified`);

root@orphean 04:58:50>EXPLAIN select * from tmp_info
-> where status = 1 and outer_sys_code = 1 and
-> level <=10000 and appraise <=1000 and open_date >='2009-06-15 11:27:39' and category like '%,1048,%'
-> ORDER BY gmt_modified desc
-> LIMIT 1,20 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tmp_info
type: ref
possible_keys: IDX_RECRUIT_STATUS
key: IDX_RECRUIT_STATUS
key_len: 1
ref: const
rows: 11234
Extra: Using where; Using filesort
1 row in set (0.00 sec)

---------------------------+------------------------+---------------------+---------------------+
20 rows in set (0.35 sec)

没有用到排序索引消耗的代价比全表扫描来的还大，可惜排序对于mysql的消耗是非常非常大的。 上面的sql比较是将30000多条记录排序啊，代价昂贵！

在性能测试的时候，使用全表扫描或者没有用上索引排序的时候，数据库CPU在50%（16个CPU用掉了8个），load在8以上，而在用索引去掉排序之后，使CPU下降到4%，load在1以下

小结：1. 尽可以去除mysql的排序，使用索引。 要使用索引来用作排序，必须索引列都在查询条件中，而且排序字段前面的查询条件是用=号连接，
因此可能会创建多个索引，前面的字段都相同，只是排序字段的位置不一样的情况。 这个时候用强制索引去提示吧
2. 如果在确定查询返回的结果集太大，查询条件的筛选度太下，那就干脆用全表扫描吧，用 IGNORE INDEX (IDX_NAME)来忽略索引。