3.Mysql架构和性能优化

MySQL是C/S 架构的，connectors是连接器；可供Native C API、JDBC、ODBC、NET、PHP、Perl、Python、Ruby、Cobol等连接mysql；ODBC叫开放数据库（系统）互联，open database connection；JDBC是主要用于java语言利用较为底层的驱动连接数据库；以上这些，站在编程角度可以理解为连入数据库管理系统的驱动，站在mysql角度称作专用语言对应的链接器.

任何链接器连入mysql以后，mysql是单进程多线程模型的，因此，每个用户连接，都会创建一个单独的连接线程；其实mysql连接也有长短连接两种方式，使用mysql客户端连入数据库后，直到使用quit命令才退出，可认为是长连接；使用mysql中的-e选项，在mysql客户端向服务器端申请运行一个命令后则立即退出，也就意味着连接会立即断开；所以，mysql也支持长短连接类似于两种类型；所以，用户连入mysql后，创建一个连接线程，完成之后，能够通过这个链接线程完成接收客户端发来的请求，为其处理请求，构建响应报文并发给客户端；由于是单进程模型，就意味着必须要维持一个线程池，跟之前介绍过的varnish很接近，需要一个线程池来管理这众多线程是如何对众多客户端的并发请求，完成并发响应的，组件connection pool就是实现这样功能；connection pool对于mysql而言，它所实现的功能，包括authentication认证，用户发来的账号密码是否正确要完成认证功能；thread reuse线程重用功能，一般当一个用户连接进来以后要用一个线程来响应它，而后当用户退出这个线程有可能并非被销毁，而是把它清理完以后，重新收归到线程池当中的空闲线程中去，以完成所谓的线程重用；connection limit 线程池的大小决定了连接并发数量的上限，例如，最多容纳100线程，一旦到达此上限后续到达的连接请求则只能排队或拒绝连接；check memory用来检测内存，caches实现线程缓存；整个都属于线程池的功能.当用户请求之后，通过线程池建立一个用户连接，这个线程一直存在，然后用户就通过这个会话，发送对应的SQL语句到服务器端.

服务器收到SQL语句后，要对语句完成执行，首先要能理解sql语句需要有sql解释器或叫sql接口sql interface就可理解为是整个mysql的外壳，就像shell是linux操作系统的外壳一样；用户无论通过哪种链接器发来的基本的SQL请求，当然，事实上通过native C API也有发过来的不是SQL 请求，而仅仅是对API中的传递参数后的调用；不是SQL语句不过都统统理解为sql语句罢了；对SQL而言分为DDL 和DML两种类型，但是无论哪种类型，提交以后必须交给内核，让内核来运行，在这之前必须要告诉内核哪个是命令，哪个是选项，哪些是参数，是否存在语法错误等等；因此，这个整个SQL 接口就是一个完完整整的sql命令的解释器，并且这个sql接口还有提供完整的sql接口应该具备的功能，比如支持所谓过程式编程，支持代码块的实现像存储过程、存储函数，触发器、必要时还要实现部署一个关系型数据库应该具备的基本组件例如视图等等，其实都在sql interface这个接口实现的；SQL接口做完词法分析、句法分析后，要分析语句如何执行让parser解析器或分析器实现

parser是专门的分析器，这个分析器并不是分析语法问题的，语法问题在sql接口时就能发现是否有错误了，一个语句没有问题，就要做执行分析，所谓叫查询翻译，把一个查询语句给它转换成对应的能够在本地执行的特定操作；比如说看上去是语句而背后可能是执行的一段二进制指令，这个时候就完成对应的指令，还要根据用户请求的对象，比如某一字段查询内容是否有对应数据的访问权限，或叫对象访问权限；在数据库中库、表、字段、字段中的数据有时都称为object，叫一个数据库的对象，用户认证的通过，并不意味着就能一定能访问数据库上的所有数据，所以说，mysql的认证大概分为两过程都要完成，第一是连入时需要认证账号密码是否正确这是authentication，然后，验证成功后用户发来sql语句还要验证用户是否有权限获取它期望请求获取的数据；这个称为object privilege，这一切都是由parser分析器进行的

