MYSQL 5.5 优化思路

精选转载

DevilRex119 2016-01-04 09:24:11 博主文章分类：数据库--MYSQL

文章标签 MYSQL 5.5 优化思路 文章分类 服务器

调优的思路

1.硬件配置优化

2.操作系统的优化

3.数据库设计和规划

4.MySQL配置优化

5.SQL查询优化

硬件配置的优化

CPU——64 位、高主频、高缓存，高并行处理能力 E5

内存——大内存、大内存位宽，尽量不要用SWAP

硬盘——15000RPM、RAID5、raid10,使用硬件阵列卡，或使用ssd

网络——标配的千兆网卡，msyql服务器尽可能和使用它的web服务器在同一局域网内，尽量避免诸如防火墙策略等不必要的开销,双网线提供冗余

1.操作系统的优化

cat /etc/sysctl.conf 追加到末尾

net.ipv4.tcp_keepalive_time=7200

fs.file-max= 819200

net.ipv4.tcp_tw_reuse= 1

net.ipv4.tcp_tw_recycle= 1

net.ipv4.neigh.default.gc_thresh3= 2048

net.ipv4.neigh.default.gc_thresh2= 1024

net.ipv4.neigh.default.gc_thresh1= 256

net.ipv4.tcp_synack_retries= 0

net.ipv4.tcp_syn_retries= 0

net.ipv4.conf.default.forwarding= 1

net.ipv4.conf.default.proxy_arp= 0

net.ipv4.tcp_max_syn_backlog=2048

net.core.netdev_max_backlog= 2048

net.core.dev_weight= 64

net.ipv4.tcp_rmem= 4096 87380 16777216

net.ipv4.tcp_wmem= 4096 65536 16777216

net.ipv4.tcp_rfc1337= 1

net.ipv4.tcp_sack= 0

net.ipv4.tcp_fin_timeout= 20

net.ipv4.tcp_keepalive_probes= 5

net.ipv4.tcp_max_orphans= 32768

net.core.optmem_max= 20480

net.core.rmem_default= 16777216

net.core.rmem_max= 16777216

net.core.wmem_default= 16777216

net.core.wmem_max= 16777216

net.core.somaxconn= 500

net.ipv4.tcp_orphan_retries= 1

net.ipv4.tcp_max_tw_buckets= 18000

net.ipv4.conf.default.proxy_arp= 0

net.ipv4.conf.all.rp_filter= 1

kernel.sysrq= 1

net.ipv4.conf.default.send_redirects= 1

net.ipv4.conf.all.send_redirects= 0

net.ipv4.ip_local_port_range= 5000 65000

vm.swappiness=0

2：IO优化

1、设置一个进程可以打开的文件数

[root@mysql~]#echo "* soft nofile 1024000" >>/etc/security/limits.conf

[root@mysql~]# echo "* hard nofile 1024000" >>/etc/security/limits.conf

重启生效

[root@mysql ~]# ulimit -n

1024000

2、nproc 用户可以打开的最大进程数

[root@mysql~]# cat /etc/security/limits.d/90-nproc.conf

* soft nproc 102400

* hard nproc 102400

重启生效

[root@mysql~]# ulimit -u

102400

3.创建一个磁盘分区格式化成xfs,专门存放data

磁盘分区：将数据库目录放到一个独立的分区上或一个独立的磁盘上的分区. 存储数据的硬盘分区和系统所在的硬盘分开。

给数据仓库一个单独的文件系统，推荐使用XFS，一般效率更高、更可靠。

可以考虑在挂载分区时启用 noatime 选项（了解）

[root@mysql~]# fdisk /dev/sda 创建一个新的磁盘分区

[root@mysql ~]# rpm -ivh/mnt/rhel6/Packages/xfsprogs-3.1.1-14.el6.x86_64.rpm #安装xfs格式化工具

[root@mysql ~]# reboot

[root@mysql ~]# mkfs.xfs /dev/sda3 #格式化

[root@mysql~]# mkdir /data

[root@mysql ~]# mount /dev/sda3 /data/ 挂载

设置开机自动挂载，并设置noatime,nodiratime：

[root@mysql~]# vim /etc/fstab

