文章目录
索引
索引(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子句或操作表的顺序
- id相同,执行顺序由上而下
- id不同,如果是子查询,id的序号会递增,id越大优先级越高,越先被查询。
- 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'