苏苏的博客

简约至极

MySQL性能优化

2016/01/11 12:02 Mon

docker run mysqld

docker run mysql –character-set-server=utf8mb4 –collation-server=utf8mb4_general_ci

配置文件

配置文件一般放在/etc/my.cnf 或者 /etc/mysql/my.cnf

默认的配置文件示例存放于/usr/share/mysql

各个文件适配不同内存的配置

文件 内存大小
my-small.cnf <= 64M 不经常开启mysql
my-medium.cnf 32M–64M 经常其他程序搭配mysql
my-large.cnf 512M
my-huge.cnf 1G-2G
my-innodb-heavy-4G.cnf 4GB 使用InnoDB

使用SHOW VARIABLES来查看系统参数,通过SHOW STATUS来判断系统状态

show 命令还有很多用途

命令 描述
show databases 或show tables from database_name 显示mysql中所有数据库的名称
show tables 显示当前数据库中所有表的名称
show columns from table_name from database_name 显示表中列名称
show grants for user_name 显示一个用户的权限，显示结果类似于grant命令
show table status 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。
show index from table_name 显示表的索引
show status 显示一些系统特定资源的信息,例如,正在运行的线程数量
show variables 显示系统变量的名称和值
show processlist 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码
show engies 显示安装以后可用的存储引擎和默认引擎
show innodb status 显示innoDB存储引擎的状态
show logs 显示BDB存储引擎的日志
show warnings 显示最后一个执行的语句所产生的错误、警告和通知
show errors 只显示最后一个执行语句所产生的错误

小内存优化

依据my-small.cnf为模板

主要指标:存储引擎,key_buffer_size,table_cache,max_connections,thread_concurrency,query_cache,thread_stack

存储引擎: 如果不使用 BDB table 和 InnoDB table 的话，加入下面2行关闭不需要的表类型很有必要，关闭 innodb 可以省下大量内存，虽然 InnoDB 好处多多但是在一个64MB的 VPS 上并不能体现出来，并且很占内存。

key_buffer_size: 用来缓存 tables keys 和 indexes，增加这个值可以更好的处理索引，读和写都需要索引,这里设置成16K足够了.优化性能需要key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好,使用show status like 'key_%';查看

table_cache: (5.1.3及以后版本又名TABLE_OPEN_CACHE) 所有线程打开的表的数量，增加值可以增大 MySQL 的文件描述符数量，避免频繁的打开表，原始 my-small.cnf 中 table_cache 设置成4有点小，一个 wordpress 的页面通常会涉及到10个左右的表，其他的程序比如 Drupal，MediaWiki 会涉及到更多，将table_cache改为8。

max_connections: 数据库最大的连接数量，可以根据自己博客/网站的访问量来定这个值,如果博客/网站经常出现：Too many connections 错误的信息说明需要增大 max_connections 的值.

thread_concurrency: 最大并发线程数，通常设置为 CPU核数量×2，在 VPS 宿主机上如果服务器有2颗物理 CPU，而每颗物理 CPU 又支持 H.T 超线程（一个处理器上整合了两个逻辑处理器单元），所以实际取值为4 × 2 ＝ 8。 如果我们在优化 php.ini 的时候设置了同时只有2个 php-cgi 运行的话，那么我们也应该只设置2个 MySQL 线程同时运行,设置过大导致线程之间的频繁切换。

这个参数在mysql 5.6.1中已经被标记为过时，在5.7.2版本的mysql中被移除

query_cache: 对于博客/新闻网站来说，用得最多的就是查询，所以需要加入 query cache 的设置。query_cache_size 是执行查询所使用的缓冲大小。 query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M,结果集超过这个大小将不会被缓存。

与查询缓存有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。

query_cache_type指定是否使用查询缓存，可以设置为0、1、2,分别代表了off、on、demand,如果是0，那么query cache 是关闭的。如果是1，那么查询总是先到查询缓存中查找，除非使用了sql_no_cache。如果是2，那么，只有使用 sql_cache的查询，才会去查询缓存中查找.

query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。

query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。

检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小 query_cache_min_res_unit。

thread_stack: 每个线程被创建的时候,mysql分配给它的内存,每个连接使用一个线程,用来存放每个线程的标识信息，如线程id，线程运行时环境等。默认294912(288KB)