/dev/sda3 /data xfs defaults,noatime,nodiratime 1 2

[root@mysql~]# mount –a

noatime,nodiratime这样以后系统在读此分区下的文件/目录时，将不会再修改atime属性。

3：网卡的优化

网卡可以采用Bonding技术，实现网卡负载均衡高可用

数据库设计和规划

1:架构设计方面：

纵向拆解:

例：现在公司一台服务器同时负责web、ftp、数据库等多个角色。

纵向拆解后就是：数据库服务器专机专用，避免额外的服务可能导致的性能下降和不稳定性。

如果将数据库服务器专机专用仍然无法满足需求，可以考虑在数据库和日常应用服务器之间加Memcached。

横向拆解: 主从同步、读写分离、负载均衡、高可用性集群，当单个 mysql 数据库无法满足日益增加的需求时，可以考虑在数据库这个逻辑层面增加多台服务器，以达到稳定、高效的效果。

2:存储引擎的选择

Myisam：数据库并发不大，读多写少,而且都能很好的用到索引，sql语句比较简单的应用，TB数据仓库

Innodb：并发访问大，写操作比较多，有外键、事务等需求的应用，系统内存较大。

3:命名规则

多数开发语言命名规则：比如MyAdress

多数开源思想命名规则：my_address

避免随便命名,符合目录名的规则

字段类型的选择的一般原则：

根据需求选择合适的字段类型，在满足需求的情况下字段类型尽可能小。

只分配满足需求的最小字符数，不要太慷慨。

原因：更小的字段类型更小的字符数占用更少的内存，占用更少的磁盘空间，占用更少的磁盘IO，以及占用更少的带宽。

类型	字节	最小值	最大值
		(带符号的/无符号的)	(带符号的/无符号的)
TINYINT	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	18446744073709551615

根据满足需求的最小整数为选择原则，能用INT的就不要用BIGINT。

用无符号INT存储IP，而非CHAR(15)。

浮点型:

