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创建：

  1. 如果定义了primary key，MySQL则会使用该primary key作为clustered index；
  2. 如果没有定义primary key，MySQL怎会使用第一个字段不为空并且是uniqueindex作为clustered index；
  3. 如果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 Keycolumn也是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  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

参考资料

  1. 15.6.2 Indexes
  2. MySQL体系构架、存储引擎和索引结构
  3. MySQL Index
  4. MySQL Performance Boosting with Indexes and Explain
  5. 8.8.2 EXPLAIN Output Format

posted on 2021-03-26 12:22  AI应用技术  阅读(547)  评论(0)  编辑  收藏  举报

导航