MySQL调优

调优的目的?让吞吐量更大,响应速度更快

关于数据库优化,我们从以下5个维度进行。

一.优化表结构

表结构尽量遵循三范式的原则,在进行多表查询的时候,必要时可以采用反范式化进行优化。

什么叫范式?

  • 在关系型数据库中,关于数据表设计的基本原则,规则就称为范式。

一共有6大范式,知道前5个就行。满足了高级范式,就一定满足低级范式。比如满足第三范式,就一定满足第1,2范式。

  • 第一范式:确保每一个字段保证"原子性",不能被拆分。比如有一个字段叫"个人信息",它就可以拆分为地址,年龄,姓名等。就不满足第一范式
  • 第二范式:确保表中的每一条记录,都有唯一的标识(主键)。所有的非主键字段,必须完全依赖主键。不能部分依赖。
    注意:这里的主键是联合主键。比如下图:姓名,年龄依赖球员编号;比赛时间,比赛场地依赖比赛编号;只有得分全部依赖

  • 第三范式:确保非主键之间是相互独立的,不能产生依赖。下图就不满足

  • 巴斯范式(BCNF):3NF的增强版,在3NF的基础上消除了主属性对候选键的部分依赖或者传递依赖的关系。相当于主键中也产生了依赖关系,就不满足巴斯范式。比如下图

  • 第四范式:一个表中只有一对1:多的关系。如果一个表中有多个1:多的关系就不满足第四范式。

  •  第五范式:也叫完美范式,了解有这么个东西就行

范式的优缺点?

  • 优点:消除数据冗余
  • 缺点:降低查询效率,因为范式越高,设计出来的数据表就越多,就需要很多的关联查询。

反范式化?

  • 是一种空间换时间的优化手段。因为我们遵循业务优先原则,可以通过在表中增加冗余字段来提高数据库的读性能。
  • 当冗余信息有价值或者能大幅度提高查询效率的时候,我们才会采用反范式化进行优化。

 

数据库的设计原则?三少一多

  • 数据表的个数越少越好
  • 数据表中的字段个数越少越好
  • 数据表中联合主键的字段个数越少越好
  • 使用主键和外键越多越好。这里是指外键关系越多,就可以重复的利用数据,而不是指在表中建立好多外键。

数据库表建模的工具?

  • PowerDesigner
二.优化逻辑查询

关联查询优化:最好"被驱动表加索引"

  • 外连接:一般驱动表是全表查询(就算添加索引也是index),被驱动表是索引查询。(也就是说最好给被驱动表添加索引,驱动表加不加都行)
    • 如:student是驱动表,book是被驱动表   :EXPLAIN SELECT * FROM student LEFT JOIN book ON student.card = book.card;
  • 内连接:谁是驱动表谁是被驱动表由优化器决定,优化器满足"小表驱动大表"。(2个都加索引,让优化器自己决定;如果只加 一个索引,优化器肯定选择加索引的作为被驱动表)

优化器中join 的原理?

  • 不加索引
    • Simple Nested-Loop Join(简单嵌套循环连接) 

    • Block Nested-Loop Join(块嵌套循环连接):不再逐条获取驱动表的数据,而是一块一块的获取,引入join buffer缓存区

  

    • Hash Join:MySQL8.0之后默认使用hash join。可以做大数据集连接。
  • 加索引
    • Index Nested-Loop Join(索引嵌套循环连接)

 

 子查询优化:

  • 子查询执行效率不高,使用关联查询(join)代替子查询
  • 效率不高的原因:
    • 查询的过程中需要建立一个临时表,查询完毕,再撤销临时表。消耗性能
    • 临时表都不会使用索引

 排序优化:

  • 前提知识:MySQL支持2中排序方式,所以优化也是从这俩方面考虑
    • index排序:b+树的叶子节点就是按照排序进行的,使用索引直接就可以保证有序性
    • FileSort排序:将需要排序的数据加载到内存中,然后进行排序。
  • 尽量使用索引完成order by排序。如果where 和order by 后面的列相同就用单索引,不同就用联合索引。
  • 对FileSort进行调优
    • 前提知识:FileSort有2种算法
      • 双路排序(慢):进行俩次磁盘扫描,第一次只加载需要排序的列到sort_buffer,进行排序。然后根据排序好的列,第二次从磁盘读取其他的列。
      • 单路排序(快):一次性将所有列的数据加载到sort_buffer中,进行排序。
    • 提高sort_buffer_size:不管哪种算法,提高这个内存值肯定加大效率。
    • 提高max_length_for_sort_data:这个参数就是一个界限,需要返回的列总长度大于这个值就使用双路,小于这个值就使用单路。

group by优化:

  • group by 优化的方法和order by一样。

分页查询优化:

  • 如果像下图查询的情况极端,尽量的使用表中其他字段的索引。

 

