MySQL 设计规范

数据库设计规范

命名规范

• 数据库名称必须使用小写字母并用下划线分割。
• 数据库名称禁止使用 MySQL 保留关键字(如果表名中包含关键字查询时，需要将其用单引号括起来)。
• 数据库名称的命名要能做到见名知意，并且不要超过 32 个字符。
• 临时库表必须以 tmp 为前缀并以日期为后缀，备份表必须以 bak 为前缀并以日期 (时间戳) 为后缀。
• 存储相同数据的列名和列类型必须一致(一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低)

基本设计规范

1. 表必须使用 InnoDB 存储引擎
   • 没有特殊要求(即 InnoDB 无法满足的功能如：列存储，存储空间数据等)的情况下;表必须使用 InnoDB 存储引擎;因为InnoDB 支持事务，支持行级锁，更好的恢复性，高并发下性能更好
2. 数据库和表的字符集统一使用 UTF8MB4
   • 兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效
3. 表和字段都需要添加注释
   • 使用 comment 从句添加表和列的备注 从一开始就进行数据字典的维护
4. 控制单表数据量的大小，建议控制在 500 万以内
   • 500 万并不是 MySQL 数据库的限制，过大会造成修改表结构、备份、恢复都会有很大的问题，可以用历史数据归档(应用于日志数据)，分库分表(应用于业务数据)等手段来控制数据量大小
5. 谨慎使用 MySQL 分区表
   • 分区表在物理上表现为多个文件，在逻辑上表现为一个表 谨慎选择分区键，跨分区查询效率可能更低 建议采用物理分表的方式管理大数据。
6. 尽量做到冷热数据分离，减小表的宽度
   • MySQL 限制每个表最多存储 4096 列，并且每一行数据的大小不能超过 65535 字节 减少磁盘 IO，保证热数据的内存缓存命中率(表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的 IO) 更有效的利用缓存，避免读入无用的冷数据 经常一起使用的列放到一个表中(避免更多的关联操作)
7. 禁止在表中建立预留字段
   • 预留字段很难做到见名识义,无法确认存储的数据类型，所以无法选择合适的类型 对预留字段类型的修改，会对表进行锁定
8. 禁止在数据库中存储图片，文件等大的二进制数据;
   • 应该设置独立的图片服务器和文件服务器进行存放，数据库只存储文件和图片的地址信息
9. 禁止在线上做数据库压力测试
10. 禁止从开发环境，测试环境直接连接生成环境数据库

数据库字段设计规范

1. 优先选择符合存储需要的最小的数据类型
   • 列的字段越大，建立索引时所需要的空间也就越大，在遍历时所需要的 IO 次数也就越多，索引的性能也就越差
   • 常用方式
     • 将字符串转换成数字类型存储
       IP 地址的存取
       inet_aton 把 ip 转为无符号整型 (4-8 位)
       inet_ntoa 把整型的 ip 转为地址 插入数据前，先用 inet_aton 把 IP 地址转为整型，可以节省空间。显示数据时，使用 inet_ntoa 把整型的 IP 地址转为地址显示即可。
     • 对于非负型的数据(如自增 ID、整型 IP)来说，优先使用无符号整型来存储，无符号相对于有符号可以多出一倍的存储空间
       SIGNED INT -21474836482147483647
       UNSIGNED INT 04294967295
       VARCHAR (N) 中的 N 代表的是字符数，而不是字节数。使用 UTF8 存储 255 个汉字 Varchar (255)=765 个字节。过大的长度会消耗更多的内存
2. 避免使用 TEXT、BLOB 数据类型
   • 把 BLOB 或是 TEXT 列分离到单独的扩展表中
   • MySQL 内存临时表不支持 TEXT、BLOB 这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行
   • 而且对于这种数据，MySQL 还是要进行二次查询，会使 SQL 性能变得很差 ; 如果一定要使用，建议把 BLOB 或是 TEXT 列分离到单独的扩展表中，查询时只需要取出必要的列，不需要 TEXT 列的数据时不要对该列进行查询
   • TEXT 或 BLOB 类型只能使用前缀索引 ; 因为 MySQL 对索引字段长度是有限制的，所以 TEXT 类型只能使用前缀索引，并且 TEXT 列上是不能有默认值的
