java笔记--mysql

关于MYSQL：

四大特性：ACID

原子性、一致性（其他三个特性为此服务）、隔离性、持久性

数据库并发操作产生的问题：

• 丢失更新
• 脏读 读取到别人还未提交的事务
• 不可重复读 一次事务中会出现一个值读取到两种结果的情况
• 幻读 一次事务中本来没有出现的东西，再次查询时出现

隔离级别有四种：

• read_uncommited （读未提交） 会发生：脏读、不可重复读、和幻读
• read_commited（读已提交） 会发生：不可重复读和幻读
• repeatable_read （可重复读） 会发生：幻读
• Serialization （串行读） 解决一切问题，但是效率低下

索引：

结构：

​ 索引采用B+树的结构；也有hash结构的，但是Innodb默认不支持hash索引，所以感觉比较少用

聚簇索引：

​ 就是索引和数据一起储存 数据放在叶子节点上，在Innodb存储引擎下采用

​ 一般是主键索引，没有主键索引就是选一个唯一索引，都没有的话，Innodb会帮忙维护一个rowid作为聚簇索引

非聚簇索引：

​ 索引和数据分开，主键索引存储数据地址 非主键索引存储主键的值 MySIMY采用这种索引 一般二级索引也是这样

多个索引一起用一般只会使用到第一个索引 除了特殊的or条件下会使用两个索引然后进行并操作

复合索引 ：

多个值一起组成一个索引 遵循最左匹配的原则；一定要从最左的值开始进入索引；不然不走索引

遇到范围查询索引停止，这个值之后的属性失效

所以创建索引时，顺序很重要！！！ 第一个值是在B+树中顺序存储的 ， 第二个值相对于第一个值有序

MYSQL的查询优化器会帮我们做出优化 在查询时的条件 会被 优化成索引的顺序

唯一索引：

索引的值必须唯一，在没有主键索引的情况下，唯一索引大概率能被选为聚簇索引

普通索引：

就单纯为了查询而生，没啥特别点

全文索引：

就匹配长字段用的一个索引，但是消化太大了，一般不使用 ， 完全被el 吊打

覆盖索引：

这不是个索引，只是一种索引的优化策略，就是在建立索引的时候，尽量把经常要查询的字段，建立在一个联合索引里面，避免在查询时出现回表的情况。

前缀索引：

这也是一个索引优化策略，如果字段的值都是很长那种，如果使用它作为索引，那么消耗就会很大，所以在允许的情况下，我们可以选择截取值的一小段作为前缀索引。

select count(distinct substring(字段名, 开始位置, 结束位置)) / count(*) from 表名 用这条语句来进行可行性分析。

使用索引的情况：

适合索引的情况：

• 频繁作为where进行查询的字段
• 关联字段（两张表）需要索引
• 分组字段可以索引 因为一个索引里包含了所有的该字段的相关行
• 统计字段可以索引 例如count(***) 等 产生一个临时表进行计算

不适合索引的情况：

• 频繁更新的字段 导致维护索引的成本加大
• 不使用在where条件查询的字段
• 数据重复且分布均匀的字段不适合索引 例如 性别
• 参与列计算的列不适合索引，索引会失效

其他：

能使用复合索引就使用复合索引

能用短索引就用短索引

null值也是可以走索引的

在order by中索引会失效

索引失效的情况：

• 条件中有or且or某一个条件没有索引的时候索引失效，除非所有条件都有索引
• 复合索引没满足最左原则就不能使用全部索引
• like查询以%开头 因为根本没法对未知的值排序
• 存在列计算（使用函数） 列计算会破坏原本有序的索引
• MySQL优化时觉得全表扫描比走索引快
• 存在类型转化有时会失效 比如数字强转为字符串

SQL优化：

插入优化：

​ 如果如到大批量的数据需要插入，要使用批量插入！批量插入可以减少与数据库建立连接的次数，这里需要自己来控制事务的开始和提交；如果有超大量的数据插入，可以使用 load 指令插入，速度是直接insert的n倍

