MySQL优化

Mysql的结构体系

配置文件分析（基于Windows环境）

• 主配置文件：‪my.ini

• 二进制日志log-bin：存在于Mysql\Data\DESKTOP-bin.000377

• 错误日志log-error：存在于Mysql\Data\DESKTOP.err

• 查询日志log

• 数据文件

  • 两系统
  • frm文件：存放表结构
  • myd文件：存放表数据
  • myi文件：存放表索引

Mysql架构图

1.客户端：各种语言都提供了连接mysql数据库的方法，比如jdbc、php、go等，可根据选择 的后端开发语言选择相应的方法或框架连接mysql

2.server层：包括连接器、查询缓存、分析器、优化器、执行器等，涵盖mysql的大多数核心服务功能，以及所有的内置函数（例如日期、世家、数 学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

3.存储引擎层（可插拔式）：负责数据的存储和提取，是真正与底层物理文件打交道的组件。 数据本质是存储在磁盘上的，通过特定的存储引擎对数据进行有组织的存放并根据业务需要对数据进行提取。存储引擎的架构模式是插件式的，支持Innodb，MyIASM、Memory等多个存储引擎。现在最常用的存储引擎是Innodb，它从mysql5.5.5版本开始成为了默认存储引擎。

4.物理文件层：存储数据库真正的表数据、日志等。物理文件包括：redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等

执行流程

1. 客户端要与数据库建立连接，需要通过连接器进行TCP握手以及身份校验，连接成功之后就可以操作数据库了
2. 进行一条语句的查询，首先是查询缓存中是否存在，如果存在直接返回，如果不存在就通过分析器分析语法是否正确
3. 语法分析完成之后就通过优化器进行优化，首先得判断是否有索引，有的话判断使用什么索引；其次就是对执行顺序进行优化
4. 通过执行器进行SQL语句的执行

主流存储存储引擎的了解与区分

对比	MyISAM	Innodb
主外键	不支持	支持
事务	不支持	支持
行锁与表锁	只支持表锁	支持行锁和表锁
缓存	只缓存索引，不缓存数据	索引和数据都缓存，内存要求高，内存大小影响性能
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

性能问题

慢的原因？

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

常见的join连接你知道几种？

一共七中：

• 内连接：select * from tableA a inner join tableB b on a.id = b.id

  

• 左连接：select * from tbaleA a left join tableB b on a.id = b.id

  

• 右连接：select * from tbaleA a right join tableB b on a.id = b.id

  

• 去除公共部分的左连接：select * from tbaleA a left join tableB b on a.id = b.id where b.id is null;

  

• 去除公共部分的右连接：select * from tbaleA a rignt join tableB b on a.id = b.id where a.id is null;

  

• 全连接：select * from tbaleA a left join tableB b on a.id = b.id union select * from tbaleA a right join tableB b on a.id = b.id

  

• 去除公共部分的全连接：select * from tbaleA a left join tableB b on a.id = b.id where b.id is null union select * from tbaleA a right join tableB b on a.id = b.id where a.id is null

  

Mysql索引结构

B+索引

数据结构：B+树的非叶子节点全部存储的是索引，而叶子节点存储的全部都是数据，这样相较于B树，B+树的每个节点可以存储更多的索引，树高会更低，而且B+树的叶子节点是由指针进行连接的，这样就可以实现范围查找

优点：

• 因为不再需要进行全表扫描，只需要对树进行搜索即可，所以查找速度快很多。
• 因为 B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。
• 可以指定多个列作为索引列，多个索引列共同组成键。
• 适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

主索引

主索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

辅助索引

辅助索引的叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找，这个过程也被称作回表。

Hash索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

• 无法用于排序与分组；
• 只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

有序数组

解决问题：hash索引是无序的，而且只支持等值查询的情况，那么如何解决呢？

有序数组：不仅可以根据下标直接查询到数据，而且因为它是有序的，所以可以范围查找，但是同样具有缺点，那就是增加和删除的时候会改变当前的数据结构，会讲当前节点的后面节点全部左右移动，这样成本非常高。（这里又引申出hash索引可以增加删除方便，但是不能排序以及范围查询）

适用场景：历史数据，比如：历史订单，账单之类的信息

平衡二叉树

平衡二叉树是有序的，所以支持范围查询，而且它的查询和更新复杂度为O(log(N))，但是同样是不支持来做索引的，因为平衡二叉树在数据很多的情况下，树会很高，这样就会造成查询的成本会很高

B树

B树比平衡二叉树更优，因为在B树的一个节点可以存储多个数据，这样就可以使树更加的矮，但是B树相比较于B+树还是略显不足的

问题

• B树的一个节点存储多大的数据最好呢？

  一页的大小，或者是页的整数倍最好，因为每次磁盘的读取都是读取一页的数据，如果小于一页就造成就会造成资源浪费，如果多余一页但是不是整数倍，也会多读一页，同样会造成资源浪费，因此选择读取的数据为页的整数倍最好

