1.写在前面
在昨天的时候,我们就谈到了数据库连接池优化
详情可参考这里:点击查看
经过昨天的分析,我们已经掌握了两个比较重要的参数MaxWait和MaxActive。
连接池的最大等待时间和最大连接数,对数据库的影响,还是比较大的。
那我们今天继续往下进行测试:SQL语句优化
说到SQL优化,可能大部分人都是比较熟悉的。经常会说,查询慢?加个索引,试试看?
哈哈,只知其一,不知其二!!!
可能,大部分人都不知道,自己写的sql,有无使用到索引?是不是最优?
那我们今天就对这个话题,进行描述,分享一些sql语句优化的一些技巧。
希望大家可以学到东西,废话不多说,上干货!!!
2.SQL语句优化
#请问这两条SQL语句有什么区别呢?你来猜一猜那条SQL语句执行查询效果更好!selectidfromsys_goodswheregoods_name=华为HUAWEI麦芒7魅海蓝6G+64G全网通;selectidfromsys_goodswheregoods_id=;复制代码
2.1.查看SQL执行计划
MySQL提供了一个EXPLAIN命令,它可以对SELECT语句的执行计划进行分析,并输出SELECT执行的详细信息,以供开发人员针对性优化。使用explain这个命令来查看一个这些SQL语句的执行计划,查看该SQL语句有没有使用上了索引,有没有做全表扫描,这都可以通过explain命令来查看。
可以通过explain命令深入了解MySQL的基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用。
EXPLAIN命令用法十分简单,在SELECT语句前加上explain就可以了,例如:
在MySQL中可以使用explain查看SQL执行计划,用法:
EXPLAINSELECT*FROMt_user;复制代码
id:SELECT识别符,这是SELECT查询序列号。
select_type(重要):表示单位查询的查询类型,比如:普通查询、联合查询(union、unionall)、子查询等复杂查询。
table:表示查询的表。
partitions:使用的哪些分区(对于非分区表值为null)。
type(重要)表示表的连接类型。
possible_keys:此次查询中可能选用的索引,一个或多个
key:查询真正使用到的索引
key_len:显示MySQL决定使用的索引长度。
ref:哪个字段或常数与key一起被使用
rows:显示此查询一共扫描了多少行,这个是一个估计值。不精确值
filtered:表示此查询条件所过滤的数据的百分比
Extra(重要):额外信息
哈哈,看到这么多的参数,可能,大部分人都直接放弃了!!!
xdm,别怕,重点的参数,我已经标出来了,也就3个,那我们就针对这3个参数进行详细说明吧。
select_type:查询类型
type:连接类型
Extra:额外信息
哈哈,开干!!!
2.2.关键结果说明
2.2.1select_type
单位查询的查询类型,比如:普通查询、联合查询(union、unionall)、子查询等复杂查询。
有以下几种值:
simple:普通查询,表示不需要union操作或者不包含子查询的简单select查询。有连接查询时,外层的查询为simple。
primary:查询的主要部分,一个需要union操作或者含有子查询的select,位于最外层的单位查询的select_type即为primary。
union:连接查询,union连接的两个select查询,第一个查询是dervied派生表,除了第一个表外,第二个以后的表select_type都是union
derived在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中
union若第二个SELECT出现在UNION之后,则被标记为UNION:若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
unionresult从UNION表获取结果的SELECT
dependentunion:依赖连接查询,与union一样,出现在union或unionall语句中,但是这个查询要受到外部查询的影响
subquery:子查询,除了from字句中包含的子查询外,其他地方出现的子查询都可能是subquery
dependentsubquery:依赖子查询,与dependentunion类似,表示这个subquery的查询要受到外部表查询的影响
derived:派生表,from字句中出现的子查询,也叫做派生表,其他数据库中可能叫做内联视图或嵌套select
01-simple简单查询
--simple:表示不需要union操作或者不包含子查询的简单select查询。有连接查询时,外层的查询为simple。explainselect*fromt_user;复制代码
02-union连接查询
--union:union连接的两个select查询,第一个查询是dervied派生表,除了第一个表外,第二个以后的表select_type都是union--语句1explainselect*fromt_useraunionselect*fromt_userb;--UNION若第二个SELECT出现在UNION之后,则被标记为UNION:--若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED--UNIONRESULT从UNION表获取结果的SELECT--语句2explainselect*from(select*fromt_useraunionselect*fromt_userb)c;--DERIVED在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中复制代码
--dependentunion:与union一样,出现在union或unionall语句中,但是这个查询要受到外部查询的影响explainselect*fromt_userawherea.idin(selectidfromt_userbunionselectidfromt_userc);复制代码
03-subquery子查询
--subquery除了from字句中包含的子查询外,其他地方出现的子查询都可能是subqueryexplainselect(selectidfromt_userwhereid=1)fromt_user;复制代码
--dependentsubquery与dependentunion类似,表示这个subquery的查询要受到外部表查询的影响explainselect(selectidfromt_userawherea.id=b.id)fromt_userb;复制代码
2.2.2type
显示的是单位查询的查询类型或者理解为访问类型,访问性能依次从好到差:
systemconsteq_refreffulltextref_or_nullunique_subqueryindex_subqueryrangeindex_mergeindexALL:查询效率很低,而且耗CPU,在数据量大的情况下才会出现复制代码
system:表中只有一行数据或者是空表。等于系统表,这是const类型的特列,平时不会出现,可以忽略不计
const(重要):使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const。其他数据库也叫做唯一索引扫描。
eq_ref(重要):唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref(重要):非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。
组合索引
非唯一索引
fulltext:全文索引检索,要注意,全文索引的优先级很高,若全文索引和普通索引同时存在时,mysql不管代价,优先选择使用全文索引
ref_or_null:与ref方法类似,只是增加了null值的比较。实际用的不多。
unique_subquery:用于where中的in形式子查询,子查询返回不重复值唯一值
index_subquery:用于in形式子查询使用到了辅助索引或者in常数列表,子查询可能返回重复值,可以使用索引将子查询去重。
range(重要):索引范围扫描,常见于使用,,isnull,between,in,like等运算符的查询中。
index_merge:表示查询使用了两个以上的索引,最后取交集或者并集,常见and,or的条件使用了不同的索引,官方排序这个在ref_or_null之后,但是实际上由于要读取所个索引,性能可能大部分时间都不如range
index(重要):select结果列中使用到了索引,type会显示为index。全部索引扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
all(重要):这个就是全表扫描数据文件,然后再在server层进行过滤返回符合要求的记录。
除了all之外,其他的type都可以使用到索引除了index_merge之外,其他的type只可以用到一个索引最少要使用到range级别
详解:
01-const:
使用唯一索引或者主键,返回记录一定是1行记录的等值where条件时,通常type是const。其他数据库也叫做唯一索引扫描。
explainselect*fromt_userwhereid=1;复制代码
02-eq_ref:
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
explainselect*fromt_useraleftjoint_userbona.id=b.id;复制代码
03-ref:
非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体。
组合索引
--ref组合索引explainselect*fromt_userwhereusername="llsydn";explainselect*fromt_useraleftjoint_userbona.username=b.username;复制代码
非唯一索引
--ref非唯一索引explainselect*fromt_userwhereaccount=llsydn;复制代码
04-range:
索引范围扫描,常见于使用,,isnull,between,in,like等运算符的查询中。
explainselect*fromt_userwhereaccountlikells%;复制代码
05-index:
select结果列中使用到了索引,type会显示为index。全部索引扫描,把索引从头到尾扫一遍,常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
explainselectaccountfromt_user;复制代码
06-all:
这个就是全表扫描数据文件,然后再在server层进行过滤返回符合要求的记录。
1explainselect*fromt_user;复制代码
2.2.3Extra
这个列包含不适合在其他列中显示的,但十分重要的额外的信息,这个列可以显示的信息非常多,有几十种。解释几个经常遇到的
01-Usingfilesort
使用了文件排序,说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。这种操作需要优化sql。
--无索引,按照文件排序explainselectsort_numberfromt_menuorderbysort_number;复制代码
--有索引,不按照文件排序explainselectsort_numberfromt_menuorderbysort_number;复制代码
02-usingindex
表示相应的SELECT查询中使用到了索引,避免访问表的数据行,这种查询的效率很高!
如果同时出现UsingWhere,索引在where之后,用作查询条件
如果没有同时出现UsingWhere,索引在where之前,用作查询结果读取
--使用where,索引在where之后,用作查询条件explainselectsort_numberfromt_menuwhereid0orderbysort_number;复制代码
--没有使用where,索引在where之前,用作查询结果读取explainselectsort_numberfromt_menuwhereid0orderbysort_number;复制代码
03-usingwhere
表示Mysql将对storageengine提取的结果进行过滤,过滤条件字段无索引;
--只有whereexplainselect*fromt_menuwhereid0;复制代码
04-Usingjoinbuffer
表明使用了连接缓存,比如说在查询的时候,多表join的次数非常多,那么将配置文件中的缓冲区的joinbuffer调大一些。
2.3.为什么使用索引就能加快查询速度呢?
二叉树数据结构,与搜索算法时间复杂度:
B+Tree面试常问题目:有没有做过数据库优化呀?我来问你一个特别简单的问题,查询的是有没有加过索引?
为什么要加索引?加上了索引之后速度就会变快吗?请您简单描述一下原因吧?
什么是索引:索引就是事先排好顺序,然后在查询的时候使用二分法等高校的查询算法来进行高效查询。
除了索引查询,肯定一般查询:这两者的差异是数量级的差异。
二分法或索引查询的时间复杂度O(log2N),使用一般查询时间的复杂度是O(n)
举例来说:w条数据,一般查询,平均50万条,比较50万次。如果用二分法,只需要不超过20次就可以找到数据!
二分法这种查找方式,效率是一般查询的2.5万倍!
哈哈,这里还是直接mark下吧!!!
好了,以上就是我个人的实操了。
个人理解,可能也不够全面,班门弄斧了。
好了,今天就先到这里了!!!^_^
后面的分享,就留在下次了,掰掰。
作者:llsydn链接: