qiuyadong's Homepage

Mysql

2020-02-20
签到系列

签到24！

学习总结

前不见古人，后不见来者。

念天地之悠悠，独怆然而涕下！

1、一条SQL是如何执行的？

建立连接

mysql数据库首先对外开一个服务，监听3306端口；

  • 通讯类型

    • 同步

      客户端同步调用被调用者，受限于被调用者的性能，一般都是1对1服务；

    • 异步

      异步调用被调用者，可以避免阻塞，但不会减少sql调用的时间， 如果每一个sql建立一个链接，曾加了服务器的负担，还需要线程池的支持，服务器排队获取任务，增加了编程的复杂度；

    一般使用同步连接数据库；

  • 长连接还是短连接

    • 长连接

      使用长连接，可以减少创建和销毁连接的系统消耗，新的请求过来可以使用该连接

    • 短连接

      使用完就close掉该连接

    一般使用长连接，但是保持长连接会消耗内存，长时间不连接，系统会关闭该连接

    show global variables like 'wait_timeout';jdbc程序的连接时间
show global variables like 'interactive_timeout';交互程序的连接时间
//默认都是28800秒，8小时
# 查看mysql数据库的连接数
show global status like 'Thread%';
//Threads_cached:缓存中的连接数
//Threads_connected:当前打开的连接数
//Threads_created:为处理连接创建的
//Threads_running:正在运行的连接，并发的连接

    每创建一个会话，mysql就创建一个线程进行处理，关闭该会话，就kill 线程；

    #查看当前连接状态
show processlist;
//其中可以查看连接的状态
//sleep query locked copyingtotemtabledisk sending data sorting for group sorting for order
#显示该mysql最大连接数
show variables like 'max_connections';
//默认151，可以修改16384 ，2的14方
# show 语句的会话级别session和global
set global max_connections=1000;

  • 两种通讯协议

    • unix socket

    • TCP

      编程语言就是使用tcp连接的

  • 通讯方式

    这里使用半双工；

    使用了半双工后，无论是发送一个请求还是获取一个数据都是一次操作，一次性发送；

    show variables like 'max_allowed_packet'

查询缓存

将结果缓存，默认不推荐使用；mysql8.0就移除了；

语法解析和预处理

  • 词法解析

  • 语法解析

  • 预处理

    检查符合语法和词法的语句的表和字段等问题

查询优化与查询执行计划

通过上一步等到解析树，一条sql语句可以有很多执行方式，最后的结果是相同的。

那么，那种执行方式好呢？查询优化就会将他们生成不同的执行计划。那种开销最少就使用那个；

show status like 'last_query_cost';

查看执行计划：

explain sele name from user;
//使用explain也有可能不是最终的结果

存储引擎

查询表使用的存储引擎

show table status from 'xxx';
//可以显示xxx数据库中所有表的使用的不同的存储引擎

不同的存储引擎存储数据使用不同的文件格式和方式；

# 查看数据存储的路径
show variables like 'datadir';

任何一种存储引擎都有一个.frm文件，表结构定义文件

innodb还有一个文件.ibd文件；

myisam有两个文件.myd、.myi

执行引擎

调用每种存储引擎的api;

返回结果

2、存储引擎的对比

  • MYISAM

    应用范围比较小，表锁限制了读写的性能，在web和数据仓储中使用只读或者以读为主的场景；

    • 表级锁，不支持事务

    • 拥有较高的插入和查询速度

    • 存储了表的行数

      如何快速的插入100万的数据，用myisam插入数据之后，修改成innodb;

    • 用于读分析库中

  • InnoDB

    • 支持事务,外键
    • 支持行级锁、表级锁
    • 支持读写并行，写不阻塞读（MVVC）
    • 特殊的索引存放，减少io，提高查询速度
    • 用于读写并行，支持事务的业务场景

  • Memory

  • CVS

  • Archive

3、Mysql的体系结构

  • 架构分层

    • 连接层

      管理连接，权限验证

    • 服务层

      缓存、解析器（词法、语法解析）、优化器（执行计划）、执行器（操作存储引擎）

    • 存储引擎

4、InnoDB的内存结构

数据都是存储在磁盘上的，这样效率肯定低；InnoDB使用了缓存技术，Buffer pool，将索引和数据组成页，以页为单位存储到缓存中；

InnoDB直接操作Buffer pool,使用单独的线程去定期将缓存的数据存储到磁盘上；

  • Buffer Pool

    • Buffer Pool

      缓存页面信息，包含数据页和索引页

      show status like "%innodb_buffer_pool%"
//默认大小是128M可以修改
//满了后执行LRU进行淘汰

    • change buffer

      提供修改的效率

      show variables like 'innodb_change_buffer_max_size';
//默认占bufferpool的1/4

    • Adaptive Hash Index

      存索引

    • log buffer(redo log)

      如果buffer pool数据没有写到磁盘，或者写的时候挂机了；

      把对页面的操作写到一个日志文件，用于恢复和支持事务

      show variables like 'innodb_log%'
show variables like 'innodb_log_buffer_size'

      为啥要使用日志文件存储不直接操作磁盘呢？

      因为顺序IO效率更高，提高系统吞吐;

      log buffer里面存缓存的日志信息，再保存到日志文件，那么什么时候刷到日志文件呢？

      show variable like 'innodb_flush_log_at_trx_commit'
# 0 没秒一次写入log file
# 1 默认，实时刷新，每一次提交事务就刷一次
# 2 结合了0和1，每秒提一次，提交事务也会提一次

5、磁盘结构

系统表空间

