《高性能MySQL》读书笔记-第六章:查询性能优化

Acheron 按:
最近看了一些SQL优化的资料,这是《高性能MySQL》(第3版)(High Performance MySQL,Third Edition)第六章的读书笔记,整理于此。这本书可能更适合DBA或运维人员看,但第六章讲性能优化的部分,也适合我这样的后台开发人员。

一、为什么查询速度会慢

查询是一个任务,它有很多子任务构成,每个子任务都需要消耗一定的时间。若想优化查询,就要优化子任务,要么消除一些子任务,要么减少子任务的执行次数,要么减少子任务的执行时间。

查询的生命周期大致顺序:

从客户端,到服务器,然后在服务器上执行解析,生成执行计划,执行,然后将结构返回给客户端。

其中,执行是最重要的阶段,包括了大量为了检索数据到储存引擎的调用以及调用后的数据处理,包括排序,分组等。

在完成这些任务的时候,需要在不同的地方花费时间,包括网络IO,CPU计算,生成统计信息和执行计划 锁等待。

在调用底层数据引擎检索数据时,需要等待内存操作,CPU操作,和IO操作等待,这会产生大量的上下文切换和系统调用!

优化查询的目的就是减少和消除这些操作所花费的时间。

二、慢查询基础:优化数据访问

对于低效率的查询,一般使用下面的步骤来分析:

    1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行,但有时候也可能访问了太多的列。
    1. 确认Mysql服务器层是否在分析大量超过需要的数据行。

2.1 是否向数据库请求了不需要的数据

有些应用程序会请求超过实际需要的数据, 然后这些多余的数据会被应用程序丢弃。这会给Mysql服务器带来额外的负担,并增加网络开销(如果应用服务器和数据库不在同一台主机上,网络开销会显得很明显),另外也会消耗应用服务器的CPU和内存资源。

一些不好的操作:

  • 查询不需要的记录:只查询需要的记录,在相关查询后加上limit
  • 多表关联时返回全部列:多表关联时会从磁盘中读取全部列然后在内存中进行计算,非常低效。
  • 总是取出全部列:不要用select *,这会取出全部列,会让优化器无法完成索引覆盖扫描这类优化。
  • 重复查询相同的数据:当查询重复的数据时,可以考虑将数据缓存起来,需要是时候从缓存取。

2.2 Mysql是否在扫描额外的记录

对于Mysql,最简单的衡量查询开销的三个指标为:

  • 响应时间
  • 扫描的行数
  • 返回的行数

    这三个指标会记录到Mysql的慢日志中,所以检查慢日志记录是找出扫描行数过多的查询的好办法。

响应时间

响应时间是两个部分之后:服务时间和排队时间。

服务时间是指数据库处理这个查询真正花了多长时间。

排队时间是指服务器因为等待某些资源(I/O操作、行锁等)而没有真正执行查询的时间。

扫描的行数和返回的行数

理想情况下扫描的行数和返回的行数应该是相同的,但实际这种情况并不多,比如在做一个关联查询时,服务器心須要扫描多行才能生成结果集中的一行。

扫描的行数和访问类型

Explain语句中的type列反应了访问类型。访问类型从慢到快有:全表扫描(ALL),索引扫描,范围扫描,唯一索引查询,常数引用。

如果查询没有办法找到合适的访问类型,那最好的办法就是增加一个合适的索引。

Explain语句中的Extra如果是Using Where表示Mysql将通过where条件来筛选存储引擎返回的记录。

一般Mysql能够使用三种方式应用WHERE条件,从好到坏依次为:

  • 在索引中使用WHERE条件来过滤不匹配的记录,这是在储存引擎中完成的。
  • 使用索引覆盖扫描(explain语句的extra列出现了Using index)来返回记录。直接从索引中过滤不需要的记录并返回命中的结果,这是在Mysql服务器层完成的,但无须再回表查询记录。
  • 从数据表中返回数据,然后过滤不满足的条件(explain语句的extra列出现了Using where)。这在Mysql服务器层完成的,Mysql需要从数据表读取记录然后过滤。

如果发现查询需要扫描大量的数据但只返回少量的行,通常可以尝试下面的技巧去优化它:

  • 使用索引覆盖扫描,把所有用到的列都放到索引中,这样储存引擎无须回表获取对应行就可以返回结果了。
  • 改变库表结构,使用单独的汇总表,比如count()时。
  • 重写这个复杂的查询,让Mysql优化器能够以更优化的方式执行这个查询。

三、重构查询的方式

  • 1.一个复杂查询还是多个简单查询

有时候,将一个大的复杂查询分解为多个小查询往往效果会更好。Mysql从设计上让链接和断开链接都是轻量级的操作,在返回一个小的查询结果时很高效。

  • 2.切分查询