3. 避免使用 ENUM 类型
   • 修改 ENUM 值需要使用 ALTER 语句
   • ENUM 类型的 ORDER BY 操作效率低，需要额外操作
   • 禁止使用数值作为 ENUM 的枚举值
4. 尽可能把所有列定义为 NOT NULL
   • 索引 NULL 列需要额外的空间来保存，所以要占用更多的空间
   • 进行比较和计算时要对 NULL 值做特别的处理
5. 使用 TIMESTAMP(4 个字节)或 DATETIME 类型(8 个字节)存储时间
   • TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07
   • TIMESTAMP 占用 4 字节和 INT 相同，但比 INT 可读性高，超出 TIMESTAMP 取值范围的使用 DATETIME 类型存储。
   • 经常会有人用字符串存储日期型的数据(不正确的做法)：
     • 缺点 1：无法用日期函数进行计算和比较
     • 缺点 2：用字符串存储日期要占用更多的空间
6. 同财务相关的金额类数据必须使用 decimal 类型
   • 非精准浮点：float，double
   • 精准浮点：decimal
   • Decimal 类型为精准浮点数，在计算时不会丢失精度。占用空间由定义的宽度决定，每 4 个字节可以存储 9 位数字，并且小数点要占用一个字节。可用于存储比 bigint 更大的整型数据。

索引设计规范

1. 限制每张表上的索引数量，建议单张表索引不超过 5 个

   • 索引并不是越多越好！索引可以提高效率同样也可以降低效率；索引可以增加查询效率，但同样也会降低插入和更新的效- 率，甚至有些情况下会降低查询效率。
   • 因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能

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

   • 5.6 版本之前，一个 SQL 只能使用到一个表中的一个索引，5.6 以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好

3. 每个 InnoDB 表必须有个主键

   • InnoDB 是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种; InnoDB 是按照主键索引的顺序来组织表的。
   • 不要使用更新频繁的列作为主键，不适用多列主键(相当于联合索引) 不要使用 UUID、MD5、HASH、字符串列作为主键(无法保证数据的顺序增长)。主键建议使用自增 ID 值。

常见索引列建议

1. 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
2. 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段。
   并不要将符合 1 和 2 中的字段的列都建立一个索引，通常将 1、2 中的字段建立联合索引效果更好。
   多表 JOIN 的关联列

如何选择索引列的顺序

1. 建立索引的目的是：

• 希望通过索引进行数据查找，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。
• 区分度最高的放在联合索引的最左侧(区分度 = 列中不同值的数量 / 列的总行数)。
• 尽量把字段长度小的列放在联合索引的最左侧(因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好)。
• 使用最频繁的列放到联合索引的左侧(这样可以比较少的建立一些索引)。

2. 避免建立冗余索引和重复索引

• 因为这样会增加查询优化器生成执行计划的时间。
• 重复索引示例：primary key (id)、index (id)、unique index (id)
• 冗余索引示例：index (a,b,c)、index (a,b)、index (a)

3. 优先考虑覆盖索引

• 对于频繁的查询优先考虑使用覆盖索引。
• 覆盖索引
• 就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引
• 覆盖索引的好处：
  • 避免 InnoDB 表进行索引的二次查询
  • InnoDB 是以聚集索引的顺序来存储的，对于 InnoDB 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了 IO 操作，提升了查询效率。
  • 可以把随机 IO 变成顺序 IO 加快查询效率
  • 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。

4. 索引 SET 规范
   • 尽量避免使用外键约束。
   • 不建议使用外键约束(foreign key)，但一定要在表与表之间的关联键上建立索引。
   • 外键可用于保证数据的参照完整性，但建议在业务端实现。
   • 外键会影响父表和子表的写操作从而降低性能。

数据库 SQL 开发规范

1. 建议使用预编译语句进行数据库操作

   • 预编译语句可以重复使用这些计划，减少 SQL 编译所需要的时间，还可以解决动态 SQL 所带来的 SQL 注入的问题 只传参数，比传递 SQL 语句更高效 相同语句可以一次解析，多次使用，提高处理效率。

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

   • 隐式转换会导致索引失效。如：select name,phone from customer where id = '111';

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

4. 避免使用双 % 号的查询条件。

   • 如 a like ‘%123%’，(如果无前置 %，只有后置 %，是可以用到列上的索引的)