• 什么是回表？

  回表就是进行一个SQL语句查询的时候，这个语句例如：select * from user where name = ‘123’，这个表中存在ID主键索引和name字段索引，那么执行这个SQL语句的时候会先通过name索引找到值为123的Id，然后进行回表，通过id查询这条数据的信息

• 有什么方法解决回表呢？

  索引覆盖，意思就是将查询的返回结果换成ID，因为查询name索引的时候就会获取到ID，这样就不需要通过回表操作再次查询数据

• 什么是最左匹配原则

  例如现在有四个索引，a,b,c,d,那么这个时候我的查询条件是a = 1,b =2,c >3,d= 4，查询的结果列中只会显示a，b，c，因为c是范围查询，d就排不了序了

Mysql索引类型

主键索引

索引列中的值必须是唯一的，不允许有空值。

普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

唯一索引

索引列中的值必须是唯一的，但是允许为空值。

全文索引

只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引。

空间索引

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

前缀索引

在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

其他（按照索引列数量分类）

1. 单列索引

2. 组合索引

   组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

索引优化分析

Explain

explain的作用

参考：(https://www.cnblogs.com/yycc/p/7338894.html)、(https://blog.csdn.net/lvhaizhen/article/details/90763799)

通过explain+sql语句可以知道如下内容：

• 表的读取顺序。（对应id）：id越大越先执行，id一样顺序执行

• 数据读取操作的操作类型。（对应select_type）：普通查询、联合查询、子查询等复杂的查询

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

• type：表示查询所使用的访问类型，type的值主要有八种，该值表示查询的sql语句好坏，从最好到最差依次为：system>const>eq_ref>ref>range>index>ALL。

• 哪些索引可以使用。（对应possible_keys）

• 哪些索引被实际使用。（对应key）

• 表直接的引用。（对应ref）：const（常量）、外键

  

• 每张表有多少行被优化器查询。（对应rows）

• Extra

  • Using filesort：Using filesort表明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。出现Using filesort就非常危险了，在数据量非常大的时候几乎“九死一生”。出现Using filesort尽快优化sql语句。

    • 

  • Using temporary

    使用了临时表保存中间结果，常见于排序order by和分组查询group by。非常危险，“十死无生”，急需优化。

    将tb_emp中name的索引先删除，出现如下图结果，非常烂，Using filesort和Using temporary，“十死无生”。

    

  • Using index

    表明相应的select操作中使用了覆盖索引，避免访问表的额外数据行，效率不错。

    如果同时出现了Using where，表明索引被用来执行索引键值的查找。（where deptid=1）

    如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

索引优化案例

单表优化

通过这个案例，可以看出有两点需要优化，type为ALL是最坏的情况，Extra中出现filesort九死一生的情况，因此需要建立适当的索引进行优化

如何建立索引呢？

观察：首先从查询条件入手，查询条件中存在，category_id、comments、views

现在考虑将这三个字段建立一个联合索引ccv

建完索引之后，进行查询，发现type已经优化成了range，但是filesort依然存在

为什么还是存在呢？

看查询条件，因为我们建立的索引包含三个字段，但是在查询条件中出现comments>1，这里是范围查询，那么在这个索引后面出现的views就相当于索引失效，无法通过comments找到views这个索引。因此对这个索引无法进行索引排序，这样就导致还是需要借助外部索引进行排序，也就是filesort。

如何解决？

通过上述索引，因为中间的comments出现范围查询，导致索引失效，那么现在绕过comments来建立索引是否可以成功呢？

建立联合索引category_id,views

问题解决！！！

两张表优化

两张表如何优化呢？

首先考虑在查询两张表的时候，我们会使用左连接和右连接，那么在进行连接的时候对哪个表建立索引效果会更好呢？

思考

左连接的特性是什么？

左表全部都有，而右表只包含共有部分，反之就是右连接特性

根据这个特性思考，此时需要对这两个表建立索引，如果是左连接，对左表添加索引，那么是不是左表所有的数据都会使用，这时候type就会变成index，而对右表建立索引，只会查询存在对应关系的列，那么右表的type就是ref，这样是不是就知道两张表的时候对哪个表建立索引呢。

三张表优化

join语句优化

• 尽可能减少join语句中的嵌套的循环总次数：“永远用小结果驱动大结果”
• 优先优化嵌套的内层循环
• 保证join语句中被驱动表上join条件字段建立了索引
• 当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要吝啬joinBuffer的设置

覆盖索引

联合索引

最左匹配原则

索引下推

唯一索引和普通索引的选择

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。

除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率

唯一索引一般一直使用内存操作数据，而普通索引会使用change buffer

change buffer的使用场景

因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好，这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价，所以，对于这种业务模式来说，change buffer反而起到了副作用。

查询截取分析

查询优化

优化原则：小表驱动大表

当B表的数据小于A表的时候用in优于exists：

select * from A where id in (select id from B)

等价于：

for select id from B

for select * from A where A.id = B.id

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

select * from A where exists (select 1 from B where B.id = A.id)

等价于:

for select * from A

for select * from B where B.id = A.id

对于order by 的优化

前提：尽量使用index方式排序，避免使用FileSort进行排序

尽可能在索引列上完成排序操作，遵照索引建的最左匹配原则

如果不在索引列上，filesort有两种算法进行排序

1. 双路排序：就是进行两次IO操作，得到最终数据。

   流程：从磁盘读取排序字段，在sortbuffer中进行排序，然后再从磁盘取出其他字段信息

2. 单路排序：主要针对的是sort_buffer进行的，如果此时取出的数据小于sortbuffer，那么只需要一次IO操作就能完成排序，但是如果现在数据量非常大，那么就需要多次的IO并且还会创建tmp文件进行多路合并操作，这样的话比双路排序更加的耗费资源。

针对上述所说的单路排序是在MySQL4之后所更新的，所以如果此时会进行多路合并操作，那么就需要扩大sortbuffer的大小，来提升性能。

最终优化方案

• 尽量遵守最左匹配原则
• 对于升序降序要一致

慢查询日志

slow_query_log，使用慢查询日志可以分析查询比较慢的SQL语句，然后针对这个SQL语句进行优化

批量数据脚本

Show Profile

全局查询日志

事务隔离级别与MVCC

MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用读已提交（READ COMMITTD）、可重复读（REPEATABLE READ）这两种隔离级别的事务在执行普通的SELECT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，数据的可重复读其实就是ReadView的重复使用。

MVCC解决数据丢失问题

MVCC，多版本的并发控制，Multi-Version Concurrency Control。

使用版本来控制并发情况下的数据问题，在B事务开始修改账户且事务未提交时，当A事务需要读取账户余额时，此时会读取到B事务修改操作之前的账户余额的副本数据，但是如果A事务需要修改账户余额数据就必须要等待B事务提交事务。MVCC使得数据库读不会对数据加锁，普通的SELECT请求不会加锁，提高了数据库的并发处理能力。借助MVCC，数据库可以实现READ COMMITTED，REPEATABLE READ等隔离级别，用户可以查看当前数据的前一个或者前几个历史版本，保证了ACID中I的特性（隔离性)。