system tablespace

  • Innodb 数据字典

    内部系统表存储表和索引

  • 双写缓存区

    磁盘一页是4k，Innodb的一页是16K,那么写一页需要4次写，写的过程中出现问题，分写失效问题

    show variables like 'innodb_doublewrite';

    redo log是有问题的，对应的是页的副本，用这个页来的副本还原这个页，再去redo log;这样就是双写，提高可靠性；

  • changebuffer

  • undo log

独占表空间

file-per-table tablespace

show variables like 'innodb_file_per_table';
#默认是开启的，每一个表一个表空间

存储在idb中，表、索引；

其他数据还是共享空间里；

通用表空间

建立通用的表空间存储不同数据库的不同表

临时表空间

Redo log

Undo log

用于撤销和回滚，保证原子操作和事务

6、Binlog

它记录了所有的DDL和DML语句，由于是逻辑语句，用于主从复制和数据恢复

加了binlog后；那怎么个流程；

  • mysql的客户端提交一条sql语句；
  • mysql server层将该语句提交给Innodeb存储引擎执行;
  • innodb存储引擎将更新结果更新到内存中
  • innodb将这个命令记录到redo log 中这行的状态改为prepare
  • innodb说修改好了，给sever可以提交事务了
  • 记录到binlog
  • server commit提交事务
  • 修改redolog 这行的状态是commit

7、为什么使用B+Tree

为了协助快速查询的数据结构

  • 普通索引（Normal）

  • 唯一索引（Unique）

    内容不能重复，主键索引是个特例也不能为空

  • 全文索引（Fulltext）

    对于比较大的数据，如消息内容，like的效率低，可以创建全文索引

索引的演进类型

  • 二分查找

    内容是有序数组，查找快，插入和删除需要移动大量的数据，为了解决该问题，单链表效率依然不高，引入二分查找树（bst）

  • 二分查找树

    根据数据的特性，有可能退化为斜树，单链表，所以引入平衡二叉树（AVL树）AVL是人名

  • 平衡二叉树

    通过左旋右旋，将二分查找树转变为左右子树深度相差不大于1

    那么这个数据结构怎么定义呢？

    索引值、数据行地址、左后代指针、右后代指针

从InnoDB的存储结构可知道一页的大小是16k,每一次交互获取16k,那么用它来存一个节点，显然平衡二叉树太浪费了

  • 多路平衡查找树

    矮胖的数比高瘦的数更有效，路数等于节点存的个数+1

  • B+树（加强版的多路平衡查找树）

    • 关键字的数量等于路数

    • B+树的跟节点和枝节点不存储数据，只有叶子节点存储数据

      这样只需要1~3层深度就可以存储数千万的数据，最多访问3次磁盘

    • 左闭右开

    • 每一个叶子节点的最后一个数据指向下一个叶子节点的第一个数据，有序

mysql使用B+树的优势

  • 它是B树的变种，能解决，每个节点能存储更多的关键字，路数更多

  • 扫库扫表能力更强，如果扫描全表，只需要扫描叶子节点就行；

  • 由于根与枝节点不存数据，能存储的关键字更多，路数更多

  • 排序能力更好，因为叶子节点是有序的

  • 效率更加稳定，因为每次查询的效率都是一样的，永远从叶子节点获取数据

    红黑树只有两路，不够平衡，java的treemap

8、InnoDB逻辑存储结构

  • 表空间

    如上：系统表空间、独占表空间、通用表空间、临时表空间、Undo空间等；

  • 段

    创建一个索引，就会创建两个段，一个索引段（管理非叶子节点），数据段（管理叶子节点）；

  • 簇（区）

    段所管理的最小单元是区，每一区是1M,就是64个连续的页，段可以无限扩展，扩展的单元就是区

  • 页

    innodb最小的存储单元是页，16k,一个表空间最多是2的32次的页，就是一个表空间最大64T

  • 行

    Innodb是面向行的，数据存放时以行为单位的；行有两种格式；

    #查看行格式
show table status like 'user'\G;

9、B+树是如何应用在Mysql中的？

  • MYISAM

    使用myi存索引、myd存数据

    主键索引跟副索引一样

  • Innodb

    ibd存储索引和数据

    • 聚集索引

      索引的顺序跟数据行的顺序一致

      主键索引是聚集索引，非主键索引是非聚集索引;

      那么主键索引跟辅助索引有一个主次之分，辅助索引存储辅助索引跟主键值，辅助索引完成后再主键索引查找，为了保证b+树的矮胖特性，不能存储地址，地址太大了

    • 如果没有主键索引

      innodb会选择第一个没有null值得列充当主键索引；

      如果上一条也不满足，使用内置的rowid

10、索引使用规则

  • 列的离散度越高越好

  • 联合索引最左匹配原则

    • 最常用的列放在最左边

      建立一个联合索引后，如果使用第二的字段进行查询，是使用不到联合索引的

  • 覆盖索引

    因为如果建立的索引里包含了要查询的字段，所以辅助索引查询的时候就能得到数据，就不需要再去主键索引中获取数据，减少了一次扫表，大大提升了查询效率

    • ICP索引条件下推

      就是where中有两个查询条件时，这两个条件时覆盖索引，mysql将二级索引条件推给索引引擎，使用两级索引过滤数据，而不是到了server层过滤，这样减少了查询返回的数据，减少了io，提高了效率

  • 索引的创建和使用

    • 在where 判断、order排序、join的字句上创建索引
    • 索引的个数不要太多（浪费空间、更新慢）
    • 区分度低的字段不要使用索引（如 sex）
    • 频繁更新的字段不要使用索引（会造成页分裂）
    • 离散度高的字段放在组合索引的左边
    • 创建组合索引而不是修改单索引

  • 用不到索引的情况

    • 索引列上使用了计算、表达式、函数等

    • 字符串不加引号，出现了隐士转换

    • like条件左边有%

    • 负向查询

      not like != <> not in

Similar Posts

Comments

    Content