Mysql基础到精通

一、基础部分（主要用于自己的查漏补缺）：

去除重复数据：

SELECT DISTINCT 去除列表重复数据，考虑查询出来所有的数据去重；比如查询username全为一样的，但是password不一样。查询出来的数据为password所有数据。

空值运算：

空值与任何值运算都为空，无论+-*/都为NULL类型，而且做比较运算 < > = 也为NULL；

查询常数：

SELECT 'iam2cc' ；即可。

查询表详细信息：

DESCRIBE tablename;

别名：

SELECT name '用户名称' FROM sys_user，注意标准形式使用单引号；

安全等于符号：

由于和NULL值得操作都为NULL，所以加入了 <=> 就可以判断空值，类似于 IS NULL;

视图的作用：

提高数据重用性，比如经常查询的东西，创建成视图。提高复用。

在不破坏表结构的情况下，拆解表。

视图的操作会影响正常的表，反之亦然，如果字段在表中不存在，则更新失败（比如调用AVG函数等）。

查看所有视图：

SHOW TABLE STATUS WHERE Commit = 'view'

创建视图：

CREATE VIEW viewname（colname1，colname2，xx）

AS

SELECT colname1 ，colname2 xx

FROM xx

利用视图格式化数据:

CREATE VIEW viewname（colname1，colname2，xx）

AS

SELECT CONCAT（col1，col2）othername（CONCAT函数会拼接字段中的值，返回新的字段）

修改视图：

AUTER VIEW 视图名称

AS 查询语句

删除视图：

DROP VIEW 视图名称

DROP VIEW 视图名称 IF EXISTS。

存储过程：# 暂不了解。

触发器（作用当，执行一个表操作，且需要去执行某些语句，比如当一个表执行插入语句后，对另一个表执行操作）：

创建触发器：

CREATE TIRGGER triggername

AFTER/BEFOR  INSERT/DELETE/UPDATE ON tablename

FOR EACH ROW

BEGIN

sql expression

END

删除触发器：

DROP TRIGGER IF EXESIST triggername

查看触发器：

SHOW TIRGGERS

总结：

触发器保证了数据完整性。和一些逻辑校验。保证了数据原子性。

二、深入MYSQL

逻辑架构：

 MYSQL 缓存在8.0后去掉了，因为命中率实在是太低了，要求必须两个查询语句完全一样，多空格都不行。

MYSQL查询流程：

 sql语句查询顺序

存储引擎（表处理器）：

查看存储引擎：

SHOW GNGINES;

修改默认存储引擎：

SET DEFAULT_STORAGE_ENGINE = MyISAM;

存储引擎的对比：

Innodb：支持事务。写效率差，内存要求高对性能有绝对性的影响。支持行锁，占用更多磁盘去保存数据索引。

myisam：不支持行锁，只支持表锁，不支持事务，崩溃后无法安全恢复，查询快适用于无事务，主要以insert 和select为主的数据库。

索引：

索引是什么？索引是排好序的数据结构

为啥使用索引？

顺序查找和散乱数据会查找会大量的消耗磁盘IO。

所以使用搜索树：提高查询速度，降低磁盘IO。

mysql的数据文件都是存放在idb文件里面的，创建索引也会增大idb文件的大小，innodb的索引就是B+树。

B+树（简单版）:

层级最多不超过四层，数据页面中一列就是一个compact。

一个compact包括索引主键值，其他字段名称还有record_type （数据的顶端）

2:代表最小数据

3:代表最大数据

1:代表目录数据

0:代表普通数据

使用了b+树，然后使用二分法查找数据大大提高了查询速度，减小了io次数。

 聚簇索引：

以主键为索引的数据索引叫聚簇索引，索引即数据，数据即索引。数据之间是按主键排序的单向链表，数据页之间是双向链表。顶层只要超过一个目录即会继续向上生成。 

 最好不要根据主键去插入数据，当我们没有主键的时候innodb会隐式的去创建索引（字段非空的）。

非聚簇索引（辅助索引，二级索引）：

辅助索引中记录的不是完整的值，只是当前字段和主键的值。

 联合索引（基于两个字段创建的索引）：

Innodb中B+树索引的注意事项：

1.根节点保持不变（当数据变多产生数据目录的时候，会先从跟节点复制出来,超过两页的时候，先复制一份数据页出来，然后根节点变为目录页，他复制的变为叶子节点）

2.二级目录的唯一性（因为有主键，必唯一）

3.一个页面最少储存两条数据（满足树结构）

创建联合索引（创建的顺序有严格的限制，在查询的时候必须跟着索引的顺序查询，不然会不上索引）

create table（id int, name char,age int,index idx_age(age,name) ） 

查看索引的两种方式：

show create table db_tsm_sit_zhang;
show index from db_tsm_sit_zhang;

删除索引

alter table tablename

drop index  indexname

Innodb中数据存储结构：

 页的内部结构：

file header包含（常用）：

file page offset 页号

