MySQL 性能优化之影响性能的因素

发表于 | 分类于 ，
字数统计: 11.9k | 阅读时长 ≈ 46

1、MySQL 性能优化之影响性能的因素

1.1 商业需求的影响

不合理需求造成资源投入产出比过低，这里我们就用一个看上去很简单的功能来分析一下。
需求：一个论坛帖子总量的统计，附加要求：实时更新

从功能上来看非常容易实现，执行一条SELECT COUNT(*) from 表名 的Query 就可以得到结果。但是，如果我们采用不是MyISAM 存储引擎，而是使用的Innodb 的存储引擎，那么大家可以试想一下，如果存放帖子的表中已经有上千万的帖子的时候，执行这条Query 语句需要多少成本？恐怕再好的硬件设备，恐怕都不可能在10秒之内完成一次查询吧 注：没有where的count(*)使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count(*)时它直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count(*)时一般要伴随where，且where中要包含主键以外的索引列。

既然这样查询不行，那我们是不是该专门为这个功能建一个表，就只有一个字段，一条记录，就存放这个统计量，每次有新的帖子产生的时候，都将这个值增加1，这样我们每次都只需要查询这个表就可以得到结果了，这个效率肯定能够满足要求了。确实，查询效率肯定能够满足要求，可是如果帖子产生很快，在高峰时期可能每秒就有几十甚至上百个帖子新增操作的时候，恐怕这个统计表又要成为大家的噩梦了。要么因为并发的问题造成统计结果的不准确，要么因为锁资源争用严重造成整体性能的大幅度下降。

其实这里问题的焦点不应该是实现这个功能的技术细节，而是在于这个功能的附加要求“实时更新”上面。当一个论坛的帖子数量很大了之后，到底有多少人会关注这个统计数据是否是实时变化的？有多少人在乎这个数据在短时间内的不精确性？恐怕不会有人会盯着这个统计数字并追究当自己发了一个帖子然后回头刷新页面发现这个统计数字没有加1 吧？所以只要去掉了这个“实时更新”的附加条件，就可以非常容易的实现这个功能了。就像之前所提到的那样，通过创建一个统计表，然后通过一个定时任务每隔一定时间段去更新一次里面的统计值，这样既可以解决统计值查询的效率问题，又可以保证不影响新发贴的效率，一举两得。

1.2 系统架构及实现的影响

所有数据都是适合在数据库中存放的吗？数据库为我们提供了太多的功能，反而让很多并不是太了解数据库的人错误的使用了数据库的很多并不是太擅长或者对性能影响很大的功能，最后却全部怪罪到数据库身上。

实际上，以下几类数据都是不适合在数据库中存放的：

  • 二进制多媒体数据

    这种数据主要包括图片，音频、视频和其他一些相关的二进制文件。将二进制多媒体数据存放在数据库中，一个问题是数据库空间资源耗用非常严重，另一个问题是这些数据的存储很消耗数据库主机的CPU 资源。这些数据的处理本不是数据库的优势，如果我们硬要将他们塞入数据库，肯定会造成数据库的处理资源消耗严重。

  • 超大文本数据

    对于5.0.3 之前的MySQL 版本，VARCHAR 类型的数据最长只能存放255 个字节，如果需要存储更长的文本数据到一个字段，我们就必须使用TEXT 类型（最大可存放64KB）的字段，甚至是更大的LONGTEXT 类型（最大4GB）。而TEXT类型数据的处理性能要远比VARCHAR 类型数据的处理性能低下很多。从5.0.3 版本开始，VARCHAR 类型的最大长度被调整到64KB 了，所以，超大文本数据存放在数据库中不仅会带来性能低下的问题，还会带来空间占用的浪费问题。
    是否合理的利用了应用层Cache 机制？
    对于Web 应用，活跃数据的数据量总是不会特别的大，有些活跃数据更是很少变化。对于这类数据，我们是否有必要每次需要的时候都到数据库中去查询呢？如果我们能够将变化相对较少的部分活跃数据通过应用层的Cache 机制Cache 到内存中，对性能的提升肯定是成数量级的，而且由于是活跃数据，对系统整体的性能影响也会很大。

1.3 查询语句对性能的影响

SQL语句的优劣是对性能有影响的，每个SQL 语句在优化之前和优化之后的性能差异也是各不相同。

