order by

asc 升序；desc 降序
order by 也可以优化，和索引有关。将“文件排序”转换成“using index”

mysql> explain select * from user order by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select age from user order by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id from user order by id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user order by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name from user order by name;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | name | 52      | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以发现当select A from T order by B; 里面的A和B相同且A有索引时，搜索方式由“Using filesort”变为了“Using index”。

group by

比 distinct 重，比较两者的输出可以发现 group by 还会排序大小

mysql> select * from user;
+----+-------------+-----+-----+
| id | name        | age | sex |
+----+-------------+-----+-----+
|  4 | zhang san   |  20 | M   |
|  5 | li si       |  21 | W   |
|  6 | zhang xiang |  20 | M   |
|  7 | zhang ai    |  21 | W   |
|  8 | wu liu      |  21 | M   |
|  9 | wu qi       |  22 | M   |
| 10 | wu ba       |  24 | M   |
| 11 | wu jiu      |  23 | M   |
+----+-------------+-----+-----+
8 rows in set (0.00 sec)

mysql> select distinct age from user;
+-----+
| age |
+-----+
|  20 |
|  21 |
|  22 |
|  24 |
|  23 |
+-----+
5 rows in set (0.00 sec)

mysql> select age from user group by age;
+-----+
| age |
+-----+
|  20 |
|  21 |
|  22 |
|  23 |
|  24 |
+-----+
5 rows in set (0.00 sec)

可以加上函数

mysql> select age,count(age) as count from user group by age;
+-----+-------+
| age | count |
+-----+-------+
|  20 |     2 |
|  21 |     3 |
|  22 |     1 |
|  23 |     1 |
|  24 |     1 |
+-----+-------+
5 rows in set (0.00 sec)

有两种方式加上条件筛选：前面加where、后面加having

mysql> select age,count(*) as count from user where age=20 group by age;
+-----+-------+
| age | count |
+-----+-------+
|  20 |     2 |
+-----+-------+
1 row in set (0.00 sec)

mysql> select age,count(*) as count from user group by age having age = 20;
+-----+-------+
| age | count |
+-----+-------+
|  20 |     2 |
+-----+-------+
1 row in set (0.01 sec)

也可以两个参数一起

mysql> select age,name from user group by age,name limit 3;
+-----+-------------+
| age | name        |
+-----+-------------+
|  20 | zhang san   |
|  20 | zhang xiang |
|  21 | li si       |
+-----+-------------+
3 rows in set (0.00 sec)

同样也有索引优化

mysql> explain select age from user group by age;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name from user group by name;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | name          | name | 52      | NULL |    8 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)