MySQL优化与原理笔记

DD
128 阅读12分钟

MySQL优化与与原理

一、MySQL存储原理

1.1 索引

1.1.1 索引存储位置

  • Innodb引擎：

    • 索引和数据放在一个文件中xxxx.ibd

  • MyISAM引擎：

    • 索引和数据分开两个文件来存储

      • 数据文件：xxxx.MYD
      • 索引文件：xxxx.MYI

注：windos下，还会多一个文件存储表的结构xxx.frm

1.1.2 索引的分类

  • 主键索引

    • 一张表的主键，是自带索引的效果，所以在创建表的时候，一定要带主键

  • 普通索引

    • 为普通的列创建索引
     #格式
 create INDEX 索引名称 on  具体的表(列)
 #示例
 create index idx_name on tb_index_demo(NAME)

  • 唯一索引

    • 在普通索引的基础上，列的值是唯一的。唯一索引的性能要优于普通索引。
     #示例
 create unique index idx_unique_name on tb_index_demo1(NAME)

  • 组合索引（联合）

    • 在一个索引中包含了表中多个列。联合索引有很多的细节，比如最左前缀法则，及如何使用该法则来命中。
     #格式
 create INDEX 索引名称 on  具体的表(列名1，列名2)
 #示例
 create index idx_book_author on tb_book_author(book_id,author_id)

  • 全文索引

    • 搜索引擎使用的就是全文索引的概念，不会用mysql做全文检索。
    • MYSQL中只有MYISAM引擎支持全文索引。

1.2 数据结构

1.3 MySQL存储引擎

InnoDB把数据和索引存放在一个文件里，MyIsam把数据和文件分开存储。

1.2.1 MYISAM

1665061538043.png

1.2.2 INNODB

1665062284225.png

1.4 常见索引面试题：

问题一：为什么非主键索引的叶子节点存放的数据是主键值？

1665064128290.png

两个原因：

  • 节约空间：冗余存放相同的数据在多个索引树会造成空间的浪费
  • 简化修改数据时的操作：如果冗余放相同数据到多个索引树上，那么数据在进行修改时，相关的每个索引树都要修改数据，而不是只修改一份。
问题二：为什么Innodb表必须创建主键？

因为辅助（普通索引）是需要存放主键索引数据的，即使在建表的时候，忘记创建主键，MYSQL也会自动创建一个临时的主键索引，用来定位数据。

问题三：为什么使用主键时推荐使用整型的自增主键？

使用整型而不是字符串，这样排序大小性能更好。

如果不适用自增主键，那么索引叶子结点是从小到大排列，不规律的主键会造成更多索引树的自旋。从而影响性能。

1.5 联合索引

1.5.1 联合索引在底层是如何存储的？

在联合索引树上，节点中的数据存放的是联合索引每个列的内容

1665067300768.png

1.5.2 最左前缀法则：

创建了联合索引后，在使用sql查询时，能否命中索引，就看查询条件是否遵循了最左前缀法则。如果遵循了，就能命中索引，而不会全表扫描

 #例子：
 create index idx_a_b_c on tb_tmp(a,b,c)
 ​
 select *from  tb_tmp where a=1 and b=2 and c=3;  可以
 select *from  tb_tmp where a=1 and b=2 ;  可以
 select *from  tb_tmp where b=2 and c=3;  不可以
 select *from  tb_tmp where a=1 and c=3;  只命中了a的索引
 select *from  tb_tmp where b=2 and c=3 and a=1;  可以（mysql内部有优化器，将a索引放在前面）

1.6 SQL优化

即使我们在创建数据库表时创建了索引，但是在实际使用的sql语句中缺没有命中索引，而造成了慢查询，从而导致整个接口的性能差。

1.6.1 为什么要做SQL优化？
  • 防止慢查询，导致接口性能较差。接口性能1秒以内，甚至200ms以内。
  • 数据库的名称不规范，表的结构不规范，导致使用时出现了问题，对生产环境造成了影响。

1.6.2 如何做SQL优化

  • 创建表时必须使用INnodb引擎
  • 创建表时一定要创建主键，主键用无符号的整数自增主键
  • 如果查询的列是普通列。又想提高性能，那么给这一列加上索引。
  • 一张表中最多5个索引列
  • 索引的应用场景是读多写少
  • 在完成建库建表遵守各种规范后，sql的性能是否ok，就需要关心sql语句是否能命中索引。

1665068728369.png

1665068914627.png

1665069220288.png

1665069913746.png

1665070682218.png