其他的优化

  • exists和in:小表驱动大表

  • count(*)和count(1)和count(具体字段)的效率
    • count(*)和count(1)没有本质区别,执行时间基本一样
    • count(具体字段)的时候,尽量使用占用空间少的二级索引。因为二级索引存储的信息相比聚簇索引要少很多。count(*)和count(1) 系统会自动选择占用空间少的二级索引进行统计。  
    • innodb的count()是O(n)级别的,MyISAM是O(1)级别的。
  • 关于select *
    • 尽量使用什么字段就指出来,不要使用select *。因为会加载很多没用的列。
    • 无法使用覆盖索引
  • 关于Limit 1:
    • 如果是全表扫描,加上Limit 1。找到数据就不会再继续查找了,加快查找效率
    • 如果是唯一索引,找到数据也不会继续查找了,Limit 1 就不管用了。

 

 三.优化物理查询(索引)
  • 选择适合做索引的字段(MySQL索引讲)
  • 哪些情况导致索引失效(MySQL索引讲)
  • 使用覆盖索引:一个索引包含了满足查询结果的数据就叫做覆盖索引。(也就是需要的列恰好都在索引的叶子节点上存储,不需要回表)
    • 好处:无需回表;可以把随机IO变成顺序IO加快查询效率(利用到索引都是顺序IO,因为索引就是有顺序的)。
  • 使用索引下推:如图

    • 索引下推就用在一些and查询语句中,本来通过非聚集索引zipcode查询出来数据,要进行回表,但是如果查出来100条,分别对这100条进行回表就很浪费性能,icp就是先不进行回表,使用后面的条件进行过滤,过滤完毕之后比如剩下10条,对这10条进行回表就行了。
    • set optimizer_switch = 'index_condition_pushdown=on'     //开启索引下推
  • 使用隐藏索引:之前索引要是删除后,发现效果不好或者出错,只能重新添加索引。一删一加浪费性能,现在就可以先隐藏索引,让索引不起作用。发现效果不错,再删除索引。
    • create index 索引名 on 表名(列名) invisible
四.使用缓存

对于热点数据可以使用redis或者Memcached作为缓存,减少数据库的压力

五.库级优化

从以下3个方面进行调优。

1.优化MySQL服务器:在my.cnf或者my.ini文件的[mysqld]组中配置。

  • innodb_buffer_pool_size:表示表和索引的最大缓存。这个值越大,查询速度越快。
  • key_buffer_size:表示索引缓存区的大小,索引缓存区是所有线程共享,对于4GB左右的服务器该参数可设置为256M或384M。
  • table_cache:表示同时打开的表的个数。默认为2402,调到512-1024最佳,这个值不是越大越好,因为同时打开好多表影响操作系统的性能。
  • query_cache_size:表示查询缓存区的大小。这个就是MySQL架构中的查询缓冲器。在8.0废除,需要配合query_cache_type才能使用。
  • query_cache_type:0代表关闭,1代表开启,2代表自定义。
    • 关于自定义:eg:select SQL_CACHE * from test where id=5  使用SQL_CACHE可以自定义的在sql声明使用查询缓存
  • sort_buffer_size:表示每一个需要进行排序的线程分配的缓存区的大小。提高这个参数的值可以提高order by和group by的速度。对于4GB左右的服务器建议设置为6-8M。如果有100个连接,实际分配的总共排序缓存区的大小为6 * 100 = 600MB。
  • join_buffer_size = 8M:表示联合查询操作时所能使用的缓存区大小,每一线程独享。
  • read_buffer_size:表示每个线程连续扫描时为扫描的每一个表分配的缓冲区的大小。默认为64K,可设置为4M。
  • innodb_flush_log_at_trx_commit:表示何时将缓存区的数据写入日志文件
    • 0:表示每秒1次,将数据写入日志文件并将日志文件写入磁盘。
    • 1:表示每次提交事务,将数据写入日志文件并将日志文件写入磁盘。
    • 2:表示每次提交事务,将数据写入日志文件,每隔1s将日志文件写入磁盘。
  • innodb_log_buffer_size:事务日志所使用的缓存区,log buffer的值。
  • max_connections:表示允许连接到MySQL数据库的最大数量
  • back_log:用于控制MySQL监听TCP端口时设置的挤压请求栈大小,比如:max_connections为500,其他来的请求就可以放入这个栈中。5.0之前为50,现在一般为50+(max_connections / 5)。
  • thread_cache_size:线程缓冲池的大小
  • wait_timeout:一个请求的最大连接时间,4GB的服务器一般设置为5-10。
  • interactive_timeout:表示服务器在关闭连接前等待行动的秒数。

 2.优化数据库结构:

  • 拆分表:冷热数据分离,将一些查询频率高的列放在一个表中,查询频率不高的列放在另一个表中。
  • 添加中间表:对于联合查询,使用一个中间表记录它们之间的关系,将原来的联合查询改为对中间表的查询,加快效率。
  • 优化数据类型:优先选择符合存储需要的最小的数据类型。
    • 存储整数一般使用int型,非负型数据(自增id,整型ip)可以使用unsigned型,因为无符号相对于有符号,同样的字节数,存储量更大(不用给负数留空间)。
    • 避免使用TEXT,BLOB类型。
    • 使用TimeStamp存储时间
    • 使用Declmal代替float和double存储精准浮点数,因为Declmal为精准浮点型,在计算时不会丢失精度,占用空间由定义的宽度决定。
  • 使用分析表,检查表,优化表的语句
    • 分析表:分析关键字的分布,analyze table ...
    • 检查表:检查表中是否存在错误,check table ...
    • 优化表:消除删除/更新造成的空间浪费,optimize table...,只能优化varchar,blob,text类型的字段,整理文件的碎片,重新规划空间。
  • 创建全局通用表空间:create tablespace...,相对于独享表空间,可以节约元数据方面的内存。

3.大表优化:

  • Memcached(高速缓存系统)+ MySQL(垂直拆分:拆分表结构) (拆分表)
  • mysql主从复制,读写分离
  • 分表分库 + 水平拆分 + Mysql 集群 (水平拆分:把数据库中的每个表中的数据,拆分在不同的数据库中)(拆分表中的数据)
    • 水平拆分就是分库分表的一个体现,也可以理解为一个东西

 

今天是除夕,祝大家新年快乐!

 寄语:放弃可以找到一万个理由,坚持只需要一个信念! 

posted @ 2022-01-31 22:34  小猴子_X  阅读(811)  评论(1编辑  收藏  举报