使用mysql有段时间了,前前后后踩过不少的坑,这里只分享我实际使用的一些经验。虽然不全,但是对于刚接触或者已经在项目实践中的你多多少少有帮助。
0、建议给每张表添加注释,方便日后维护;
1、int(N)中的N代表最大显示宽度,与存储的范围和位数无关。比如2147483646这个数字,int(11)能存储,int(10)也可以存储。如果一个数过大,超过int能存储的最大值,可以考虑用bigint;
2、使用AS关键字声明一个别名时,不要和表名或列名相同;
select t as table from table
3、带索引的查询,尽量不要用or,like等语句查询,否则会造成索引不生效;
4、参数比较包含隐式转换的,无法使用索引。比如
SELECT * FROM user WHERE age >= '60';
5、要避免在where字句中对字段进行NULL值判断,可能导致索引失效而进行全表扫描;
select id from user where name is null
6、不要使用负向查询,比如
select name from user where id not in (42,3,123)
7、不要对对个表进行group by或者order by,可能产生巨大性能隐患,并且可能消耗大量内存和磁盘上的临时空间;
select tb1.name,tab2.name from tab1,tab2 where age=20 order by tb1.name,tb2.name
8、能用where查询条件替换的,尽量不要使用HAVING字句。因为where字句查询处理期间能使用索引;
SELECT s.id,count(s.id) FROM s where c = test GROUP BY s.c_id HAVING s.id <> '5' AND s.id <> '2' order by s.id
9、尽量不使用*通配符查询,当表结构变更时,将返回更多数据;
select * from user where age=10
10、在创建列或者修改列时,为列添加默认值;
11、一个表在构建时,不要包含太多的列,不然数据量太多,要考虑垂直分表;
12、可使用 VARCHAR 代替 CHAR, VARBINARY 代替 BINARY;
13、varchar定义长度不要超过 255,如果存储长度过长 MySQL 默认类型为 text,并独立出来一张表,这将会影响其它字段索引效率;
14、不建议使用 COUNT (col) 或 COUNT (常量) 来替代 COUNT (*), COUNT (*) 是 SQL92 定义的标准统计行数的方法,跟数据无关,跟 NULL 和非 NULL 也无关。
15、凡是涉及删除或者更新的操作,要确认无误后再执行。避免操作失败导致数据丢失;
16、sql语句中不等于运算符用‘<>’表示,‘!=’是非标准的;
17、子查询不支持limit;
SELECT * FROM user WHERE name IN (SELECT NAME FROM customer ORDER BY name LIMIT 1)
18、表索引不宜过多,根据实际情况添加。太多的索引会导致表写入慢;
19、建议使用自增列作为主键;
20、不建议使用存储过程,存储过程无版本控制,配合业务的存储过程升级很难做到业务无感知。存储过程在拓展和移植上也存在问题;
21、避免更新主键列,频繁更新主键列将影响其他语句正常查询;