关系型数据库性能优化总结数据库技术

摘要：今天我们要讨论的是关系型数据库性能优化的问题，平时我们也最好要遵守一定的规则，希望对大家有所帮助。对于web应用开发，多数性能瓶颈均出现在数据库上，除了采用分布式架构或云处理（大公司基本上都是），更重要的是平时程序设计时要遵照一些规则，从根本上提高系统的性能，以下总结了一些常用的规则方法，仅供参考，欢迎跟帖补充。。。
1、把数据、日志、索引放到不同的I/O设备上，增加读取速度。数据量（尺寸）越大，提高I/O越重要。
2、纵向、横向分割表，减少表的尺寸，如：可以把大数据量的字段拆分表。
3、根据查询条件，建立索引，优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，尽量使用字节数小的列建索引，不要对有限的几个值的列建单一索引。
4、用OR的字句可以分解成多个查询，并且通过UNION链接多个查询。它们的速度只与是否使用索引有关，如果查询需要用到联合索引，用UNION all执行的效率更高。
5、在查询SELECT语句中用WHERE子句限制返回的行数，避免表扫描。如果返回不必要的数据，则浪费了服务器的I/O资源，加重了网络的负担，降低了性能。如果表很大，在表扫描期间将表锁住，禁止其他的联结访问表，后果很严重。
6、注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢。
7、在IN后面值的列表中，将出现最频繁的值放在最前面，出现最少的放在最后面，减少判断的次数。
8、一般在GROUP BY和HAVING子句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作，也就是说尽可能在WHERE中过滤数据。
9、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译、优化过，并且被组织到一个执行规划里，且存储在数据库中的SQL语句（存储过程是数据库服务器端的一段程序），是控制流语言的集合，速度当然快。
10、不要在一句话里再三地使用相同的函数，浪费资源，将结果放在变量里再调用更快。
11、针对大量只读查询操作进行优化的方法：
1)数据量小的数据，可以考虑不存储在数据库中，而是通过程序常量的方式解决。
2)需要存储在数据库中的数据，可以考虑采用物化视图（索引视图）。当DBA在视图上创建索引时，这个视图就被物化（执行）了，并且结果集被永久地保存在唯一索引中，保存方式与一个有聚簇索引的表的保存方式相同。物化视图减除了为引用视图的查询动态建立结果集的开销，优化人员可以在查询中使用视图索引，而不需要在FROM子句中直接指定视图。
3)数据存储时可以考虑适当的数据冗余，以减少数据库表之间的链接操作，提高查询效率。
4)针对数据的特点，采取特定的索引类型。例如，位图索引等。
12、对于SQL语句书写时的一些建议：
1)写语句时能够确定数据库对象所有者的，尽可能把所有者带上，如：

SELECT*FROM dbo.Users

2)存储过程中，参数定义最好放在最前面，尽可能一次定义，如：

DECLARE @USER_ID INT
,@USER_NAMEVARCHAR(50)
,@PASSWORDVARCHAR(50)

3)为参数赋值时，尽可能一次赋值，如：

SELECT @USER_ID= 1001
,@USER_NAME= 'xiaojun.liu'

4)尽量少用游标

Mysql数据库优化总结

参考网站：http://blog.chinaunix.net/uid-20639775-id-3154234.html

说明：本文的环境为CENTOS 5.5 64 Bit /Mysql 5.1.50
简介：使用Mysql有一段时间了，期间做了不少关于Mysql优化、设计、维护的工作，这两天有时间做一下简单的总结，方便自己回忆，同时也希望能对大家有点帮助.

I 硬件配置优化 ?CPU选择：多核的CPU，主频高的CPU
?内存：更大的内存
?磁盘选择：更快的转速、RAID、阵列卡，
?网络环境选择：尽量部署在局域网、SCI、光缆、千兆网、双网线提供冗余、0.0.0.0多端口绑定监听