类型	字节	精度类型	使用场景
FLOAT(M,D)	4	单精度	精度要求不高，数值比较小
DOUBLE(M,D)（REAL）	8	双精度	精度要求不高，数值比较大
DECIMAL(M,D)(NUMERIC）	M+2	自定义精度	精度要求很高的场景

时间类型:

类型	取值范围	存储空间	零值表示法
DATE	1000-01-01~9999-12-31	3字节	0000-00-00
TIME	-838:59:59~838:59:59	3字节	00:00:00
DATETIME	1000-01-01 00:00:00~9999-12-31 23:59:59	8字节	0000-00-00 00:00:00
TIMESTAMP	19700101000000~2037年的某个时刻	4字节	00000000000000
YEAR	YEAR(4):1901~2155 YEAR(2)：1970~2069	1字节	0000

字符类型:

类型	最大长度	占用存储空间
CHAR[(M)]	M字节	M字节
VARCHAR[(M)]	M字节	M+1字节
TINYBLOD，TINYTEXT	2^8-1字节	L+1字节
BLOB，TEXT	2^16-1字节	L+2
MEDIUMBLOB，MEDIUMTEXT	2^24-1字节	L+3
LONGBLOB，LONGTEXT	2^32-1字节	L+4
ENUM('value1','value2',...)	65535个成员	1或2字节
SET('value1','value2',...)	64个成员	1,2,3,4或8字节

注：L表示可变长度的意思

对于varchar和char的选择要根据引擎和具体情况的不同来选择，主要依据如下原则:

1.如果列数据项的大小一致或者相差不大，则使用char。

2.如果列数据项的大小差异相当大，则使用varchar。

3.对于MyISAM表，尽量使用Char，对于那些经常需要修改而容易形成碎片的myisam和isam数据表就更是如此，它的缺点就是占用磁盘空间。

4.对于InnoDB表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分（所有数据行共用一个表头部分，这个标头部分存放着指向各有关数据列的指针），所以使用char类型不见得会比使用varchar类型好。事实上，因为char类型通常要比varchar类型占用更多的空间，所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利。

5.表中只要存在一个varchar类型的字段，那么所有的char字段都会自动变成varchar类型，因此建议定长和变长的数据分开。

5:字符集的选择

单字节 latin1

Gbk (汉字英文都占2个字节)

多字节 utf8(汉字占3个字节，英文字母占用一个字节)

如果含有中文字符的话最好都统一采用utf8类型，避免乱码的情况发生。

6:主键选择原则

注：这里说的主键设计主要是针对INNODB引擎

1. 能唯一的表示行。

2. 显式的定义一个数值类型自增字段的主键，这个字段可以仅用于做主键，不做其他用途。

3. MySQL主键应该是单列的，以便提高连接和筛选操作的效率。

4. 主键字段类型尽可能小，能用 SMALLINT就不用INT，能用INT就不用BIGINT。

5. 尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能。

6. MySQL主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。

7. MySQL主键应当有计算机自动生成。

8. 主键字段放在数据表的第一顺序。

推荐采用数值类型做主键并采用auto_increment属性让其自动增长。

7:其他设计要注意的

NULL OR NOT NULL

尽可能设置每个字段为NOT NULL，除非有特殊的需求，原因如下：

1. 使用含有NULL列做索引的话会占用更多的磁盘空间，因为索引NULL列需要而外的空间来保存。

2. 进行比较的时候，程序会更复杂。

3. 含有NULL的列比较特殊，SQL难优化，如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。

索引

索引的缺点：极大地加速了查询，减少扫描和锁定的数据行数。

索引的缺点：占用磁盘空间，减慢了数据更新速度，增加了磁盘IO。

添加索引有如下原则：

1. 选择唯一性索引。

2. 为经常需要排序、分组和联合操作的字段建立索引。

3. 为常作为查询条件的字段建立索引。

4. 限制索引的数据，索引不是越多越好。

5. 尽量使用数据量少的索引，对于大字段可以考虑前缀索引。

6. 删除不再使用或者很少使用的索引。

7. 结合核心SQL优先考虑覆盖索引。

8. 忌用字符串做主键。

8:Mysql安装尽量采用源码编译安装

对编译参数进行性能优化，精简不必要启用的功能，合适的应用程序接口。

9:创建表注意问题

保持每个表都不要太大，可以对大表做横切和纵切：比如说我要取得某ID 的lastlogin，完全可以做一张只有“ID和“lastlog”的小表，而非几十、几百列数据的并排大表。

另外对一个有 1000 万条记录的表做更新比对10 个100 万记录的表做更新一般来的要慢。

MySQL服务配置优化1、常用的mysql系统查看语句

mysql> show status; 看系统的状态

mysql> show engine innodb status\G #显示 InnoDB 存储引擎的状态

mysql> show variables; #看变量，在 my.cnf 配置文件里定义的变量值

mysql> show variables like '%log%';

mysql> show warnings; 查看最近一个 sql 语句产生的错误警告，看其他的错误信息，需要看日志/var/log/mysqld.log。

mysql> show processlist ; #显示mysql系统中正在运行的所有线程。可以看到每个客户端在正执行的命令

2、配置优化

1）启用 mysql 慢查询：

---分析 sql 语句，找到影响效率的 SQL

vim /etc/my.cnf

[mysqld]

slow_query_log=on #开启慢查询

slow-query-log-file=/usr/local/mysql/data/slow.log #这个路径对 mysql 用户要具有可写权限

long_query_time=2 #查询超过 2 秒钟的语句记录下

log-queries-not-using-indexes #没有使用索引的查询

[root@xuegod63 ~]# service mysqld restart

mysql> show variables like '%query%';

2）对查询进行缓存

优化总原则：给mysql 的资源太少，则mysql 施展不开：给mysql 的资源太多，可能会拖累整个OS。

大多数LAMP应用都严重依赖于数据库查询，查询的大致过程如下:

PHP发出查询请求->数据库收到指令对查询语句进行分析->确定如何查询->从磁盘中加载信息->返回结果

如果反复查询，就反复执行这些。MySQL 有一个特性称为查询缓存，他可以将查询的结果保存在内存中，在很多情况下，这会极大地提高性能。不过，问题是查询缓存在默认情况下是禁用的。

/etc/my.cnf

query_cache_size= 64M

query_cache_limit = 2M #指定单个查询能够使用的缓冲区大小，默认1M

[root@xuegod63 ~]# service mysqld restart

mysql> show status like 'qcache%';

参数说明：

1. Qcache_free_blocks缓存中相邻内存块的个数。数目大说明可能有碎片。如果数目比较大，可以执行：mysql> flush query cache; #对缓存中的碎片进行整理，从而得到一个空闲块

2. Qcache_free_memory缓存中的空闲内存。

3. Qcache_hits每次查询在缓存中命中时就增大。

4. Qcache_inserts每次插入一个查询时就增大

5. Qcache_lowmem_prunes# prune [pru:n] 修剪因内存不足，删除缓存次数，缓存出现内存不足并且必须要进行清理,以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的Qcache_free_blocks 和 free_memory 可以告诉您属于哪种情冴）。如果Qcache_free_blocks比较大，说明碎片严重。如果free_memory 很小，说明缓存不够用了。

6. Qcache_not_cached# 不适合进行缓存的查询的数量，通常是这些查询不是SELECT 诧句

7. Qcache_queries_in_cache# 在当前缓存的查询（和响应）的数量

8. Qcache_total_blocks#缓存中块的数量。

3.启用表缓存

数据库中的每个表存储在一个文件中，要读取文件的内容，你必须先打开文件，然后再读取。为了加快从文件中读取数据的过程，mysqld 对这些打开文件进行了缓存

vim /etc/my.cnf

table_open_cache = 1024

service mysqld restart

注意：

1-MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值64

2-假定系统有200个并发连接，则需将此参数设置为200*N(N为每个连接所需的文件描述符数目)；

3-当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，

mysql> show status like 'open%tables';

上图说明：有28个打开的文件，有6个表需要打开，如果你每次重新执行

mysql> show status like 'open%tables';

open_tables 的变化很大，说明该缓存的命中率不高。

缓存表的数量：

table_cache作用：打开表缓存总数，如果经常对一些表进行操作，希望将读过的表放入到 cache 中，减少对磁盘的读取次数。通过检查峰值时间的状态值 Open_tables 和Opened_tables.

open_tables 打开表的数量，即可以缓存多个表。

opened_tables 已经打开表的数量，这个值累加，这个值再增加说明表的缓存要设置大些如果你发现 open_tables 等于table_cache，并且 opened_tables 在不断增长，那么你就需要增加 table_cache 的值了。

在 mysql 默认安装情况下，table_cache 的值在 2G 内存以下的机器中的值默认从 256 到 512个,对于有 1G 内存的机器，推荐值是 128-256。

4）启用关键字缓冲区

指定用于索引的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，

系统将开始换页并且真的变慢了。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低，至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE 'key_read%'获得)。注意：该参数值设置的过大反而会是服务器整体效率降低