Innodb是如何实现MVCC的

undo log 不同的事务隔离级别产生的read view 的时期也不相同

Innodb是怎么存储MVCC数据的？

InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的。一个保存了行的事务ID（DB_TRX_ID），一个保存了行的回滚指针（DB_ROLL_PT）。每开始一个新的事务，都会自动递增产 生一个新的事务id。事务开始时刻的会把事务id放到当前事务影响的行事务id中，当查询时需要用当前事务id和每行记录的事务id进行比较。

Mysql语句的执行顺序

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果

例子：

select 考生姓名, max(总成绩) as max总成绩 
 
from tb_Grade 
 
where 考生姓名 is not null 
 
group by 考生姓名 
 
having max(总成绩) > 600 
 
order by max总成绩 

在上面的示例中 SQL 语句的执行顺序如下:

　　 (1). 首先执行 FROM 子句, 从 tb_Grade 表组装数据源的数据

　　 (2). 执行 WHERE 子句, 筛选 tb_Grade 表中所有数据不为 NULL 的数据

　　 (3). 执行 GROUP BY 子句, 把 tb_Grade 表按 “学生姓名” 列进行分组(注：这一步开始才可以使用select中的别名，他返回的是一个游标，而不是一个表，所以在where中不可以使用select中的别名，而having却可以使用，感谢网友 zyt1369 提出这个问题)
　　 (4). 计算 max() 聚集函数, 按 “总成绩” 求出总成绩中最大的一些数值

　　 (5). 执行 HAVING 子句, 筛选课程的总成绩大于 600 分的.

　　 (7). 执行 ORDER BY 子句, 把最后的结果按 “Max 成绩” 进行排序.

常见的索引失效场景

• 使用函数导致索引失效

  错误的例子：select * from test where round(id)=10;
  如何解决？
  create index test_id_fbi_idx on test(round(id));
  然后 select * from test where round(id)=10; 这时函数索引起作用了 1,<> 2,单独的>,<,(有时会用到，有时不会)

• 对索引进行计算操作

  错误的例子：select * from test where id-1=9;

• 或者在建立索引的时候用的是varchar但是查询的时候使用number类型

  错误的例子：select * from test where tu_mdn=13333333333;
  正确的例子：select * from test where tu_mdn=’13333333333’;

• like “%_” 百分号在前.

  解决：使用覆盖索引进行解决

• 单独引用复合索引里非第一位置的索引列.（最左匹配原则）

• 少用or，用or连接会导致索引失效

• 使用is null ，is not null 无法使用索引

• 使用！=或者<>时候，索引失效

• 使用范围查询，导致索引失效·