MySQL数据库设计及性能优化手顺

性能指标： TPS - Transactions Per Second（每秒传输的事物处理个数），这是指服务器每秒处理的事务数，支持事务的存储引擎如InnoDB等特有的一个性能指标。 TPS = (COM_COMMIT + COM_ROLLBACK)/UPTIME TPS是TransactionsPerSecond的缩写，也就是事务数/秒。它是软件测试结果的测量单位。一个事务是指一个客户机向服务器发送请求然后服务器做出反应的过程。客户机在发送请求时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务个数，最终利用这些信息来估计得分。客户机使用加权协函数平均方法来计算客户机的得分，测试软件就是利用客户机的这些信息使用加权协函数平均方法来计算服务器端的整体TPS得分。 QPS - Queries Per Second（每秒查询处理量）同时适用与InnoDB和MyISAM 引擎 QPS=QUESTIONS/UPTIME Queries Per Second意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。 优化的方向 硬件：CPU、内存、磁盘 操作系统参数 MySQL参数my.cnf配置文件 SQL语句：工具 iDB （阿里巴巴开源），inception（美团开源） 基准测试工具sysbench

• sysbench安装 Quick install instructions: Debian/Ubuntu curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash sudo apt -y install sysbench RHEL/CentOS: curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash sudo yum -y install sysbench Fedora: curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash sudo dnf -y install sysbench #查看版本sysbench --version

• sysbench对数据库进行压力测试的过程： 　　1　　prepare 阶段 这个阶段是用来做准备的、比较说建立好测试用的表、并向表中填充数据。 　　2　　run 阶段 这个阶段是才是去跑压力测试的SQL 　　3　　cleanup 阶段 这个阶段是去清除数据的、也就是prepare阶段初始化好的表要都drop掉

