MySQL数据类型选择规范_数据库：MySQL 高性能优化规范建议

占用 4 字节和 INT 相同，但比 INT 可读性高

超出 取值范围的使用 类型存储

经常会有人用字符串存储日期型的数据（不正确的做法）

6. 同财务相关的金额类数据必须使用 类型

类型为精准浮点数，在计算时不会丢失精度

存储容量取决于预设的宽度，每四个数据单位能够容纳九个数值字符，而小数分隔符会占用一个数据单位

可用于存储比 更大的整型数据

数据库索引的构建原则明确指出,每张数据表所包含的索引总数应当加以控制,单表所建立的索引个数最好不超过五个

索引并不是越多越好！索引可以提高效率同样可以降低效率。

索引能够提升检索速度，不过同样会减慢数据添加和修改的速度，在特定情形下还可能使检索速度变慢。

MySQL优化器在确定查询优化方案时，会依据综合信息，针对所有可用的索引逐一进行考量，从而制定出最优的执行方案，倘若存在多个索引都适用于查询，那么优化器构建执行方案所需的时间便会延长，进而影响查询效率。

2. 禁止给表中的每一列都建立单独的索引

在5.6版本发行之前，单个sql语句仅能借助一个数据表里的一个索引执行查询，到了5.6版本之后，即便出现了合并索引的技术改进，其查询效率仍然远逊于采用组合索引的查询手段。

3. 每个 表必须有个主键

这是一种索引结构安排：数据存放的思路和索引的布局是一致的。任何一个表格都可以设置好几个索引，不过表格本身的存储布局只能有一个。

是按照主键索引的顺序来组织表的

4. 常见索引列建议 5.如何选择索引列的顺序

建立索引是为了通过索引来查询数据,这样可以降低随机 IO,提升查询速度,索引能够筛选出越少的数据,那么从硬盘读入的数据也就越少。

不该创建不必要的索引，也不可设立重复的索引，因为那样会耗费更多时间来制定查询方案，应当对经常被执行的查询，优先采用能够完全匹配查询条件的索引

索引覆盖就是包含所有查询条件所涉及列的索引，比如在筛选条件排序规则或分组依据中出现的列，这种索引能够直接满足查询需求

覆盖索引的好处：

8.索引 SET 规范

尽量避免使用外键约束

数据库操作应采用预编译语句执行方式，这是推荐的做法。

预编译语句能够多次利用这些方案，节省 SQL 编译所需耗费的时长，并且能够处理动态 SQL 引发的 SQL 注入风险。

只传参数，比传递 SQL 语句更高效。

相同语句可以一次解析，多次使用，提高处理效率。

2. 避免数据类型的隐式转换

隐式转换会导致索引失效如:

查询客户信息,需要获取其姓名,联系电话,条件是该客户的编号为111,通过数据库命令实现这一功能

3. 充分利用表上已经存在的索引

不要运用带有双百分号的检索要求。例如，a like '3%'，但是，如果只有后面的百分号，没有前面的百分号，那么这种情况下是可以运用到字段上的索引的

一个 SQL 查询在运用到复合索引时，仅能借助其中某个字段执行区间检索。举例来说，若存在包含 a、b、c 字段的组合索引，一旦查询要求针对 a 字段进行区间筛选，那么 b、c 字段上的索引将无法发挥作用。

创建组合索引时，若 a 字段需要执行区间检索，则应将其置于索引的末端位置，通过运用 left join 或者 not 来改进 not in 的执行过程，由于 not in 语句往往会导致索引无法发挥作用。

数据库规划时，需要顾及未来可能增加的需求，程序访问各个数据库需采用不同的登录凭证，不允许在不同数据库间进行数据探查，查询指令中必须明确指定字段，不能使用通配符

原因：

7. 禁止使用不含字段列表的 语句

如：

向表中存入数据，第一列为a，第二列为b，第三列为c。

应使用：

向表中插入数据，c1列赋值为a，c2列赋值为b，c3列赋值为c。

8. 避免使用子查询，可以把子查询优化为 join 操作

