MySQL优化

文章目录

索引执行计划执行计划字段IDselect_typetableTypeSystem&Consteq_refrefrangeindexall possiable_keys & Keykey_len字符串类型数值类型日期和时间类型总结 Refrowsextra SQL优化全值匹配最佳左前缀不要在索引列做任何操作范围条件放最后覆盖索引尽量用不等于要慎用NULL和NOT NULL有影响Like 查询要当心字符类型加引号or 改 union 效率高

索引

索引(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查询，查询中不包含子查询或者UNIONPRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为SUBQUERY：在select或where列表中包含了子查询DERIVED：在from列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询，把结果放到临时表UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在form子句的子查询中，外层select被标记为DerivedUNION 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'