MySQL数据库，详解索引管理

创建索引

⽅式1：

create [unique] index 索引名称 on 表名(列名[(length)]);

⽅式2：

alter 表名 add [unique] index 索引名称 on (列名[(length)]);

如果字段是char、varchar类型，length可以⼩于字段实际长度，如果是blog、text等长⽂本类型，必须指定length。

[unique]：中括号代表可以省略，如果加上了unique，表⽰创建唯⼀索引。

如果table后⾯只写⼀个字段，就是单列索引，如果写多个字段，就是复合索

引，多个字段之间⽤逗号隔开。

删除索引

drop index 索引名称 on 表名;

查看索引

查看某个表中所有的索引信息如下：

show index from 表名;

索引修改

可以先删除索引，再重建索引。

示例

准备200万数据

/*建库javacode2018*/

DROP DATABASE IF EXISTS javacode2018;

CREATE DATABASE javacode2018;USE javacode2018;

/*建表test1*/

DROP TABLE IF EXISTS test1;

CREATE TABLE test1 (

id INT NOT NULL COMMENT '编号',

name VARCHAR(20) NOT NULL COMMENT '姓名',

sex TINYINT NOT NULL COMMENT '性别,1：男，2：⼥',

email VARCHAR(50)

);

/*准备数据*/

DROP PROCEDURE IF EXISTS proc1;

DELIMITER $

CREATE PROCEDURE proc1()

BEGIN

DECLARE i INT DEFAULT 1;

START TRANSACTION;

WHILE i <= 2000000 DO

INSERT INTO test1 (id, name, sex, email) VALUES

(i,concat('javacode',i),if(mod(i,2),1,2),concat('javacode',i,'@163.com

'));

SET i = i + 1;

if i%10000=0 THEN

COMMIT;

START TRANSACTION;

END IF;

END WHILE;

COMMIT;

END $

DELIMITER ;

CALL proc1();

SELECT count(*) FROM test1;

上图中使⽤存储过程循环插⼊了200万记录，表中有4个字段，除了sex列，其他

列的值都是没有重复的，表中还未建索引。

插⼊的200万数据中，id，name，email的值都是没有重复的。⽆索引我们体验⼀下查询速度

mysql> select * from test1 a where a.id = 1;

+----+-----------+-----+-------------------+

| id | name | sex | email |

+----+-----------+-----+-------------------+

| 1 | javacode1 | 1 | javacode1@163.com |

+----+-----------+-----+-------------------+

1 row in set (0.77 sec)

上⾯我们按id查询了⼀条记录耗时770毫秒，我们在id上⾯创建个索引感受⼀下

速度。

创建索引

我们在id上⾯创建⼀个索引，感受⼀下：

mysql> create index idx1 on test1 (id);

Query OK, 0 rows affected (2.82 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from test1 a where a.id = 1;

+----+-----------+-----+-------------------+

| id | name | sex | email |

+----+-----------+-----+-------------------+

| 1 | javacode1 | 1 | javacode1@163.com |

+----+-----------+-----+-------------------+

1 row in set (0.00 sec)

上⾯的查询是不是⾮常快，耗时1毫秒都不到。

我们在name上也创建个索引，感受⼀下查询的神速，如下：

mysql> create unique index idx2 on test1(name);

Query OK, 0 rows affected (9.67 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from test1 where name = 'javacode1';

+----+-----------+-----+-------------------+

| id | name | sex | email |+----+-----------+-----+-------------------+

| 1 | javacode1 | 1 | javacode1@163.com |

+----+-----------+-----+-------------------+

1 row in set (0.00 sec)

查询快如闪电，有没有，索引是如此的神奇。

创建索引并指定⻓度

通过email检索⼀下数据

mysql> select * from test1 a where a.email =

'javacode1000085@163.com';

+---------+-----------------+-----+-------------------------+

| id | name | sex | email |

+---------+-----------------+-----+-------------------------+

| 1000085 | javacode1000085 | 1 | javacode1000085@163.com |

+---------+-----------------+-----+-------------------------+

1 row in set (1.28 sec)

耗时1秒多，回头去看⼀下插⼊数据的sql，我们可以看到所有的email记录，每条记录的

前⾯15个字符是不⼀样的，结尾是⼀样的（都是@163.com），通过前⾯15个字符就可以

定位⼀个email了，那么我们可以对email创建索引的时候指定⼀个长度为15，这样相对于

整个email字段更短⼀些，查询效果是⼀样的，这样⼀个页中可以存储更多的索引记录，

命令如下：

mysql> create index idx3 on test1 (email(15));

Query OK, 0 rows affected (7.67 sec)

Records: 0 Duplicates: 0 Warnings: 0

然后看⼀下查询效果：

mysql> select * from test1 a where a.email =

'javacode1000085@163.com';

+---------+-----------------+-----+-------------------------+

| id | name | sex | email |

+---------+-----------------+-----+-------------------------+

| 1000085 | javacode1000085 | 1 | javacode1000085@163.com |+---------+-----------------+-----+-------------------------+

1 row in set (0.00 sec)

耗时不到1毫秒，神速。

查看表中的索引

我们看⼀下test1表中的所有索引，如下：

mysql> show index from test1;

+-------+------------+----------+--------------+-------------

+-----------+-------------+----------+--------+------+------------

+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name |

Collation | Cardinality | Sub_part | Packed | Null | Index_type |

Comment | Index_comment |

+-------+------------+----------+--------------+-------------

+-----------+-------------+----------+--------+------+------------

+---------+---------------+

| test1 | 0 | idx2 | 1 | name | A

| 1992727 | NULL | NULL | | BTREE | |

|

| test1 | 1 | idx1 | 1 | id | A

| 1992727 | NULL | NULL | | BTREE | |

|

| test1 | 1 | idx3 | 1 | email | A

| 1992727 | 15 | NULL | YES | BTREE | |

|

+-------+------------+----------+--------------+-------------

+-----------+-------------+----------+--------+------+------------

+---------+---------------+

3 rows in set (0.00 sec)

可以看到test1表中3个索引的详细信息(索引名称、类型，字段)。

删除索引

我们删除idx1，然后再列出test1表所有索引，如下：

mysql> drop index idx1 on test1;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0mysql> show index from test1;

+-------+------------+----------+--------------+-------------

+-----------+-------------+----------+--------+------+------------

+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name |

Collation | Cardinality | Sub_part | Packed | Null | Index_type |

Comment | Index_comment |

+-------+------------+----------+--------------+-------------

+-----------+-------------+----------+--------+------+------------

+---------+---------------+

| test1 | 0 | idx2 | 1 | name | A

| 1992727 | NULL | NULL | | BTREE | |

|

| test1 | 1 | idx3 | 1 | email | A

| 1992727 | 15 | NULL | YES | BTREE | |

|

+-------+------------+----------+--------------+-------------

+-----------+-------------+----------+--------+------+------------

+---------+---------------+

2 rows in set (0.00 sec)