MySQL的索引概述

2020年3月20日
| 没有评论
摘要：MySQL的索引概述

和其它数据库一样，MySQL索引对表中指定列进行排序后另外保存，用于快速查找具有特定值的行。如果没有索引，必须从第一行开始，读取整个表查找，表越大，成本就越高。如果表中有相关列的索引，就可以快速确定要在数据文件中间查找的位置，而不必查看所有数据，比按顺序读取每一行快得多。

MySQL中不是所有表上都可以建索引，要根据表使用的存储引擎来看，有的存储引擎支持建索引，有的不支持。MySQL 5.7中主要存储引擎对索引的支持程度见下表：

Feature MyISAM Memory InnoDB Archive NDB
B-tree indexes Yes Yes Yes No No
Clustered indexes No No Yes No No
Full-text search indexes Yes No Yes (note 1) No No
Geospatial indexing support Yes No Yes (note 2) No No
Hash indexes No Yes No (note 3) No Yes
T-tree indexes No No No No Yes
Index caches Yes N/A Yes No Yes

Notes:

  1. InnoDB support for FULLTEXT indexes is available in MySQL 5.6 and later.
  2. InnoDB support for geospatial indexing is available in MySQL 5.7 and later.
  3. InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

MySQL的PRIMARY KEY索引、UNIQUE索引、普通索引、FULLTEXT索引都使用B-trees存储，Spatial索引使用R-trees存储。

MySQL 5.7中的创建索引语法：

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)] [ASC | DESC]

index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT ‘string’

index_type:
USING {BTREE | HASH}

algorithm_option:
ALGORITHM [=] {DEFAULT | INPLACE | COPY}

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

也可以使用alter table语句来创建索引。

MySQL支持前缀索引，即对索引字段的前N个字符创建索引。

root@database-one 21:56:  [gftest]> select * from emp;
+--------+------+---------+------------+--------+
| ename  | age  | sal     | hiredate   | deptno |
+--------+------+---------+------------+--------+
| 郭军   |   27 | 8400.00 | 2019-12-08 |     10 |
| 刘杰   |   30 | 9100.00 | 2018-04-09 |     10 |
| 王艳   |   24 | 6000.00 | 2020-01-05 |     20 |
| 马丽   |   26 | 7200.00 | 2018-07-06 |     30 |
| 肖伟   |   29 | 8700.00 | 2017-05-28 |     30 |
+--------+------+---------+------------+--------+
5 rows in set (0.02 sec)

root@database-one 21:57:  [gftest]> show index from emp \G
Empty set (0.01 sec)

root@database-one 21:57:  [gftest]> create index idx_emp_ename on emp(ename(2));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@database-one 21:57:  [gftest]> show index from emp \G
*************************** 1. row ***************************
        Table: emp
   Non_unique: 1
     Key_name: idx_emp_ename
 Seq_in_index: 1
  Column_name: ename
    Collation: A
  Cardinality: 5
     Sub_part: 2
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

root@database-one 21:57:  [gftest]> explain select * from emp where ename like '王%';
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | range | idx_emp_ename | idx_emp_ename | 9       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.07 sec)

root@database-one 21:58:  [gftest]> explain select * from emp where sal>6000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.03 sec)

 

可以看到，创建的索引是BTREE类型，并且在按照ename查询时被使用。

索引使用总有一些原则：

  • 对经常在where、连接条件中出现的列考虑建索引。
  • 对选择性比较好的列必要时建索引。比如用户表中，身份证的列具有不同值，选择性很好，索引被使用时特别高效；姓名列，选择性较好，索引被使用时也比较高效；性别列，只含有男和女，选择性很差，对此列建索引就没有多大用处。
  • 不要过度创建索引。索引不是越多越好，每个索引都要占用磁盘空间，并会降低DML操作的性能。另外MySQL在生成执行计划时，过多的索引也会加重优化器的工作，甚至可能干扰优化器选择不到最好的索引。

© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。

MySQL
| Tags: , , , ,