mysql优化

1、只使Innodb 存储引擎 Engine=innodb Auto_increment = 1 Default charset=utf8mb4
2、所有的InnoDB表都设计一个与业物无关的自增无符号int 物理主键,使数据按顺序保存 如：id int(10) unsigned primary key not null auto_increment
预估数据会无限增加的表主键 id bigint(19) unsigned primary key not null auto_increment
3、尽可能不使用TEXT/BLOB类型 可varchar .确实需要的话，建议拆分到子表中，不要和主表放在一起，避免SELECT * 的时候读性能太差，把innodb buffer 充满挤出热点数据。
4 、字段长度满足需求前提下，尽可能选择长度小的；字段属性尽量都加上NOT NULL约束并设置默认值。因为null 字段很难查询优化，null 字段的复合索引无效，null 字段有索引需要额外空间
5、读取数据时，只选取所需要的列，不要每次都SELECT *，避免产生严重的随机读问题，尤其是读到一些TEXT/BLOB列；
6、通常情况下，子查询的性能比较差，建议改造成JOIN写法；
7、多表联接查询时，关联字段类型尽量一致，并且都要有索引；如 T1 id(varchar) = T2 id(int) 会进行隐式转换索引失效，应改写成T1 id(int) = T2 id(int)
8、mysql会一直向右匹配遇到范围查询(>,<, between , kill )就停止匹配，比如a=1 and b=2 and c>3 and d=4 如果建交(a,b,c,d)顺序索引，d 是用不到索引的
如果建交(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
9、精确确定where条件对应的行，尽量使用复合索引，避免重复索引；如 创建indx_abc 多列索引，相当于创建(a)单列索引，(a,b)组合索引及(a,b,c)组合索引。
不要在索引列使用函数 max(id)>10,id+1>3等。
10、尽量选择区分度高的列作为前缀索引；区分度的公式：count(distinct col)/count(*) 字段不重复的比例越大扫描的记录数越少。
like “%aaa%” 不会使用索引,而like “aaa%”可以使用索引，in (xx,xx,xx)的数量不超300;不使用反向查询 如 not in 可以NOT EXISTS代替 ,not like 不会使用索引将进行全表扫描.
避免使用大事务，尽量使用短小事务

11、通常单表物理大小不超过10GB，单表行数不超过1亿条，行平均长度不超过8KB

12、定期使用pt-duplicate-key-checker检查并删除重复的索引。定期使用pt-index-usage工具检查并删除使用频率很低的索引；

13、定期采集slow query log，用pt-query-digest工具进行分析，可结合Anemometer系统进行slow query管理以便分析slow query并进行后续优化工作；

14、可使用pt-kill杀掉超长时间的SQL请求，Percona版本中有个选项 innodb_kill_idle_transaction 也可实现该功能；

15、使用pt-online-schema-change来完成大表的ONLINE DDL需求；

16、定期使用pt-table-checksum、pt-table-sync来检查并修复mysql主从复制的数据差异；

mysql优化

标签：版本   多列   percona   def   检查   无限   请求   热点   需求