当前位置：Gxlcms > 数据库问题 > MySQL数据库开发的36条原则

MySQL数据库开发的36条原则

时间：2021-07-01 10:21:17 帮助过：11人阅读

CREATE TABLE t1 ( 2 id INT NOT NULL AUTO_INCREMENT, 3 data TEXT NOT NULL, 4 PRIMARY KEY(id) 5 ) ENGINE=InnoDB;

 

6.不在数据库里存图片

先上图：

技术图片

技术图片

可见，如果将图片全部存在数据库，将使得数据库体积变大，会造成读写速度变慢。

图片存数据库的弊端：

  • 对数据库的读/写的速度永远都赶不上文件系统处理的速度

  • 数据库备份变的巨大，越来越耗时间

  • 对文件的访问需要穿越你的应用层和数据库层

★推荐处理办法：数据库中保存图片路径

按照年月日生成路径。具体是按照年月日还是按照年月去生成路径，根据自己需要(不一定是按照日期去生成)。

理解为什么要分散到多个文件夹中去才是关键，涉及到一个原理就明白了：

操作系统对单个目录的文件数量是有限制的。当文件数量很多的时候。从目录中获取文件的速度就会越来越慢。所以为了保持速度，才要按照固定规则去分散到多个目录中去。

图片分散到磁盘路径中去。数据库字段中保存的是类似于这样子的”images/2012/09/25/ 1343287394783.jpg”

原来上传的图片文件名称会重新命名保存，比如按照时间戳来生成，1343287394783. jpg。这样子是为了避免文件名重复，多个人往同一个目录上传图片的时候会出现。

反正用什么样的规则命名图片，只要做到图片名称的唯一性即可。

比如网站的并发访问量大，目录的生成分得月细越好。比如精确到小时，一个小时都可以是一个文件夹。同时0.001秒有两个用户同时在上传图片(因为那么就会往同一个小时文件夹里面存图片)。因为时间戳是精确到秒的。为了做到图片名称唯一性而不至于覆盖，生成可以在在时间戳后面继续加毫秒微秒等。总结的规律是，并发访问量越大。就越精确就好了。

题外话：

1）为什么保存的磁盘路径，是”images/2012/09/25/1343287394783.jpg”，而不是” /images/2012/09/25/ 1343287394783.jpg”(最前面带有斜杠)

在页面中需要取出图片路径展示图片的时候，如果是相对路径，则可以使用”./”+”images/2012/09/25/1343287394783.jpg”进行组装。

如果需要单独的域名(比如做cdn加速的时候)域名，img1.xxx.com,img2.xxx.com这样的域名，

直接组装 “http://img1.xxx.com/”+”images/2012/09/25/1343287394783.jpg”

2）为什么保存的磁盘路径，是”images/2012/09/25/1343287394783.jpg”，而不是“http://www.xxx.com/images/2012/09/25/1343287394783.jpg"

这里其实涉及到CDN的知识，具体CDN的知识在此不多展开，简而言之：

cdn服务：对于静态内容是非常适合的。所以像商品图片，随着访问量大了后，租用cdn服务，只需要把图片上传到他们的服务器上去。

例子：北京访问长沙服务器，距离太远。我完全可以把商品图片，放到北京的云服务（我觉得现在提供给网站使用的云存储其实就是cdn，给网站提供分流和就近访问）上去。这样子北京用户访问的时候，实际上图片就是就近获取。不需要很长距离的传输。

自己用一个域名img.xxx.com来载入图片。这个域名解析到北京的云服务上去。

做法:数据库中保存的是” images/2012/09/25/1343287394783.jpg”,

这些图片实际上不存储在web服务器上。上传到北京的cdn服务器上去。

我从数据库取出来，直接”img.xxx.com/”+” images/2012/09/25/1343287394783.jpg”

比如如果还有多个，就命名img1.xx.com、img2.xx.com

反正可以随便。所以如果把域名直接保存进去。就显得很麻烦了。迁移麻烦。

三、索引类原则

1.谨慎合理添加索引

  • 添加索引是为了改善查询

  • 添加索引会减慢更新

  • 索引不是越多越好

  • 能不加的索引尽量不加（综合评估数据密度和数据分布，最好不超过字段数20%）

  • 结合核心SQL有限考虑覆盖索引

举例：不要给“性别”列创建索引

理论文章会告诉你值重复率高的字段不适合建索引。不要说性别字段只有两个值，网友亲测，一个字段使用拼音首字母做值，共有26种可能，加上索引后，百万加的数据量，使用索引的速度比不使用索引要慢！

为什么性别不适合建索引呢？因为你访问索引需要付出额外的IO开销，你从索引中拿到的只是地址，要想真正访问到数据还是要对表进行一次IO。假如你要从表的100万行数据中取几个数据，那么利用索引迅速定位，访问索引的这IO开销就非常值了。但如果你是从100万行数据中取50万行数据，就比如性别字段，那你相对需要访问50万次索引，再访问50万次表，加起来的开销并不会比直接对表进行一次完整扫描小。

2.字符字段必须建前缀索引

区分度：

  • 单字母区分度：26

  • 4字母区分度：26*26*26*26 = 456,976

  • 5字母区分度：26*26*26*26*26 = 11,881,376

  • 6字母区分度：26*26*26*26*26*26 = 308,915,776

字符字段必须建前缀索引，例如：

`pinyin` varchar(100) DEFAULT NULL COMMENT ‘小区拼音‘, 
KEY `idx_pinyin` (`pinyin`(8)), 
) ENGINE=InnoDB

3.不在索引列做运算

原因有两点：

1）会导致无法使用索引

2）会导致全表扫描

举例：

技术图片

BAD SAMPLE:

select * from table 
WHERE to_days(current_date) – to_days(date_col) <= 10

GOOD SAMPLE:

select * from table 
WHERE date_col >= DATE_SUB(‘2011-10-22‘,INTERVAL 10 DAY);

4.自增列或全局ID做INNODB主键

  • 对主键建立聚簇索引

  • 二级索引存储主键值

  • 主键不应更新修改

  • 按自增顺序插入值

  • 忌用字符串做主键

  • 聚簇索引分裂

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

  • 若不指定主键，InnoDB会用唯一且非空值索引代替

技术图片

5.尽量不用外键

线上OLTP系统尽量不用外键：

  • 外键可节省开发量

  • 有额外开销

  • 逐行操作

  • 可“到达”其他表，意味着锁

  • 高并发时容易死锁

建议由程序保证约束

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

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）注意避免用保留字命名

技术图片

以上所有坑，建议数据库开发人员都要铭记于心。

作者：真爱无敌

MySQL数据库开发的36条原则

标签：自增   foreign   dex   LTP   触发器   类型比较   唯一性   log   的区别   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！