在数据库管理软件中，最大的性能瓶颈就是在于磁盘IO，也就是数据的存取操作上面。而对于同一份数据，当我们以不同方式去寻找其中的某一点内容的时候，所需要读取的数据量可能会有天壤之别，所消耗的资源也自然是区别很大。

功能完全相同的两条SQL 的在性能方面的差异。

我们在执行sql语句时可以用explain来查看执行计划：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> explain 
    -> select stuid,stuname,cardid from tb1 where stuid between 3000 and 5000
    -> order by stuid desc
    -> limit 20 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3678
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

还可以打开mysql的profiling 功能，来查看sql的实际执行计划

1
2
3
mysql> set profiling=1;
mysql> select stuid,stuname,cardid from tb1 where stuid between 3000 and 5000 order by stuid desc limit 5\G
mysql> show  profile;

通过执行“SHOW PROFILE” 命令获取当前系统中保存的多个Query 的profile 的概要信息。

1
mysql> show profile CPU,BLOCK IO for query 1;

1.4 数据库Schema设计对性能的影响

1.5 硬件选择对性能的影响

首先，数据库主机是存取数据的地方，所以数据库主机的IO 性能肯定是需要最优先考虑的一个因素，这一点不管是什么类型的数据库应用都是适用的。在主机中决定IO 性能部件主要由磁盘和内存所决定，当然也包括各种与IO 相关的板卡。

其次，由于数据库主机和普通的应用程序服务器相比，资源要相对集中很多，单台主机上所需要进行的计算量自然也就比较多，所以数据库主机的CPU处理能力也不能忽视。
最后，由于数据库负责数据的存储，与各应用程序的交互中传递的数据量比其他各类服务器都要多，所以数据库主机的网络设备的性能也可能会成为系统的瓶颈。

所以，数据库应用系统的优化，实际上是一个需要多方面配合，多方面优化的才能产生根本性改善的事情。简单来说，可以通过下面三句话来简单的概括数据库应用系统的性能优化：商业需求合理化，系统架构最优化，逻辑实现精简化，硬件设施理性化。

2、MySQL性能优化之-索引

关于MySQL索引的好处，如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。

2.1 做一个简单测试

假如我们创建了一个tb1表，向表中插入20000行数据，表的创建和数据插入用如下脚本实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[root@localhost ~]# cat mysql3.sh 
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD="123.abc"

DBNAME="test1"
TABLENAME="tb1"

#create database
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"
create_db_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "${create_db_sql}"

#create table
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) not null,stusex char(1) not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100) default null)"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${create_table_sql}"

#insert data to table
i=1
while [ $i -le 20000 ]
do 
insert_sql="insert into ${TABLENAME} values ($i,'zhangsan','1','1234567890123456','1999-10-10','2016-9-3','zhongguo beijingshi changpinqu')"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"
let i++
done
#select data
select_sql="select count(*) from ${TABLENAME}"
mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"

再手动插入一行数据，如

下面开始测试，查询stuname=’admin’的记录

情况1：stuname列上没有创建索引的情况

1
mysql> explain select stuid,stuname,stusex,cardid,entertime from tb1 where stuname='admin'\G;

*** 1. row ***
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 18910
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.01 sec)

情况2：stuname列上创建索引的情况再查询

1
2
mysql> create index index_stuname on tb1(stuname);
mysql> explain select stuid,stuname,stusex,cardid,entertime from tb1 where stuname='admin'\G;

*** 1. row ***
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ref
possible_keys: index_stuname
key: index_stuname
key_len: 62
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)

在查找stuname=”admin”的记录时，如果在stuname上已经建立了索引，MySQL无须任何扫描全表，即准确可找到该记录。相反，MySQL会扫描所有记录。
所以在数据库表中，对字段建立索引可以大大提高查询速度。
索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。

2.2 索引概述

2.2.1 什么是索引？

索引（Index）是帮助MySQL高效获取数据的数据结构，这是一种比较官方的说法。它的存在形式是文件。索引能够帮助我们快速定位数据。 更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
索引的数据结构
这里主介绍B-tree索引的结构

image-20191116150701420

如上图，是一颗b+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项和指针，如17、35并不真实存在于数据表中。
b+树的查找过程
如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

为什么使用索引？
索引可以让mysql高效运行，可以大大提高mysql的查询（包括排序，分组）效率；数据约束（唯一索引的建立）。

索引给我带来什么好处？
提高查询效率，快速定位数据

