mysql 优化包括哪些内容？

mysql的优化大的有两方面：

1、配置优化

配置的优化其实包含两个方面的： *** 作系统内核的优化和mysql配置文件的优化

1)系统内核的优化对专用的mysql服务器来说，无非是内存实用、连接数、超时处理、TCP处理等方面的优化，根据自己的硬件配置来进行优化，这里不多讲；

2)mysql配置的优化，一般来说包含：IO处理的常用参数、最大连接数设置、缓存使用参数的设置、慢日志的参数的设置、innodb相关参数的设置等，如果有主从关系在设置主从同步的相关参数即可，网上的相关配置文件很多，大同小异，常用的设置大多修改这些差不多就够用了。

2、sql语句的优化

1）  尽量稍作计算

Mysql的作用是用来存取数据的，不是做计算的，做计算的话可以用其他方法去实现，mysql做计算是很耗资源的。

2）尽量少 join

MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈

3）尽量少排序

排序 *** 作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL的响应时间。

对于MySQL来说，减少排序有多种办法，比如：

通过利用索引来排序的方式进行优化

减少参与排序的记录条数

非必要不对数据进行排序

4）尽量避免 select *

在数据量少并且访问量不大的情况下，select * 没有什么影响，但是量级达到一定级别的时候，在执行效率和IO资源的使用上，还是有很大关系的，用什么字段取什么字段，减少不必要的资源浪费。

5）尽量用 join 代替子查询

虽然 Join 性能并不佳，但是和 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 /data xfs defaults,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

加上

*softnofile 65535

*hardnofile 65535

文件系统选择 xfs

/dev/sda5 /data xfs defaults,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-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

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版本，提升性能

利用分区新功能进行大数据的数据拆分

VMysql配置优化

注意：全局参数一经设置，随服务器启动预占用资源。

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)

表示每秒进行一次log写入cache，并flush log到磁盘。

表示在每次事务提交后执行log写入cache，并flush log到磁盘。

表示在每次事务提交后，执行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语句。

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.

1. 优化一览图

2. 优化

笔者将优化分为了两大类,软优化和硬优化,软优化一般是 *** 作数据库即可,而硬优化则是 *** 作服务器硬件及参数设置.

2.1 软优化

2.1.1 查询语句优化

1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.

2.例:

显示:

其中会显示索引和查询数据读取数据条数等信息.

2.1.2 优化子查询

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

2.1.3 使用索引

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者<MySQL数据库索引>一文,介绍比较详细,此处记录使用索引的三大注意事项:

2.1.4 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

2.1.5 中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

2.1.6 增加冗余字段

类似于创建中间表,增加冗余也是为了减少连接查询.

2.1.7 分析表,,检查表,优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

1. 分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user

2. 检查表: 使用 CHECK关键字,如CHECK TABLE user [option]

option 只对MyISAM有效,共五个参数值:

3. 优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

2.2 硬优化

2.2.1 硬件三件套

1.配置多核心和频率高的cpu,多核心可以执行多个线程.

2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.

3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行 *** 作的能力.

2.2.2 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

2.2.3 分库分表

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

2.2.4 缓存集群

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。

一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.

---