5. 一个 SQL 只能利用到复合索引中的一列进行范围查询

   • 如：有 a,b,c 列的联合索引，在查询条件中有 a 列的范围查询，则在 b,c 列上的索引将不会被用到，在定义联合索引时，如果 a 列要用到范围查找的话，就要把 a 列放到联合索引的右侧。

6. 使用 left join 或 not exists 来优化 not in 操作， 因为 not in 也通常会使用索引失效。

7. 数据库设计时，应该要对以后扩展进行考虑

8. 程序连接不同的数据库使用不同的账号，禁止跨库查询

   • 为数据库迁移和分库分表留出余地
   • 降低业务耦合度
   • 避免权限过大而产生的安全风险

9. 禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

   • 消耗更多的 CPU 和 IO 以网络带宽资源
   • 无法使用覆盖索引
   • 可减少表结构变更带来的影响

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

    • 如：insert into values ('a','b','c');
    • 应使用：insert into t(c1,c2,c3) values ('a','b','c');

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

    • 通常子查询在 in 子句中，且子查询中为简单 SQL (不包含 union、group by、order by、limit 从句) 时，才可以把子查询转化为关联查询进行优化。
    • 子查询性能差的原因：
    • 子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
    • 特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
    • 由于子查询会产生大量的临时表也没有索引，所以会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。

12. 避免使用 JOIN 关联太多的表
    对于 MySQL 来说，是存在关联缓存的，缓存的大小可以由 join_buffer_size 参数进行设置。
    在 MySQL 中，对于同一个 SQL 多关联(join)一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大。
    如果程序中大量的使用了多表关联的操作，同时 join_buffer_size 设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。
    同时对于关联操作来说，会产生临时表操作，影响查询效率 MySQL 最多允许关联 61 个表，建议不超过 5 个。

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

    • 数据库更适合处理批量操作 合并多个相同的操作到一起，可以提高处理效率

14. 对应同一列进行 or 判断时，使用 in 代替 or

    • In 的值不要超过 500 个， in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引。

15. 禁止使用 order by rand () 进行随机排序

    • 会把表中所有符合条件的数据装载到内存中，然后在内存中对所有数据根据随机生成的值进行排序，并且可能会对每一行都生成一个随机值，如果满足条件的数据集非常大，就会消耗大量的 CPU 和 IO 及内存资源。
    • 推荐在程序中获取一个随机值，然后从数据库中获取数据的方式。

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

    • 对列进行函数转换或计算时会导致无法使用索引。
      • 不推荐where date(create_time)='20190101'
      • 推荐where create_time >= '20190101' and create_time < '20190102'

17. 在明显不会有重复值时使用 UNION ALL 而不是 UNION

    • UNION 会把两个结果集的所有数据放到临时表中后再进行去重操作。
    • UNION ALL 不会再对结果集进行去重操作。

18. 拆分复杂的大 SQL 为多个小 SQL

    • 大 SQL：逻辑上比较复杂，需要占用大量 CPU 进行计算的 SQL 。
    • MySQL：一个 SQL 只能使用一个 CPU 进行计算。
    • SQL 拆分后可以通过并行执行来提高处理效率。

数据库操作行为规范

1. 超 100 万行的批量写(UPDATE、DELETE、INSERT)操作，要分批多次进行操作
   大批量操作可能会造成严重的主从延迟
   主从环境中，大批量操作可能会造成严重的主从延迟，大批量的写操作一般都需要执行一定长的时间，而只有当主库上执行完成后，才会在其他从库上执行，所以会造成主库与从库长时间的延迟情况
   Binlog 日志为 row 格式时会产生大量的日志
   大批量写操作会产生大量日志，特别是对于 row 格式二进制数据而言，由于在 row 格式中会记录每一行数据的修改，我们一次修改的数据越多，产生的日志量也就会越多，日志的传输和恢复所需要的时间也就越长，这也是造成主从延迟的一个原因。

避免产生大事务操作
大批量修改数据，一定是在一个事务中进行的，这就会造成表中大批量数据进行锁定，从而导致大量的阻塞，阻塞会对 MySQL 的性能产生非常大的影响。
特别是长时间的阻塞会占满所有数据库的可用连接，这会使生产环境中的其他应用无法连接到数据库，因此一定要注意大批量写操作要进行分批。

