mysql

mysql造成cpu飙升？

mysql占用cpu过高

造成飙升原因？

慢查询： 查询语句不当，索引不当

mysql配置不当：缓存设置太小 连接数过多

硬件资源限制：cpu核心 内存

mysql版本过低：

解决？

查询语句：查询范围过大–定期清理无用数据

​ 避免使用select*，避免使用不必要的子查询，避免使用join查询过多表

索引不当：没有索引，创建索引， 过多索引，避免不必要的索引

mysql：配置不当 修改缓存大小， innodb_buffer_pool_size, 存储引擎缓存池大小

​ innodb_sort_buffer_size, 排序期间的内存大小

​ innodb_long_buffer_size 日志缓存大小

​ 修改最大连接数：max_connections

硬件资源： 增加cpu核心数 扩展内存

mysql版本过低

如何定位和优化SQL语句造成的性能问题？

定位：通过慢查询日志中 执行时间异常较长的sql语句

​ explain分析执行计划 索引 sql语句

性能优化：表设计是否合理

​ 索引是否合理：设置了索引到那时没有走索引

​ 没有索引，创建索引

​ 索引过多 ，精简索引。索引是一个b+树 占用资源且要维护。

​ 数据量是否过大： 过大造成b+数的深度增加（4） 分表，减小查询范围

​ sql语句是否合理： 检查数据类型

​ 检测查询条件：子查询

​ join语句

​ …

什么是视图？为什么使用视图？

视图：又叫虚表，是一组数据的逻辑表示，本质是一条select语句，结果集赋予一个名字叫视图。视图

作用：对于复杂的查询可以多表联合查询，可对表数据计算，过滤 聚合等操作。

使用：

创建
查询

视图： 一种逻辑表（逻辑上关联各个表），通过sql定义createview_name as =SQL，用来创建视图的表叫基表，用过视图可以展现基表的部分或全部内容。

常用于查询，可但一般不会用于增删改操作；

为什么使用视图？

简单可复用，无需关心基表的结构，关联条件和删选条件，只关心获取结果

安全：基表不对用户开放，视图对用户开放

数据独立：屏蔽基表的

SQL语句的执行过程

连接池–一个连接一个线程–sql语句–通过语法树检测sql语法–解析器过滤–制定多个执行计划–选最优执行—存储引擎来执行语句；

连接器：

分析器：语法分析

查询缓存：k_v缓存，命中直接返回，否则继续执行

​ 8.0版本没缓存

优化器： 制定执行计划，选择最优计划。

执行器： 根据执行计划，由存储引擎操作数据，并返回客户端

写undolog（dml）：用于回滚事务/记录历史版本信息

目标页是否在缓存中： 在 ： 唯一索引 检查数据是否冲突 ， 更新内存

​ 普通索引 更新内存

​ 不在：唯一索引 将数据页加载到内存

​ 普通索引 在changebuffer中更新记录

写redo log （物理日志） ： 用于事务奔溃恢复，将随机写变为顺序写

写bin log （） ： 用于数据备份和主从复制

提交事务：

redolog刷盘

binlog刷盘 ：两阶段提交保证逻辑一致

索引什么时候会失效

索引：是一种有序的数据结构，按照单个或多个列的只进行排序，每个索引对应一个B+树(一个修改可能造成多个b+树的修改) b+树可以使得可以快速定位到查询位置数据，而不需要全表扫描。

使用场景：where

​ group by ：

​ order by：

不适合使用索引的场景：没有where/group by /order by；区分度不高的列；经常修改的列；表的数据量少

索引失效：左模糊匹配：造成全表扫描

​ 索引参与运算：索引列使用函数；索引列进行表达式运算；索引列参与隐士转换

​ where or 非索引字段

​ 不符合最左匹配原则

explan:  事务逻辑表

sql中索引的作用

索引：一种有序的数据结构b+树,按照单个或多个列的值进行排序。查询ologn

目的：提升搜索效率

场景： where ，group by ，order by

索引代价： 占用资源 （一个表最大创建6个索引）

​ 时间（维护b+树的代价-表一个字段修改，会修改多个b+树）

​ dml（增删改）操作

主键索引与唯一索引

主键索引：非空唯一索引—设置表中的一列为主键，那此列中的值不能有空值，不能有重复的值。

一个表有且仅有一个主键索引。primary key（‘id’）是一个特殊的b+树，包含所有行信息；

非主键索引是一种辅助索引，只记录索引信息

显示设置：primary key 显示： 有非空唯一索引：选择第一个非空唯一索引作为主键

​ 无非空唯一索引：系统自动生成主键

唯一索引：索引列不可有重复值，但可有空值； unique （‘id’）

注意：创建一个表创建了索引：那么再插入值时有要求，不能为空，不能重复

区别：主键索引对应所有的数据信息

​ 唯一索引再在没确认为主键时，对应数据只包含索引信息及主键信息及主键信息

聚集索引与辅助索引

聚集索引(是主键索引)：他的b+数的叶子节点存储有行数据

辅助索引(非主键索引)：他的b+树叶子节点只存储索引信息和主键信息

覆盖索引

是一种数据查询的方式：针对的是辅助索引；

直接通过辅助索引B+树就能获取要查询的值，而无需通过回表查询

回表查询原理

undolog和redolog作用：

最左匹配原则

b+树通过比较索引值来维持有序。所以在创建多个索引时即组合索引，它时按照从左到右的顺序查找中间不能断。否则不满足最左匹配原则

先比较第一个，相等就比较第二个,一次类推.。当遇到> < betwen ,like等会停止匹配。不走索引。

在创建表时，创建组合索引，那么在查询时，会走索引增加查询效率。 尽可能的扩展索引，即组合索引，可以减少b+树的创建。优化性能。

