Linux运维学习笔记之MySQL优化教程

第三十六章 MySQL优化

一、网站打开慢的排查思想

1、首先，查客户端到Web服务器慢不慢

2、其次，查Web服务器到数据库慢不慢

(1)调用文本文件，看Web慢不慢

(2)调用连接数据库的URL，看慢不慢

a、慢则查看数据库负载

b、查看慢查询

3、接着，查Web服务器到NFS等存储慢不慢

可以直接调用一个图片文件，看慢不慢，慢表示NFS存储慢

4、最后，如果Web程序调用插件、外链，需看一下插件、外链慢不慢

5、可以用Google、Firefox浏览器通过插件查看哪个URL返回速度，慢的进行优化。

二、网站打开慢的排查实战

1、现象：网站打开慢

2、排查思路

(1)在MySQL中间隔几秒就执行一次showfull processlist;如果同一条语句多次出现，说明有问题，可能查询很慢。MySQL中SQL语句执行正常情况是很快的。

(2)开启慢查询日志，将慢查询语句写入文件中。再针对文件中的慢查询语句进行优化。

long_query_time = 1

log-slow_queries = /data/3306/slow.log

3、解决方法

(1)场景一：针对数据库可以调优解决

a、登陆服务器，检查负载，发现负载较高

uptime

b、登陆MySQL，检查当前运行的语句，发现大量对同时对某一表进行写入和读取操作

show full processlist;

c、确定待优化的语句

d、查看SQL的执行计划

explain select id from test where dateline='2014-03-16' andader='ccc' and pos='ddd'\G;

e、查看建表语句及引擎。

表是只有主键，引擎为MyISAM。MyISAM引擎是要锁表的，读写频繁，所以效率低。

f、原因没有索引，全表扫描，在条件列上建索引

(i)检查列值的唯一性

select count(distinct dateline) from test; #值为531

select count(distinct ader) from test; #值为154

select count(distinct pos) from test; #值为421

select count(*) from test; #总记录数为13239条

(ii)根据上述情况，咨询研发经理，结合其它语句情况，创建联合索引

create index d_a_p on test(dateline, ader(20), pos(20));

g、再次检查SQL的执行计划，检查负载，show full processlist;发现系统已正常

h、注意事项：在生产环境中，如果访问频繁的大表，创建索引会很耗费时间，应在业务流量低谷时建索引。

(2)场景二：数据库调优意义不大

a、现象

登陆服务器，检查负载，发现负载较高. 登陆MySQL，show full processlist;检查当前运行的语句，发现大量like '%关键字%'的语句，根本不会走索引。经判断是有类似百度搜索式的查询页面，但没有走全文，直接在数据库是like '%%'。

b、优化思路

(i)从业务上实现用户登录后再搜索，减少搜索次数，降低压力

(ii)如有大量频繁搜索，有可能是爬虫在爬网站，需分析Web日志IP，封掉(AWSTATS)

(iii)配置多个主从同步，程序上实现读写分离，让like '%%'这样的查询查从库

(iv)在数据库的前端加上memcached缓存服务器

(v)象like '%关键字%'的语句，一般在mysql里很难优化，可以通过搜索服务sphinx实现搜索

(vi)还可以利用c，ruby等开发程序，实现每日读库计算搜索索引，保存在服务器上提供搜索，然后，每5分钟从一个从库做一次增量(也就是对搜索做集群，这是在公司针对站内搜索采取的比较好的方案)。

c、采取方法

短期来看，倾向于1，2的方案，简单，可实施，3，4是后期要发展的目标。

经排查，上述案例，系有人爬站，封IP后就好了。

三、SQL语句优化

1、insert指量插入

insert into test values(4,'oldboy4'),(5,'oldboy5'),(6,'oldboy6');

2、select查询时，不要用*，只列出需查询的列

select name from test;

3、索引优化

(1)在where条件列上建索引

(2)在唯一值多的列上建索引

(3)联合索引上只要有一列含有NULL值，那么这一列将不会使用索引。所以在数据库设计时，不要让字段的默认值为NULL。

