mysql 调优-优化

mysql 优化

1.能够描述索引的作用及优劣势

2.能够说出索引的底层数据结构

3.能够区别聚簇索引和非聚簇索引

4.能够使用Explain执行计划

5.能够描述SQL优化的方案

00、MySQL体系结构

客户端连接

支持接口：支持的客户端连接，例如 C、Java、PHP 等语言来连接 MySQL 数据库。

第一层：网络连接层

连接池：管理、缓冲用户的连接，线程处理等需要缓存的需求。

第二层：核心服务层

管理服务和工具：系统的管理和控制工具，例如备份恢复、复制、集群等。

SQL 接口：接受 SQL 命令，并且返回查询结果。

查询解析器：验证和解析 SQL 命令，例如过滤条件、语法结构等。

查询优化器：在执行查询之前，使用默认的一套优化机制进行优化sql语句。

缓存：如果缓存当中有想查询的数据，则直接将缓存中的数据返回。没有的话再重新查询。

第三层：存储引擎层

插件式存储引擎：管理和操作数据的一种机制，包括(存储数据、如何更新、查询数据等)

第四层：系统文件层

文件系统：配置文件、数据文件、日志文件、错误文件、二进制文件等等的保存。

01、MySQL：性能优化概述

在应用开发的过程中，由于前期数据量少，开发人员编写的SQL语句或者数据库整体解决方案都更重视在功能上的实现，但是当应用系统正式上线后，随着生产环境中数据量的急剧增长，很多SQL语句和数据库整体方案开始逐渐显露出了性能问题，对生产的影响也越来越大，此时MySQL数据库的性能问题成为系统应用的瓶颈，因此需要进行MySQL数据库的性能优化。

为什么要进行数据库优化

1. 避免网站页面出现访问错误

由于数据库连接timeout产生页面5xx错误

由于慢查询造成页面无法加载

2. 增加数据库的稳定性

很多数据库问题都是由于低效的查询引起的

3. 优化用户体验

流畅页面的访问速度

性能下降的原因

• 数据量太大。

• 没有建立索引。

• 建立的索引失效，建立了索引，在真正执行时有用上建立的索引(通过执行explain来解决)。

• 关联查询太多join查询语句写的不好，各种连接，各种子查询导致用不上索引。

• 服务器调优及配置参数导致，如果设置的不合理，不恰当，也会导致性能下降，sql变慢（专业DBA）。

• 系统架构的问题。

常见优化方案【重点】

• 索引优化: 添加适当索引（index）。

• sql优化: 写出高质量的sql，避免索引失效。

• 关联查询: 不允许超过3张表，如果超过说明你表设计不合理，就进行优化和合并。

