一、硬件配置优化
1、CPU选择：多核的CPU，主频高的CPU
2、内存：更大的内存
3、磁盘选择：更快的转速、RAID、阵列卡，
4、网络环境选择：尽量部署在局域网、SCI、光缆、千兆网、双网线提供冗余、0.0.0.0多端口绑定监听
二、操作系统级优化
1、使用64位的操作系统，更好的使用大内存。
2、设置noatime,nodiratime
[zhangxy@dowload_server1 ~]$ cat /etc/fstab
LABEL=/ / ext3 defaults,noatime,nodiratime 1 1
/dev/sda5 /data xfs defaults,noatime,nodiratime 1 2
3、优化内核参数
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 = 5000 65000
kernel.shmmax = 167108864
vm.swappiness=0
4、加大文件描述符限制
Vim /etc/security/limits.conf
加上
* soft nofile 65535
* hard nofile 65535
5、文件系统选择 xfs
/dev/sda5 /data xfs defaults,noatime,nodiratime 1 2
三、Mysql设计优化
1、存储引擎的选择
Myisam：数据库并发不大，读多写少,而且都能很好的用到索引，sql语句比较简单的应用，TB数据仓库
Innodb：并发访问大，写操作比较多，有外键、事务等需求的应用，系统内存较大。
2、命名规则
多数开发语言命名规则：比如MyAdress
多数开源思想命名规则：my_address
避免随便命名
3、字段类型选择
字段类型的选择的一般原则：
根据需求选择合适的字段类型，在满足需求的情况下字段类型尽可能小。
只分配满足需求的最小字符数，不要太慷慨。
原因：更小的字段类型更小的字符数占用更少的内存，占用更少的磁盘空间，占用更少的磁盘IO，以及占用更少的带宽。
4、编码选择
单字节：latin1
多字节：utf8(汉字占3个字节，英文字母占用一个字节)
如果含有中文字符的话最好都统一采用utf8类型，避免乱码的情况发生。
5、主键选择原则
注：这里说的主键设计主要是针对INNODB引擎
5-1. 能唯一的表示行。
5-2. 显式的定义一个数值类型自增字段的主键，这个字段可以仅用于做主键，不做其他用途。
5-3. MySQL主键应该是单列的，以便提高连接和筛选操作的效率。
5-4. 主键字段类型尽可能小，能用SMALLINT就不用INT，能用INT就不用BIGINT。
5-5. 尽量保证不对主键字段进行更新修改，防止主键字段发生变化，引发数据存储碎片，降低IO性能。
5-6. MySQL主键不应包含动态变化的数据，如时间戳、创建时间列、修改时间列等。
5-7. MySQL主键应当有计算机自动生成。
5-8. 主键字段放在数据表的第一顺序。
推荐采用数值类型做主键并采用auto_increment属性让其自动增长。
6、其他需要注意的地方
6-1. NULL OR NOT NULL
尽可能设置每个字段为NOT NULL，除非有特殊的需求，原因如下：
6-1-1. 使用含有NULL列做索引的话会占用更多的磁盘空间，因为索引NULL列需要而外的空间来保存。
6-1-2. 进行比较的时候，程序会更复杂。
6-1-3. 含有NULL的列比较特殊，SQL难优化，如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率。
6-2. 索引
索引的优点：极大地加速了查询，减少扫描和锁定的数据行数。
索引的缺点：占用磁盘空间，减慢了数据更新速度，增加了磁盘IO。
添加索引有如下原则：
6-2-1. 选择唯一性索引。
6-2-2. 为经常需要排序、分组和联合操作的字段建立索引。
6-2-3. 为常作为查询条件的字段建立索引。
6-2-4. 限制索引的数据，索引不是越多越好。
6-2-5. 尽量使用数据量少的索引，对于大字段可以考虑前缀索引。
6-2-6. 删除不再使用或者很少使用的索引。
6-2-7. 结合核心SQL优先考虑覆盖索引。
6-2-8. 忌用字符串做主键。
6-3. 反范式设计
适当的使用冗余的反范式设计，以空间换时间有的时候会很高效。
四、Mysql软件优化
1、开启mysql复制，实现读写分离、负载均衡，将读的负载分摊到多个从服务器上，提高服务器的处理能力。
2、使用推荐的GA版本，提升性能
3、利用分区新功能进行大数据的数据拆分
五、Mysql配置优化
注意：全局参数一经设置，随服务器启动预占用资源。
1、key_buffer_size参数
mysql索引缓冲，如果是采用myisam的话要重点设置这个参数，根据（key_reads/key_read_requests）判断
2、innodb_buffer_pool_size参数
INNODB 数据、索引、日志缓冲最重要的引擎参数，根据（hit riatos和FILE I/O）判断
3、wait_time_out参数
线程连接的超时时间，尽量不要设置很大，推荐10s
4、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;
5、thread_concurrency参数
线程并发利用数量，(cpu+disk)*2,根据(os中显示的请求队列和tickets)判断
6、sort_buffer_size参数
获得更快的–ORDER BY,GROUP BY,SELECT DISTINCT,UNION DISTINCT
7、read_rnd_buffer_size参数
当根据键进行分类操作时获得更快的–ORDER BY
8、join_buffer_size参数
join连接使用全表扫描连接的缓冲大小，根据select_full_join判断
9、read_buffer_size参数
全表扫描时为查询预留的缓冲大小，根据select_scan判断
10、tmp_table_size参数
临时内存表的设置，如果超过设置就会转化成磁盘表，根据参数(created_tmp_disk_tables)判断
11、innodb_log_file_size参数(默认5M)
记录INNODB引擎的redo log文件，设置较大的值意味着较长的恢复时间。
12、innodb_flush_method参数(默认fdatasync)
Linux系统可以使用O_DIRECT处理数据文件，避免OS级别的cache，O_DIRECT模式提高数据文件和日志文件的IO提交性能
13、innodb_flush_log_at_trx_commit(默认1)
13-1. 0表示每秒进行一次log写入cache，并flush log到磁盘。
13-2. 1表示在每次事务提交后执行log写入cache，并flush log到磁盘。
13-3. 2表示在每次事务提交后，执行log数据写入到cache，每秒执行一次flush log到磁盘。
六、Mysql语句级优化
1、性能查的读语句，在innodb中统计行数,建议另外弄一张统计表，采用myisam，定期做统计.一般的对统计的数据不会要求太精准的情况下适用。
2、尽量不要在数据库中做运算。
3、避免负向查询和%前缀模糊查询。
4、不在索引列做运算或者使用函数。
5、不要在生产环境程序中使用select * from 的形式查询数据。只查询需要使用的列。
6、查询尽可能使用limit减少返回的行数，减少数据传输时间和带宽浪费。
7、where子句尽可能对查询列使用函数，因为对查询列使用函数用不到索引。
8、避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’。
9、所有的SQL关键词用大写，养成良好的习惯，避免SQL语句重复编译造成系统资源的浪费。
10、联表查询的时候，记得把小结果集放在前面，遵循小结构及驱动大结果集的原则。
11、开启慢查询，定期用explain优化慢查询中的SQL语句。
转自：http://blog.chinaunix.net/space.php?uid=20639775&do=blog&id=3154234
转载请注明：LINUX服务器运维架构技术分享 » Mysql数据库优化总结