MySQL 索引示例

索引

定义：

索引是一个排序的列表，包含索引字段的值和其对应的行记录数据所在的物理地址
作用：加快表的查询速度（主要作用），还可以对字段排序
副作用：会额外占用磁盘空间；更新包含索引的表会花费更多的时间

工作方式：

没有索引的情况下，要查询某行记录数据时，需要先扫描全表，再定位某行记录数据的位置
有了索引后，会先通过索引查询到行记录数据所在的物理地址，即可直接访问相应的行记录数据，就像通过书目录的页码快速查找书内容一样。

创建索引的依据：

1. 表的记录行数较多时（一般超过三五百行时），应该要创建索引
2. 建议在表的 主键字段、外键字段、多表连接使用的公共字段、唯一性较好的字段、不经常更新的字段、where条件字段、分组(group by)字段、排序(order by)字段、短小的字段 上创建索引
3. 不建议在 唯一性较差的字段、更新太频繁的字段、大文本字段 上创建索引

索引的类型：

1.普通索引 index

最基本的索引类型，没有唯一性之类的限制

直接创建普通索引
create index 索引名 on 表名（字段）；
修改表方式创建
alter table 表名 add index 索引名（列名）；
创建表的方式创建普通索引
create table 表名 （字段1 数据类型， 字段2 数据类型， index 索引名（列名））；

2.唯一索引 unique

与普通索引类似，但区别是唯一索引列的每个值都唯一。唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。
 

直接创建唯一索引
create unique index 索引名 on 表名（字段）；
修改表的方式
alter table 表名 add unique index 索引名（字段）；
创建表的方式来创建唯一索引
create table 表名（字段1 数据类型 ， 字段2 数据类型， unique index 索引名（字段））；

3.主键索引

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。

创建表添加
create table 表名 （字段1 数据类型， 字段2 数据类型，primary key（字段））；
修改表添加
alter table 表名 add primary key（字段）；

4.组合索引 index

可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为 select 语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

create index 索引名 on 表名（字段1，字段2） 
alter table 表名 add index 索引名（字段1，字段2）
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ; 
#查询语句使用 and 做逻辑运算符时，字段顺序要与创建多列索引的字段顺序一致（要满足最左原则）

5.全文索引 fulltext

create fullext index 索引名 on 表名（字段）；
alter table 表名 add fullext index 索引名（字段）；
create table 表名（字段 书数据类型， ... fullext index (字段)）；

删除索引 

删除普通索引、唯一索引、全文索引、组合索引

drop index 索引名 on 表名；
alter table 表名 deop index 索引名；

删除主键索引
alter table 表名 drop primary key；

查看索引

show create table 表名；
 
show index from 表名；
show key from 表名；

遇到 select 查询语句执行速度慢该怎么办？

1、升级 CPU 内存 硬盘 硬件性能
2、对 MySQL 配置进行优化
3、对查询语句的结构进行优化，比如将嵌套子查询优化成表连接查询；或连接表时，可以先用where条件对表进行过滤，然后做表连接
4、进行索引优化：先使用 explain 分析 select 语句（看 key rows type 字段），判断这个查询语句是否正确的使用了索引

     再根据查询语句中的 where 条件字段建立相应的单列索引或者多列组合索引（多列组合索引要满足最左原则）