MySQL高级

MySQL version：5.7.20

一、MySQL架构

（一）Mysql的基本架构图

（二）连接器

（三）查询缓存

（四）分析器

（五）优化器

（六）Redo日志—innodb存储引擎的日志文件

redo日志属于InnoDB存储引擎层面的日志，用来记录事务对缓存页记录的改动，刷盘前改动数据放在redo日志文件中，刷盘后会删除掉，其主要是用来做崩溃恢复，以此来保证事务的持久性；可以通过参数的形式来控制redo日志文件的个数和大小，每个redo日志文件都有自己对应的编号；而binlog日志属于MySQL的server层面的日志，它是以追加的方式把对数据库数据的增删改逻辑操作保存起来，主要用于数据同步或主从复制。
往redo日志和binlog日志中写入数据是一个顺序I/O过程，效率比较高，因为它们是按顺序写入文件的。

（七）疑惑

既然要避免io，为什么写redo log的时候不会造成io的问题？

（八）Undo日志

（九）binlog-服务端的日志文件

1.binlog日志与redo日志的区别

2.binlog日志

（十）数据更新的流程

（十一）Redo log的两阶段提交

二、MySQL性能监控

（一）show profiles工具

show progiles工具是MySQL提供的用来分析SQL执行过程中资源消耗情况的工具，可用于SQL调优的测量，在当前会话中，默认情况下show profiles是处于关闭状态，打开之后默认保存最近15次的运行结果。针对运行慢的SQL，通过profiles工具进行详细分析，可以得到SQL运行过程中的资源开销情况，如IO开销、CPU开销、内存开销等。

1.官网show profile命令的使用

show profile命令的使用
如何找到show profile命令使用指导模块：
MySQL 5.7 Reference Manual->SQL Statements->Database Administration Statements->
SHOW Statements->SHOW PROFILE Statement

2.show profile cpu;

3.show profile all;

（二）performance_schema

1.performance_schema替代show profile和show profiles

高版本MySQL中show profile和show profiles被performance_schema替代掉。show profile和show profiles比performance_schema好用，因为它的功能不是很完善且实现比较简单。

（1）性能监控数据库performance_schema

performance_schema

2.performance_schema详解

见附件：MySQL的performance_schema详解.md

（三）show processlist

1.实时监控MySQL的连接个数

(1)数据库连接池

一般情况下公司里面都会使用数据库连接池来管理数据库连接。

①dbcp：现在已经很少使用

②c3p0：

③druid：性能较好，目前大多数公司使用

github上开源的druid连接池使用资料

2.官网show processlist命令的使用

（1）官网show processlist命令链接

（2）官网show processlist命令路径：

MySQL 5.7 Reference Manual->SQL Statements->Database Administration Statements->
SHOW Statements->SHOW PROCESSLIST Statement

三、schema与数据类型优化

（一）数据类型的优化

1.更小的通常更好

在psn和psn2表中各插入2w条数据，然后对比.ibd数据文件大小：
应该尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期更少，但是要确保没有低估需要存储的值的范围，如果无法确认哪个数据类型，就选择你认为不会超过范围的最小类型
案例：
设计两张表，设计不同的数据类型，查看表的容量。

CREATE TABLE `psn` (
  `id` int(10) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `psn2` (
  `id` int(10) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

public class Test {
   
    public static void main(String[] args) throws Exception{
   
        long start = System.currentTimeMillis();
        System.out.println("start: "+start);
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/lianpengju?useSSL=false&serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8","root","123456");
        PreparedStatement pstmt = conn.prepareStatement("insert into psn2 values(?,?)");
        for (int i = 0; i < 20000; i++) {
   
            pstmt.setInt(1,i);
            pstmt.setString(2,i+"");
            pstmt.addBatch();
        }
        pstmt.executeBatch();
        long end = System.currentTimeMillis();
        System.out.println("end: "+end);
        System.out.println("用时: "+(end-start)/1000+"s");
        conn.close();
    }
}

2.简单就好

简单数据类型的操作通常需要更少的CPU周期，例如，
1、整型比字符操作代价更低，因为字符集和校对规则是字符比较比整型比较更复杂，
2、使用mysql自建类型而不是字符串来存储日期和时间
3、用整型存储IP地址
案例：
创建两张相同的表，改变日期的数据类型，查看SQL语句执行的速度

CREATE TABLE `emp` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(4) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int(4) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `emp2` (
  `EMPNO` int(4) NOT NULL,
  `ENAME` varchar(10) DEFAULT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MGR` int(4) DEFAULT NULL,
  `HIREDATE` varchar(255) DEFAULT NULL,
  `SAL` double(7,2) DEFAULT NULL,
  `COMM` double(7,2) DEFAULT NULL,
  `DEPTNO` int(4) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.尽量避免null值

为了避免给后续带来麻烦，尽量避免将字段设置成可为null。另外，字段的默认值慎重设置，因为有可能默认值会出现在业务数据中，会产生干扰。不过，将字符类型的字段默认值设置成空字符还是可以的。
如果查询中包含可为NULL的列，对mysql来说很难优化，因为可为null的列使得索引、索引统计和值比较都更加复杂，坦白来说，通常情况下null的列改为not null带来的性能提升比较小，所有没有必要将所有的表的schema进行修改，但是应该尽量避免设计成可为null的列。

4.实际类型的优化

（1）整型

可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。
尽量使用满足需求的最小数据类型。

（2）字符和字符串类型

1、char长度固定，即每条数据占用等长字节空间；最大长度是255个字符，适合用在身份证号、手机号等定长字符串；
2、varchar可变程度，可以设置最大长度；最大空间是65535个字节，适合用在长度可变的属性；
3、text不设置长度，当不知道属性的最大长度时，适合用text；
按照查询速度：char>varchar>text。

①varchar根据实际内容长度保存数据

1、使用最小的符合需求的长度。
2、varchar(n) n小于等于255使用额外一个字节保存长度，n>255使用额外两个字节保存长度。
3、varchar(5)与varchar(255)保存同样的内容，硬盘存储空间相同，但内存空间占用不同，是指定的大小 。
4、varchar在mysql5.6之前变更长度，或者从255一下变更到255以上时，都会导致锁表。

应用场景
1、存储长度波动较大的数据，如：文章，有的会很短有的会很长
2、字符串很少更新的场景，每次更新后都会重算并使用额外存储空间保存长度
3、适合保存多字节字符，如：汉字，特殊字符等。

②char固定长度的字符串

1、最大长度：255
2、会自动删除末尾的空格