一、什么是sql优化
mysql 做为数据存储的中间件,在业务上使用的非常频繁,与此同时,数据包括新增、修改、删除、查询,这四个基本操作。不可否认,我们使用的最频繁的是查询。查询的应用场景下,包含各种各样的条件与场景:
1、分页
2、模糊查询
3、分组查询
4、排序
事实上针对不同的业务场景,我们处理sql 的场景不同。针对查询条件我们会建立索引。如果数据库数据表的数据太多,我们还可能考虑分库分表,总之针对不同的情况,我们优化的方案不尽相同。
二、怎么优化
2.1 建立索引
mysql 的索引分类
b+树的特点:
1、叶子节点连接起来的,只需要遍历一遍叶子节点即可,而且是按照顺序排列的,所以适合区间范围查询。
2、非叶子节点不存储数据,用于存储索引。
聚簇索引
聚簇索引也叫主键索引,聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分,每张表只能拥有一个聚簇索引。
Innodb通过主键聚集数据,如果没有定义主键,innodb会选择非空的唯一索引代替。如果没有这样的索引,innodb会隐式的定义一个主键来作为聚簇索引。
1、数据访问更快,因为聚簇索引将索引和数据保存在同一个B+树中,因此从聚簇索引中获取数据比非聚簇索引更快
2、聚簇索引对于主键的排序查找和范围查找速度非常快
非聚簇索引
在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的Page Directory找到数据行。
Innodb辅助索引的叶子节点并不包含行记录的全部数据,叶子节点除了包含键值外,还包含了相应行数据的聚簇索引键。
辅助索引的存在不影响数据在聚簇索引中的组织,所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。
2.2最左匹配原则
如果我们建立了一个联合索引,index(a,b),那么我们在查询条件的时候,使用到了 条件 b = ? 但是没有条件a 这个时候我们是不走索引的,原因是因为在构建索引B+树的时候,会先按照a的条件排序,再按照b的条件排序,也就是如果想利用到索引,必须包含条件a。
2.3索引失效
1、复合索引未用左列条件,也就是必须满足最左匹配原则
2、like 以%开头
3、需要类型转换
4、where 索引列有运算
5、where中索引列使用了函数
6、全表扫描更快。数据量少。
慢sql分析
SELECT labelId FROM admin.admin_rule
WHERE (admin_rule
.labelType
= 1)
AND (admin_rule
.product
= “Text”)
GROUP BY labelId
使用explain 查看执行计划
explain SELECT labelId FROM admin.admin_rule
WHERE (admin_rule
.lype
= 1)
AND (admin_rule
.p
= “Text”)
GROUP BY labelId
Using index condition; Using where; Using temporary; Using filesort
通过mysql 的执行计划发现该sql 的索引已经失效,使用了文件排序和临时表。
将 lType,p,labelId 建立联合索引