(4)用or分割开的条件，如果or的前条件有索引，但后条件列无索引，那么涉及的索引都不会被用到

四、数据库优化思想

1、硬件优化

(1)CPU：64位CPU，百度一台机器为8-16颗CPU，一般公司为2-4颗

(2)MEM：百度为94G-128G，跑3-4个实例。一般公司为32G-64G，跑2个实例

(3)Disk：数量越多越好。

性能为SSD(高并发)> SAS(普通业务)> STAT(线下)

Raid(以4盘为例)：RAID0 > RAID10 > RAID5 >RAID1

(4)网卡：多块网卡bond，以及buffer，tcp优化

2、软件优化

(1)操作系统：64位操作系统

(2)MySQL：编译安装、优化

关于MySQL数据库的线上环境安装，建议采取编译安装的方式，这样性能会有较大的提升。服务器系统则建议CentOS6.7 X86_64，源码包的编译参数会默认以Debug模式生成二进制代码，而Debug模式给MySQL带来的性能损失是比较大的，所以当我们编译准备安装的产品代码时，一定不要忘记使用–without-debug参数禁止Debug模式。如果把–with-mysqld-ldflags和–with-client-ld-flags两个编译参数设置为–all-static的话，可以告诉编译器以静态的方式编译，编译结果将得到最高的性能。使用静态编译和使用动态编译的代码相比，性能差距可能会达到5%至10%之多。

3、my.cnf里参数优化

(1)参数说明：详细见注3-Web服务相关应用配置文件总结中的my.cnf

a、线程类参数：此类参数为线程使用，参数值都比较小(不能大，线程太多)

sort_buffer_size = 2M #线程排序

join_buffer_size = 2M

read_buffer_size = 2M

b、可以设置大一些的参数

open_files_limit = 10240 #打开文件的数目

tmp_table_size = 256M #临时表，会占用磁盘空间，可以大一点，不需要太大

max_heap_table_size = 512M #内存表

c、不用管的参数(下面3个参数是MySQL的缓存配置，但MySQL的缓存性能不高，所以不需设置大太，默认就行了)

query_cache_size = 64M

query_cache_limit = 2M

query_cache_min_res_unit = 2k

(2)InnoDB引擎参数优化

a、重要参数

innodb_additional_mem_pool_size = 16M

innodb_data_file_path = ibdata1:256M:autoextend

innodb_buffer_pool_size = 2048M #建议为实例占用物理内存的30%~50%

innodb_data_file_path = ibdata1:256M:autoextend

innodb_file_io_threads = 4#文件IO

innodb_thread_concurrency = 8#并发线程

innodb_flush_log_at_trx_commit = 2 #提交时，是否刷新binlog

innodb_file_per_table = 0 #InnoDB为独立表空间模式每个表都会生成一个数据空间。0关闭，1开启。

b、独立表空间优点

(i)每个表都有自己独立的表空间。

(ii)每个表的数据和索引都会存在自己的表空间中。

(iii)可以实现单表在不同的数据库中移动。

(iv)空间可以回收(除drop table操作处，表空不能自己回收。)

c、注意事项

(i)强烈建议不要武断地将InnoDB的Buffer Pool值配置为物理内存的50%~80%，应根据具体环境而定。

(ii)如果key_reads太大，则应该把my.cnf中的key_buffer_size变大，保持key_reads和key_read_re-quests至少在1/100以上，越小越好。

(iii)如果qcache_lowmem_prunes很大，就要增加query_cache_size的值。

(iv)不过很多时候需要具体情况具体分析，其他参数的变更我们可以等MySQL上线稳定一段时间后在根据status值进行调整。

(3)MySQL上线后根据status状态进行优化

MySQL数据库上线后，可以等其稳定运行一段时间后再根据服务器的status状态进行适当优化，我们可以用如下命令列出MySQL服务器运行的各种状态值：

mysql > show global status;#个人比较喜欢的用法是 showstatus like ‘关键字%’;