​ 按主键顺序插入

主键优化：

​ 主键在情况允许的下，尽量使用自增或者递增的主键，因为这样子在每次插入的时候，新的数据就会往页的后面去放，这样子可以有效地减少页分裂的次数。 主键也要尽可能短

order by 优化：

​ 通过索引或者全表扫描，读取满足条件的数据行，在排序缓冲区sort buffer中完成排序 ， 所有不是通过索引直接返回排序结果的排序都叫FileSort排序

​ 通过索引顺序扫描直接返回有序的数据，这种情况叫 using index ，不需要额外排序，效率高；我觉得这个和那个覆盖索引就很相似，一个道理；

​ 这里需要注意的是联合索引的使用，要去符合最左匹配原则，还要在使用时注意联合索引的创建的方式，联合索引创建时可以指定是以升序还是倒叙的方式建立索引，这里对应着order by 查询。

​ 如果只能使用缓冲区排序，我们可以控制一下缓冲区的大小 SHOW VARIABLES LIKE ‘sort_buffer_size’ 默认是256k 可以修改这个值。

group by优化：

​ group by 优化也是需要建立索引，在使用联合索引也是要满足最左匹配原则，但是这里不同的是，如果我想使用联合索引中的第二个值去分组，如果不做其他操作，就还是要使用到临时表，但是如果我们可以在where条件中做一个过滤（如果能满足需求），将联合索引的第一个值过滤出来，这时候就能走索引 ，给第二个值分组

limit优化：

select * from tb_* limit 起始位置, 每页数据 ；

可以通过覆盖索引+子查询的方式 去优化 没听懂。。。

count函数优化：

如果是用MyISAM引擎的话 ， 不加where条件 用count（*）这个效率是飞快的，因为MyISAM把总行数的数据放在磁盘上

Innodb不支持上面那个操作

先写写count的使用情况：

count(*) 遍历全表 但是不取值 直接按行累加

count(主键) 遍历所有主键 ，把主键值取出来 返回给服务层 按行累加

count(字段) 遍历字段所有值 返回给服务层 看有没有not null的约束 没的话 要对行的数据进行判断 再进行累加

count(1) 遍历整张表 但是不取值 在服务层直接按行累加

count(null) 0

update优化：

在Innodb中默认是使用的行锁

所以在update语句中的条件语句一定要加上索引且保证索引不失效，不然就GG了，行锁升级成表锁，大大降低了并发度

锁：

全局锁：

给整个数据库上锁， 使用在全库备份的时候；

元数据锁：

由系统自动控制

意向锁：对表加的 当表中某一行加上了行锁之后，如果有其他事务想给表加上表锁，这时候如果没有意向锁，就需要一行行的扫描所有的数据行来判断是否可以加表锁，但是有了意向锁，就可以直接判断意向锁，比如：某一行加了s锁，表的意向锁就是意向共享锁，别的事务可以加表级的共享锁，但是不可以加x锁。

s锁 共享锁 允许多个线程一起读数据

x锁 独占锁 只允许一个线程对一个数据进行修改的读取

行锁：对一行数据进行上锁 MyISAM对行锁不支持 Innodb支持行锁 行锁只有在有索引的列才能生效 如果不是在索引上加锁，行锁就会升级成表锁，这是我们要规避的

表锁：对整个数据表上锁 明显行锁比表锁粒度小 更加适合高并发！

间隙锁：

锁的是数据之间的记录，不包括两个端点 ， 在RR隔离级别下适用，防止出现幻读现象

临键锁：

行锁和间隙锁的结合 同时锁住数据和数据之间的间隙

性能分析：

1、使用语句----show global STATUS LIKE ‘com_______’ 来查询SQL语句的执行频率；

2、 SELECT @@have_profiling

​ SELECT @@profiling

​ set profiling = 1;

​ show PROFILEs; 使用这一套语句来查询每一句的SQL执行的耗时时间

3、慢查询日志 : 查看执行时间较长的SQL语句 慢日志默认关闭 需要修改配置文件打开 ： slow_query_log = 1

