MySQL优化_索引&执行计划

索引

索引(index)是帮助MySQL高效获取数据的数据结构->本质:索引是数据结构
MySQL默认存储引擎innodb只显示支持B-Tree(从技术上来说是B+Tree)索引

索引的分类：

• 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引。
• 唯一索引：索引列必须唯一，但允许有空值。
• 复合索引：即一个索引包含多个列
• .聚簇索引：聚集索引：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，innodb的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上是B+Tree)和数据行。
• 非聚簇索引：不是聚簇索引就是非聚簇索引

--  查看索引
show index from account;
--  创建索引
create  [unique] index indexName on tablename(columnname(length))
alter table tablename add [unique] index indexname on (columnname(length))
--  删除索引
drop index indexname on tablename;

执行计划

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。
explain +SQL语句

执行计划的作用：

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

执行计划字段

ID

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

1. id相同，执行顺序由上而下
2. id不同，如果是子查询，id的序号会递增，id越大优先级越高，越先被查询。
3. id相同不同，同时存在

select_type

查询的类别，主要用来区别普通查询、联合查询、子查询等复杂查询

• SIMPLE: 简单的select查询，查询中不包含子查询或者UNION
• PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为
• SUBQUERY：在select或where列表中包含了子查询
• DERIVED：在from列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询，把结果放到临时表
• UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在form子句的子查询中，外层select被标记为Derived
• UNION RESULT: 从UNION表获取结果的select

--  SIMPLE
explain select * from t1
--  PRIMARY SUBQUERY
explain selectt t1.* ,(select t2.id from t2 where t2.id=1) from t1
--  DERIVED
select t1.* from t1,(select t2.* from t2 where t2.id =1) s2 where t1.id=t2.id
--  UNION RESULT UNION 
explain select * from t2 union select * from t2

table

显示这一行的数据是关于哪张表的。

Type

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system -> const -> eq_ref -> fulltext -> ref_or_null -> index_merge->unique_subquery -> index_subquery -> range -> ALL
比较重要的：
system -> const -> eq_ref -> ref -> range -> index -> ALL

System&Const

System：表中只有一条记录(等于系统表)，是construction类型的特例平常不会出现，这个可以忽略不计
Const：表示通过索引依次就找到了
const用于比较primary key 或者 unique索引。因为只匹配一行数据，所以很快。

--  将主键置于where列表中，MySQL就能将查询转换为一个常量 const
explain select * from (select * from test_demo where id =1 ) d

eq_ref

唯一性索引扫描，对于每个索引建，表中只有一条记录与之匹配。常用语主键或唯一索引扫描

--  id是account和test_demo 的主键  account-all  test_demo  eq_ref
explain select * from account,test_demo where account.id=test_demo.id

ref

非唯一性索引扫描，返回匹配某个单独值的所有行
本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体

--  balance 是非主键索引  type ref
explain select  balance from account where balance =1

range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引某一点而结束于另一点，不用扫描全部索引。

--  范围查找  range
explain select * from account where id between 1 and 3

index

当查询的结果全为索引列的时候，虽然也是全部扫描，但只查询了索引库，而没有查询数据

--  id和balance 均为索引  index
explain select id,balance from account 

all

full table scan，将遍历全表以找到匹配的行

--  遍历全表   all
explain select * from account 

possiable_keys & Key

possible_key：可能使用的key
Key：实际使用的索引，如果为null，则没有使用索引。

查询中若使用了覆盖索引，则该索引和查询的select字段重叠

--  extra= using index type = index
explain select balance from account 

其中 key 和 possible_keys 都可能出现null的情况

key_len

key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

• key_len 表示索引使用的字节数
• 根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到
• char和varchar跟字符编码也有密切的联系
• latin1占用一个字节，gbk占用两个字节，utf8占用三个字节(不同字符编码占用的存储空间不同)

字符串类型

create table test_index(
id int auto_increment primary key,
char_not_null char(10) not null,
char_null char(10) default null,
varchar_not_null varchar(10) not null,
varchar_null varchar(10) default null,
index idx_char_not_null (char_not_null),
index idx_char_null (char_null),
index idx_varchar_not_null (varchar_not_null),
index idx_varchar_null (varchar_null)
)engine=innodb default charset=utf8;

show index from test_index
--  utf8  占3个字节   变长字段+2
--  char(10) not null  key_len=30 -> char(10)*utf8 = 30
explain select * from test_index where char_not_null = '1'
--  char(10) default null  key_len= 31-> char(10)*utf8+null =31
explain select * from test_index where char_null = '1'
--  varchar(10) not null key_len=32 ->varchar(10)*utf8+2=32
explain select * from test_index where varchar_not_null = '1'
--  varchar(10) default null  key_len= 33->varchar(10)*utf8+null+2=33
explain select * from test_index where varchar_null = '1'

数值类型

create table num_index(
int_null int(255) null default null,
int_not_null int(255) not null,
index idx_null (int_null),
index idx_not_null (int_not_null)
)engine = innodb charset=utf8

show index from num_index
--  所占字节+null = 5-> int+null = 5
explain select * from num_index where int_null=1
--  所占字节= int -> 4
explain select * from num_index where int_not_null=1

日期和时间类型

表示时间值的日期和时间类型为datetime、 date、 timestamp、 time、 year
每个时间类型都有一个有效值范围和零值，当指定不合法的MySQL不能表示值时使用零值
timestamp类型有转悠的自动更新特性
datetime类型在5.6中长度为5个字节，在5.6中字段长度为8个字节