• 测试的命令演示 sysbench 自带了一些测试脚本，在安装目录下 /usr/share/sysbench sysbench oltp_insert help See 'sysbench <testname> help' for a list of options for each test. 第一步 prepare：sysbench oltp_insert --mysql-host=192.168.1.6 --mysql-port=3306 --mysql-user=root --mysql-password='1qaz!QAZ' --mysql-db=sbtest --threads=10 --time=120 --report-interval=10 --db-driver=mysql prepare 第二步：sysbench oltp_insert --mysql-host=192.168.1.6 --mysql-port=3306 --mysql-user=root --mysql-password='1qaz!QAZ' --mysql-db=sbtest --threads=10 --time=120 --report-interval=10 --db-driver=mysql run 第三步：sysbench oltp_insert --mysql-host=192.168.1.6 --mysql-port=3306 --mysql-user=root --mysql-password='1qaz!QAZ' --mysql-db=sbtest --threads=10 --time=120 --report-interval=10 --db-driver=mysql cleanup 常用测试命令： sysbench oltp_insert --mysql-host=192.168.1.6 --mysql-port=3306 --mysql-user=root --mysql-password='1qaz!QAZ' --mysql-db=sbtest --db-driver=mysql --threads=20 --time=120 --events=0 prepare sysbench oltp_insert --mysql-host=192.168.1.6 --mysql-port=3306 --mysql-user=root --mysql-password='1qaz!QAZ' --mysql-db=sbtest --db-driver=mysql --threads=10 --time=120 --report-interval=10 prepare MySQL数据库容量设计： 对于一个存储设计，必须考虑业务特点，收集的信息如下： 1.数据的容量：1-3年内会大概多少条数据，每条数据大概多少字节； 2.数据项：是否有大字段，那些字段的值是否经常被更新； 3.数据查询SQL条件：哪些数据项的列名称经常出现在WHERE、GROUP BY、ORDER BY子句中等； 4.数据更新类SQL条件：有多少列经常出现UPDATE或DELETE 的WHERE子句中； 5.SQL量的统计比，如：SELECT：UPDATE+DELETE：INSERT=多少？ 6.预计大表及相关联的SQL，每天总的执行量在何数量级？ 7.表中的数据：更新为主的业务 还是 查询为主的业务 8.打算采用什么数据库物理服务器，以及数据库服务器架构？ 9.并发如何？ 10.存储引擎选择InnoDB还是MyISAM？ 大致明白以上10个问题，至于如何设计此类的大表，应该什么都清楚了！ 至于优化若是指创建好的表，不能变动表结构的话，那建议InnoDB引擎，多利用点内存，减轻磁盘IO负载，因为IO往往是数据库服务器的瓶颈 另外对优化索引结构去解决性能问题的话，建议优先考虑修改类SQL语句，使他们更快些，不得已只靠索引组织结构的方式，当然此话前提是， 索引已经创建的非常好，若是读为主，可以考虑打开query_cache， 以及调整一些参数值：sort_buffer_size,read_buffer_size,read_rnd_buffer_size,join_buffer_size。 MySQL数据库设计军规 军规适用场景：并发量大、数据量大的互联网业务 一、基础规范 （1）必须使用InnoDB存储引擎 解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高 （2）必须使用UTF8字符集 解读：万国码，无需转码，无乱码风险，节省空间 （3）数据表、数据字段必须加入中文注释 解读：N年后谁tm知道这个r1,r2,r3字段是干嘛的 （4）禁止使用存储过程、视图、触发器、Event（不在数据库做运算，cpu计算务必移至业务层） 解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧 （5）禁止存储大文件或者大照片 解读：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URI多好 二、命名规范 （6）只允许使用内网域名，而不是ip连接数据库 （7）线上环境、开发环境、测试环境数据库内网域名遵循命名规范 业务名称：xxx 线上环境：dj.xxx.db 开发环境：dj.xxx.rdb 测试环境：dj.xxx.tdb 从库在名称后加-s标识，备库在名称后加-ss标识 线上从库：dj.xxx-s.db 线上备库：dj.xxx-sss.db （8）库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用 （9）表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx 三、表设计规范 （10）单实例表数目必须小于500；控制单表数据量，int型不超过1000w，含char则不超过500w；合理分表； （11）单表列数目必须小于30个 （12）表必须有主键，例如自增主键 解读： a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用 b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率 c） 无主键的表删除，在row模式的主从架构，会导致备库夯住 （13）禁止使用外键，如果有外键完整性约束，需要应用程序控制 解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先 "拒绝3B 拒绝大sql语句：big sql 拒绝大事物：big transaction 拒绝大批量：big batch" 四、字段设计规范 （14）必须把字段定义为NOT NULL并且提供默认值 解读： a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化 b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多 c）null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识 d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录 （15）禁止使用TEXT、BLOB类型 解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能 （16）禁止使用小数存储货币 解读：使用整数吧，小数容易导致钱对不上 （17）必须使用varchar(20)存储手机号 解读： a）涉及到区号或者国家代号，可能出现+-() b）手机号会去做数学运算么？ c）varchar可以支持模糊查询，例如：like“138%” （18）禁止使用ENUM，可使用TINYINT代替 解读： a）增加新的ENUM值要做DDL操作 b）ENUM的内部实际存储就是整数，你以为自己定义的是字符串？ "关于字段设计规范补充 用好数值类型 tinyint(1Byte) smallint(2Byte) mediumint(3Byte) int(4Byte) bigint(8Byte) bad case：int(1)/int(11)

字符转化为数字 用int而不是char(15)存储ip

优先使用enum或set 例如：sex enum (‘F’, ‘M’)

避免使用NULL字段 NULL字段很难查询优化； NULL字段的索引需要额外空间； NULL字段的复合索引无效； bad case： name char(32) default null age int not null good case： age int not null default 0

少用text/blob varchar的性能会比text高很多；实在避免不了blob，请拆表；

