Client  -  Server  - 存储引擎

优化

RBO 规则优化

CBO 成本优化

8.0 之后取消MySQL缓存

1. 连接器

控制用户链接

2. 分析器

     词法分析 语法分析

3. 优化器（可以查看SQL的执行计划，对应优化 加快查询速度）

  优化SQL语句 规定执行流程

4. 执行器

SQL语句的实际执行组件

查看当前MySQL服务连接

show processlist;

查询当前运行的线程

use `performance_schema`

SELECT  * FROM  events_waits_current

use `performance_schema`

-- 当前正在执行的线程执行情况

SELECT  * FROM  events_waits_current

-- 查看历史线程执行情况

SELECT  * FROM  events_waits_history

-- 哪类SQL语句执行的比较多

SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

-- 哪类SQL的平均执行时间最长

SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC

-- 查询每个阶段的耗时情况

SELECT event_id,EVENT_NAME,SOURCE,TIMER_END-TIMER_START FROM events_statements_history_long WHERE NESTING_EVENT_ID= '事件ID'

调优

更小的通常更好  

按照实际业务类型选择对应的数据格式  tinint  / int

存储IP函数   实际业务中存储成数字类型 效率高于字符类型

INET_ATON  // IP转数字

INET_NTOA // 数字转IP

合理的使用范式和反范式

尽量避免使用null

MySQL 索引数据结构

为什么要选择B+树？

树的发展：  (会因为节点过深导致IO次数过多)

• 二叉树   连式结构
• 二叉搜索树  BST
• 平衡二叉树  AVL   高度不能超过1    左右旋太多
• 红黑树 RBT  旋转+变色

磁盘预读  16K *3      B+树 三层 可以调整

B树   非叶子节点存储data  4096  

B+    叶子节点只存储data  4096000  （千万级别）

MyISAM 和InnoDB

InnoDB B+树叶子节点   查到的直接是整行数据   因为数据结构和数值都是在一个文件里面  （聚簇索引）

MyISAM B+树叶子节点 需要通过数据位置地址 再去数据文件里面读取  因为MyISAM是两个文件（非聚簇索引）

执行计划

system->const->ref->range->index->all

聚簇索引  非聚簇索引

页分裂     页合并

DV  hyperLogLog

哈希索引

CRC32 /CRC64

-- 覆盖索引

-- 当使用索引列查询的时候尽量少使用表达式 把数据的操作放到业务层而不是数据访问层

错误：SELECT id from user where id+1=2

-- 尽量使用主键查询 因为主键查询不会触发回表

-- 使用前缀索引    更小更快 但是有弊端 就是order by  和group by不走索引
如订单号 TCPGX202203154567  使用不重复的片段（2203154567）作为索引 ,TCPGX20 占用多余的索引内存空间

SELECT count(*) as count,LEFT(nick_name,2) as pref FROM `user` GROUP BY pref ORDER BY count desc limit 10;

--   union all    in   or  小优化

set profiling =1;

SELECT *FROM  user where id IN (1,2);

SELECT *FROM  user where id=1  union all SELECT * FROM user where id=2;

SELECT *FROM  user where id =1 or id=2;

show PROFILES;

使用exists

SELECT * FROM user u1 where EXISTS(SELECT 1 FROM user u2 where (id=1 or id=2) and u1.id=u2.id)

强制类型转换并不会进行索引优化

age是varchar

不会走索引SELECT * from  user where age=22

使用索引   SELECT * from  user where age='22'

JOIN的三种方式

1、Using join buffer (Block Nested Loop)

例如A表 Join B表，如TYPE类型是ALL或Index时候，则可以使用连接缓存（Join Buffer）

2、索引join

如果为连接列增加索引,则会通过索引匹配，而不需要到表里扫描

3、普通join

MySQL查询优化

1.查询慢的原因

• 网络
• CPU
• IO
• 上下文切换
• 系统调用
• 生成统计信息
• 锁等待时间

查询优化执行过程

AST 抽象语法树

Apache Calcite

• 语法解析器和预处理
• 查询优化器

-- 将外连接转为内连接  内连接效率高于外连接.

SELECT * FROM user   u left  JOIN  user_boby_info  u1 using(id)  

SELECT * FROM user   u inner  JOIN  user_boby_info  u1 using(id)

-- 等值传播

SELECT u.id FROM user   u inner  JOIN  user_boby_info  u1 using(id)   where u.id>1