sort_buffer_size: 每个需要排序的线程分配的缓冲区大小，增加该值可以加速 order by 和 group by 的操作。注意：该参数是以每个连接分配内存，也就是说，如果有16个连接，sort_buffer_size 为 64K，那么实际分配的内存为：16 × 64K = 1MB。如果设置的缓存大小无法满足需要，MySQL 会将数据写入磁盘来完成排序。因为磁盘操作和内存操作不在一个数量级，所以 sort_buffer_size 对排序的性能影响很大。

read_buffer_size: 顺序读取数据时的缓冲区大小，与 sort_buffer_size 一样，该参数分配的内存也是以每连接为单位的。 当需要顺序读取数据的时候，如无法使用索引的情况下的全表扫描，全索引扫描等。在这种时候，MySQL 按照数据的存储顺序依次读取数据块，每次读取的数据快首先会暂存在 read_buffer_size 中，当 buffer 空间被写满或者全部数据读取结束后，再将 buffer 中的数据返回给上层调用者，以提高效率。

read_rnd_buffer_size: 随机读取数据时的缓冲区大小，与顺序读相对应。

net_buffer_size: 用来存放客户端连接线程的连接信息和返回客户端的结果集的缓存大小。当 MySQL 接到请求后，产生返回结果集时，会在返回给请求线程之前暂存在在这个缓存中，等积累到一定大小的时候才开始向客户端发送，以提高网络效率。不 过，net_buffer_size 所设置的仅仅只是初始大小，MySQL 会根据实际需要自行申请更多的内存，但最大不会超过 max_allowed_packet。

skip-locking用来避免 MySQL 外部锁定，减少出错几率，增强稳定性。

thread_cache_size 优化配置

Thread_Cache是mysql自己维护的连接池,将空闲的连接线程放在连接池中,而不是立即销毁.这样的好处就是,当又有一个新的请求的时候,mysql不会立即去创建连接 线程,而是先去Thread_Cache中去查找空闲的连接线程,如果存在则直接使用,不存在才创建新的连接线程.

thread_cache_size为连接池里最大连接线程数,在短连接的应用中Thread_Cache的功效非常明显,因为在应用中数据库的连接和创建是非常频繁的,如果不使用 Thread_Cache那么消耗的资源是非常可观的!在长连接中虽然带来的改善没有短连接的那么明显,但是好处是显而易见的.但并不是越大越好大了反而浪费资源 依据内存

1G  ---> 8
2G  ---> 16
3G  ---> 32
4G  ---> 64

show variables like 'thread%';show variables like "max_connections";show status like '%connections%';show status like 'thread%';

表3中Connections表示服务器总共服务了多少次连接,Max_used_connections为实际达到的最大并发连接数

表4中Threads_cached是处于连接池中的连接数(休眠的),从表1可以知道连接池允许的最大数量(thread_cache_size)

Threads_connected为当前的连接数,也可以从show full processlist;得知当前的连接数,他的最大值即为Max_used_connections

Threads_running为此时刻正在进行SQL操作的线程,这个值必定是小于等于Threads_connected

Threads_created为总共执行了多少次创建线程的操作,该数定大于等于Max_used_connections,一个线程创建后用于服务,客户端断开后并不会被销毁,而是放入连接池(只有连接池的数量大于设定值才会被抛弃),当创建的时候也会检查连接池内有没有,没有才会创建新的.

配置项max_connections为允许的最大并发连接数,也可以依据Max_used_connections作为参考,增加该值不会占用系统资源,只有实际使用到的才会占用,但设置过小会出现Too many connections,默认值为100,须大于Max_used_connections,根据网站的并发量来设定.

每一个连接都需要一个线程服务,每个线程初始分配thread_stack字节的内存,连接池的利用率为

Thread_Cache_Hit=(Connections-Thread_created)/Connections*100%

即尽量减少线程创建操作.

show variables like '%timeout%';

wait_timeout参数有时也是什么有用,该值为客户端已连接但是未活动,最多等待的秒数,超过此时间将主动断开连接.默认是28800(8小时)

https://www.cnblogs.com/ivictor/p/5979731.html

