共计 10277 个字符,预计需要花费 26 分钟才能阅读完成。
一、数据库根本设计规范
- 数据库设计时,应该要对当前扩大进行思考
- 所有表必须应用 InnoDB 存储引擎
没有特殊要求(即 InnoDB 无奈满足的性能如:列存储,存储空间数据等)的状况下,所有表必须应用 InnoDB 存储引擎(MySQL 5.5 之前默认应用 Myisam,5.6 当前默认的为 InnoDB)InnoDB 反对事务,反对行级锁,更好的恢复性,高并发下性能更好。(== 咱们程序中禁止应用事务,因为事务有锁表危险 ==) - 数据库和表的字符集对立应用 UTF8
兼容性更好,对立字符集能够防止因为字符集转换产生的乱码,不同的字符集进行比拟前须要进行转换会造成索引生效。 - 所有表和字段都须要增加正文
应用 comment 从句增加表和列的备注 从一开始就进行数据字典的保护。 - 尽量管制单表数据量的大小,倡议管制在 500 万以内
500 万并不是 MySQL 数据库的限度,过大会造成批改表构造、备份、复原都会有很大的问题,能够用历史数据归档(利用于日志数据),分库分表(利用于业务数据)等伎俩来控制数据量大小。 - 审慎应用 MySQL 分区表
分区表在物理上体现为多个文件,在逻辑上体现为一个表 审慎抉择分区键,跨分区查问效率可能更低 倡议采纳物理分表的形式治理大数据。 - 禁止在表中建设预留字段
预留字段的命名很难做到见名识义 预留字段无奈确认存储的数据类型,所以无奈抉择适合的类型 对预留字段类型的批改,会对表进行锁定 - 禁止在数据库中存储图片,文件等大的二进制数据
通常文件很大,会短时间内造成数据量快速增长,数据库进行数据库读取时,通常会进行大量的随机 IO 操作,文件很大时,IO 操作很耗时 通常存储于文件服务器,数据库只存储文件地址信息。 - 禁止在线上做数据库压力测试
- 禁止从开发环境,测试环境间接连贯生成环境数据库
二、数据库表设计规范
- 设计表时,应该要对当前扩大进行思考
- 所有表必须应用 InnoDB 存储引擎
- 每张表必须有一个主键
- 禁止应用外键束缚
- 表的字符集对立应用 utf8mb4; 排序应用 utf8mb4_general_ci
- 设计表时要加正文
- 表前缀用项目名称字母缩写;所有表名都小写,单词之间用下划线离开,单词都用复数模式,命名要能做到见名识意,并且最好不要超过 3 2 个字符
- 关联表以_index 结尾
- 长期库表必须以 tmp_ 为前缀并以日期为后缀
- 备份表必须以 bak_ 为前缀并以日期 为后缀
[^ 备份表]: bak_kyy_user_2021-11-01
- 分表以_00~99 结尾
[^ 分表]: kyy_user_00、kyy_user_01
三、数据库字段设计规范
- 命名要能做到见名识意,单词之间用下划线离开
- 字段要有明确的正文,形容该字段的用处及可能存储的内容
- 禁止在表中建设预留字段。预留字段的命名很难做到见名识义 预留字段无奈确认存储的数据类型,所以无奈抉择适合的类型 对预留字段类型的批改,会对表进行锁定
- 所有字段,均为非空(NOT NULL),最好显示指定默认值
- 数值类型的字段请应用 UNSIGNED 属性
- 是别的表的外键均应用 xxx_id 的形式来表明;
- 所有的布尔值字段,以 is_ 前缀,如 is_hot、is_deleted,都必须设置一个默认值,并设为 0;应用 tinyint 类型
- varchar 类型字段的程序处理,要验证用户输出,不要超出其预设的长度;
- 不同表中表白同一意思字段要对立,例如创立工夫对立 用 created_at; 更新工夫用 updated_at; 备注对立用 remark 排序对立用 order_id
- 防止应用 ENUM 类型的 能够用 tinyint 类型代替
- 同财务相干的金额类型应用 decimal 类型
- text 字段尽量少用,或是拆到冗余表中
- 禁止给表中的每一列都建设独自的索引,正当应用联结索引,倡议索引数量不超过 5 个
- 防止建设冗余索引和反复索引
[^ 反复索引示例:]: primary key(id)、index(id)、unique index(id)
[^ 冗余索引示例]: index(a,b,c)、index(a,b)、index(a)
四、数据库操作标准
- 禁止应用事务,有锁表危险
- 不做非凡阐明,不做物理删除,对立做软删除
- 防止数据类型的隐式转换(隐式转换会导致索引生效)
- 尽量不应用 select * 倡议应用 select < 字段列表 > 查问
- 禁止应用不蕴含字段列表的 insert 语句
- 禁止应用联表查问
- 倡议应用 in 代替 or,in 的值不要超过 500 个
- 禁止应用 order by rand() 进行随机排序
- WHERE 从句中禁止对列进行函数转换和计算
-
什么时候应用组合索引,什么时候应用独自索引 以下是索引应用示例:
一. 后期数据筹备
1. 建表
CREATE TABLE `user` (`uid` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `pwd` varchar(50) DEFAULT NULL, `create_time` datetime DEFAULT NULL, `modify_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `rids` varchar(15) DEFAULT NULL, `nickname` varchar(45) DEFAULT NULL, `company` varchar(15) DEFAULT NULL, PRIMARY KEY (`uid`), UNIQUE KEY `name_UNIQUE` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
2. 插入数据
INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (1, 'rocker', 'rocker', NULL, '2019-10-08 11:05:02', '1', 'rocker', 'rocker'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (2, 'danny', 'danny', NULL, '2019-10-08 11:31:36', '2', 'rocker', 'danny'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (3, 'tom', 'tom', NULL, '2019-10-08 11:31:39', '1', 'tom', 'rocker'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (4, 'messi', 'messi', NULL, '2019-10-08 11:31:21', '2', 'messi', 'messi'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (5, 'wenger', 'wenger', NULL, '2019-10-08 11:29:38', '1', 'wenger', 'rocker'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (6, 'henry', 'henry', NULL, '2019-10-08 11:30:46', '2', 'henry', 'henry'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (7, 'ronaldo', 'ronaldo', NULL, '2019-10-08 11:30:49', '1', 'ronaldo', 'ronaldo'); INSERT INTO `monitor`.`user`(`uid`, `name`, `pwd`, `create_time`, `modify_time`, `rids`, `nickname`, `company`) VALUES (8, 'kaka', 'kaka', NULL, '2019-10-08 11:29:45', '2', 'kaka', 'rocker');
二. 剖析
1. 不加索引
首先在 ’nickname’ 和‘company’这俩字段不加索引的状况下执行一个查问语句,并剖析
mysql> explain select * from user where nickname = 'rocker' and company = 'rocker'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 12.50 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
能够看到,没有走索引,总共查问了 8 条数据,而表中总共也是 8 条数据,相当于全表扫描了。
mysql> explain select * from user where company = 'rocker' or nickname = 'rocker'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | user | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 23.44 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
能够看到:不加任何索引的状况下,不论是 and 还是 or,都是全表扫描,没有索引。
2. 独自索引
给 nickname 和 company 别离加上索引,再执行 and 和 or 的 sql 查问
alter table user add index `idx_nickname` (`nickname`); alter table user add index `idx_company` (`company`);
执行查问语句 and
mysql> explain select * from user where nickname = 'rocker' and company = 'rocker'; +----+-------------+-------+------+--------------------------+--------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+--------------------------+--------------+---------+-------+------+-------------+ | 1 | SIMPLE | user | ref | idx_nickname,idx_company | idx_nickname | 138 | const | 2 | Using where | +----+-------------+-------+------+--------------------------+--------------+---------+-------+------+-------------+ 1 row in set (0.05 sec)
执行查问语句 or
mysql> explain select * from user where company = 'rocker' or nickname = 'rocker'; +----+-------------+-------+------+--------------------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+--------------------------+------+---------+------+------+-------------+ | 1 | SIMPLE | user | ALL | idx_nickname,idx_company | NULL | NULL | NULL | 8 | Using where | +----+-------------+-------+------+--------------------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
能够看到:加上索引后 and 查问是能够走索引的,然而只有一个索引起作用,对于另一个索引字段还是要进行遍历,而且 and 查问会依据关联性高 (合乎该条件的行数少) 抉择具体走哪个索引
or 查问不走索引
3. 组合索引
删除原先的独自索引,新增组合索引
alter table user drop index `idx_nickname` alter table user drop index `idx_company` alter table user add index `idx_composition` (`nickname`,`company`);
执行查问语句 and
mysql> explain select * from user where nickname = 'rocker' and company = 'rocker';+----+-------------+-------+------+-----------------+-----------------+---------+-------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+-----------------+-----------------+---------+-------------+------+-------------+| 1 | SIMPLE | user | ref | idx_composition | idx_composition | 186 | const,const | 1 | Using where |+----+-------------+-------+------+-----------------+-----------------+---------+-------------+------+-------------+1 row in set (0.00 sec)
执行查问语句 or
mysql> explain select * from user where company = 'rocker' or nickname = 'rocker';+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+| 1 | SIMPLE | user | ALL | idx_composition | NULL | NULL | NULL | 8 | Using where |+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+1 row in set (0.00 sec)
能够看到:加上组合索引后,组合索引起作用,只需查问一条合乎后果的数据,效率要比独自索引高,
然而复合索引对于 or 查问不起作用
4. 组合索引查问单个索引列
对于组合索引为 (
nickname
,company
) 这个程序的状况alter table user drop index `idx_composition`;alter table user add index `idx_composition` (`nickname`,`company`);mysql> explain select * from user where nickname = 'rocker';+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+| 1 | SIMPLE | user | ref | idx_composition | idx_composition | 138 | const | 2 | Using where |+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from user where company = 'rocker';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 8 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)
能够看到:组合索引中 nickname 在前时,独自查问 nickname 会走索引,独自查问 compamy 不会走索引
对于组合索引为 (
company
,nickname
) 这个程序的状况alter table user drop index `idx_composition`;alter table user add index `idx_composition` (`company`,`nickname`);mysql> explain select * from user where nickname = 'rocker';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | user | ALL | NULL | NULL | NULL | NULL | 8 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from user where company = 'rocker';+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+| 1 | SIMPLE | user | ref | idx_composition | idx_composition | 48 | const | 2 | Using where |+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+1 row in set (0.00 sec)
能够看到:组合索引中 compamy 在前时,独自查问 compamy 会走索引,独自查问 nickname 不会走索引
如果组合索引是(A,B),则对于条件 A =a,是能够用上这个组合索引的,因为组合索引是先依照第一列进行排序的,所以没必要对 A 独自建设一个索引,然而对于 B = b 就用不上了,因为只有在第一列雷同的状况下,才比拟第二列,因此第二列雷同的,能够散布在不同的节点上,没方法疾速定位