MySQL注意事项

一、核心原则 

1.不在数据库做复杂运算：包括但不限于复杂的排序，聚合，连接操作，请移至业务层
2.控制单表数据量：单表记录控制在1000w
3.控制单表字段数量：字段数控制在20以内
4.平衡范式与冗余：为提高效率牺牲范式设计，冗余数据
5.拒绝3B：拒绝大sql，大事物，大批量；拒绝容忍小表上高频 filesort 的 SQL；

 二、字段类

6.数值类型

 评估所需要的值域，能用小的不用大的，如果要存储的数值不会为负，可以使用unsigned修饰(对于整型可以扩大其值域上限)
   tinyint(1Byte)
   smallint(2Byte)
   mediumint(3Byte)
   int(4Byte)
   bigint(8Byte)

对于int(4)，括号中的数字代表显示宽度，如果数值不足4位，查询结果显示的时候会在前面补空格，这个值与存储空间毫无关系！

7.字符转化为数字，既节省空间，又使查询高效

    用int而不是char(15)存储ip，用inet_aton()和inet_ntoa()来在点分表示法和int表示法之间转换

 8.如果字段值域有限，且不会扩展，则优先使用enum或set, 

    例如：`sex` enum (‘F’, ‘M’)

9.避免使用NULL字段，因为

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

10.少用text/blob

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

11.不在数据库里存图片

 三、索引类

12.谨慎合理使用索引

      索引可以改善查询，减慢增删改
      索引一定不是越多越好（能不加就不加，要加的一定得加）
      覆盖记录条数过多，区分度不高的列不适合建索引，例如“性别”
 

13.如果必须要给字符字段建索引，则最好使用前缀索引

      前缀索引的长度，根据该字段前缀的相似程度来确定

14.不在索引列做运算，不对索引列做函数调用，这样都用不到索引

 bad case：
select id where age +1 = 10;

 select id where from_unixtime(order_time,'%Y%m%d')='2015-03-01';

15.innodb表的主键推荐使用自增列

先了解下InnoDB引擎表的一些关键特征：

• InnoDB引擎表是基于B+树的索引组织表(IOT)；
• 每个表都需要有一个聚簇索引(clustered index)；
• 所有的行记录都存储在B+树的叶子节点(leaf pages of the tree)；
• 基于聚簇索引的增、删、改、查的效率相对是最高的；
• 如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择其作为聚簇索引；
• 如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引；
• 如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚簇索引(ROWID随着行记录的写入而逐渐递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

综上总结，如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的，也就是下面这几种情况的存取效率最高：

• 使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；
• 该表不指定自增列做主键，同时也没有可以被选为主键的唯一索引(上面的条件)，这时候InnoDB会选择内置的ROWID作为主键，写入顺序和ROWID增长顺序一致；

除此以外，如果一个InnoDB表没有显式主键，而是有可以被选择为主键的唯一索引，但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况)，该表的存取效率就会比较差。

主键建立聚簇索引
主键不应该被修改
字符串不应该做主键

16.不用外键

      请由程序保证约束

四、sql类

17.sql语句尽可能简单

     一条sql只能在一个cpu运算
     大语句拆小语句，减少锁时间
     一条大sql可以堵死整个库

 18.简单的事务

       事务时间尽可能短，能不用事务就不要用，能用小事务就不用大事务
bad case：
      上传图片事务

 19.避免使用trig/func

       触发器、函数不用
      客户端程序取而代之

 20.不用select *，需要多少字段就查询多少字段

       多余的字段会消耗数据库的cpu，io，内存，带宽，流量

 21.OR改写为IN()

      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的索引合并很弱智，很多时候使用index_merge比单独使用一个index的性能要差不少
      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.避免负向查询和%前缀模糊查询

      避免负向查询，如NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等。避免%前缀模糊查询，使用不了索引，导致全表扫描

 24.慎用count(*)

 25.limit高效分页

      limit越大，效率越低
      select id from t limit 10000, 10;
      =>
      select id from t where id > 10000 limit 10;

 26.使用union all替代union

       union有去重开销

 27.少用连接join

 28.请使用同类型比较

 29.使用load data批量导入数据

       load data比insert快约20倍；

 30.打散批量更新，分散数据库的写压力

 31.性能分析工具

查看sql执行计划explain;
show profile;
mysqlsla;
mysqldumpslow;
show slow log;
show processlist;
show query_response_time(percona)