使用索引产生的代价？

  1. 索引本身是以文件的形式存放在硬盘，需要的时候才加载至内存，所以添加索引会增加磁盘的开销；
  2. 写数据：需要更新索引，对数据库是个很大的开销，降低表更新、添加和删除的速度

不建议使用索引的情况：

  1. 表记录较少
  2. 索引的选择性较低，所谓索引的选择性，是指不重复的索引值与表记录数的比值，取值范围（0-1）。选择性越高，索引的价值越大。

2.2.2 索引的类型？

索引包括单列索引和组合索引
单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

  1. 普通索引
    这是最基本的索引，它没有任何限制
1
CREATE INDEX indexName ON tablename(column1[,column2,……])

  1. 唯一索引
    它与前面的普通索引类似，不同的就是索引列的值必须唯一，但允许空值，空值是指null。如果是组合索引，组合列的值必须唯一

    1
    CREATE UNIQUE INDEX indexName ON tablename(column1[,column2,……])

    主键索引：一种特殊的唯一索引，不允许有空值，一般在建表的时候同时建立主键索引

    1
    CREATE TABLE tablename(ID INT NOT NULL,  username VARCHAR(16) NOT NULL,  PRIMARY KEY(ID) );

  1. 组合索引
    为了进一步提升MySQL的效率，就要考虑建立组合索引
    例如：创建一个表，包含如下字段

    1
    CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL  );

    将 username, city, age建到一个索引里

    1
    CREATE INDEX username_city_age ON mytable(username,city,age);

    如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于组合索引。虽然此时有了三个索引，但MySQL只能用到其中那个它认为似乎是最有效率的单列索引。
    建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：
    usernname,city,age usernname,city usernname 为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

    1
    2
    SELECT * FROM mytable WHREE username="admin" AND city="郑州"  
    SELECT * FROM mytable WHREE username="admin"

    而下面几个则不会用到：

    1
    2
    SELECT * FROM mytable WHREE age=20 AND city="郑州"  
    SELECT * FROM mytable WHREE city="郑州"

