从4到1,成本是逐渐增大的,因此数据库的优化上,SQL语句优化是很重要的一个方面。
针对SQL的优化有以下基本概念需要掌握:
例如性别这列,有男女两个不同的唯一键,其基数为2。
基数的高低反映出该例的数据分布情况。
如果某个列基数很低,该例数据分布就会非常不平衡,由于该列数据分布不均衡,会导致SQL索引可能走全表扫描,也可能走索引。
如果SQL语句是单表访问,那么可能走索引,也可能走全表扫描,还有可能走物化视图扫描。走索引的条件:返回表中5%的数据以内的时候走索引,超过5%以上走全表扫描(根本原因在回表,下面有讲到回表)。
数据量大的情况下,基数小,会走全表扫描。 数据量大的情况下,基数大,选择数据超过百分之20,会走全表扫描。
什么样的列必须建立索引呢?
在进行SQL优化的时候,但看基数是没有意义的,必须对比总行数此案有实际意义,因而引入了选择性。
选择性大于20%,说明该列的数据比较均衡。当一个列出现在where语句中且选择性大于20%,在该列上创建索引能够提升SQL查询性能。
SQL优化核心思想:只有大表才会产生性能问题。
因此在大表建索引是优化方式之一,可以使用V$SQL_PLAN
或者自动化脚本抓取表的哪一列出现在where语句中,用于建索引。
直方图是一种统计信息图,它使用高低不等的纵向条纹或线段表示数据分布情况。
如果没有对基数低的列收集直方图统计信息,基于成本的优化器(CBO)会认为该列数据分布是均衡的。
在做SQL优化的时候,经常需要做的工作就是帮助CBO计算出比较准确的Rows,收集直方图统计信息是一种有效的方式。如果CBO每次计算都可以得到精确的Rows,那么我们就只需要关心业务逻辑、表设计、SQL写法已经如何建立索引了,不需要担心SQL会走错执行计划。
可以使用自动化脚本抓出必须建立直方图的列。
当对一个列建立索引后,索引会包含该列的键值以及对应行所在的rowid
。通过索引中记录的rowid
访问表中的数据就叫回表。回表一般是单块读,回表次数太多会严重影响SQL性能,如果回表次数过多,就不应该走索引扫描了,应该走全表扫描。
为什么5%的数据以内的时候走索引,超过5%以上走全表扫描?
根本原因在回表。在回表无法避免的条件下,走索引如果返回数据量太多,必然导致回表次数太多,从而导致性能严重下降。
集群因子用于判断索引回表需要消耗的物理I/O次数。
集群因子介于表的块数和表行数之间。
如果集群因子与块数接近,说明数据基本上是有序的,而且其顺序基本与索引一样。这样在进行索引范围或者索引全扫描的时候,回表只需要读取少量的数据块就能完成。
如果集群因子与表记录数接近,说明表的数据和索引顺序差异很大,在进行索引范围扫描或者索引全扫描的时候,回表会读取更多的数据块。
在进行SQL优化的时候,往往会建立合适的组合索引消除回表,或者建立组合索引尽量减少回表次数。
一对一、一对多、多对多。
项 | 含义 | SQL示例 |
---|---|---|
full table scan | 全表扫描(多块读):从表中读取所有行 | |
index fast full scan | 索引快速全扫描(多块读):不需要通过rowid获取其他数据 | select id from taxidata where id = '1' |
table access by rowid | 直接通过rowid定位行,即回表 | |
index unique scan | 索引唯一扫描 | select * from taxidata where id = '1' |
index range scan | 索引局部扫描:范围定位行 select * from taxidata where id < '5' | |
index full scan | 索引全扫描:遍历索引扫描 | select id from taxidata |
取决于数据在哪:
在读取数据小的时候,IO操作少,明显索引扫描性能更好; 在读取数据大的时候,IO操作多,索引性能退化,还不如全表扫描。
在SQL语句前加Explain:
explain select * from taxidata where id = '18763'
(id是主键,索引列)
explain select * from taxidata where carid = '18763'
(非索引列查找)
通过explain分析我们可以得到: 1.表的读取顺序(多表连接) 2.数据读取操作的操作类型 3.使用了哪些索引 4.表之间的引用 5.每张表有多少行被优化器查询
select id from a where num is full
!=
<>
or
, in
,not in
or -> union; in ->between and这个不属于全表扫秒,补充的SQL可以优化的地方。
select * from taxidatas where speed/2 = '16'
(耗时1.859s)
select * from taxidatas where speed = '32'
(耗时1.831s)大表优化是数据库架构优化的一个重要思想。
因为如果存在大表,数据库的CRUD性能会明显下降。优化方法也很直观,需要把大表拆成小表即分库分表。
MySQL优化主要分为以下四个方面: 设计:存储引擎,字段类型、范式与逆范式 功能:索引、缓存、分区分表 架构:主从复制,读写分离、负载均衡 合理SQL:经验,测试比较(上面的SQL语句优化)、
为项目选择合适的存储引擎,在性能和可靠性上做一些取舍。
字段类型应该要满足需求,尽量要满足以下需求。
尽可能小(占用存储空间少)、尽可能定长(占用存储空间固定)、尽可能使用整数。
为了建立冗余较小、结构合理的数据库,设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库,必须满足一定的范式。
第一范式1NF,原子性 第二范式2NF,消除部分依赖 第三范式3NF,消除传递依赖
逆范式 逆范式是指打破范式,通过增加冗余或重复的数据来提高数据库的性能。
在范式和逆范式之间做一些取舍。
其余部分都是一些直观的优化技巧,这里不再赘述。
SQL优化核心思想-异步图书。