a、慢查询

有时我们为了定位系统中效率比较低下的Query语法，需要打开慢查询日志，也就是Slow Que-ry log。打开慢查询日志的相关命令如下：

mysql> show variables like ‘%slow%’;

+———————+—————————————–+

| Variable_name | Value |

+———————+—————————————–+

| log_slow_queries | ON |

| slow_launch_time | 2 |

+———————+—————————————–+

 

mysql> show global status like ‘%slow%’;

+———————+——-+

| Variable_name | Value |

+———————+——-+

| Slow_launch_threads | 0 |

| Slow_queries | 2128 |

+———————+——-+

打开慢查询日志可能会对系统性能有一点点影响，如果你的MySQL是主从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响也会很小。另外，可以用MySQL自带的命令mysqldumpslow进行查询。比如：下面的命令可以查出访问次数最多的20个SQL语句：

mysqldumpslow -s c -t 20 host-slow.log

b、连接数

我们如果经常遇见MySQL：ERROR1040：Too manyconnections的情况，一种情况是访问量确实很高，MySQL服务器扛不住了，这个时候就要考虑增加从服务器分散读压力。另外一种情况是MySQL配置文件中max_connections的值过小。来看一个例子。

mysql>show variables like ‘max_connections’;

+—————–+——-+

| Variable_name | Value |

+—————–+——-+

| max_connections | 800 |

+—————–+——-+

这台服务器最大连接数是256，然后查询一下该服务器响应的最大连接数;

mysql> show global status like ‘Max_used_connections’;

+———————-+——-+

| Variable_name | Value |

+———————-+——-+

| Max_used_connections | 245 |

+———————-+——-+

MySQL服务器过去的最大连接数是245，没有达到服务器连接数的上线800，不会出现1040错误。

Max_used_connections /max_connections * 100% = 85%

最大连接数占上限连接数的85%左右,如果发现比例在10%以下，则说明MySQL服务器连接数的上限设置得过高了。

c、key_buffer_size

key_buffer_size是设置MyISAM表索引缓存空间的大小，此参数对MyISAM表性能影响最大。下面是一台MyISAM为主要存储引擎服务器的配置：

mysql> show variables like ‘key_buffer_size’;

+—————–+———–+

| Variable_name | Value |

+—————–+———–+

| key_buffer_size | 536870912 |

+—————–+———–+

从上面可以看出，分配了512MB内存给key_buffer_size。再来看key_buffer_size的使用情况：

mysql> show global status like ‘key_read%’;

+——————-+————–+

| Variable_name | Value |

+——————-+——-+

| Key_read_requests | 27813678766 |

| Key_reads | 6798830 |

+——————-+————–+

一共有27813678766个索引读取请求，有6798830个请求在内存中没有找到，直接从硬盘读取索引。

key_cache_miss_rate = key_reads / key_read_requests * 100%

比如上面的数据，key_cache_miss_rate为0.0244%，4000%个索引读取请求才有一个直接读硬盘，效果已经很好了，key_cache_miss_rate在0.1%以下都很好，如果key_cache_miss_rate在0.01%以下的话，则说明key_buffer_size分配得过多，可以适当减少。

d、临时表

当执行语句时，关于已经被创建了隐含临时表的数量，我们可以用如下命令查询其具体情况：

mysql> show global status like ‘created_tmp%’;

+————————-+———-+

| Variable_name | Value |

+————————-+———-+

| Created_tmp_disk_tables | 21119 |

| Created_tmp_files | 6 |

| Created_tmp_tables | 17715532 |

+————————-+———-+

每次创建临时表时，Created_tmp_table都会增加，如果磁盘上创建临时表，Created_tmp_disk_tables也会增加。Created_tmp_files表示MySQL服务创建的临时文件数，比较理想的配置是：

Created_tmp_disk_tables / Created_tmp_files* 100% <= 25%

比如上面的服务器Created_tmp_disk_tables /Created_tmp_files * 100% =1.20%，就相当不错。我们在看一下MySQL服务器对临时表的配置：

