• Mysql高级篇学习总结9：创建索引、删除索引、降序索引、隐藏索引

    Mysql高级篇学习总结9：创建索引、删除索引、降序索引、隐藏索引

    1、索引的声明和使用

    1.1 索引的分类

    mysql的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引、空间索引。

    • 按照功能逻辑：索引主要有４种，分别是普通索引、唯一索引、主键索引、全文索引；
    • 按照物理实现方式：索引可以分为２种，聚簇索引和非聚簇索引；
    • 按照作用字段个数：分成单列索引和联合索引；

    1.2 创建索引

    Mysql支持多种方法在单个或多个列上创建索引：
    １）在创建表的定义语句CREATE TABLE中指定索引列；
    ２）在已存在的表上，使用ALTER TABLE语句创建索引，或者使用CREATE INDEX语句在已存在的表上添加索引；

    1.2.1 创建表的时候创建索引

    1.2.1.1 隐式的方式创建索引

    在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引

    CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);

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)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    可以通过命令查看索引：
    方式１：SHOW INDEX FROM 表名;

    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 |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp   |          0 | PRIMARY        |            1 | emp_id      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| emp   |          0 | emp_name       |            1 | emp_name    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| emp   |          1 | emp_dept_id_fk |            1 | dept_id     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    可以看到主键emp_id的索引名是PRIMARY，emp_name的索引名是emp_name，dept_id的索引名是emp_dept_id_fk。

    方式2：SHOW CREATE TABLE 表名;

    mysql> SHOW CREATE TABLE emp;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `emp_id` int(11) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(20) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`emp_id`),
  UNIQUE KEY `emp_name` (`emp_name`),
  KEY `emp_dept_id_fk` (`dept_id`),
  CONSTRAINT `emp_dept_id_fk` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    1.2.1.2 显式的方式创建索引

    １）创建普通的索引

    mysql> CREATE TABLE book(
    -> book_id INT ,
    -> book_name VARCHAR(100),
    -> AUTHORS VARCHAR(100),
    -> info VARCHAR(100) ,
    -> COMMENT VARCHAR(100),
    -> year_publication YEAR,
    -> #声明索引
    -> INDEX idx_bname(book_name)
    -> );