2. 对于大表使用 pt-online-schema-change 修改表结构
   避免大表修改产生的主从延迟
   避免在对表字段进行修改时进行锁表
   对大表数据结构的修改一定要谨慎，会造成严重的锁表操作，尤其是生产环境，是不能容忍的。
   pt-online-schema-change 它会首先建立一个与原表结构相同的新表，并且在新表上进行表结构的修改，然后再把原表中的数据复制到新表中，并在原表中增加一些触发器。
   把原表中新增的数据也复制到新表中，在行所有数据复制完成之后，把新表命名成原表，并把原来的表删除掉，把原来一个 DDL 操作，分解成多个小的批次进行。

3. 禁止为程序使用的账号赋予 super 权限

   • 当达到最大连接数限制时，还运行 1 个 有 super 权限的用户连接 super 权限只能留给 DBA 处理问题的账号使用。

4. 对于程序连接数据库账号，遵循权限最小原则

   • 程序使用数据库账号只能在一个 DB 下使用，不准跨库 程序使用的账号原则上不准有 drop 权限。

SQL优化

1. 避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

2. 避免在 where 子句中对字段进行 null 值判断

3. 避免在 where 子句中使用 != 或 <> 操作符。MySQL 只有对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的 like

4. 避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用 union 合并查询:select id from t where num=10 union all select id from t where num=20

5. in 和 not in 也要慎用，否则会导致全表扫描。对于连续的数值，能用 between 就不要用 in:select id from t where num between 1 and 3

6. 模糊查询的使用区别：

   • select id from t where name like '%abc%' 或select id from t where name like '%abc'会导致全表扫描
   • 而select id from t where name like 'abc%'才用到索引

7. 在 where 子句中使用参数，也会导致全表扫描

8. 避免在 where 子句中对字段进行表达式操作，避免在 where 子句中对字段进行函数操作

9. 很多时候用 exists 代替 in 是一个好的选择:select num from a where num in(select num from b)

   • 使用:select num from a where exists(select 1 from b where num=a.num)替换

10. 索引提高相应的 select 的效率，同时也降低了 insert 及 update 的效率;因为 insert 或 update 时有可能会重建索引 ; 所以一个表的索引数最好不要超过 6 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要

11. 应尽可能的避免更新 clustered(聚合) 索引数据列， 因为 clustered(聚合) 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引

12. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销

13. 尽可能的使用 varchar, nvarchar 代替 char, nchar。因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些

14. 不要使用返回所有：select * from t，用具体的字段列表代替 “*”，不要返回用不到的任何字段

15. 避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理

16. 使用表的别名(Alias)：

    • 当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个 Column 上。这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误

17. 使用“临时表”暂存中间结果

    • 简化 SQL 语句的重要方法就是采用临时表暂存中间结果。但是临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在 tempdb 中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能

18. SQL 查询语句应加上 nolock，读、写是会相互阻塞的，为了提高并发性能。对于一些查询，可以加上 nolock，这样读的时候可以允许写，但缺点是可能读到未提交的脏数据

    • 使用 nolock 原则：
      • 查询的结果用于“插、删、改”的不能加 nolock；
      • 查询的表属于频繁发生页分裂的，慎用 nolock ；
      • 使用临时表一样可以保存“数据前影”，起到类似 Oracle 的 undo 表空间的功能，能采用临时表提高并发性能的，不要用 nolock

19. 常见的简化规则

    • 不要有超过 5 个以上的表连接(join)，考虑使用临时表或表变量存放中间结果
    • 少用子查询，视图嵌套不要过深，一般视图嵌套不要超过 2 个为宜

20. 将需要查询的结果预先计算好放在表中，查询的时候再select ; 这在SQL7.0以前是最重要的手段，例如医院的住院费计算

21. 用 or 的字句可以分解成多个查询，并且通过 union 连接多个查询。他们的速度只同是否使用索引有关，如果查询需要用到联合索引，用 union all 执行的效率更高。多个 or 的字句没有用到索引，改写成 union 的形式再试图与索引匹配。一个关键的问题是否用到索引

22. 在 in 后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数

23. 将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程

    • 存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的 sql 语句，是控制流语言的集合，速度当然快。反复执行的动态 SQL，可以使用临时存储过程，该过程(临时表)被放在 Tempdb 中。

