mysql的using filesort优化

       使用order by的时候， 容易引入using filesort,  可能导致性能问题， 来看看：

mysql> show create table tb_province;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_province | CREATE TABLE `tb_province` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `score` int(10) unsigned DEFAULT '0',
  `x` int(10) unsigned DEFAULT '0',
  `x1` int(10) unsigned DEFAULT '0',
  `x2` int(10) unsigned DEFAULT '0',
  `x3` int(10) unsigned DEFAULT '0',
  `x4` int(10) unsigned DEFAULT '0',
  `x5` int(10) unsigned DEFAULT '0',
  `x6` int(10) unsigned DEFAULT '0',
  `x7` int(10) unsigned DEFAULT '0',
  `x8` int(10) unsigned DEFAULT '0',
  `x9` int(10) unsigned DEFAULT '0',
  `x10` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `g` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=124178 DEFAULT CHARSET=latin1 |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    看看：

mysql> explain select * from tb_province where name='lucy' order by score ;
+----+-------------+-------------+------------+------+---------------+------+---------+-------+-------+----------+---------------------------------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref   | rows  | filtered | Extra                                 |
+----+-------------+-------------+------------+------+---------------+------+---------+-------+-------+----------+---------------------------------------+
|  1 | SIMPLE      | tb_province | NULL       | ref  | g             | g    | 34      | const | 54390 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------------+------------+------+---------------+------+---------+-------+-------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

   缺少联合索引， 添加一下：

mysql> alter table tb_province add index g1(name, score);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> 
mysql> show create table tb_province;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tb_province | CREATE TABLE `tb_province` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `score` int(10) unsigned DEFAULT '0',
  `x` int(10) unsigned DEFAULT '0',
  `x1` int(10) unsigned DEFAULT '0',
  `x2` int(10) unsigned DEFAULT '0',
  `x3` int(10) unsigned DEFAULT '0',
  `x4` int(10) unsigned DEFAULT '0',
  `x5` int(10) unsigned DEFAULT '0',
  `x6` int(10) unsigned DEFAULT '0',
  `x7` int(10) unsigned DEFAULT '0',
  `x8` int(10) unsigned DEFAULT '0',
  `x9` int(10) unsigned DEFAULT '0',
  `x10` int(10) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `g` (`name`),
  KEY `g1` (`name`,`score`)
) ENGINE=InnoDB AUTO_INCREMENT=124178 DEFAULT CHARSET=latin1 |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    再看看：

mysql> explain select * from tb_province where name='lucy' order by score ;
+----+-------------+-------------+------------+------+---------------+------+---------+-------+-------+----------+-----------------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref   | rows  | filtered | Extra                 |
+----+-------------+-------------+------------+------+---------------+------+---------+-------+-------+----------+-----------------------+
|  1 | SIMPLE      | tb_province | NULL       | ref  | g,g1          | g1   | 34      | const | 54390 |   100.00 | Using index condition |
+----+-------------+-------------+------------+------+---------------+------+---------+-------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

   using filesort消失了。

   吃饭。