1、覆蓋索引

盡量使用覆蓋索引（查詢使用了索引，并且需要返回的列，在該索引中已經全部能夠找到），減少select * 。

1.1、查看索引

mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |          22 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro         |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.00 sec)mysql>

1.2、刪除單列索引 idx_user_pro

mysql> drop index idx_user_pro on tb_user;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index from tb_user;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user |          0 | PRIMARY              |            1 | id          | A         |          24 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            1 | profession  | A         |          16 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            2 | age         | A         |          22 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| tb_user |          1 | idx_user_pro_age_sta |            3 | status      | A         |          24 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)mysql>

1.3、查詢 profession=‘軟件工程’ and age=31 and status=‘0’

mysql> select * from tb_user where profession='軟件工程' and age=31 and status='0';
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
| id | name | phone       | email             | profession | age  | gender | status | createtime          |
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
| 16 | 妲己 | 17799990015 | 2783238293@qq.com | 軟件工程   |   31 | 2      | 0      | 2001-01-30 00:00:00 |
+----+------+-------------+-------------------+------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)mysql>

1.4、執行計劃 profession=‘軟件工程’ and age=31 and status=‘0’

mysql> explain select * from tb_user where profession='軟件工程' and age=31 and status='0';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 54      | const,const,const |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)mysql>

1.5、執行計劃 select id,profession,age,status

mysql> explain select id,profession,age,status from tb_user where profession='軟件工程' and age=31 and status='0';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+--------------------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra                    |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+--------------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 54      | const,const,const |    1 |   100.00 | Using where; Using index |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)mysql>

using where;using index：查找使用了索引，但是需要的數據都在索引列中能找到，所以不需要回表查詢數據

1.6、執行計劃 select id,profession,age,status,name

mysql> explain select id,profession,age,status,name from tb_user where profession='軟件工程' and age=31 and status='0';
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
|  1 | SIMPLE      | tb_user | NULL       | ref  | idx_user_pro_age_sta | idx_user_pro_age_sta | 54      | const,const,const |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------+----------------------+---------+-------------------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)mysql>

using index condition：查找使用了索引，但是需要回表查詢數據

2、思考題

一張表，有四個字段（id，username，password，status），由于數據量大，需要對一下SQL語句進行優化，該如何進行才是最優方案：
select id,username,password from tb_user where username=‘csdn’

select id,username,password from tb_user where username='csdn';

答案：針對于 username，password 建立聯合索引，sql 為：create index idx_user_name_pass on tb_user(username,password);這樣就可以避免上述的SQL語句，在查詢的過程中，出現回表查詢。

create index idx_user_name_pass on tb_user(username,password);