24. 当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用配制线程数量< 最大连接数，启用 sql server 的线程池来解决，如果还是数量 = 最大连接数+5，严重的损害服务器的性能

25. 查询的关联同写的顺序 :

    • select a.personMemberID, * from chineseresume a,personmember b where personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' (A = B, B = '号码') select a.personMemberID, * from chineseresume a,personmember b where a.personMemberID = b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid = 'JCNPRH39681' (A = B, B = '号码', A = '号码') select a.personMemberID, * from chineseresume a,personmember b where b.referenceid = 'JCNPRH39681' and a.personMemberID = 'JCNPRH39681' (B = '号码', A = '号码')

26. 使用 exists 代替 select count(1) 来判断是否存在记录

27. 尽量使用 “>=”，不要使用 “>”

28. 索引的使用规范

    • 索引的创建要与应用结合考虑，建议大的 oltp 表不超过 6 个索引
    • 尽可能的使用索引字段作为查询条件，尤其是聚合索引，必要时可以通过 index index_name 来强制指定索引;避免对大表查询时进行 table scan，必要时考虑新建索引;
    • 在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用;
    • 要注意索引的维护，周期性重建索引，重新编译存储过程

29. 当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新

30. 在所有的存储过程中，能够用 SQL 语句的，绝不用循环去实现

31. 提高 group by 语句的效率，可以通过将不需要的记录在 group by 之前过滤掉。下面两个查询返回相同结果，但第二个快很多

    • 低效:SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER'
    • 高效:SELECT JOB, AVG(SAL) FROM EMPWHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP BY JOB

32. SQL语句的书写规范

    • 所有表名，列名全部小写
    • 关键字，内置函数名全部大写

33. 别名的使用，别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快 1.5 倍

34. 避免死锁，在你的存储过程和触发器中访问同一个表时总是以相同的顺序；事务应经可能地缩短，在一个事务中应尽可能减少涉及到的数据量；永远不要在事务中等待用户输入

35. 避免使用临时表，除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替。大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在 TempDb 数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢

36. 最好不要使用触发器

    • 触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程;如果能够使用约束实现的，尽量不要使用触发器;
    • 不要为不同的触发事件(Insert、Update 和 Delete)使用相同的触发器;不要在触发器中使用事务型代码

37. 索引创建规则
    表的主键、外键必须有索引；
    数据量超过 300 的表应该有索引；
    经常与其他表进行连接的表，在连接字段上应该建立索引；
    经常出现在 WHERE 子句中的字段，特别是大表的字段，应该建立索引；
    索引应该建在选择性高的字段上；
    索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
    复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替；
    正确选择复合索引中的主列字段，一般是选择性较好的字段；
    复合索引的几个字段是否经常同时以 AND 方式出现在 WHERE 子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
    如果复合索引中包含的字段经常单独出现在 WHERE 子句中，则分解为多个单字段索引；
    如果复合索引所包含的字段超过 3 个，那么仔细考虑其必要性，考虑减少复合的字段；
    如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
    频繁进行数据操作的表，不要建立太多的索引；
    删除无用的索引，避免对执行计划造成负面影响；
    表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
    尽量不要对数据库中某个含有大量重复的值的字段建立索引

38. MySQL 查询优化总结
    使用慢查询日志去发现慢查询，使用执行计划去判断查询是否正常运行，总是去测试你的查询看看是否他们运行在最佳状态下。

久而久之性能总会变化，避免在整个表上使用 count(*)，它可能锁住整张表，使查询保持一致以便后续相似的查询可以使用查询缓存，在适当的情形下使用 GROUP BY 而不是 DISTINCT，在 WHERE、GROUP BY 和 ORDER BY 子句中使用有索引的列，保持索引简单，不在多个索引中包含同一个列。

有时候 MySQL 会使用错误的索引，对于这种情况使用 USE INDEX，检查使用 SQL_MODE=STRICT 的问题，对于记录数小于5的索引字段，在 UNION 的时候使用LIMIT不是是用OR。

为了避免在更新前 SELECT，使用 INSERT ON DUPLICATE KEY 或者 INSERT IGNORE；不要用 UPDATE 去实现，不要使用 MAX；使用索引字段和 ORDER BY子句 LIMIT M，N 实际上可以减缓查询在某些情况下，有节制地使用，在 WHERE 子句中使用 UNION 代替子查询，在重新启动的 MySQL，记得来温暖你的数据库，以确保数据在内存和查询速度快，考虑持久连接，而不是多个连接，以减少开销。