分析器分析完成之后，可能会生成多个执行树，这意味着为了能够达到访问期望访问到的目的，可能有多条路径都可实现，就像文件系统一样可以使用相对路径也可使用绝对路径；它有多种方式，在多种路径当中一定有一个是最优的，类似路由选择，因此，优化器就要去衡量多个访问路径中哪一个代价或开销是最小的，这个开销的计算要依赖于索引等各种内部组件来进行评估；而且这个评估的只是近似值，同时还要考虑到当前mysql内部在实现资源访问时统计数据，比如，根据判断认为是1号路径的开销最小的，但是众多统计数据表明发往1号路径的访问的资源开销并不小，并且比3号路径大的多，因此，可能会依据3号路径访问；这就是所谓的优化器它负责检查多条路径，每条路径的开销，然后评估开销，这个评估根据内部的静态数据，索引，根域根据动态生成的统计数据来判定每条路径的开销大小，因此这里还有statics；一旦优化完成之后，还要生成统计数据，这就是优化器的作用；如果没有优化器mysql执行语句是最慢的，其实优化还包括一种功能，一旦选择完一条路径后，例如用户给的这个命令执行起来，大概需要100个开销，如果通过改写语句能够达到同样目的可能只需要30个开销；于是，优化器还要试图改写sql语句；所以优化本身还包括查询语句的改写；一旦优化完成，接下来就交给存储引擎完成.mysql是插件式存储引擎，它就能够替换使用选择多种不同的引擎，MyISAM是MySQL 经典的存储引擎之一，InnoDB是由Innobase Oy公司所开发，2006年五月由甲骨文公司并购提供给MySQL的，NDB主要用于MySQL Cluster 分布式集群环境，archive做归档的等等，还有许多第三方开发的存储引擎；存储引擎负责把具体分析的结果完成对磁盘上文件路径访问的转换，数据库中的行数据都是存储在磁盘块上的，因此存储引擎要把数据库数据映射为磁盘块，并把磁盘块加载至内存中；进程实现数据处理时，是不可能直接访问磁盘上的数据的，因为它没有权限，只有让内核来把它所访问的数据加载至内存中以后，进程在内存中完成修改，由内核再负责把数据存回磁盘；对于文件系统而言，数据的存储都是以磁盘块方式存储的，但是，mysql在实现数据组织时，不完全依赖于磁盘，而是把磁盘块再次组织成更大一级的逻辑单位，类似于lvm中的PE或LE的形式；其实，MySQL的存储引擎在实现数据管理时，也是在文件系统之上布设文件格式，对于文件而言在逻辑层上还会再次组织成一个逻辑单位，这个逻辑单位称为mysql的数据块datablock 一般为16k ，对于关系型数据库，数据是按行存储的；一般一行数据都是存储在一起的，因此，MySQL 在内部有一个datablock，在datablock可能存储一行数据，也可能存放了n行数据；将来在查询加载一行数据时，内核会把整个一个数据数据块加载至内存中，而mysql存储引擎，就从中挑出来某一行返回给查询者，是这样实现的；所以整个存储是以datablock在底层为其最终级别的.

事实上，整个存取过程，尤其是访问比较热点的数据，也不可能每一次当用户访问时或当某SQL语句用到时再临时从磁盘加载到内存中，因此，为了能够加上整个性能，mysql的有些存储引擎可以实现，把频繁访问到的热点数据，统统装入内存，用户访问、修改时直接在内存中操作，只不过周期性的写入磁盘上而已，比如像InnoDB，所以caches和buffers组件就是实现此功能的；MySQL为了执行加速，因为它会不断访问数据，而随计算机来说io是最慢的一环，尤其是磁盘io，所以为了加速都载入内存中管理；这就需要MySQL 维护cache和buffer缓存或缓冲；这是由MySQL 服务器自己维护的；有很多存储引擎自己也有cache和buffer一个数据库提供了3种视图，物理视图就是看到的对应的文件系统存储为一个个的文件，MySQL的数据文件类型，常见的有redo log重做日志，undo log撤销日志，data是真正的数据文件，index是索引文件，binary log是二进制日志文件，error log错误日志，query log查询日志，slow query log慢查询日志，在复制架构中还存在中继日志文件，跟二进制属于同种格式；这是mysql数据文件类型，也就是物理视图；逻辑视图这是在mysql接口上通过存储引擎把mysql文件尤其是data文件，给它映射为一个个关系型数据库应该具备组成部分，比如表，一张表在底层是一个数据文件而已，里面组织的就是datablock，最终映射为磁盘上文件系统的block，然后再次映射为本地扇区的存储，但是整个mysql需要把他们映射成一个二维关系表的形式，需要依赖sql接口以及存储引擎共同实现；所以，把底层数据文件映射成关系型数据库的组件就是逻辑视图；DBA 就是关注内部组件是如何运作的，并且定义、配置其运作模式，而链接器都是终端用户通过链接器的模式进入数据库来访问数据；数据集可能非常大，每一类用户可能只有一部分数据的访问权限，这个时候，最终的终端用户所能访问到的数据集合称作用户视图；

为了保证MySQL运作还提供了管理和服务工具，例如:备份恢复工具，安全工具，复制工具，集群服务，管理、配置、迁移、元数据等工具

3.1存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力,此种技术称为存储擎,MySQL 支持多种存储引擎其中目前应用最广泛的是InnoDB和MyISAM两种

