面试题目整理（MySQL系列-调优）

影响mysql的性能因素

  • 业务需求对MySQL的影响(合适合度)

  • 存储定位对MySQL的影响

    • 不适合放进MySQL的数据
      • 二进制多媒体数据
      • 流水队列数据
      • 超大文本数据
    • 需要放进缓存的数据
      • 系统各种配置及规则数据
      • 活跃用户的基本信息数据
      • 活跃用户的个性化定制信息数据
      • 准实时的统计信息数据
      • 其他一些访问频繁但变更较少的数据

  • Schema设计对系统的性能影响

    • 尽量减少对数据库访问的请求
    • 尽量减少无用数据的查询请求

  • 硬件环境对系统性能的影响

MySQL常见瓶颈

  • CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候

  • IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

  • 服务器硬件的性能瓶颈：top，free，iostat 和 vmstat来查看系统的性能状态

性能下降SQL慢 执行时间长 等待时间长 原因分析

  • 查询语句写的烂
  • 索引失效（单值、复合）
  • 关联查询太多join（设计缺陷或不得已的需求）
  • 服务器调优及各个参数设置（缓冲、线程数等）

MySQL常见性能分析手段

在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

性能瓶颈定位

我们可以通过 show 命令查看 MySQL 状态及变量，找到系统的瓶颈：

Mysql> show status ——显示状态信息（扩展show status like ‘XXX’）

Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）

Mysql> show innodb status ——显示InnoDB存储引擎的状态

Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等

Shell> mysqladmin variables -u username -p password——显示系统变量

Shell> mysqladmin extended-status -u username -p password——显示状态信息
Explain(执行计划)

是什么：使用 Explain 关键字可以模拟优化器执行SQL查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈

 

 

各字段解释

  • id（select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序）

    • id相同，执行顺序从上往下
    • id全不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
    • id部分相同，执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行

  • select_type（查询类型，用于区别普通查询、联合查询、子查询等复杂查询）

    • SIMPLE ：简单的select查询，查询中不包含子查询或UNION
    • PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
    • SUBQUERY：在select或where列表中包含了子查询
    • DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里
    • UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层select将被标记为DERIVED
    • UNION RESULT：从UNION表获取结果的select

  • table（显示这一行的数据是关于哪张表的）

  • type（显示查询使用了那种类型，从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）

    • system：表只有一行记录（等于系统表），是 const 类型的特例，平时不会出现
    • const：表示通过索引一次就找到了，const 用于比较 primary key 或 unique 索引，因为只要匹配一行数据，所以很快，如将主键置于 where 列表中，mysql 就能将该查询转换为一个常量
    • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
    • ref：非唯一性索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能也会找到多个符合条件的行，多以他应该属于查找和扫描的混合体
    • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需开始于索引的某一点，而结束于另一点，不用扫描全部索引
    • index：Full Index Scan，index于ALL区别为index类型只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
    • ALL：Full Table Scan，将遍历全表找到匹配的行

    tip: 一般来说，得保证查询至少达到range级别，最好到达ref

  • possible_keys（显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）

  • key

    • 实际使用的索引，如果为NULL，则没有使用索引

    • 查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在key列表中

 

explain-key

 

  • key_len

    • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
    • key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

  • ref （显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）

  • rows （根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数）

  • Extra（包含不适合在其他列中显示但十分重要的额外信息）

    1. using filesort: 说明mysql会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中

    2. Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

    3. using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作

    4. using where：使用了where过滤

    5. using join buffer：使用了连接缓存

    6. impossible where：where子句的值总是false，不能用来获取任何元祖

    7. select tables optimized away：在没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

    8. distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作

case:

 

explain-demo

 

  1. 第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name......】

  2. 第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,name from t1 where other_column=''】

  3. 第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】

  4. 第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】

  5. 第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】

 

性能优化

索引优化

  1. 全值匹配我最爱
  2. 最佳左前缀法则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
  3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
  4. 存储引擎不能使用索引中范围条件右边的列
  5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select
  6. is null ,is not null 也无法使用索引
  7. like "xxxx%" 是可以用到索引的，like "%xxxx" 则不行(like "%xxx%" 同理)。like以通配符开头('%abc...')索引失效会变成全表扫描的操作，
  8. 字符串不加单引号索引失效
  9. 少用or，用它来连接时会索引失效
  10. <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描

一般性建议

  • 对于单键索引，尽量选择针对当前query过滤性更好的索引

  • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

  • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引

  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

  • 少用Hint强制索引

查询优化

永远小标驱动大表（小的数据集驱动大的数据集）

slect * from A where id in (select id from B)`等价于
#等价于
select id from B
select * from A where A.id=B.id
复制代码

当 B 表的数据集必须小于 A 表的数据集时，用 in 优于 exists

select * from A where exists (select 1 from B where B.id=A.id)
#等价于
select * from A
select * from B where B.id = A.id`
复制代码

当 A 表的数据集小于B表的数据集时，用 exists优于用 in

注意：A表与B表的ID字段应建立索引。

