索引创建

主键和具有unique约束的字段自动会添加索引。
根据主键查询效率较高，尽量根据主键检索。

  1. create index inx_emp_sal on emp(sal);

查看索引

mysql> show index from emp;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY     |            1 | EMPNO       | A         |          13 | NULL     | NULL   |      | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | inx_emp_sal |            1 | SAL         | A         |          12 | NULL     | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.08 sec)

查看sql语句的执行计划—explain

mysql> explain select ename,sal from emp where sal = 5000;
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | inx_emp_sal   | inx_emp_sal | 9       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set (0.03 sec)