MySQL-02.工作中对索引的理解

索引

1.什么是索引？

• 帮助MySQL高效获取数据的数据结构
• 相当于目录，是一个文件，需要占据物理空间的

2.使用场景

• where 后面字段，对该字段建立索引（alter table 表名 add index（字段名））。
• order by 后面的字段，如果没有建立索引，执行计划会将查询出的所有数据使用外部排序，影响性能，使用排序之后，索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。
• 对join语句匹配关系on涉及的字段建立索引提高效率。
• 尽可能的在select后只写必要的查询字段，增加索引覆盖的几率。

3.创建索引的三种方式

3.1 在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,//主键索引
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),//普通索引
	FULLTEXT KEY (information),//全文索引
	UNIQUE KEY (id_card)//唯一索引
);

3.2 使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

3.3 使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

3.4删除索引

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

4.避免索引失效

• 违背了"最佳左前缀法则"，带头大哥不能死，中间兄弟不能断
• 不在索引列上做任何操作（计算、函数）
• 使用！=或者<>会导致全表扫描
• is null，is not null也无法使用索引
• 字符串不加单引号