|
MySQL评估范围：
第一章 需要开发商提供的资料
1数据字典
（提供详细的数据库所有表的详细说明，如下格式，外加表的简单描述）
2常用SQL
（针对数据字典，给出每个表上的常发生的SQL语句，形式如，针对上面的表给出的SQL）
3表数据量与频度
（预估上面每个表的一年内的数据增长量，和每个表的读写频率，如） (之后可以使用MONyog来观察每个库以及每个表的数据和索引的大小) 4定期清理机制
对log或history等类型表的清理机制 第二章 设计问题
1保留关键字
不要使用MySQL保留关键字作为名称，容易造成SQL语法错误。 2字段默认值设计
表的字段尽量使用NOT NULL。 摘自MySQL文档： “NULL columns require additional space in the row to record whether
their values are NULL. For MyISAM tables, each NULL column takes one bit extra,
rounded up to the nearest byte.” 3字段设计
数字和日期类型：  实际上还有一类数字类型是通过二进制格式以字符串来存放的，如，DECIMAL(DEC)[(M[,D])]，NUMERIC[(M[,D])]，由于其存放长度主要通过其定义时候的M所决定，M定义多大，则实际就有多长。M代表整个位数的长度，D代表小数殿后的位数，默认M为10，D为0。一般考虑到这种数据完全可以变化形式以整数存放，所以这种数据类型（）一般不用。但是，在mysql 中float、double（或real）是浮点数，decimal（或numberic）是定点数。浮点数（float、double）存在误差问题；对货币等对精度敏感的数据，应该用定点数表示或存储；编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较； 另外IP地址字段的话，尽量设置为UNSIGNED INT（32无符号整形），查询时可以使用INET_ATON()把一个字符串IP转成一个整形，并使用INET_NTOA()把一个IP转换成字符串IP。 时间存储格式，常用的是DATETIME，DATE，TIMESTAMP这三种，从存储空间看TIMESTAMP最少，4个字节，而其他两种都要8个字节，多了一倍。如果有需要存放早于1970年之前的时间，则要使用DATETIME类型，假如不需要，则最好使用TIMESTAMP来减少存储空间的占用。 字符存储类型：
mysql5.0.3之后的表示，VARCHAR[(M)]表示的字节数，最大限制和字符集有关，如果是gbk编码，最大长度为(65535-1-2)/2=32766，减1的原因是实际行存储从第二个字节开始，减2的原因是varchar头部的2个字节表示长度，除2因为是gbk编码；如果是utf8编码，最大长度为(65535-1-2)/3=21844。字段尽量选择固定长度。另外MySQL 5.0之后版本存储VARCHAR类型的时候会保留末尾空格，CHAR[(M)]都是以空格填补剩余的空间，所以，如果要保留结尾空格的话，一定要使用VARCHAR。 另外不要使用数字作为ENUM枚举常量！ 尽量用char(1)来代替bit(1)，尽量避免程序错误！ MyISAM表的话，尽量使用char()代替varchar()；对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），主要的性能因素是数据行使用的存储总量，建议使用varcahr()，可以减少存储空间。 对于BLOB和TEXT字段，如果可以，就把这些字段分拆到另外一个表里面去。 4字符集选择
mysql的字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。字符集是用来定义mysql存储字符串的方式，校对规则是定义了比较字符串的方式。 UTF-8：是用以解决国际上字符的一种多字节编码，它对英文使用8位（即一个字节），中文使用24位（三个字节）来编码。UTF-8包含全世界所有国家需要用到的字符，是国际编码，通用性强。 GBK： 是国家标准GB2312基础上扩容后兼容GB2312的标准。GBK的文字编码是用双字节来表示的，即不论中、英文字符均使用双字节来表示，为了区分中文，将其最高位都设定成1。GBK包含全部中文字符，是国家编码，通用性比UTF8差，不过UTF8占用的数据库比GBK大。 为了避免所有乱码问题，应该采用UTF-8，将来要支持国际化也非常方便UTF-8可以看作是大字符集，它包含了大部分文字的编码。 5命名规范
1、
数据库和表名应尽可能和所服务的业务模块名一致； 2、
服务于同一子模块的一类表尽量以子模块名（或部分单词）为前缀或后缀； 3、
表名应尽量包含与所存放数据相对应的单词； 4、
字段名称也尽量保持和实际数据相对应 5、
索引名称尽量包含所有的索引键字段名或者缩写，且各字段名在索引名中的顺序应与索引键在索引中的索引顺序一致，且尽量包含一个类似于idx 或者ind 之类的前缀或者后缀，以表名其对象类型是索引，同时还可以包含该索引所属表的名称； 6、
约束等其他对象也应该尽可能包含所属表或其他对象的名称，以表名各自关系。 7、
同一个SQL语句中必须统一大小写，不允许SELECT * FROM my_table
WHERE MY_TABLE.col=1; 6索引的设计
说明：MyISAM的主键索引（聚集索引）和普通索引（非聚集索引）；InnoDB中索引分为聚集索引形式的主键索引和非聚集索引形式的普通索引。 ·频繁的作为条件进行查询的字段应创建索引 ·唯一性太差的字段不要创建索引 ·频繁更新的字段不适合创建索引 ·能建立组合索引的尽量建立组合索引 ·索引键长不能过长（最大限制为1000个字节） ·join条件字段类型不一致的时候mysql无法使用索引 ·如果一个字段设计成用来存储URL，则可以新增一个被索引的url_crc列，使用CRC32做哈希 ·如果对较长的字段使用索引，可以考虑前缀索引（注意索引唯一性） ·避免使用重复索引(可以使用pt-duplicate-key-checker来检查重复索引) ·统计未使用的索引(可以使用pt-index-usage来查看) ·只有MyISAM
存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR 和TEXT 列 7外键问题
InnoDB中定义外键时需要注意的地方：
7准实时性数据处理
如系统当前在线人数，论坛系统当前总帖数、回帖数等，多条件大结果集查询页面的总结果数以及 总页数，某些虚拟积分的top n 排名等等。这些统计的计算都会涉及到大量的数据，同时也需要大量的计算资源，访问频率也都非常的高。如果都通过实时统计，恐怕只要数据量稍微大一些，都会带来非常大的硬件资源开销。但在短时间内的不够精确，又并不会带来太大用户体验的降低。所以完全可以通过定时任务程序（如cront跑一个脚本定时的去算统计数据），没隔一定时间段进行一次统计后存放在专门设计的统计表中。这样，在统计数据需要展示的时候，我们只需要从统计好的结果数据中取出即可。
8垂直分割需求
示例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，除了个人信息外，并不需要经常读取或是改写这个字段。那么，可以把这个字段设计到另外一张表中。这样的表有更好的性能。
示例二： 你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。 另外，你需要注意的是，这些被分出去的字段所形成的表，确保不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是指数级的下降。
9水平拆分需求
譬如DNWeb的eventlog表，里面的log类型分为很多种，而且这个表又比较大，所以这个时候最好做水平拆分，将像登陆记录，统计在线人数等类型分离出去。 10适度冗余的设计
被频繁引用且只能通过join 2张（或者更多）大表的方式才能得到的独立小字段，适合作为一个冗余字段，但要保证数据的一致性！ 第三章
数据库安全审核1账户和权限
执行：SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query
FROM mysql.user; （可以根据MySQL Workbench这个软件来看）
第四章
MySQL优化1 MySQL安装优化（1）二进制的发行版包括RPM等包装好的特定二进制版本： 好处： a) 通过非常简单的安装方式快速完成MySQL 的部署； b) 安装版本是经过比较完善的功能和性能测试的编译版本； c) 所使用的编译参数更具通用性的，且比较稳定； 不足：存储引擎的种类，字符集的选择，安装目录，等都是定制好的（后期也可以更改），一台server上只能安装一个MySQL。 补充RPM默认安装路径：
（2）源码编译安装： 好处： a) 针对自己的硬件平台选用合适的编译器来优化编译后的二进制代码； b) 根据不同的软件平台环境调整相关的编译参数； c) 针对我们特定应用场景选择需要什么组件不需要什么组件； d) 根据我们的所需要存储的数据内容选择只安装我们需要的字符集； e) 同一台主机上面可以安装多个MySQL； 不足： a) 对编译参数的不够了解造成编译参数使用不当可能使编译出来的二进制代码不够稳定； b) 对自己的应用环境把握失误而使用的优化参数可能反而使系统性能更差； c) 还有一个并不能称之为隐患的小问题就是源码编译安装将使安装部署过程更为复杂，所花费的时间更长
2 MySQL日志设置优化首先看下BINLOG（做主从的时候要开启）（默认情况下，系统仅仅打开错误日志log_error）： ·binlog_cache_size：在事务过程中容纳二进制日志SQL语句的缓存大小，是为每个客户端分配的。可以从binlog_cache_use和binlog_cache_disk_use来判断binlog_cache_size的设置是否合理。 ·max_binlog_cache_size：指所有binlog能够使用的最大cache内存大小。默认即可 ·max_binlog_size：binlog日志的最大值，一般设置为512M或者1G，最大为1G。 ·sync_binlog：默认0，表示当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让filesystem自行决定什么时候同步，或者cache满了之后才同步到磁盘。这个时候性能是最好的，但也是风险较大的。设置为1的时候，故障时最多只丢失binglog_cache中未完成的一个事务，是最安全但是性能损耗较大。对于高并发事务系统来说，sync_binlog保持默认即可。 ·binlog_do_db：设置哪些数据库需要记录binlog；最好不要使用此设置 ·binlog_ignore_db：设置哪些数据库不需要记录binlog；最好不要使用此设置 例如：
SLOW QUERY LOG相关参数及使用建议（初期临时可以开启，动态变量可以直接关闭set global slow_query_log=OFF;）：
3 Query Cache优化QueryCache的缺点（所以一般禁用query_cache）：后端的任何一个表的任何一条数据发生变化后，就会将与该表有关的query的cache全部失效。query语句的hash运算以及hash查找资源CPU消耗；query cache中缓存的是result set，而不是数据页 QueryCache相关参数： ·have_query_cache：是否支持Query Cache； ·query_cache_limit：存放的单条query最大的result set，默认1M； ·query_cache_min_res_unit：query cache每个result set存放的最小内存大小，默认4K； ·query_cache_size：系统中用于query cache内存的大小；默认1048576B=1024K ·query_cache_type：是否启用Query Cache；（>= 5.6.8，默认禁止） ·query_cache_wlock_invalidate：针对MyISAM存储引擎，设置当有write lock在某个table上面的时候，读请求时要等write lock释放资源之后再查询，还是允许直接从query cache中读取结果，默认为false（可以直接从query cache中取出结果）； Query Cache 命中率=
Qcache_hits / ( Qcache_hits + Qcache_inserts )； 例如：
4 MySQL Server其他常用优化网络连接与连接线程： ·max_connections：整个MySQL
允许的最大连接数；一般来说500 到800 左右是一个比较合适的参考值，默认为151，可以依照Max_used_connections
/ max_connections * 100% ≈ 85%。 ·net_buffer_length：网络包传输中，传输消息之前的net buffer 初始化大小；系统默认大小为16KB，一般来说可以满足大多数场景 ·max_allowed_packet：在网络传输中，一次传消息输量的最大值；这个参数与net_buffer_length 相对应，只不过是net buffer 的最大值。系统默认值为16MB，最大值是1GB，必须设定为1024 的倍 数，单位为字节。当与大的BLOB字段一起工作，可以适当增加。 ·back_log：在MySQL 的连接请求等待队列中允许存放的最大连接请求数。 (MySQL>= 5.6.6)默认为-1 (autosized)，初始为
50 + (max_connections / 5)，封顶为900 ·thread_cache_size：Thread Cache 池中应该存放的连接线程数。一般来说可能50 到100 之间应该就可以了。默认为-1 (autosized)，初始为 8 + (max_connections / 100)，封顶为100 ·thread_stack：每个连接线程被创建的时候，MySQL 给他分配线程使用的堆大小。使用系统的默认值（256KB）基本上可以所有的普通应用环境。 Threads_Cache_Hit =
(Connections - Threads_created) / Connections * 100%
Table Cache相关的优化： ·table_open_cache：设置系统中Cache
的打开表文件描述符的数量，默认2000, (MySQL>= 5.6.12, autosized)。
Sort Buffer和Join Buffer： ·join_buffer_size：当Join是ALL，index，rang或者index_merge 的时候使用的Buffer；一般设置为1-2M即可。 ·sort_buffer_size：系统中对数据进行排序的时候使用的Buffer；一般设置设置为2-4M即可。 例如：
5 MyISAM常用优化索引缓存优化： ·key_buffer_size：索引缓存大小；一般来说，如果我们的MySQL 是运行在32 位平台纸上，此值建议不要超过2GB 大小。如果是运行在64 位平台纸上则不用考虑此限制，但也最好不要超过4GB。(只适用于MyISAM
Server) ·key_buffer_block_size，索引缓存中的Cache Block Size，默认即可；
Key_buffer_UsageRatio = (1 -
Key_blocks_used/(Key_blocks_used + Key_blocks_unused)) *100% Key_Buffer_Read_HitRatio = (1 -
Key_reads/Key_read_requests) * 100% Key_Buffer_Write_HitRatio
= (1 - Key_writes/Key_Write_requests) * 100%
表读取缓存优化： ·read_buffer_size，以Sequential
Scan 方式扫描表数据时候使用的Buffer；每个Thread
进行Sequential Scan 的时候都会产生该Buffer，所以在设置的时候尽量不要太高，避免因为并发太大造成内存不够。系统默认为128KB，最大为2GB，设置的值必须是4KB
的倍数，否则系统会自动更改成小于设置值的最大的4KB 的倍数。可以尝试适当调大此参数看是否能够改善全表扫描的性能，一般设置为2MB即可。 ·read_rnd_buffer_size，进行Random
Scan 的时候使用的Buffer；read_rnd_buffer_size
的默认值256KB，最大值为4G。一般来说，read_rnd_buffer_size 值的适当调大，对提高ORDER BY 操作的性能有一定的效果。一般设置为read_rnd_buffer_size
= 8M
并发优化： ·concurrent_insert：提高INSERT 操作和SELECT 之间的并发处理，使二者尽可能并行。大部分情况下concurrent_insert 的值都被设置为1，当表中没有删除记录留下的空余空间的时候都可以在尾部并行插入。这其实也是MyISAM 的默认设置。如果我们的系统主要以写为主，尤其是有大量的INSERT 的时候。为了尽可能提高INSERT 的效率，我们可以将concurrent_insert 设置为2，也就是告诉MyISAM，不管在表中是否有删除行留下的空余空间，都在尾部进行并发插入，使INSERT 和SELECT 能够互不干扰。默认设置AUTO，保留默认设置即可。 例如：
6 Innodb常用优化Innodb缓存相关优化： ·innodb_buffer_pool_size：设置Innodb 最主要的Buffer(Innodb_Buffer_Pool)的大小，也就是缓存用户表及索引数据的最主要缓存空间，对Innodb 整体性能影响也最大。一般为设置Innodb 的Buffer Pool 设置为整个系统物理内存的50% ～ 80%
之间 ·innodb_log_buffer_size：这个参数就是用来设置Innodb 的Log Buffer 大小的，系统默认值为1MB。Log Buffer的主要作用就是缓冲Log 数据，提高写Log 的IO 性能。一般来说，如果你的系统不是写负载非常高且以大事务居多的话，8MB 以内的大小就完全足够了。 ·innodb_additional_mem_pool_size：是用于存放Innodb
的字典信息和其他一些内部结构所需要的内存空间。一个常规的几百个Innodb 表的MySQL，如果不是每个表都是上百个字段的话，20MB 内存已经足够了。 ·innodb_doublewrite：Innodb 在将数据同步到数据文件进行持久化之前，首先会将需要同步的内容写入存在于表空间中的系统保留的存储空间，也就是被我们称之为Double Write Buffer 的地方，然后再将数据进行文件同步。默认ON即可。 ·innodb_adaptive_hash_index：浅显一点就是给Buffer Pool 中的数据做的索引，保持默认即可。
事务优化： ·innodb_flush_log_at_trx_commit：一般来说，如果完全不能接受数据的丢失，那么我们肯定会通过牺牲一定的性能来换取数据的安全性，选择设置为1。而如果我们可以丢失很少量的数据(比如说1 秒之内)，那么我们可以设置为0。当然，如果大家觉得我们的OS 足够稳定，主机硬件设备，而且主机的供电系统也足够安全，我们也可以将innodb_flush_log_at_trx_commit
设置为2 让系统的整体性能尽可能的高。（说明：如果是游戏服务器，建议此值设置为2；如果是对数据安全要求极高的应用，建议设置为1；设置为0性能最高，但如果发生故障，数据可能会有丢失的危险！）
其他方面优化： ·innodb_flush_method：如果磁盘是通过RAID 卡做了硬件级别的RAID，建议可以使用O_DIRECT，可以一定程度上提高IO 性能。 ·innodb_thread_concurrency：主要控制Innodb 内部的并发处理线程数量的最大值，默认为0，完全让Innodb
自己根据实际需要创建并行线程，而且在不少场景下设置为0 还是一个非常不错的选择，尤其是当系统写IO 压力较大的时候。过高的值可能导致线程的互斥。 例如：
7 数据存储优化分散IO提升磁盘响应： 可以通过innodb_log_group_home_dir
参数来指定Innodb 日志存放位置，同时再通过设置数据文件位置innodb_data_home_dir 参数来告诉Innodb 我们希望将数据文件存放在哪里。
详细参照：MySQL_InnoDB_MyISAM优化及监控.docx 第五章 MySQL监控1使用pt-mysql-summary查看服务器配置这个工具在percona-toolkit-2.2.5-2.noarch.rpm中(下面有介绍)，使用方法如下： /usr/bin/pt-mysql-summary
--user=root --password=root --save-samples=/usr/tuzhen/pt-mysql-summary-2 对MySQL配置有个全面的了解。 2关注连接数如果连接数达到了最大连接数，那不管有多少资源，用户都会阻塞在外面。 修改mysql最大连接数： 打开my.cnf，修改max_connections=100(默认为100)。 mysql>
show global status like ‘Max_used_connections’; 检查下最大的过往使用连接数，这个值在max_connections的85%左右是比较合适的，如果过高则是max_connections过少或者系统负荷过高了。
3 show full
processlist显示所有进程 mysql>
show full processlist; +—–+——+———–+——+———+——+——-+———————–+ |
Id | User | Host | db
| Command | Time | State | Info | +—–+——+———–+——+———+——+——-+———————–+ |
629 | root | localhost | NULL | Query
| 0 | NULL | show full processlist | |
633 | root | localhost | NULL | Sleep
| 11 | | NULL | +—–+——+———–+——+———+——+——-+———————–+ 2
rows in set (0.00 sec) 如果正在运行的语句太多，运行时间太长，表示MySQL效率有问题。必要的时候可以将对应的进程kill掉。 mysql>
kill 633; Query
OK, 0 rows affected (0.00 sec) 关注TIME参数，看看正在运行的用户进程有多少是长时间占用的，具体分析下。 4使用mysqlreport关注Connections和Threads__
Connections _________________________________________________________ Max
used 6 of 500
%Max: 1.20 Total 914.64k 0.1/s __
Threads _____________________________________________________________ Running 2 of 3 Cached 3 of 8
%Hit: 64.38 Created 325.79k 0.0/s Slow 0 0/s 5关注系统锁情况可以通过Cacti或者MySQLReport来监控，也可以show status like ‘%lock%’; 使用mysqlreport关注Table Locks，InnoDB Lock。 6关注慢查询日志在需要是可以将慢查询日志打开: 在mysql配置文件my.cnf中增加 log-slow-queries=/var/lib/mysql/slowquery.log
(指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log) long_query_time=2
(记录超过的时间，默认为10s，设置为0时，记录所有的查询) log-queries-not-using-indexes
(log下来没有使用索引的query,可以根据情况决定是否开启) log-long-format
(如果设置了，所有没有使用索引的查询也将被记录) 分析慢查询日志： ·返回平均查询时间最长的前十条语句 /usr/local/mysql/bin/mysqldumpslow -s at -t 10
/usr/local/mysql/var/slow_queries.log ·返回查询次数最对的前十条语句 /usr/local/mysql/bin/mysqldumpslow
-s c -t 10 /usr/local/mysql/var/slow_queries.log; ·返回查询记录数最多的前十条语句 /usr/local/mysql/bin/mysqldumpslow
-s r -t 10 on; 7整理索引碎片尽量不要让表的行数过大，有索引的尽量控制在1千万以内，没有索引的尽量控制在500万行。 使用脚本mysql_fragment_optimize.sh（F:\百度云盘\百度云\MySQL\05_优化\02_索引碎片整理\BFOLogDB01） 8使用percona-toolkit-2.2.5-2.noarch.rpm 先开启慢查询日志，再利用这个套件里面的 /usr/bin/pt-query-digest命令去查看整个服务器的慢查询情况。(F:\百度云盘\百度云\MySQL\08_服务器测试\02_pt-query-digest)，作用=mysqldumpslow 9利用SHOW PROFILE单个语句进行分析 步骤大概如下： --开启（这里开启的是会话级别的） mysql>
set prfiling=1; --查询语句 mysql>
select * from test001 where id=2; --查看profiles mysql>
show profiles; --查看第一个profile mysql>
show profile for query 1; --按每个步骤消耗的时间排序，进一步查看 mysql>
set @query_id=1; mysql>
SELECT STATE,SUM(DURATION) AS Total_R, ->
ROUND( ->
100*SUM(DURATION)/ ->
(SELECT SUM(DURATION) ->
FROM INFORMATION_SCHEMA.PROFILING ->
WHERE QUERY_ID=@query_id ->
),2) AS Pct_R, ->
COUNT(*) AS Calls, ->
SUM(DURATION) / COUNT(*) AS "R/Call" -> FROM INFORMATION_SCHEMA.PROFILING -> WHERE QUERY_ID=@query_id -> GROUP BY STATE -> ORDER BY Total_R DESC; 10主从状态监控 在从服务器上运行shell脚本：
第六章
建立性能基线
1使用Cacti监控
部署Cacti性能监控，观察性能曲线； 2部署MySQL Report监控服务器状态
（参考E:\百度云同步盘\MySQL\04_监控工具） 3使用MySQL Workbench管理数据库
4其他监控工具MONyog
MONyog—收费：（参照E:\百度云同步盘\软件\MySQL_Monitor_MONyog） 可以在CB阶段使用；
Tuning-primer—免费：（参照E:\百度云同步盘\MySQL\04_监控工具\02_tuning-primer） 可以直接在运行过一段时间的MySQL服务器上使用，可以给出服务器参数配置建议 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
