MySQL高级

vampire 2020年09月15日 23次浏览

MySQL架构

MySql架构

  1. 连接层

最上层是一些客户端和连接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

  1. 服务层

    • Management Serveices & Utilities: 系统管理和控制工具

    • SQL Interface: SQL接口

      ​ 接受用户的SQL命令,并且返回用户需要查询的结果。比如select from就是调用SQL Interface

    • Parser: 解析器

      ​ SQL命令传递到解析器的时候会被解析器验证和解析。

    • Optimizer: 查询优化器。

      ​ SQL语句在查询之前会使用查询优化器对查询进行优化。

      ​ 用一个例子就可以理解: select uid,name from user where gender= 1;

      ​ 优化器来决定先投影还是先过滤。

    • Cache和Buffer: 查询缓存。

      ​ 如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。

      ​ 这个缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等

  2. 引擎层

     存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
    
  3. 存储层

     数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互。
    

MySQL字符集

  1. 查看字符集

    show variables like 'character%';

    show variables like '%char%';

  2. 修改my-huge.cnf

    在/usr/share/mysql/ 中找到my-huge.cnf的配置文件,

    拷贝其中的my-huge.cnf 到 /etc/ 并命名为my.cnf

    然后修改my.cnf:

    [client]

    default-character-set=utf8

    [mysqld]

    character_set_server=utf8

    character_set_client=utf8

    collation-server=utf8_general_ci

    [mysql]

    default-character-set=utf8

  3. 重新启动mysql

    查看原库的字符集:show create database mydb

    但是原库的设定不会发生变化,参数修改之对新建的数据库生效

  4. 已生成的库表字符集如何变更

    修改数据库的字符集

    mysql> alter database mydb character set 'utf8';

    修改数据表的字符集

    mysql> alter table mytbl convert to character set 'utf8';

    但是原有的数据如果是用非'utf8'编码的话,数据本身不会发生改变。

sql的执行周期

先开启 show variables  like '%profiling%';
set profiling=1;
select * from xxx ;
show profiles;     #显示最近的几次查询
show profile cpu,block io for query Query_ID  #查看程序的执行步骤

查询说明

- 查询流程图:
	首先,mysql的查询流程大致是:
	* mysql客户端通过协议与mysql服务器建连接,发送查询语句,先检查查询缓存,如果命中,直接返回结果,否则进行语句解析,也就是说,在解析查询之前,服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中,服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可,这将大大提高系统的性能。
	* 语法解析器和预处理:首先mysql通过关键字将SQL语句进行解析,并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询;预处理器则根据一些mysql规则进一步检查解析数是否合法。
	* 查询优化器当解析树被认为是合法的了,并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。。
	* 然后,mysql默认使用的BTREE索引,并且一个大致方向是:无论怎么折腾sql,至少在目前来说,mysql最多只用到表中的一个索引。

sql执行顺序

  • 手写
    1573472662983
  • 机读
    1573472609413

MySQL引擎

  • 查看引擎

     # 查看mysql现在已提供什么存储引擎:
      mysql> show engines;
     # 查看mysql当前默认的存储引擎:
      mysql> show variables like '%storage_engine%';
    
  • 引擎分类

    1. InnoDB存储引擎
        InnoDB是MySQL的默认事务型引擎,它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎。
    2. MyISAM存储引擎
        MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务和行级锁,有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
    3. Archive引擎
        Archive档案存储引擎只支持INSERT和SELECT操作,在MySQL5.1之前不支持索引。
        Archive表适合日志和数据采集类应用。
        根据英文的测试结论来看,Archive表比MyISAM表要小大约75%,比支持事务处理的InnoDB表小大约83%。
    4. Blackhole引擎
        Blackhole引擎没有实现任何存储机制,它会丢弃所有插入的数据,不做任何保存。但服务器会记录Blackhole表的日志,所以可以用于复制数据到备库,或者简单地记录到日志。但这种应用方式会碰到很多问题,因此并不推荐。 
    5. CSV引擎 
        CSV引擎可以将普通的CSV文件作为MySQL的表来处理,但不支持索引。
        CSV引擎可以作为一种数据交换的机制,非常有用。
        CSV存储的数据直接可以在操作系统里,用文本编辑器,或者excel读取。
    6. Memory引擎
        如果需要快速地访问数据,并且这些数据不会被修改,重启以后丢失也没有关系,那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。
    7. Federated引擎
        Federated引擎是访问其他MySQL服务器的一个代理,尽管该引擎看起来提供了一种很好的跨服务器的灵活性,但也经常带来问题,因此默认是禁用的。
    
    • InnoDB和MyISAM
      1573473323747
  • 阿里数据库引擎

    img

    • Percona 为 MySQL 数据库服务器进行了改进,在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具;另外有更多的参数和命令来控制服务器行为。

    • 该公司新建了一款存储引擎叫xtradb完全可以替代innodb,并且在性能和并发上做得更好,

    • 阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。

    • AliSql+AliRedis