Query OK, 0 rows affected (0.21 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    还是可以通过show index from book来查看索引：

    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 |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          1 | idx_bname |            1 | book_name   | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    除此之外，还可以通过性能分析工具：EXPLAIN来查看是否使用到了索引：

    mysql> EXPLAIN SELECT * FROM book WHERE book_name = 'mysql';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | book  | NULL       | ref  | idx_bname     | idx_bname | 103     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    比如通过book_name进行检索，可以看到使用到的索引（key）是idx_bname，这就是刚刚上面建立的普通索引。

    ２）创建唯一索引
    声明有唯一索引的字段，在添加数据时，要保证唯一性，但是可以添加null
    比如，下面以注释comment作为唯一索引

    CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
UNIQUE INDEX uk_idx_cmt(COMMENT)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    mysql> SHOW INDEX FROM book1;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book1 |          0 | uk_idx_cmt |            1 | COMMENT     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    ３）主键索引
    通过定义主键约束的方式定义主键索引

    CREATE TABLE book2(
book_id INT PRIMARY KEY ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    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 |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book2 |          0 | PRIMARY  |            1 | book_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    ４）创建单列索引

    CREATE TABLE book3(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
UNIQUE INDEX idx_bname(book_name)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    ５）创建联合索引

    CREATE TABLE book4(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
INDEX mul_bid_bname_info(book_id,book_name,info)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    mysql> SHOW INDEX FROM book4;
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book4 |          1 | mul_bid_bname_info |            1 | book_id     | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| book4 |          1 | mul_bid_bname_info |            2 | book_name   | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| book4 |          1 | mul_bid_bname_info |            3 | info        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    注意，使用联合索引要遵循最左前缀原则，也就是使用索引的话至少要包含联合索引的最左边的索引才能检索。

    比如下面的２条检索语句，在第２条语句中，由于直接以book_name进行检索，而联合索引的首先是以book_id进行排序检索的，它这里找不到，所以就没有成功用到我们刚刚建的联合索引了。

    mysql> EXPLAIN SELECT * FROM book4 WHERE book_id = 1001 AND book_name = 'mysql';
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys      | key                | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | book4 | NULL       | ref  | mul_bid_bname_info | mul_bid_bname_info | 108     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM book4 WHERE book_name = 'mysql';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | book4 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    ６）创建全文索引

    CREATE TABLE test4(
id INT NOT NULL,
NAME CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX futxt_idx_info(info(50))
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    1.2.2 在已存在的表上创建索引

    方式１：表已经创建好了，可以使用 **ALTER TABLE … ADD …**来添加索引。

    CREATE TABLE book5(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    在创建了该表后，给该表添加索引：

    ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);
ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);
ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info);

    • 1
    • 2
    • 3

    方式２：表已经创建好了，还可以使用 **CREATE INDEX … ON …**来添加索引。

    CREATE TABLE book6(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    CREATE INDEX idx_cmt ON book6(COMMENT);
CREATE UNIQUE INDEX  uk_idx_bname ON book6(book_name);
CREATE INDEX mul_bid_bname_info ON book6(book_id,book_name,info);

    • 1
    • 2
    • 3

    1.3 删除索引

    方式1：ALTER TABLE … DROP INDEX …

    ALTER TABLE book5 DROP INDEX idx_cmt;

    • 1

    方式2：DROP INDEX … ON …

    DROP INDEX uk_idx_bname ON book5;

    • 1

    注意：删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

    2、Mysql8.0索引新特性

    2.1 支持降序索引

    Mysql在8.0版本之前创建的是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。

    比如创建表ts1：

    CREATE TABLE ts1(a INT,b INT,INDEX idx_a_b(a ASC,b DESC));

    • 1

    如果是经常使用b的降序进行检索，那么8.0版本可以成功使用索引，大大降低数据库检索时间。而8.0之前的版本，由于没有这个降序索引的特性，所以检索时间更长。

    EXPLAIN SELECT * FROM ts1 ORDER BY a,b DESC LIMIT 5;

    • 1

    2.2 隐藏索引

    有的时候，我们不确定索引是否有效，或者想验证下删除索引是否会造成什么影响，可以不直接删除索引，可以先隐藏索引。

    从Mysql8.0开始支持隐藏索引（invisible indexes），只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引。等确认将索引设置为隐藏索引后，系统不会造成任何影响，就可以彻底删除该索引。

    隐藏索引的方式，也分为创建表时，和创建表后：
    方式１：创建表时，隐藏索引

    CREATE TABLE book7(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#创建不可见的索引
INDEX idx_cmt(COMMENT) invisible
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    方式２：创建表以后，隐藏索引

    ALTER TABLE book7
ADD UNIQUE INDEX uk_idx_bname(book_name) invisible;

    • 1
    • 2

    还可以修改索引的可见性：
    比如将索引idx_cmt由不可见改为可见：

    ALTER TABLE book7 ALTER INDEX idx_cmt visible; 

    • 1

    然后再将索引idx_cmt由可见改为不可见：

    ALTER TABLE book7 ALTER INDEX idx_cmt invisible; 

    • 1
  • 相关阅读:
    leetcode264 丑数 II
    设计模式——11. 享元模式
    139 单词拆分 140 单词拆分II
    【慕课C#学习笔记】第二章: C#基础语法
    CDN工作原理
    Spring5应用之高级注解开发
    图像数据增强2_标注框同时修改（VOC、YOLO）
    目标识别项目实战：基于Yolov7-LPRNet的动态车牌目标识别算法模型
    L2-020 功夫传人(Python3)
    AIGC之Stable Diffusion
  • 原文地址：https://blog.csdn.net/xueping_wu/article/details/125583631