MySQL索引总结

概念

帮助MySQL高效获取数据的、排好序的数据结构。
在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在数据结构之上实现高级查找算法。这种数据结构就是索引。

优缺点

• 优点

1. 降低数据库I/O成本。
2. 降低CPU的消耗。

• 缺点

1. 建立索引会占用磁盘空间。
2. 会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

类型

主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。

唯一索引

索引列的值必须唯一，允许有多个NULL但不允许有多个空值('')。如果是组合索引，则列值的组合必须唯一。

普通索引

这是最基本的索引，它没有任何限制。

覆盖索引

select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件。查询列要被所建的索引覆盖

语法

CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

• unique|fulltext为可选参数，分别表示唯一索引、全文索引

• index和key为同义词，两者作用相同，用来指定创建索引

• col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择

• index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值

• length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度

• asc或desc指定升序或降序的索引值存储

创建索引

• 建表时指定

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_name` (`name`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 建表后添加

mysql> alter table user add primary index index_name(id);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table user add index index_name(name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table user add unique index_age(age);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> create primary index index_id on user(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index index_name on user(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create unique index index_age on user(age);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

显示索引

mysql> desc user;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)     | NO   | UNI | NULL    |                |
| age   | int(20)          | NO   | MUL | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> show index from user;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          0 | index_age  |            1 | age         | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_name |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 131   |
+-----------------------+-------+
7 rows in set (0.00 sec)

• handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
• handler_read_rnd_next:这个值越高，说明查询低效

修改索引名称

5.7及以上版本

mysql> alter table user rename index index_age to index_age_new;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

5.7以下的版本

ALTER TABLE user DROP INDEX index_age 
ALTER TABLE user ADD INDEX index_age_new(age)

删除索引

mysql> drop index index_name on user;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
mysql> drop index index_age on user;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0