数据库小高手之MySQL优化

MySQL优化指南

本学习笔记学习自周阳老师，是我个人学习的总结和分享，如有不当之处请指教；因为个人原因，本次演示都建立于win10系统上，建议大家在linux环境下操作，请大家包涵

1. MySQL架构图

相比其它数据库，MySQL 可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

1.1 架构详解

1.1.1 连接层

最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具实现的类似于 tcp/ip 的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于** SSL **的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

1.1.2 服务层

Management Serveices & Utilities 系统管理和控制工具
SQL Interface SQL 接口。接受用户的 SQL 命令，并且返回用户需要查询的结果。比如 select from 就是调用 SQL Interface
Parser 解析器。 SQL 命令传递到解析器的时候会被解析器验证和解析
Optimizer 查询优化器。 SQL 语句在查询之前会使用查询优化器对查询进行优化，比如有 where 条件时，优化器来决定先投影还是先过滤
Cache & Buffer 查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存，权限缓存等

1.1.3 引擎层

存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同
的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取

1.1.4 存储层

主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互

1.2 引擎介绍

我们可以通过 show engines;和 show variables like '%storage_engines%'; 来查看我们的mysql提供了什么存储引擎和当前默认的存储引擎

在mysql安装目录下bin目录中，通过 mysql -uroot -p 命令进入mysql控制台
可以看到默认是有 **MyISAM **和 InnoDB

1.2.1 MyISAM 和 InnoDB对比

MyISAM InnoDB
主外键 no yes
事务 no yes
行表锁 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 行锁，只锁一行，适合高并发操作
缓存 只缓存索引，不缓存真实数据 不仅缓存索引还缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响
表空间 小 大
关注点 性能 事务
默认安装 yes yes

2. 性能优化

2.1 为什么sql性能会下降

  1. sql写的烂
  2. 索引失效
    单值索引：create index idx_user_name on user(name); 复合索引：create index idx_user_nameEmail(name,email);
  3. 关联查询太多 join
  4. 服务器调优及各个参数设计（缓冲、线程数等）

2.2 sql执行顺序

我们写sql的顺序

mysql真正执行sql的顺序

2.3 JOIN

2.3.1 常见的 Join 查询图

需要注意的是，图中的全连接的语句，mysql不支持，但是我们可以把它堪称是左连接加右连接，重合部分去重（利用union进行拼接，自带去重）；同理，最后一种情况也可以看成是左独有加右独有去重

3. 索引

索引（Index）是帮助 MySQL 高效获取数据的数据结构（≈排好序的快速查找数据结构）；

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

我们一般所说的索引指的是B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集/次要/复合/前缀/唯一索引默认都是使用B+树索引，统称索引。除了B+树类型索引外，还有哈希索引（hash index）等

3.1 索引优劣势

优势：

  • 提高数据检索效率，降低数据库IO成本（图书馆检索）
  • 降低数据排序成本，降低CPU消耗
    劣势：
  • 索引也是表，保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的
  • 虽然索引极大的提高查询速度，但也会降低更新表的速度，因为更新表时，MySQL不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
  • 索引知识提高效率的一个因素，如果我们的MySQL有大数据量的表，那我们就需要花时间按去研究建立最优秀的索引，或优化sql语句

3.2 索引的分类

  1. 单值索引：只包含单个列，一个表可以有多个单列索引
  2. 唯一索引：索引列的值必须唯一，但允许有空值
  3. 复合索引：包含多个列

3.2.1 创建索引基本语法

  1. 创建：
  • CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
  • ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
  1. 删除：
  • DROP INDEX [indexName] ON mytable;
  1. 查看：
  • SHOW INDEX FROM table_name\G;
  1. 使用ALTER命令：
    有四种方式来添加数据表的索引
  • ALTER TABLE tbl_name ADD PRIMARY KEY (colimn_list);：该语句添加一个主键，意味着索引值必须唯一且不能为NULL
  • ALTER TABLE tbl_name ADD UNIUQE index_name(column_list);：该语句创建索引的值必须唯一（除null外，null可能会出现多次）
  • ALTER TABLE tbl_name ADD INDEX index_name(column_list);：添加普通索引，索引值可出现多次
  • ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list);：该语句指定了索引为 FULLTEXT，用于全文索引

3.3 索引数据结构

主要包括有BTree索引、Hash索引、full-text全文索引和R-Tree索引，这里只针对BTree索引进行详细讲解，其他的请大家自行课外学习

3.3.1 BTree索引

以下就是一个BTree索引的例子

磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3，P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如 17、35 并不真实存在于数据表中

现实中，3 层的 b+树可以表示上百万的数据，如果上百万的数据查找只需要三次 IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次 IO，那么总共需要百万次的 IO，显然成本极高

3.4 索引的应用

3.4.1 适合创建索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 查询中统计或分组字段
  5. 查询中排序的字段，排序字段若通过索引去访问将极大提高排序速度

3.4.2 不适合创建索引

  1. 频繁更新的字段不适合创建索引（每次更新不单单是更新记录还会更新索引）
  2. where条件中用不到的字段不创建索引
  3. 表记录太少
  4. 数据列包含许多重复内容，建立索引无太大意义

索引的选择性：指索引列中不同值的数目与表中记录数的比；如表有2000条记录，表索引列有1980个不用的值，那这个索引的选择性为1980/2000=0.99；一个索引的选择性越接近1，则这个索引的效率越高

4. Explain 性能分析