如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。
例如：
假设存在组合索引index1(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即要想使用c2列进行查找，必需出现c1等于某值。因此我们在创建组合索引时应该将最常用作限制条件的列放在最左边，依次递减。

  1. 全文索引
    只用于MyISAM 表 对文本域进行索引。字段类型包括char、varchar、text
    不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

    1
    CREATE FULLTEXT INDEX indexname ON tablename(column)

查看索引

1
2
mysql> show index from tablename;
mysql> show keys from tablename;

2.3 建立索引的时机

到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？
一般来说，在WHERE和JOIN子句中出现的列需要建立索引，

例如：
代码如下:在username上创建索引

1
SELECT * FROM mytable WHREE username="admin" AND city="郑州"

代码如下:

1
SELECT t.Name  FROM mytable1 t LEFT JOIN mytable2 m   ON t.username=m.username;

此时就需要对两个表的userame上建立索引。
使用索引的注意事项
刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。

例如下句会使用索引：

1
SELECT * FROM mytable WHERE username like'admin%'

而下句就不会使用：

1
SELECT * FROM mytable WHERE Name like'%admin'

不要在列上进行运算,例如

1
select * from users where YEAR(adddate)<2007;

这将导致索引失效而进行全表扫描，因此我们可以改成

1
select * from users where adddate<'2007-01-01';

2.4 总结

选择索引列：

  1. 性能优化过程中，选择在哪个列上创建索引是最重要的步骤之一。可以考虑使用索引 的主要有 两种类型的列：在where子句中出现的列，在join子句中出现的列。
  2. 考虑列中值的分布，索引的列的基数越大，索引的效果越好。
  3. 使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度。
    例如：CREATE INDEX username_city_age ON mytable(username（10）,city,age);
    建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。
  4. 利用最左前缀
  5. 不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。 在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,
以及某些时候的like(不以通配符%或_开头的情形)。
以上就是有关Mysql索引的相关理论介绍，下面我们来学习如何慢查询分析、优化索引和配置

3、mysql性能优化-慢查询分析、优化索引和配置

3.1 基本思路

  1. 性能瓶颈定位
    Show命令
    慢查询日志
    explain分析查询
    profiling分析查询
  2. 索引及查询优化
  3. 配置优化

MySQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈，CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候，如果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。
除了服务器硬件的性能瓶颈，对于MySQL系统本身，我们可以使用工具来优化数据库的性能，通常有三种：使用索引，使用EXPLAIN分析查询以及调整MySQL的内部配置。

3.2 查询与索引优化分析

在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。
性能瓶颈定位
show命令
可以通过show命令查看MySQL状态及变量，找到系统的瓶颈：
查看MySQL服务器配置信息

1
mysql> show variables;

查看MySQL服务器运行的各种状态值

1
mysql> show global status;
1
2
# mysqladmin variables -u username -ppassword    //显示系统变量
# mysqladmin extended-status -u username -ppassword   //显示状态信息

比较全的show命令的使用可参考：

1
mysql> help show

或 http://dev.mysql.com/doc/refman/5.7/en/show.html

3.2.1 慢查询日志

慢查询日志开启：
在配置文件my.cnf中在[mysqld]一行下面加入3个配置参数，并重启mysql服务

1
2
3
slow_query_log = 1    //0关闭  1开启
slow_query_log_file = /usr/local/mysql/data/slow-query.log    //慢查询日志存放地点
long_query_time = 1                               //表示查询超过1秒才记录

在my.cnf中添加log-queries-not-using-indexes参数，表示向慢查询日志中记录没有使用索引的查询。

慢查询日志开启方法二：
我们也可以通过命令行设置变量来即时启动慢日志查询

1
2
3
4
5
6
7
mysql> set global slow_query_log = on;
mysql> set long_query_time = 0.01;
mysql> set global slow_query_log_file = "/usr/local/mysql/data/slow-query.log";

查看慢查询的设置信息
mysql> show variables like '%slow_query_log%';
mysql> show variables like '%long_query_time%';

我们可以通过打开log文件查看得知哪些SQL执行效率低下

1
[root@localhost data]# cat slow-query.log

Time: 2016-09-06T14:17:12.582189Z
User@Host: root[root] @ localhost [] Id: 3
Query_time: 0.008316 Lock_time: 0.000304 Rows_sent: 1 Rows_examined: 20002
SET timestamp=1473171432;
select * from test1.tb1 where stusex=’0’; //没有使用索引的query
Time: 2016-09-06T15:54:42.648291Z
User@Host: root[root] @ localhost [] Id: 14
Query_time: 0.017319 Lock_time: 0.000612 Rows_sent: 1 Rows_examined: 20001
SET timestamp=1473177282;
select * from test1.tb1 where entertime <> ‘2016-9-3’; //慢查询query

从日志中，可以发现查询时间超过0.01 秒的SQL，而小于0.01秒的没有出现在此日志中。

如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。

有关mysqldumpslow命令的用法可以参考其帮助：mysqldumpslow --help

1
[root@localhost data]# mysqldumpslow -a -s at -r slow-query.log

Reading mysql slow query log from slow-query.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts
Time: 2016-09-06T15:54:42.648291Z
#User@Host: root[root] @ localhost [] Id: 14
#Query_time: 0.017319 Lock_time: 0.000612 Rows_sent: 1 Rows_examined: 20001
SET timestamp=1473177282;
select * from test1.tb1 where entertime <> ‘2016-9-3’

上面显示结果中就是一条慢查询，如何优化呢？

一是在entertime列上创建索引优化查询

1
mysql> create index index_entertime on test1.tb1(entertime);

二是优化这个sql查询语句

1
mysql> select * from test1.tb1 where entertime < '2016-9-3' or entertime > '2016-9-3';

从下图可以看查询0.00秒

image-20191116152914565

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。

3.2.2 explain分析查询

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> explain select * from test1.tb1 where stuname='admin'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb1
   partitions: NULL
         type: ALL      //全表扫描
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 19986
     filtered: 10.00
        Extra: Using where    //使用where过滤数据
1 row in set, 1 warning (0.00 sec)

EXPLAIN字段含义：

字段描述
Table显示这一行的数据是关于哪张表的
type这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL
possible_keys显示可能应用在这张表中的索引。如果为空，没有可能的索引
key实际使用的索引。如果为NULL，则没有使用索引
key_len使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref显示索引的哪一列被使用了，如果可能的话，是一个常数
rowsMySQL认为必须检索的用来返回请求数据的行数
Extra关于MYSQL如何解析查询的额外信息

从上面的explain模拟优化器执行sql语句来看是没有使用索引查询的，而是全表扫描

优化方法：在stuname列上创建索引

1
mysql> create index index_stuname on test1.tb1(stuname);

再次执行explain

1
mysql> explain select * from test1.tb1 where stuname='admin'\G;

*** 1. row ***
id: 1
select_type: SIMPLE
table: tb1
partitions: NULL
type: ref
possible_keys: index_stuname
key: index_stuname
key_len: 62
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)

