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)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值