一、索引分类
1. 单值索引
即一个索引只包含单个列,一个表可以有多个单列索引
2. 唯一索引
索引列的值必须唯一,但允许有空值
3. 主键索引
设定为主键后数据库会自动建立索引,innodb为聚簇索引
4. 复合索引
即一个索引包含多个列
二、索引的语法
1.创建
CREATE [UNIQUE ] INDEX indexName ON mytable(column name(length));
length : 默认可以不用写
如果是CHAR,VARCHAR类型,length可以小于字段实际长度; 如果是BLOB和TEXT类型,必须指定length。
ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
2.删除
DROP INDEX [indexName] ON mytable;
3.查看
SHOW INDEX FROM table_name
4.使用ALTER命令
有四种方式来添加数据表的索引:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。了解
- 创建的时候指定
CREATE TABLE customer (
id INT (10) UNSIGNED AUTO_INCREMENT,
customer_no VARCHAR (200),
customer_name VARCHAR (200),
PRIMARY KEY (id), # 主键索引
KEY (customer_name), # 单值索引 ,可自己指定索引名
UNIQUE (customer_name), # 唯一索引,可自己指定索引名
KEY (customer_no, customer_name) # 复合索引/组合索引,可自己指定索引名
- 后期修改
- 注意
1.唯一索引:通常针对手机号、用户名(非真实姓名)、身份证号、员工编号等字段创建
2.主键索引:建表时,表主键指定后,主键索引自动创建
3.一个表一旦建立主键之后后期几乎不会改动,如果要删除一个表的主键,必须保证该主键不能是自增的主键,否则删除不了,且提示:
三、索引得应用场景
6.1需要创建索引得情况
1)主键自动建立唯一索引
2)频繁作为查询条件的字段应该创建索引
3)查询中与其它表关联的字段,外键关系建立索引
4)单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
5)查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
6)查询中统计或者分组字段
- 查询频繁得字段,用作关联得字段,用做排序得字段
6.2不要创建索引得情况
1)表记录太少
300万数据时MySQL性能就开始下降了,这时就可以开始开始优化了
2)经常增删改的表
提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。
因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件
3)where条件里用不到的字段不创建索引(索引也是占用空间的)
四、聚簇索引和非聚簇索引
聚簇索引:
- 并不是一种单独的索引类型,而是一种数据存储方式。
- 索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的
- 主键索引是聚簇索引,非主键都是非聚簇索引。
主键索引和唯一索引:
- 主键索引一定是唯一索引,唯一索引不一定是主键索引
- 主键索引不能为null,唯一索引可以为null
- 一个表只能有一个主键,可以有多个唯一索引