切分查询是对大查询”分而治之”,将大查询分为多个小查询,每个查询功能完全一样,只完成一小部分。

比如:定期的清楚大量数据时,如果一个大的语句一次性完成的话,则可能一次锁住很多数据,占满整个事物日志,耗尽系统资源,阻塞很多小的但重要的查询。这个时候可以分多次删除,每次删除一部分数据,删除一次暂停一会再接着做下一次删除,可以将服务器上一次性的压力分散到一个更长的时间段,性能会更好。

  • 3.分解关联查询

将一条关联多个表的sql语句通过应用程序拆分成多个操作单个表的语句。

分解关联查询方式的优势:

  • 让缓存效率更高,应用程序可以缓存了单表的结果对象,然后下次直接使用这个缓存的结果,这样就跳过了一次查询。
  • 将查询分解后,执行单个查询可以减少锁的竞争
  • 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展,为分布式做准备!
  • 查询本身效率也会有提升
  • 可以减少冗余记录的查询:在应用层做关联,意味着某条记录只需要查询一次,而在数据库做关联,则可以会重复返回一部分数据。
  • 这样分解相关于在应用中实现哈希索引,而不是使用数据库的嵌套循环关联

四、查询执行的基础

当向mysql发送一个请求的时候,mysql做了什么:

  • 1.客户端发送一条查询给服务器
  • 2.服务器先查询查询缓存,若缓存命中,则立刻返回,否则进入下一阶段
  • 3.服务器端进行SQL解析,预处理,再由优化器生成对应的执行计划
  • 4.Mysql根据优化器生成的执行计划,调用储存引擎的API执行查询
  • 5.将结果返回给客户端

4.1 Mysql客户端/服务器通信协议

Mysql客户端和服务器之间的通信协议是”半双工”的,这意味着,在任何一个时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据。这种简单的协议限制了Mysql,一个明显的限制是没办法进行流量控制,一旦一端开始发送数据,另一端中能接受完整的消息才能响应它。

查询状态:对于一个Mysql链接,或者一个线程,任何时候都有一个状态,该状态表示了Mysql当前正在做什么。查看当前状态可以用命令:

show full processlist

Mysql的状态值有:

  • Sleep:线程正在等待客户端发送新的请求
  • Query:线程正在执行查询,或者正在将结果发送到客户端
  • Locked:在Mysql服务器层,该线程正在等待表锁。在储存引擎级别实现的锁,例如InnoDB的行锁,并不会体现在线程状态中。对于MyISAM来说这是一个典型的状态,因为它没有行锁。
  • Analyzing and statistics:线程正在收集储存引擎的统计信息,并生成查询的执行计划。
  • Copying to tmp table [on disk]:线程正在执行查询,并且将结果集都复制到一个临时表中,这种状态要么是在执行GROUP BY操作,要么是文件排序操作,或者是UNION操作,[on disk]标记表示Mysql正在将一个内存临时表存放到磁盘上。
  • Sorting result:线程正在对结果集进行排序
  • Sending data:这表示多种情况,线程可能在多个状态之间传送数据,或者生成结果集,或者在向客户端返回数据。

4.2 查询缓存:

  • 在解析一个查询语句之前,要检查缓存是否命中。
  • 这个检查是通过一个对大小写敏感的哈希查找实现的
  • 若缓存命中了,那么在返回查询结果之前Mysql会检查一次用户权限。权限没问题,则直接从缓存中拿到结果返回客户端。

4.3 查询优化处理

查询生命周期的下一步是将一个SQL转换成一个执行计划,这个阶段包括多个子阶段:解析SQL,预处理,优化SQL执行计划。

语法解析器和预处理:Mysql通过关键字将SQL语句进行解析,并生成一颗对应的”解析树”,Mysql解析器将使用Mysql的语法规则验证和解析查询。比如:它将验证是否使用了错误的关键字,或者使用关键字顺序是否正确,或者验证引号能否正确匹配。

预处理器则根据一些Mysql规则进一步检查解析树是否合法,例如,这里将检查数据表和数据是否存在,还会解析名字和别名,看看他们是否有歧义。

下一步预处理器会验证权限。

4.4 查询优化器:

一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就好找到其中最好的执行计划。

Mysql使用基于成本的优化器,它尝试预测一下查询使用某种计划时的成本,并选择其中最小的一个。

Mysql能够处理的优化类型:

  • 重新定义关联表的顺序
  • 将外连接转化为内连接
  • 使用等价变换规则,如(5=5 and a>5)改为a>5
  • 优化COUNT() MIN() MAX():如要找某列的最小值,只需查B-Tree中最左端的记录
  • 预估并转化为常数表达式
  • 覆盖索引扫描
  • 子查询优化
  • 提前终止查询
  • 等值传播
  • 列表IN()的比较:mysql将in()列表中的数据先进行排序,然后通过二分查找的方式然定列表中的值是否满足条件