执行show full processlist;可以查看所有连接的状态及空闲时间等.

大量长期Sleep的连接,也是在白白消耗内存,该数量被记为Threads_connected,如果大量创建连接而又不断开不使用,会导致改值增加,达到max_connections上限后会报错too many connections

要改变他,要同时设置interactive_timeout和wait_timeout才会生效

以上: 64M VPS数据库配置

skip-bdb
skip-innodb

key_buffer_size = 16K
table_cache = 8

thread_stack = 64K

max_connections = 16
thread_cache_size = 8
thread_concurrency = 2

query_cache_type = 1
query_cache_size = 4M
query_cache_limit = 128K
query_cache_strip_comments = 1

sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
skip-locking

wait_timeout=3600
interactive_timeout=3600

max_allowed_packet = 8M

为性能的优化配置

innodb_buffer_pool_size = 128M
tmp_table_size=64M
key_buffer_size=256M
read_buffer_size=4M
read_rnd_buffer_size=16M
sort_buffer_size=32M
join_buffer_size = 32M
bulk_insert_buffer_size = 32M
myisam_sort_buffer_size = 128M
max_allowed_packet = 32M

如果你的服务器内存较大(4G或更多),专门来跑mysql,可设置key_buffer_size为内存的一半 key_buffer_size = 2048M

连接的并发量不大,还有多余内存可以加大join_buffer_size和sort_buffer_size

sort_buffer_size = 64M
join_buffer_size = 64M

Packet for query is too large

show variables like '%max%'

当update或者insert的内容过大时,max_allowed_packet的值过小,会引起sql异常,需要改大;其默认值为1048576(1M)，

修改my.cnf max_allowed_packet = 100M

或者零时动态设置 set global max_allowed_packet = 104857600;

查看是否生效,零时修改一般断开后重新连接即可生效. show variables like '%max_allowed_packet%';

数据库优化

查看query_cache相关配置, 默认这个开关是关闭的,就是禁止使用query_cache query_cache_type 为0,是关闭的

查看当前的配置 show variables like '%query_cache%';

单独查询一个可以使用 select @@query_cache_type;

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 262144   |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

上述开启query_cache 32MB

(OFF)如果设置为0，那么可以说，你的缓存根本就没有用，相当于禁用了。

(ON)如果设置为1，将会缓存所有的结果，除非你的select语句使用SQL_NO_CACHE禁用了查询缓存。

(DEMAND)如果设置为2，则只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。

query_cache_size为0,没有分配缓存空间,如果配置了query_cache_size = 4M,这里会显示具体的字节

查看配置及运行的状态 show global variables like '%query_cache%';show global status like '%Qcache%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 1        |
| Qcache_free_memory      | 26444248 |
| Qcache_hits             | 159619   |
| Qcache_inserts          | 27496    |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6922     |
| Qcache_total_blocks     | 13850    |
+-------------------------+----------+

如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况

如果Qcache_hits的值也非常大，则表明查询缓存使用非常频繁，此时需要增加缓冲大小；

如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓存反而会影响效率，那么可以考虑不用查询缓存。

此外，在SELECT语句中加入SQL_NO_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可以执行如下,注意Query Cache对于读多写少的场景才会有优势

SET GLOBAL query_cache_type = 1;
SET GLOBAL query_cache_size = 16777216;
SET GLOBAL query_cache_limit = 262144;
SET GLOBAL query_cache_strip_comments = 1;

query_cache_type:开启缓存

query_cache_size:总的缓存大小16Mb

query_cache_limit:结果集超过256KB则不缓存

各个数据库引擎对比

主要讨论引擎: MyISAM存储引擎 InnoDB存储引擎 Memory存储引擎

MyISAM存储引擎

支持B-tree/FullText/R-tree索引类型 锁级别是表锁,表锁的开销小，加锁快;锁粒度大，发生锁冲突的概率较高，并发度低；表锁适合查询 不支持事务性，也不支持外键。

并发不高，不需要支持事务,需要进行全文搜索,读次数大于写次数,适合数据量不是特别大并发不太高的大部分场合

InnoDB存储引擎