create table date_index(
datetime_null datetime null,
datetime_not_null datetime not null,
index idx_date_null (datetime_null),
index idx_date_not_null (datetime_not_null)
)engine=innodb charset=utf8

show index from date_index
--  6
explain select * from date_index where datetime_null=1
--  5
explain select * from date_index where datetime_not_null=1

总结

字符类型：

• 变长字段需要额外的两个字节(varchar 值保存时值保存需要的字符数，另外一个字节来记录长度(如果列声明长度超过255，则需要两个字节，所以varchar索引长度计算时候加2))，固定长度字段不需要额外的字节。
• 而null都需要一个字节的额外空间，所以索引字段最好不要为null，因为null让统计更加复杂并且需要额外的存储空间。
• 复合索引有最左前缀的特性，如果复合索引全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。
  整数、浮点数、时间类型索引长度
• not null = 字段本身的字段长度
• null = 字段本身的字段长度+1(需要一个字节存储是否为空的的标记)
• datetime类型在5.6中字段长度是5个字节，datetime类型在5.5字段长度是8个字节

Ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

extra

包含不适合在其他列中显示但十分重要的额外信息。

SQL优化

create table staffs (
 id int primary key auto_increment,
name varchar(24) not null default "" comment '姓名',
age int not null default 0 comment '年龄',
pos varchar(20) not null default "" comment '职位',
add_time timestamp not null default current_timestamp comment '入职时间'
) engine = innodb charset=utf8;

insert into staffs(name,age,pos,add_time) values ('jack',22,'manage',now());
insert into staffs(name,age,pos,add_time) values ('king',23,'manage',now());
insert into staffs(name,age,pos,add_time) values ('tom',24,'manage',now());

alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
show index from staffs;

全值匹配

建立索引列之后，能使用索引的尽量使用索引

--  全值匹配
explain select * from staffs where name= 'jack'
explain select * from staffs where name= 'jack' and age=22 
explain select * from staffs where name= 'jack' and age=22 and pos='namager'

最佳左前缀

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

--  最左前缀
--  没用到索引 索引多列，必须有索引第一列，否则不会使用索引 
explain select * from staffs where age=22 and pos ='manage'
--  索引多列，中间索引未使用   key_len=74 ->name
explain select * from staffs where name='jack' and pos='manage'
--  索引多列，中间不断  key_len = 78 -> name age
explain select * from staffs where name='jack' and age=22
--  索引多列   key_len = 140->  name  age  pos  充分使用索引
explain select * from staffs where name= 'jack' and age=22 and pos='namager'

不要在索引列做任何操作

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。

--  不要在索引列做任何操作
--  使用索引 key_len = 74
explain select * from staffs where name = 'jack'
--  全表扫描
explain select * from staffs where left(name,4) = 'jack'

范围条件放最后

--  范围条件放在最后
--  key_len 140 -> name age pos
explain select * from staffs where name ='jack'  and age =22 and pos ='manage'
--  ken_len  78 -> name age
explain select * from staffs where name ='jack'  and age >20 and pos ='manage'

覆盖索引尽量用

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

--  覆盖索引尽量用
explain select * from staffs where name= 'jack' and age=22 and pos='namager'
explain select * from staffs where name= 'jack' and age>20 and pos='namager'

--  使用了覆盖索引
explain select  name,age,pos from staffs where name= 'jack' and age=22 and pos='namager'
--  使用了覆盖索引
explain select  name,age,pos from staffs where name= 'jack' and age>22 and pos='namager'

不等于要慎用

mysql在使用不等于( != 或者 <>)的时候无法使用索引会导致全表扫描

--  不等于要慎用
explain select * from staffs where name = 'staffs';
--  索引失效  key_len = null key = null 
explain select * from staffs where name != 'staffs';
--  索引失效  key_len = null key = null 
explain select * from staffs where name <> 'staffs';

NULL和NOT NULL有影响

--  IS NULL 和 IS Not NULL
--  字段属性为 not null    ken_len=0  extra  为impossible WHERE
explain select * from staffs where name is null
--  字段属性为 not null key_len = 0 
explain select * from staffs where name is not null
--  字段属性为 default null    ken_len= 75 extra  
explain select * from staffs where name is null
--  字段属性为 not null key_len = 0 
explain select * from staffs where name is not null

Like 查询要当心

like以通配符开头(’%abc…’) mysql索引失效会变成全表扫描的操作

--  like查询要当心
--  key_len =74
explain  select * from staffs where name like 'jack'
--  key_len = null
explain  select * from staffs where name like '%jack%'
--  key_len=null
explain  select * from staffs where name like '%jack'
--  key_len = 74
explain  select * from staffs where name like 'jack%'

字符类型加引号

字符串不加单引号索引失效

--  字符串类型加引号
--  key_len =74
explain select * from staffs where name = '123'
--  key_len = null
explain select * from staffs where name = 123

or 改 union 效率高

--  or改union效率高
--  ken_len = null
explain select * from staffs where name ='jack' or name= 'king'
--  id 1  key_len=74  id 2 key_len = 74
explain select * from staffs where name ='jack' union select * from staffs where name = 'king'
--  使用覆盖索引  key_len = 74
explain select name,age from staffs where name ='jack' or name= 'king'