数据库查询优化

数据库查询优化

通常笛卡尔积的优化很明显,可以用字查询等来做。

查询的优化其实实际上很大部分都是来自索引的优化。我是这么认为的。我们很多时候都会查执行计划的方式来优化语句。而这里面我们看得最多的就是表是否使用了索引。其实如果数量量少的情况下,我们用全表扫描可能会比用索引扫描要好。但是一般情况下还是优化成索引查询比较好。使用的方法就是查执行计划。explainexplain extended

另外还有一个特别有意思的语句优化: where name like 'Abc%'优化成:where name>='Abc' and name < 'Abcd'

本文来自《数据库查询优化器的艺术》-李海翔的读书摘抄。

今天先给自己挖个坑。等之后我再来填。7天之内必填完,今日是3月12日。3月19日填完此坑。

来填坑,今天是3月16日。

性能优化简介

谈优化,我们先的知道怎么去测量。我们优化的性能其实实际上指的就是响应时间。主要测量也是看时间花在哪里。响应时间一般指的是执行时间和等待时间。在进行优化之前我们必须先明白什么样的优化才值得我们去优化:

  1. 值得优化的查询,如果为了优化1%的查询去浪费20%的人力,那肯定是不合算的;
  2. 异常情况要优化,有异常当然要优化了;
  3. 丢失时间,执行一些语句,发现花费了莫名奇妙的很长一段时间,而且这段时间也没有任何日志记录;这也有点像异常情况,对待异常一定要明白为什么;
  4. 不要相信平均值,毕竟有时候峰值几十秒,然后其它时候几毫秒,这种均值肯定是不能信的

既然要测量,那当然要有可以测量的点了。比如说从慢查询日志,show status,show profile。下面先介绍下几个常用的测量点:

慢查询日志

慢查询日志可以通过设置:long_query_time=0来开启。慢查询日志是开销最低,精度最高的测量查询时间的工具。慢查询最大的担心就是消耗大量的磁盘空间,所以建议在只开启某一段时间进行收集即可。推荐一款工具:pt-query-digest

show profile

开始profile的命令为:set profiling=1,开启之后,在服务器上执行的所有语句,都会测量其耗费的时间和其它一些查询执行状态变更相关的数据。当一条查询提交给服务器时候,此工具会记录信息到临时表,然后我们可以使用show profiles;来查询结果。当然你可以使用我下面的这个sql来查询:

1
2
3
4
5
6
7
8
9
10
11

set @query_id=1;
select state,sum(duration) as Total_R,
ROUND(
100*SUM(DURATION) /(Select sum(duration) from information_schema.profiling where query_id=@query_id),2) as Pct_R,
count(*) as Calls,
SUM(duration) /count(*) as 'R/CALL'
From information_schema.profiling
Where query_id=@query_id
Group by state
order by Total_R desc;

show status

该命令返回一些计数器。既有服务器级别的全局计数器,也有基于某个连接的会话级别的计数器。

另外还有一些命令:show processlist;show global status;这些命令可能需要在实际使用中再查了。不是我想说的重点。

优化过程

谈到优化,其实优化的点有很多,从数据库表开始设计,字段类型,字段索引设置都有很多的可优化之处,我们非专业DBA,但是可以多去尝试理解,至少靠自己,是最靠谱的。

数据类型优化

选择优化的数据类型

比如使用可以正确存储数据的最小数据类型。他们更快,因为更小的数据类型占用更少的磁盘,内存和CPU缓存,并且处理时需要的CPU周期也更少。

使用更简单的数据类型

整型比字符操作代价更低,使用内建类型代替字符串存储日期和时间,使用整型存储IP;

避免使用NULL

为null的列会使用更多的存储空间;null的列为索引,需要一个额外的存储空间;

另外还有些优化是选择具体数据类型,设计表时候,列不要太多,太多的列需要考虑分表;少关联多个表,多表查询,MySQL最多关联表61个。

索引优化

很多时候谈优化,最终都无法避免的就是说到索引优化。索引是存储引擎用来快速找到记录的一种数据结构。在MySQL中也成为key。MySQL中索引是存储在存储引擎中的,不是在服务器中。一般我们说的索引都是B-Tree索引,也就是B-Tree数据结构。B-Tree对索引的列是顺序组织存储的(这里可以看到数据结构的威力了!),索引对值进行排序是按照create table时列的顺序来的。B-Tree 索引适用于全键值,键值范围,键前缀值。

索引的种类有很多,除了上面说的B-tree索引,还有哈希索引,空间数据索引,全文索引。

索引的优点:快速定位到表的位置,减少服务器需要扫描的数量,帮助服务器避免排序和临时表,将随机I/O变成顺序I/O。不过别太迷信索引,在表数量少的情况下,全表扫描更高效。

索引查询:

  1. 全值匹配;
  2. 匹配最左前缀,多列索引匹配最左边的索引;
  3. 匹配列前缀,匹配某一列的值的开头部分;
  4. 匹配范围值;
  5. 精确匹配某一列并范围匹配其它列;
  6. 只访问索引的查询

限制:

  1. 如果不按照索引最左列开始查找,则无法使用索引;
  2. 不能跳过索引列:比如有三个索引列name,address,age;不能跳过address来查询age的索引。
  3. 如果某列为范围查询,其右侧列都无法使用索引查询;如有索引a,b,c;如果在查询中使用了 a between 1 and 15 and b =12,由于a使用了范围查询,a之后的索引b不会生效。

索引优化实例

链接地址:http://www.chenzhijun.me/2018/03/23/mysql-optimization-index/

本文标题:数据库查询优化

文章作者:陈志军

发布时间:2018-03-12 22:16:35

原始链接:http://chenzhijun.me/2018/03/12/mysql-optimization/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

坚持原创技术分享,您的支持将鼓励我继续创作!