1 写完 SQL 先 explain 查看执行计划
写完 SQL, 用 explain 分析一下, 尤其注意走不走索引
explain select userid,name,age from user where userid=10086 or age=18;
2 操作 delete 或者 update 语句, 加个 limit
delete from euser where age> 30 limit 200;
好处:
降低写错 SQL 的代价
SQL 效率很可能更高
避免了长事务
数据量大的话, 容易把 CPU 打满, 系统越来越卡和越删越慢
3 设计表的时候, 所有表和字段都添加相应的注释
设计数据库表的时候, 所有表和字段都加上对应注释, 后面更容易维护
- create table 'account' (
- 'id' int(11) not null auto_increment comment '主键',
- 'name' varchar(255) default null comment '账户名',
- 'balance' int(11) default null comment '余额',
- 'create_time' datetime not null comment '创建时间',
- 'update_time' datetime not null on update current_timestamp comment '更新时间',
- primary key ('id'),
- key 'idx_name' ('name') using btree
- ) engine=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
4 SQL 书写格式, 关键字大小保持一致, 使用缩进
- SELECT stu.name,sum(stu.score)
- FROM Student stu
- WHERE stu.classNo='1 班'
- GROUP BY stu.name;
5 INSERT 插入标明对应字段名称
insert into student (student_id,name,score) values ('666','sky','100');
6 变更 SQL 操作先在测试环境执行, 写明详细的操作步骤以及回滚方案, 并在上生产前 review
7 设计数据库表的时候, 加上 3 个字段: 主键, create_time,update_time
- create table 'account' (
- 'id' int(11) not null auto_increment comment '主键',
- 'name' varchar(255) default null comment '账户名',
- 'balance' int(11) default null comment '余额',
- 'create_time' datetime not null comment '创建时间',
- 'update_time' datetime not null on update current_timestamp comment '更新时间',
- primary key ('id'),
- key 'idx_name' ('name') using btree
- ) engine=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
主键一般要加上, 没有主键的表是没有灵魂的表
创建时间和更新时间, 还是建议加上, 详细审计, 跟踪记录, 都是有用的
8 写完 SQL, 检查 where,order by, groug by 后面的列, 多表关联的列是否已加索引, 优先考虑组合索引
添加索引:
- alter table user add index idx_address_age 9(address,age)
- explain select * from user where address='深圳' order by age;
9 修改或删除重要数据前, 要先备份, 先备份, 先备份
10 where 后面的 11 字段, 留意其数据类型的隐式转换
反例:
select * from user where userid = 123;
正例:
select * from user where userid = '123';
因为不加单引号, 是字符串跟数字的比较, 他们类型不匹配, MySQL 会做隐式类型转换, 把他们转换成浮点数再做比较, 最后导致索引失效
11 尽量把所有列定义为 not null
NOT NULL 列更节省空间, null 列需要一个额外字节作为判断是否为 null 的标志位
null 列需要注意空指针问题, NULL 列在计算和比较的时候, 需要注意空指针问题
12 减少不必要的字段返回, 如使用 select < 具体字段 > 代替 select *
13 所有表必须使用 Innodb 储存引擎
14 数据库和表的的字符集尽量统一使用 UTF8
可以避免乱码问题
可以避免, 不同字符集比较转换, 导致的索引失效问题
15 尽量使用 varchar 代替 char
'deptname' varchar(100) DEFAULT NULL COMMENT '部门名称'
因为首先变长字段存储空间小, 可以节省存储空间
16 SQL 命令行修改数据, 养成 begin + commit 事务的习惯
- begin;
- update account set balance = 100000 where name = 'sky';
- commit;
17 索引命名要规范, 主键索引名为 pk_字段名, 唯一索引名为 uk_字段名, 普通索引名为 idx_字段名
说明:
pk 即 primary key, uk 即 unique key, idx 即 index 的简称
18 如果修改更新数据过多, 考虑批量进行
- for each (200 次)
- {
- delete from account limit 500;
- }
来源: http://www.bubuko.com/infodetail-3720870.html