​ long_query_time = 2; //这个是设置慢查询的慢的阈值

​ 关于慢查询可以使用外部的工具来查看： 调试工具：Arthas 运维工具：Prometheus 、 Skywalking

4、explain关键字 这个是最主要的，下面有网上抄的字段分析；比较重要的是，type、键的使用情况、额外信息等

日志：

慢查询日志：

用于监测查询语句，慢日志默认关闭 需要修改配置文件打开 ： slow_query_log = 1

long_query_time = 2; //这个是设置慢查询的慢的阈值

binlog：

二进制日志中存储的内容称之为事件，binlog 是作为mysql操作记录归档的日志，这个日志记录了所有对数据库的数据、表结构、索引等等变更的操作。也就是说只要是对数据库有变更的操作都会记录到binlog里面来

binlog 是server层的日志，而redo log 和undo log都是引擎层（innodb）的日志

很多基于binlog的使用场景，比如：主从复制、读写分离、保证数据最终一致性等等

binlog 有三种记录格式，分别是ROW、STATEMENT、MIXED。

1、ROW： 基于变更的数据行进行记录，如果一个update语句修改一百行数据，那么这种模式下就会记录100行对应的记录日志。（就是不仅仅记录语句，还把执行语句之前的数据记录下来）

2、STATEMENT：基于SQL语句级别的记录日志，相对于ROW模式，STATEMENT模式下只会记录这个update 的语句。所以此模式下会非常节省日志空间，也避免着大量的IO操作。

3、MIXED： 混合模式，此模式是ROW模式和STATEMENT模式的混合体，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog。

undoLog：

记录数据的逻辑日志，用于事务回滚操作，保证了事务的原子性

undolog存的是历史版本的旧数据，至于我下面写的这些逻辑，我不理解，大概是生成旧数据的逻辑吧

当执行一条delete时，日志中会记录一条对应的insert 语句

执行一条update时，会记录一条相反的update

undolog在事务开启时产生 ， 在事务提交的时候不会立马删了undolog 因为它在 mvcc中也有作用

redoLog：

记录数据的物理日志，用来实现事务的持久性 ；

redolog 分为两个部分 重做日志缓存 （位于内存） 和 重做日志文件（位于磁盘）

每一次数据在Buffer Pool 修改之后会记录对应的物理日志 redolog 在 重做日志缓存（这里记录的是物理页的变化）然后当事务提交之后，重做日志缓存中的数据会被刷新到重做日志文件中；

主要作用：在刷新脏页到磁盘时，如果发生了错误，需要使用redolog 进行数据恢复使用

MVCC：

Multi-Version Concurrency Control，多版本并发控制

MVCC中的基本概念：

当前读：

​ 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读：

​ 简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

• Read Committed：每次select，都生成一个快照读。

• Repeatable Read：开启事务后第一个select语句才是快照读的地方。

• Serializable：快照读会退化为当前读。

隐式字段：

DB_TRX_ID ： 最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。

DB_ROLL_PTR： 回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。

DB_ROW_ID ： 隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

上面的三个隐式字段会出现在行数据中

ReadView：

ReadView（读视图）是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务

（未提交的）id。

四个核心字段：

• m_ids ：当前活跃的事务ID集合
• min_trx_id ： 最小活跃事务ID
• max_trx_id ：预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
• creator_trx_id ： ReadView创建者的事务ID

四种访问情况： trx_id 数据行的隐式的id 就是版本链上的修改行数据的那个事务的id

• trx_id == creator_trx_id 可以访问该版本 说明数据是当前这个事务更改的。
• trx_id < min_trx_id 可以访问该版本 说明数据已经提交了。
• trx_id > max_trx_id 不可以访问该版本 说明该事务是在ReadView生成后才开启。
• min_trx_id <= trx_id <= max_trx_id 如果trx_id不在m_ids中，是可以访问该版本的 说明数据已经提交。

不同的隔离级别，生成ReadView的时机不同：

READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。

REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。