数据库优化

数据库优化

优化基本手段

• 说明：计算机性能的瓶颈通常在于磁盘的IO
• 手段：
  • 进行架构调整
  • 进行硬件提升
  • 减少磁盘的IO
  • 优化SQL语句
  • 数据库的设计

数据库的设计

• 存储引擎的选择

  • 根据需要选择合适的存储引擎，见文档01-数据库存储引擎.md

• 数据表的设计

  • 说明：前人总结出来的数据库设计的规范称为范式，主要有三范式。
  • 第一范式：表的所有字段都是不可再分割的，称为满足第一范式，记为1NF

  优化前：
  用户ID 用户名 手机号 地址
  优化后：
  用户ID 用户名 手机号 省份 城市 详细地址
  

  • 第二范式：表的字段之间没有部分依赖时，称为满足第二范式，记为2NF

  优化前：
  学号 姓名 年龄 课程号 课程名称 课程分数
  优化后：
  学号 姓名 年龄
  课程号 课程名称
  学号 课程号 课程分数
  

  • 第三范式：表的字段之间没有传递依赖时，称为满足第三范式，记为3NF

  优化前：
  学号 姓名 年龄 性别 所在院校 院校地址 院校电话
  优化后：
  学号 姓名 年龄 性别 院校代号
  院校代号 院校名称 院校地址 院校电话
  

  • 总结：有时我们会根据业务的需要进行反范式设计，通过适当的冗余以减少关联查询。

• 使用字段索引

  • 根据需要添加合适的字段索引，见文档02-数据库索引优化.md

优化SQL语句

• 慢查询

  • 说明：慢查询时MySQL自带的功能，默认时关闭，开启后会记录执行较慢的SQL语句
  • 使用：

  查看慢查询时间：show variables like 'long_query_time'
  	默认是10s，设置为0表示记录所有SQL语句（设置后在新的客户端才能看到)
  查看慢查询状态：show variables like 'slow_query%'
  	slow_query_log		是否开启慢查询，默认OFF表示关闭，ON表示开启
  	slow_query_log_file	执行较慢的SQL语句保存的文件
  开启慢查询：set global slow_query_log=ON
  是否记录没有使用索引的语句：show variables like 'log_queries_not_using_indexes'
  	默认为OFF表示关闭，OF表示开启
  

  • 作用：定位耗时的SQL语句。

• explain

  • 说明：查询执行计划，可以看到语句的执行情况
  • 示例：explain select * from star where id > 3\G;
  • 选项说明：

  select_type：表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。
  table：输出结果集的表。
  type:ALL代表全表扫描,通常是不好的，其他的如index/range/const/ref/system则是较好的
      type=ALL，全表扫描，MySQL遍历全表来找到匹配的行
      type=index，索引全扫描，MySQL遍历整个索引来查询匹配的行
      type=range，索引范围扫描，常见于<、<=、>、>=、between等操作符
      type=ref，使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行
      type=eq_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是多表连接中使用 primary key或者 unique index作为关联条件。
      type=const/system，单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key或者唯一索引 unique index进行的查询。
      type=NULL，MySQL不用访问表或者索引，直接就能够得到结果
  possible_keys:可能被用到的索引
  key:查询过程中实际用到的索引，当为null时表示没有使用索引，通常不好
  key_len:索引字段最大可能使用的长度，也叫作索引基数，索引基数越大，
  	表明可能查询的行数越多，效率越低
  rows:mysql估计的需要扫描的行数，行数越多，效率越低
  这一列只有在EXPLAIN EXTENDED语句中才会出现。
  filtered:返回结果的行占需要读到的行(rows列的值)的百分比
  extra:显示上述信息之外的其它信息，其主要有以下返回结果
      Usingindex
      表明此查询使用了覆盖索引(CoveringIndex)，即通过索引就能返回结果，无需访问表。
      若没显示"Usingindex"表示读取了表数据。
      Using index condition
      可能会使用索引，
      Using index就是一定使用索引，这种索引成为覆盖索引，Using index condition则是在必要的时候才使用索引
      Using where
      表示 MySQL 服务器先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。效率较慢。
      Using filesort
      表示Mysql会按查询所需的顺序对结果进行排序，这时就会出现 Using filesort 。排序自然会增加查询时间，导致效率变慢。
      解决方法是利用索引进行排序。若查询所需的排序与使用的索引的排序一致，因为索引是已排序的，因此按索引的顺序读取结果返回，此时就不会出现Using filesort。
  

• 优化示例：

  1.尽量不要使用 select *，需要什么字段查询什么字段
  2.需要多少数据取多少数据，不要多取一条，使用limit 1限制一条
  3.尽量避免复杂的join或子查询
  4.禁止结果集自动排序：分组后的结果集会自动排序order by null可以阻止其自动排序
  	explain select province from star group by province order by null\G;
  5.分页时的limit优化
  	select * from star order by id limit 9999, 10;
  	这种方式几乎相当于进行全表扫描，可以记录上次查询的最大id为max_id
  	select * from star where id>max_id order by id limit 10;
  6.使用连接代替子查询
  	子查询：select username from user where gid in (select id from goods);
      连接：select username from user left join goods on user.gid=goods.id;
      说明：左连接效率最高
  

减少磁盘的IO

• 说明：简单理解就是使用缓存技术代替或减少数据库的读写

• 原理：

  请求到来，先检查缓存中是否有相关数据，有缓存数据直接返回。
  若没有缓存数据，则从数据库中读取，然后保存到缓存中再返回数据。
  

进行硬件提升

• 说明：当资源的使用效率足够高，依然不能满足需求，可以提升硬件配置
• 配置：CPU、磁盘、内存、带宽等

进行架构调整

• 单台服务器架构：见图片单台服务器结构.jpg
• 数据库读写分离：见图片数据库读写分离.jpg
• 高可用负载均衡：见图片高可用负载均衡.jpg