II 操作系统级优化 ?使用64位的操作系统，更好的使用大内存。
?设置noatime,nodiratime
[zhangxy@dowload_server1 ~]$ cat /etc/fstab
LABEL=//ext3defaults,noatime,nodiratime1 1
/dev/sda5/dataxfsdefaults,noatime,nodiratime1 2
?优化内核参数
net.ipv4.tcp_keepalive_time=7200
net.ipv4.tcp_max_syn_backlog=1024
net.ipv4.tcp_syncookies=1
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.conf.default.rp_filter = 1
net.ipv4.conf.default.forwarding = 1
net.ipv4.conf.default.proxy_arp = 0
net.ipv4.tcp_syncookies = 1
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.ip_forward = 0
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 = 500065000
kernel.shmmax = 167108864
vm.swappiness=0
?加大文件描述符限制
Vim /etc/security/limits.conf
加上
*softnofile65535
*hardnofile65535
?文件系统选择 xfs
/dev/sda5/dataxfsdefaults,noatime,nodiratime1 2

III Mysql 设计优化
III.1 存储引擎的选择 ?Myisam：数据库并发不大，读多写少,而且都能很好的用到索引，sql语句比较简单的应用，TB数据仓库
?Innodb：并发访问大，写操作比较多，有外键、事务等需求的应用，系统内存较大。

III.2 命名规则 ?多数开发语言命名规则：比如MyAdress
?多数开源思想命名规则：my_address
?避免随便命名

III.3 字段类型选择字段类型的选择的一般原则：
?根据需求选择合适的字段类型，在满足需求的情况下字段类型尽可能小。
?只分配满足需求的最小字符数，不要太慷慨。
原因：更小的字段类型更小的字符数占用更少的内存，占用更少的磁盘空间，占用更少的磁盘IO，以及占用更少的带宽。

III.3.1 整型：见如下图:

类型	字节	最小值	最大值
		(带符号的/无符号的)	(带符号的/无符号的)
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)。

III.3.2 浮点型：

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

III.3.3 时间类型

类型	取值范围	存储空间	零值表示法
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-0100: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

III.3.4 字符类型

类型	最大长度	占用存储空间
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类型，因此建议定长和变长的数据分开。

III.4 编码选择单字节 latin1
多字节 utf8(汉字占3个字节，英文字母占用一个字节)
如果含有中文字符的话最好都统一采用utf8类型，避免乱码的情况发生。

III.5 主键选择原则注：这里说的主键设计主要是针对INNODB引擎
1.能唯一的表示行。
2.显式的定义一个数值类型自增字段的主键，这个字段可以仅用于做主键，不做其他用途。
3.MySQL主键应该是单列的，以便提高连接和筛选操作的效率。
4.主键字段类型尽可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT。
5.尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能。
6.MySQL主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
7.MySQL主键应当有计算机自动生成。
8.主键字段放在数据表的第一顺序。
推荐采用数值类型做主键并采用auto_increment属性让其自动增长。

III.6 其他需要注意的地方 ?NULL OR NOT NULL
尽可能设置每个字段为NOT NULL，除非有特殊的需求，原因如下：
1.使用含有NULL列做索引的话会占用更多的磁盘空间，因为索引NULL列需要而外的空间来保存。
2.进行比较的时候，程序会更复杂。
3.含有NULL的列比较特殊，SQL难优化，如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。

?索引
索引的缺点：极大地加速了查询，减少扫描和锁定的数据行数。
索引的缺点：占用磁盘空间，减慢了数据更新速度，增加了磁盘IO。
添加索引有如下原则：
1.选择唯一性索引。
2.为经常需要排序、分组和联合操作的字段建立索引。
3.为常作为查询条件的字段建立索引。
4.限制索引的数据，索引不是越多越好。
5.尽量使用数据量少的索引，对于大字段可以考虑前缀索引。
6.删除不再使用或者很少使用的索引。
7.结合核心SQL优先考虑覆盖索引。
8.忌用字符串做主键。

