数据库优化-数据库优化基本手段和数据库的设计

ernestwang 970 0

优化基本手段

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

数据库的设计

  • 存储引擎的选择
    • 根据需要选择合适的存储引擎,见文档01-数据库存储引擎.md
  • 数据表的设计
    • 说明:前人总结出来的数据库设计的规范称为范式,主要有三范式:
    • 第一范式:表的字段都是不可再分割的,称为满足第一范式,记为1NF
    优化前:用户ID 手机号 地址
    优化后:用户ID 手机号 省份 城市 详细地址
    • 第二范式:表的字段之间没有部分依赖,称为满足第二范式,记为2NF
    优化前:学号 姓名 年龄 课程号 课程名称 课程成绩
    优化后:
        学号 姓名 年龄
        课程号 课程名称
        学号 课程号 课程成绩
    • 第三范式:表的字段之间没有传递依赖,称为满足第三范式,记为3NF
    优化前:学号 姓名 年龄 所在院校 院校地址 院校电话
    优化后:
        学号 姓名 年龄 院校代号
        院校代号 院校名称 院校地址 院校电话
    • 总结:有时需要根据业务需要进程反范式设计,通过适当冗余来减少关联查询。
  • 使用字段索引
    • 根据需要添加合适的字段索引,见文档02-数据库索引优化.md
    • 优化SQL语句

      • 慢查询
        • 说明:慢查询时MySQL自带的功能,默认是关闭的,开启后会自动记录执行较慢的SQL语句
        • 使用:
        查看慢查询时间阀值:show variables like 'long_query_time';
            默认是10s,设置为0是会记录所有SQL语句,设置后换一个客户端才能看到。
            设置为0:set global long_query_time=0;
        查看慢查询开关状态: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';
        • 作用:定位耗时的SQL语句
      • explain
        • 说明:查询执行计划,可以看到SQL语句的执行情况。
        • 格式:explain SQL \G;
        • 示例:explain select * from star\G;
        • 选项说明:
        select_type:表示SELECT的类型,常见的取值有SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SELECT)等。
        table:输出结果集的表。
        partitions:匹配的分区(查询数据将访问的分区)
        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
            当Query中包含 order by 操作,而且无法利用索引完成的排序操作称为“文件排序”。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、内存、磁盘、带宽等

      进行架构调整

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

标签: 数据库

发布评论 0条评论)

还木有评论哦,快来抢沙发吧~

复制成功
微信号: irenyuwang
关注微信公众号,站长免费提供流量增长方案。
我知道了