显示结果说明该查询语句使用了index_stuname索引查询数据而非全表扫描。

3.2.3 profiling分析查询

通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。

profiling默认是关闭的。可以通过以下语句查看

1
mysql> show variables like '%profiling%';    //off表示未开启

image-20191116153727442

或

1
mysql> select @@profiling;     //0表示未开启

image-20191116153812831

打开profiling功能：

1
2
3
4
5
6
7
8
9
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@profiling;
+---------------------+
| @@profiling  |
+---------------------+
|           1 |
+----------------------+

执行要测试的sql语句

1
2
mysql> select * from test1.tb1 where stuname='admin' and entertime='2016-9-1';
mysql> show profiles\G;   //可以得到被执行的SQL语句的时间和ID

*** 1. row ***
Query_ID: 1
Duration: 0.00012650
Query: select @@profiling
*** 2. row ***
Query_ID: 2
Duration: 0.00121725
Query: select * from test1.tb1 where stuname=’admin’ and entertime=’2016-9-1’

1
mysql> show profile for query 2;  //得到对应SQL语句执行的详细信息

+———————-+————————-+
| Status | Duration |
+———————-+————————-+
| starting | 0.000230 |
| checking permissions | 0.000013 |
| Opening tables | 0.000030 |
| init | 0.000087 |
| System lock | 0.000018 |
| optimizing | 0.000128 |
| statistics | 0.000378 |
| preparing | 0.000026 |
| executing | 0.000005 |
| Sending data | 0.000187 |
| end | 0.000013 |
| query end | 0.000011 |
| closing tables | 0.000010 |
| freeing items | 0.000061 |
| cleaning up | 0.000021 |
+———————-+————————-+

status:是profile里的状态，duration：是status状态下的耗时。因此我们关注的就是那个状态最耗时，这些状态中那些可以优化。

当然也可以查看更多的信息如CPU等等

SHOW PROFILE [type [, type] … ] [FOR QUERY n]
type:
ALL：显示所有的开销信息
BLOCK IO：显示块IO相关开销
CPU：显示用户CPU时间、系统CPU时间
IPC：显示发送和接收相关开销信息
PAGE FAULTS：显示页面错误相关开销信息
SWAPS：显示交换次数相关开销的信息

测试完成之以后，记得要关闭调试功能，以免影响数据库的正常使用：

1
mysql> set profiling=0;

3.3 配置优化

Mysql参数优化对于不同的网站，及其在线量，访问量，帖子数量，网络情况，以及机器硬件配置都有关系，优化不可能一次性完成，需要不断的观察以及调试，才有可能得到最佳效果。
下面列出了对性能优化影响较大的主要变量，主要分为连接请求的变量和缓冲区变量

3.3.1 连接请求的变量

3.3.1.1 max_connections

MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多， MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
数值过小会经常出现ERROR 1040: Too many connections错误，可以过mysql> show status like ‘connections’;通配符查看当前状态的连接数量(试图连接到MySQL(不管是否连接成功)的连接数)，以定夺该值的大小。

1
2
3
mysql> show variables like 'max_connections';   //最大连接数
mysql> show global status like 'max_used_connections';   //响应的连接数
max_used_connections / max_connections * 100% （理想值≈ 85%）

如果max_used_connections跟max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。

如何设置max_connections?

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容，如设置最大连接数为1024

1
max_connections = 1024

重启mysql服务

3.3.1.2 back_log

MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，它就会起作用。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它。
当观察你主机进程列表（mysql> show full processlist;），发现大量
xxxxx | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大back_log 的值了或加大max_connections的值。
通过

1
mysql> show variables like 'back_log';

查看back_log的设置
如何设置back_log?
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容，如设置最大连接数为1024

1
back_log = 1024

重启mysql服务

3.3.1.3 wait_timeout 和 interactive_timeout

wait_timeout – 指的是MySQL在关闭一个非交互的连接之前所要等待的秒数
interactive_time – 指的是mysql在关闭一个交互的连接之前所要等待的秒数，比如我们在终端上进入mysql管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了interactive_time设置的时间就会自动断开。默认数值是28800，可调优为7200。

