数据库基础(3)

一 MYSQL表与字段设计

1 数据库基本设计规范

（1）尽量控制单表数据量的大小，建议控制在 500 万以。500 万并不是 MySQL 数据库的限制，过大会造成修改表结构、备份、恢复都会有很大的问题，可以用历史数据归档（应用于日志数据），分库分表（应用于业务数据）等手段来控制数据量大小

（2）谨慎使用 MySQL 分区表。分区表在物理上表现为多个文件，在逻辑上表现为一个表 谨慎选择分区键，跨分区查询效率可能更低 建议采用物理分表的方式管理大数据。

（3）禁止在数据库中存储图片，文件等大的二进制数据。通常文件很大，会短时间内造成数据量快速增长，数据库进行数据库读取时，通常会进行大量的随机 IO 操作，文件很大时，IO 操作很耗时 通常存储于文件服务器，数据库只存储文件地址信息

（4）禁止在线上做数据库压力测试

2 数据库字段设计规范

（1）优先选择符合存储需要的最小的数据类型。列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少也越少，在遍历时所需要的 IO 次数也就越多， 索引的性能也就越差

（2）避免使用 TEXT、BLOB 数据类型，最常见的 TEXT 类型可以存储 64k 的数据

（3）尽可能把所有列定义为 NOT NULL

3 索引设计规范

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

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

（3）每个 InnoDB 表必须有个主键

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

4 数据库 SQL 开发规范

（1）充分利用表上已经存在的索引,避免使用双 % 号的查询条件。如 a like '%123%'，（如果无前置 %，只有后置 %，是可以用到列上的索引的）
一个 SQL 只能利用到复合索引中的一列进行范围查询，如：有 a,b,c 列的联合索引，在查询条件中有 a 列的范围查询，则在 b,c 列上的索引将不会被用到，在定义联合索引时，如果 a 列要用到范围查找的话，就要把 a 列放到联合索引的右侧。
使用 left join 或 not exists 来优化 not in 操作， 因为 not in 也通常会使用索引失效。

（2）禁止使用 SELECT * 必须使用 SELECT <字段列表> 查询

（3）避免使用子查询，可以把子查询优化为 JOIN 操作

（4）避免使用 JOIN 关联太多的表

二 order by (排序)

MySQL有两种方式可以实现ORDER BY：

1 通过索引扫描生成有序的结果

 2 使用文件排序(filesort)

mysql就只能先扫表筛选出符合条件的数据，再将筛选结果根据字段进行排序.

order by 优化

 SQL语句中，WHERE子句和ORDER BY子句都可以使用索引，一条SQL里，对于一张表的查询 一次只能使用一个索引，也就是说，一个既有WHERE又有ORDER BY的SQL中，使用索引有三个可能的场景：

1 只用于WHERE子句 筛选出满足条件的数据

2 只用于ORDER BY子句 返回排序后的结果 

 3 既用于WHERE又用于ORDER BY

筛选出满足条件的数据并返回排序后的结果，因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。

三 数据库优化

1 sql执行过程