数据库优化策略小结

  • 数据类型的优化
    • MySQL数据类型
    • 优化策略
  • 索引优化
    • 索引类型
    • 高性能索引策略
  • 查询优化
    • 优化数据访问
    • 从好到坏的where条件应用
    • 重构查询

 

一、数据类型的优化

（1）MySQL数据类型

  • 整数类型：
    • TinyInt，存储空间8， 字节长度1；
    • SmallInt， 存储空间16， 字节长度2；
    • MediumInt，存储空间24， 字节长度3；
    • Int，存储空间32， 字节长度4；
    • BigInt，存储空间64， 字节长度8；
  • 实数类型：
    • Float：字节长度8，单精度浮点数；
    • Double：字节长度16，双精度浮点数；
    • Decimal：未打包的浮点数，计算中会转化为Double；Decimal 相比于 Float 和 Double 需要额外的空间和计算开销，因此尽量只在对小数进行精确计算时才使用，例如存储财务数据。在数据量比较大的时候，可以考虑使用 BigInt 代替 Decimal，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。 　
  • 字符串类型：
    • VarChar：存储可变长字符串，需要一个或两个额外字节记录字符串长度。适用于：字符串列的最大长度比平均长度大很多；列的更新很少（所以碎片不是问题）；使用了UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
    • Char：定长，根据定义的字符串长度分配足够的空间。Char 适合存储很短的字符串，或者所有值都接近同一个长度。如：存储密码的MD5值（这是一个定长的值）；经常变更的数据（定长的 Char 类型不容易产生碎片）；非常短的列，比如用 char(1) 来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是 varchar(1) 却需要两个字节（还有一个记录长度的额外字节）。
  • 时间类型
    • DATETIME：使用8字节存储空间，将日期和时间装到格式为YYYYMMDDHHMMSS的整数中；
    • TIMESTAMP：使用4字节存储空间，显示的值依赖于时区。尽量使用它，因为它的空间效率更好。

（2）优化策略

  • 更小的通常更好
    • 更小的通常更快，因为它占用更小的磁盘、内存和cpu缓存，且处理时需要的cpu周期更小；
    • 但是要确保没有低估需要存储的值的范围。
  • 简单就好
    • 简单的数据类型操作需要更少的cpu处理周期；
    • 如：整型比字符串代价更低、MySQL内建类型（date，time，datetime）而非字符串来存储时间、用整型存储IP地址。
  • 尽量避免使用NULL
    • 通常最好指定列为NOT NULL，除非真的需要存储NULL值；
    • 因为如果查询中包含可为 NULL 的列，对 MySQL 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂；可为 NULL 的列会使用更多的存储空间，在MySQL里也需要特殊处理；当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节。

二、索引优化

（1）索引类型

  • B-Tree索引：
    • 通常意味着所有值按顺序存储，并且每一个叶子叶到根的距离相等；
    • 能加快访问速度，因为存储引擎不需要全表扫描来捕获需要的数据，而是从索引的根节点开始进行搜索；
    • 对索引顺序存储，所以很适合查找范围数据
  • B-Tree索引有效的查询类型：
    • 全值匹配：和索引中所有列进行匹配；
    • 匹配最左前缀：只使用索引第n列匹配；
    • 匹配列前缀：只匹配某一列值的开头部分；
    • 匹配范围值：某一列在xx和xxx之间的值；
    • 精确匹配某一列，范围匹配另一列：某一列全匹配，另一列范围匹配；
    • 只访问索引的查询：只访问索引，不访问数据行。
  • B-Tree索引的限制：
    • 如果不是按照索引的最左列查找，则无法使用索引；
    • 不能跳过索引中间的列；
    • 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找；（如果范围查询有限，建议使用多个等于代替范围查询）。
  • 哈希索引：
    • 基于哈希表实现，只有精确匹配索引所有列的查询才有效；
    • 对于每一行数据，存储引擎都会对所有索引列计算一个哈希码，哈希索引将所有哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针；
  • 哈希索引的限制：
    • 只包含哈希值和行指针，不存储字段，所以不能使用索引中的值避免读取行；
    • 不是按索引值顺序存储，所以不能排序；
    • 不支持部分索引列匹配查找；
    • 访问哈希索引的速度非常快，除非出现很多哈希冲突，出现很多哈希冲突的话，一些索引维护的代价非常大；
  • 哈希索引的应用：
    • InnoDB“自适应哈希索引”：某些索引值被引用的非常频繁，他会在内存中基于B-Tree索引的基础上创建一个哈希索引。