影响mysql性能的主要原因主要有MySQL Query Optimizer，硬件瓶颈和Explain，这里主要是学习Explain的相关知识，前两个大致过一下

4.1 MySQL Query Optimizer

MySQL中有专门负责 select 的优化器模块，根据特定算法得出最优的Query执行计划（不一定是DBA认为最优的）；

4.2 硬件瓶颈

  • cpu：cpu的饱和一般发生在数据装入内存或从磁盘读取数据时
  • io：发生在装入数据远大于内存容量时
  • 服务器：yop，free，iostat和vmstat来查看系统的性能状态

4.3 Explain

Explain（执行计划）；使用explain关键字可以模拟优化器执行sql查询语句，从而指导mysql时如何处理我们的sql语句的。即分析你的查询语句或是表结构的性能瓶颈

4.3.1 Explain能干嘛？

  • 得到表的读取顺序
  • 得到数据读取操作的操作类型
  • 知道哪些索引可以使用
  • 知道哪些索引被实际使用
  • 得到表之间的引用
  • 知道每张表有多少行被优化器查询

4.3.2 怎么玩？

我们在数据库的表中执行 explain select * from 表名可以看到如下场景

可以看到运行执行计划后，出现了一个新的表格信息，下面我们就对这个表格信息进行讲解

4.3.3 详解Explain

  1. id*：select查询的序列号，表执行select子句或操作表的顺序；主要分成三种情况，
  • id相同，执行顺序由上至下
  • id不同，若是子查询，id序号会递增，id值越大，优先级越高，越先被执行
  • id有相同有不同时，先执行大id，然后相同id按顺序从上至下执行
  1. select_type：查询类型，有如下分类
  • SIMPLE：不包含子查询或union
  • PRIMARY：若包含任何复杂的子部分，最外层查询就被标记成PRIMARY
  • SUBQUERY：在select或where列表中包含了子查询
  • DERIVED：在from列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
  • UNION：若第二个select出现在union后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为DERIVED
  • UNION RESULT：从union表获取结果的select

  1. table：显示该行数据是关于哪张表

  2. type*：显示查询属于哪种类型；常见的类型优先级 system>const>eq_red>ref>range>index>ALL，一般至少要保证达到 range 级别，最好达到ref

  • system：表只有一行数据（等于系统表），少见，忽略吧
  • const：通过索引一次就找到，用于比较 primary key或unique索引，因只匹配一行数据，所以很快啊！如将主键之于where列表中，mysql就能将该查询转换成一个常量
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质也是一种索引访问，返回所有匹配某个单独值的行，但它可能会找到多个符合条件的行，所有它应该属于查找和扫描的混合体
  • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在where中的between、>、<、in等
  • index：与all的区别为只遍历索引树，一般比all快
  • all：遍历全表找到匹配行

  1. possible_keys：可能会用到的一个或多个索引，但不一定真的用到

  2. key*：实际用到的索引，查询中若使用了覆盖索引，则该索引仅出现在key列表中

  3. key_len：索引用到的字节数，是最大可能长度，并非实际使用长度（即是由表定义计算得到的，而非表内检索出），在不损失精确性的前提下，越小越好

  4. ref：显示索引的哪一列被使用，如果可能，是一个常数

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

  6. Extra*：包含不适合在其他列中显示但十分重要的额外信息

  • Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取；mysql中无法利用索引完成的排序操作称为“文件排序”
  • Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于order by和group by
  • Using index：表示相应的select操作中使用了 覆盖索引（Covering Index），避免访问了表的数据行，效率高；如果同时出现using where ，表明索引被用来执行索引键值的查找；反之表明索引用来读取数据而非执行查找结果
  • Using where：where过滤
  • Using join buffer：连接缓存
  • impossible where：where子句的值总是false，不能用于获取任何元组
  • select tables optimized away：在没有 group by 子句时，基于索引优化MIN/MAX操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
  • distinct：优化distinct操作

覆盖索引：即select的数据列只用从索引中就能够获得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件（查询列要被所建的索引覆盖）
如果要使用覆盖索引，要注意select列表中只取出需要的列，不可 select *，因为如果将所有字段一起做索引会导致文件过大，查询性能下降

5. 索引优化

join语句的优化；尽量减少NestedLoop的循环总次数，即永远用小结果集驱动大的结果集；优先优化NestedLoop内层循环；保证join语句中被驱动表上join条件字段已经被索引；当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要吝啬JoinBuffer的设置

5.1 优化法则

  1. 全值匹配我最爱
  2. 最佳左前缀法则*：指查询从索引的最左前列开始并且不跳过索引中的列（在索引了多列情况下）
  3. 不在索引列上做任何操作（计算、函数（自动/手动）类型转换），会导致索引失效而转向全表扫描
  4. 存储引擎不能使用索引中范围条件右边的列
  5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
  6. mysql在使用不等于（!= 或 <>）时无法使用索引会导致全表扫描
  7. is null ，is not null也无法使用索引
  8. like以通配符开头（'%abc...'）mysql索引失效会编程全表扫描的操作
  9. 字符串不加单引号会导致索引失效
  10. 少用 or，用它来连接时索引会失效

索引失效（应该避免）：定值、范围还是排序，一般order by是给个范围；group by 基本上都需要进行排序，并产生临时表

5.2 一般性建议

  • 对于单键索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引时，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
  • 在选择组合索引时，尽量选择可以包含当前query中的where子句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

优化小口诀
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；

posted @ 2021-02-25 20:03  刘条条  阅读(197)  评论(0)  编辑  收藏  举报