本文共 9524 字，大约阅读时间需要 31 分钟。

一、数据库硬件优化（选型）

1.一般数据库有几种

1.真实的硬件，物理机2.云服务器 ECS，自己搭建mysql3.选择云数据库 RDS#数据库类型OLTP：在线事务处理系统OLAP：一般用来数据存储，数据处理，数据展示

2.硬件选型

1）CPU选择

1.IO密集型：在企业里部署数据库的服务器CPU都是该类型，支持高并发，E系列，主频低一些，核心数量多一些2.CPU密集型：数据处理，数据分析，CPU要具有很高的计算能力，I系列，主频较高，核心数少

2）内存选择

1.内存并不是越大越好，越大浪费越多，命中率低2.建议cpu核心数量的2-3倍    4核 12G    8核 24G

3）磁盘选择

1.SATA2.SAS3.Fc4.SSD    PCI-E    FLASH

4）存储选择

5）网络选择

1.网卡选择单卡单口2.网卡绑定（bonding）

二、操作系统优化

1.Swap调整

#swappiness值越小，越不会利用swap空间，优先释放cache[root@db01 bin]# cat /proc/sys/vm/swappiness30#临时设置[root@db01 bin]# echo 0 > /proc/sys/vm/swappiness#永久设置[root@db01 bin]# vim /etc/sysctl.conf vm.swappiness = 0[root@db01 bin]# sysctl -pvm.swappiness = 0

2.IO调度策略

[root@db01 bin]# echo deadline > /sys/block/sda/queue/scheduler[root@db01 bin]# cat /sys/block/sda/queue/schedulernoop [deadline] cfq [root@db01 bin]#

3.应用优化

1.减少使用like语句、不走索引的语句、逻辑复杂的语句2.将大事务切割成多个小事务3.避免出现逻辑错误，避免锁表

三、数据库优化

百万数据

#第一步create database opt;use optcreate table test(id int(11),num int(11),k1 char(2),k2 char(4),dt timestamp not null);delimiter //create procedure rand_data(in num int)begindeclare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';declare str2 char(2);declare str4 char(4);declare i int default 0;while i

压力测试语句

mysqlslap --defaults-file=/etc/my.cnf \--concurrency=100 --iterations=1 --create-schema='opt' \--query="select * from opt.test where num='505050'" engine=innodb \--number-of-queries=20000 -uroot -p123 -verbose

1.max_connections（最大连接数）

1.简介mysql最大连接数2.查看方式mysql> show variables like '%connection%';mysql> select @@max_connections;#查看已经连接数mysql> show status like '%connections%';3.一般配置max_connections=1024

2.back_log

1.简介mysql暂时队列存储的连接数，超过最大连接数的连接会临时存储在back_log2.查看方式mysql> show variables like '%back_log%';mysql> select @@back_log;#查看状态，如果有等待的，就需要适量加大max_connections或者back_logmysql> show full processlist;3.一般配置back_log=1024

3.wait_timeout和interactive_timeout

1.简介wait_timeout：超过该值设置的时间没有任何操作，则关闭该连接interactive_timeout：sql执行过程中，超过该值设置的时间没有任何操作，则关闭该连接2.查看方式（默认8小时）mysql> select @@wait_timeout;mysql> show variables like '%wait_timeout%';3.一般设置wait_timeout=60interactive_timeout=3600

4.key_buffer_size

1.简介指定索引缓冲区大小，它的大小决定读取索引的速度    1）与myisam表的索引有关    2）临时表（多表联查，子查询，union）        内存：key_buffer_size        磁盘：ibdata12.查看方式（默认8M）mysql>  show variables like '%key_buffer%';3.配置依据mysql> show status like '%Created_tmp%';+-------------------------+-------+| Variable_name           | Value |+-------------------------+-------+| Created_tmp_disk_tables | 10    |     #创建在磁盘的临时表| Created_tmp_files       | 6     |     #创建临时表文件| Created_tmp_tables      | 78    |     #创建在内存的临时表+-------------------------+-------+Created_tmp_disk_tables占比例越高，key_buffer_size要调整大一些4.一般设置key_buffer_size=64M

