MySQL-总结-SQL优化

mysql> select * from a;
+----+------+------+
| id | num1 | num2 |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    2 |    2 |
|  4 |    1 |    2 |
|  5 |    1 |    5 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> show create table a;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `id` int(11) NOT NULL,
  `num1` int(11) DEFAULT NULL,
  `num2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select id from a where num1=2 order by id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | index | NULL          | PRIMARY | 4       | NULL |    5 |    20.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)
######type是index  官方文档解释：The index join type is the same as ALL, except that the index tree is scanned. 

mysql> alter table a add index idx_num1 (num1);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select id from a where num1=2 order by id;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | a     | NULL       | ref  | idx_num1      | idx_num1 | 5       | const |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

####type 是ref表明是走的二级索引， Extra的using index 表明使用到覆盖索引，不需要回表。

mysql> explain select id from a where num1 > 1 order by id;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | range | idx_num1      | idx_num1 | 5       | NULL |    2 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)
#####type是range表明是范围扫描，Extra用到Using index，说明不需要回表，Using filesort说明需要排序。num1是范围的话，id是逻辑无序的。 

mysql> explain select * from a where num1=2 order by id;
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | a | NULL | ref | idx_num1 | idx_num1 | 5 | const | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

######### Extra里有Using index condition，索引递归下推。 

 

为什么索引递归下推性能优化了呢？