mysql> show variables where Variable_name in(‘tmp_table_size’,’max_heap_table_size’);

+———————+———+

| Variable_name | Value |

+———————+———+

| max_heap_table_size | 2097152 |

| tmp_table_size | 2097152 |

+———————+———+

e、打开表的情况

Open_tables表示打开表的数量，Opened_tables表示打开过的表数量，我们可以用如下命令查看其具体情况：

mysql> show global status like ‘open%tables%’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_tables | 351 |

| Opened_tables | 1455 |

如果Opened_tables数量过大，说明配置中table_open_cache的值可能太小。我们查询下服务器table_open_cache;

mysql> show variables like ‘table_open_cache’;

+——————+——-+

| Variable_name | Value |

+——————+——-+

| table_open_cache | 2048 |

+——————+——-+

比较合适的值为：

open_tables / opened_tables* 100% > = 85%

open_tables / table_open_cache* 100% < = 95%

f、进程使用情况

如果我们在MySQL服务器的配置文件中设置了thread_cache_size，当客户端断开时，服务器处理此客户请求的线程将会缓存起来以响应一下客户而不是销毁(前提是缓存数未达上线)Thread_created表示创建过的线程数，我们可以用如下命令查看：

mysql> show global status like ‘thread%’;

+——————-+——-+

| Variable_name | Value |

+——————-+——-+

| Threads_cached | 40 |

| Threads_connected | 1 |

| Threads_created | 330 |

| Threads_running | 1 |

+——————-+——-+

如果发现Threads_created的值过大的话，表明MySQL服务器一直在创建线程，这也是比较耗费资源的，可以适当增大配置文件中thread_cache_size的值。查询服务器thread_cache_size配置如下：

mysql> show variables like ‘thread_cache_size’;

+——————-+——-+

| Variable_name | Value |

+——————-+——-+

| thread_cache_size | 100 |

+——————-+——-+

示例中的MySQL服务器还是挺健康的。

g、查询缓存(query cache)

它主要涉及两个参数，query_cache_size是设置MySQL的Query Cache大小，query_cache_type是设置使用查询缓存的类型，我们可以用如下命令查看其具体情况：

mysql> show global status like ‘qcache%’;

+————————-+———–+

| Variable_name | Value |

+————————-+———–+

| Qcache_free_blocks | 22756 |

| Qcache_free_memory | 76764704 |

| Qcache_hits | 213028692 |

| Qcache_inserts | 208894227 |

| Qcache_lowmem_prunes | 4010916 |

| Qcache_not_cached | 13385031 |

| Qcache_queries_in_cache | 43560 |

| Qcache_total_blocks | 111212 |

+————————-+———–+

MySQL查询缓存变量的相关解释如下：

Qcache_free_blocks：缓存中相领内存快的个数。数目大说明可能有碎片。flush query cache会对缓存中的碎片进行整理，从而得到一个空间块。

Qcache_free_memory：缓存中的空闲空间。

Qcache_hits：多少次命中。通过这个参数可以查看到Query Cache的基本效果。

Qcache_inserts：插入次数，没插入一次查询时就增加1。命中次数除以插入次数就是命中比率。

Qcache_lowmem_prunes：多少条Query因为内存不足而被清楚出Query Cache。通过Qcache_lowmem_prunes和Query_free_memory相互结合，能够更清楚地了解到系统中Query Cache的内存大小是否真的足够，是否非常频繁地出现因为内存不足而有Query被换出的情况。

Qcache_not_cached：不适合进行缓存的查询数量，通常是由于这些查询不是select语句或用了now()之类的函数。

Qcache_queries_in_cache：当前缓存的查询和响应数量。

Qcache_total_blocks：缓存中块的数量。

我们在查询一下服务器上关于query_cache的配置命令：

mysql> show variables like ‘query_cache%’;

+——————————+———+

| Variable_name | Value |

+——————————+———+

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 2048 |

| query_cache_size | 2097152 |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

+——————————+———+

字段解释如下：