不在数据库里存图片 这个我不能理解！但这是赶集网的经验，求detail！" 五、索引设计规范 （19）单表索引建议控制在5个以内 （20）单索引字段数不允许超过5个 解读：字段超过5个时，实际已经起不到有效过滤数据的作用了 （21）禁止在更新十分频繁、区分度不高的属性上建立索引 解读： a）更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能 b）“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似 （22）建立组合索引，必须把区分度高的字段放在前面 解读：能够更加有效的过滤数据 "关于索引补充： 谨慎合理使用索引 改善查询、减慢更新；索引一定不是越多越好能不加就不加，要加的一定得加、；覆盖记录条数过多不适合建索引，例如“性别”； 字符字段必须建前缀索引 不在索引做列运算 ！！！不只是索引，都不能做列运算吧！！！ bad case： select id where age +1 = 10; innodb主键推荐使用自增列； 主键建立聚簇索引；主键不应该被修改；字符串不应该做主键；如果不指定主键，innodb会使用唯一且非空值索引代替； 不用外键 请由程序保证约束；" 六、SQL使用规范 （23）禁止使用SELECT *，只获取必要的字段，需要显示说明列属性 解读： a）读取不需要的列会增加CPU、IO、NET消耗 b）不能有效的利用覆盖索引 c）使用SELECT 容易在增加或者删除字段后出现程序BUG （24）禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性 解读：容易在增加或者删除字段后出现程序BUG （25）禁止使用属性隐式转换 解读：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone索引，猜猜为什么？（这个线上问题不止出现过一次） （26）禁止在WHERE条件的属性上使用函数或者表达式 解读：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描 正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00') （27）禁止负向查询，以及%开头的模糊查询 解读： a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描 b）%开头的模糊查询，会导致全表扫描 （28）禁止大表使用JOIN查询，禁止大表使用子查询 解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能 （29）禁止使用OR条件，必须改为IN查询 解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢？ （30）应用程序必须捕获SQL异常，并有相应处理 总结：大数据量高并发的互联网业务，极大影响数据库性能的都不让用，不让用哟。 "关于SQL使用规范补充： 17、sql语句尽可能简单 一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库； 18、简单的事务 事务时间尽可能短； bad case： 上传图片事务 19、避免使用trig/func 触发器、函数不用；客户端程序取而代之； 20、不用select * 消耗cpu，io，内存，带宽；这种程序不具有扩展性； 21、OR改写为IN() or的效率是n级别； in的消息时log(n)级别； in的个数建议控制在200以内； select id from t where phone=’159′ or phone=’136′; => select id from t where phone in (’159′, ’136′); 22、OR改写为UNION mysql的索引合并很弱智 select id from t where phone = ’159′ or name = ‘john’; => select id from t where phone=’159′ union select id from t where name=’jonh’ 23、避免负向% 24、慎用count() 25、limit高效分页,limit偏移量越大（如下的例子是从第10001起的10条记录，其中10000就是偏移量），效率越低 select id from t limit 10000, 10; => select id from t where id > 10000 limit 10; 26、使用union all替代union union有去重开销 27、少用连接join 28、使用group by 分组；自动排序； 29、请使用同类型比较 30、使用load data导数据 load data比insert快约20倍； 31、打散批量更新 32、新能分析工具 show profile; mysqlsla; mysqldumpslow; explain; show slow log; show processlist; show query_response_time(percona) "

一、一些常见的SQL实践 （1）负向条件查询不能使用索引 ·         select * from order where status!=0 and stauts!=1 not in/not exists都不是好习惯

可以优化为in查询： ·         select * from order where status in(2,3)

（2）前导模糊查询不能使用索引 ·         select * from order where desc like '%XX' 而非前导模糊查询则可以： ·         select * from order where desc like 'XX%'

（3）数据区分度不大的字段不宜使用索引 ·         select * from user where sex=1 原因：性别只有男，女，每次过滤掉的数据很少，不宜使用索引。

经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引。