5.query_cache_size

1.简介查询缓存，使用查询缓存将之前查询的结果保存到缓冲区，如果后面查询使用的语句相同，则直接从缓冲区拿数据SQL层：select * from world.city;       select * from world.City;  大写不行2.查看方式mysql> show variables like '%query_cache_size%';#查看是否开启mysql>  show variables like '%query_cache%';+------------------------------+---------+| Variable_name                | Value   |+------------------------------+---------+| query_cache_limit            | 1048576 |      #查询的数据大于缓存大小将不缓存| query_cache_min_res_unit     | 4096    |      #缓冲区块的最小大小| query_cache_size             | 1048576 |      #查询缓存大小| query_cache_type             | OFF     |      #缓存是否开启| query_cache_wlock_invalidate | OFF     |      #查询的表如果被锁，还是可以从缓冲区查询+------------------------------+---------+3.配置依据mysql> show status like 'Qcache%';+-------------------------+---------+| Variable_name           | Value   |+-------------------------+---------+| Qcache_free_blocks      | 1       || Qcache_free_memory      | 1031352 |   #缓冲区剩余大小，值小需要调大| Qcache_hits             | 0       |   #命中次数，越多越好| Qcache_inserts          | 0       || Qcache_lowmem_prunes    | 0       |   #当缓冲区用完了，踢掉一部分缓冲内容| Qcache_not_cached       | 20026   |   #不记录缓冲区，调用函数的情况下| Qcache_queries_in_cache | 0       || Qcache_total_blocks     | 1       |+-------------------------+---------+4.一般配置query_cache_size=128Mquery_cache_type=1

6.max_connect_errors

1.简介阻止连接错误过多的服务器连接，防止尝试暴力破解密码连接数据库，如果失败连接次数超过该值，MySQL服务器会禁止该host连接，直到mysql重启或者清空host，flush hosts2.查看方式mysql> show variables like '%max_connect_errors%';3.一般配置max_connect_errors=20

7.sort_buffer_size

1.简介对于排序的查询建立一个缓冲区，增加查询速度order by、group by、distinct、union2.查看方式mysql>  show variables like '%sort_buffer_size%';3.一般配置sort_buffer_size=1M

8.max_allowed_packet

1.简介mysql会限制接收的数据包大小2.查看方式mysql> show variables like '%max_allowed_packet%';3.一般配置max_allowed_packet=32M

9.join_buffer_size

1.简介执行join on时建立的缓冲区2.查看方式mysql>  show variables like '%join_buffer_size%';3.一般配置join_buffer_size=2M

10.thread_cache_size

1.简介服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.正常情况一个线程支持一个sql，如果配置该值，相当于开启长链接，一个线程处理多条sql2.查看方法mysql> show variables like '%thread_cache_size%';3.配置依据mysql> show status like 'threads%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| Threads_cached    | 100   || Threads_connected | 1     || Threads_created   | 101   || Threads_running   | 1     |+-------------------+-------+4.一般配置thread_cache_size=200

11.innodb_buffer_pool_size

1.简介对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。对索引和数据进行缓存，一般配置服务器内存的80%2.查看方式mysql> show variables like '%innodb_buffer_pool_size%';+-------------------------+------------+| Variable_name           | Value      |+-------------------------+------------+| innodb_buffer_pool_size | 1073741824 |+-------------------------+------------+

12.innodb_flush_log_at_trx_commit（面试可能会问）

1.简介主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。    0，表示当事务'提交'时，不做日志写入操作，而是每秒钟将'log buffer'中的数据写入'log file'日志文件并flush('刷到磁盘')磁盘一次；    1，则在'每秒钟'或是每次事物的'提交'都会引起日志文件写入、flush磁盘的操作，'确保了事务的ACID'；    2，每次事务'提交'引起'写入日志文件'的动作,但'每秒钟完成一次flush'磁盘操作。    2.查看方式mysql> show variables like '%innodb_flush_log_at_trx_commit%';3.配置方式innodb_flush_log_at_trx_commit=14.双一标准sync_binlog=1百度   mysql双一标准