query_cache_limit：超过此大小的查询将不缓存。

query_cache_min_res_unit：缓存块的最小值。

query_cache_size：查询缓存大小。

query_cache_type：缓存类型，决定缓存什么样的查询，示例中表示不缓存select sql_no_cache查询。

query_cache_wlock_invalidat：表示当有其他客户端正在对MyISAM表进行写操作，读请求是要等WRITELOCK释放资源后再查询还是允许直接从QueryCache中读取结果，默认为OFF(可以直接从Query Cache中取得结果。)

query_cache_min_res_unit的配置是一柄双刃剑，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。

查询缓存碎片率 = Qcache_free_blocks/Qcache_total_blocks * 100%

如果查询碎片率超过20%，可以用 flush query cache 整理缓存碎片，或者试试减少query_cache_min_res_unit，如果你查询都是小数据库的话。

查询缓存利用率 = (Qcache_free_size – Qcache_free_memory)/query_cache_size *100%

查询缓存利用率在25%一下的话说明query_cache_size设置得过大，可适当减少;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话则说明query_cache_size可能有点小，不然就是碎片太多。

查询命中率= (Qcache_hits – Qcache_insert)/Qcache)hits* 100%

示例服务器中的查询缓存碎片率等于20%左右，查询缓存利用率在50%，查询命中率在2%，说明命中率很差，可能写操作比较频繁，而且可能有些碎片。

h、排序使用情况

它表示系统中对数据进行排序时所用的Buffer，我们可以用如下命令查看：

mysql> show global status like ‘sort%’;

+——————-+———-+

| Variable_name | Value |

+——————-+———-+

| Sort_merge_passes | 10 |

| Sort_range | 37431240 |

| Sort_rows | 6738691532 |

| Sort_scan | 1823485 |

+——————-+———-+

Sort_merge_passes包括如下步骤：

MySQL首先会尝试在内存中做排序，使用的内存大小由系统变量sort_buffer_size来决定。

如果它不够大则把所有的记录都读在内存中，而MySQL则会把每次在内存中排序的结果存到临时文件中，等MySQL找到所有记录之后，再把临时文件中的记录做一次排序。这次再排序就会增加sort_merge_passes。

实际上，MySQL会用另外一个临时文件来存储再次排序的结果，所以我们通常会看到sort_merge_passes增加的数值是建临时文件数的两倍。因为用到了临时文件，所以速度可能会比较慢，增大sort_buffer_size会减少sort_merge_passes和创建临时文件的次数，但盲目地增大sort_buffer_size并不一定能提高速度。

i、文件打开数(open_files)

我们现在处理MySQL故障时，发现当Open_files大于open_files_limit值时，MySQL数据库就会发生卡住的现象，导致Nginx服务器打不开相应页面。这个问题大家在工作中应注意，我们可以用如下命令查看其具体情况：

show global status like ‘open_files’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Open_files | 1481 |

+—————+——-+

mysql> show global status like ‘open_files_limit’;

+——————+——-+

| Variable_name | Value |

+——————+——–+

| Open_files_limit | 4509 |

+——————+——–+

比较合适的设置是：Open_files/ Open_files_limit * 100% < = 75%

j、InnoDB_buffer_pool_cache合理设置

InnoDB存储引擎的缓存机制和MyISAM的最大区别就在于，InnoDB不仅仅缓存索引，同时还会缓存实际的数据。此参数用来设置InnoDB最主要的Buffer的大小，也就是缓存用户表及索引数据的最主要缓存空间，对InnoDB整体性能影响也最大。

无论是MySQL官方手册还是网络上许多人分享的InnoDB优化建议，都是简单地建议将此值设置为整个系统物理内存的50%~80%。这种做法其实不妥，我们应根据实际的运行场景来正确设置此项参数。

(4)示例：某电商MySQL数据库配置文件

这是一份电子商务网站MySQL数据库调整后所运行的配置文件/etc/my.cnf(服务器为DELL R710、16GB内存、RAID10)，大家可以根据实际的MySQL数据库硬件情况进行调整配置文件如下：

