一、前提信息
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 '',
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 字符集 多表 组合索引