对性能的影响：
wait_timeout：

  1. 如果设置大小，那么连接关闭的很快，从而使一些持久的连接不起作用
  2. 如果设置太大，容易造成连接打开时间过长，在show processlist时，能看到太多的sleep状态的连接，从而造成too many connections错误
  3. 一般希望wait_timeout尽可能地低

interactive_timeout 的设置将要对你的web application没有多大的影响
查看wait_timeout和interactive_timeout

1
2
mysql> show variables like '%wait_timeout%';
mysql> show variables like '%interactive_timeout%';

如何设置wait_timeout和interactive_timeout?
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

1
2
wait_timeout=7200
interactive_timeout=7200

重启MySQL Server进入后，查看设置已经生效。

3.3.2 绶冲区变量

全局缓冲：

3.3.2.1 key_buffer_size

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE 'key_read%';获得）。

img

一共有6个索引读取请求，有3个请求在内存中没有找到直接从硬盘读取索引，计算索引

未命中缓存的概率：

key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =50%

key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

image-20191116155703606

如何调整key_buffer_size
默认配置数值是8388608(8M)，主机有4GB内存，可以调优值为268435456(256MB)
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

1
2
3
key_buffer_size=268435456
或 
key_buffer_size=256M

重启MySQL Server进入后，查看设置已经生效。

3.3.2.2 query_cache_size(查询缓存简称QC)

使用查询缓冲，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。
一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用查询缓存。
注：两个SQL语句，只要相差哪怕是一个字符（例如大小写不一样；多一个空格等），那么这两个SQL将使用不同的一个CACHE。

通过检查状态值'Qcache%'，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOW STATUS LIKE 'Qcache%';获得）。

img

Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示较大，则说明Query Cache 中的内存碎片较多了，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。

注：当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks
Qcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察出当前系统中的Query Cache 内存大小是否足够，是需要增加还是过多了。
Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
Qcache_inserts：表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
Qcache_lowmem_prunes：多少条Query 因为内存不足而被清除出Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的free_blocks和free_memory可以告诉您属于哪种情况）
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前Query Cache 中cache 的Query 数量；
Qcache_total_blocks：当前Query Cache 中的block 数量；。

我们再查询一下服务器关于query_cache的配置：

img

上图可以看出query_cache_type为off表示不缓存任何查询

各字段的解释：
query_cache_limit：超过此大小的查询将不缓存
query_cache_min_res_unit：缓存块的最小大小 ，query_cache_min_res_unit的配置是一柄双刃剑，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
query_cache_size：查询缓存大小 (注：QC存储的最小单位是1024 byte，所以如果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。)
query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字，可选项目以及说明如下：

img

如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。
如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。
如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。
修改/etc/my.cnf,配置完后的部分文件如下：

1
2
query_cache_size=256M
query_cache_type=1

保存文件，重新启动MYSQL服务，然后通过如下查询来验证是否真正开启了：

img

img

query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。
查询缓存命中率 = Qcache_hits/(Qcache_hits +Qcache_inserts) * 100%

Query Cache 的限制

  1. 所有子查询中的外部查询SQL 不能被Cache；
  2. 在Procedure，Function 以及Trigger 中的Query 不能被Cache；
  3. 包含其他很多每次执行可能得到不一样结果的函数的Query不能被Cache。
    鉴于上面的这些限制，在使用Query Cache 的过程中，建议通过精确设置的方式来使用，仅仅让合适的表的数据可以进入Query Cache，仅仅让某些Query的查询结果被Cache。

如何设置query_cache_size？

修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

1
2
query_cache_size=256M
query_cache_type=1

重启MySQL Server进入后，查看设置已经生效。

3.3.2.3 max_connect_errors

max_connect_errors是一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况, 当超过指定次数，MYSQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。max_connect_errors的值与性能并无太大关系。
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

1
max_connect_errors=20

重启MySQL Server进入后，查看设置已经生效。

3.3.2.4 sort_buffer_size

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。
Sort_Buffer_Size 是一个connection级参数，在每个connection（session）第一次需要使用这个buffer的时候，一次性分配设置的内存。
Sort_Buffer_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。例如：500个连接将会消耗 500*sort_buffer_size(2M)=1G内存
例如设置sort_buffer_size
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

1
sort_buffer_size = 2M