索引下推

innoDB与MyISAM区别

索引为什么使用B+树

b+树结点16k

事务四大特性

原子性

隔离性

一致性

持久性

并发 读取异常

背景：隔离级别 事务之间互相影响的程度，数据库常设置多个隔离级别来提升数据库并发性能。

隔离级别： 读未提交 read uncommitted 读： 无操作 写：加x锁 脏读，不可重复读，幻读

​ 读已提交 read committed 读： mvcc读取最新版本信息 写：加x锁 不可重复读，幻读

​ 可重复读 repeatable read 读： mvcc读取事务版本信息 写：加x锁 幻读

​ 可串行读 serialization 读： 加s锁，next-key锁 写：加x锁

并发异常： 脏读：一个事务可以读到另一个事务未提交的数据–读未提交读操作没加锁处理

​ 不可重复读：一个事务中两次读取同一条记录结果不一致–读已提交的读操作

​ 幻读：一个事务中两次读取同一范围内的结果集不一致—当前读和快照读不一致造成

异常原因：在并发连接时， 读未加锁导致

不可重复读与幻读的区别：

​ 由于设置了不同的隔离级别，事务读取的方式不一样

区别： 不可重复：一事务两次读取同一记录结果不一致 —另一个事务做updata操作

​ 解决:提升隔离级别

​ 幻读：一事务两次读取同一范围内的结果集不一致 —另一个事务做了insert/delete操作

​ 解决：1快照读该当前读；2在可重复级别下，范围查询加gap锁；3提升隔离级别

sql注入原理，避免sql注入

用户在发送sql语句时，将恶意的sql语句作为参数嵌入到原来的sql语句当中，当服务器组装sql语句时以此绕过授权直接操作数据

eg: 查询用户名+密码

select * from 'log_t' where 'name' = '用户名' and 'password' = '密码';

select * from  'log_t' where 'name' = '' or 1=1 -- ' and 'password'= '用户密码';   //可以拉取所有用户名和密码

背景：sql语句执行过程： 语法句法分析生成语法树–优化sql语句–指定执行计划–开始执行计划

如何避免sql注入： 1预编译 （每条sql语句都需要）

select * from 'log_t' where 'name' =? and 'password'=?;   //后面注入的参数将不会进行sql语句编译，恶意sql注入将视为一个参数

2.字符串正则表达式匹配，有特殊字符的直接退出

3.函数过滤转义

死锁

两个及其以上的事务在执行过过程中，因争夺资源而造成相互等待的现象。会异常报错，dead lock found when trying to get lock

锁: 查询 S锁 可串行化,自动加锁

​ X锁 for update

​ 可重复读手动加锁,范围操作或未命中加gap锁,避免幻读

​ 删除更新 自动添加x锁

​ 可重复度,范围操作或未命中,加gap锁,避免幻读

​ 插入 auto-inc lock 特殊表锁实现

​ insert intention lock 同时加上x锁

​ 锁冲突 S锁和X锁互相冲突

​ 已获得gap锁 尝试加插入意向锁,发生锁等待

​ 锁的释放 具体操作时加锁,一直到事务结束才会释放锁

​ 死锁: 相反顺序加锁: 1不同表的加锁顺序相反2相同表不同行加锁顺序相反

​ 3给辅助索引加锁的给聚集索引加锁,造成加锁顺序相反

​ 4.给具有外键索引加锁时,给父表加锁,隐含给子表也会加锁

​ 5.触发器同样也是

​ 解决:修改sql语句,调整执行顺序

​ 锁冲突死锁: 一个事务已经加了gap锁,另一个事务尝试获取插入意向锁,造成死锁

​ 解决:更换语句或降低隔离级别

如何避免死锁:

​ 1尽可能以相同顺序来访问索引记录或表 2降低隔离级别为读已提交 3添加合理的索引,不走索引将可能为每一行记录加锁,死锁的概率大 4尽量在一个事务中锁定所需的所有资源,减少死锁的概率 5尽量避免大事务,将大事务拆分为小事务.(大事务占用资源多,耗时长,死锁概率高)

begin;
update	table set name ='yy' where id =3;
update	table set name ='xx' where id =4;
commit;

begin;
update	table set name ='yy' where id =4;
update	table set name = 'xx' where id =3;
commit;

MVCC底层原理

背景: 是什么? 一致性非锁定读,解决隔离级别中读的异常问题.

​ mvcc的数据结构 undolog:回滚 记录版本信息

​ 聚集索引记录B+树

解决隔离级别中读的异常问题: 读已提交 读:mvcc读取版本信息 写:加X锁

​ 可重读读 读:mvcc读取事务开始前的版本信息 写:加X锁

mvcc原理 关键数据结构 read view: m_ids 创建read view时,启动未提交事务id列表

​ min_trx_id 创建read view时,启动未提交事务列表中最小事务id

​ max_trx_id 创建read view时,预分配给下一个未开启事务的id

​ creator_trx_id 创建read view的事务的id

​ 聚集索引记录的隐藏行: trx_id 事务修改记录时,trx_id记录该修改事务id

​ roll_pointer 事务修改记录时,将旧记录写入undolog,该指针指向上一个版本的记录

​ 可见性判断 事务可以看到事务本身的修改

​ 事务的可见性 trx_id < min_trx_id 已提交,可见

​ trx_id < max_trx_id 后启动,不可见

​

​ min_trx_id <= trx_id < max_trx_id trx_id in m_ids 已启动单未提交

​ trx_id not in m_ids 已提交,可见

​ 读已提交 每次读取数据(select)时,生成新的read view

​ 可重复读 启动事务时,生成新的read view,一直使用到事务结束