官方参考资料：

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/storage-engines.html
https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/storage-engines.html

Feature	MyISAM	Memory	InnoDB	Archive	NDB
B-tree indexes	Yes	Yes	Yes	No	No
Backup/point-in-time recovery (note 1)	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Clustered indexes	No	No	Yes	No	No
Compressed data	Yes (note 2)	No	Yes	Yes	No
Data caches	No	N/A	Yes	No	Yes
Encrypted data	Yes (note 3)	Yes (note 3)	Yes (note 4)	Yes (note 3)	Yes (note 3)
Foreign key support	No	No	Yes	No	Yes (note 5)
Full-text search indexes	Yes	No	Yes (note 6)	No	No
Geospatial data type support	Yes	No	Yes	Yes	Yes
Geospatial indexing support	Yes	No	Yes (note 7)	No	No
Hash indexes	No	Yes	No (note 8)	No	Yes
Index caches	Yes	N/A	Yes	No	Yes
Locking granularity	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
Replication support (note 1)	Yes	Limited (note 9)	Yes	Yes	Yes
Storage limits	256TB	RAM	64TB	None	384EB
T-tree indexes	No	No	No	No	Yes
Transactions	No	No	Yes	No	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes

3.1.1MyISAM 存储引擎

MyISAM 引擎特点

• 不支持事务
• 表级锁定
• 读写相互阻塞，写入不能读，读时不能写
• 只缓存索引
• 不支持外键约束
• 不支持聚簇索引
• 读取数据较快，占用资源较少
• 不支持MVCC（多版本并发控制机制）高并发
• 崩溃恢复性较差
• MySQL5.5.5 前默认的数据库引擎

MyISAM 存储引擎适用场景

• 只读（或者写较少）
• 表较小（可以接受长时间进行修复操作）

MyISAM 引擎文件

• tbl_name.frm 表格式定义
• tbl_name.MYD 数据文件
• tbl_name.MYI 索引文件

3.1.2 InnoDB 引擎

InnoDB引擎特点

• 行级锁
• 支持事务，适合处理大量短期事务
• 读写阻塞与事务隔离级别相关
• 可缓存数据和索引
• 支持聚簇索引
• 崩溃恢复性更好
• 支持MVCC高并发
• 从MySQL5.5后支持全文索引
• 从MySQL5.5.5开始为默认的数据库引擎

InnoDB数据库文件

• 所有InnoDB表的数据和索引放置于同一个表空间中

数据文件：ibdata1, ibdata2,存放在datadir定义的目录下
表格式定义：tb_name.frm,存放在datadir定义的每个数据库对应的目录下

• 每个表单独使用一个表空间存储表的数据和索引

两类文件放在对应每个数据库独立目录中
数据文件(存储数据和索引)：tb_name.ibd
表格式定义：tb_name.frm

启用：innodb_file_per_table=ON (MariaDB 5.5以后版是默认值)
参看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table

3.1.3其它存储引擎

• Performance_Schema：Performance_Schema数据库使用
• Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎
• MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库
• Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区
• Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境
• BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性
• Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性
• CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
• BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储
• example：”stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎

3.1.4管理存储引擎

查看mysql支持的存储引擎

mysql> mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)


mysql> show engines\G;
*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 4. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
9 rows in set (0.00 sec)

查看当前默认的存储引擎

mysql> mysql> show variables%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.06 sec)

设置默认的存储引擎

vim /etc/my.cnf
[mysqld]
default_storage_engine= InnoDB

查看库中所有表使用的存储引擎

mysql> show table status from hellodb\G;
*************************** 1. row ***************************
           Name: classes
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8
 Avg_row_length: 2048
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 9
    Create_time: 2021-12-06 15:35:17
    Update_time: 2021-12-06 15:35:17
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 2. row ***************************
           Name: coc
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 14
 Avg_row_length: 1170
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 14
    Create_time: 2021-12-06 15:35:17
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 3. row ***************************
           Name: courses
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 7
 Avg_row_length: 2340
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 7
    Create_time: 2021-12-06 15:35:17
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 4. row ***************************
           Name: scores
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 15
 Avg_row_length: 1092
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 15
    Create_time: 2021-12-06 15:35:17
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 5. row ***************************
           Name: students
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 25
 Avg_row_length: 655
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 25
    Create_time: 2021-12-06 15:35:17
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 6. row ***************************
           Name: teachers
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 4
    Create_time: 2021-12-06 15:35:17
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
*************************** 7. row ***************************
           Name: toc
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-12-06 15:35:17
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
7 rows in set (0.00 sec)

查看库中指定表的存储引擎

