【MySQL基础】06：索引（Index）

最新推荐文章于 2021-02-07 20:43:52 发布

zyplanke

最新推荐文章于 2021-02-07 20:43:52 发布

阅读量187

点赞数

分类专栏： MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/zyplanke/article/details/103002867

版权

MySQL 专栏收录该内容

26 篇文章 3 订阅

订阅专栏

本教程以MySQL8为主版本（内容兼顾MySQL5.7）。

所有MySQL文章的目录为：总目录 https://blog.csdn.net/zyplanke/article/details/102968014

在数据库中，如果Table是一本书，那么索引Index就是这本书的目录。目录存在的价值就是能快速查找定位，比如我们想看书某一章，没有索引则需要从书第一页开始逐页翻阅。有了索引就可以快速查找。但是索引是有代价的，体现在两方面：一是需要额外的纸张（磁盘空间），每次更新书的内容时，还需要同步更新索引（额外开销）。

表上的主键也是索引的一种，且是唯一索引。

一般来说，索引能大大加快查询检索的速度，是提高数据库访问性能的有利工具。但在极端情况，有时索引反而会降低性能。比如：索引自身比内容更大时，就像一本书各种级别的标题很多，但里面文字的内容很少。

在MySQL中，所有的列都可以作为被作为索引字段。不同的存储引擎支持的索引种类和限制不同。MyISAM和InnoDB支持BTREE索引；Memory引擎默认为HASH索引（也支持BTREE）。

MySQL还支持FULL TEXT索引，FULLTEXT索引是在基于文本的列（CHAR， VARCHAR或TEXT列）上创建的，该索引可以用于文本全文搜索。仅在MyISAM和InnoDB引擎支持FULL TEXT索引。

创建索引

索引的创建语法如下（源自官方文档）。有时会在创建表时一并创建索引，MySQL8.0语法为：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name

[index_type]

ON tbl_name (key_part,...)

[index_option]

[algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option: {

KEY_BLOCK_SIZE [=] value

| index_type

| WITH PARSER parser_name

| COMMENT 'string'

| {VISIBLE | INVISIBLE}

}

index_type:

USING {BTREE | HASH}

algorithm_option:

ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:

LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

索引的删除语法：

DROP INDEX index_name ON tbl_name

[algorithm_option | lock_option] ...

algorithm_option:

ALGORITHM [=] {DEFAULT|INPLACE|COPY}

lock_option:

LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

本文MySQL5.7和MySQL8，创建的Table默认为InnoDB存储。我们创建表（先不创建Index），查看执行计划：

mysql> create table cities(city_name varchar(64) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into cities values ('Beijing'),  ('Beijing'), ('Tianjin'), ('ShangHai'), ('ShenZhen'), ('GuangZhou'), ('WuHan');
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> show indexes from cities;
Empty set (0.00 sec)

mysql> explain select * from cities where city_name = 'ShangHai' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cities
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 14.29
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

可以看到在没有索引的情况下，执行计划为：type=ALL既全表扫描，也没有使用任何的Key。

提示：关于执行计划explain的用法及含义，参见附录。

然后创建索引如下，这是可以看到该Table上的索引，执行计划也变为：type=ref非唯一索引，且使用了我们刚建的索引。：

mysql> create index idx_cityname on cities(city_name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from cities;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| cities |          1 | idx_cityname |            1 | city_name   | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> explain select * from cities where city_name = 'ShangHai' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cities
   partitions: NULL
         type: ref
possible_keys: idx_cityname
          key: idx_cityname
      key_len: 259
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

隐藏索引

MySQL8.0增加了隐藏索引，既INVISIBLE属性。 INVISIBLE的含义是该索引正常定义，但不被优化器看见和使用。

通过INVISIBLE和VISIBLE 可以设置一个索引是否被优化器使用，常用于删除索引前，检测确认该索引是否有效。

多列索引

在MySQL中，创建一个多列索引，MySQL会自动变成多个单列索引。

mysql> create index idx_name_all on T1(name1, name2, name3);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from T1;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| t1    |          1 | idx_name_all |            1 | name1       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t1    |          1 | idx_name_all |            2 | name2       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| t1    |          1 | idx_name_all |            3 | name3       | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)

这意味着，虽然创建的是多列索引。以后where条件只使用其中一列，也能走索引。

索引设计指引

索引是一把双刃剑，虽然在大多数场景都是正面效果大于负面效果。对此，我们应合理的使用索引，遵循相关经验和最佳实践，以更好的使用好索引这个有力工具。

索引字段选择

结果集字段(出现在select与from之间的字段）应该作为索引字段吗？显然不是。作为索引的字段一定是要出现在Where条件中的字段。这句话的意思，只有经常被作为where条件的字段，才有可能作为索引的备选字段。之所以是备选而不是一定。是因为还应评估这个where条件字段访问频率，访问的区间占整个表数据的比例。比如某个字段虽然在where字段中出现，但经常都要选出全部表80%的结果集，那么这时将这个字段作为索引的意义不大，甚至起到反效果。

尽量使用唯一索引

唯一索引，说明该字段在各行中的数据是一样的，在查找是很容易区分，便于快速精准定位结果集。

所有表都应增加主键

按照数据库设计范式，每个表都应该有主键（索引的一种），特别是InnoDB表。特殊情况，在理由充足的情况下可以特殊处理

使用前缀索引

如果字段中内容较长，比如VARCHAR（512），且该字段中内容前20个字符大体是唯一的，那么可以只使用该字符串的前20个字符作为索引，而不是整个字段。这样可以大幅减少索引的存储空间，字符串端比较起来也比较快。而且在缓存中，能容纳更多的值，加快访问效率。

使用函数索引（8.0.13以上）

如下面查询where条件year(brithday)，直接在字段上建立索引，explain不会走索引：

mysql> desc Person;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(32) | YES  |     | NULL    |       |
| brithday | date        | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show index from Person;    
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| person |          1 | idx_brithday |            1 | brithday    | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)

mysql> explain select * from Person where year(brithday)=1966;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | Person | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

从MySQL8.0.13开始，已经支持函数索引。如下：

mysql> create index idx_year on Person( (year(brithday)) );
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from Person where year(brithday)=1966;
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | Person | NULL       | ref  | idx_year      | idx_year | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

如果MySQL版本是小于8.0.13的低版本，则可以通过以下增加序列列方法间接实现函数索引：

1、增加虚列列：（注意Extra的信息：VIRTUAL GENERATED ）

mysql> alter table Person add column brith_year int generated always as (year(brithday));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc Person;
+------------+-------------+------+-----+---------+-------------------+
| Field      | Type        | Null | Key | Default | Extra             |
+------------+-------------+------+-----+---------+-------------------+
| name       | varchar(32) | YES  |     | NULL    |                   |
| brithday   | date        | YES  | MUL | NULL    |                   |
| brith_year | int(11)     | YES  |     | NULL    | VIRTUAL GENERATED |
+------------+-------------+------+-----+---------+-------------------+
3 rows in set (0.00 sec)

2、再虚列列上创建索引：

mysql> create index idx_year on Person(brith_year);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from Person;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| person |          1 | idx_brithday |            1 | brithday    | A         |           2 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| person |          1 | idx_year     |            1 | brith_year  | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)

3、再看执行计划，则已经使用了索引。效果相当于Oracle DB中的函数索引：

mysql> explain select * from Person where year(brithday)=1966;
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | Person | NULL       | ref  | idx_year      | idx_year | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

不要过度使用索引

索引自身会有开销，包括需要额外空间来保存索引，增删改时也需要更新索引，甚至重构索引。不经常使用的索引应该删除，小表也不宜建太多索引（数据量几十条、几百条的小表，建立索引的意义不大，主键除外）。在一个表上索引数量也需要加一限制。

BTREE和HASH索引

BTREE索引是一种二叉树的数据结构（具体可以百度搜索，有大量的介绍文章）。B树索引可以在使用表达式中使用的对列的比较 =， >， >=， <， <=，BETWEEN。如果LIKE是以非通配符开头的常量字符串，也可以使用。

而HASH索引：

适合等于、不等于等精准查找。
在ORDER BY操作无法加快速度。（此索引类型不能用于按顺序搜索下一个条目。）
无法确定两个值之间大约有多少行，不适合where条件是一段范围内。
字段内容需整体作为索引列（不像B树索引，可用字段中最左边的一小段的内容）。

为了对比两种索引，在上面已的表cities（BTREE索引）基础上，再创建一个同结构的MEMORY存储引擎的表cities_hash（HASH索引），表中的内容也相同。（因为默认的InnoDB引擎不支持HASH，所以用MEMORY引擎）

mysql> create table cities_hash engine=memory select * from cities; 
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> create index idx_hash_cityname USING HASH  on cities_hash(city_name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from cities_hash;
+-------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| cities_hash |          1 | idx_hash_cityname |            1 | city_name   | NULL      |           4 |     NULL | NULL   | YES  | HASH       |         |               |
+-------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

对两张表进行范围查询，可以看到相同的SQL相同的表结构，由于不同的索引执行计划不同。范围查询，可以使用BTREE索引，但无法使用HASH索引，只能ALL全表扫描：

mysql> explain select * from cities where city_name >= 'C' and city_name <='Z';     
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | cities | NULL       | range | idx_cityname  | idx_cityname | 259     | NULL |    5 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from cities_hash where city_name >= 'C' and city_name <='Z'; 
+----+-------------+-------------+------------+------+-------------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys     | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+-------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | cities_hash | NULL       | ALL  | idx_hash_cityname | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-------------+------------+------+-------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

FULL TEXT索引

创建一个存放文章的表（article），有主键(a_id)、文章标题（title）、文章内容（content）字段。并在title和content两个列上创建全文索引，创建SQL语句如下，并插入5条数据。

mysql> CREATE TABLE articles (
               id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
               title VARCHAR(200),
               body TEXT,
               FULLTEXT (title,body)
             ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO articles (title,body) VALUES
             ('MySQL Tutorial','DBMS stands for DataBase ...'),
             ('How To Use MySQL Well','After you went through a ...'),
             ('Optimizing MySQL','In this tutorial we will show ...'),
             ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
             ('MySQL vs. YourSQL','In the following database comparison ...'),
             ('MySQL Security','When configured properly, MySQL ...'),
             ('中文标题', '中文内容：MySQL教程学习等等');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> show indexes from articles;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| articles |          0 | PRIMARY  |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| articles |          1 | title    |            1 | title       | NULL      |           7 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| articles |          1 | title    |            2 | body        | NULL      |           7 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

提升：网上仍很多文章写到“全文索引只能用于数据库引擎为MyISAM的数据表”，这仅针对比较老的MySQL版本。现在MyISAM和InnoDB引擎都支持FULL TEXT索引。

FULL TEXT查询语法不同于普通SQL中的Like语句，全文搜索的语法格式为： MATCH (columnName,...) AGAINST ('string')。如下(默认就是IN NATURAL LANGUAGE MODE，可以省略)：

mysql> SELECT * FROM articles WHERE MATCH (title,body)  AGAINST ('database'  IN NATURAL LANGUAGE MODE) ;
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body)  AGAINST ('MySQL') ;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  6 | MySQL Security        | When configured properly, MySQL ...      |
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
+----+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)

默认情况下，搜索以不区分大小写的方式执行。

从上面第二个结果集可以看到，包含MySQL的内容，少了一条。原因是默认的全文索引parser非常合适于Latin拉丁英文，因为Latin是通过空格来分词的。不适合中文等其他文字。我们需要一个能支持中文的搜索插件来处理。在MySQL5.7中自带了可支持中文的N-gram插件。通过以下命令，重新创建full text index为N-gram Parser：

mysql> alter table articles  drop index title;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create FULLTEXT index idx_text_sreach on articles(title, body) WITH PARSER ngram;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show indexes from articles;
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| articles |          0 | PRIMARY         |            1 | id          | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| articles |          1 | idx_text_sreach |            1 | title       | NULL      |           7 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
| articles |          1 | idx_text_sreach |            2 | body        | NULL      |           7 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

再次执行上面的Select搜索语句，这是可以发现结果集中已经有中文了。

mysql> SELECT * FROM articles WHERE MATCH (title,body)  AGAINST ('MySQL');
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  7 | 中文标题              | 中文内容：MySQL教程学习等等              |
+----+-----------------------+------------------------------------------+
7 rows in set (0.00 sec)

zyplanke

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【MySQL基础】06：索引（Index）

本教程以MySQL8为主版本（内容兼顾MySQL5.7）。所有MySQL文章的目录为：总目录 https://blog.csdn.net/zyplanke/article/details/102968014 在数据库中，如果Table是一本书，那么索引Index就是这本书的目录。目录存在的价值就是能快速查找定位，比如我们想看书某一章，没有索...
复制链接

扫一扫