1.7 什么会导致索引失效？

  • 查询条件包含or，可能导致索引失效
  • 如果字段类型是字符串，where时一定用引号括起来，否则索引失效
  • like通配符可能导致索引失效。 并不是用了like通配符，索引一定失效，而是like查询是以%开头，才会导致索引失效。
  • 对索引列运算（如，+、-、*、/），索引失效
  • 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效
  • 索引字段上使用is null， is not null，可能导致索引失效
  • 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效
  • mysql内部优化器估计使用全表扫描要比使用索引快,则不使用索引

二、SQL性能查询工具---Explain

2.1 SQL优化概述

1665074124262.png

2.2 MySQL内部优化器

2.2 Explain详解

1665076463303.png

1）select_type列

该列描述了查询的类型：

  • simple：简单查询
  • primary：外部的主查询
  • derived：在from后面的子查询，会产生衍生表
  • subquery：在from的前面进行的子查询
  • union：进行的联合查询

来自简单的查询：simple类型

 explain select *from tb_book where id=1;

来自复杂的查询：除simple外的其他类型

 explain select (select 1 from tb_author where id=1) from (select * from tb_book where id=1) der;

1665075946676.png

分析：

1665076094537.png

2）table列

这一列表示改sql正在访问哪一张表。也可以看出正在访问的衍生表N会自增

3）type列

通过Type列，可以直接看出SQL语句的查询性能，性能从小到大的排列：

null > system > const > eq_ref > ref > range > index > ALL

不同级别的情况：

  • null：

一般在查询时使用了聚合函数，于是直接从索引树里获取数据，而不用查询表中的记录。

 explain select min(id) from tb_book;
  • system：

是const的特殊情况，一般在衍生表里，直接匹配一条记录，就是system。

  • const:

在进行查询时，查询的条件，使用了主键列或者唯一索引列的值与常量进行比较，这种性能是非常快的，所以是const

  • eq_ref:

在进行连接查询时，连接查询的条件使用了本表的主键进行关联，因此这种类型的sql就是eq_ref.

 explain select *from tb_book_author left join tb_book on tb_book_author.book_id=tb_book.id;

1665077657288.png

  • ref:

简单查询里：

使用普通索引列作为查询条件。

复杂查询里：

在进行连接查询时，连接查询的条件中使用了本表的联合索引列，这种类型就是ref。

1665077670296.png

  • range:

在索引列上使用了范围查找，性能是ok的。

 explain select *from tb_author where id>1;
  • index:

在查询表中的所有记录，但是所有的记录可以直接从索引树上获取，因此这种SQL的查询类型就是index。

 explain select *from tb_book;

tb_book中的所有列：所有列均为索引列。

  • ALL：

全表扫描。

4) id列

在复杂查询中，多条SQL语句，谁的id大，谁先执行。如果多条sql语句的id一样大，那么谁在上面，谁先执行。

5）possible_keys列

这一次查询可能使用的索引（名称）。为什么要设计possible_key这一列。因为在实际Mysql内部优化器做优化选择时，虽然我们写的sql语句是使用了索引列，但是MySQL内部优化器发现，使用索引列查找的性能并没有全表扫描的性能好，于是mysql内部优化器就会使用全表扫描。

 explain select *from employee name like  'custom%'

1665078818143.png

6) key列

MySQL内部优化器最终选择是否使用索引来进行查找。

7) key_len列

这一列的主要作用，通过查看这一列的数值，推断出本sql中选择了联合索引的哪几列。

1665079367153.png

1665079396783.png

8）extra列

1665079441288.png

1665080047803.png

1665080264707.png

三、MySQL优化实战

1.order by 优化

1665125257701.png

2.group by 优化

group by 本质上是先排序后分组，所以排序优化参考order by优化

3.文件排序的执行原理

mysql在进行文件排序时，会设置一个缓冲区，比较要排序的所有列的数据大小之和，与max_length_for_sort_data(默认1024个字节)进行比较。

  • 单路排序：比该变量小，所有的数据会放到缓冲区里进行排序。
  • 双路排序：比该变量大，只把排序字段和主键字段放到缓冲区里进行排序，完成后使用主键再做一次回表查询。

1665125918471.png

4.分页查询优化
 Explain select *from employees limit 100000,10;

这条SQL会把前面100000条数据全部查询出来，再找之后的10条，最后把前面的100000条数据舍弃掉。这样很影响性能。

如果在主键连续的情况下可以进行以下的优化：

 Explain select *from employees where id>100000 limit 10;

如果主键不连续：

1665128654810.png

避免了全表扫描。

5.join查询优化

1665129567577.png

上面两个例子，大表（数据量大的）和小表（数据量小的）进行join查询时，如果查询条件都设置了索引，那么会使用NLJ算法，性能是ok的。

注意：无论小表和大表在join时书写的位置，mysql内部优化器都会选择让小表驱动大表，也就是从小表中获取数据，去大表中查。

