-
数据存储方案
image.png
- 选 SQL 还是 NoSQL
- 使用sql
- 需要确保事务，数据结构是不变的
- 使用nosql
- 存储大量没有结构的数据
- 充分利用云计算和存储
- Nosql 数据 跨多个数据中心进行扩展，不会带来很多麻烦。
- 快速发展的
- 两周一个迭代，频繁更新数据结构
- 使用sql
NoSQL vs SQL Databases: 6 Important Differences | Upwork
一. Mysql 开发规范
- 针对内部开发的一系列建议或规则，由DBA制定
1. 规范存在的意义
保证线上数据库 schema 规范
减少出问题概率
方便自动化管理
需要长期坚持规范，这对开发和DBA来说是双赢
2. 为什么有这个规范
- 很大一部分开发规范都跟 索引实现有关
- 索引存在意义
- 假如有 10 亿个不重复的数字， 你要找到其中一个数
- 直接循环 时间复杂度 O(n) = 10亿 次
- B+树 时间复杂度接近 O(logn) = 29 次
- 假如有 10 亿个不重复的数字， 你要找到其中一个数
- 索引存在意义
2.1 InnoDB 索引的实现
- innoDB 索引使用 B+树实现
- 建表
CREATE TABLE `workers`
(
`id` int AUTO_INCREMENT COMMENT '员工工号'
PRIMARY KEY,
`name` varchar(16) NOT NULL COMMENT '员工名字',
`sales` int NULL COMMENT '员工业绩'
) CHARSET = utf8;
INSERT INTO `workers` (`id`, `name`, `sales`) VALUES (1, '江南', 12744);
INSERT INTO `workers` (`id`, `name`, `sales`) VALUES (3, '今何在', 14082);
INSERT INTO `workers` (`id`, `name`, `sales`) VALUES (7, '路明非', 14738);
INSERT INTO `workers` (`id`, `name`, `sales`) VALUES (8, '吕归尘', 7087);
INSERT INTO `workers` (`id`, `name`, `sales`) VALUES (11, '姬野', 8565);
INSERT INTO `workers` (`id`, `name`, `sales`) VALUES (15, '凯撒', 8501);
INSERT INTO `workers` (`id`, `name`, `sales`) VALUES (20, '绘梨衣', 7890);
INSERT INTO `workers` (`id`, `name`, `sales`) VALUES (21, '西泽尔', 16634);
- 形成索引文件
- 聚簇索引
- 怎么划分 聚簇索引 与 非聚簇索引 依据 根据叶子节点的内容
-
叶子节点存的是整行数据, 索引文件就是数据文件
image.png
- 聚簇索引
- 添加一个 索引
alter table workers add index index_name(name);
- 非聚簇索引 也叫 辅助索引，二级索引
-
叶子节点存放的是主键的值
image.png
回表
select * from workers where name='吕归尘';
image.png
覆盖索引
select id,name from workers where name='吕归尘';
image.png
image.png
联合索引 与 最左前缀原则
- 查询条件有多个，太多字段建立索引，维护索引成本会高
- 左前缀匹配原则
- 例如 我要高频 根据 业绩 和 名称 查这个人
alter table workers add index index_sales_name(sales, name);
- 形成的索引文件
-
索引项是按照 索引定义里面出现的字段顺序排序的。
image.png
-
# 最左前缀原则 这两个都能命中 index_id_name这个索引
SELECT * FROM `workers` WHERE `name` = 'xx' AND `sales` = 1;
SELECT * FROM `workers` WHERE `sales ` = 1;
# 这个条件 不能命中 index_id_name这个索引
SELECT * FROM `workers` WHERE `name` = 'xx';
3. 从 InnoDB 索引层面 不符合规范会有什么问题
-
表必须有主键（复合主键，无主键需要说明原因），统一使用应用生成的全局唯一id，类型为bigint且单调递增不推荐使用数据库auto_increment。
- 假如我们 用一个业务唯一 字符串做主键， 例如身份证
-
问题
image.png- 问题1 因为innoDB 使用聚簇索引，数据记录本身存放在主索引的叶子节点上。同一个叶子节点内(大小为一个内存页或磁盘页) 的各条是按顺序存放的，插入新记录的时候，插入适当的节点和位置，如果达到负载因子（InnoDB默认15%16）,则要开辟新的页， 。
- 如果使用字符串主键 ，主键的值近似于随机，每次插入 都可能移动数据，
- 目标页可能已经回写到磁盘而从缓存中清除，又要从磁盘中读回来，增加开销
- 同时频繁的移动，分页造成了大量的碎片，得到不紧凑的索引结构，后续不得不通过 OPTIMIZE TABLE（重新组织 table 数据和关联索引数据的物理存储）
- 如果是自增主键，每次插入新记录，就会顺序添加到当前索引节点后续位置，页满自动开辟新的一页。
- 如果使用字符串主键 ，主键的值近似于随机，每次插入 都可能移动数据，
- 问题2
- 一个表的非聚簇索引 有很多个。 每个都是一颗B+树，叶子节点都是 主键值
- 主键长度越小，普通索引叶子节点就越小，普通索引占用空间就小。
-
当是 bigint 就是 8字节，当是 字符串 远远大于8字节
image.png
- 一个表的非聚簇索引 有很多个。 每个都是一颗B+树，叶子节点都是 主键值
- 问题1 因为innoDB 使用聚簇索引，数据记录本身存放在主索引的叶子节点上。同一个叶子节点内(大小为一个内存页或磁盘页) 的各条是按顺序存放的，插入新记录的时候，插入适当的节点和位置，如果达到负载因子（InnoDB默认15%16）,则要开辟新的页， 。
-
禁用default NULL，业务上也尽量不使用NULL值。字符串默认值可用default ''代替，数值可用default 0代替
- 可以为NULL的列， 使得索引，索引统计和值比较都变复杂
- 需要使用 ISNULL()来判断是否为 NULL 值。
- NULL 与任何值的直接比较都为 NULL。
- 需要使用 ISNULL()来判断是否为 NULL 值。
-
可以为NULL的列，每个索引需要一个额外的字节。
image.png
- 可以为NULL的列， 使得索引，索引统计和值比较都变复杂
表必备3个字段：id，create_time，modify_time。说明：id必为主键，类型为unsigned bigint，单调递增。create_time，modify_time类型均为datetime（时间字段为了追溯数据的变化）
- Timestamp 时区有关
- ‘1970-01-01 00:00:00’到现在的毫秒数， 最大值到 2038年 ， 可能又是一个千年虫问题
- 精确到秒 是 4字节， 精确到毫秒 是 7字节。
- DateTime 时区无关
- 不管保留几位毫秒数 都是8字节
二 资料总结
博客：
CodingLabs - MySQL索引背后的数据结构及算法原理
NoSQL vs SQL Databases: 6 Important Differences | Upwork
- 数据结构和算法 可视化工具
书籍：
《高性能 MySQL》
适合 dba 和 开发
《MySQL 技术内幕：InnoDB 存储引擎》
想了解内部原理