13.innodb_thread_concurrency

1.简介保证cpu处理数据很平均，cpu使用情况均匀2.查看方式mysql> show variables like '%innodb_thread_concurrency%';3.配置依据    1）查看top负载，当cpu负载不平均时    2）先设置小一点 8，逐渐增大，加倍增大    3）什么时候平均了，配置就定了

14.innodb_log_buffer_size

1.简介此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。    1）大事务    2）多事务2.查看方式mysql> show variables like '%innodb_log_buffer_size%';3.一般配置innodb_log_buffer_size=128M

15.innodb_log_file_size

1.简介设置磁盘文件的大小，设置ib_logfile0 ib_logfile1的大小2.查看方式mysql> show variables like '%innodb_log_file_size%';3.配置innodb_log_file_size=128M

16.innodb_log_files_in_group

1.简介为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为32.查看方法mysql> show variables like '%innodb_log_files_in_group%';3.设置innodb_log_files_in_group=3

17.read_buffer_size

1.简介MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size一样，该参数对应的分配内存也是每个连接独享2.查看方式mysql> show variables like '%read_buffer_size%';3.配置read_buffer_size=1M

18.read_rnd_buffer_size

1.简介MySql的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。2.查看方式mysql> show variables like '%read_rnd_buffer_size%';3.配置read_rnd_buffer_size=1M

19.bulk_insert_buffer_size

1.简介批量插入数据时缓存大小，提高插入数据的速度2.查看方式mysql> show variables like '%bulk_insert_buffer_size%';3.一般配置bulk_insert_buffer_size=8M

20.binary_log

log_bin=mysql-binbinlog_cache_size = 2M //为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存, 提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是--1M，后者建议是：即 2--4Mmax_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,表示“没有自动删除”。log-bin=/data/mysql-binbinlog_format=row sync_binlog=1               #每次执行commit直接刷新binlog到磁盘

21）配置参数（额外的）

Innodb_flush_method=(O_DIRECT, fdatasync) 1.fdatasync    1)在数据页需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘    2)在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘，但如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘2.Innodb_flush_method=O_DIRECT    1)在数据页需要持久化时，直接写入磁盘    2)在redo buffuer需要持久化时，首先将数据写入OS buffer中，然后由os决定什么时候写入磁盘，但如果innodb_flush_log_at_trx_commit=1的话，日志还是直接每次commit直接写入磁盘    1.数据库基于安全的话innodb_flush_log_at_trx_commit=1innodb_flush_method=O_DIRECT2.数据库基于性能的话innodb_flush_log_at_trx_commit=0innodb_flush_method=fdatasync

四、最终配置文件

[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket=/tmp/mysql.socklog-error=mysql.errlog_bin=mysql-binbinlog_format=rowskip-name-resolveserver-id=1log-slave-updates=1relay_log_purge=0max_connections=1024back_log=128wait_timeout=60interactive_timeout=7200key_buffer_size=16Mquery_cache_size=64Mquery_cache_type=1query_cache_limit=50Mmax_connect_errors=20sort_buffer_size=2Mmax_allowed_packet=32Mjoin_buffer_size=2Mthread_cache_size=200innodb_buffer_pool_size=1024Minnodb_flush_log_at_trx_commit=1innodb_log_buffer_size=32Minnodb_log_file_size=128Minnodb_log_files_in_group=3binlog_cache_size=2Mmax_binlog_cache_size=8Mmax_binlog_size=512Mexpire_logs_days=7read_buffer_size=2Mread_rnd_buffer_size=2Mbulk_insert_buffer_size=8M[client]socket=/tmp/mysql.sock

1.没有优化时查询

2.优化后查询第一次


3.优化后查询第二次