file page type 该页类型（目录页还是数据页）

file page prev 前一页 （为什么是双链表的缘故）

file page prev 后一页

file page lsn 页面被最后修改的日志序列位置（与文件尾配合使用，刷盘的时候如果发生宕机等情况，此字段会配合文件尾,判断是否是同一个数据页。）

file page space or checksum 校验和（比如过长字符串的时候可以写通过算法变成一个短的check sum ，通过check sum 判断是不是同一个字段）

user record 和 free space ：

用户记录和空闲空间如图，最大最小记录也能理解infumun和supremun是记录当前页最大最小值

user record中的数据按compact（行格式）一条一条的摆在这里形成单链表：

compact行格式：

边长字段列表长度

null值列表

记录头信息（5b）：

delete mask 删除标识位，因为在索引中是有序的，物理删除会导致性能大幅度降低(删除之后磁盘挨个向前移)，所以在这里删除都是把此标识变成 1 标识已经删除，且next record 变为0 上一条的next record 指向下一条，跳过本条。当重新插入的时候可以复写此条记录，或者等删除的记录多了，可以另外形成一条逻辑连续的索引。

min rec mask B+树的每层非叶子节点中的最小纪录就会添加该标记为1

recrod type 0表示普通记录 1表示非叶子节点 2最小记录 3最大记录

heap no 当前数据在本页的一个位置从2开始(0和1被最大最小占据)

n_owned  (与页目录有关,数据会进行分组,最小的自成一组,其他组4-8个,最后一组1-8个. 每组最后一个数据的n_owned的值就是该组的数据个数，其余都为0)

next recrod 指向下一条数据的偏移量

页目录与(n_owend有关):

 页目录插槽,指向每组的最大数据,这样二分法不仅仅用于查找数据页,也可以查找页内数据.

区/段/碎片区:

 表空间:

索引创建和设计原则:

tips唯一索引：查询速度更快，因为是惟一的，查到一条直接返回。

 创建\删除索引：

索引会在创建主键和唯一约束的时候自动创建:

创建：

alter table [tablename] create index [indexname(colname)]

create index [indexname] on [tablename(colname)]

删除：

alter table [tablename] drop index [indexname]

drop index [indexname] on [tablename]

索引的使用：

1.当有查询字段的时候，需要看用到的索引是否会有回表操作。有回表操作不如不使用索引。

2.当查询先有group by 后有order by的时候创建联合索引的时候需要注意其中的顺序。

3.当然delet和update语句中的where条件限定的时候也会使用到索引。

4.distinct去重的时候有索引效率更高

5.多表查询的时候,注意表链接不要太多,连接条件保证类型一致(否则,有时候会使用隐士函数转换让索引失效,比如char->int)

6.当创建字符串索引的时候,字符串太长可以使用分字符前缀串创建索引.

create index [indexname] on [tablename(colname(12))]

7.使用区分度高(散列值高)的列作为索引

 8.使用最频繁的索引放在联合索引的左边.

不适合索引的情况:

1.where 中用不到的字段.

2.数据量太小的表

3.重复过高的列不要创建索引与上7对应

4.经常更新数据的表

5.不建议无序字段作为索引

6.删除不使用或者很少使用的索引

7.不定义冗余,重复的索引.

数据库优化思路

常用性能参数：

show status like [参数]

last_query_cost 查看查询成本（多少个数据页取出的）

Connections 查看连接数

Uptime 查看启动时间

定位慢查询SQL：

 开启slow_query_log:

查看满查询日志是否开启

show variables like '%slow_query_log'

set globla 'slow_query_log' = on

设置慢查询的范围

set globla 'long_query_time' = 1 

性能分析工具mysqldumpslow（查看下面老铁的谢谢）：

(131条消息) mysqldumpslow使用总结_帝壹的博客-CSDN博客_mysqldumpslowhttps://blog.csdn.net/ydyang1126/article/details/73501824重置慢查询logs （slow_query_log关闭就是删除，否则是重置清楚数据）

分析查询语句EXPLAE：

 table: 查询所包含的表，（多表查询展示多个表）

id：每一个select对应的id，如果有子查询的话就会展示为2或者更多（但是查询优化器有可能会对我们的子查询重写成多表查询。所以不一定会出现2或者更高的id）

tips：

union会创建临时表去重，union all不会创建临时表。

type:针对单表的访问方法

值类型的解释：

system系统级别性能最高

const常量级别高效（一级二级索引的时候就会出现先该变量）

eq_ref:

 ref:二级索引与普通字符串进行匹配

 key_len:(帮助检查是否充分利用到索引，跟自己比越大说明用到的索引层级越深)

rows：预估查询的行数

filtered:where中满足的比例

explain的四种格式

explain formate=json\tree [SQL EXPRESSION]

json 比 默认多了查询成本

数据库调优

数据库条优分为逻辑条优和物理调优

逻辑条优就是简化sql或者更快的查询速度，物理就是大数据量情况下设置缓存，连接数，读写分离，分库分表等。