mysql> show table status like 'students'\G;
*************************** 1. row ***************************
           Name: students
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 25
 Avg_row_length: 655
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 25
    Create_time: 2021-12-06 15:35:17
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)


mysql> mysql> show create table students;
+----------+------------------------------------------------------+
| Table    | Create Table                                         |
+----------+------------------------------------------------------+
| students | CREATE TABLE `students` (
`StuID` int unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Age` tinyint unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint unsigned DEFAULT NULL,
`TeacherID` int unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb3         |
+----------+------------------------------------------------------+
1 row in set (0.00 sec)

设置表的存储引擎：

mysql> alter table students engine=innodb;          #修改表的存储引擎
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table test(name int) engine=innodb;   #建表的时候直接定义存储引擎
Query OK, 0 rows affected (0.01 sec)

3.2MySQL中的系统数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql 数据库
是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

information_schema 数据库
MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与”数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

performance_schema 数据库
MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

sys 数据库
MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DataBase的运行情况

3.3服务器配置和状态

可以通过mysqld选项，服务器系统变量和服务器状态变量进行MySQL的配置和查看状态
官方帮助：

https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html
https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/

注意：
其中有些参数支持运行时修改，会立即生效
有些参数不支持动态修改，且只能通过修改配置文件，并重启服务器程序生效
有些参数作用域是全局的，为所有会话设置
有些可以为每个用户提供单独（会话）的设置

3.3.1 服务器选项

注意: 服务器选项用横线,不用下划线
获取mysqld的可用选项列表：

#查看mysqld可用选项列表和及当前值
mysqld --verbose  --help

#获取mysqld当前启动选项
mysqld --print-defaults

[root@localhost ~]#mysqld --print-defaults
/usr/libexec/mysqld would have been started with the following arguments:
--default_authentication_plugin=mysql_native_password --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysql/mysqld.log --pid-file=/run/mysqld/mysqld.pid --default_storage_engine=InnoDB 

设置服务器选项方法：

1、在命令行中设置

[root@localhost ~]#/usr/libexec/mysqld --basedir=/usr

2、在配置文件my.cnf中设置

vim /etc/my.cnf
[mysqld]
skip_name_resolve=1
skip-grant-tables

#注意：skip-grant-tables是服务器选项,但不是系统变量
[root@localhost ~]#mysqladmin -uroot -p'sunxiang' variables |grep skip_grant_tables
mysqladmin: [Warning] Using a password on the command line interface can be insecure.

[root@localhost ~]#mysqladmin -uroot -p'sunxiang' variables |grep skip_name_resolve
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| skip_name_resolve                                        | OFF                       

不知道哪些是系统变量，哪些是服务器选择，哪些既是服务器选项也是系统变量可以通过官网文档进行查看
https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html
https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/

3.3.2服务器系统变量

服务器系统变量：可以分全局和会话两种

注意: 系统变量用下划线,不用横线

获取系统变量

SHOW GLOBAL VARIABLES; #只查看global变量
SHOW [SESSION] VARIABLES;#查看所有变量(包括global和session)

#查看指定的系统变量
SHOW VARIABLES LIKE 'VAR_NAME';
SELECT @@VAR_NAME;

#查看选项和部分变量
[root@centos8 ~]#mysqladmin variables

修改服务器变量的值：

help SET

修改全局变量：仅对修改后新创建的会话有效；对已经建立的会话无效

SET GLOBAL system_var_name=value;
SET @@global.system_var_name=value;

修改会话变量：

SET [SESSION] system_var_name=value;
SET @@[session.]system_var_name=value;

#character_set_results是系统变量并非服务器选项
#修改系统变量
root@db 09:42:  [(none)]show variables like 'character_set_results';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| character_set_results | utf8mb3 |
+-----------------------+---------+
1 row in set (0.00 sec)

root@db 09:42:  [(none)]set character_set_results="utf8mb4";
Query OK, 0 rows affected (0.00 sec)

root@db 09:43:  [(none)]show variables like 'character_set_results';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| character_set_results | utf8mb4 |
+-----------------------+---------+
1 row in set (0.00 sec)

#修改服务器选项
[root@localhost ~]#vim /etc/my.cnf.d/mysql-server.cnf 
[mysqld]
character_set_results=utf8mb4     

[root@localhost ~]#systemctl restart mysqld.service         #重启服务出现报错
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.

#结论：如果不是服务器选项无法通过配置文件进行修改，是系统变量切是Dynamic状态的可以直接通过set修改

修改mysql的最大并发连接数

注意: CentOS 8.2 已无此问题,CentOS7 仍有此问题

#默认值为151
[root@centos8 ~]#mysqladmin variables |grep 'max_connections'
| max_connections                    | 151

[root@centos8 ~]#mysql
MariaDB [(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 151  |
+-----------------+-------+
1 row in set (0.001 sec)

MariaDB [hellodb]> set global max_connections=2000;     #注意全局变量需要加上global参数
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name  | Value  |
+-----------------+-------+
| max_connections | 2000  |
+-----------------+-------+
1 row in set (0.001 sec)

[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
max_connections = 8000

[root@centos8 ~]#systemctl restart mariadb

[root@centos8 ~]#mysql -uroot -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> select @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|        594        |
+-------------------+
1 row in set (0.000 sec)

#方法1
[root@centos8 ~]#vim /usr/lib/systemd/system/mariadb.service
[Service]
#加下面一行
LimitNOFILE=65535

#方法2
[root@centos8 ~]#mkdir /etc/systemd/system/mariadb.service.d/
[root@centos8 ~]#vim /etc/systemd/system/mariadb.service.d/limits.conf
[Service]
LimitNOFILE=65535
[
root@centos8 ~]#systemctl daemon-reload
[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#mysql -uroot -p -e "select @@max_connections"
Enter password:
+-------------------+
| @@max_connections |
+-------------------+
|        8000       |
+-------------------+

修改页大小
参看：https://mariadb.com/kb/en/innodb-system-variables/#innodb_page_size
说明：初始化数据目录后，不能更改此系统变量的值。 在MariaDB实例启动时设置InnoDB的页面大小，此后保持不变。

[root@centos8 ~]#mysqladmin variables |grep innodb_page_size
| innodb_page_size                    | 16384

[root@centos8 ~]#mysql
MariaDB [(none)]> show variables like "innodb_page_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.001 sec)

[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
innodb_page_size=64k

[root@centos8 ~]#rm -rf /var/lib/mysql/*
[root@centos8 ~]#systemctl restart mariadb

[root@centos8 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like "innodb_page_size";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 65536 |
+------------------+-------+
1 row in set (0.001 sec)

3.3.3服务状态变量

服务器状态变量：分全局和会话两种
状态变量（只读）：用于保存mysqld运行中的统计数据的变量，不可更改

SHOW GLOBAL STATUS;
SHOW [SESSION] STATUS;

root@db 09:49:  [(none)]show status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

root@db 09:57:  [(none)]SHOW GLOBAL STATUS like 'com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 0     |
+---------------+-------+
1 row in set (0.00 sec)

3.3.4服务器变量SQL_MODE

SQL_MODE：对其设置可以完成一些约束检查的工作,可分别进行全局的设置或当前会话的设置

参考：
https://mariadb.com/kb/en/library/sql-mode/
https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sql-mode

常见MODE:

• NO_AUTO_CREATE_USER： 禁止GRANT创建密码为空的用户
• NO_ZERO_DATE：在严格模式，不允许使用’0000-00-00’的时间
• ONLY_FULL_GROUP_BY： 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么将认为这个SQL是不合法的
• NO_BACKSLASH_ESCAPES： 反斜杠”\”作为普通字符而非转义字符
• PIPES_AS_CONCAT： 将”||”视为连接操作符而非”或”运算符

root@db 10:00:  [hellodb]show variables like 'sql_mode';        #查看sql_mode
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

root@db 10:00:  [hellodb]select classid,count(*) from students group by classid;    #正确查询语句
+---------+----------+
| classid | count(*) |
+---------+----------+
|       2 |        3 |
|       1 |        4 |
|       4 |        4 |
|       3 |        4 |
|       5 |        1 |
|       7 |        3 |
|       6 |        4 |
|    NULL |        2 |
+---------+----------+
8 rows in set (0.00 sec)

root@db 10:00:  [hellodb]select stuid,classid,count(*) from students group by classid;  #不合理的查询语句
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'hellodb.students.StuID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

root@db 10:00:  [hellodb]set sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";     #修改sql_mode
Query OK, 0 rows affected (0.00 sec)

root@db 10:02:  [hellodb]select stuid,classid,count(*) from students group by classid;  #查询不合理语句      
+-------+---------+----------+
| stuid | classid | count(*) |
+-------+---------+----------+
|     1 |       2 |        3 |
|     2 |       1 |        4 |
|     4 |       4 |        4 |
|     5 |       3 |        4 |
|     6 |       5 |        1 |
|     8 |       7 |        3 |
|     9 |       6 |        4 |
|    24 |    NULL |        2 |
+-------+---------+----------+
8 rows in set (0.00 sec)

3.4INDEX索引

3.4.1 索引介绍

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现

优点：

• 索引可以降低服务需要扫描的数据量，减少了IO次数
• 索引可以帮助服务器避免排序和使用临时表
• 索引可以帮助将随机I/O转为顺序I/O

缺点：

• 占用额外空间，影响插入速度

索引类型：

• B+ TREE、HASH、R TREE、FULL TEXT
• 聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
• 主键索引、二级（辅助）索引
• 稠密索引、稀疏索引：是否索引了每一个数据项
• 简单索引、组合索引: 是否是多个字段的索引
• 左前缀索引：取前面的字符做索引
• 覆盖索引：从索引中即可取出要查询的数据，性能高

3.4.2 索引结构

参考链接 : https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

二叉树
参考链接: https://www.cs.usfca.edu/~galles/visualization/BST.html

红黑树

参考链接:https://www.cs.usfca.edu/~galles/visualization/RedBlack.html

B Tree 索引
参考链接: https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+Tree索引
参考链接: https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

B+Tree索引：按顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据

可以使用B+Tree索引的查询类型：(假设前提: 姓,名,年龄三个字段建立了一个复合索引)

• 全值匹配：精确所有索引列，如：姓sun，名xiang，年龄25
• 匹配最左前缀：即只使用索引的第一列，如：姓sun
• 匹配列前缀：只匹配一列值开头部分，如：姓以s开头的记录
• 匹配范围值：如：姓sun和姓zhang之间
• 精确匹配某一列并范围匹配另一列：如：姓sun,名以x开头的记录
• 只访问索引的查询

B+Tree索引的限制：

• 如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
• 不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列

特别提示：
索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

Hash索引
Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好

Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持
适用场景：只支持等值比较查询，包括=, <=>, IN()

不适合使用hash索引的场景

• 不适用于顺序查询：索引存储顺序的不是值的顺序
• 不支持模糊匹配
• 不支持范围查询
• 不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效

地理空间数据索引R-Tree（ Geospatial indexing ）

• MyISAM支持地理空间索引，可使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多
• InnoDB从MySQL5.7之后也开始支持

全文索引(FULLTEXT)

• 在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎
• InnoDB从MySQL 5.6之后也开始支持

聚簇和非聚簇索引，主键和二级索引

参考博客：

https://blog.csdn.net/lm1060891265/article/details/81482136
https://www.cnblogs.com/jiawen010/p/11805241.html

冗余和重复索引：

• 冗余索引：（A），（A，B）,注意如果同时存在,仍可能会使用(A)索引
• 重复索引：已经有索引，再次建立索引

3.4.3索引优化

参考资料: 阿里的《Java开发手册》

https://developer.aliyun.com/topic/java2020

• 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）
• 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度
• 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
• 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
• 只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
• 对于经常在where子句使用的列，最好设置索引
• 对于有多个列where或者order by子句，应该建立复合索引
• 对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引
• 尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高
• 不要使用RLIKE正则表达式会导致索引失效
• 查询时，能不要就不用，尽量写全字段名，比如:select id,name,age from students;
• 大部分情况连接效率远大于子查询
• 在有大量记录的表分页时使用limit
• 对于经常使用的查询，可以开启查询缓存
• 多使用explain和profile分析查询语句
• 查看慢查询日志，找出执行时间长的sql语句优化

3.4.4管理索引

创建索引：

CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]);
help CREATE INDEX;

删除索引：

DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引：

SHOW INDEX FROM [db_name.]tbl_name;

优化表空间：

OPTIMIZE TABLE tb_name;

查看索引的使用

SET GLOBAL userstat=1;  #MySQL无此变量
SHOW INDEX_STATISTICS;

#mariadb
MariaDB [hellodb]> SET GLOBAL userstat=1;       
Query OK, 0 rows affected (0.000 sec)

MariaDB [hellodb]> SHOW INDEX_STATISTICS;
Empty set (0.000 sec)


MariaDB [hellodb]> show index from students\G
*************************** 1. row ***************************
    Table: students
 Non_unique: 0
  Key_name: PRIMARY
Seq_in_index: 1
Column_name: StuID
  Collation: A
Cardinality: 25
  Sub_part: NULL
   Packed: NULL
    Null:
 Index_type: BTREE
   Comment:
Index_comment:
1 row in set (0.000 sec)

MariaDB [hellodb]> SHOW INDEX_STATISTICS;
Empty set (0.000 sec)

MariaDB [hellodb]> select * from students where stuid=10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|   10  | Yue Lingshan |  19 | F      |    3    |    NULL   |
+-------+--------------+-----+--------+---------+-----------+
1 row in set (0.000 sec)

MariaDB [hellodb]> SHOW INDEX_STATISTICS;           #此功能mysql没有
+--------------+------------+------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+------------+-----------+
| hellodb      | students   | PRIMARY    |     1     |
+--------------+------------+------------+-----------+

MariaDB [hellodb]> select * from students where stuid=10;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name         | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
|   10 | Yue Lingshan  |  19 | F      |    3    |    NULL   |
+-------+--------------+-----+--------+---------+-----------+
1 row in set (0.000 sec)

MariaDB [hellodb]> SHOW INDEX_STATISTICS;
+--------------+------------+------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+------------+-----------+
| hellodb      | students   | PRIMARY    |     2     |
+--------------+------------+------------+-----------+
1 row in set (0.000 sec)



#mysql
root@db 12:36:  [hellodb]show index from students\G
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: StuID
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

root@db 12:41:  [hellodb]explain select *from students where stuid=10\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
   partitions: NULL
         type: const
possible_keys: PRIMARY          #可用索引，主键索引
          key: PRIMARY          #使用的索引，主键索引
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

3.4.5EXPLAIN工具

可以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询

参考资料： https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

语法：

EXPLAIN SELECT clause

EXPLAIN输出信息说明：

Column	JSON Name	Meaning
id	select_id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	None	简单查询：SIMPLE|复杂查询：PRIMARY（最外面的SELECT）、DERIVED（用于FROM中的子查询）、UNION（UNION语句的第一个之后的SELECT语句）、UNION RESUlT（匿名临时表）、SUBQUERY（简单子查询）The matching partitions
table	table_name	访问引用哪个表（引用某个查询，如“derived3”）
partitions	partitions	匹配的分区
type	access_type	关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式
possible_keys	possible_keys	查询可能会用到的索引
key	key	显示mysql决定采用哪个索引来优化查询
key_len	key_length	显示mysql在索引里使用的字节数
ref	ref	当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows	rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数
filtered	filtered	按表条件过滤的行百分比
Extra	None	额外信息 Using index：MySQL将会使用覆盖索引，以避免访问表 Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary：MySQL对结果排序时会使用临时表 Using filesort：对结果使用一个外部索引排序

说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：NULL> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL //最好到最差
备注：掌握以下10种常见的即可
NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

类型	说明
All	最坏的情况,全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 （高效）
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）

3.4.6使用profile工具

#打开后，会显示语句执行详细的过程
set profiling = ON;

#查看语句,注意结果中的query_id值
show profiles ;

root@db 14:09:  [hellodb]show profiles;
Empty set, 1 warning (0.00 sec)

root@db 14:09:  [hellodb]explain select *from students where stuid=10;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

root@db 14:09:  [hellodb]show profiles;
+----------+------------+----------------------------------------------+
| Query_ID | Duration   | Query                                        |
+----------+------------+----------------------------------------------+
|        1 | 0.00053000 | explain select *from students where stuid=10 |
+----------+------------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)


#显示语句的详细执行步骤和时长
Show profile for query

root@db 14:09:  [hellodb]show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000160 |
| Executing hook on transaction  | 0.000006 |
| starting                       | 0.000023 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000073 |
| init                           | 0.000018 |
| System lock                    | 0.000011 |
| optimizing                     | 0.000009 |
| statistics                     | 0.000104 |
| preparing                      | 0.000052 |
| explaining                     | 0.000033 |
| end                            | 0.000003 |
| query end                      | 0.000002 |
| waiting for handler commit     | 0.000008 |
| closing tables                 | 0.000005 |
| freeing items                  | 0.000012 |
| cleaning up                    | 0.000006 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)


#显示cpu使用情况
Show profile cpu for query

root@db 14:13:  [hellodb]show profile cpu for query 2;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000102 | 0.000051 |   0.000049 |
| Executing hook on transaction  | 0.000005 | 0.000002 |   0.000002 |
| starting                       | 0.000007 | 0.000004 |   0.000003 |
| checking permissions           | 0.000006 | 0.000003 |   0.000003 |
| Opening tables                 | 0.000039 | 0.000020 |   0.000019 |
| init                           | 0.000004 | 0.000002 |   0.000002 |
| System lock                    | 0.000055 | 0.000024 |   0.000023 |
| optimizing                     | 0.000012 | 0.000006 |   0.000006 |
| statistics                     | 0.000046 | 0.000024 |   0.000023 |
| preparing                      | 0.000012 | 0.000006 |   0.000005 |
| executing                      | 0.000009 | 0.000004 |   0.000005 |
| end                            | 0.000002 | 0.000001 |   0.000000 |
| query end                      | 0.000002 | 0.000001 |   0.000002 |
| waiting for handler commit     | 0.000007 | 0.000004 |   0.000003 |
| closing tables                 | 0.000005 | 0.000002 |   0.000002 |
| freeing items                  | 0.000012 | 0.000006 |   0.000006 |
| cleaning up                    | 0.000006 | 0.000003 |   0.000003 |
+--------------------------------+----------+----------+------------+
17 rows in set, 1 warning (0.00 sec)

3.5并发控制

3.5.1锁机制

锁类型：

• 读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务） ，多个读互不阻塞
• 写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写
• S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容，举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况也称为锁冲突

锁粒度：

• 表级锁：MyISAM
• 行级锁：InnoDB

实现

• 存储引擎：自行实现其锁策略和锁粒度
• 服务器级：实现了锁，表级锁，用户可显式请求

分类：

• 隐式锁：由存储引擎自动施加锁
• 显式锁：用户手动请求

锁策略：在锁粒度及数据安全性寻求的平衡机制

3.5.2 显式使用锁

帮助:https://mariadb.com/kb/en/lock-tables/

加锁

LOCK TABLES tbl_name [[AS] alias] lock_type  [, tbl_name [[AS] alias]
lock_type] ...

lock_type:
READ                #读锁
WRITE               #写锁

解锁

UNLOCK TABLES

关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁

FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]

查询时加写或读锁

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]

root@db 15:09:  [hellodb]lock tables students read;     #加read锁
Query OK, 0 rows affected (0.00 sec)

root@db 15:09:  [hellodb]update students set classid=2 where stuid=24;
ERROR 1100 (HY000): Table 'sutdents' was not locked with LOCK TABLES

root@db 15:10:  [hellodb]select *from students where stuid=24;
+-------+---------+-----+--------+---------+-----------+
| StuID | Name    | Age | Gender | ClassID | TeacherID |
+-------+---------+-----+--------+---------+-----------+
|    24 | Xu Xian |  27 | M      |    NULL |      NULL |
+-------+---------+-----+--------+---------+-----------+
1 row in set (0.00 sec)


root@db 15:13:  [hellodb]unlock tables students;        #删锁，啥都不加才能删
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'students' at line 1
root@db 15:13:  [hellodb]unlock tables;
Query OK, 0 rows affected (0.00 sec)

root@db 15:13:  [hellodb]update students set classid=2 where stuid=24;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

root@db 15:13:  [hellodb]select *from students where stuid=24;
+-------+---------+-----+--------+---------+-----------+
| StuID | Name    | Age | Gender | ClassID | TeacherID |
+-------+---------+-----+--------+---------+-----------+
|    24 | Xu Xian |  27 | M      |       2 |      NULL |
+-------+---------+-----+--------+---------+-----------+
1 row in set (0.00 sec)

root@db 15:13:  [hellodb]

#同时对同一行记录执行update
#在第一终端提示1行成功
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1 Changed: 1 Warnings: 0

#在第二终端提示0行修改
MariaDB [hellodb]> update students set classid=1 where stuid=24;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1 Changed: 0 Warnings: 0

3.5.3事物

事务 Transactions：一组原子性的 SQL语句，或一个独立工作单元

事务日志：记录事务信息，实现undo,redo等故障恢复功能

3.5.3.1 事务特性

ACID特性：

• A：atomicity 原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
• C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
• I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
• D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库中

Transaction 生命周期

3.5.3.2 管理事务

显式启动事务：

BEGIN
BEGIN WORK
START TRANSACTION

结束事务：

#提交,相当于vi中的wq保存退出
COMMIT

#回滚,相当于vi中的q!不保存退出
ROLLBACK

注意：只有事务型存储引擎中的DML语句方能支持此类操作

自动提交：

set autocommit={1|0}

默认为1，为0时设为非自动提交
建议：显式请求和提交事务，而不要使用”自动提交”功能

事务支持保存点：

SAVEPOINT identifier
ROLLBACK [WORK] TO [SAVEPOINT] identifier
RELEASE SAVEPOINT identifier

查看事务：

#查看当前正在进行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

#以下两张表在MySQL8.0中已取消
#查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
#查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

死锁：
两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态

3.5.3.3 事务隔离级别

MySQL 支持四种隔离级别，事务隔离级别从上至下更加严格

隔离级别	脏读	不可重复读	幻读	加读锁
读未提交	可以出现	可以出现	可以出现	否
读提交	不允许出现	可以出现	可以出现	否
可重复读	不允许出现	不允许出现	可以出现	否
序列化	不允许出现	不允许出现	不允许出现	是

• READ UNCOMMITTED
  可读取到未提交数据，产生脏读
• READ COMMITTED
  可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
• REPEATABLE READ
  可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
• SERIALIZABLE
  可串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞其它事务的读写（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差

MVCC和事务的隔离级别：
MVCC（多版本并发控制机制）只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

指定事务隔离级别：

• 服务器变量tx_isolation(MySQL8.0改名为transaction_isolation)指定，默认为REPEATABLE-READ，可在GLOBAL和SESSION级进行设置

#MySQL8.0之前版本
SET tx_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'

#MySQL8.0
SET transaction_isolation='READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE'

• 服务器选项中指定

vim /etc/my.cnf
[mysqld]
transaction-isolation=SERIALIZABLE