Mysql数据库优化总结2

时间：2021-12-02 11:01:47

说明：本文的环境为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=/         /          ext3    defaults,noatime,nodiratime        1 1

/dev/sda5      /data       xfs     defaults,noatime,nodiratime        1 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 = 5000    65000

kernel.shmmax = 167108864

vm.swappiness=0

  • 加大文件描述符限制

Vim /etc/security/limits.conf

加上

*        soft    nofile  65535

*        hard    nofile  65535

  • 文件系统选择 xfs

/dev/sda5      /data       xfs     defaults,noatime,nodiratime        1 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版本，提升性能
  • 利用分区新功能进行大数据的数据拆分

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语句。

    12. 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

      2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，

      Sql 代码 : select id from t where num is null;

      可以在 num 上设置默认值 0,确保表中 num 列没有 null 值，然后这样查询：

      Sql 代码 : select id from t where num=0;

      3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

      4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，

      Sql 代码 : select id from t where num=10 or num=20;

      可以这样查询：

      Sql 代码 : select id from t where num=10 union all select id from t where num=20;

      5.in 和 not in 也要慎用，否则会导致全表扫描，如：

      Sql 代码 : select id from t where num in(1,2,3);

      对于连续的数值，能用 between 就不要用 in 了：

      Sql 代码 : select id from t where num between 1 and 3;

      6.下面的查询也将导致全表扫描：

      Sql 代码 : select id from t where name like 'c%';

      若要提高效率，可以考虑全文检索。

      7.如果在 where 子句中使用参数，也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量，但优 化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而，如果在编译时建立访问计 划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

      Sql 代码 : select id from t where num=@num ;

      可以改为强制查询使用索引：

      Sql 代码 : select id from t with(index(索引名)) where num=@num ;

      8.应尽量避免在 where 子句中对字段进行表达式操作， 这将导致引擎放弃使用索引而进行全表扫描。

      Sql 代码 : select id from t where num/2=100;

      可以这样查询：

      Sql 代码 : select id from t where num=100*2;

      9.应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

      Sql 代码 : select id from t where substring(name,1,3)='abc';#name 以 abc 开头的 id

      应改为：

      Sql 代码 : select id from t where name like 'abc%';

      10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用 索引。

      11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件 时才能保证系统使用该索引， 否则该索引将不会 被使用， 并且应尽可能的让字段顺序与索引顺序相一致。

      12.不要写一些没有意义的查询，如需要生成一个空表结构：

      Sql 代码 : select col1,col2 into #t from t where 1=0;

      这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

      Sql 代码 : create table #t(…);

      13.很多时候用 exists 代替 in 是一个好的选择：

      Sql 代码 : select num from a where num in(select num from b);

      用下面的语句替换：

      Sql 代码 : select num from a where exists(select 1 from b where num=a.num);

      14.并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时， SQL 查询可能不会去利用索引，如一表中有字段 ***,male、female 几乎各一半，那么即使在 *** 上建 了索引也对查询效率起不了作用。

      15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

      16.应尽可能的避免更新 clustered 索引数据列， 因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

      17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并 会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言 只需要比较一次就够了。

      18.尽可能的使用 varchar/nvarchar 代替 char/nchar , 因为首先变长字段存储空间小， 可以节省存储空间， 其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

      19.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

      20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限(只有主键索引)。

      21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

      22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用 表中的某个数据集时。但是，对于一次性事件， 最好使用导出表。

      23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大，为了缓和系统表的资源，应先 create table,然后 insert.

      24.如果使用到了临时表， 在存储过程的最后务必将所有的临时表显式删除， 先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

      25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过 1 万行，那么就应该考虑改写。

      26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更 有效。

      27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

      28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF .无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

      29.尽量避免大事务操作，提高系统并发能力。 sql 优化方法使用索引来更快地遍历表。 缺省情况下建立的索引是非群集索引，但有时它并不是最佳的。在非群集索引下，数据在物理上随机存放在数据页上。合理的索引设计要建立在对各种查询的分析和预测上。一般来说：

      a.有大量重复值、且经常有范围查询( > ,< ,> =,< =)和 order by、group by 发生的列，可考虑建立集群索引;

      b.经常同时存取多列，且每列都含有重复值可考虑建立组合索引;

      c.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。索引虽有助于提高性能但 不是索引越多越好，恰好相反过多的索引会导致系统低效。用户在表中每加进一个索引，维护索引集合就 要做相应的更新工作。

      30.定期分析表和检查表。

      分析表的语法：ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name[, tbl_name]...

      以上语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。在分析期间，使用一个读取锁定对表进行锁定。这对于MyISAM，DBD和InnoDB表有作用。

      例如分析一个数据表：analyze table table_name
      检查表的语法：CHECK TABLE tb1_name[,tbl_name]...[option]...option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

      检查表的作用是检查一个或多个表是否有错误，CHECK TABLE 对MyISAM 和 InnoDB表有作用，对于MyISAM表，关键字统计数据被更新

      CHECK TABLE 也可以检查视图是否有错误，比如在视图定义中被引用的表不存在。

      31.定期优化表。

      优化表的语法：OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [,tbl_name]...

      如果删除了表的一大部分，或者如果已经对含有可变长度行的表(含有 VARCHAR、BLOB或TEXT列的表)进行更多更改，则应使用OPTIMIZE TABLE命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE TABLE 命令只对MyISAM、 BDB 和InnoDB表起作用。

      例如： optimize table table_name

      注意： analyze、check、optimize执行期间将对表进行锁定，因此一定注意要在MySQL数据库不繁忙的时候执行相关的操作。

      补充：

      1、在海量查询时尽量少用格式转换。

      2、ORDER BY 和 GROPU BY:使用 ORDER BY 和 GROUP BY 短语，任何一种索引都有助于 SELECT 的性能提高。

      3、任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移 至等号右边。

      4、IN、OR 子句常会使用工作表，使索引失效。如果不产生大量重复值，可以考虑把子句拆开。拆开的子 句中应该包含索引。

      5、只要能满足你的需求，应尽可能使用更小的数据类型：例如使用 MEDIUMINT 代替 INT

      6、尽量把所有的列设置为 NOT NULL,如果你要保存 NULL,手动去设置它，而不是把它设为默认值。

      7、尽量少用 VARCHAR、TEXT、BLOB 类型

      8、如果你的数据只有你所知的少量的几个。最好使用 ENUM 类型

      9、正如 graymice 所讲的那样，建立索引。

      10、合理用运分表与分区表提高数据存放和提取速度。

      转自:http://blog.chinaunix.net/uid-20639775-id-3154234.html

相关文章