[client]

port = 3306

socket = /data/3306/mysql.sock

default-character-set = utf8

 

[mysqld]

user = mysql

port = 3306

character-set-server = utf8

socket = /data/3306/mysql.sock

basedir = /application/mysql

datadir = /data/3306/data

log-error=/data/3306/mysql_err.log

pid-file=/data/3306/mysql.pid

log_slave_updates = 1

log-bin = /data/3306/mysql-bin

binlog_format = mixed

binlog_cache_size = 4M

max_binlog_cache_size = 8M

max_binlog_size = 1G

expire_logs_days = 90

binlog-ignore – db = mysql

binlog-ignore – db = information_schema

key_buffer_size = 384M

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

join_buffer_size = 2M

thread_cache_size = 8

query_cache_size = 32M

query_cache_limit = 2M

query_cache_min_res_unit = 2k

thread_concurrency = 32

table_cache = 614

table_open_cache = 512

open_files_limit = 10240

back_log = 600

max_connections = 5000

max_connect_errors = 6000

external-locking = FALSE

max_allowed_packet =16M

thread_stack = 192K

transaction_isolation = READ-COMMITTED

tmp_table_size = 256M

max_heap_table_size = 512M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

long_query_time = 2

slow_query_log

slow_query_log_file = /data/3306/slow.log

skip-name-resolv

skip-locking

skip-networking

server-id = 1

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 512M

innodb_data_file_path = ibdata1:256M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 16M

innodb_log_file_size = 128M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

 

[mysqldump]

quick

max_allowed_packet = 64M

 

[mysql]

no – auto – rehash

4、SQL语句的优化

(1)索引优化

a、白名单机制(百度)

在项目开发过程中，DBA参与，减少上线后的慢SQL数量

运行过程中，抓出慢SQL，配置my.cnf

long_query_time=2

log-slow-queries=/data/3306/slow-log.log

按天轮询切分slow-log.log

b、慢查询日志分析工具-mysqlsla

慢查询日志分析工具有许多，如mysqlsla、mysqldumplow、myprofi、mysql-explain-slow-log、mysqllogfilter等等，推荐使用mysqlsla。

c、定时分析慢查询

每天晚上指定时间定时分析慢查询，将结果发送到核心开发、DBA分析，同时，也发发送到高级运维、CTO的邮箱中。

由DBA分析给出的优化建议-->核心开发确认更改-->DBA线上操作处理。

(2)将大的、复杂的SQL语句拆分成多个小的SQL语句

子查询、JOIN连表查询都可以进行拆分，大表要分表或拆库

(3)数据库是存储数据的地方，但不是计算数据的地方

对数据的计算、应用类处理，都要到前端应用解决，禁止在数据库上处理。

(4)搜索功能，类似 like '%%'的操作，一般不要用MySQL数据库。

5、架构的优化

(1)业务拆分：搜索功能，like '%关键字%'等功能，一般不要用MySQL数据库

(2)业务拆分：某些业务应用如粉丝关注、好友关系等，应使用nosql持久化存储，如memcachedb、redis、ttserver.

(3)在数中前端必须要加cache。例如用户登录、商品查询等应用前端可加memcached。

(4)动态数据静态化。整个文件静态化和页面片段静态化。

(5)数据库念集群和读写分离。一主从从，通过程序或dbproxy进行集群读写分离。

(6)拆库拆表。单表超2000万。可按登录、商品、订单等业务进行自动或手动拆库

百度、阿里国内前三公司都是自动拆分。

6、流程、制度、安全优化

任何一次人为数据库记录的更新，都需要走一个流程

(1)人的流程

开发-->核心开发-->运维或DBA

(2)测试流程

内部测试-->IDC测试-->线上执行

(3)客户端管理

a、更改默认mysql client 端口，如phpadmin 管理端口为9999，其他客户端也是一样的

b、数据库web client端统一部署在1-2台不对外服务Server上，限制ip,及9999端口只能从内网访问。