支持事务性，支持回滚,支持Hash/B-tree索引类型 锁级别是行锁，行锁在锁定上带来的消耗大于表锁，但是在系统并发访问量较高时，InnoDB整体性能远高于MyISAM。 InnoDB的索引不仅缓存索引本身，也缓存数据，所以InnoDB需要更大的内存。

表数据量超过千万，高并发,频繁更新大字段,安全性和可用性要求高,更适合与大并发大数据量的场合，除了支持事务，在高并发时行级锁的优势就会发挥出来。

Memory存储引擎

内存级的存储引擎，它将所有数据都存储在内存中，所以它能够存储的数据量是比较小的 Memory的锁级别和MyISAM一样，是表锁；并且不支持事务性。

适合与性能要求高数据量小的地方，和缓存的效果类似。

innodb查询表的行数需要全表扫描，速度会非常慢，查询1千万行数据的表最多时要6、7s,而myisam因为保存了总行数是极快的。 在一个进程操作的情况下，myisam的更新和查询速度都会稍快于innodb。memory引擎插入和查询修改的速度都极快，但支持的数据量有限,不支持BLOB/TEXT数据类型,适用于单表几万行数据.

MEMORY表最大值受系统变量 max_heap_table_size 限制，默认为16MB，要改变MEMORY表大小限制，需要改变max_heap_table_size 的值。 MEMORY表写入性能不如InnoDB,读取优于Redis, 更多相关阅读 https://www.v2ex.com/t/303137

锁

读锁：读操作时增加锁，叫共享锁，S-lock特征是阻塞其他客户端的写操作，不阻塞读操作

写锁：写操作时增加锁，叫独占锁或排他锁，X-lock特征是阻塞其他客户端的读、写操作

锁定粒度（范围）

行级：提升并发性，锁定开销大

表级：不利于并发性，锁定开销小

show global status like '%cost%'; 查看上一次查询的代价

show global variables like 'long_query_time'; 查看长查询设定的时间

show global status like 'slow_queries'; –查看查询时间超过long_query_time秒的查询的个数。

分页优化

http://www.cnblogs.com/nnhy/p/BigData.html#!comments 37楼

13456971 行数据 (一千三百万) select * from orderpack order by id limit 13456901,20; 查询约 31.22 秒

修改为2条查询语句

第一条 select id from orderpack order by id limit 13456901,1; 12.80 秒

根据查出来的首条数据的ID,然后查询该页的数据

select * from orderpack where id > 1251083 order by id limit 20; 0.01秒

性能能提升3倍

查看最近执行的sql

  1. If general mysql logging is enabled then we can check the queries in the log file or table based what we have mentioned in the config. Check what is enabled with the following command

mysql> show variables like ‘general_log%'; mysql> show variables like ‘log_output%'; If we need query history in table then

Execute SET GLOBAL log_output = ‘TABLE’; Execute SET GLOBAL general_log = ‘ON’; Take a look at the table mysql.general_log

If you prefer to output to a file:

SET GLOBAL log_output = “FILE”; which is set by default. SET GLOBAL general_log_file = “/path/to/your/logfile.log”; SET GLOBAL general_log = ‘ON’; 2) We can also check the queries in the .mysql_history file cat ~/.mysql_history

例子

TRUNCATE TABLE mysql.general_log;
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
SELECT SLEEP(60);
SET GLOBAL general_log = 'OFF';

使用抓包工具tcpdump也可以查看（使用root,或者sudo）

tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

使用tcpdump

sudo tcpdump -i any -s 0 -l -w - tcp and host x.x.x.x and dst port 3306 | strings

使用ngrep

sudo ngrep -W byline -q -d any '' tcp and host x.x.x.x and dst port 3306

还可以用tcpdump 记录数据用于 分析 slowlog

https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html

dump mysql query , pg query

https://github.com/k1LoW/tcpdp

ibdata1文件过大

  1. 需要指定innodb_file_per_table参数,单独保存每个表的数据,否则MySQL的数据都会存放在ibdata1文件里，文件只增加不减小,时间久了这个文件就会变的非常大。

show variables like '%innodb%';查看目前innodb的配置, ON为开启.否则会造成都存储到一个文件内

需要给my.cnf添加一行innodb_file_per_table,记得这个需要在[mysqld]段下面

  1. 可能有长时间执行但未提交的事务.

