索引 -- 2

1、自动创建索引

1. 当我们为一张表加主键约束（Primary key)，外键约束（Foreign Key)，唯一约束（Unique)时，MySQL会为对应的的列自动创建一个索引

2. 如果表不指定任何约束时，MySQL会自动为每一列生成一个索引并用ROW_ID进行标识

2、手动创建

2.1、主键索引

1. 创建表时创建主键

查看索引的简要信息：desc t_pk1;

查看索引：show keys from 表名；

                  show index from 表名；

2. 创建表时单独指定主键列

3. 修改表中的列为主键索引

修改表的结构和列：
alter table 表名 [add | modify | drop] 要修改的内容;

2.2、唯一索引

2.3、普通索引

1. 创建表时指定索引列

2. 修改表中的列为普通索引

3. 使用 create index 创建索引

语法：create index 索引名 on 表名(列名[列名]..….);

-- 索引名推荐使用 idx_表名_列名[_列名]...

---

-- 为name 列建立索引，不指定索引名时失败，必须要指定名字
create index on t_index3(name); -- 错误

2.4、复合索引

与创建普通索引相同，只不过指定多个列，列与列之间用逗号隔开

推荐使用 单独创建索引并指定索引名

3、删除索引

主键索引

语法：alter table 表名 drop primary key;

如果主键是自增列，先把自增列改成非自增

其他索引

语法：alter table 表名 drop index 索引名;

示例：alter table t_index_6 drop index idx_t_index_6_sno_name;

4、索引注意事项

• 索引应该创建在高频查询的列上
• 索引需要占用额外的存储空间
• 对表进行插入、更新和删除操作时，同时也会修索引，可能会影响性能
• 创建过多或不合理的索引会导致性能下降，需要谨慎选择和规划索引

5、explain

问题：怎么去查看自己写的SQL有没有使用索引？
答：查看执行计划 explain，如果 key 列的显示结果显示结果非null，则使用了索引

示例：先为学生表创建一个索引(组合)

create index idx_student_sn on student (sn, name);

1. 不加条件，查询所有
执行结果是explain后面的SQL语句的执行计划

2. 使用主键查询

3. 子查询中使用索引

4. 使用普通索引

5. 使用复合索引（idx_student_sn 是 sn 和 name 的复合索引）

explain select * from student where sn = '09982' and name = '黑旋风李逵';

explain select sn, name from student where sn = '09982' and name = '黑旋风李逵';

explain select sn, name from student where name = '黑旋风李逵' and sn = '09982';

explain select name from student where sn = '09982';

 explain select sn from student where name = '黑旋风李逵';