MySQL InnoDB 索引(Index)
MySQL使用索引加快数据的查询,没有索引的话,在查找数据的时候,MySQL会全表扫描。
数据越多,扫描的速度就会越慢。
InnoDB
存储引擎是MySQL默认的存储引擎,本文整理的是InnoDB
的Index
。
索引概念
索引是一个数据结构,一般RDBMS是BTree
,通过保存排序后的key value
数据字段,以便用来快速查询数据。
索引分类:聚簇索引(Clustered Index
或 Primary Index
)和 二级索引(Secondary Index
)
clustered index
clustered index
其实就是根据primary key排过序的数据表,因为clustered index本身和数据一起存储在同一个表的。
clustered index
使数据行根据创建clustered index所用的字段排序存储;因此,一个InnoDB数据表只能有一个clustered index。
clustered index创建:
- 如果定义了
primary key
,MySQL则会使用该primary key作为clustered index; - 如果没有定义
primary key
,MySQL怎会使用第一个字段不为空并且是unique
index作为clustered index; - 如果db table既没有primary key,又没有满足条件的unique key,MySQL则会创建一个名为
GEN_CLUST_INDEX
的clustered index。
所以,每个InnoDB数据表都会有clustered index。
Secondary index
非clustered index
都是 secondary index
。
在InnoDB数据表中,secondary index包含的数据记录是: primary key + 创建secondary index的列值
; MySQL会使用primary key回溯原表(也是clustered index)查询具体的数据行。
MySQL Prefix Index
当在column上创建secondary index
时,MySQL会把此column数据单独存在BTree
上。如果column是string
类型的,index就会消耗很多硬盘空间,并可能会导致插入数据变很慢。
为了解决此类问题,MySQL可以创建Prefix Index
,
CREATE TABLE table_name(
column_list,
INDEX(column_name(length))
);
或
CREATE INDEX index_name ON table_name(column_name(length));
- 如果
string
类型是CHAR, VARCHAR, 和 TEXT
,则length
是字符的个数。 - 如果
string
类型是BINARY, VARBINARY, 和 BLOB
,则length
是字节的个数。 - 对于
CHAR, VARCHAR, BINARY, 和 VARBINARY
类型的列,MySQL是可选创建prefix key
的。 - 如果想给
BLOB
和TEXT
类型的列创建index,则必须创建prefix index
。
如何决定创建prefix index
的length
呢?
- 一个合理的长度,并让唯一性最大化; 即选了一个合理的长度后,所有行此长度的字符串前缀基本都是唯一的、不重复的。
MySQL Invisible Index
MySQL 8.x才有的
CREATE INDEX index_name ON table_name( c1, c2, ...) INVISIBLE;
ALTER TABLE table_name ALTER INDEX index_name [VISIBLE | INVISIBLE];
- 默认情况indexs是可见的visible,MySQL提供关键字
VISIBLE
和INVISIBLE
来设置index的可见性; - MySQL primary key是不可以为
invisible
的; - MySQL查询优化器(
query optimizer
)不使用invisible的index;那这个invisible有啥用呢?- 有些时候,我们可以通过设置index visible或invisible来看看这个index对查询性能有没有作用。
注意:
Query Optimizer是否使用invisible indexes是受 optimizer_switch
系统变量的 use_invisible_indexes
参数控制。
- use_invisible_indexes =
off
(默认值),则MySQL Server忽略invisible的indexes; - use_invisible_indexes =
on
,则MySQL Server会把invisible的indexes参与到执行计划中;
MySQL Descending Index
DROP TABLE IF EXISTS asc_desc_index_test;
create table asc_desc_index_test
(
id int not null auto_increment,
test_name varchar(50),
test_age int,
primary key(id),
index idx_name_asc_age_desc(test_name ASC, test_age DESC)
);
MySQL 5.7
或之前版本,DESC
关键字会被忽略掉。
mysql> show create table asc_desc_index_test \G;
*************************** 1. row ***************************
Table: asc_desc_index_test
Create Table: CREATE TABLE `asc_desc_index_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test_name` varchar(50) DEFAULT NULL,
`test_age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_asc_age_desc` (`test_name`,`test_age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
MySQL 8.x
版本:
mysql> show create table asc_desc_index_test \G;
*************************** 1. row ***************************
Table: asc_desc_index_test
Create Table: CREATE TABLE `asc_desc_index_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`test_name` varchar(50) DEFAULT NULL,
`test_age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_asc_age_desc` (`test_name`,`test_age` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
MySQL composite index
MySQL composite index
由多个columns创建的index。
MySQL Server在使用组合index的时候,遵循leftmost prefix of the index
。
语法:
CREATE TABLE table_name (
c1 data_type PRIMARY KEY,
c2 data_type,
c3 data_type,
c4 data_type,
INDEX index_name (c2,c3,c4)
);
或
CREATE INDEX index_name ON table_name(c2,c3,c4);
示例:
create index idx_first_last_name on customer(first_name, last_name);
下面用是idx_first_last_name
有作用的的查询:
explain select * from customer where first_name = 'a' ;
explain select * from customer where first_name like 'a' ;
explain select * from customer where first_name like 'a%' ;
explain select * from customer where first_name = 'a' and last_name = 'b';
explain select * from customer where first_name = 'a' and last_name in ('b', 'c');
explain select * from customer where last_name = 'b' and first_name = 'a' ;
没作用的查询:
explain select * from customer where first_name like '%a' ;
explain select * from customer where first_name like '%a%' ;
explain select * from customer where first_name = 'a' or last_name = 'b';
explain select * from customer where last_name = 'b';
Unique index
Primary Key
column也是unique的,但是一个表只能有一个primary key。
如果想设置多个column值的唯一性,可以使用unique index
:
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);
另一个方法是使用 UNIQUE constraint
,创建了unique constraint后,MySQL会在后台也创建Unique Index
。
CREATE TABLE table_name(
...
UNIQUE KEY(index_column_,index_column_2,...)
);
或
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);
- 这里
UNIQUE KEY
,也可以换成UNIQUE INDEX
,它们是同义的。
注意
- MySQL中,如果column是unique的,column里面可以有多个NULL值;MySQL认为NULL都是唯一的,这是MySQL专门这么设计的。
创建索引
CREATE TABLE t(
c1 INT PRIMARY KEY,
c2 INT NOT NULL,
c3 INT NOT NULL,
c4 VARCHAR(10),
INDEX (c2,c3)
);
CREATE INDEX index_name ON table_name (column_list)
如果不指定索引的类型,MySQL默认创建BTree Index
。
查看表的索引
SELECT
index_name,
is_visible
FROM
information_schema.statistics
WHERE
table_schema = 'mysql_practice' AND table_name = 'book';
或
mysql> show indexes from book\G;
或
mysql> show keys in book\G;
*************************** 1. row ***************************
Table: book
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 178
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: book
Non_unique: 1
Key_name: category_id
Seq_in_index: 1
Column_name: category_id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 3. row ***************************
上面显示表indexes的sql语句中,
indexs
和keys
是同义的from
和in
是同义的
Show Indexes返回字段的信息:
Table
: 所在表名Non_unique
:index是否可以包含重复记录,1是可以重复,0是不可以。Key_name
:index的名字,注意 primary key 的index名字是PRIMARY
Seq_in_index
: 如果index包含多个column时,index中column的sequence number,从1
开始;如果index只包含一个column,那么这个值总是1;
*************************** 4. row ***************************
Table: book
Non_unique: 1
Key_name: idx_book_no_name --index名字
Seq_in_index: 1
Column_name: no --index包含的第一个column
Collation: A
Cardinality: 178
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 5. row ***************************
Table: book
Non_unique: 1
Key_name: idx_book_no_name --index名字和上面一样
Seq_in_index: 2
Column_name: name --index包含的第二个column
Collation: A
Cardinality: 178
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
column_name
:index包含的列名;Collation
:表名column是如何排序的,A
表示升序,B
表示降序,NULL
表示没有排序;Cardinality
:index里不重复记录的预估值;值越大,index就越大概率被用到查询里;Sub_part
:index的前缀,如果整个字段都是index,则值为NULL
;否则,显示column有多少个字符被index。Packed
:Null
:表示column是否可空;yes表示可空,空白表示不可空;Index_type
:idex的方法,如BTREE, HASH, RTREE, or FULLTEXT
;Comment
: index的备注;index_comment
:Visible
:index是否可见,即是否会被用到query中;Expression
:
删除表索引
DROP INDEX index_name ON table_name [algorithm_option | lock_option];
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
如果是drop clustered index:
DROP INDEX `PRIMARY` ON table_name; -- PRIMARY须用引号标识符,因为PRIMARY是关键字。
ALGORITHM
- DEFAULT
- INPLACE
- COPY
- INSTANT
LOCK
- DEFAULT
- NONE
- SHARED
- EXCLUSIVE
示例:
DROP INDEX idx_book_no ON book
ALGORITHM = INPLACE
LOCK = DEFAULT;
MySQL FORCE INDEX 和 USE INDEX
MySQL Use index
SELECT select_list
FROM table_name USE INDEX(index_list)
WHERE condition;
- 注意,实际query执行的时候可能使用指定的index list
MySQL force index
SELECT *
FROM table_name
FORCE INDEX (index_list)
WHERE condition;
- 注意,实际query执行的时候一定要使用指定的index list
索引效果
使用explain
关键字查看sql query的执行情况,索引的使用情况。
给book表no字段加索引前:
mysql> EXPLAIN SELECT * FROM mysql_practice.book WHERE no = 'f457948c80ac11';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 178 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
给book表no字段加了索引后:
mysql> EXPLAIN SELECT * FROM mysql_practice.book WHERE no = 'f457948c80ac11';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | book | NULL | ref | idx_book_no | idx_book_no | 202 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+