vim /etc/my.cnf

key_buffer_size = 256M

service mysqld restart

mysql>show status like '%key_read%';

Key_reads 代表命中磁盘的请求个数，Key_read_requests 是总数，命中磁盘的读请求数除以读请求总数就是不中比率。

如果每 1,000 个请求中命中磁盘的数目超过1 个，就应该考虑增大关键字缓冲区了。如果不中比率超过0.1% ，就应该考虑增大关键字缓冲区了

key_buffer_size只对 MyISAM 表起作用。即使你不使用 MyISAM 表，但是内部的临时磁盘表是MyISAM 表，也要使用该值。可以使用检查状态值created_tmp_disk_tables 得知详情。对于 1G 内存的机器，如果不使用 MyISAM 表，推荐值是 16M(8-64M）。

5）mysql资源链接优化

vim /etc/my.cnf

max_connections = 1000

max_connect_errors = 200

wait_timeout = 10

service mysqld restart

1） MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。

2）对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。

3）服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。参数默认值：28800秒（8小时），MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到MySQL Server处理完相应的操作后，应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，最终肯定会达到MySQL Server的连接上限数，这会报'too many connections'的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，可以进行适当的调整小些。要同时设置interactive_timeout和wait_timeout才会生效。

mysql> show status like 'conn%';

mysql> show variables like '%max_conn%';