【MYSQL优化16,17】索引与排序

索引与排序

排序可能发生2种情况
1.对于覆盖索引，直接在索引上查询时就是有序的，using index
2.先取出数据，形成临时表filesort（文件排序，但文件可能在磁盘上，也可能在内存中）

我们争取的目标–取出的数据本身就是有序的，利用索引来排序

比如：gold_log表，(uid,num)组成联合索引

mysql> show create table gold_log G;
*************************** 1. row ***************************
       Table: gold_log
Create Table: CREATE TABLE `gold_log` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `uid` int(10) DEFAULT '0' COMMENT '用户id',
  `change_type` tinyint(4) DEFAULT '0' COMMENT '来源类型',
  `num` int(10) DEFAULT '0' COMMENT '数量',
  `left_num` int(10) DEFAULT '0' COMMENT '剩余数量',
  `type` int(10) DEFAULT '0' COMMENT '行为类型',
  `create_time` int(10) DEFAULT '0' COMMENT '创建时间',
  `game_type` int(10) DEFAULT '0' COMMENT '游戏分类',
  `sub_type` int(10) DEFAULT '0' COMMENT '游戏id',
  `order_id` varchar(32) DEFAULT '0' COMMENT '订单id',
  `lvl` int(11) NOT NULL DEFAULT '0',
  `relief_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '需要救济状态',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `ctime_type_left_num` (`create_time`,`type`,`left_num`) USING BTREE,
  KEY `uid_num` (`uid`,`num`)
) ENGINE=InnoDB AUTO_INCREMENT=205329440 DEFAULT CHARSET=utf8 COMMENT='金币日志表'

using where，按照 uid_num 索引取出的结果，本身就是有序的

mysql> explain select id,uid,num,create_time from gold_log where uid =1520002 order by num;
+----+-------------+----------+------+------------------------+---------+---------+-------+------+-------------+
| id | select_type | table    | type | possible_keys          | key     | key_len | ref   | rows | Extra       |
+----+-------------+----------+------+------------------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | gold_log | ref  | uid_ctime_type,uid_num | uid_num | 5       | const |   24 | Using where |
+----+-------------+----------+------+------------------------+---------+---------+-------+------+-------------+

using filesort，使用文件排序，即取出的结果再次排序

mysql> explain select id,uid,num,create_time from gold_log where uid =1520002 order by create_time;
+----+-------------+----------+------+---------------+---------+---------+-------+------+-----------------------------+
| id | select_type | table    | type | possible_keys | key     | key_len | ref   | rows | Extra                       |
+----+-------------+----------+------+---------------+---------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | gold_log | ref  | uid_num       | uid_num | 5       | const |   24 | Using where; Using filesort |
+----+-------------+----------+------+---------------+---------+---------+-------+------+-----------------------------+

去掉联合索引时，查询使用 using filesort 文件排序

mysql> alter table gold_log drop index uid_num;

mysql> explain select id,uid,num,create_time from gold_log where uid =1520002 order by num;
+----+-------------+----------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+----------+------+---------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | gold_log | ALL  | NULL          | NULL | NULL    | NULL | 63118 | Using where; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+-------+-----------------------------+

重复索引与冗余索引

重复索引是指，在同一个列（如a）,或者顺序相同的几个列（a,b）,建立了多个索引，称为重复索引.
重复索引没有任何帮助，只会增大索引文件，拖慢更新速度，去掉

mysql> alter table gold_log add index num(num);
mysql> alter table gold_log add index num2(num);
mysql> show create table gold_log G;
*************************** 1. row ***************************
       Table: gold_log
Create Table: CREATE TABLE `gold_log` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `uid` int(10) DEFAULT '0' COMMENT '用户id',
  `change_type` tinyint(4) DEFAULT '0' COMMENT '来源类型',
  `num` int(10) DEFAULT '0' COMMENT '数量',
  `left_num` int(10) DEFAULT '0' COMMENT '剩余数量',
  `type` int(10) DEFAULT '0' COMMENT '行为类型',
  `create_time` int(10) DEFAULT '0' COMMENT '创建时间',
  `game_type` int(10) DEFAULT '0' COMMENT '游戏分类',
  `sub_type` int(10) DEFAULT '0' COMMENT '游戏id',
  `order_id` varchar(32) DEFAULT '0' COMMENT '订单id',
  `lvl` int(11) NOT NULL DEFAULT '0',
  `relief_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '需要救济状态',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `ctime_type_left_num` (`create_time`,`type`,`left_num`) USING BTREE,
  KEY `uid_num` (`uid`,`num`),
  KEY `num` (`num`),
  KEY `num2` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=205329440 DEFAULT CHARSET=utf8 COMMENT='金币日志表'

冗余索引指，2个索引所覆盖的列有重叠，称为冗余索引
比如，x,y列加索引 inde x(x),index xy(x,y)，即上列中的索引 num 和 uid_num
或者顺序不一样，index xy(x,y)，index yx(y,x)