索引优化分析

引入原因

* 性能下降SQL慢
* 执行时间长
* 等待时间长
	- 数据过多:分库分表
    - 关联太多的表,太多join: SQL优化
    - 没有充分利用到索引: 索引建立
    - 服务器调优及各个参数设置:调整my.cnf

SQL预热

  • 常用的join

索引

  • 定义

    1. MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
    2. 可以得到索引的本质:索引是数据结构。
    	在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,
    3. 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上
    

    这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。下图就是一种可能的索引方式示例:

    img

    左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址

    为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到相应数据,从而快速的检索出符合条件的记录。

  • 优势和劣势

    * 优势:
    	提高检索效率,降低IO成本
    	利用索引进行排序,降低排序成本。降低CPU成本
    * 劣势
    	降低更新表的速度
    	占用空间,增加存储空间消耗
    
  • 索引结构

    • BTree索引
      img

      * 【初始化介绍】 
          一颗b树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),
          如磁盘块1包含数据项17和35,包含指针P1、P2、P3,
          P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。
          真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
          非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。
      * 【查找过程】
          如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。
          真实的情况是,3层的b树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。
      
    • B+Tree索引
      image-20200915203051657

    • BTree和B+Tree的区别

      * B+Tree与B-Tree 的区别
          1)B-树的关键字和记录是放在一起的,叶子节点可以看作外部节点,不包含任何信息;B+树的非叶子节点中只有关键字和指向下一个节点的索引,记录只放在叶子节点中。
          2)在B-树中,越靠近根节点的记录查找时间越快,只要找到关键字即可确定记录的存在;而B+树中每个记录的查找时间基本是一样的,都需要从根节点走到叶子节点,而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好,而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据,这样每个节点可容纳的元素个数比B-树多,树高比B-树小,这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多,但是一次磁盘访问的时间相当于成百上千次内存比较的时间,因此实际中B+树的性能可能还会好些,而且B+树的叶子节点使用指针连接在一起,方便顺序遍历(例如查看一个目录下的所有文件,一个表中的所有记录等),这也是很多数据库和文件系统使用B+树的缘故。
      
    • 时间复杂度
      img

    • 思考:为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引?

      * 1) B+树的磁盘读写代价更低 
          B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。 
      * 2) B+树的查询效率更加稳定 
          由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
      
    • 聚簇索引与非聚簇索引

      • 聚簇索引

        * 聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。
        * 好处
        	按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不用从多个数据块中提取数据,所以节省了大量的io操作。
        * 坏处
        	- 对于mysql数据库目前只有innodb数据引擎支持聚簇索引,而Myisam并不支持聚簇索引。
        	- 由于数据物理存储排序方式只能有一种,所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
            - 为了充分利用聚簇索引的聚簇的特性,所以innodb表的主键列尽量选用有序的顺序id,而不建议用无序的id,比如uuid这种。
        

      1573481184423

  • 索引分类

    • 单值索引

      * 即一个索引只包含单个列,一个表可以有多个单列索引
      * 创建
      	- key(字段名):随表创建
      	- create index 索引名 ON 表名(字段名);
      * 删除
      	- drop index 索引名 ON 表名
      
    • 唯一索引

      * 索引列的值必须唯一,但允许有空值
      * 创建
      	- UNIQUE (customer_no)
      	- CREATE UNIQUE INDEX 索引名 ON 表名(字段名); 
      * 删除
      	DROP INDEX 索引名 on 表名;
      
    • 主键索引

      * 设定为主键后数据库会自动建立索引,innodb为聚簇索引
      * 创建
      	- PRIMARY KEY(id) 随表创建
      	- ALTER TABLE 表名 add PRIMARY KEY 表名(字段名);  
      * 删除建主键索引:
      	- ALTER TABLE 表名 drop PRIMARY KEY ;  
      * 修改建主键索引:
      	必须先删除掉(drop)原索引,再新建(add)索引
      
    • 复合索引

      * 即一个索引包含多个列
      
  • 索引是否应用

    • 适用场景

      * 主键自动建立唯一索引
      * 频繁作为查询条件的字段应该创建索引
      * 查询中与其它表关联的字段,外键关系建立索引
      * 单键/组合索引的选择问题, 组合索引性价比更高
      * 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
      * 查询中统计或者分组字段
      
    • 不适用场景

      * 表记录太少
      * 经常增删改的表或者字段
      * Where条件里用不到的字段不创建索引
      * 过滤性不好的不适合建索引
      	eg: 根据性别过滤
      

性能分析(Explain)

  • 执行计划

    * 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
    

主从复制

复制原理

* MySQL复制过程分成三步:
	1. master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件,binary log events;
	2. slave将master的binary log events拷贝到它的中继日志(relay log);
	3. slave重做中继日志中的事件,将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的