（2）高性能索引策略：

  • 索引的优点：
    • 索引可以大大减少数据库表的扫描量
    • 索引可以帮助服务器避免排序和临时表
    • 索引可以将随机I/O变成顺序I/O

  • 索引选择：

    • 前缀索引：使得索引更小，更快（比如要索引很长的字符串），但是无法做GROUP BY和ORDER BY操作，也无法覆盖扫描；
    • 索引列顺序：经验法则是将选择性最高的放在最前面；

  • 聚簇索引：实际上是一种数据的存储方式

    • 数据航存放在索引的叶子结点，且数据行和相邻的键值紧凑地存放在一起
    • 优点：
      • 可以将相关数据保存在一起，减少磁盘I/O
      • 索引和数据保存在一个B-Tree，数据访问更快
      • 使用覆盖索引的扫描时可以直接使用主键
    • 缺点：
      • 插入速度依赖于插入顺序，最好是按照主键顺序插入
      • 更新列代价很高
      • 插入行可能导致页分裂

三、查询优化

（1）优化数据访问

  • 避免查询不需要的记录：使用limit；
  • 避免多表查询查询所有列：不要随意使用select * ；
  • 重复查询相同数据：采用缓存；

（2）从好到坏的where条件应用

  • 最佳：存储引擎层在索引中使用where过滤不匹配的记录
  • 次佳：使用索引覆盖扫描，直接从索引中过滤不需要的记录并返回，在服务器层完成
  • 最次：先从数据表中返回数据，然后过滤，需要回表查询

（3）重构查询

  • 一个复杂查询改为多个简单查询
  • 切分查询：
    • 对大查询“分而治之”，减少锁持有的时间
    • 例如删除过期记录，每次LIMIT 10000，否则可能一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小但是重要的查询；
  • 分解关联查询：
    • 让缓存效率更高；
    • 减少锁的竞争；
    • 应用层关联便于表的拆分，更容易做到高性能和可扩展；
    • 减少冗余记录查询；

 

要解决数据量大的问题，是避不开数据库优化的，下面就来介绍一些常见的数据库优化策略

 1，表结构优化

     表结构优化是数据库优化中最重要的，需要结合实际情况来看怎么设计更加的优化合理

2，sql语句优化

    *sql语法优化，写出更加便捷的sql语句

     *处理逻辑优化，如配合索引和缓存的使用

      一个常见的做法是，将涉及到大数据量的sql语句记录下来，观察日志，有侧重点的优化

3，分库分表

    分区是指将一张表的数据按照一定的规则分到不同的区来保存。若一张表中有几种类型，可以考虑分表

    举一个例子，分区按照月份来分，将不同类型的字段分表，这么做的好处就是增删改查数据的时候范围已经大大缩小了

4，索引优化

  索引的原理是在进行增删改的时候就预先按照指定的字段顺序排列后保存了，在查找的时候就可以从索引找到对应的指针找到数据

  优点：查询效率很高 缺点:每次增删改要更新索引，降低增删改的速度

5，分离活跃数据

   将活跃数据单独存放起来

   比如登录网站，可以将活跃度高的用户单独存放（依据最近登录时间或者单位时间内登录次数等），查询时先从活跃数据查找，没有再去不活跃处查找

6，读写分离

   读写分离的本质是对数据库进行集群，在高并发的情况下降低单台服务器的压力。

  一般将写的服务器叫主服务器，写入后同步到读服务器（即从服务器），并将读请求分配到多个服务器上

参考文献：《高性能MySQL》

posted @ 2018-10-17 14:40  kaleidoscopic  阅读(1422)  评论(0)  编辑  收藏  举报