MySQL索引的创建
2022/04/21
MySQL
> 视频教程：[尚硅谷_MySQL](http://www.atguigu.com/download_detail.shtml?v=349) # 索引的声明与使用 ## 索引类型 > 分别库 - 按功能逻辑： - 普通索引 - 唯一索引 - 主键索引 - 全文索引 - 空间索引 - 按物理实现方式： - 聚簇索引 - 非聚簇索引 - 按作用字段个数： - 单列索引 - 联合索引 > 不同的存储引擎支持的索引类型也不一样 - InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash 索引； - MyISAM ：支持 B-tree、Full-text 等索引，不支持 Hash 索引； - Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引； - NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引； - Archive ：不支持 B-tree、Hash、Full-text 等索引； ## 索引的创建 ### 创建表时添加索引 #### 基本语法 > 方式1：隐式的方式创建索引。在声明有**主键约束**、**唯一性约束**、**外键约束**的字段上，会自动的添加相关的索引 ``` mysql> CREATE TABLE dept ( dept_id INT PRIMARY KEY AUTO_INCREMENT, dept_name VARCHAR ( 20 ) ); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE emp ( -> emp_id INT PRIMARY KEY AUTO_INCREMENT, -> emp_name VARCHAR ( 20 ) UNIQUE, -> dept_id INT, -> CONSTRAINT emp_dept_id_fk FOREIGN KEY ( dept_id ) REFERENCES dept ( dept_id ) -> ); Query OK, 0 rows affected (0.02 sec) 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 | emp_id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | | emp | 0 | emp_name | 1 | emp_name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | | emp | 1 | emp_dept_id_fk | 1 | dept_id | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 3 rows in set (0.01 sec) ``` > 方式2：显式创建表时创建索引，基本语法格式如下： ```sql CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC] ``` - `UNIQUE` 、 `FULLTEXT` 和 `SPATIAL` 为可选参数，分别表示唯一索引、全文索引和空间索引 - `INDEX` 与 `KEY` 为同义词，两者的作用相同，用来指定创建索引 - `index_name` 指定索引的名称，为可选参数，如果不指定，那么 `MySQL` 默认 `col_name` 为索引名 - `col_name` 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择； - `length` 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度； - `ASC` 或 `DESC` 指定升序或者降序的索引值存储。 #### 普通索引 ``` mysql> CREATE TABLE user ( id INT, mobile INT, INDEX ( mobile ) ); Query OK, 0 rows affected (0.03 sec) mysql> SHOW INDEX FROM user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | user | 1 | mobile | 1 | mobile | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) ``` #### 唯一索引 > 显示 ``` mysql> CREATE TABLE employee ( id INT, job_num INT, UNIQUE INDEX ( job_num ) ); Query OK, 0 rows affected (0.02 sec) mysql> SHOW INDEX FROM employee; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | employee | 0 | job_num | 1 | job_num | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.01 sec) ``` > 隐式 ``` mysql> CREATE TABLE employee2 ( id INT, job_num INT UNIQUE ); Query OK, 0 rows affected (0.02 sec) mysql> SHOW INDEX FROM employee2; +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | employee2 | 0 | job_num | 1 | job_num | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) ``` #### 主键索引 > 显示 ``` mysql> CREATE TABLE student ( id INT, student_name VARCHAR ( 200 ), PRIMARY KEY ( id ) ); Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> SHOW INDEX FROM student; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | student | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.01 sec) ``` > 隐式 ``` mysql> CREATE TABLE student2 ( id INT PRIMARY KEY, student_name VARCHAR ( 100 ) ); Query OK, 0 rows affected (0.01 sec) mysql> SHOW INDEX FROM student2; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | student2 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) ``` > 删除约束 ``` mysql> ALTER TABLE student2 DROP PRIMARY KEY; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW INDEX FROM student2; Empty set (0.01 sec) mysql> SHOW CREATE TABLE student2; +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | student2 | CREATE TABLE `student2` ( `id` int NOT NULL, `student_name` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` #### 单列索引 作用在一个列上的索引均称为单列索引 #### 联合索引 在表中的 `name` 和 `age` 字段上建立组合索引 ``` mysql> CREATE TABLE employee ( id INT NOT NULL, `name` CHAR ( 30 ) NOT NULL, age INT NOT NULL, INDEX multi_idx ( `name`, age ) ); Query OK, 0 rows affected (0.02 sec) mysql> SHOW INDEX FROM employee; +----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | employee | 1 | multi_idx | 1 | name | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | | employee | 1 | multi_idx | 2 | age | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | +----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 2 rows in set (0.01 sec) ``` #### 全文索引 ``` mysql> CREATE TABLE articles ( id INT, title VARCHAR ( 200 ), body TEXT, FULLTEXT INDEX ( body ) ); Query OK, 0 rows affected (0.09 sec) mysql> SHOW INDEX FROM articles; +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | articles | 1 | body | 1 | body | NULL | 0 | NULL | NULL | YES | FULLTEXT | | | YES | NULL | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) ``` 不同于like方式的的查询： ```sql SELECT * FROM articles WHERE title LIKE '%查询字符串%'; ``` 全文索引用match+against方式查询： ```sql SELECT * FROM articles WHERE MATCH(body) AGAINST ('查询字符串'); ``` > 全文搜索建议使用专业的搜索引擎，例如 `Elasticsearch` #### 空间索引 注：仅了解 ``` mysql> CREATE TABLE test_geo ( geo GEOMETRY NOT NULL, SPATIAL INDEX spa_idx_geo ( geo ) ); Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> SHOW INDEX FROM test_geo; +----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | test_geo | 1 | spa_idx_geo | 1 | geo | A | 0 | 32 | NULL | | SPATIAL | | | YES | NULL | +----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.01 sec) ``` ### 在已经存在的表上创建索引 #### 使用 ALTER TABLE > 语法： ``` ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC] ``` > 示例 ``` mysql> CREATE TABLE book ( id INT, `name` VARCHAR ( 100 ) ); Query OK, 0 rows affected (0.01 sec) mysql> ALTER TABLE book ADD INDEX idx_cmt ( `name` ); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW INDEX FROM book; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | book | 1 | idx_cmt | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) ``` #### 使用 CREATE INDEX > 在 `MySQL` 中，`CREATE INDEX 被映射到一个 `ALTER TABLE` 语句上。语法： ``` CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC] ``` > 示例 ``` mysql> CREATE TABLE book2 ( id INT, `name` VARCHAR ( 100 ) ); Query OK, 0 rows affected (0.01 sec) mysql> CREATE INDEX idx_cmt ON book2 ( `name` ); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW INDEX FROM book2; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | book2 | 1 | idx_cmt | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.01 sec) ``` ## 索引的删除 ### 使用 ALTER TABLE ```sql ALTER TABLE table_name DROP INDEX index_name; ``` ### 使用 DROP INDEX ```sql DROP INDEX index_name ON table_name; ``` # MySQL 8.0 新特性 ## 降序索引 在索引的字段名称后面添加 `DESC` 关键字，默认情况下是 `ASC` ，一般用于倒序查找，如按创建时间倒序。 ``` mysql> CREATE TABLE user(id INT,create_time datetime,INDEX (create_time DESC)); Query OK, 0 rows affected (0.01 sec) mysql> SHOW INDEX FROM user; +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | user | 1 | create_time | 1 | create_time | D | 0 | NULL | NULL | YES | BTREE | | | YES | NULL | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.01 sec) ``` ## 隐藏索引 从 `MySQL 8.x` 开始支持**隐藏索引（invisible indexes）**，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引，确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。 > 创建表时直接创建 ``` mysql> CREATE TABLE `user` ( id INT, `name` VARCHAR ( 100 ), INDEX idx_cmt ( `name` ) invisible ); Query OK, 0 rows affected (0.01 sec) mysql> SHOW INDEX FROM `user`; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | user | 1 | idx_cmt | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | NO | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec) ``` > 在已经存在的表上创建 ```sql CREATE INDEX indexname ON tablename(propname[(length)]) INVISIBLE; ``` > 通过ALTER TABLE语句创建 ```sql ALTER TABLE tablename ADD INDEX indexname (propname [(length)]) INVISIBLE; ``` > 切换索引可见状态 ``` ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引 ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引 ``` # 索引的设计原则 ## 适合创建索引的11种情况 1. 字段的数值有唯一性的限制<br>业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）<br>说明：不要以为唯一索引影响了 `insert` 速度，这个速度损耗可以忽略，但提高查找速度是明显的。 2. 频繁作为 `WHERE` 查询条件的字段<br>某个字段在 `SELECT` 语句的 `WHERE` 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。 3. 经常 `GROUP BY` 和 `ORDER BY` 的列<br>索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 `GROUP BY` 对数据进行分组查询，或者使用 `ORDER BY` 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立联合索引 。 4. `UPDATE` 、 `DELETE` 的 `WHERE` 条件列<br>对数据按照某个条件进行查询后再进行 `UPDATE` 或 `DELETE` 的操作，如果对 `WHERE` 字段创建了索引，就能大幅提升效率。原理是因为需要先根据 `WHERE` 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。 5. `DISTINCT` 字段需要创建索引<br>有时候需要对某个字段进行去重，使用 `DISTINCT` ，那么对这个字段创建索引，也会提升查询效率。 6. 多表 `JOIN` 连接操作时，创建索引注意事项 - 连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。 - 对 `WHERE` 条件创建索引 ，因为 `WHERE` 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 `WHERE` 条件过滤是非常可怕的。 - 对用于连接的字段创建索引 ，并且该字段在多张表中的类型必须一致 。 7. 使用列的类型小的创建索引 8. 使用字符串前缀创建索引<br>拓展：Alibaba《Java开发手册》<br>【 强制 】在 `varchar` 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。<br>说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90% 以上 ，可以使用 `count(distinct left(列名, 索引长度))/count(*)` 的区分度来确定。 9. 区分度高(散列性高)的列适合作为索引 10. 使用最频繁的列放到联合索引的左侧<br>这样也可以较少的建立一些索引。同时，由于"最左前缀原则"，可以增加联合索引的使用率。 11. 在多个字段都要创建索引的情况下，联合索引优于单值索引 ## 不适合创建索引的7种情况 1. 在 `where` 中使用不到的字段，不要设置索引 2. 数据量小的表最好不要使用索引 3. 有大量重复数据的列上不要建立索引 4. 避免对经常更新的表创建过多的索引 5. 不建议用无序的值作为索引<br>例如 `UUID` 、 `MD5` 、 `HASH` 、无序长字符串等。 6. 删除不再使用或者很少使用的索引 7. 不要定义冗余或重复的索引
3