子查询通常用在 in 子句里,而且子查询里面要是简单的 SQL,不能含有 union、group by、order by、limit 这些子句,这样才可以把它转变成关联查询来优化。

子查询性能差的原因：

子查询查询到的数据不能利用索引进行访问，一般这些数据会被放在临时数据表中，无论是内存中的还是磁盘上的临时表，它们都不具备索引功能，因此查询速度会变慢。当子查询返回大量数据时，对查询速度的拖累会更加明显。

子查询在执行时会创建许多临时的数据库表格，并且这些表格没有设置索引，因此会占用大量的中央处理器和输入输出资源，导致出现许多执行缓慢的查询操作。

9. 避免使用 JOIN 关联太多的表

MySQL 内部具备关联缓存机制，该缓存容量能够通过特定参数加以调整。

在 MySQL 环境，每增加一个 SQL 语句中的表连接，系统就会额外占用一份连接缓存资源，关联表数量提升，会使得内存消耗随之增加。

程序里要是频繁进行多表联合查询，并且配置参数不恰当，就很容易导致服务器内存耗尽，进而影响数据库运行状态的平稳性。

对于涉及多表联合的情况，系统会运用临时数据结构，从而对检索速度造成制约，MySQL系统在处理此类操作时，其数据库上限为61个数据表，但为了确保性能稳定，不建议超过5个表进行关联分析。

10. 减少同数据库的交互次数

数据库在执行大量任务时表现更佳，能够将许多相同的动作集中起来，完成后再分解，这样可以节省时间。

当针对同一列执行或运算时，可以采用 in 而非 or 来实现

in 的数值须在 500 以下，in 查询能更好地运用索引功能，or 条件多数情形下难以发挥索引优势。

不能运用 order by rand来达成随机排列的目的

随机排序会先将满足要求的记录全部加载进内存，接着在内存里依据随机数值对它们进行排列，有时每条记录都会生成一个随机数，当符合条件的数据量极大时，会耗费大量处理器及磁盘输入输出和内存资源。

推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

13. WHERE 从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引

不推荐：

创建时间的日期等于二零一九年一月一日

推荐：

创建时间在20190101或之后，并且创建时间< '20190102'

当确定不会出现数据重复的情况下，应选用 UNION ALL 而非 UNION进行连接查询，将结构繁复的巨量 SQL 语句分解为若干个小型 SQL 语句执行，这是数据库操作行为规范的要求，其中，超过百万行记录的批量写入操作，必须分阶段多次实施

大批量操作可能会造成严重的主从延迟

在主从架构下，进行大量数据操作时，主从节点间可能出现显著时间差，大量写入动作通常需要耗费较长时间，只有当主节点处理完毕后，从节点才能开始执行，因此主从节点间会形成持久性的时间差

日志为 row 格式时会产生大量的日志

大量写入操作会生成海量记录，尤其针对行格式二进制数据，这类数据会详尽记录每行信息的变更，因此单次改动涉及的数据越庞大，生成的记录就越多，导致记录的传输和回溯耗时增加，进而造成主从系统之间的时间差

避免产生大事务操作

处理海量数据更新，必定要采用事务机制，这样就会引发对数据表的广泛锁定，进而造成众多进程互相干扰，这种干扰会严重削弱 MySQL 的运行效率。

长时间拥堵会耗尽所有数据库的可供使用通道，这会导致生产环境里的其他软件无法接入数据库，所以必须注意大批量写入任务要分阶段执行

2. 对于大表使用 pt--- 修改表结构

对大表的数据结构进行改动，务必小心，因为它会引起严重的锁表状况，特别是在生产环境中，这种情况是不可接受的。

它将先创建一个与原表结构一致的新表，在新表上实施结构变更，然后将原表数据迁移至新表，同时向原表添加若干触发器，原表新增数据也需转存至新表，待所有数据迁移完毕后，将新表更名替换原表，并移除原表，将原本的单次DDL操作拆分为多个分步执行。

不能给程序账户设置管理员权限,程序访问数据库的账号,要按照最少权限要求来配置