脏读(读取时未提交数据)：
A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据
不可重复读(前后多次读取，数据内容不一致):
A事务在读取数据，但是事务A比较大花费很长时间，在A第一次读取时数据为20，后来事务B修改为了30，此时A事务再次读取数据为30，和之前的不一样，数据不重复了，系统不可以读取到重复的数据，称为不可重复读
幻读(前后多次读取，数据总量不一致):
事务A读取数据总量为100，事后B事务新增了100条数据并提交后，A再次查询，发现总量为200了，就好像产生幻觉凭空多了数据，称为幻读
事务隔离级别：
1.读未提交
在这种隔离级别下，所有事务能够读取其他事务未提交的数据。读取其他事务未提交的数据，会造成脏读，因此在该种隔离级别下，不能解决脏读、不可重复读和幻读
2.读已提交(大部分数据库默认，mysql除外)
在这种隔离级别下，所有事务只能读取其他事务已经提交的内容。能够彻底解决脏读的现象。但在这种隔离级别下，会出现一个事务的前后多次的查询中却返回了不同内容的数据的现象，也就是出现了不可重复读。
3.可重复读(mysql默认)
在这种隔离级别下，所有事务前后多次的读取到的数据内容是不变的。也就是某个事务在执行的过程中，不允许其他事务进行update操作，但允许其他事务进行add操作，造成某个事务前后多次读取到的数据总量不一致的现象，从而产生幻读
4.可串行化
在这种隔离级别下，所有的事务顺序执行，所以他们之间不存在冲突，从而能有效地解决脏读、不可重复读和幻读的现象。但是安全和效率不能兼得，这样事务隔离级别，会导致大量的操作超时和锁竞争，从而大大降低数据库的性能，一般不使用这样事务隔离级别。
一.为什么需要做关系型数据优化
1.1 现在试用场景
单体项目：一个云服务器安装一个数据库服务器
微服务项目：
1)可以一个云服务器安装一个数据库服务器，里面有多个数据库
2)可以多个云服务器分别安装一个数据库服务器，里面只有自己业务数据库
1.2 有问题
高配
1)并发的读写效率 分布式+集群
2)海量数据高效读写 分库分表
3)单点故障&动态扩展 分布式+集群
低配=
1）商业需求对性能的影响
2）数据库设计对性能的影响
某些数据不适合基于数据库查询，比如大量数据的全文检索：
解决方案：基于ES全文检索
热点数据高频查询：
解决方案：合理的缓存，提高响应速度
多余的数据库交互，重复的sql：
解决方案：减少数据库交互次数，减少重复相同sql的重复执行
错误的表设计：
解决方案：冗余字段：达标拆小表(垂直分表，水平分表)
3）sql编写的不合理对性能的影响
太多表JOIN
使用冗余字段减少JOIN
用一个非常复杂的sql且进行了大量的计算
SELECT * ，明明不需要那些字段，非要全部查询出来
4）其他方面 --运维
硬件环境
网路环境
对关系型数据库的优化是一个综合性的技术，主要包括
1.找出执行效率低SQL(定位慢查询)-发现问题
2.分析慢SQL expain sql -分析问题
3.优化 -解决问题
3.1 单机(表结构、索引、SQL(代码问题))：
表
合适表结构 3 N F 和反3NF
合适引擎
3.2多机(IO瓶颈)
集群(读写分离，让多台服务器提供服务)
分布式(把不同的业务分给不同的集群处理)
3.3其他优化方案
缓存es页面静态化
1.优化方案不仅可以适用在mysql。还可以适用于其他关系型数据库
2.优化时先考虑单台数据库服务器，如果单台优化完成后，是在满足不了再考虑多机集群和分布式
mysql执行过程
-- mysql运行多久 单位s
show status like 'uptime';
-- crud执行次数
show status like 'Com_%'
show status like 'Com_insert'
show status like 'Com_select'
-- 完整语法 show session/global status 'Com_select'
-- 如果不写默认就是session，指取出当前窗口的执行，如果想看msq启动到现在，则是global
-- 查询所有连接数-做报表
show status like 'connections'
-- 查询最大并发连接数 默认100，一般都要改
-- 先设置为很大，等他运行一段时间，查询最大访问值往上抛一点
show status like 'Max_used_connections'
SET GLOBAL Max_used_connections=1000 -- 待确定
-- 查看执行的线程 可以检查锁未释放问题
show processlist
查找出慢查询sql
-- 查询慢查询阈值
show variables like 'long_query_time'
-- 设置慢查询阈值
set long_query_time=0.5;
-- 查询慢查询次数
show status like 'slow_queries'
-- 开启慢查询sql持久化
set global slow_query_log = 1 -- 开启后默认以日志的方式保存查询日志
set global log_output='FILE,TABLE' -- 日志文件喝表都存放
-- 找到sql
show varibales like '%slow_query_log' -- 日志文件
select * from mysql.slow_log -- 查看日志内容
什么时候开启慢查询
系统中所有sql都执行一遍，才能判断是否有慢sql，什么时候开启能覆盖所有sql执行？
1.开发者自测：
开发完成后，需统一打包，统一部署，统一认证，开发者在集成测试的时候可以开启慢查询
2.测试人员测试：
测试人员需要测试所有功能
注:开启日志记录是会影响性能的
在配置文件中写，就可开启
分析sql
-- 3.1 分析索引使用情况
explain select * from emp where empno = '100983'
-- 3.2 sql详细执行请求 cpu io
set profiling = 1; -- 开启sql的profiling统计
select * from emp where empno = '100983'
set profiling = 0; -- 关闭profiling
-- 分析详细情况
show profiles;
show profile for query 284
show profile cpu,block io for query 284
优化-单机优化
1NF：表的列具有原子性，不可再分割，只要是关系型数据库就自动满足
2NF：表中的记录时唯一的，通常我们设计一个主键来实现，即:行唯一
3NF：即表中不要有冗余数据，即表的信息能够被推到出来，就不要设计一个单独字段来实现
反3NF：3NF指的是A表不能出现B表非主键字段，如果出现了，那这个字段就是冗余字段，违反了三泛式.(反三范式指反第三范式)
例：使用冗余字段减少JOIN连表提高查询效率
存储引擎-创建表时要选择存储引擎
分类：myisam和innodb，memory(不用)
优缺点:问MyISAM和INNODB的区别(主要)
事务安全：MyISAM不支持事务，INNODB支持
查询和添加速度:MyISAM速度快，INNODB速度慢
支持全文索引:MyISAM支持，INNODB不支持
锁机制MyISAM表锁，INNODB行锁
外键：MyISAM不支持外键约束，INNODB支持外键(通常不设置外键，通常在程序中保证数据的一致)
使用场景
如果对数据安全要求不高，主要以添加查询为主，使用MyISAM，比如发帖和回复帖，其余情况用INNODB
选择存储引擎
-- 创建表时选择存储引擎，如果不指定会默认使用my.ini配置文件的
create table aaa(id int) engine myisam;
-- 创建后修改
alter table aaa engine innodb;
索引：是帮助DBMS(数据库管理系统)高效获取数据的数据结构
总结：使用索引把全表变为索引查找(先在索引中查找，直接定位数据)，减少查询次数，增加查询效率。而索引查找效率取决索引数据结构
mysql中索引的分类:
1)根据索引字段的类型和唯一性
普通索引Normal：允许重复的值出现，可以在任何字段上添加
唯一索引Unique：除了不能有重复的记录外，其它和普通索引一样，可以在值时唯一的字段添加，可以为null，并且可以多个null
主键索引:随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会给该列创建索引，值不能为null
全文索引:用来对表中的文本域(char,varchar,text)进行索引，全文索引针对MyISAM有用，InnoDB不支持全文索引，所以一般不用，默认只支持英文，使用ES，Lucene代替就ok
2)根据索引列的多少分为复合索引(多列索引)和普通索引(单列索引)
普通索引(单列索引):该索引旨在一个列上面创建
复合索引(多列索引):该索引只在多个列上面创建
3)存储数据结构不同
FullText：也是一种数据结构
HASH,BTREE,RTREE
注意事项
索引的代价：
占用磁盘空间
对dml操作有影响，因为要维护，变慢
在那些列上适合添加索引
1.不会出现在where字句中字段不该出现索引
2.较频繁的作为查询条件字段应该创建索引
例如:select * from emp where empno = 1
3.唯一性太差(离散度)的字段不适合单独创建索引，即使频繁作为查询条件
例如select * from emp where sex = ‘男’
4.更新非常平凡的字段不适合创建索引
select * from emp where logincount = 1
-- 查看索引
show index from emp
-- 创建索引
alter table emp add primary key(empno) -- 主键索引
alter table emp add INDEX myindex (empno) -- 普通索引
alter table emp add UNIQUE (empno) -- 非空索引
-- 删除索引
drop index myindex ON emp
alter table emp drop index myindex
alter table emp drop primary key
select * from emp where empno = '399955'
索引小技巧
-- 对于创建的多列索引(复合索引)，不是使用的第一部分就不会使用索引(向左匹配原则),用了也不一定会使用
alter table dept add index my_index (dname,loc)
show index from dept
explain select * from dept where dname='aaa' -- 使用
explain select * from dept where loc='aaa' -- 未使用
explain select * from dept where loc='aaa' and dname ='aaa' -- 使用
explain select * from dept where loc='aaa' or dname ='aaa' -- 未使用
-- 对于使用like的查询，如果%在前面则不会使用索引,在其他位置则会使用
explain select * from dept where dname like '%aaa' -- 未使用
explain select * from dept where dname like 'a%aa' -- 使用
-- 对于条件有多个or，需要每个条件都能单独使用索引才使用索引
explain select * from dept where loc='aaa' or dname ='aaa' -- 未使用
-- 如果列是字符串，一定要用引号把数据引起来，否则不使用索引
explain select * from dept where dname='111' -- 使用
explain select * from dept where dname=111 -- 不使用
索引的存储方式：
索引底层实现：
Hash方式底层使用的是Hash表算法，时间复杂度是n(1)，一次IO就能查询到结果，有以下缺点：
1.Hash结构的索引不支持排序，InnoDB和MyIsam都不支持Hash
2.只能进行等值查询(=,in)，不能使用范围查询(>,<,Between)
3.列的重复值过多会出现大量Hash冲突问题
4.InnoDB不支持Hash方式，Memory存储引擎可以用到
BTREE：MyIsam，InnoDB默认使用，B+tree算法
FullText：myisam只能在char varchar text
Btree(底层使用B+树):
相比较BTree来说有如下优势：
1.查询效率稳定:B+Tree属于多路树，每次查询都要走到叶子节点，查询效率稳定
2.磁盘读写能力强：非叶子节点不存储完整数据，而是存储键值KEY，和字树节点的指针，
这样每个节点就可以存储更多的KEY，充分利用每个节点的存储空间16kb，减少了节点数，
树高变矮，IO次数变少，性能更高
3.排序能力强：叶子节点存储完整数据，叶子节点是有序的，每个叶子节点指向下一个节点的
应用，形成一个链表，适合范围查询，和支持排序
InnoDB主键索引和辅助索引
对于主键默认会创建主键索引，其他列创建的索引就叫辅助索引，也叫二级索引，辅助索引的子节点存
储的是主键索引的简直，这就意味着辅助索引需要查询两个B+Tree
概念：
1.回表：辅助索引扫描完之后还会扫描主键索引，这叫回表
2.覆盖索引：如果Select name查询的列正好包含在辅助索引的节点的键值中，它就不需要再扫描
主键索引了，这个叫覆盖索引，所以不需要写select *
如果表没有主键怎么办？
1.mysql会选择第一个不喊null的唯一索引作为主键索引
2.如果不满足条件一，那么会选择一个隐藏的行RowID作为主键索引
MyIsam索引结构，数据和索引是分开的，所以叶子节点存的是数据的磁盘地址，而不是数据
注意：MyIsam的辅助索引的叶子节点没有指向主键索引的键值，而是直接指向数据的磁盘地址
sql优化小技巧
DDL：建表，建库，视图，索引
DML：增删改
DQL：查询
DCL：对用户权限设置，控制事务
DDL优化，批量插入
1.变多次索引维护为一次索引维护
alter table test DISABLE keys; -- 禁用索引
insert into test select * from test3；-- 批量插入数据
alter table test ENABLE keys；-- 恢复索引
2.关闭唯一校验
set unique_checks=0 -- 关闭
insert into test select * from test3；-- 批量插入数据
set unique_checks=1 -- 开启
3.修改事务提交方式，变多次事务提交为一次事务提交
set autocommit=0 -- 关闭
set autocommit=1 -- 开启
DML优化，批量插入
合并多条为一条
insert into test vaule(1,3);
insert into test vaule(2,4);
变为
insert into test vaule(2,4),(1,3);
代码如下
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9rxf9w32-1668484883778)(G:\java学习笔记\数据库优化\优化.png)]
DQL优化(常用)
1.对查询进行优化，尽量避免全表扫描，首先应考虑再where及ordey by设计的列上建立索引
2.应尽量避免再where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
3.尽可能使用varchar来代替char，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说
在一个相对较小的字段内搜索效率显然要高些
4.任何地方不要使用select *，用具体字段代替，不要返回用不到的任何数据
5.谨慎使用in和not in，否则会导致全表扫描
mysql
insert into test vaule(1,3);
insert into test vaule(2,4);
变为
insert into test vaule(2,4),(1,3);
DQL优化(常用)
1.对查询进行优化，尽量避免全表扫描，首先应考虑再where及ordey by设计的列上建立索引
2.应尽量避免再where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
3.尽可能使用varchar来代替char，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说
在一个相对较小的字段内搜索效率显然要高些
4.任何地方不要使用select *，用具体字段代替，不要返回用不到的任何数据
5.谨慎使用in和not in，否则会导致全表扫描
6.对于连续的数值，能用between就别用in
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