解决方法可参见 http://blog.fens.me/mysql-ibdata1/

sql

mysql导出数据

导出全部数据

USER=kris
PASS=myPassword
HOST=database-server.mydomain.com

# Databases to exclude
DBEXCLUDE=db_name_i_dont_want

MYSQL=$(mysql -N -u${USER} -p${PASS} -h${HOST} <<<"SHOW DATABASES" | grep -v ${DBEXCLUDE} | grep -v mysql | grep -v information_schema | grep -v test | tr "\n" " ")

mysqldump -v -u${USER} -p${PASS} -h${HOST} --databases  --skip-lock-tables ${MYSQL} > DB-DUMP.sql

mysqldump -uroot -p --all-databases | gzip > sqlfile.sql.gz

mysqldump -uroot -p --databases bureau_datasix_c caascms cas indoor_positioning jeecms ourcat test ucenter | gzip > backup.sql.gz

命令行下具体用法如下： mysqldump -u用戶名 -p密码 -d 數據库名 表名 脚本名;

1、导出數據库為dbname的表结构（其中用戶名為root,密码為dbpasswd,生成的脚本名為db.sql） mysqldump -uroot -pdbpasswd -d dbname >db.sql;

2、导出數據库為dbname某张表(test)结构 mysqldump -uroot -pdbpasswd -d dbname test>db.sql;

3、导出數據库為dbname所有表结构及表數據（不加-d） mysqldump -uroot -pdbpasswd dbname >db.sql;

4、导出數據库為dbname某张表(test)结构及表數據（不加-d） mysqldump -uroot -pdbpasswd dbname test>db.sql;

5.导出数据中dbname多张表（test1,test2,test3）结构及表数据用用空格隔开

mysqldump -uroot -pdbpasswd dbname test1 test2 test3>db.sql;

导出多个数据库,使用 mysqldump -uroot -pdbpassword –databases db1 db2

使用gzip压缩

导出时: mysqldump -uroot -pdbpasswd dbname | gzip > backupfile.sql.gz

导入时: gunzip < backupfile.sql.gz | mysql -uroot -pdbpasswd dbname

使用xz压缩

使用xz压缩会大幅增加导出时间

导出时: mysqldump -uroot -pdbpasswd dbname | xz > backupfile.sql.xz

导入时: unxz -c backupfile.sql.xz | mysql -uroot -pdbpasswd dbname

gzip和xz压缩时后面都可以使用参数-9加大压缩率.但也更耗时.(如果磁盘够用,请不要使用,非常耗时)

同理还可以使用bzip2和bzip2 -dc压缩和解压

DELAYED仅适用于MyISAM, MEMORY和ARCHIVE表

replace delayed into

insert delayed into

insert delayed ignore into

查看数据库或某个表的磁盘占用.

https://www.oschina.net/question/12_3673

mysql 中 localhost与127.0.0.1的区别

  1. localhost不会解析成ip，也不会占用网卡、网络资源 127.0.0.1是通过网卡传输，依赖网卡，并受到网络防火墙和网卡相关的限制。

  2. mysql -h localhost 的时候，是不使用TCP/IP连接的，而使用Unix socket；

  3. 权限配置中， 127.0.0.1 与 localhost 中是不同的

mysql 内存占用分析

https://my.oschina.net/qcloudcommunity/blog/3004940

如何只安装mysql client

Mysql has a client-only set of utilities:

Mysql client shell

Other command line utilities https://dev.mysql.com/downloads/utilities/

Mac OSX version available.

MySQL 5.7.12 中新增了 mysql-shell , 是另一个mysql的客户端管理工具

可以到 https://dev.mysql.com/downloads/shell/ 下载,提供编译好的mac版本

或者使用 brew cask install mysql-shell 安装

如果需要mysql命令，可以下载 MySQL Workbench DMG file,然后挂在,不用安装

# Adjust the path to the version of MySQL Workbench you downloaded
cp "/Volumes/MySQL Workbench 6.3.9.CE/MySQLWorkbench.app/Contents/MacOS/mysql" /usr/local/bin
# To make sure it's executable
chmod +x /usr/local/bin/mysql

可以复制其中的mysqldump和mysql命令出来。