（4）在属性上进行计算不能命中索引 ·         select * from order where YEAR(date) < = '2017' 即使date上建立了索引，也会全表扫描，可优化为值计算： ·         select * from order where date < = CURDATE() 或者： ·         select * from order where date < = '2017-01-01'

二、并非周知的SQL实践 （5）如果业务大部分是单条查询，使用Hash索引性能更好，例如用户中心 ·         select * from user where uid=? ·         select * from user where login_name=? 原因： B-Tree索引的时间复杂度是O(log(n)) Hash索引的时间复杂度是O(1)

（6）允许为null的列，查询有潜在大坑 单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集 ·         select * from user where name != 'shenjian' 如果name允许为null，索引不存储null值，结果集中不会包含这些记录。

所以，请使用not null约束以及默认值。

（7）复合索引最左前缀，并不是值SQL语句的where顺序要和复合索引一致 用户中心建立了(login_name, passwd)的复合索引 ·         select * from user where login_name=? and passwd=? ·         select * from user where passwd=? and login_name=? 都能够命中索引

·         select * from user where login_name=? 也能命中索引，满足复合索引最左前缀

·         select * from user where passwd=? 不能命中索引，不满足复合索引最左前缀

（8）使用ENUM而不是字符串 ENUM保存的是TINYINT，别在枚举中搞一些“中国”“北京”“技术部”这样的字符串，字符串空间又大，效率又低。

三、小众但有用的SQL实践 （9）如果明确知道只有一条结果返回，limit 1能够提高效率 ·         select * from user where login_name=? 可以优化为： ·         select * from user where login_name=? limit 1 原因： 你知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动

（10）把计算放到业务层而不是数据库层，除了节省数据的CPU，还有意想不到的查询缓存优化效果 ·         select * from order where date < = CURDATE() 这不是一个好的SQL实践，应该优化为： $curDate = date('Y-m-d'); $res = mysql_query(     'select * from order where date < = $curDate'); 原因： 释放了数据库的CPU 多次调用，传入的SQL相同，才可以利用查询缓存

（11）强制类型转换会全表扫描 ·         select * from user where phone=13800001234 你以为会命中phone索引么？大错特错了，这个语句究竟要怎么改？

末了，再加一条，不要使用select *（潜台词，文章的SQL都不合格 =_=），只返回需要的列，能够大大的节省数据传输量，与数据库的内存使用量哟。

MySQL my.cnf主要参数配置解析