?反范式设计
适当的使用冗余的反范式设计，以空间换时间有的时候会很高效。

IV Mysql 软件优化 ?开启mysql复制，实现读写分离、负载均衡，将读的负载分摊到多个从服务器上，提高服务器的处理能力。
?使用推荐的GA版本，提升性能
?利用分区新功能进行大数据的数据拆分

V Mysql 配置优化注意：全局参数一经设置，随服务器启动预占用资源。
?key_buffer_size参数
mysql索引缓冲，如果是采用myisam的话要重点设置这个参数，根据（key_reads/key_read_requests）判断
?innodb_buffer_pool_size参数
INNODB 数据、索引、日志缓冲最重要的引擎参数，根据（hit riatos和FILE I/O）判断
?wait_time_out参数
线程连接的超时时间，尽量不要设置很大，推荐10s
?max_connections参数
服务器允许的最大连接数，尽量不要设置太大，因为设置太大的话容易导致内存溢出，需要通过如下公式来确定：
SET @k_bytes = 1024;
SET @m_bytes = @k_bytes * 1024;
SET @g_bytes = @m_bytes * 1024;
SELECT
(
@@key_buffer_size + @@query_cache_size + @@tmp_table_size+
@@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size+
@@innodb_log_buffer_size+
@@max_connections *

( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size+
@@join_buffer_size + @@binlog_cache_size + @@thread_stack
) )
/ @g_bytes AS MAX_MEMORY_USED_GB;

?thread_concurrency参数
线程并发利用数量，(cpu+disk)*2,根据(os中显示的请求队列和tickets)判断
?sort_buffer_size参数
获得更快的--ORDER BY,GROUP BY,SELECT DISTINCT,UNION DISTINCT
?read_rnd_buffer_size参数
当根据键进行分类操作时获得更快的--ORDER BY
?join_buffer_size参数
join连接使用全表扫描连接的缓冲大小，根据select_full_join判断
?read_buffer_size参数
全表扫描时为查询预留的缓冲大小，根据select_scan判断
?tmp_table_size参数
临时内存表的设置，如果超过设置就会转化成磁盘表，根据参数(created_tmp_disk_tables)判断
?innodb_log_file_size参数(默认5M)
记录INNODB引擎的redo log文件，设置较大的值意味着较长的恢复时间。
?innodb_flush_method参数(默认fdatasync)
Linux系统可以使用O_DIRECT处理数据文件，避免OS级别的cache，O_DIRECT模式提高数据文件和日志文件的IO提交性能
?innodb_flush_log_at_trx_commit(默认1)
1.0表示每秒进行一次log写入cache，并flush log到磁盘。
2.1表示在每次事务提交后执行log写入cache，并flush log到磁盘。
3.2表示在每次事务提交后，执行log数据写入到cache，每秒执行一次flush log到磁盘。

VI Mysql 语句级优化 1.性能查的读语句，在innodb中统计行数,建议另外弄一张统计表，采用myisam，定期做统计.一般的对统计的数据不会要求太精准的情况下适用。
2.尽量不要在数据库中做运算。
3.避免负向查询和%前缀模糊查询。
4.不在索引列做运算或者使用函数。
5.不要在生产环境程序中使用select * from 的形式查询数据。只查询需要使用的列。
6.查询尽可能使用limit减少返回的行数，减少数据传输时间和带宽浪费。
7.where子句尽可能对查询列使用函数，因为对查询列使用函数用不到索引。
8.避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’。
9.所有的SQL关键词用大写，养成良好的习惯，避免SQL语句重复编译造成系统资源的浪费。
10. 联表查询的时候，记得把小结果集放在前面，遵循小结果集驱动大结果集的原则。
11. 开启慢查询，定期用explain优化慢查询中的SQL语句。
【关系型数据库性能优化总结】