公司DBA关于MySQL开发的一点经验

尽量不要让数据库做过多运算

数据库主要是用来存储的，我们应避免让数据库做运算，比如写定时任务，存储过程等。复杂的计算应该在程序代码中实现。我们应该尽量简单的使用数据库。

控制数据量

一年内单表数据量一般含char不超过500W条，我们需要合理的分表。单个库的表建议在300到400之间。

单表字段数量

单表的字段应该少而精，那多少合适呢？一般单表字段上线控制在20到50个。

在开发中，我们要注意避免使用大SQL、大事务。

避免使用NULL字段

我发现很多童鞋在建表时喜欢字段默认NULL，使用NULL很难进行查询优化，我们如果对NULL列加索引，需要额外空间，而且含NULL的符合索引无效。

`a` char(32) DEFAULT NULL
`b` int(10) NOT NULL

比如上面这种。

少用TEXT

我们可以使用VARCHAR代替TEXT，因为TEXT类型处理性能比VARCHAR要低，TEXT会强制生成临时表，从而浪费更多空间。UTF-8下，VARCHAR(65535)大概占用64K空间。

如果一定要使用，就拆成单独的一张表吧。

CREATE TABLE t1 (
    id INT NOT NULL AUTO_INCREMENT,
    data text NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

关于索引

索引数量建议

索引虽然能改善查询，但如果索引列更新频繁，索引维护也会成为负担。我们如果不加索引就尽量不要加，最好不要超过字段数的20%。

关于函数运算

不要在索引列进行数学运算或者函数运算，因为这样会使索引失效。

-- 不要这样用
select * from table WHERE to_days(current_date) – to_days(date_col) <= 10

-- 推荐使用
select * from table WHERE date_col >= DATE_SUB('2011-10-22',INTERVAL 10 DAY);

自增列做主键

自增列做主键，有利于索引维护，并且主键不应该平凡修改。别用字符串做主键，比如不要使用UUID做主键。

推荐用独立亍业务的AUTO_INCREMENT列或全局ID生成器做代理主键。

我们知道，InnoDB中，主键索引默认是以主键列创建的，那么问题来了。

“
如果没有指定主键，那么还会创建主键索引么？
”

尽量少使用外键

如果使用了外键，高并发下面容易产生死锁。我们应该由程序来保证约束。

SQL语句要简单

我看过好几百行的SQL，我认为这样不太好，一般一条SQL只能在一个CPU中运算，一条大SQL可能把数据库搞崩。

我们应该将大SQL拆成多条简单的小SQL，简单的SQL会使缓存命中率更高。

关于事务

我们应该尽量将与事务无关的操作放到事务外面，这样能减少锁资源的占用。

简单的使用

我们尽量少使用存储过程，触发器，尽量少用MySQL函数处理结果。数据的运算应该交由程序去操作。

关于SQL效率

改写OR为IN(）

同一字段，将or改写为in()

OR效率：O(n)
IN 效率：O(Log n)
当n很大时，OR会慢很多

Select * from table WHERE phone='12347856' or phone='42242233';

-- 推荐使用in
Select * from table WHERE phone in ('12347856' , '42242233')

不同字段，将or改为union

Select * from table WHERE phone='010-88886666' or cellPhone='13800138000';

-- 推荐使用 union
Select * from table WHERE phone='010-88886666'
union
Select * from table WHERE cellPhone='13800138000';

用UNION ALL 而非 UNION

使用UNION会有去重开销。

关于JOIN

很多童鞋喜欢使用JOIN来连表查询，阿里巴巴开发手册里就建议不要超过三表的JOIN。

建议将表拆分。

Select * from tag JOIN tag_post on tag_post.tag_id=tag.id JOIN post on  tag_post.post_id=post.id WHERE tag.tag='二手玩具';

推荐这样做：

Select * from tag WHERE tag='二手玩具';
Select * from tag_post WHERE tag_id=1321;
Select * from post WHERE post.id in (123,456,314,141)

关于计数统计

实时统计：用memcache，双向更新，凌晨跑基准
非实时统计：尽量用单独统计表，定期重算

关于limit分页

我们平常可能写过这样的SQL：

Select * from table limit 10000,10;

但是这样会很慢，偏移量越大越慢。

我们推荐使用这样的方式

select * from table WHERE id>=23434 limit 11;

网上有人做了测试：

不要在程序端对数据库显示加锁

“
外部锁对数据库不可控
高并发时是灾难
极难调试和排查