MySQL数据库开发的36条原则(小结)(2)-木庄网络博客

比如我们原来建表语句是这样的：

CREATE TABLE `user` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
 `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',
 PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `order` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
 `total_price` decimal(10,2) NOT NULL DEFAULT '0.00', 
 `user_id` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`), 
 KEY `for_indx_user_id` (`user_id`), 
 CONSTRAINT `for_indx_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

不使用外键约束后：

CREATE TABLE `user` (
 `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', 
 `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',
 PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE `order` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', 
 `total_price` decimal(10,2) NOT NULL DEFAULT '0.00', 
 `user_id` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

不适用外键约束后，为了加快查询我们通常会给不建立外键约束的字段添加一个索引。

CREATE TABLE `order` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', 
 `total_price` decimal(10,2) NOT NULL DEFAULT '0.00', 
 `user_id` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`), KEY `idx_user_id` (`user_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

实际开发中，一般不会建立外键约束。

四、SQL类原则

1.SQL语句尽可能简单

在开发过程中，我们尽量要保持SQL语句的简单性，我们对比一下大SQL和多个简单SQL

传统设计思想
BUG MySQL NOT
一条SQL只能在一个CPU运算
5000+ QPS的高并发中，1秒大SQL意味着？
可能一条大SQL就把整个数据库堵死

拒绝大SQL，拆解成多条简单SQL

简单SQL缓存命中率更高
减少锁表时间，特别是MyISAM
用上多CPU

2.保持事务（连接）短小

事务/连接使用原则：即开即用，用完即关
与事务无关操作都放到事务外面，减少锁资源的占用
不破坏一致性前提下，使用多个短事务代替长事务

举例：

1）发帖时的图片上传等待

2）大量的sleep连接

3.尽可能避免使用SP/TRIG/FUNC

线上OLTP系统中，我们应当：

尽可能少用存储过程
尽可能少用触发器
减少使用MySQL函数对结果进行处理

将上述这些事情都交给客户端程序负责

4.尽量不用SELECT *

用SELECT * 时，将会更多的消耗CPU、内存、IO以及网络带宽

我们在写查询语句时，应当尽量不用SELECT * ,只取需要的数据列：

更安全的设计：减少表变化带来的影响
为使用covering index提供可能性
Select/JOIN 减少硬盘临时表生成，特别是有TEXT/BLOB时

举例：

不推荐：

SELECT * FROM tag
WHERE id = 999148

推荐：

SELECT keyword FROM tag
WHERE id = 999148

5.改写OR为IN()

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

OR效率：O(n)

IN效率：O(Log n）

当n很大时，OR会慢很多

注意控制IN的个数，建议n小于200

举例：

不推荐：

Select * from opp WHERE phone='12347856' or phone='42242233'

推荐：

Select * from opp WHERE phone in ('12347856' , '42242233')

6.改写OR为UNION

不同字段，将or改为union

减少对不同字段进行 "or" 查询
Merge index往往很弱智
如果有足够信心：set global optimizer_switch='index_merge=off';

举例：

不推荐：

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

推荐：

Select * from opp 
WHERE phone='010-88886666' 
union 
Select * from opp 
WHERE cellPhone='13800138000';

7.避免负向查询和%前缀模糊查询

在实际开发中，我们要尽量避免负向查询，那什么是负向查询呢，主要有以下：

NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等

同时，我们还要避免%前缀模糊查询，因为这样会使用B+ Tree，同时会造成使用不了索引，并且会导致全表扫描，性能和效率可想而知

举例：

8.减少COUNT(*)

在开发中我们经常会使用COUNT(*)，殊不知这种用法会造成大量的资源浪费，因为COUNT(*)资源开销大，所以我们能不用尽量少用

对于计数类统计，我们推荐：

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

来对比一下COUNT(*)和其他几个COUNT吧：

`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '公司的id',
`sale_id` int(10) unsigned DEFAULT NULL,

结论：

COUNT(*)=COUNT(1)

COUNT(0)=COUNT(1)

COUNT(1)=COUNT(100)

COUNT(*)!=COUNT(col)

9.LIMIT高效分页

传统分页：

Select * from table limit 10000,10;

LIMIT原理：

Limit 10000,10
偏移量越大则越慢

推荐分页：

Select * from table WHERE id>=23423 limit 11; 
#10+1 (每页10条)
select * from table WHERE id>=23434 limit 11;

分页方式二：

Select * from table WHERE id >= ( select id from table limit 10000,1 ) limit 10;

分页方式三：

SELECT * FROM table INNER JOIN (SELECT id FROM table LIMIT 10000,10) USING (id) ;

分页方式四：

#先使用程序获取ID：
select id from table limit 10000,10;
#再用in获取ID对应的记录
Select * from table WHERE id in (123,456…) ;

具体需要根据实际的场景分析并重组索引

示例：

10.用UNION ALL 而非UNION

如果无需对结果进行去重，仅仅是对多表进行联合查询并展示，则用UNION ALL，因为UNION有去重开销

举例：

MySQL>SELECT * FROM detail20091128 UNION ALL 
SELECT * FROM detail20110427 UNION ALL 
SELECT * FROM detail20110426 UNION ALL 
SELECT * FROM detail20110425 UNION ALL 
SELECT * FROM detail20110424 UNION ALL 
SELECT * FROM detail20110423;

11.分解联接保证高并发

高并发DB不建议进行两个表以上的JOIN

适当分解联接保证高并发：

可缓存大量早期数据
使用了多个MyISAM表
对大表的小ID IN()
联接引用同一个表多次

举例：

原SQL：

MySQL> 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=‘二手玩具';

分解SQL：

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

12.GROUP BY 去除排序

使用GROUP BY可以实现分组和自动排序

无需排序：Order by NULL

特定排序：Group by DESC/ASC

举例：

13.同数据类型的列值比较

原则：数字对数字，字符对字符

数值列与字符类型比较：同时转换为双精度进行比对

字符列与数值类型比较：字符列整列转数值，不会使用索引查询

举例：

字段：`remark` varchar(50) NOT NULL COMMENT '备注,默认为空',

MySQL>SELECT `id`, `gift_code` FROM gift 
WHERE `deal_id` = 640 AND remark=115127; 
1 row in set (0.14 sec)
 
 
MySQL>SELECT `id`, `gift_code` FROM pool_gift 
WHERE `deal_id` = 640 AND remark='115127'; 
1 row in set (0.005 sec)

14.Load data 导数据

批量数据快导入：

成批装载比单行装载更快，不需要每次刷新缓存
无索引时装载比索引装载更快
Insert values ,values，values 减少索引刷新
Load data比insert快约20倍

尽量不用INSERT ... SELECT，一个是有延迟，另外就是会同步出错

15.打散大批量更新

大批量更新尽量凌晨操作，避开高峰
凌晨不限制
白天上线默认为100条/秒（特殊再议）

举例：

update post set tag=1 WHERE id in (1,2,3); 
sleep 0.01; 
update post set tag=1 WHERE id in (4,5,6); 
sleep 0.01;
……

16.Know Every SQL

作为DBA乃至数据库开发人员，我们必须对数据库的每条SQL都非常了解，常见的命令有：

SHOW PROFILE
MYSQLsla
MySQLdumpslow
explain
Show Slow Log
Show Processlist
SHOW QUERY_RESPONSE_TIME(Percona）

五、约定类原则

1.隔离线上线下

构建数据库的生态环境，确保开发无线上库操作权限

原则：线上连线上，线下连线下

生产数据用pro库
预生产环境用pre库
测试用test库
开发用dev库

2.禁止未经DBA确认的子查询

大部分情况优化较差
特别WHERE中使用IN id的子查询
一般可用JOIN改写

举例：

MySQL> select * from table1 where id in (select id from table2); 
MySQL> insert into table1 (select * from table2); //可能导致复制异常

3.永远不在程序端显式加锁

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

对于类似并发扣款等一致性问题，我们采用事务来处理，Commit前进行二次校验冲突

4.统一字符集为UTF8

5.统一命名规范

1）库表等名称统一用小写

2）索引命名默认为“idx_字段名"

3）库名用缩写，尽量在2~7个字母

DataSharing ==> ds

4）注意避免用保留字命名

以上所有坑，建议数据库开发人员都要铭记于心。希望对大家的学习有所帮助，也希望大家多多支持。

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容

数据库系统概念第6版

￥41.1元 机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » MySQL数据库开发的36条原则(小结)

MySQL数据库开发的36条原则(小结)

数据库系统概念第6版

相关推荐

评论