MySQL index 增删改

一、前提信息

1.数据库版本

mysql> select version(),user();
+------------+----------------+
| version()  | user()         |
+------------+----------------+
| 5.5.32-log | root@localhost |
+------------+----------------+
1 row in set (0.00 sec)

2.创建索引的帮助信息

mysql> help create index;

二、操作语句

1.添加索引

（1）建表时添加索引

create table zwq.stu2( 
    id int(4) not null auto_increment, 
    name char(20) not null, 
    age tinyint not null default '0', 
    dept varchar(16) default null, 
    primary key(id), 
    key index_name(name) 
) charset=gbk;

（2）指定列添加主键

alter table stu add primary key(id);

（3）指定列上添加索引

-- 在name列上添加名为index_name的索引
alter table zwq.stu2 add index index_name(name);

（4）在指定的库、表、列、字符长度上创建索引

-- 数据库zwq 表 stu2 字段dept 字符长度8
create index index_dept on zwq.stu2(dept(8));

（5）创建哈希索引

 create index index_dept USING HASH on zwq.stu2(dept(8)) comment '哈希索引' ;

（6）创建组合索引

create index index_name_dept on zwq.stu2(name(8),dept(10));

注意：按条件列查询数据时，联合索引是前缀生效特性的 index(a,b,c) 仅 a,ab,abc三个查询条件可以使用到索引；b、c、bc、ac则无法使用索引

（7）创建唯一索引

mysql> create unique index uniq_index_email on zwq.stu2(email);
mysql> show columns from zwq.stu2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(4)      | NO   | PRI | NULL    | auto_increment |
| name  | char(20)    | NO   | MUL | NULL    |                |
| age   | tinyint(4)  | NO   |     | 0       |                |
| dept  | varchar(16) | YES  |     | NULL    |                |
| email | varchar(30) | YES  | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

2.删除普通索引

-- 方式1
alter table zwq.stu2 drop index index_name;
-- 方式2
drop index index_dept on zwq.stu2;

3.查看索引

mysql> show index from zwq.stu2;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
| stu2  |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |
| stu2  |          1 | index_dept |            1 | dept        | A         |           0 |        8 | NULL   | YES  | BTREE      |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+
2 rows in set (0.03 sec)

注意：使用唯一索引且存在自增列的表，当因为唯一列重复而插入记录失败时，自增列依旧自增。

三、索引使用的基本条件

索引可以加快查询速度，但索引不仅占用存储空间、更新数据库数据时还需要维护索引数据，因此，索引是把双刃剑，并不是越多越好。几百行的数据不要建索引；更新频繁、读取少的表也不要建索引。

创建索引一定要在where语句的条件列，而不是select的选择数据的列，尽量选择唯一值的列上建索引。

四、索引失效的场景

or like null 字符集多表组合索引