****skip-name-resolve    禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，    则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求      skip-networking      back_log = 600    MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，    然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。    如果期望在一个短时间内有很多连接，你需要增加它。也就是说，如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，    以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。    另外，这值（back_log）限于您的操作系统对到来的TCP/IP连接的侦听队列的大小。    你的操作系统在这个队列大小上有它自己的限制（可以检查你的OS文档找出这个变量的最大值），试图设定back_log高于你的操作系统的限制将是无效的。      max_connections = 1000    MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。      max_connect_errors = 6000    对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。      open_files_limit = 65535    MySQL打开的文件描述符限制，默认最小1024;当open_files_limit没有被配置的时候，比较max_connections5和ulimit -n的值，哪个大用哪个，    当open_file_limit被配置的时候，比较open_files_limit和max_connections5的值，哪个大用哪个。      table_open_cache = 128    MySQL每打开一个表，都会读入一些数据到table_open_cache缓存中，当MySQL在这个缓存中找不到相应信息时，才会去磁盘上读取。默认值64    假定系统有200个并发连接，则需将此参数设置为200N(N为每个连接所需的文件描述符数目)；    当把table_open_cache设置为很大时，如果系统处理不了那么多文件描述符，那么就会出现客户端失效，连接不上      max_allowed_packet = 4M    接受的数据包大小；增加该变量的值十分安全，这是因为仅当需要时才会分配额外内存。例如，仅当你发出长查询或MySQLd必须返回大的结果行时MySQLd才会分配更多内存。    该变量之所以取较小默认值是一种预防措施，以捕获客户端和服务器之间的错误信息包，并确保不会因偶然使用大的信息包而导致内存溢出。      binlog_cache_size = 1M    一个事务，在没有提交的时候，产生的日志，记录到Cache中；等到事务提交需要提交的时候，则把日志持久化到磁盘。默认binlog_cache_size大小32K      max_heap_table_size = 8M    定义了用户可以创建的内存表(memory table)的大小。这个值用来计算内存表的最大行数值。这个变量支持动态改变      tmp_table_size = 16M    MySQL的heap（堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。    大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。    如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果      read_buffer_size = 2M    MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。    如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能      read_rnd_buffer_size = 8M    MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，    MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大      sort_buffer_size = 8M    MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。    如果不能，可以尝试增加sort_buffer_size变量的大小      join_buffer_size = 8M    联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享      thread_cache_size = 8    这个值（默认8）表示可以重新利用保存在缓存中线程的数量，当断开连接时如果缓存中还有空间，那么客户端的线程将被放到缓存中，    如果线程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线程将被重新创建,如果有很多新的线程，    增加这个值可以改善系统性能.通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。(–>表示要调整的值)    根据物理内存设置规则如下：    1G  —> 8    2G  —> 16    3G  —> 32    大于3G  —> 64      query_cache_size = 8M   MySQL的查询缓冲大小（从4.0.1开始，MySQL提供了查询缓冲机制）使用查询缓冲，MySQL将SELECT语句和查询结果存放在缓冲区中，    今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。    通过检查状态值'Qcache_%'，可以知道query_cache_size设置是否合理：如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，    如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，    这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲      query_cache_limit = 2M   指定单个查询能够使用的缓冲区大小，默认1M      key_buffer_size = 4M   指定用于索引的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，    系统将开始换页并且真的变慢了。对于内存在4GB左右的服务器该参数可设置为384M或512M。通过检查状态值Key_read_requests和Key_reads，    可以知道key_buffer_size设置是否合理。比例key_reads/key_read_requests应该尽可能的低，    至少是1:100，1:1000更好(上述状态值可以使用SHOW STATUS LIKE 'key_read%'获得)。注意：该参数值设置的过大反而会是服务器整体效率降低      ft_min_word_len = 4    分词词汇最小长度，默认4      transaction_isolation = REPEATABLE-READ    MySQL支持4种事务隔离级别，他们分别是：    READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.    如没有指定，MySQL默认采用的是REPEATABLE-READ，ORACLE默认的是READ-COMMITTED      log_bin = mysql-bin   binlog_format = mixed   expire_logs_days = 30 #超过30天的binlog删除      log_error = /data/mysql/mysql-error.log #错误日志路径   slow_query_log = 1   long_query_time = 1 #慢查询时间 超过1秒则为慢查询   slow_query_log_file = /data/mysql/mysql-slow.log      performance_schema = 0   explicit_defaults_for_timestamp      lower_case_table_names = 1 #不区分大小写      skip-external-locking #MySQL选项以避免外部锁定。该选项默认开启      default-storage-engine = InnoDB #默认存储引擎      innodb_file_per_table = 1    InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间    独立表空间优点：    1．每个表都有自已独立的表空间。    2．每个表的数据和索引都会存在自已的表空间中。    3．可以实现单表在不同的数据库中移动。    4．空间可以回收（除drop table操作处，表空不能自已回收）    缺点：    单表增加过大，如超过100G    结论：    共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时，请合理调整：innodb_open_files      innodb_open_files = 500    限制Innodb能打开的表的数据，如果库里的表特别多的情况，请增加这个。这个值默认是300      innodb_buffer_pool_size = 64M    InnoDB使用一个缓冲池来保存索引和原始数据, 不像MyISAM.    这里你设置越大,你在存取表里面数据时所需要的磁盘I/O越少.    在一个独立使用的数据库服务器上,你可以设置这个变量到服务器物理内存大小的80%    不要设置过大,否则,由于物理内存的竞争可能导致操作系统的换页颠簸.    注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制,    所以不要设置的太高.      innodb_write_io_threads = 4   innodb_read_io_threads = 4    innodb使用后台线程处理数据页上的读写 I/O(输入输出)请求,根据你的 CPU 核数来更改,默认是4    注:这两个参数不支持动态改变,需要把该参数加入到my.cnf里，修改完后重启MySQL服务,允许值的范围从 1-64      innodb_thread_concurrency = 0    默认设置为 0,表示不限制并发数，这里推荐设置为0，更好去发挥CPU多核处理能力，提高并发量      innodb_purge_threads = 1    InnoDB中的清除操作是一类定期回收无用数据的操作。在之前的几个版本中，清除操作是主线程的一部分，这意味着运行时它可能会堵塞其它的数据库操作。    从MySQL5.5.X版本开始，该操作运行于独立的线程中,并支持更多的并发数。用户可通过设置innodb_purge_threads配置参数来选择清除操作是否使用单    独线程,默认情况下参数设置为0(不使用单独线程),设置为 1 时表示使用单独的清除线程。建议为1      innodb_flush_log_at_trx_commit = 2    0：如果innodb_flush_log_at_trx_commit的值为0,log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作（执行是由mysql的master thread线程来执行的。    主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交）默认的日志文件是ib_logfile0,ib_logfile1    1：当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。    2：如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。    每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有 缓存的，所以这个写入并不能保证数据已经写入到物理磁盘    默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。    设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。    总结    设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2，如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能      innodb_log_buffer_size = 2M    此参数确定些日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，但意外的故障将会丢失数据。MySQL开发人员建议设置为1－8M之间      innodb_log_file_size = 32M    此参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间      innodb_log_files_in_group = 3    为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3      innodb_max_dirty_pages_pct = 90    innodb主线程刷新缓存池中的数据，使脏数据比例小于90%      innodb_lock_wait_timeout = 120     InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。InnoDB在它自己的锁定表中自动检测事务死锁并且回滚事务。InnoDB用LOCK TABLES语句注意到锁定设置。默认值是50秒      bulk_insert_buffer_size = 8M    批量插入缓存大小， 这个参数是针对MyISAM存储引擎来说的。适用于在一次性插入100-1000+条记录时， 提高效率。默认值是8M。可以针对数据量的大小，翻倍增加。      myisam_sort_buffer_size = 8M    MyISAM设置恢复表之时使用的缓冲区的尺寸，当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区      myisam_max_sort_file_size = 10G    如果临时文件会变得超过索引，不要使用快速排序索引方法来创建一个索引。注释：这个参数以字节的形式给出      myisam_repair_threads = 1    如果该值大于1，在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内)        interactive_timeout = 28800    服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。默认值：28800秒（8小时）      wait_timeout = 28800    服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，    取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。参数默认值：28800秒（8小时）    MySQL服务器所支持的最大连接数是有上限的，因为每个连接的建立都会消耗内存，因此我们希望客户端在连接到MySQL Server处理完相应的操作后，    应该断开连接并释放占用的内存。如果你的MySQL Server有大量的闲置连接，他们不仅会白白消耗内存，而且如果连接一直在累加而不断开，    最终肯定会达到MySQL Server的连接上限数，这会报'too many connections'的错误。对于wait_timeout的值设定，应该根据系统的运行情况来判断。    在系统运行一段时间后，可以通过show processlist命令查看当前系统的连接状态，如果发现有大量的sleep状态的连接进程，则说明该参数设置的过大，    可以进行适当的调整小些。要同时设置interactive_timeout和wait_timeout才会生效。      [mysqldump]   quick   max_allowed_packet = 16M #服务器发送和接受的最大包长度      [myisamchk]   key_buffer_size = 8M   sort_buffer_size = 8M   read_buffer = 4M   write_buffer = 4M  **