重启MySQL Server进入后，查看设置已经生效。

3.3.2.5 max_allowed_packet = 32M

MySQL根据配置文件会限制Server接受的数据包大小。有时候大的插入和更新会受 max_allowed_packet 参数限制，导致写入或者更新失败。最大值是1GB，必须设置1024的倍数。

3.3.2.6 join_buffer_size = 2M

用于表间关联缓存的大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

3.3.2.7 thread_cache_size = 300

服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比 较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。设置规则如下：1GB 内存配置为8，2GB配 置为16，3GB配置为32，4GB或更高内存，可配置更大。服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

image-20191116160759133

试图连接到MySQL(不管是否连接成功)的连接数

image-20191116160810843

Threads_cached: *代表当前此时此刻线程缓存中有多少空闲线程。
*Threads_connected: 代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created: 代表从最近一次服务启动，已创建线程的数量，如果发现Threads_created值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值。
Threads_running: 代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。

3.3.3 配置InnoDB的几个变量

3.3.3.1 innodb_buffer_pool_size

对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了。

1
mysql> show variables like 'innodb_buffer_pool_size';

设置innodb_buffer_pool_size
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容

1
nnodb_buffer_pool_size = 2048M

重启MySQL Server进入后，查看设置已经生效。

3.3.3.2 innodb_flush_log_at_trx_commit

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。0，表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；1，则在每秒钟或是每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；设置为2，每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘操作。
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。
根据MySQL手册，在允许丢失最近部分事务的危险的前提下，可以把该值设为0或2。

3.3.3.3 innodb_thread_concurrency = 0

此参数用来设置innodb线程的并发数量，默认值为0表示不限制，若要设置则与服务器的CPU核数相同或是cpu的核数的2倍，建议用默认设置，一般为8.

3.3.3.4 innodb_log_buffer_size

此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。

1
innodb_log_buffer_size=32M
3.3.3.5 innodb_log_file_size = 50M

此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.

3.3.3.6 innodb_log_files_in_group = 3

为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3

3.3.3.7 read_buffer_size = 1M

MySql 读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

3.3.3.8 read_rnd_buffer_size = 16M

MySql 的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

3.3.3.9 bulk_insert_buffer_size = 64M

批量插入数据缓存大小，可以有效提高插入效率，默认为8M

3.3.3.10 binary log
1
log-bin=/usr/local/mysql/data/mysql-bin

binlog_cache_size = 2M //为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是–1M，后者建议是：即 2–4M
max_binlog_cache_size = 8M //表示的是binlog 能够使用的最大cache 内存大小
max_binlog_size = 512M //指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于1GB或小于4096字节。 默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
expire_logs_days = 7 //定义了mysql清除过期日志的时间。
二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。
mysqladmin flush-logs 也可以重新开始新的binary log

在优化之前执行mysqlslap工具进行测试

1
[root@localhost ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 --iterations=1 --create-schema='test1' --query='select * from test1.tb1' --engine=innodb --number-of-queries=2000 -uroot -p123456 –verbose

显示结果：

1
2
3
4
5
6
7
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 13.837 seconds
	Minimum number of seconds to run all queries: 13.837 seconds
	Maximum number of seconds to run all queries: 13.837 seconds
	Number of clients running queries: 10
	Average number of queries per client: 200

优化之后执行mysqlslap工具进行测试

1
[root@localhost ~]#mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 --iterations=1 --create-schema='test1' --query='select * from test1.tb1' --engine=innodb --number-of-queries=2000 -uroot -p123456 –verbose

显示结果：

1
2
3
4
5
6
7
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 4.199 seconds
	Minimum number of seconds to run all queries: 4.199 seconds
	Maximum number of seconds to run all queries: 4.199 seconds
	Number of clients running queries: 10
	Average number of queries per client: 200

相关优化参数总结：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[mysqld]
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/data/slow-query.log
long_query_time = 1
log-queries-not-using-indexes
max_connections = 1024
back_log = 128
wait_timeout = 60
interactive_timeout = 7200
key_buffer_size=256M
query_cache_size = 256M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size = 2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size = 2048M
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
log-bin=mysql-bin
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=64M
log-error = /usr/local/mysql/data/mysqld.err
-------------本文结束感谢您的阅读-------------
分享到： 收藏夹 复制网址 邮件 微信 QQ空间 腾讯微博 豆瓣 一键分享 更多