基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询，当负载增加在服务器上，使用 SHOW PROCESSLIST 查看慢的和有问题的查询，在开发环境中产生的镜像数据中测试的所有可疑的查询

39. MySQL 备份过程
    从二级复制服务器上进行备份；
    在进行备份期间停止复制，以避免在数据依赖和外键约束上出现不一致；
    彻底停止 MySQL，从数据库文件进行备份；
    如果使用 MySQL dump 进行备份，请同时备份二进制日志文件 – 确保复制没有中断；
    不要信任 LVM 快照，这很可能产生数据不一致，将来会给你带来麻烦；
    为了更容易进行单表恢复，以表为单位导出数据——如果数据是与其他表隔离的。
    当使用 mysqldump 时请使用 –opt；
    在备份之前检查和优化表；
    为了更快的进行导入，在导入时临时禁用外键约束。；
    为了更快的进行导入，在导入时临时禁用唯一性检测；
    在每一次备份后计算数据库，表以及索引的尺寸，以便更够监控数据尺寸的增长；
    通过自动调度脚本监控复制实例的错误和延迟；
    定期执行备份。
40. 查询缓冲并不自动处理空格，因此，在写 SQL 语句时，应尽量减少空格的使用，尤其是在 SQL 首和尾的空格(因为查询缓冲并不自动截取首尾空格)
41. 我们应该为数据库里的每张表都设置一个 ID 做为其主键，而且最好的是一个 INT 型的(推荐使用 UNSIGNED)，并设置上自动增加的 AUTO_INCREMENT 标志
42. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON，在结束时设置 SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息
43. MySQL 查询可以启用高速查询缓存。这是提高数据库性能的有效MySQL优化方法之一。当同一个查询被执行多次时，从缓存中提取数据和直接从数据库中返回数据快很多
44. EXPLAIN SELECT 查询用来跟踪查看效果
    • 使用 EXPLAIN 关键字可以让你知道 MySQL 是如何处理你的 SQL 语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的，你的数据表是如何被搜索和排序的
45. 当只要一行数据时使用 LIMIT 1
    • 当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。
    • 在这种情况下，加上 LIMIT 1 可以增加性能。这样一来，MySQL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据
46. 选择表合适存储引擎
    myisam：应用时以读和插入操作为主，只有少量的更新和删除，并且对事务的完整性，并发性要求不是很高的。
    InnoDB：事务处理，以及并发条件下要求数据的一致性。除了插入和查询外，包括很多的更新和删除。（InnoDB 有效地降低删除和更新导致的锁定）。
    对于支持事务的 InnoDB类 型的表来说，影响速度的主要原因是 AUTOCOMMIT 默认设置是打开的，而且程序没有显式调用 BEGIN 开始事务，导致每插入一条都自动提交，严重影响了速度。可以在执行 SQL 前调用 begin，多条 SQL 形成一个事物（即使 autocommit 打开也可以），将大大提高性能
47. 优化表的数据类型，选择合适的数据类型
    原则：更小通常更好，简单就好，所有字段都得有默认值，尽量避免 NULL。

例如：数据库表设计时候更小的占磁盘空间尽可能使用更小的整数类型。(mediumint 就比 int 更合适) 

比如时间字段：datetime 和 timestamp。datetime 占用8个字节，timestamp 占用4个字节，只用了一半。而 timestamp 表示的范围是 1970—2037 适合做更新时间。

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。

因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如：在定义邮政编码这个字段时，如果将其设置为 CHAR(255)，显然给数据库增加了不必要的空间。甚至使用VARCHAR 这种类型也是多余的，因为 CHAR(6) 就可以很好的完成任务了。

同样的，如果可以的话，我们应该使用 MEDIUMINT 而不是 BIGIN 来定义整型字段，应该尽量把字段设置为 NOT NULL，这样在将来执行查询的时候，数据库不用去比较 NULL 值。

对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为 ENUM 类型。因为在 MySQL 中，ENUM 类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能

51. 字符串数据类型：char, varchar, text 选择区别。

52. 任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边

53. count(1) 和 count(*) 查询所有数据;count(1)效率更高