c、不做公网域名解析,用host实现访问或者内部IP

d、phpadmin站点目录独立所有其他站点根目录外，只能由指定的域名或ip地址访问。

e、限制使用web连接的账号管理数据库，根据用户角色设置指定账号访问。

f、按开发及相关人员根据职位角色分配管理账号

g、设置指定账号访问(apache/nginx验证+mysql用户两个登录限制)

h、统一所有数据库账号登录入口地址。禁止所有开发私自上传phpadmin等数据库管理等

i、开通vpn，跳板机，内部IP管理数据库

7、MySQL优化小思想

很多时候我们会发现，通过参数设置进行性能优化所带来的性能提升，并不如许多人想象的那样会产生质的飞跃，除非是之前的设置存在严重不合理的情况。我们不能将性能调优完全依托与通过DBA在数据库上线后进行参数调整，而应该在系统设计和开发阶段就尽可能减少性能问题。(重点在于前期架构合理的设计及开发的程序合理)

五、数据库安全权限控制管理思想

1、项目开发制度流程

办公开发环境-->办公测试环境-->IDC测试环境-->IDC正式环境。通过这种较完善的项目开发制度及流程控制，尽可能的防止潜在的问题隐患发生。

2、数据库更新流程

开发人员提交需求-->开发主管审核 -->DBA审核-->执行开发流程的数据库更新测试步骤-->最后在IDC上线执行。需要说明的是在一开始提交需求，就会同时抄给以上的领导及审核人员，然后，审批人员依次审核。对于特殊紧急需求，可以根据紧急程度特殊处理，这里可以制定一个紧急需求处理流程，比如开发人员提交需求-->DBA或运维审核，然后操作完成后在汇报给其它审核人员等。

通过较为完善的数据库更新流程控制，可以防止很多潜在的数据丢失破坏问题发生。

3 DBA参与项目数据库设计

在开发环节上，DBA或资深运维人员最好可以参与数据库的设计与审核，从源头上减少降低不良设计及不良SQL语句的发生，还可以做所有语句的审核工作，包括Select，但这个需要评估工作量是否允许，一般的互联网公司实施全审核比较困难。

参考博文23：运维就是一场没有硝烟的战争http://oldboy.blog.51cto.com/2561410/1296694

4 各种操作申请流程

(1)开发测试等人员权限申请流程

需要权限直接发邮件并create task到DBA，协商后予以申请权限。

(2)数据库更新执行流程

a、涉及到生产数据库重大更新，比如订单取消等，发邮件到技术总监以及DBA，判断业务是否允许，完成上述数据库更改。

b、 涉及到生产数据库小规模变更，比如产品要求做刷单操作，直接发邮件给DBA，抄送技术总监和开发负责人等。

(3)烂SQL语句计入KPI考核

DBA定期巡检数据库SQL语句，该优化的SQL提出建议，发邮件给相关开发责任人，并抄送技术总监。一般提出优化SQL事宜需要1-3个工作日完善，需和开发等协商。烂SQL语句计入KPI绩效考核

5 定期对内部人员培训

(1)定期给开发及相关人员培训，还是从源头上减少降低不良设计及SQL语句的发生，并通过培训告诉大家数据库性能的重要性，让大家提升性能意识。

数据库设计规范及制度。

(2)SQL语句执行优化，性能优化技巧等。

(3)数据库架构设计等内容。

6、账户权限控制

(1)内部开发等人员权限分配

a、权限申请流程要设置严格点，让需求不明确者知难而退。

b、开发和功能测试环境可以开放一些权限，阿里云正式环境严格控制数据库写权限，并且读权限和对外业务服务器分离。

c、开发人员线上数据库权限分配，给单独的不对外服务的正式从库只读权限，不能分配线上正式主从库写权限。程序账号授权该库的SELECT，DML操作。

d、如公司领导，开通权限时问清楚做什么，发邮件回复，注明主机IP、用户名、密码、权限范围，多提醒操作注意事项。