Mysql如何执行关联查询:在Mysql中,不仅仅是两个表匹配叫关联,其实单表查询,子查询都叫关联。

对于UNION查询,原理是Mysql先将一系列的单个查询放到一个临时表中,然后再重新读出临时表的数据来完成UNION查询。在Mysql概念中,每个查询都是一次关联,所以读取临时表的也是一次关联!

  • 执行计划:

和其他关系数据库不同,Mysql不会生成查询字节码来执行查询。Mysql生成查询的一颗指令树,然后通过储存引擎执行完成这颗指令树并返回结果。如果对某个查询执行explain extended后,再执行show warnings,就可以看到重构出的查询。

  • 排序优化:

排序是一个成本很高的操作,从性能考虑,应尽可能避免排序或尽可能避免对大量数据排序。

  • mysql两种排序算法:
    • 两次传输排序(旧版本使用):先读取行指针和需要排序的字段,进行排序,然后根据排序结果读取所需要的数据行。这需要进行两次数据传输,并且第二次是随机IO,所以成本很高,不过好处是在排序时使用的储存很少!
    • 单次传输排序(新版本使用):先查询所需要排序的列,然后根据给定列进行排序。

五、优化案例

5.1 优化count()查询

count()两种作用:

  • 统计某个列值的数量(不统计null)
  • 统计结果集的行数,用通配符count(*)

案例1:

如何快速查找到所有id>5的城市,首先想到这样查询:

select count(*) from City where id > 5;

通过show status的结果可以看到这个查询要扫描4000行数据,如果将条件反转一下,先查找id<=5的数,然后总数减去,可以将扫描行数减少到5行,大大提高效率,这是因为在查询优化阶段会将其中的子查询直接当作一个常数来处理:

select (select count(*) from City)-count(*) from City where id <=5;

案例2:

问题:如何在同一个查询中统计同一列的不同值的数量,以减少查询的语句量。

解决,用sum():

select sum(if(color='blue',1,0)) as blue,sum(if(color='red',1,0)) as red from items;

或者使用count(),只需要将满足条件设置真,不满足条件设置为null:

select count(color='blue' or null) as blue,count(color='red' or null) as red from items;

5.2 优化关联查询

  • 确保on或者using子句中列上有索引
  • 确保group by或order by表达式中只涉及到一个表中的列,这样mysql才有可能使用索引来优化这个过程

5.3 group by

如果没有通过order by子句显式地指定排序列,当查询使用group by子句的时候,结果集会自动按照分组的字段进行排序,如果不关心结果集顺序,则可以使用order by null,让mysql不再进行文件排序。

5.4 优化limit分页

问题:

当偏移量非常大的时候,例如可以是limit 10000,20,这里mysql需要查询10000条记录然后只返回最后20条,前面10000条将被抛弃,翻到很后面,性能会很差。

延迟查询:

select film_id,desc from sakila.film order by title limit 50,5;

如果这个表非常大,改成延迟查询将大大提高效率,它让mysql扫描尽可能少的页面,获取需要访问的记录后再根据关联列回原表查询需要的所有列:

select film.film_id,film.desc
from sakila.film
    innser join(
        select film_id from sakila.film order by title limit 50,5
    ) as lim using(film_id);

使用canal实现redis缓存刷新

背景

假设现有项目P1,是直连数据库(mysql)的。为了提高性能,现在要加上缓存(redis),但是另有项目P2,也对数据库有操作,如何保证当P1加上redis后,P2对数据库修改后,P1从redis中获取的数据是最新的。有两种方案:

  • 一种是P1的开发人员和P2开发人员协调,当P2对mysql有修改、增加等操作,必须清除P1使用到的相关缓存。当P1读redis时,发现缓存失效,再读mysql,获取最新数据。
  • 一种是利用阿里巴巴的canal,获得mysql-binlog,根据mysql-binlog清除相关的缓存。这种方法的好处是不需要修改原来的程序。

使用到的技术

部署配置canal

canal的部署可以参考官方文档:https://github.com/alibaba/canal/wiki/QuickStart 配置的时候要注意:

  • conf/canal.properties是canal本身的配置文件,基本上默认即可
  • conf/example/instance.properties:应用的配置参数,一般需要修改其中的数据库连接配置

使用

1.启动canal:

sh bin/startup.sh

canal 的官方例子: https://github.com/alibaba/canal/wiki/ClientExample

利用canal解析mysql-binlog,能够获取到执行的sql,修改的表数据等信息,我这里获取修改的表,根据表名去清除掉使用到这个表的相关缓存,所以这里有一个对应关系:表名--缓存key

这里表名和缓存key的对应关系,为了方便,我将其存在一个属性文件中:cache.properties,并提供一个web界面来维护这个文件。