order by关键字优化

  • order by子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序

  • MySQL 支持两种方式的排序，FileSort 和 Index，Index效率高，它指 MySQL 扫描索引本身完成排序，FileSort 效率较低；

  • ORDER BY 满足两种情况，会使用Index方式排序；①ORDER BY语句使用索引最左前列 ②使用where子句与ORDER BY子句条件列组合满足索引最左前列

  • 尽可能在索引列上完成排序操作，遵照索引建的最佳最前缀

  • 如果不在索引列上，filesort 有两种算法，mysql就要启动双路排序和单路排序

    • 双路排序：MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据
    • 单路排序：从磁盘读取查询需要的所有列，按照order by 列在 buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序

  • 优化策略

    • 增大sort_buffer_size参数的设置
    • 增大max_lencth_for_sort_data参数的设置

GROUP BY关键字优化

  • group by实质是先排序后进行分组，遵照索引建的最佳左前缀
  • 当无法使用索引列，增大 max_length_for_sort_data 参数的设置，增大sort_buffer_size参数的设置
  • where高于having，能写在where限定的条件就不要去having限定了

数据类型优化

MySQL 支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。

  • 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。

    简单就好：简单的数据类型通常需要更少的CPU周期。例如，整数比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较复杂。

  • 尽量避免NULL：通常情况下最好指定列为NOT NULL

分区、分表、分库

MySQL分区

一般情况下我们创建的表对应一组存储文件，使用MyISAM存储引擎时是一个.MYI和.MYD文件，使用Innodb存储引擎时是一个.ibd和.frm（表结构）文件。

当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率

能干嘛

  • 逻辑数据分割
  • 提高单一的写和读应用速度
  • 提高分区范围读查询的速度
  • 分割数据能够有多个不同的物理文件路径
  • 高效的保存历史数据

怎么玩

首先查看当前数据库是否支持分区

  • MySQL5.6以及之前版本：

    SHOW VARIABLES LIKE '%partition%';
复制代码

  • MySQL5.6：

    show plugins;
复制代码

分区类型及操作

  • RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。mysql将会根据指定的拆分策略，,把数据放在不同的表文件上。相当于在文件上,被拆成了小块.但是,对外给客户的感觉还是一张表，透明的。

    按照 range 来分，就是每个库一段连续的数据，这个一般是按比如时间范围来的，比如交易表啊，销售表啊等，可以根据年月来存放数据。可能会产生热点问题，大量的流量都打在最新的数据上了。

    range 来分，好处在于说，扩容的时候很简单。

  • LIST分区：类似于按RANGE分区，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。

  • HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

    hash 分发，好处在于说，可以平均分配每个库的数据量和请求压力；坏处在于说扩容起来比较麻烦，会有一个数据迁移的过程，之前的数据需要重新计算 hash 值重新分配到不同的库或表

  • KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

看上去分区表很帅气，为什么大部分互联网还是更多的选择自己分库分表来水平扩展咧？

  • 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁
  • 一旦数据并发量上来，如果在分区表实施关联，就是一个灾难
  • 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控

随着业务的发展，业务越来越复杂，应用的模块越来越多，总的数据量很大，高并发读写操作均超过单个数据库服务器的处理能力怎么办？

这个时候就出现了数据分片，数据分片指按照某个维度将存放在单一数据库中的数据分散地存放至多个数据库或表中。数据分片的有效手段就是对关系型数据库进行分库和分表。

区别于分区的是，分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。分库分表和分区并不冲突，可以结合使用。

说说分库与分表的设计

MySQL分表

分表有两种分割方式，一种垂直拆分，另一种水平拆分。

  • 垂直拆分

    垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。

  • 水平拆分(数据分片)

    单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。

    水平分割的几种方法：

    • 使用MD5哈希，做法是对UID进行md5加密，然后取前几位（我们这里取前两位），然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。
    • 还可根据时间放入不同的表，比如：article_201601，article_201602。
    • 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。
    • 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二 个表user_0001中，随用户增加，直接添加用户表就行了。

 

 

MySQL分库

为什么要分库?

数据库集群环境后都是多台 slave，基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对master性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

分库是什么？

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

优点：

  • 减少增量数据写入时的锁对查询的影响

  • 由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

但是它无法解决单表数据量太大的问题

分库分表后的难题

分布式事务的问题，数据的完整性和一致性问题。

数据操作维度问题：用户、交易、订单各个不同的维度，用户查询维度、产品数据分析维度的不同对比分析角度。 跨库联合查询的问题，可能需要两次查询 跨节点的count、order by、group by以及聚合函数问题，可能需要分别在各个节点上得到结果后在应用程序端进行合并 额外的数据管理负担，如：访问数据表的导航定位 额外的数据运算压力，如：需要在多个节点执行，然后再合并计算程序编码开发难度提升，没有太好的框架解决，更多依赖业务看如何分，如何合，是个难题。

主从复制

 

复制的基本原理

  • slave 会从 master 读取 binlog 来进行数据同步

  • 三个步骤

    1. master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
    2. salve 将 master 的 binary log events 拷贝到它的中继日志（relay log）;
    3. slave 重做中继日志中的事件，将改变应用到自己的数据库中。MySQL 复制是异步且是串行化的。

     

    img

     

复制的基本原则

  • 每个 slave只有一个 master
  • 每个 salve只能有一个唯一的服务器 ID
  • 每个master可以有多个salve

复制的最大问题

  • 延时

三个范式

  • 第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。
  • 第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。
  • 第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在"A → B → C"的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y

百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

  1. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
  2. 然后删除其中无用数据（此过程需要不到两分钟）
  3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
  4. 与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。
 
 
posted @ 2020-10-17 21:20  布尔先生  阅读(4588)  评论(0)  编辑  收藏  举报