设计流程
- 概要设计产物-ER图
- 详细设计产物-数据模型图-pdshell
MySQL整体逻辑结构
- 先连接mysql
- 查询缓存:提高效率(mysql8之后移除)
- 解析器:校验sql语法
- 优化器:对sql查询结果进行优化
- 执行计划:怎么干
- 存储引擎层:接口,开放性的,可安装新的实现。
MySQL存储引擎的特点
- 5.5后推荐选用InnoDB
- MyISAM引擎,它是一个适合于读多写少的引擎,但是每次写操作都会锁表,不支持并发读写,且不支持事务。
- InnoDB引擎,支持事务,每次写操作是行锁,支持并发读写,所以适合读多写多的场景。
- TokuDB引擎,支持事务,适合写多读少的场景,适合于做归档操作,比如,InnoDB引擎的表如果超过 2000万(500万),那么读写性能会明显下降,那么我们一般的做法是将不常用的数据转移到归档表里 面,这样业务表的数据就可以瘦身,那么这个采用TokuDB引擎来操作归档表,其写性能可以达到 InnoDB引擎的10倍,且数据压缩比也达到了10倍以上。
建表原则
- 数据类型从小出发。应该选择可以正确存储数据的最小数据类型。 最小的数据类型意味占用更小的磁盘,内存和CPU缓存。
- 但是提醒一点,要正确估算存储的数据的取值范围,后期修改数据类型会是一个非常耗时的过程。
- 列避免NULL,一般设置为NOT NULL。通常情况下,列的值出现NULL,不利于MySQL做优化
选择适合的数据类型
int类型
数据类型 | 位数 |
---|---|
tinyint | 8(无符号 0-255,有符号-128-127) |
smallint | 16 |
mediumint | 24 |
int | 32 |
bigint | 64 |
注意事项: 我们通常会见到一些客户端软件,给我们的创建的字段类型设置为int(11),其实,它不会限制 值的合法范围,只是规定了MySQL的交互工具(客户端)用来显示字符的个数,对于存储和计算而言, int(1)和int(32)没什么区别
float类型
- 浮点类型有float(32),double(64),但对于需要精确点计算的,不建议采用这两种类型。严禁使用。
- 比如对精确度要求高的“金额”字段应该采用什么样的类型?
2.1 选择一:decimel需要额外的空间和计算开销,
2.2 选择二:采用整数类型,比如bigint,在显示时需要通过程序换算,才可以展示小数点的信息(约定只保 留几位小数)
字符串类型
日期类型
建立合适的索引
- 索引是存储引擎用于快速找到记录的一种数据结构。(书的目录)
- ALTER TABLE
t_user
ADD INDEXidx_username
(username
) USING BTREE ; - 索引应该是对查询性能优化最有效的手段。
- 如果查询,没有索引,查数据就需要“全表扫描”,这就是我们要避免的问题。相反,如果查询操作可以使用上索引,那么就可以提高我们的查询性能。
- 优点:提高读的效率,适合读多写少。
- 缺点:降低了写的效率,因为写的时候,需要去维护索引信息。# 写多读多,千万不要用索引。
索引的创建和删除
创建索引:create [unique|fulltext] index 索引名 on 表名 (属性名[长度]);
删除索引:drop index 索引名 on 表名;
索引的数据结构
- 问题:索引到底是一种什么样的数据结构?为什么可以提高查询效率?
- 底层分类:MySQL索引的底层数据结构,主要有两种,一种是B+tree,一种是哈希索引。
- 问题:哪种索引的查询效率更高?
- 很明显是哈希索引,在定值查询方面,时间复杂度为O(1),那为什么不采用哈希索引,原因是哈希索引 无法实现范围查找,而范围查找在业务系统中非常常见。
索引的使用原则
什么时候该添加索引
- where字句中的列,频繁作为查询字段的列,优先考虑建立组合索引
- 表连接关联的列
- 排序用到的列
- 索引的区分度高,索引的效率就越高,这样的列就适合创建索引,比如手机号,邮箱这的字段
什么时候不适合添加索引
- 表的数据量很小,这个时候创建索引就是一种浪费,反而对性能没有帮助
- 该字段数据是属于写多读少的场景
- 区分度低的字段,比如性别,是否删除,是否激活这样的字段
如何合理的创建索引
- 应该将区分度更高的列放在前面,区分度更高,索引的性能也会更好。
- 比如唯一索引的区分度就最高,是1,而像性别这样的字段,区分度就不高,因为数据值不是男就是女。
- Select count(distinct column)/count(*) from table;
前缀索引
- 选择字符列的前n个字符作为索引,这样可以减少索引空间大小,提高索引效率。
- Alter table table_name add index index_name (index_column(length));
- 注意:MySQL无法使用前缀索引做ORDER BY 和GROUP BY
- UUID字符串的特点是无规则的,这样会导致索引树的生成经常变动,建议采用一个与业务无关的字段作为主键.
达到的标准
- 字段命名,见名知意
- 字段的类型选择合理
- 创建合理的索引字段
- 表之间的关系理顺
- 一切都要以需求理解为最初的出发点!