索引的创建与设计原则
1 索引的声明与使用
1.1 索引的分类
MySQL索引包括普通索引
、唯一性索引
、全文索引
、单列索引
、多列索引
和空间索引
按照逻辑结构划分,主要有四种:普通索引
、唯一性索引
、主键索引
和全文索引
按照物理实现方式划分,主要有两种:聚簇索引
和非聚簇索引
按照作用字段:单列索引
和多列索引
(联合索引
)
多列索引即联合索引,当多个字段构成联合主键的时候则为聚簇索引,否则为非聚簇索引
普通索引
在创建普通索引的时候,不需要任何的限制条件,只是用于提高查询速度
这类索引可以创建在任何数据类型
中,其值是否非空和唯一,取决于字段本身的完整性约束
创建索引之后,可以直接通过索引进行查询。
唯一性索引
使用UNIQUE参数
可以设置索引为唯一索引
在创建唯一索引的时候,会限制索引的值必须是唯一的,但是允许有空值
在一张表中可以有多个唯一索引。
UNIQUE会自动创建唯一索引,并且可以通过删除字段的唯一索引来删除唯一性
主键索引
主键索引是一种特殊的唯一索引,也就是NOT NULL + UNIQUE
,也即是聚簇索引
一张表中只能有一个主键索引
这是由主键索引的物理实现方式(聚簇索引)决定的:数据存储在文件中只能按照一种顺序(主键大小)进行存储
单列索引
即在单个字段上创建的索引
单列索引可以是普通索引,也可以是唯一性索引还可以是全文索引,只需要保证对应一个字段即可
一张表可以有多个单列索引
多列索引
即在多个字段组合上创建一个索引,该索引指向创建时对应的多个字段,可以通过这几个字段进行查询
但是在查询的时候,只有使用了第一个字段时索引才会被使用(和上面一样,这是由主键索引的物理实现方式决定的:数据存储在文件中只能按照一种顺序(一个列的大小)进行存储),即最左前缀原则
全文索引
空间索引
1.2 创建索引
首先创建数据库和表
CREATE DATABASE dbtest2 CHARACTER SET 'utf8'
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)
)
① 创建表的时候添加索引
隐式创建索引:在create table的时候,会对主键约束
、唯一性约束
和外键约束
的字段上,自动地添加相关的索引
显示创建普通索引:语法如下
CREATE TABLE tableName [colName dataType ...]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [indexName] (colName [length]) [ASC | DESC]
UNIQUE | FULLTEXT | SPATIAL:可选参数,即唯一索引、全文索引和空间索引
index和key作用相同,用来指定创建索引,一般情况下使用key
indexName:索引名,不指定的话默认为列名
colName:列名
length:可选参数,表示索引的长度,只有列为字符类型字段才需要指定
ASC、DESC:指定索引的排序顺序
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)
)
通过命令查看索引:
方式一:show create table tableName
mysql> show create table book\G
*************************** 1. row ***************************
Table: book
Create Table: CREATE TABLE `book` (
`book_id` int DEFAULT NULL,
`book_name` varchar(100) DEFAULT NULL,
`AUTHORS` varchar(100) DEFAULT NULL,
`info` varchar(100) DEFAULT NULL,
`COMMENT` varchar(100) DEFAULT NULL,
`year_publication` year DEFAULT NULL,
KEY `idx_bname` (`book_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.01 sec)
方式二:show index from tableName;
mysql> show index from book\G
*************************** 1. row ***************************
Table: book
Non_unique: 1 // 表示不是唯一
Key_name: idx_bname
Seq_in_index: 1
Column_name: book_name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.09 sec)
EXPLAIN性能分析工具
EXPLAIN SELECT * FROM book WHERE book_name = 'mysql'
可以看到查询语句possible_keys可能使用到的索引包括了idx_bname
创建表的时候添加唯一索引
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)
);
INSERT INTO book1 VALUES (1, 'mysql', NULL, NULL, 'mysql高级', NULL)
INSERT INTO book1 VALUES (2, 'mysql', NULL, NULL, 'mysql高级', NULL)
-- [Err] 1062 - Duplicate entry 'mysql高级' for key 'book1.uk_idx_cmt'
INSERT INTO book1 VALUES (2, 'mysql', NULL, NULL, NULL, NULL)
INSERT INTO book1 VALUES (2, 'mysql', NULL, NULL, NULL, NULL)
声明有唯一索引的字段,在添加数据的时候要保证数据的唯一性,但是可以多次添加NULL值,等效于添加唯一性约束
主键索引:通过主键约束的方式定义,即隐式的方式
INSERT INTO book1 VALUES (2, 'mysql', NULL, NULL, NULL, NULL)
mysql> show index from book2\G
*************************** 1. row ***************************
Table: book2
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: book_id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (0.04 sec)
同样删除索引也只能通过删除主键约束的方式:
ALTER TABLE book2
DROP PRIMARY KEY
添加自增的主键不能删除
创建单列索引
前面的都是,这里就不再写了
创建联合索引
CREATE TABLE book3 (
book_id INT,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info varchar(100),
COMMENT VARCHAR(100),
year_publication YEAR,
INDEX multi_bid_bname_binf(book_id, book_name, info)
)
SHOW INDEX FROM book3;
Seq_in_index表示索引的排序顺序,该联合索引会按照id、name、info的顺序进行排序,并且查询的时候要求根据最左前缀原则
先查询id才能使用索引
EXPLAIN SELECT * FROM book3 WHERE book_id = 1 AND book_name = 'mysql'
-- possible keys : multi_bid_bname_binf
EXPLAIN SELECT * FROM book3 WHERE book_name = 'mysql' = 1 AND book_id
-- possible keys : multi_bid_bname_binf
EXPLAIN SELECT * FROM book3 WHERE book_name = 'mysql'
-- possible keys : NULL
最左前缀原则是由数据库表真实的物理存储结构决定的,底层使用的B+树按照联合主键的字段顺序大小进行排序,因此使用别的字段值无法进行检索
全文索引
全文检索使用match + against的方式查询:
select * from test4 where Match(name, info) against('检索字符串')
使用全文检索性能相较于like快很多,但是精度差(可能会少数据)
空间索引
② 创建表的时候添加索引
alter table
ALTER TABLE tableName ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX indexName(colName ...)
create index on
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX indexName ON tableName(colName ...)
1.3 删除索引
使用场景
:当表中存在较多索引并且进行大量增删改操作的时候,可以先进行删除索引的操作,以减少增删改过程对于索引的维护。
ALTER TABLE DROP INDEX对应ALTER TABLE ADD INDEX
ALTER TABLE book3
DROP INDEX multi_bid_bname_binf
DROP INDEX ON 对应 CREATE INDEX ON
DROP INDEX multi_bid_bname_binf ON book3
对于使用
AUTO_INCREMENT
约束的字段不能删除其主键或者索引,前面页提到过,因为AUTO_INCREMENT
约束要求字段为主键或者UNIQUE
当删除、修改字段的时候,会自动对索引进行维护:
SHOW INDEX FROM book3;
book3 1 multi_bid_bname_binf 1 book_id A 0 YES BTREE YES
book3 1 multi_bid_bname_binf 2 book_name A 0 YES BTREE YES
book3 1 multi_bid_bname_binf 3 info A 0 YES BTREE YES
ALTER TABLE book3
DROP COLUMN book_name;
SHOW INDEX FROM book3;
book3 1 multi_bid_bname_binf 1 book_id A 0 YES BTREE YES
book3 1 multi_bid_bname_binf 2 info A 0 YES BTREE YES
2 MySQL 8.0 索引新特性
2.1 支持降序索引
降序索引会以降序存储键值,虽然在语法上mysql4.0就支持索引降序了,但是实际上DESC是被忽略的(使用反向扫描来实现降序),直到mysql 8.0 才开始真正支持降序索引(仅限于innoDB存储引擎)
使用降序索引意义重大,如一个查询,需要对多个列进行排序,而且顺序要求不一致,那么降序索引就能够避免数据库使用额外的文件排序
来实现反向扫描,从而提高性能。
举个栗子,首先在5.0 和 8.0 都创建一张表:
CREATE TABLE ts1 (
a int,
b int,
INDEX idx_a_b(a, b DESC)
)
然后编写一个存储过程添加一些数据:
DELIMITER //
CREATE PROCEDURE ts_insert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 800
DO
INSERT INTO ts1 SELECT RAND() * 80000, RAND() * 80000;
SET i = i + 1;
END WHILE;
COMMIT;
END //
DELIMITER;
此时按照a升序、b升序检索,查看执行计划:
EXPLAIN SELECT * FROM ts1 ORDER BY a, b
1 SIMPLE ts1 index idx_a_b 10 799 100 Using index; Using filesort
可以看到使用了索引和文件排序
此时按照a升序、b降序检索,查看执行计划:
EXPLAIN SELECT * FROM ts1 ORDER BY a, b DESC
1 SIMPLE ts1 index idx_a_b 10 799 100 Using index
可以看到只使用了索引
而5.0版本的mysql则是也会使用文件排序,因为实际存储时仍按照b升序排列的
这里就不演示了
2.2 支持隐藏索引
使用场景
:在MySQL5.7及之前,只能通过显示的方式删除索引,此时如果发现索引删除后出现错误,则又需要再添加创建索引,如果此时表中已经存储了大量的数据或者表本身比较大,则就会消耗过多的资源,操作成本非常高。或者想要验证索引删除后的性能影响。
功能
:Mysql 8.0后支持的隐藏索引,只需要将待删除的索引设置为隐藏索引
,查询优化器
就不会使用这个索引(哪怕使用force index
也不会),确认删除索引不会出错后,再真正删除索引。这种通过设置隐藏索引,再删除索引的方式就是软删除。
主键不能设置为隐藏索引,当表中没有显示的主键,表中的第一个非空索引就会成为隐式主键,也不能设置隐藏索引
创建表的时候直接创建隐藏索引
CREATE TABLE book4 (
book_id INT,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info varchar(100),
COMMENT VARCHAR(100),
year_publication YEAR,
INDEX multi_bid_bname_binf(book_id, book_name, info) INVISIBLE
)
SHOW INDEX FROM book4
这时候尝试使用索引进行查询:
EXPLAIN SELECT * FROM book4 WHERE book_id = 1
创建表以后隐藏索引
ALTER TABLE book4
ADD UNIQUE INDEX uq_idx_bid(book_id)
ALTER TABLE book4
ADD UNIQUE INDEX uq_idx_bname(book_name) INVISIBLE
修改索引可见性
ALTER TABLE book4
ALTER INDEX uq_idx_bname VISIBLE;
注意
索引被隐藏只是查询优化器不可见,但是仍然和正常索引一样是实时更新的,因此索引长时间被隐藏应该删除,避免影像增删改的性能