e、特殊账号，由DBA专人控制，禁止在任何客户端上执行特殊账号操作(如只能localhost或其他策略)。

f、临时在生产库申请账号，需要发邮件注明事项，发邮件给DBA。

(2)Web账户权限分配制度

a、写库账号默认权限为select，insert，update，delete，不要给建表改表(create，alter)的权限，更不能给all权限。

b、读库账号默认权限为select(配合read-only参数用)。一定要确保从库是只读的(对所有人员)。

c、根据需要，最好专库专账号，不要一账号管理多个库。碎库特别多的，根据情况处理。

d、web和数据库分离的服务器的授权可以根据web服务器数量多少按IP或网段来授权。

f、安全性和方便管理，是矛盾的，要尽量达到一个平衡的状态，如何使平衡就要根据具体公司和业务来衡量了。

(3)web账户授权案例

a、生产环境主库用户的账号授权：

GRANT SELECT,INSERT,UPDATE,DELETE ON blog.*TO 'blog'@10.0.0.%'identified by 'TonyS1$521';

说明：这里表示给10.0.0/24的用户blog管理blog数据库的所有表(*表示所有库)只读权限和DML权限。密码为TonyS1$521。

b、生产环境从库用户的授权：

GRANT SELECT ON blog.*TO 'blog'@'10.0.0.%'identified by ' TonyS1$521';

当然从库除了做SELECT 的授权外，还可以加read-only等只读参数。

(4)生产环境读写分离账户设置

给开发人员的读写分离用户设置，除了IP必须要不同外，我们尽量为开发人员使用提供方便。因此，读写分离的地址，除了IP不同外，账号，密码，端口等看起来都是一样的，这才是人性化的设计，体现了DBA人员的专业。

主库(尽量提供写服务)：blog TonyS1$521ip:10.0.0.179 port 3306

从库(今提供读服务)： blog TonyS1$521 ip:10.0.0.180 port 3306

提示： 两个账号的权限是不一样的

提示：从数据库的设计上，对于读库，开发人员应该设计优先连接读库，如果读库没有，超时后，可以考虑主库，从程序设计上来保证提升用也要根据主库的繁忙程度来综合体验，具体情况都是根据业务项目需求来抉择。

7、数据库客户端访问控制

(1)只允许特定IP和账号使用，要登记清楚。

(2)限制使用web连接的账号管理数据库，根据用户角色设置指定账号访问。

(3) 按开发及相关人员根据职位角色分配管理账号。

(4) 统一所有数据库账号登录入口地址。禁止所有开发私自上传等数据库管理等。

(5)远程维护主机和数据库：开通vpn，跳板机，内部IP等管理数据库。

8、系统层控制

(1)开发环境、生产环境限制或禁止开发人员ssh root 管理，通过sudo细化权限，使用日志审计。

(2)禁止非管理人员管理有数据库web client端的服务器的权限。

读库分业务读写分离

(3)细则补充：对数据库的select 等大量测试，统计，备份等操作，要在不对外提供select的单独从库执行。因为在主库上执行有表锁(如果访问数据表很大时)。

9、主从使用规则

(1)在master上主要操作为DML、DDL和部分SELECT操作‘

(2)在slave上主要是SELECT查询操作。

10、定期巡检

(1)慢查询日志分析;白名单机制à上线前审核所有SQL;

(2)定期检查数据库内存使用命中率;

(3)定期检查主从复制是否一致;

(4)定期做好备份恢复测试à检查数据库备份文件的有效性;

(5)定期检查主从数据库中数据表设计是否有多余索引;

(6)定期检查数据库锁情况;

(7) 分析数据库TPS和QPS情况;

(8) 分析数据库主机资源使用比率、内存、CPU、磁盘I/O阻塞、网络等。

11、数据库运维管理思想核心

(1)未雨绸缪，不要停留在制度上，而是，实际做出来

(2)亡羊补牢，举一反三，切记，不能好了伤疤忘了疼

(3)完备的架构设计及备份、恢复策略

(4)定期思考，并实战模拟以上策略演练