本文主要从以下几个方面来聊聊我们熟悉的 mysql 数据库

机读顺序

MYSQL EXPLAN 关键字

避免索引失效情况

机器读sql的顺序与人不同,从from关键 字 开始时读,先找表,再添加条件、分组from join group by select limit (rows) affset on where having order by2. join七种表关联 A 和 B 两张表 关联

1 left join (查询 A表所有)

select * from tableA a left join tableB bon a.id = b.id

2 right join (查询 B表所有)

select * from tableA a right join tableB bon a.id = b.id

3 inner join (查询 AB表交集)

select * from tableA a inner join tableB bon a.id = b.id

4 left join where b.id is null(查询A表去除AB交集)

select * from tableA a left join tableB bon a.id = b.id where b.id is null

5 right join where a.id is null(查询B表去除AB交集)

select * from tableA a right join tableB bon a.id = b.id where a.id is null

6 full outer join (查询AB表并集)

select * from tableA a full outer join tableB bon a.id = b.id

7 full outer join where a.id is null or b.id is null (查询AB表并集)

select * from tableA a full outer join tableB bon a.id = b.id where a.id is null or b.id is null

注意 :mysql 中没有full join 使用union 会主动去重

select * from tableA a left join tableB bon a.id = b.id union

select * from tableA a right join tableB bon a.id = b.id

select * from tableA a left join tableB b on a.id = b.id where b.id is nullunionselect * from tableA a right join tableB b on a.id = b.id where a.id is null3. index 索引定义: 帮助mysql高效获取数据的数据结构

表结构 = 表数据+索引

这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。

单值索引 create index idxusername on user(name);

唯一索引 create unique index idxusername on user(name)

复合索引 create unique index idxusername on user(name,address)

删除索引 drop index indexName on tableName

查看索引 show index from tableName

使用ALTER命令创建索引

ALTER TABLE TableName ADD PRIMARY KEY(column_list); 添加一个主键索引 必须是唯一 非空

ALTER TABLE TableName ADD UNIQUE indexname(columnlist); 创建唯一索引

ALTER TABLE tableName ADD INDEX indexname(columnlist); 添加普通索引,搜索引值可以出现多次

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

表的读取顺序 (id)

数据读取操作的操作类型 (select_type)

哪些搜索引可以使用 (possible_key)

哪些索引被实际使用 (key)

表之间的引用 (ref)

每张表有多少行被优化器查询 (row)

Explain + SQL语句

执行计划包含的信息如下

查询结果表头:

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

衍生 = DERIVED

id相同 执行顺序由上至下

id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行

id相同不同,同时存在

id如果相同,可以认为是一组,从上往下顺序执行; 在所有组中,id值越大,优先级越高,越先执行

select_type

简单的 select 查询,查询中不包含子查询或者UNION

查询中若包含任何复杂的子部分,最外层查询则被标记为PRIMARY

在SELECT或WHERE列表中包含了子查询

在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询, 把结果放在临时表里。

若第二个SELECT出现在UNION之后,则被标记为UNION; 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED

UNION RESULT 从UNION表获取结果的SELECT

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

type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:

system > const > eqref > ref > fulltext > refornull > indexmerge > uniquesubquery > indexsubquery > range > index > ALL

常用的: system>const>eq_ref>ref>range>index>ALL

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

index 例如: select id from table id为主键列 只查找索引树 Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快,因为索引文件通常比数据文件小。 (也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)

all 全表扫描

表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计

例如:select * from table where id = 1;

表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快 如将主键置于where列表中,MySQL就能将该查询转换为一个常量

例如:查询CEO的部门 只有CEO部一个员工 select * from user,dept where user.deptId = dept.id

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

例如:在性别列建索引 查询性别为男的学生

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

备注:一般来说,得保证查询至少达到range级别,最好能达到ref。

possible_keys

理论上应该用到的索引 显示可能应用在这张表中的索引,一个或多个。 查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用

实际使用的索引。如果为NULL,则没有使用索引

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

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好

keylen显示的值为索引字段的最大可能长度,并非实际使用长度,即keylen是根据表定义计算而得,不是通过表内检索出的

计算方法

索引字段为char类型+不可为Null时 char(10) utf8 3*10=30

索引字段为char类型+允许为Null时 char(10) utf8 3*10 +1(NULL)=31

索引字段为varchar类型+不可为Null时 varchar(10) utf8 3*10 +2(varchar需要2个字节)=32

索引字段为varchar类型+允许为Null时 varchar(10) utf8 3*10 +2(varchar需要2个字节)+1(NULL)=33

整数/浮点数/时间类型的索引长度

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

shared.a1.col1,const

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

rows 查询到想要的结果 总共查询了几行 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

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

覆盖索引(Covering Index),一说为索引覆盖。 理解:就是select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

Using where

表明使用了where过滤

using join buffer 使用了连接缓存:

impossible where where子句的值总是false,不能用来获取任何元组

Using filesort

说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”

使用 order by 或 group by 时 分组 排序 一定 顺序 数量 列 要跟建立的索引列一致 否则会导致索引失效

例如:索引列idxcol1col2 order by col1,col2

Using temporary

使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

USING index

表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错! 意思是 直接查询索引就查询到结果

如果同时出现using where,表明索引被用来执行索引键值的查找; 如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。

第一行(执行顺序4):id列为1,表示是union里的第一个select,select_type列的primary表 示该查询为外层查询,table列被标记为<derived3>,表示查询结果来自一个衍生表,其中derived3中3代表该查询衍生自第三个select查询,即id为3的select。【select d1.name......】 第二行(执行顺序2):id为3,是整个查询中第三个select的一部分。因查询包含在from中,所以为derived。【select id,name from t1 where other_column=''】 第三行(执行顺序3):select列表中的子查询select_type为subquery,为整个查询中的第二个select。【select id from t3】 第四行(执行顺序1):select_type为union,说明第四个select是union里的第二个select,最先执行【select name,id from t2】 第五行(执行顺序5):代表从union的临时表中读取行的阶段,table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】避免索引失效情况1 全值匹配我最爱2 最佳左前缀法则3 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描4 存储引擎不能使用索引中范围条件右边的列5 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select 6 mysql 在使用不等于(!= 或者<>)的时候有时候无法使用索引会导致全表扫描7 注意null/not null对索引的可能影响8 like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作9 字符串不加单引号索引失效10 少用or,用它来连接时会索引失效where a = 3 Y,使用到awhere a = 3 and b = 5 Y,使用到a,bwhere a = 3 and b = 5 and c = 4 Y,使用到a,b,cwhere b = 3 或者 where b = 3 and c = 4 或者 where c = 4 Nwhere a = 3 and c = 5 使用到a, 但是c不可以,b中间断了where a = 3 and b > 4 and c = 5 使用到a和b, c不能用在范围之后,b断了where a = 3 and b like 'kk%' and c = 4 Y,使用到a,b,cwhere a = 3 and b like '%kk' and c = 4 Y,只用到awhere a = 3 and b like '%kk%' and c = 4 Y,只用到awhere a = 3 and b like 'k%kk%' and c = 4 Y,使用到a,b,c【优化口诀】全职匹配我最爱,最左前缀要遵守;带头大哥不能死,中间兄弟不能断;索引列上少计算,范围之后全失效;LIKE百分写最右,覆盖索引不写*;不等空值还有OR,索引影响要注意;VAR引号不可丢, SQL优化有诀窍。