mysql索引的概述_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:

InnoDB support for FULLTEXT indexes is available in MySQL 5.6 and later.

InnoDB support for geospatial indexing is available in MySQL 5.7 and later.

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在生成执行计划时，过多的索引也会加重优化器的工作，甚至可能干扰优化器选择不到最好的索引。