• 设计优化: 表的设计合理化(符合3NF，有时候要进行反三范式操作）。

• 架构优化: 分表技术(水平分割、垂直分割)主从复制，读写分离。

• 配置优化: 对MySQL配置优化（配置最大并发数my.ini, 调整缓存大小）。

调整MySQL参数配置（DBA）也需要硬件上的支持

• 考虑其他的存储方式（redis/mongodb/es/solr等）。

• 直接修改MySQL内核（阿里）。

• 硬件优化: 增加硬件（CPU/内存/硬盘）。

02、MySQL：存储引擎

存储引擎介绍

在生活中，引擎就是整个机器运行的核心(发动机)，不同的引擎具备不同的功能，应用于不同的场景之中。

Oracle、SqlServer 等数据库只有一种存储引擎。而MySQL针对不同的需求，配置不同的存储引擎，就会让数据库采取不同处理数据的方式和扩展功能。

简单的理解：MySQL中不同的存储引擎，使用不同的方式将数据保存到文件中。

MySQL 支持的存储引擎有很多，常用的有三种：InnoDB、MyISAM、MEMORY。

特性对比

MyISAM 存储引擎：访问快，不支持事务和外键操作。

InnoDB 存储引擎：支持事务和外键操作，支持并发控制，占用磁盘空间大。(MySQL 5.5版本后默认)

MEMORY 存储引擎：内存存储，速度快，不安全。适合小量快速访问的数据。

注意: MySQL5.5后默认的存储引擎是InnoDB。

查看存储引擎

我们可以通过命令查看MySQL数据库有9种数据存储引擎:

show engines;

修改存储引擎

-- 修改存储引擎为MyISAM

ALTER TABLE 表名 ENGINE = MyISAM;

-- 修改存储引擎为InnoDB

ALTER TABLE 表名 ENGINE = InnoDB;

03、MySQL：磁盘存储和索引概念

目标: 掌握和了解MySQL的索引原理及相关概念

• 磁盘存取示意图

每次执行SQL从磁盘中查找数据称为磁盘I/O，而磁盘IO至少要经历磁盘寻道、磁盘旋转、数据读取等等操作，非常消耗性能，所以对于读取数据，最大的优化就是减少磁盘I/O。

什么是数据结构: 计算机存储、组织数据的方式。常见的数据结构有数组，二叉树，红黑树，堆，栈，队列等。设计这些数据结构的目的就是为：统一数据的存储和快速查找数据。选择不同的数据结构，它性能都不一样。

什么是索引：是帮助数据库快速查询数据的技术。在MySQL中索引使用B+Tree(N叉树)数据结构，索引类似新华字典的索引目录，可以通过索引目录快速查到你想要的字快速查找数据。索引是解决SQL性能问题的重要手段之一，使用索引可以帮助用户解决大多数的SQL性能问题。

查询MySQL一页的大小:

SHOW GLOBAL STATUS LIKE 'innodb_page_size';

• 执行sql的过程

• 会发起一起磁盘IO (寻道 + 旋转 + 读取数据 + 返回数据存储 内存的过程)。7200转/min，旋转一周需要8.33ms，寻道约10ms。

• 如果条件不满足就会不停的寻道和发起IO。

• 怎么解决这个问题呢？没错就是优化表，对表的数据进行重新编排和建立目录映射。其实就是优化数据的存储结构，就是建立索引。

• 小结

• 每次SQL语句都会进全表的匹配查询，每一次的匹配都是一次IO的寻道的过程，这个性能极低。这样为什么当数据越来越大的时候，SQL的执行速度变慢的原因也是罪魁祸首，怎么解决：索引。

• 建立索引的目的: 就是对数据重新整理的过程，目的减少IO的寻道的次数。

• 索引是一种数据结构（B+tree）这种数据结构：就是对表的数据重新整理的过程，把表的记录建立映射关系。

04、MySQL：索引底层实现

目标: 了解MySQL索引底层实现

Btree 还是 B+tree 都是通过最原始的数据的结构 二叉树 演变而来。

4.1 二叉树

参考网站: https://www.cs.usfca.edu/~galles/visualization/BST.html

为了加快数据的查找，可以维护二叉查找树，每个节点分别包含索引键和一个指向对应数据记录的物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取相应的数据，从而快速的检索出符合条件的记录。

二叉树作为索引的缺点：从二叉树的查找过程了来看，最坏的情况下磁盘IO的次数由树的高度来决定。二叉树只能存两个节点，层级越来越大越来越深，发生磁盘的IO会越频繁。

从前面分析情况来看，减少磁盘IO的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树，所以B-Tree强势登场。

4.2 B-Tree

参考网站: https://www.cs.usfca.edu/~galles/visualization/BTree.html

B-tree树即B树，B即Balanced(平衡的意思)，B-Tree又称为多路平衡查找树。因为B树的原英文名称为B-Tree，B-Tree是为磁盘等待外存设备设计的一种平衡查找树。每个节点包含key和data。

系统从磁盘读取数据到内存时以磁盘块block为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，不是需要什么取什么。

B树是一种多路平衡搜索树，它类似普通的二叉树，但是Btree允许每个节点有更多的子节点。

完整示意图:

模拟查找关键字29的过程:

• 找到根节点对应的磁盘块1，读入内存。【磁盘I/O操作第1次】

• 比较关键字29在区间（17,35）。

• 找到磁盘块3，读入内存。【磁盘I/O操作第2次】

• 比较关键字29在区间（26,30）。

• 找到磁盘块8，读入内存。【磁盘I/O操作第3次】

• 在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于二叉树缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

特点说明

• 平衡查询树，它对数据会进行自我平衡，它比二叉树的层级要低，所以查询的性能要比二叉树高很多。

• 和二叉树一样比父节点大的数据存储在右边，小的存储在左边。

• 度（degree)节点的数据存储个数。度越深代表存储的数据越密，树的层级和高度就越低。越利于搜索和存储数据。评价一个索引的好坏一定是进入索引的次数越小越快。

• 节点中数据key从左到右递增排列

缺点说明

• Btree数据是存储到每个节点中，所以每次查询的和维护的时候就会维护索引值又维护了数据，这样会就是造成内存的浪费和性能的消耗。这也是B+TREE优化的地方。

• 为了提升度的长度，还需要对这种数据结构进行优化，所以它的升华版B+Tree诞生了。

4.3 B+Tree

参考网站: https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

在B-Tree基础上进行优化，使其更适合实现外存储索引结构。InnoDB就是存储引擎就是用B+Tree。

在B-Tree中每一个节点存储空间有限，如果data数据较大，会导致每个节点key太小，当数据量很大时也会导致B-Tree深度较大，增大查询的磁盘IO次数，影响查询效率。

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层叶子节点上，而非叶子节点上只存储key值信息，可以大大增大每个节点存储的key值的数量，降低B&#