索引优化

14、索引优化

sql优化

原因：

1、查询语句写的差

2、性能低，执行时间比较长，等待时间长

3、索引失效

14.1 索引

索引 index 是帮助MySQL高效获取数据的数据结构

索引是数据结构

（树：B树，hash树等）

MySQL使用的是B树，B+树

14.2 索引分类

1、单值索引

单列的值，比如把phone这一列取出制作索引，一张表可以有多个单值索引

2、唯一索引

索引列的值不能有重复，必须唯一，允许数据是null

3、复合索引

一个索引由多个列（最少两列）构成

4、主键索引

索引列的值不能有重复，必须唯一，不允许数据是null

14.3 创建索引

#创建索引
/*
第一种方式：
create 索引类型 索引的名字 on 表名(字段名);
*/

show index from students;

#单值索引
create index name_index on students(name);

#唯一值索引
create unique index phone_index on students(phone);

#复合索引
create index phone_name on students(phone,name);

#删除索引
drop index phone_name on students;
drop index phone_index on students;
drop index name_index on students;

/*
第二种：
alter table 表名 add 索引类型 索引名
*/

alter table students add index name_index(name);
alter table students add unique index phone_index(phone);
alter table students add index phone_name_index(phone,name);




注意点：
建议一张表不要超过5个索引

14.4 删除索引

drop index 索引名 on 表名;

#删除索引
drop index phone_name on students;
drop index phone_index on students;
drop index name_index on students;

14.5 查看索引

show index from 表名;

14.6 索引的优缺点

优点：

通过索引建立对数据的排序，降低了数据排序的成本，降低了CPU的消耗，降低数据库的io操作

缺点：

1、占据空间

2、索引确实提高了查询速度，但是会降低更新速度

3、如果数据库有大数据量的表，需要花时间研究建立最优秀的索引

14.7 创建索引的注意点

一、需要创建索引

1、主键自动创建唯一索引

2、频繁作为查询条件的字段应该创建索引

3、查询中与其他表关联的字段，外键应该创建索引

二、不需要创建索引

1、在where条件筛选中用不到的字段不需要创建索引

2、经常发生修改的(增删改)表

14.8 查询计划

#explain SQL语句
explain select * from students where phone='13111111112';

select_type：查询类型

simple           简单的select查询，查询中不包括子查询或union

primary          查询中包含任何复杂的子部分

subquery         在select或where中包含子查询的

derived          在from的列表中包含子查询的

union            第二个select出现在union后面

union result     从union的表中获取结果的select

type

显示查询使用了哪种类型，优劣情况

system > const > eq_ref > ref > range > index > all

system   表只有一行数据(系统表)，这是const类型的特例，平时不会出现

const    通过索引一次就找到了，const用于比较 primary key 和 unique 索引

eq_ref   唯一性索引扫描

ref      非唯一性索引扫描

range    检索指定范围的行，使用一个索引来选择行，一般就是where条件筛选的时候，出现between,<,>,in，这种范围查找比全表查找要好

index    遍历的是索引树，index从索引树中获取，all从硬盘上获取

all      全表扫描，遍历全表找匹配行



注意点：
一般达到range级别

possible_keys

显示可能应用在当前表中的索引，一个或者多个

查询涉及到的字段有索引，会被列出，不一定会在实际的查询中使用

key

实际使用的索引，若为null，表示没有使用索引