2.配置cache.properties

在计算机上任一位置建一文件:cache.properties,在项目的application.properties中有一属性:cache.properties.path,指向cache.properties文件的位置:

cache.properties.path=/Users/acheron/cache.properties

2.配置redis

util/RedisUtil.java中配置redis相关信息,主要是以下三项:

// Redis的地址
private static String HOST = "127.0.0.1";
// Redis的端口号
private static int PORT = 6379;
// 访问密码
private static String PASS = null;

3.添加表-缓存key配置

运行项目,浏览器访问,添加一条记录,数据将会保存到cache.properties

table_name1=herohuang'skey
table_name2=user.name.haha
table_name3=hahaha,com.hero,com.herohuang*
table_name4=com.hahaha,user.name.key,test

注意cachekey的配置:

  • 如果有多个,则用英文逗号隔开
  • 如果要删除所有以com.herohuang开头的缓存,则可以com.herohuang*

源码地址:https://github.com/Ac-heron/hexo-canal

界面展示

canal1canal2

Linux系统MySQL的安装与使用

安装与配置MySQL

安装MySQL数据库

MySQL数据库分服务器和客户端,服务器用于管理和维护数据库,客户端用于连接和访问数据库,可以用下列命令安装服务器和客户端。安装期间将会提示输入数据库管理员root的密码。

sudo apt-get install mysql-server

配置文件my.cnf

MySQL的配置文件是/etc/mysql/my.cnf,主要用于配置数据库文件的存储位置,日志文件等,以下是安装之后默认的配置参数。

2015.07.24_19h25m36s_003_

mysql配置参数2
注意:
每次修改my.cnf时,都需要重新启动mysqld守护进程,可以使用如下命令:

sudo /etc/init.d/mysql restart

MySQL的简单使用

使用mysql

进入mysql命令环境,使用如下命令,-u后跟用户名,-p后跟密码,没有则为空

mysql -u -root -p

创建 查询 使用和删除数据库

创建:

create database test;

查询:

show databases;

使用:

use test;

删除:

drop database test;

设置用户及访问权限

mysql的用户和密码都存在一个专用的数据库mysql中,管理员root可以在其中添加用户及赋权,命令如下:

GRANT ALL PRIVILEGES ON database TO username@"servername" IDENTIFIED BY 'password';

如果flush配置变量没有设为ON,或者启动mysql进程时没有使用“–flush”选项,需要使用flush命令,才能使添加的帐户的生效。

flush privileges

SQL脚本与批处理

可以将多条命令写入一个文件,以I/O的方式运行mysql,批量执行命令,命令如下 :

mysql -u username -p [password] < scriptfile

例如新建一个文件:test.sql,内容如下:

show databases;
use test;
show tables;
select * from user;

利用命令执行,则将会显示执行结果:

mysql -u root -p < test.sql;

如果是在mysql交互环境下,则可用“source” 或 “ \ . ” 命令运行sql脚本:

source test.sql;

\. test.sql

绿色版MySQL的安装配置

我以mysql-5.6.13-win64.zip为例安装
 
一、配置环境变量
     
1、解压绿色版Mysql到某个目录,我这里是D:\javatools\mysql-5.6.13-winx64,将bin目录设为系统环境变量
绿色版MySQL的安装配置
二、修改my-default.ini文件
修改D:\javatools\mysql-5.6.13-winx64\my-default.ini文件,主要修改两个地方,basedir和datadir,默认是用#号注释的,去掉#号,basedir设为mysql安装目录;datadir设为mysql数据库存放位置

修改ini

三、安装mysql的服务
 
1、运行cmd,进入到bin目录下,运行命令:mysqld install MySQL –defaults-file=”D:\javatools\mysql-5.6.13-winx64\my-default.ini”
     出现Service successfully installed.表示安装成功。 如果出现Install/Remove of the Service Denied! 是因为权限不够,进入 C:\Window\System32找到cmd.exe右键选择 以管理员身份 进行,再进入相应目录执行命令,一切就OK了。
安装服务
四、启动mysql


   1、安装服务完成后,打开服务窗口,就可以找到mysql服务了,右键选择启动,可以在右键属性中,将启动类型设为自动,这样就能随开机而启动了。

  如果无法启动,报1067错误,则删除data/下ib-logfile0   ib-logfile1两个文件,然后启动。
绿色版MySQL的安装配置

绿色版MySQL的安装配置

     2、也可以用命令来启动和停止mysql
2014.07.13_15h15m38s_007   
     

五、登录mysql服务器
     输入命令:mysql -uroot
     注意:mysql的管理员用户名为root,密码默认为空。
     2014.07.13_15h19m34s_008

六、操作

1.查看数据库:show databases;

2.使用数据库:user 数据库名
3.查看表:show tables;
2014.07.13_15h28m27s_009