索引失效的情况

1.聚合函数会导致无法直接使用索引

2.sql语句里面计算会导致无法使用索引

3.联合索引的where的条件顺序会导致，索引只能使用到一半或者无法使用。

4.隐式的语句转换会导致索引无法使用比如int转化char虽然能查出来，但是会导致索引失效。

5.联合索引中 如果左边列使用了不等式比较，右边的索引就会失效。比如联合索引（ id，age，name），where id =1 and name like '%李' and age > 18,这样的name的索引就会失效。

6.判断值is not null 是无法使用索引的。

7.不等值判断是无法使用索引的。

8.左模糊，会使索引失效，比如like "%ddd"。

内外链接查询优化：

tips：explian 中先展示的表为驱动表

1.内链接的时候，查询优化器会选择成本低的为驱动表。（有索引的会被优先被选择为被驱动表）

2.内链接的时候，查询优化器会选择成本低的为驱动表。（都有索引的会选择小表为驱动表）

3.外链接的时候如果只能有一个表能加索引，就选择被驱动表加索引。

join原理：

其中我们的buffer大小也可以设置：

 排序优化：

此情况无法使用索引，因为还需要回表，查询优化器发现使用联合索引排序后还得回表根据主键索引查询其他字段，还没有直接内存排序快，所以选择不适用联合索引。所以排序最好限制查询的字段和条数。

 使用了limit就可使用索引了，只有10条，查询优化器认为成本较低。

 orderby错误的时候索引也会失效

比如联合索引（age，name），order by（name）直接失效。

 没有过滤的情况下无法使用索引。

当where和orderby索引需要取舍的时候：

 分页优化：

覆盖索引:

比如不能使用索引的情况:

create index on tb inx_workno_addr(workno，addr)

select * from tb where age<>20;(查询优化器判断使用索引还得回表,不如直接正常查)

select workno ,addr where age<>20; 此时插叙优化器发现不需要回表查询其他字段,索引上直接有,此时,不使用索引成本大于>使用索引成本.所以又用上索引了.此时就叫覆盖索引.

索引下推（ICP）

create index on tb inx_workno_addr(workno，addr)

select * where workno=15 and addr like ‘%重庆%’

左模糊不限制条件的时候无法使用索引，不使用索引下推的时候，通过索引找到workno=15的，回表IO查询workno=15 ，然后where过滤。

当有索引下推的时候，找到workno=15的，查询优化器发现addr也在索引里面，就直接找到addr匹配的数据，然后再回表IO，减少了IO的次数，成本低于第一种。

exist与in的用法：

 

主键的设计：

mysql 8.0 后 UUid_to_bin(@uuid,true) 得到一个递增的uuid。适合用主键

数据库范式:

   数据库事务：

 

事务状态

Mysql中的事务：

        并发时会出现的问题：

 

 设置事务隔离机制：

 事务日志：

数据调优和redo的刷盘策略（innodb_flush_log_at_trx_commit）：

除了事务提交刷盘外，innodb后台的线程也会每秒有一次同步操作。

innodb_flush_log_at_trx_commit = 1的时候

 innodb_flush_log_at_trx_commit = 2的时候:

  innodb_flush_log_at_trx_commit = 0的时候:

redologfile参数:

写入redologbuffer过程:

 

  write pos只要在写入log file的时候就会往后移,checkpoint当 当系统读取redologfile往磁盘里面写入数据的时候,checkpoint就会往后移动,意味着在cp左边的数据都是已经写过的属于,是可以擦除的数据.(redologfile已经从写入磁盘的部分,就无效可擦除了.)

Undo日志：

锁：

读锁和写锁

以粒度划分锁表锁：

意向锁

 这样就省了很大一部分性能比如我上了行锁的时候，又需要上一个表锁，无意向锁的情况我们还得去遍历表才能确定行数据是否上锁，但是有了意向锁，直接再最外层就可以判断是否上锁，上了什么锁（给行数据上锁的时候，会先去获取意向锁）

 自增锁（非重点）

元数据锁：

 

 两个gap锁，锁定同一个范围，会对对方实现得事务进行锁定，重而形成死锁，innodb会发现死锁然后回滚成本低得事务来解开死锁。

临建锁

 插入意向锁解决了在等待gap锁得时候，两个事务都在插入主键一样的值得问题，有插入意向锁就不会出现这个问题。

页锁

 

 全局锁和死锁 :

 处理死锁:

MVCC：

 mvcc undolog链 行格式隐藏字段（trx_id,roll_pointer）

ReadVIew 设计

六大日志:

数据恢复的过程先查看:

如下:

恢复需要找到恢复位置:

binlog的删除

写入过程

 redo log 和binlog日志不一致，出现什么问题？

 主从同步，中继日志

 

binglog的设置

stament (binlog_format=stagment)

row (binlog_format=row)

   

 解决查询数据不一致的问题(读写分离模式下):