精华内容
下载资源
问答
  • 2021-12-22 09:10:19

    mysql 优化步骤

    正如上图所示,数据库优化可以从架构优化,硬件优化,DB优化,SQL优化四个维度入手。

    此上而下,位置越靠前优化越明显,对数据库的性能提升越高。我们常说的SQL优化反而是对性能提高最小的优化。

    业务优化

    • 查询跨度不能太大,限制三个月
    • 数据归档,只查询最近一年的数据,老数据去历史数据里查询
    • 导数数据,异步任务进行。

    接下来我们再看看每种优化该如何实施。

    架构优化

    一般来说在高并发的场景下对架构层进行优化其效果最为明显,常见的优化手段有:分布式缓存,读写分离,分库分表等,每种优化手段又适用于不同的应用场景。

    要点:

      1. 读写分离
      1. 读多写少,加分布式缓存(redis)
      1. 写多、海量数据:分库分表
      1. 复杂查询,es 全文索引

    硬件优化

    我们使用数据库,不管是读操作还是写操作,最终都是要访问磁盘,所以说磁盘的性能决定了数据库的性能。一块PCIE固态硬盘的性能是普通机械硬盘的几十倍不止。这里我们可以从吞吐率IOPS两个维度看一下机械硬盘、普通固态硬盘、PCIE固态硬盘之间的性能指标。

    吞吐率:单位时间内读写的数据量

    • 机械硬盘:约100MB/s ~ 200MB/s
    • 普通固态硬盘:200MB/s ~ 500MB/s
    • PCIE固态硬盘:900MB/s ~ 3GB/s

    IOPS:每秒IO操作的次数

    • 机械硬盘:100 ~200
    • 普通固态硬盘:30000 ~ 50000
    • PCIE固态硬盘:数十万

    通过上面的数据可以很直观的看到不同规格的硬盘之间的性能差距非常大。

    不仅仅只有硬盘要优化,还有CPU,内存,网络等,具体看哪个是瓶颈。

    DB优化

    SQL执行慢有时候不一定完全是SQL问题,手动安装一台数据库而不做任何参数调整,再怎么优化SQL都无法让其性能最大化。要让一台数据库实例完全发挥其性能,首先我们就得先优化数据库的实例参数。

    数据库实例参数优化遵循三句口诀:日志不能小、缓存足够大、连接要够用。

    数据库事务提交后需要将事务对数据页的修改刷( fsync)到磁盘上,才能保证数据的持久性。这个刷盘,是一个随机写,性能较低,如果每次事务提交都要刷盘,会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法:

    • 先将事务写到日志文件RedoLog(WAL),将随机写优化成顺序写
    • 加一层缓存结构Buffer,将单次写优化成顺序写

    所以日志跟缓存对数据库实例尤其重要。而连接如果不够用,数据库会直接抛出异常,系统无法访问。

    接下来我们以Oracle、MySQL(InnoDB)、POSTGRES、达梦为例,看看每种数据库的参数该如何配置。

    Oracle

    参数分类参数名参数值备注
    数据缓存SGA_TAGET、MEMORY_TARGET物理内存70-80%越大越好
    数据缓存DB_CACHE_SIZE物理内存70-80%越大越好
    SQL解析SHARED_POOL_SIZE4-16G不建议设置过大
    监听及连接PROCESSES、SESSIONS、OPEN_CURSORS根据业务需求设置一般为业务预估连接数的120%
    其他SESSION_CACHED_CURSORS大于200软软解析

    MySQL

    参数分类参数名参数值备注
    数据缓存INNODB_BUFFER_POOL_SIZE物理内存50-80%一般来说越大性能越好
    日志相关Innodb_log_buffer_size16-32M根据运行情况调整
    日志相关sync_binlog1、100、01安全性最好
    监听及连接max_connections根据业务情况调整可以预留一部分值
    文件读写性能innodb_flush_log_at_trx_commit2安全和性能的折中考虑
    其他wait_timeout,interactive_timeout28800避免应用连接定时中断

    POSTGRES

    参数分类参数名参数值备注
    数据缓存SHARED_BUFFERS物理内存10-25%
    数据缓存CACHE_BUFFER_SIZE物理内存50-60%
    日志相关wal_buffer8-64M不建议设置过大过小
    监听及连接max_connections根据业务情况调整一般为业务预估连接数的120%
    其他maintenance_work_mem512M或更大
    其他work_mem8-16M原始配置1M过小
    其他checkpoint_segments32或者更大

    达梦数据库

    参数分类参数名参数值备注
    数据缓存MEMROY_TARGET、MEMROY_POOL物理内存90%
    数据缓存BUFFER物理内存60%数据缓存
    数据缓存MAX_BUFFER物理内存70%最大数据缓存
    监听及连接max_sessions根据业务需求设置一般为业务预估连接数的120%

    SQL优化

    SQL优化很容易理解,就是通过给查询字段添加索引或者改写SQL提高其执行效率,一般而言,SQL编写有以下几个通用的技巧:

    1. 查询慢SQL 日志

    mysql> SHOW VARIABLES LIKE 'slow_query%';
    +---------------------+---------------------------------------------------------------------+
    | Variable_name       | Value                                                               |
    +---------------------+---------------------------------------------------------------------+
    | slow_query_log      | OFF                                                                 |
    | slow_query_log_file | C:\ProgramData\MySQL\MySQL Server 5.7\Data\LAPTOP-UHQ6V8KP-slow.log |
    +---------------------+---------------------------------------------------------------------+
    2 rows in set, 1 warning (0.02 sec)
    
    mysql> SHOW VARIABLES LIKE 'long_query_time';
    +-----------------+-----------+
    | Variable_name   | Value     |
    +-----------------+-----------+
    | long_query_time | 10.000000 |
    +-----------------+-----------+
    1 row in set, 1 warning (0.01 sec)
    

    参数说明如下:

    • slow_query_log:慢查询开启状态
    • slow_query_log_file:慢查询日志存放的位置(一般设置为 MySQL 的数据存放目录)
    • long_query_time:查询超过多少秒才记录
    启动和设置慢查询日志
    • 配置文件
    [mysqld]
    log-slow-queries=dir\filename
    long_query_time=n
    
    • sql
    SET GLOBAL slow_query_log=ON/OFF;
    SET GLOBAL long_query_time=n;
    set global log_queries_not_using_indexes=on; 默认值为off 表示没有使用索引的sql查询也会记录下来
    

    2.得到慢SQL进行慢sql分析

    1. 使用 explain 分析SQL的执行计划,是否索引失效

    2. 以小表驱动大表

    3. 使用UNION ALL替代UNION

    4. 避免使用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE。可以是话用UNION代替

    5. 减少数据回表

    1. 主键 id 优先自增,分布式使用 雪花ID,或者redis自增,自增步长

    小结

    我们今天分别从架构优化、硬件优化、DB优化、SQL优化四个角度探讨了如何实施优化,提升数据库性能。但是大家还是要记住一句话,数据库系统没有银弹, 要让适合的系统,做合适的事情。

    MySQL如何优化? https://mp.weixin.qq.com/s/_E6szZ0WFtCImYKUfygSMA
    完爆90%的性能毛病,收好数据库优化八大通用绝招

    更多相关内容
  • 数据库SQL优化大总结之 百万级数据库优化方案
  • 数据库优化详解

    2020-12-14 18:06:50
    数据库优化从以下几个方面优化: 数据库设计—三大范式、字段、表结构 数据库索引 存储过程 (模块化编程,可以提高速度) 分表分库 (水平分割,垂直分割) 主从复制、读写分离 SQL 调优 对 MySQL 配置优化 (配置最大...
  • wordpress数据库优化介绍 这篇wordpress优化数据库的方法(详解版)添加了很多的说明,目的是让不清楚wordpress数据库的童鞋可以了解下理论知识。理论应用于实际么。如果只想看操作的本站总结了wordpress数据库优化的...
  • 浅谈数据库优化方案

    2020-09-10 01:19:56
    主要为大家详细介绍了数据库优化方案,具有一定的参考价值和实用性,感兴趣的小伙伴们可以参考一下
  • 本文介绍了一些很实用但却不是很常见的Oracle数据库优化策略,包括批量FETCH、SQL预解析等,需要的朋友可以参考下
  • 简单描述数据库优化方案,以及数据库一些常用的操作,包括一些简单的查询语句,函数使用,合适学习mysql的读者。 简单描述数据库优化方案,以及数据库一些常用的操作,包括一些简单的查询语句,函数使用,合适学习...
  • 数据库优化资源

    2018-02-28 10:20:57
    数据库访问性能优化,本文只是面对数据库应用开发的程序员,不适合专业DBA,DBA在数据库性能优化方面需要了解更多的知识;
  • db2数据库优化技巧,个人总结版
  • SQL数据库优化方法

    2019-03-05 10:15:55
    数据库优化通常可以过对 网络、硬件操作系统数据库参数和应用程序的优化来进行。
  • ORACLE数据库优化基础培训,涵盖了基础操作和常见的问题。 数据库培训内容: 1、Oracle之SQL语句性能优化 2、数据库常用管理命令 3、数据库常见问题处理
  • 德哥的Postgresql数据库优化的培训视频,从Postgtesql的安装,配置,到数据库配置文件postgresql.conf的讲解,执行计划的查看,非常详细。
  • 数据库优化的方法

    千次阅读 2021-12-04 17:25:23
    1、数据库优化概览图 在数据库优化方面,从主到次的顺序: 以SQL优化、索引优化为主,解决慢SQL问题,最大程度地利用好索引 其次从数据库表结构入手、分库与分表,对数据量级进行处理 最大化利用机器配置,比如...

    1、数据库优化概览图

    在数据库优化方面,从主到次的顺序:

    以SQL优化、索引优化为主,解决慢SQL问题,最大程度地利用好索引 其次从数据库表结构入手、分库与分表,对数据量级进行处理 最大化利用机器配置,比如设置使用机器内存的大小 如果以上三点无法满足需求,那么再考虑硬件方面的问题,比如提升机器配置,再不行就多用几台服务器,这种成本较高,其性价比相对来说是最低的

    img

    2、软优化:

    2.1、查询语句的优化 用EXPLAIN 分析一条查询语句

    • 1.避免索引失效导致的全表扫描

    • 2.SQL语句的规范

    • 3.不要在列上进行运算

    • 4.不使用NOT IN和<>操作

    2.2、优化子查询

    在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

    使用连接(JOIN)来代替子查询(Sub-Queries)

    2.3、添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]

    索引是提高数据库查询速度最重要的方法之一.

    使用短索引

    在建有索引的字段上尽量不要使用函数进行操作

    应该注意避免在查询中让MySQL进行自动类型转换,因为转换过程也会使索引变得不起作用

    索引失效的情况:mysql

    • 1.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')

    • 2.如果是组合索引的话,如果不按照索引的顺序进行查找,比如直接使用第三个位置上的索引而忽略第一二个位置上的索引时,则会进行全表查询,组合索引,多列索引必须满足最左匹配.

    • 3.条件中有or,OR关键字的两个字段必须都是用了索引,该查询才会使用索引

    • 4.索引无法存储null值,所以where的判断条件如果对字段进行了null值判断,将导致数据库放弃索引而进行全表查询.

    为什么索引列无法存储Null值?

    a.索引是有序的。NULL值进入索引时,无法确定其应该放在哪里。

    • 5.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

    • 6.in 和 not in 也要慎用,否则会导致全表扫描

    • 7.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。

    • 8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。

    2.4、表的设计合理化 符合三大范式(3NF)

    1NF、列不可分;

    强调的是列的原子性,即列不能够再分成其他几列。

    2NF、非主键列完全依赖主键,不存在部分依赖;

    一是表必须有一个主键;

    二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分。

    3NF、非主键列必须直接依赖主键,不存在传递依赖。

    不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。

    分解表

    对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表

    中间表

    对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时

    使用联合(UNION)来代替手动创建的临时表

    2.5、表字段设置合理

    选取最适用的字段属性

    MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小,在它上面执行的查询也就会越快。因此,在创建表的时候,为了获得更好的性能,我们可以将表中字段的宽度设得尽可能小。

    另外一个提高效率的方法是在可能的情况下,应该尽量把字段设置为NOTNULL,这样在将来执行查询的时候,数据库不用去比较NULL值。

    数据字典,不存汉字查询

    2.6、优化子查询

    3、硬优化:

    3.1、硬件三件套(cpu,内存,磁盘)

    3.2、优化数据库参数

    优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

    • key_buffer_size:索引缓冲区大小

    • table_cache:能同时打开表的个数

    • query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.

    • sort_buffer_size:排序缓冲区

    3.3、数据库的分库分表

    因为数据库压力过大,首先一个问题就是高峰期系统性能可能会降低,因为数据库负载过高对性能会有影响。另外一个,压力过大把你的数据库给搞挂了怎么办?所以此时你必须得对系统做分库分表 + 读写分离,也就是把一个库拆分为多个库,部署在多个数据库服务上,这时作为主库承载写入请求。然后每个主库都挂载至少一个从库,由从库来承载读请求。

    设置数据库主从复制状态下的读写分离,主库进行更新操作,从库进行查询操作。

    3.4、缓存集群

    如果用户量越来越大,此时你可以不停的加机器,比如说系统层面不停加机器,就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高,就扩容加数据库服务器,通过分库分表是可以支持扩容机器的,如果数据库层面的读并发越来越高,就扩容加更多的从库。但是这里有一个很大的问题:数据库其实本身不是用来承载高并发请求的,所以通常来说,数据库单机每秒承载的并发就在几千的数量级,而且数据库使用的机器都是比较高配置,比较昂贵的机器,成本很高。如果你就是简单的不停的加机器,其实是不对的。所以在高并发架构里通常都有缓存这个环节,缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万,甚至每秒数十万,对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性,对那种写少读多的请求,引入缓存集群。具体来说,就是在写数据库的时候同时写一份数据到缓存集群里,然后用缓存集群来承载大部分的读请求。这样的话,通过缓存集群,就可以用更少的机器资源承载更高的并发。

    img

    每天努力一点,每天都在进步。

    展开全文
  • 数据库优化的四大方法

    千次阅读 多人点赞 2021-12-26 10:53:58
    正如上图所示,数据库优化可以从架构优化,硬件优化,DB优化,SQL优化四个维度入手。 此上而下,位置越靠前优化越明显,对数据库的性能提升越高。我们常说的SQL优化反而是对性能提高最小的优化。 接下来我们再看看...

    目录

    一、前言

    二、架构优化

    分布式缓存

    读写分离

    水平切分

    架构优化小结

    三、硬件优化

    四、DB优化

    五、SQL优化

    执行计划

    SQL优化小结


    一、前言

    首先,我们看一下,数据库优化可以从那些方面入手:

    图片

    正如上图所示,数据库优化可以从架构优化,硬件优化,DB优化,SQL优化四个维度入手。

    此上而下,位置越靠前优化越明显,对数据库的性能提升越高。我们常说的SQL优化反而是对性能提高最小的优化。

    接下来我们再看看每种优化该如何实施。

    二、架构优化

            一般来说在高并发的场景下对架构层进行优化其效果最为明显,常见的优化手段有:分布式缓存,读写分离,分库分表等,每种优化手段又适用于不同的应用场景。

    分布式缓存

            有句老话说的好,性能不够,缓存来凑。当需要在架构层进行优化时我们第一时间就会想到缓存这个神器,在应用与数据库之间增加一个缓存服务,如Redis或Memcache。

    图片

             当接收到查询请求后,我们先查询缓存,判断缓存中是否有数据,有数据就直接返回给应用,如若没有再查询数据库,并加载到缓存中,这样就大大减少了对数据库的访问次数,自然而然也提高了数据库性能。

            不过需要注意的是,引入分布式缓存后系统需要考虑如何应对缓存穿透、缓存击穿和缓存雪崩的问题。

    读写分离

            一主多从,读写分离,主动同步,是一种常见的数据库架构优化手段。

            一般来说当你的应用是读多写少,数据库扛不住读压力的时候,采用读写分离,通过增加从库数量可以线性提升系统读性能。

    图片

     

            主库,提供数据库写服务;从库,提供数据库读能力;主从之间,通过binlog同步数据。

            当准备实施读写分离时,为了保证高可用,需要实现故障的自动转移,主从架构会有潜在主从不一致性问题。

    水平切分

            水平切分,也是一种常见的数据库架构优化手段。

            当你的应用业务数据量很大,单库容量成为性能瓶颈后,采用水平切分,可以降低数据库单库容量,提升数据库写性能。

    图片

            当准备实施水平切分时,需要结合实际业务选取合理的分片键(sharding-key)。

    架构优化小结

    1. 读写分离主要是用于解决 “数据库读性能问题”

    2. 水平切分主要是用于解决“数据库数据量大的问题”

    3. 分布式缓存架构可能比读写分离更适用于高并发、大数据量大场景。

    三、硬件优化

            我们使用数据库,不管是读操作还是写操作,最终都是要访问磁盘,所以说磁盘的性能决定了数据库的性能。一块PCIE固态硬盘的性能是普通机械硬盘的几十倍不止。这里我们可以从吞吐率、IOPS两个维度看一下机械硬盘、普通固态硬盘、PCIE固态硬盘之间的性能指标。

    吞吐率:单位时间内读写的数据量

    • 机械硬盘:约100MB/s ~ 200MB/s

    • 普通固态硬盘:200MB/s ~ 500MB/s

    • PCIE固态硬盘:900MB/s ~ 3GB/s

    IOPS:每秒IO操作的次数

    • 机械硬盘:100 ~200

    • 普通固态硬盘:30000 ~ 50000

    • PCIE固态硬盘:数十万

            通过上面的数据可以很直观的看到不同规格的硬盘之间的性能差距非常大,当然性能更好的硬盘价格会更贵,在资金充足并且迫切需要提升数据库性能时,尝试更换一下数据库的硬盘不失为一个非常好的举措,你之前遇到SQL执行缓慢问题在你更换硬盘后很可能将不再是问题。

    四、DB优化

            SQL执行慢有时候不一定完全是SQL问题,手动安装一台数据库而不做任何参数调整,再怎么优化SQL都无法让其性能最大化。要让一台数据库实例完全发挥其性能,首先我们就得先优化数据库的实例参数。

            数据库实例参数优化遵循三句口诀:日志不能小、缓存足够大、连接要够用。

    数据库事务提交后需要将事务对数据页的修改刷( fsync)到磁盘上,才能保证数据的持久性。这个刷盘,是一个随机写,性能较低,如果每次事务提交都要刷盘,会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法:

    • 先将事务写到日志文件RedoLog(WAL),将随机写优化成顺序写

    • 加一层缓存结构Buffer,将单次写优化成顺序写

            所以日志跟缓存对数据库实例尤其重要。而连接如果不够用,数据库会直接抛出异常,系统无法访问。

    五、SQL优化

            SQL优化很容易理解,就是通过给查询字段添加索引或者改写SQL提高其执行效率,一般而言,SQL编写有以下几个通用的技巧:

    • 合理使用索引

    索引少了查询慢;索引多了占用空间大,执行增删改语句的时候需要动态维护索引,影响性能 选择率高(重复值少)且被where频繁引用需要建立B树索引;一般join列需要建立索引;复杂文档类型查询采用全文索引效率更好;索引的建立要在查询和DML性能之间取得平衡;复合索引创建时要注意基于非前导列查询的情况

    • 使用UNION ALL替代UNION

    UNION ALL的执行效率比UNION高,因为UNION执行时需要排重;

    • 避免select * 写法

    执行SQL时优化器需要将 * 转成具体的列;每次查询都要回表,不能走覆盖索引。

    • JOIN字段建议建立索引

    一般JOIN字段都提前加上索引

    • 避免复杂SQL语句

    提升可阅读性;避免慢查询的概率;可以转换成多个短查询,用业务端处理

    • 避免where 1=1写法

    • 避免order by rand()类似写法

    RAND()导致数据列被多次扫描

    执行计划

    要想优化SQL必须要会看执行计划,执行计划会告诉你哪些地方效率低,哪里可以需要优化。通过explain sql 可以查看执行计划

    SQL优化小结

    这里给大家总结一下SQL优化的套路:

    1. 查看执行计划 explain sql

    2. 如果有告警信息,查看告警信息 show warnings;

    3. 查看SQL涉及的表结构和索引信息

    4. 根据执行计划,思考可能的优化点

    5. 按照可能的优化点执行表结构变更、增加索引、SQL改写等操作

    6. 查看优化后的执行时间和执行计划

    7. 如果优化效果不明显,重复第四步操作

    展开全文
  • wordpress数据库优化介绍 这篇wordpress数据库优化的方法(简洁操作版),没什么理论的说明,如果想更具体wordpress数据库优化的操作的意义请查看:wordpress优化数据库的方法(详解版) 在wordpress允许一段时间后,...
  • 数据库优化有很多可以讲,按照支撑的数据量来分可以分为两个阶段:单机数据库和分库分表,前者一般可以支撑500W或者10G以内的数据,超过这个值则需要考虑分库分表。另外,一般大企业面试往往会从单机数据库问起,...
  • K3数据库优化方案K3数据库优化方案
  • 大型网站数据库优化大型网站数据库优化
  • 数据库面试题索引sql优化.pdf+数据库SQL优化总结之百万级数据库优化.pdf 附赠Oracle高性能sql优化
  • 数据库优化

    2012-04-05 16:17:55
    高并发下MySQL数据库优化 高并发下MySQL数据库优化 高并发下MySQL数据库优化
  • 数据库优化大体分为四个方面,一是数据库服务器的优化,二是架构的设计层面的优化,三是my.cnf配置的优化,四是sql语句的优化。 一. 数据库服务器的优化 1. 服务器硬件方面:影响Mysql性能因素主要有 CPU、可用...

    数据库优化大体分为四个方面,一是数据库服务器的优化,二是架构的设计层面的优化,三是my.cnf配置的优化,四是sql语句的优化。

    一. 数据库服务器的优化

    1. 服务器硬件方面:影响Mysql性能因素主要有 CPU、可用内存大小、磁盘读写速度、网络带宽等;

    2. 操作系统方面:影响Mysql性能因素主要有 应用文件句柄数、系统的网络配置等;

    这部分的优化一般由DBA和运维工程师去完成,在硬件资源的优化中,我们重点关注的应该是服务本身所承载的体量,然后提出合理的指标要求避免出现资源浪费的想象。

    二. 架构的设计层面的优化

    Mysql是一个磁盘IO访问特别频繁的关系型数据库,在高并发和高性能的场景中,Mysql必然会承受巨大的并发压力,优化方式如下:

    1. 搭建Mysql主从集群:单个Mysql服务容易导致单点故障,一旦服务宕机将会导致依赖Mysql数接库的应用全部无法响应,主从集群可以保证服务的高可用性;

    2. 读写分离设计:在读多写少的场景中,通过读写分离的方案可以去避免读写冲突,导致性能问题;

    3. 分库分表机制:通过分库可以降低单个服务器节点的IO压力,通过分表的方式可以降低单表数据量从而提升sql的查询效率。

    三. my.cnf配置的优化

    四. sql语句的优化

    常见提问:你有没有sql优化的实际经验,或者是你对sql优化有没有自己的见解?

    答:后期在项目维护的时候,对sql进行过优化,(实际优化例子阐述),通过对sql的优化,也有一些自己的理解,主要是如下几点:

    1. 使用缓存优化查询:

    (1) 原理:进行多次相同的查询,结果就会放入缓存中,后续在进行同样的查询,就会从缓存中查询,不会到表中提取数据了。

    (2) 避免缓存失效:查询的sql不能变,也就是说sql语句的大小写不同、多空格、少空格、不确定数据等都会算做不同的sql,会导致缓存失效。

    为什么?

    MySql在实现缓存方面也采用用了Key-Value的方式,就是将SELECT语句和该查询语句的结果集做了一个HASH映射并保存在一定的内存区域中。

    (3) 缓存数据失效时机:

    1) 表的结构或数据发生改变:在表的结构或数据发生改变时,查询缓存中的数据不再有效,有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效,所以查询缓存适合有大量相同查询的应用,不适合有大量数据更新的应用;

    2) 写入数据时缓存失效:当某个表正在写入数据,则这个表的缓存(命中检查,缓存写入等)将会处于失效状态,在Innodb中,如果某个事务修改了表,则这个表的缓存在事务提交前都会处于失效状态,在这个事务提交前,这个表的相关查询都无法被缓存。

    4) Mysql会出现内存泄漏问题:

    Mysql一般是不会出现这中情况的,如果出现了,一般都是自己的参数设置问题,需要在参数设置和设计上尽量合理,或者去官方bug里memory leak查找,是否存在修复的版本等等,本人暂时未遇到过该情况,如需了解可自行百度。

    (3) 优点:提高sql的查询效率

    (4) 缺点:查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空,因此很可能费劲地把结果存起来,还没使用,就被一个更新全清空了,对于更新压力大的数据库来说,查询缓存的命中率会非常低,除非你的业务就是有一张静态表,很长时间才会更新一次,所以对于某些写频繁的系统,开启Query Cache功能可能并不能让系统性能有提升,有时反而会有下降。

    所以需要根据实际情况选择使用mysql自带缓存功能还是使用像redis等第三方缓存。

    查询缓存的使用方法可以查看 Mysql之查询缓存介绍_BestandW1shEs_lsy的博客-CSDN博客

    2. explain查看sql的执行计划:

    用法:explain select ......;

    explain的详细使用方法可以查看 Mysql之快速上手sql优化_BestandW1shEs_lsy的博客-CSDN博客

    3. 给搜索的字段建立索引:

    给哪些字段:

    where后面一些常用字段建立索引,但是并不是所有的字段都适合建立索引,如果sql筛选出来的结果集只占表的少部分,就适合建立索引,如果查询出来的数据太大,超过了表的50%以上,就不适合建立索引,因为当数据量达到很大的时候,查询的速度就很慢,这是因为当数据量达到一定量的时候就会走全表扫描,这是因为mysql的内部的一个优化器进行了最优策略。

    4. limit X 的使用:

    在明确知道只有一条数据的时候,可以使用limit 1,明确知道有五行数据,使用limit 5等,如下图所示:

    5. 永久连接:

    数据库再创建完一个连接之后,会永久处于连接状态,apache在极端的环境中,它进行的每一个http请求都会创建子进程去请求数据库,到时候如果连接太多后,会增加数接库的负担,该问题是属于架构的问题,比如大型网站的访问,或者是恶意频繁请求数据等情况。

    6. 选择正确的数据库引擎:

    mysql中有两个数据库引擎的,myISAM 和 innoBD。

    (1) myISAM适合大数据量的查询,但是对于一些插入的操作并不友好,因为在进行update的时候会进行表锁,此时如果还有用户在读表,查询是无效的,必须等到插入操作完成后,才可以去读数据;

    (2) innoDB对于插入操作比较友好,但是对查询操作不是特别友好,它是一个行锁。

    7. 在进行大量数据的delete或insert的时候:

    该情况也是会经常遇到,该问题可能会导致数接库服务器宕机,因为在进行大量数据的delete或insert的时候,会将整个表锁起来,此时外部的一些web请求再来,就请求不到该表,当请求达到足够多的时候后,并且此时的执行语句完成,大量的请求就会把数据库搞宕机,并且如过数据量过大,在执行途中中止了该语句的运行,数据库就会回滚操作,可能导致数据库负载变高等影响。

    优化方案:需要使用limit去做限制,不要一次性处理全部数据。

    8. 数据类型,尽量使用小的:

    如果一个硬盘的数据量特别多,不同的数据类型暂用的硬盘空间是不一样的,如果占用硬盘的空间特别小,特别紧凑,那么硬盘的读取速度就会特别快,在大型的公司里,数据库字段类型的选择是有明确的规定的,这样不仅可以节约硬盘空间,而且对数据读取的性能提升也很大。

    9. 固定的字段长度:

    (1) 优点:从速度来说,处理固定长度字符比可变长度字符的速度快;

    用房子打比方简单的说,偏移量是门牌号,数据记录长度是房间数,根据起始位置加上偏移量定位到记录的位置,再从定位到的位置读取记录长度的这么多数据,就把这条记录完整读出来了。

    (2) 缺点:从存储空间来说,可变长度字符比固定长度字符节省空间。

    10. 尽量不要赋null:

    可以用' ',不然容易报空指针的错误。

    11. 明确的固定的字段上使用enum:

    比如性别、国家、市,这些都是固定的,就可以使用enum,但是有人会选择使用varchar,最好别这么用,因为enum的速度比varchar的速度更快。

    enum类型是非常快和紧凑的,实际上,其保存的是 tinyint,但其外表上显示为字符串,这样一来,用这个字段来做一些选项列表变得相当的完美,就比如一个表中存储性别,很多人喜欢存成:男,‘m’,女,'f' ,这时候还不如ENUM性能高,还有一些状态值,例如:新建,审批中,结束,当然中间应该还有其他状态,反正这些用ENUM是最高效的,而且显示的时候,看起来也特别的直观,虽然真实存储tinyint,但是表面是字符呀,这个看的时候特别直观,查询的时候特别高效;

    因为无论enum(‘manmaman’,’womanwomanwoman’) 枚举的字符多长,内部都是用整型表示,在内存中产生的数据大小不变,而varchar型,却在内存中产生的数据越来越多。

    12. id主键每张表都需要建立:

    对于查询性能来说是非常重要的,因为不管通过任何方式去查询表,最终都会通过主键去定位到数据,所有建立主键是可以有效的提高性能的,而且在建主键的时候能不使用varchar,最好不用varchar,因为varchar的查询性能是比不上int的,严谨一点都是使用int,但是大多数都是使用varchar。

    13. 避免使用select * :

    原因就是,查询的约多,速度就越慢,数据越多,对网络的传输也会负载过重。

    14. 避免使用某些运算函数:

    例如,rand() 函数,它的计算是在CPU上进行的,尽量避免使用它,实在必须使用也没办法。

    15. 连接两表的时候,join尽量保持两个字段的类型要一致:

    因为两个字段的类型不一样,比如一个是int,一个是varchar,join后会导致索引失效。

    16. 垂直分割:

    将表按列的形式分割成表,如果表中的字段特别多,可以将一张表分为两张表,可以提高访问效率。

    展开全文
  • 数据库SQL优化大总结之 百万级数据库优化方案,网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一 下,其中有错误和不足的地方,还请大家纠正补充。 这篇文章我花费了大量的时间查找...
  • 易语言EDB数据库优化

    2020-07-20 18:40:37
    易语言EDB数据库优化源码,EDB数据库优化
  • 数据库优化之实战

    2018-04-12 10:06:39
    数据库优化方法,千万级数据库记录查询解决实战。1 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
  • 易语言-EDB数据库优化

    2021-06-29 15:47:27
    EDB数据库优化源码分析优化易语言数据库记录,减小数据库体积。只对文本内容有效。 三叶自学网
  • MySQL数据库优化

    2018-04-09 10:34:09
    MySQL数据库优化MySQL数据库优化MySQL数据库优化MySQL数据库优化
  • 易语言数据库优化

    2020-07-20 12:09:13
    易语言数据库优化源码,数据库优化
  • 全面深入Mysql数据库优化

    千人学习 2019-09-26 11:44:58
    本课程作为MySQL高级课程, 主要讲解了MySQL中的视图/存储过程/触发器/索引等对象的使用、常见的SQL语句优化的技巧 、应用优化、数据库优化、数据库日志等方面的知识,并通过综合案例,对课程中的知识进行一个整合...
  • ORACLE数据库优化方式和MYSQL等很大的区别,今天通过一个ORACLE数据库实例从表格、数据等各个方便分析了如何进行ORACLE数据库优化。 tsfree.sql视图 这个sql语句迅速的对每一个表空间中的空间总量与每一个表...
  • 数据库优化的8个方面

    千次阅读 2021-03-01 22:49:55
    主要的优化是SQL的优化和表结构的优化,这里可以起到数据库优化的80%的作用。 优先考虑这两个方面的优化。 1. 优化数据库表结构的设计 为什么数据库表的设计会影响性能? 字段的数据类型:不同的数据类型的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 908,782
精华内容 363,512
关键字:

数据库优化