SELECT u.id FROM user   u inner  JOIN  user_boby_info  u1 using(id)   where u.id>1 and u1.id>1

-- limit 限制输出

    能使用limit就尽量使用limit

-- 行转列

-- CASE WHEN

SELECT sname,

MAX(CASE cname WHEN 'JAVA' THEN score ELSE 0 END ) 'JAVA',

MAX(CASE cname WHEN 'MySQL' THEN score ELSE 0 END ) 'MySQL'

FROM student_socre GROUP BY sname;

-- 自定义变量    mybatis不支持

 当前时间一周前日期  

set @last_week:=CURRENT_DATE -INTERVAL 1 WEEK;

select @last_week;

-- 连接顺序

  MySQL 优化器 作用 在SQL—1上 对比cost大小可知

select   u1.nick_name,u1.age,u2.height,u2.weight from user u1  INNER JOIN user_boby_info u2 using(id);

show status like 'last_query_cost';

select  STRAIGHT_JOIN u1.nick_name,u1.age,u2.height,u2.weight from user u1  INNER JOIN user_boby_info u2 using(id);

show status like 'last_query_cost';

select  STRAIGHT_JOIN u1.nick_name,u1.age,u2.height,u2.weight from  user_boby_info u2 INNER JOIN  user u1 using(id);

show status like 'last_query_cost';

特定类型的优化查询

1. MySQL count() 函数

SELECT count(1) from user;

SELECT count(id) from user;

SELECT count(*) from user;

-- on 或者 using 字段上使用索引

--  group by order by 使用索引

-- 子查询优化 避免子查询创建临时表

分区表

服务器参数设置

1. character
2. connection

 show VARIABLES  like '%max_connection%';

 show VARIABLES  like '%max_user_connection%';

 show VARIABLES  like '%back_log%';

 show VARIABLES  like '%wait_timeout%';

-- A C I D

-- 原子性  一致性 隔离性 持久性

-- redo log  undolog  

--  bin log

• A 原子性是通过  undo log来实现的
• C 一致性
• I 隔离性 通过隔离级别->锁来实现
• D 持久性  redolog

采用第2种能够保证安全性
一般采用第1种来使用减少IO

RedoLog是循环写（随机写）   记录的是物理日志修改的内容

binlog是追加写 （顺序写） 记录的是逻辑日志，原始逻辑

数据更新的流程

1. 执行器 先从引擎中查找数据，如果在内存中直接返回否则去磁盘中查询并返回
2. 执行器拿到数据后会先修改数据，然后调用引擎接口重新写入数据
3. 引擎将数据同步到内存中，同时写入redo，处于prepare阶段，告诉执行器就绪。
4. 执行器生成当前操作的binlog
5. 执行器调用引擎的事务提交接口，引擎把redo改为commit状态，更新完成。

log参数

1. general_log  查询记录日志

show VARIABLES  like '%general_log%';

2. slow_query_log  慢日志记录记录      long_query_time

show VARIABLES  like '%query_log%';  show VARIABLES  like '%long_query_time%';

Cache参数

show VARIABLES  like '%sort_buffer_size';

show VARIABLES  like '%max_allowed_packet%';

show VARIABLES  like '%thread_cache_size%';

INNODB参数

 0  1最安全  2 性能最高 （会丢失1s数据）

show VARIABLES  like '%innodb_flush_log_at_trx_commit%';

MySQL锁

存储引擎

MyISAM 共享读锁  独占写锁

对表加了read锁后，仅能对加锁的表进行读操作，不能更新表记录，也不能对其他表进行读操作

innodb  共享锁   排它锁

1.共享锁：指多个事务对同一个数据可以共享一把锁，只能读不能修改。

lock in share mode   读共享

2.排它锁 ：不能与其他锁共存。如果一个事务获取了一个数据行的排他锁，其他事物就不能再获取该行的锁，只有获取了当前排它锁的事务可以进行数据的修改和读取。

for update      

3.意向共享锁：表示一个数据行在加共享锁之前必须获得该表的IS锁。

4.意向排它锁：表示一个数据行在加排它锁之前必须获得该表的IX锁。

5.自增锁 ：对自增列的特殊表锁

索引对锁的影响： 加索引的话 是行锁 不加索引退化为表锁。

Mysql 死锁 自动释放 重新开启一个事务。MySQL程序保证自动释放死锁， 后给谁加锁 谁释放。