如果查询条件没有使用索引，那么就会发生BNLJ算法，通过创建内存缓冲区来实现join查询，对性能影响较大。

两个概念：

  • NLJ（nested loop join）：嵌套循环join
  • BNLJ（block nested loop jon）：块嵌套循环join

结论：

在使用jon查询时，join的两张表的关联字段：数据类型、长度一定要一致，并且都要建立索引。

6.in和exists查询优化

1665129968485.png

1）in（先查in里面的数据）

in的应用场景是：小表驱动大表。

 select *from A where id in (select id from B)
 #相当于：
 for （select id from B）{
     select *from A where A.id=B.id
 }
2）exists(先查exists前面的表)
 select *from A where exists (select 1 from B where B.id=A.id)
7.count优化

在经常使用count做总数统计的场景下，建议将总数维护在redis中，使用缓存提高访问效率，也减少数据库的压力。

四、MySQL的各种锁

1.锁的定义

锁是用来协调在多个线程并发访问统一资源时带来的安全问题。如果频繁的使用锁，在并发非常大的时候，会带来性能问题。

频繁用锁必然会造成性能问题，但不用锁，又会造成安全问题，MySQL在安全问题和性能方面是如何权衡的。

2.锁的分类

1）从性能上划分：乐观锁和悲观锁
  • 乐观锁（并发性能比较好）：CAS自旋锁，是非常典型的乐观锁。乐观的认为，当前系统环境下的并发量并不是很大，当真的有并发发生的时候，才会去上锁。
  • 悲观锁（并发性能要弱）：悲观的认为当前系统环境下并发情况是非常严重的，所有任务在执行时都要上锁。保证了安全性，牺牲了并发性。
2）从数据库操作的类型上划分：读锁和写锁

这两种所都是属于悲观锁

  • 读锁（共享锁）：对于读取同一行数据的并发来说，是可以同时进行的，但是不能写
  • 写锁（排他锁）：在上了写锁后及释放写锁之前，所有其他并发操作（包括读和写），都不能进行。
3）从数据操作粒度上来划分：表锁和行锁
  • 表锁：对整张表进行上锁。
  • 行锁：对表中的某一行记录进行上锁。
表锁

MyISAM默认是支持表锁的，也就是说在默认情况下，多个线程并发操作时，一个线程操作MyISAM表后，相当于获取了该表的表锁，其他所有操作都不能进行。

InnoDB如何上表锁：

  • 手动上锁：
 # 格式
 lock table 表1 read/write,表2 read/write
 # 示例
 lock table tb_book read 读锁。

读锁：当前会话能读能写，其它对话只能读不能写。并且当前对话不能够读取其它表的数据。（共享）

写锁：当前会话能读能写，其它会话则不行（排他）

  • 查看当前会话获取的所有锁
 show open tables;
  • 释放当前会话的所有锁，不会把其它会话持有的锁释放掉
 unlock tables;
行锁

InnoDB支持行锁，在并发事务里，每个事务的读写删的操作，相当于上了行锁。可以通过for update为select上行锁

行锁的特点：上锁的开销大,加锁速度慢，但是细粒度更细，并发性能更好。

上行锁的两种方式：

  • 更新或者插入：update / insert
  • select 。。。for update；

3.事务的特性

  • 原子性：一个事务是一个最小（原子）的操作单位，一个事务中多条sql语句要么同时成功，要么同时失败。
  • 一致性：事务提交之前和回滚之后的数据是一致性的。
  • 持久性：一旦事务被提交，对数据的影响是持久的
  • 隔离性：多个事务在并发操作下提供了一套隔离机制，通过设置不同的隔离级别会有不同的并发效果。

4.事务的隔离级别

  • read uncommitted：读未提交。一个事务中读到了另一个事务没有提交的数据，这种情况成为脏读。
  • read committed：读已提交。一个事务中能读到另一个事务中已经提交的数据，那么就出现了不可重复读，也就是说多次读取的数据有可能不一致。
  • repeatable read（mysql 默认的隔离级别）：可重复读。就是为了解决不可重复读的问题。会出现幻读（虚度）的情况：

在一个事务A中插入数据时，该条数据已经被事务B插入了，那么在事务A中就不能进行插入，但事务A的查询依然是查不到该事务的。

解决方式：通过上行锁，或者设置隔离级别为：Serializable

  • Serializable：序列化

5.MVCC思想

1665135064610.png

6.死锁

所谓的死锁就是在多个并发中，事务A和事务B都持有对方需要的锁，且双方都无法释放手中的锁。

1665135608895.png

7.间隙锁

所谓的间隙锁，就是通过范围来上锁，对满足范围的区间实现上锁的效果。

1665135674352.png

avatar
文章
阅读
粉丝