mysql优化

前言：总是在灾难发生以后，才想起容灾的重要性》

其实数据库优化有很多种方式，不过只有在具体的场景中才会发挥它的最大价值，这是我对前人的优化做的一些总结，希望对你有帮助，以下优化针对mysql：

A 设计原则

1.不在数据库做运算；CPU计算务必移至业务层；

2.控制单表数据量：单表记录控制在1000W；

3.控制列数量：字段数控制在20以内；

4.平衡范式与冗余：为提高效率牺牲范式设计，冗余数据；

5.拒绝3B：拒绝大SQL，大事务，大批量；

6.表字符集使用utf8；

7.使用INNODB存储引擎；

B 数据表设计

1.尽可能地使用最有效（最小）的数据类型；

 

2.不要将数字存储为字符串，字符转化为数字，用int存储IP而非char（15）；

3.优先使用enum或set，sex enum（‘F’，‘M’）；

4.避免使用NULL字段

 

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

6.不在数据库里存图片

7.对于MyISAM表，如果没有任何变长列（varchar、text或blob列），使用固定尺寸的记录格式。这比较快但是不幸的可能会浪费一些空间。即使你已经用create选项让varchar列ROW_FORMAT=fixes，也可以提示想使用固定长度的行。

8.使用sample character set，例如Latin1.尽量少使用utf-8，因为utf-8占用的空间是Latin1 的3倍。可以在不需要使用utf-8的字段上面使用Latin1 ，例如mail，URL等。

9.精确度与空间的转换。在存储相同数值范围的数据时，浮点数类型通常都会比DECIMAL类型使用更少的空间。float字段使用4字节存储数据。double类型需要8个字节并拥有更高的精确度和更大的数值范围，decimal 类型的数据将会转换成double类型。

10.库名表名字段名必须有固定的命名长度，12个字符以内；库名、表名、字段名禁止超过32个字符。须见名知意；库名、表名、字段名禁止使用mysql保留字；临时库、表名必须以tmp为前缀，并以日期为后缀；备份库、表必须以bak为前缀，并以日期为后缀；

11.INNODB 表行记录物理长度不超过8KB，INNODB的data page默认是16KB，基于B+Tree的特点，一个data page中需要至少存储2条记录。因此，当实际存储长度超过8 KB（尤其是text/blob列）的大列（large column）时会引起“page-overflow存储”，类似oracle中的“行迁移”，因此，如果必须使用大列（尤其是text/blob类型）且读写频繁的话，则最好把这些列拆分到字表中，不要和主表放在一起储存，如果太频繁，可以考虑继续保留在主表中，如果将INNODBpagesize选项修改为8KB，那么行记录物理长度建议不超过4KB。

C 索引类

1.谨慎合理使用索引

2.字符字段必须建前缀索引

3.不在索引做列运算，bad case： select ID where age + 1=10；

4.INNODB 主键推荐使用自增列

5.不用外键，请由程序保证约束

6.避免在已有索引的前缀上建立索引。例如：如果存在index（a,b）则去掉index（a）；

7.控制单个索引的长度，使用key（name（8））在数据的前面几个字符建立索引

8.要选择性的使用索引。在变化很少的列上使用索引并不是很好，例如性别列；

9.Optimize table 可以压缩和排序index，注意不要频繁运行

10.Analyze table 可以更新数据

11、索引选择性是不重复的索引值也叫基数（cardinality）表中数据行数的比值，索引选择性=基数/数据行，count(distinct(username))/count(*) 就是索引选择性，高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行，唯一索引的选择性最佳，值为1

12、不要用重复或多余索引，对于INNODB引擎的索引来说，每次修改数据都要把主键索引，辅助索引中相应索引值修改，这可能会出现大量数 据迁移，分页，以及碎片的出现

13、超过20个长度的字符串列，最好创建前缀索引而非整列索引（例如：ALTER TABLE t1 ADD INDEX(user(20))），可以有效提高索引利用率，不过它的缺点是对这个列排序时用不到前缀索引。前缀索引的长度可以基于对该字段的统计得出， 一般略大于平均长度一点就可以了

14、定期用 pt-duplicate-key-checker 工具检查并删除重复的索引。比如 index idx1(a, b) 索引已经涵盖了 index idx2(a)，就可以删除 idx2 索引了

D SQL语句设计类

1.SQL语句尽可能简单，一条SQL只能在一个CPU运算，大语句拆小语句，减少锁时间，一条大SQL可以堵死整个库（充分利用querycache和充分利用多喝CPU）；

2.简单的事务，事务时间尽可能短，bad case：上传图片事务；

3.避免使用trig/func，触发器、函数不用，客户端程序取而代之；

4.不用select*，消耗CPU，io，内存，带宽，这种程序不具有拓展性；

5.or改写为in（）

6.or改写为union

 

7.避免负向%，如not in/like

8.慎用count（*）

9.limit高效分页

10.使用union all 代替union，union有去重开销

11.少用连接join

12.使用group by分组自动排序

13.请使用同类型比较

14.使用load data导数据，load data比insert快约20倍

15.对数据的更新要打散后批量更新，不要一次更新太多数据

16.使用性能分析工具

 

17.使用--log--slow--queries--long--query--time=2查看查询比较慢的语句，然后使用explain分析查询，做出优化。

 

optimize数据在插入，更新，删除的时候难免一些数据迁移，分页，之后就会出现一些碎片，久而久之碎片堆积起来影响性能，这就需要DBA定期的优化数据库减少碎片，这就通过optimize命令。如对MyISAM表操作：optimize table 表名；

18.禁止在数据库中跑大查询

19.使用预编译语句，只传参数，比传递SQL语句更高效；一次解析，多次使用；降低SQL注入概率；

20。禁止使用order by rand()

21.禁止单条SQL语句同时更新多个表

22.避免在数据库中进行数学运算(MySQL不擅长数学运算和逻辑判断)

23、SQL语句要求所有研发，SQL关键字全部是大写，每个词只允许有一个空格

24、能不用NOT IN就不用NOT IN，坑太多了。。会把空和NULL给查出来；

思考题：性能状态关键指标该怎么计算？

QPS，Queries Per Second：每秒查询数，一台数据库每秒能够处理的查询次数
TPS，Transactions Per Second：每秒处理事务数

注意
1、哪怕是基于索引的条件过滤，如果优化器意识到总共需要扫描的数据量超过30%时（ORACLE里貌似是20%，MySQL目前是30%，没准以后会调整），就会直接改变执行计划为全表扫描，不再使用索引

2、多表JOIN时，要把过滤性最大（不一定是数据量最小哦，而是只加了WHERE条件后过滤性最大的那个）的表选为驱动表。此外，如果JOIN之后有排序，排序字段一定要属于驱动表，才能利用驱动表上的索引完成排序

3、绝大多数情况下，排序的代价通常要来的更高，因此如果看到执行计划中有 Using filesort，优先创建排序索引吧

4、利用 pt-query-digest 定期分析slow query log，并结合 Box Anemometer 构建slow query log分析及优化系统