数据库设计与优化-优快云博客

2019独角兽企业重金招聘Python工程师标准>>>

建表原则

选择合适数据类型

1.更小通常更好 varchar(5) vs varchar(200)

2.尽量避免NULL

3.尽量使用整型定义主键

4.建议用tinyint代替枚举（减少alter table操作，表锁，易引发线上case）

CHAR和VARCHAR

要根据存储引擎做出相应的选择：

（1） MyISAM存储引擎：建议使用固定长度的数据列

（2）MEMORY存储引擎：目前都使用固定长度的数据行存储，无论使用CHAR还是VARCHAR都作为CHAR类型处理

（3）InnoDB存储引擎：建议使用VARCHAR类型，主要的性能因素是使用数据行使用的存储容量。由于CAHR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储容量和磁盘IO是比较好的

由于CHAR固定长度，处理速度比VARCHAR快得多，缺点是浪费存储空间，程序需要对行尾空格进行处理（存储时会去除尾部空格），所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用CAHR类型来存储。

TEXT和BLOB

BLOB——>二进制数据

TEXT——>字符数据

（1）删除操作会在数据表中留下很大的空洞，以后填入这些空洞的记录在插入的性能上会有影响。为了提高性能，建议定期使用optimize table（optimize英文意为性能优化）功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。

（2）使用合成索引来提高大文本字段（BLOB或TEXT）的精确查询性能

（3）使用前缀索引（对这列的前几个字符建立索引而不是全部）对BLOB或者CLOB字段进行模糊查询

（4）在不必要的时候避免检索大型的BLOB或者TEXT值

（5）把BLOB或TEXT列分离到单独的表中

浮点数与定点数

浮点数——>float、double，存在误差问题，实际编程中浮点数的比较最好使用范围比较而不要使用==直接比较

定点数——>decimal（或numberic）字符串形式存放，更加精确地保存数据，如应用在货币等对精度敏感的数据

日期类型选择

根据实际需要选择能满足应用的最小存储的日期类型，不仅可以节约存储，更能够提高表的操作效率。

数据库存储引擎选择

其他

所有的表结构之间的关联关系，在应用层通过业务逻辑来保证，禁止使用外键约束。（原因：性能考虑）

查询优化

1.分页查询的时候，Mysql 的 limit 在页数比较大的时候效率比较低（limit offset, n 实现逻辑是扫描 offset+n 行记录，然后丢掉前 offset 行，返回最后的 n 行），故要么限制总页数，要么改写 SQL。

-- 1. 通过增加过滤条件改写
select *
from table
where limitKey > lastMaxLimitKey
limit n;
  
-- 2. 通过关联查询改写
select a.*
from table a, (select id from table where conditionCol = conditionValue limit offset, n) b
where a.id = b.id;

索引建立几大原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大，因为MySQL暂不支持函数索引。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可（索引加快查询性能 VS 维护索引的代价）

6.尽量利用覆盖索引来加速查询，如果覆盖索引无法满足，返回的数据列也尽量按照最小需求原则来实施

select colB, colC   
// 如果业务仅需要colB, colC这2列数据即可，则idx_colA_colB_colC能够覆盖；
如果是select *，则索引无法覆盖，还会增加一次根据索引查找数据行的查找
from table // 索引idx_colA_colB_colC
where colA = 1
and colB = 2
and colC like 'abc%';

7.业务上有惟一性要求的字段（即使是多个字段组合），也必须建立惟一索引。

惟一索引一是简化了mysql对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

B+树索引和哈希索引

1).如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
2).从示意图中也能看到，如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
3).同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
4).哈希索引也不支持多列联合索引的最左匹配规则；
5).B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。