参考链接1：https://blog.csdn.net/qd2013498006/article/details/119511633
参考链接2：https://blog.csdn.net/qq_35642036/article/details/82820129
参考链接3：https://blog.csdn.net/weixin_43748615/article/details/105781575
参考链接4：https://www.bilibili.com/video/BV1Pb4y1X7hn?p=5
Mysql性能查询
慢SQL原因：
- 查询语句写的不好
- 索引失效
- 关联过多的 join （设计缺陷或不得已的需求）
- 服务器调优以及参数设置
SQL优化的目的是为了SQL语句能够具备优秀的查询性能，实现这样的目的有很多的途径：
- 工程优化如何实现︰数据库标准、表的结构标准、字段的标准、创建索引 阿里：MySQL数据库规范
- SQL语句的优化：当前SQL语句有没有命中索引
Mysql执行计划（Explain详解)
使用EXPLAIN关键字可以模拟优化器执行sQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
参考链接1: MySQL Explain详解
参考链接2：Explain最完整总结--Explain 作用：表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询
**使用格式**：explain + sql语句
1.id
id越大越先被执行，如果id相同，上面的先执行。2.select_type
| 类型 | 描述 | | —- | —- | | simple | 简单查询 | | primary | 外部的主查询 | | devived | 在from后面的子查询， 产生衍生表 | | subquery | 在from的前面的子查询 | | union | 进行的联合查询 |
关闭对mysql对衍生表的合并优化set session optimizer_switch = 'derived_merge=off';
3.table
表示这一列表示该sql正在访问哪一张表。也可以看出正在访问的衍生表。
4.type
type列可以直观的判断出当前的sql语句的性能。type里的取值和性能的优—>劣顺序如下:** null > system > const > eq_ref > range > index > all**
-- null性能最好的，一般在使用了聚合函数操作索引列，结果直接从索引树获取即可-- system很少见 直接和一条记录进行匹配-- const使用主键索引或者唯一索引和常量进行比较，性能也很好-- eq_ref在进行连接查询时，连接查询的条件中使用了本表的主键进行关联-- ref-- 简单查询使用普通列作为查询条件-- 复杂查询在进行连接查询时，连接查询的条件中使用了本表的普通索引列-- range在索引列上使用了范围查找，性能是ok的-- index在查询表中的所有的记录，但是所有的记录可以直接从索引树上获取，(表中字段均加索引)-- ALL全表扫描。就是要从头到尾对表中的数据扫描一遍。这种查询性能是一定要做优化的。
5.possible_keys
显示这一次查询可能会用到的索引。mysql优化器查询时会进行判断，那么内部优化器就会让此次查询进行全表扫描———我们可以通过trace工具进行查看
6.key
7.rows
8.key_len
通过查看这一列的数值，推断出本sql命中了联合索引中的哪几列。 key_len的计算规则
9.extra
extra列提供了额外的信息，是能够帮助我们判断当前sql的是否使用了覆盖索引、文件排序、使用了索引进行查询条件等等的信息
-- unsing index使用了覆盖索引 （指的是当前查询的所有数据字段都是索引列，这就意味着可以直接从索引列中获取数据，而不需要进行查表。使用覆盖索引进行性能优化这种手段是之后sql优化经常要用到的。）-- using wherewhere的条件没有使用索引列。这种性能是不ok的，我们如果条件允许可以给列设置索引，也同样尽可能的使用覆盖索引。-- using index condition查询的列没有完全被索引覆盖，并且where条件中使用普通索引-- using temporary会创建临时表来执行，比如在没有索引的列上执行去重操作，就需要临时表来实现。（这种情况可以通过给列加索引进行优化。）-- using filesortMySQL对数据进行排序，都会使用磁盘骧完成，可能会借助内存，涉及到两个概念︰单路排序、双路排序-- Select tables optimized away当直接在索引列上使用聚合函数，意味着不需要操作表
背景：app_user表：id为主键，name加了普通index索引
Mysql优化细节
索引优化建议（命中索引）
- 对于SQL优化来说，要尽量保证type列的值是属于range及以上级别。
- 不能在索引列上做计算、函数、类型转换，会导致索引失效
- 对于日期时间的处理 转换成范围查找
- 尽量使用覆盖索引
- 使用不等于(!=或者<>)会导致全表扫描
- 使用is null、 is not null会导致全表扫描
- 使用like以通配符开头(’%xxx…”)会导致全表扫描 (使用覆盖索引或者搜索引擎中间件)
- 字符串不加单引号会导致全表扫描
- 少用or或in，MySQL内部优化器可能不使用索引 （使用多线程或者搜索引擎中间件）
- 范围查询优化 （范围大的拆分查找）
Trace工具
在执行计划中我们发现有的sql会走索引，有的sql即使明确使用了索引也不会走索引。
mysql依据Trace工具的结论：-- 开启trace 设置格式为JSON，设置trace的缓存大小，避免因为容量大小而不能显示完整的跟踪过程。set optimier_trace="enabled=on",end_markers_in_JSON=on;-- 执行sql语句-- 获得trace分析结果select * from information_schema.optimizer_trace \G
Order by优化
在Order by中，如果排序会造成文件排序（在磁盘中完成排序，这样的性能会比较差)，那么就说明sql没有命中索引，怎么解决? 可以使用最左前缀法则，让排序遵循最左前缀法则，避免文件排序。
优化手段：
- 如果排序的字段创建了联合索引，那么尽量在业务不冲突的情况下，遵循最左前缀法则来写排序语句。
- 如果文件排序没办法避免，那么尽量想办法使用覆盖索引。all->index
- 对于Group by而言 ：本质上是先排序后分组，所以排序优化参考order by优化。
分页查询优化
```java — 原始 Explain select * from employees limit 10000,10
— 对于主键连续的情况下进行优化:(少见) Explain select * from employees where id>10000 limit 10
— 通过先进行覆盖索引的查找，然后在使用join做连接查询获取所有数据。这样比全表扫描要快 EXPLAIN select * from employees a inner join (select id from employees order by name limit 1000000,10) b on a.id = b.id;
<a name="LgR3J"></a>### Join查询优化<a name="UHvzn"></a>### in、exstis优化在sql中如果A表是大表，B表是小表，那么使用in会更加合适。反之应该使用exists。<br />即`where ...in (小表）` `where ...esxists(大表)`<br /><a name="jwopE"></a>### count优化对于count的优化应该是架构层面的优化，因为count的统计是在一个产品会经常出现，而且每个用户访问，所以对于访问频率过高的数据建议维护在缓存中。<a name="f8eIX"></a># Mysql的锁机制<a name="liIv4"></a>### 锁的定义与分类定义<br />锁是用来解决多个任务（线程、进程)在并发访问同一共享资源时带来的数据安全问题。虽然使用锁解决了数据安全问题，但是会带来性能的影响，频繁使用锁的程序的性能是必然很差的。<br />对于数据管理软件MySQL来说，必然会到任务的并发访问。那么MySQL是怎么样在数据安全和性能上做权衡的呢?——MVCC设计思想。<br />分类<br />**MySQL的八种锁：**1. **行锁（Record Locks）：**作用在索引上的。1. **间隙锁（Gap Locks）：**间隙锁一定是**开区间**，比如（3，5）。间隙锁本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的。3. **临键锁（Next-key Locks）：**临键锁是行锁+间隙锁，即临键锁是是一个左开右闭的区间，比如（3，5]。InnoDB的默认事务隔离级别是RR，在这种级别下，如果你使用select ... in share mode或者select ... for update语句，那么InnoDB会使用临键锁，因而可以防止幻读；但即使你的隔离级别是RR，如果你这是使用普通的select语句，那么InnoDB将是快照读，不会使用任何锁，因而还是无法防止幻读。3. **共享锁/排他锁（Shared and Exclusive Locks）：**共享锁/排他锁都只是**行锁**，与**间隙锁**无关。其中共享锁是一个事务并发读取某一行记录所需要持有的锁，比如select ... in share mode；排他锁是一个事务并发更新或删除某一行记录所需要持有的锁，比如select ... for update。尽管共享锁/排他锁是行锁，与间隙锁无关，但一个事务在请求共享锁/排他锁时，获取到的结果却可能是行锁，也可能是间隙锁，也可能是临键锁，这取决于数据库的隔离级别以及查询的数据是否存在。5. **意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）：**意向共享锁/意向排他锁属于**表锁**，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的**前置条件**。<br />需要重点关注的是IX锁和IX锁是相互兼容的，这是导致上面场景一发生死锁的前置条件6. **插入意向锁（Insert Intention Locks）：**插入意向锁是一种**特殊**的间隙锁，但不同于间隙锁的是，该锁只用于并发**插入**操作。如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点。插入意向锁+共享锁+排他锁涵盖了常用的增删改查四个动作。6. **自增锁（Auto-inc Locks）：**自增锁是一种特殊的**表级锁**，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。6. **预测锁：**这种锁主要用于存储了空间数据的**空间索引**，本文暂不讨论。6. **死锁**：就是开启的锁没有办法关闭，导致资源的访问因为无法获得锁而处于阻塞状态。```java--MYSQL如何查看锁的详情？set global innodb_status_output_locks =1; #设置可查看锁详情set autocommit=0; #关闭自动提交事务commit;begin;sql语句; (select * from ...;)show engine innodb status \G;查看上面Transaction里面的内容
1.从性能上划分:
- 悲观锁：悲观的认为当前并发非常严重，任何操作都是互斥，保证了线程的安全性，但降低了性能；
- 乐观锁：乐观的认为当前并发并不严重，读的时候可以，对于写的情况，在进行上锁;以CAS自旋锁为例，性能高，但频繁自旋会消耗很大的资源；
2.从数据的操作细粒度划分：
- 行锁：对表中的某一行上锁
- 表锁：对整张表上锁（基本不用）
3.从数据库的操作类型划分(悲观锁） :
- 读锁：称为共享锁，对同样数据进行读来说 可以同时进行 但是不能执行写操作；
- 写锁：称为排他锁，上锁之后与释放锁之前，在整个过程之中不能进行任何的并发操作（其他的任务读与写都无法进行）
== 行锁 ==# MySQL 是默认开启事务自动提交的SET autocommit = 0; # 关闭SET autocommit = 1; # 开启 默认的-- 开启事务begin;-- 上行锁 对id = 8 的这行数据上锁update `user` set name='前度' where id = 8;-- 方式2select * from `user` where id = 8 for update;-- 释放锁commit;SET autocommit = 0;---------------------------------------------------------------------------------------------------------== 表锁 ==-- 对表上读锁或者写锁格式lock table 表名 read/write;-- 释放当前锁unlock tables-- 查看表的上锁情况show open tables
MVCC设计思想
MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
事务的特性
- 原子性：一个事务是一个最小的操作单位，要么都成功，要么都失败
- 隔离性：数据库为每个用户开启的事务，不能被其他事务影响
- 一致性：事务提交之前与回滚之后的数据一致
- 持久性：事务一旦提交不可逆 被持久化到数据库中
事务的隔离级别
- read uncommitted(RU_读未提交) : 一个事务读取了另一个事务还没有提交的数据 会出现脏读的情况
- read committed(RC_读已提交) : 已经解决了脏读问题，在一个事务中只会读取另一个事务已提交的数据，会出现不可重复读情况
- repeatable read (RR_可重复读): 默认级别 在一个事务中每次读取的数据都是一致的．不会出现脏读和不可重复读的问题。但会出现幻读情况
- Serializable: 串行化的隔离界别直接不允许事务的并发发生，不存在任何的并发性。相当于锁表，性能非常差，一般都不考虑 通过上行锁来解决幻读问题。解决幻读只能通过锁表。
理解：脏读、幻读、不可重复度
-- 设置隔离级别set session transaction isolation level 隔离级别;隔离导致的一些问题脏读： 一个事务读取了另外一个事务未提交的数据不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同虚读，幻读：是指在一个事务内读取了别的事务插入的数据，导致前后读取不一致（一般是行影响，多了一行）
Innodb每次在执行数据插入的时候，数据必须要跟某个索引列绑定。索引列的选择顺序为：主键 > 唯一键 > 大于自动生成的row_id
理解RC和RR隔离级别
部分面试题
为什么非主键索引的叶子节点存放的数据是主键值?
如果普通索引中不存放主键，而存放完整数据，那么就会造成：
- 数据冗余，虽然提升了查询性能，但是需要更多的空间来存放冗余的数据
- 维护麻烦:一个地方修改数据，需要在多棵索引树上修改。
为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?
Mysql 为什么建议 innodb 表要建一个主键？
- 如果有一个主键，可以直接使用主键建索引
- 如果没有主键，会从第一列开始选择一列所有值都不相同的，作为索引列
- 如果没有选到唯一值的索引列，mysql 会建立一个隐藏列，维护一个唯一id（rowid），以此来组织索引
为什么推荐使用整型作为主键?
- 在索引中查找数据时，减少比较的性能。
- 使用整形作为主键相比字符型可以节省数据页的空间。
- 构建索引 b+ 树时，为了保证索引的有序性，使用整形可以避免页分裂。
主键为什么要自增?
- 索引结构 b+ 树，具有有序的特性。
- 如果主键不是自增的，在进行增删数据的时候，会判断数据应该存放的位置，进行插入和删除，为了保持平衡，会对数据页进行分裂等操作移动数据，严重影响性能，所以主键需要是自增的，插入时，插入在索引数据页最后。
[
](https://blog.csdn.net/qd2013498006/article/details/119511633)
若有收获，就点个赞吧
0 人点赞
