MySQL中文章表的设计 mysql 设计

转载

mob64ca13f30cc8 2023-09-02 07:28:24

文章标签 MySQL中文章表的设计数据库 sql mysql SQL 文章分类 MySQL 数据库

好记忆不如烂笔头，能记下点东西，就记下点，有时间拿出来看看，也会发觉不一样的感受.

一、总体设计思想

二、字段相关设计原则

三、索引设计原则

四、SQL操作原则

五、其他原则

一、总体设计思想

   1.不要在数据库做运算符操作
       数据库服务器资源宝贵，要特别珍惜利用，如果在数据库上做如rand(),md5()等操作，在并发下对服务器很有压力，让数据库多做她擅长的事：尽量不在数据库做运算，复杂运算秱到程序端CPU，尽可能简单应用MySQL。
   2.合理控制数据库表的数据量
       单库不要超过300张表；单表的数据量不要超过5000W。如果要达到较好的体验,数值INT型的，不要超过1000W，字符型的不要超过500W。当数据量超过一定量时候，需要考虑分表，分库操作。
   3.控制好表的列数
       单表字段数上限控制在40个以内，超过的部分，可以考虑附表或者放入其他的表中。列数过多，在insert 数据的时候，就会慢，get的时候，也会慢。
   4.三范式和冗余共存
       并不是每个表的列信息都必须按照三范式来，而是效率优先、提升性能，没有绝对的对与错，适当时牺牲范式、加入冗余。
   5.拒绝三大设置
       大SQL (BIG SQL)；大事务 (BIG Transaction)；大批量 (BIG Batch)；任何的一大，在并发场景下，都可能拖慢服务，甚至让数据库服务器dang掉。

二、字段相关设计原则

   1.用好数值字段类型
       使用好INT类型：TINYINT(1Byte)、SMALLINT(2B)、MEDIUMINT(3B)、INT(4B)、BIGINT(8B);使用好浮点型：FLOAT(4B)、DOUBLE(8B);金融类型：DECIMAL(M,D)
   2.将字符串转换成数字
       数字类型比字符串类型索引的有点是：1、更高效;2、查询更快;3、占用空间更小；例如存储用IP用数字而不用字符串。
   3.优先使用枚举(ENUM)和集合(SET)
       例如：`sex` enum('F','M') COMMENT '性别';`status` enum('0','1','2','3') COMMENT '账户状态';
   4.避免使用NULL做为字段的默认值
       如果使用NULL作为字段的默认值,会导致的情况是：1、很难进行查询优化;2、NULL列加索引，需要额外空间;3、含NULL复合索引无效;
   5.少用并拆分TEXT/BLOB/LONGTEXT
       1、TEXT类型处理性能远低亍VARCHAR;2、强制生成硬盘临时表;3、浪费更多空间;4、VARCHAR(65535)==>64K (注意UTF-8);
       总结:尽量不要用TEXT/BLOB/LONGTEX数据类型,若必须使用则拆分到单独的表。
   6.不要在数据库里存储图片
       存储图片路径，而不是将图片BASE64进行流式存储。

三、索引设计原则

   1.谨慎合理的添加索引
       索引的优点是：提高响应时间,改善查询速度;缺点是：更新操作缓慢，添加操作缓慢;原则：索引适可而止,不是越多越好,核心查询字段应该加索引
   2.字符字段应该建立前缀索引
       `order_no` varchar(100) NOT NULL COMMENT '订单编号', //order20220501000000001
       KEY `idx_order_no` (`order_no`(5)),
   3.不要在索引列做运算
       索引列上做的运算会导致:1、索引失效; 2、全表扫描
   4.自增列或全局ID做INNODB主键
       用独立于业务的AUTO_INCREMENT列或全局ID生成器做代理主键,若不指定主键，InnoDB会用唯一且非空值索引代替。
   5.尽量不使用外键
       外键的优点是：外键可节省开发量;可锁其他表;缺点是：高并发时候容易死锁;最好的方案是在程序中保证主外键约束。

四、SQL操作原则

   1.SQL语句尽可能的简单
       Simple is beauty！越是简单的SQL语句,越容易操作。一条SQL语句只能在一个CPU中运算,所以巨型SQL极有可能将整个数据库服务器堵死;
       应对方案：将大SQL拆解成若干小SQL,在程序内存中完成数据集的生成。
   2.保持事务连接最小
       要保持事务/数据连接短小精悍：事务/连接即开即用,用完即关。事务只处理事务相关操作，无关操作放在事务之外操作。
   3.尽可能少的使用存储过程,触发器,功能函数
       存储过程、触发器等的使用,都是建立在数据库服务器的CPU上进行的,在并发量上来的时候,会将数据库拖垮、宕机。
   4.不要使用SELECT *
       select * 的操作会更多消耗CPU、内存、IO、网络带宽等资源,需要什么数据就提取什么数据。
   5.改OR为IN 操作
       注意在 IN 操作时候, IN 控制的值在 200个以内.
   6.改OR为UNION 操作
       UNION操作获取数据合集更利于使用数据库的特性。
   7.避免负向查询和% 前缀模糊查询
       避免负向查询:NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等;
       避免 % 前缀模糊查询:B+ Tree使用不了索引,导致全表扫描;
   8.减少COUNT(*)操作
       COUNT(*)的资源开销大，尽量不用少用;
   9.使用 LIMIT 实现高效分页
       Select order_no,user_id from table WHERE id>=100000 limit 10;
   10.用UNION ALL 而非 UNION
       若无需对结果进行去重，则用UNION ALL,UNION有去重开销
   11.分解联接保证高并发(最多可以进行三张表以内的关联查询)
       高幵发DB不建议进行两个表以上的JOIN