本文主要涉及MySQL核心组件、查询过程、索引等原理，另外还涉及一些不常用但较重要的命令。除此之外，一些运维的理论和命令也有提及。

组成原理

主要介绍MySQL的核心组件，以及查询执行过程。

组件

直接上图

查询过程

客户端发送一条查询给服务器；
服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
服务器端进行SQL解析、预处理，再由优化器生成优化后对应的执行计划；
根据优化器生成的执行计划，调用存储引擎的API来执行查询；
将结果返回给客户端。

查询过程

存储引擎

常用MySql存储引擎比较，这里重点讲MyISAM和InnoDB。

指标	MyISAM	InnoDB
事务	不支持	支持
读写效率	高	低
索引	支持全文索引	不支持全文索引
外键	不支持	支持
锁	表锁	行锁
文件存储形式	.MYD .MYI *.FRM	*.FRM(默认为共享表空间，可修改)
适用场景	大量select语句	大量update语句
删除表后数据文件是否存在	自动清除	不自动清除

锁

三种类型

表级锁：(MyISAM/Memory引擎) 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：(InnoDB引擎) 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁：(BDB引擎) 开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

两种模式

共享锁（读锁), 不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
排它锁（写锁), 会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

索引

索引: 是一种用来实现MySQL高效获取数据的数据结构。在某个字段上建索引, 就是让MySQL对该字段以索引这种数据结构来存储, 然后查找的时候就有对应的查找算法。

InnoDB使用B+Tree, B+Tree中的B是指balance, 意为平衡。需要注意的是, B+树索引并不能找到一个给定键值的具体行, 它找到的只是被查找数据行所在的页, 接着数据库会把页读入到内存, 再在内存中进行查找, 最后得到要查找的数据。

算法介绍

平衡二叉树

可以是空树
如果不是空树，任何一个结点的左子树与右子树都是平衡二叉树，并且高度之差的绝对值不超过1

B-树

B-树是一种多路自平衡的搜索树, 它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。

B+树

所有关键字存储在叶子节点出现, 内部节点(非叶子节点并不存储真正的 data)
为所有叶子结点增加了一个链指针

索引类型

普通索引 index：没有任何限制
唯一索引 unique：允许为空值
主键 primaryKey：有值且唯一

操作命令

创建索引

mysql> create index index1 on t_table(name);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table t_table add index index2 (grade);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看索引

mysql> show index from t_table;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_table |          1 | index1   |            1 | name        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
| t_table |          1 | index2   |            1 | grade       | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

删除索引

mysql> alter table t_table drop index index1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> drop index index2 on t_table;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

SQL语句

SQL语句可以划分为三个类别：

DDL(Data Definition Languages)语句
数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter 等。
DML(Data Manipulation Language)语句
数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、update 和 select 等。
DCL(Data Control Language)语句
数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

主从复制

基本流程

slave端的IO线程连接上master端，并请求从指定binlog日志文件的指定pos节点位置(或者从最开始的日志)开始复制之后的日志内容。
master端在接收到来自slave端的IO线程请求后，通知负责复制进程的IO线程，根据slave端IO线程的请求信息，读取指定binlog日志指定pos节点位置之后的日志信息，然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外，还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。
slave端的IO线程在接收到master端IO返回的信息后，将接收到的binlog日志内容依次写入到slave端的relaylog文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的master端的binlog文件名和pos节点位置记录到master-info（该文件存在slave端）文件中，以便在下一次读取的时候能够清楚的告诉master “我需要从哪个binlog文件的哪个pos节点位置开始，请把此节点以后的日志内容发给我”。
slave端的SQL线程在检测到relaylog文件中新增内容后，会马上解析该log文件中的内容。然后还原成在master端真实执行的那些SQL语句，并在自身按顺序依次执行这些SQL语句。这样，实际上就是在master端和slave端执行了同样的SQL语句，所以master端和slave端的数据是完全一样的。

配置流程

在master库执行

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

1	[root@master ~]# mysqldump -uroot -p rep > /usr/local/mysql/rep.sql

1 2	mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)

在slave库执行

1	mysql < /usr/local/mysql/rep.sql

[root@slave ~]# vim /etc/my.cnf 
server-id=2          // 设置server-id，不可重复
log-bin=mysql-bin    // 开启二进制日志，用于链式复制的情况下，即这台服务器如果需要作为其他从服务器的主服务器，则需要开启这个选项
read_only = 1       // 设置为只读
relay_log = mysql-relay-bin  //配置中继日志
log_slave_updates = 1        // 表示slave将复制事件写进自己的二进制日志

mysql> change master to
    -> master_host='192.168.30.110',
    -> master_user='rep',
    -> master_password='123456',
    -> master_log_file='mysql-bin.000005',
    -> master_log_pos=107;
Query OK, 0 rows affected (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

常用操作

帮助命令

mysql> ? index;
Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following
topics:
   ALTER TABLE
   CACHE INDEX
   CREATE INDEX
   CREATE TABLE
   DROP INDEX
   JOIN
   LOAD INDEX
   SHOW
   SHOW INDEX
   SPATIAL

查看系统时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-09-21 08:16:48 |
+---------------------+
1 row in set (0.00 sec)

mysql> select current_time;
+--------------+
| current_time |
+--------------+
| 08:17:02     |
+--------------+
1 row in set (0.00 sec)

创建表

mysql> create table t_table(id int(11),
    -> name varchar(20),
    -> grade float );
Query OK, 0 rows affected (0.02 sec)

查询表结构

mysql> describe t_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| grade | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

查询表创建语句

mysql> show create table t_table;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_table | CREATE TABLE `t_table` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `grade` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t_table\G;
*************************** 1. row ***************************
       Table: t_table
Create Table: CREATE TABLE `t_table` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `grade` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR:
No query specified

插入部分数据

mysql> insert into t_table(id,name) values(1,"henry");
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_table;
+------+-------+-------+
| id   | name  | grade |
+------+-------+-------+
|    1 | henry |  NULL |
+------+-------+-------+
1 row in set (0.00 sec)

插入完整数据

mysql> insert into t_table values(2,"cc", 3.30);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_table;
+------+-------+-------+
| id   | name  | grade |
+------+-------+-------+
|    1 | henry |  NULL |
|    2 | cc    |   3.3 |
+------+-------+-------+
2 rows in set (0.00 sec)

更新数据

mysql> update t_table set grade=1.10 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_table;
+------+-------+-------+
| id   | name  | grade |
+------+-------+-------+
|    1 | henry |   1.1 |
|    2 | cc    |   3.3 |
+------+-------+-------+
2 rows in set (0.00 sec)

清空表

mysql> select * from table2;
+------+------+
| id   | xx   |
+------+------+
|    1 | 323  |
+------+------+
1 row in set (0.00 sec)

mysql> truncate table table2;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from table2;
Empty set (0.00 sec)

查询状态

mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

Connection id:		2
Current database:	test
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.23 MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			1 hour 36 min 12 sec

Threads: 1  Questions: 48  Slow queries: 0  Opens: 108  Flush tables: 1  Open tables: 101  Queries per second avg: 0.008
--------------

修改存储引擎

mysql> alter table t_table engine=myisam;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show create table t_table;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_table | CREATE TABLE `t_table` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `grade` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改慢查询

mysql> show variables like '%slow%';
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| log_slow_admin_statements | OFF                                  |
| log_slow_slave_statements | OFF                                  |
| slow_launch_time          | 2                                    |
| slow_query_log            | OFF                                  |
| slow_query_log_file       | /var/lib/mysql/56bd89bc82a8-slow.log |
+---------------------------+--------------------------------------+
5 rows in set (0.01 sec)

mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.01 sec)

mysql> show variablesshow master status; like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/lib/mysql/56bd89bc82a8-slow.log |
+---------------------+--------------------------------------+

mysql> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)

mysql> exit
Bye

root@56bd89bc82a8:/# mysqldumpslow /var/lib/mysql/56bd89bc82a8-slow.log

Reading mysql slow query log from /var/lib/mysql/56bd89bc82a8-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  mysqld, Version: N.N.N (MySQL Community Server (GPL)). started with:
  # Time: N-N-21T13:N:N.266357Z
  # User@Host: root[root] @ localhost []  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  use test;
  SET timestamp=N;
  select sleep(N)

查看进程

mysql> show full processlist\G
*************************** 1. row ***************************
     Id: 2
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: starting
   Info: show full processlist
1 row in set (0.00 sec)

性能分析

mysql> explain select * from t_table;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

explain查看方法：

先看查询类型“type”列，如果为ALL就不用看后面的了，因为是全表扫描；
再看“key”列，看是否使用了索引；
再看“rows”列，表示在SQL执行过程中扫描的行数；

半同步部署

Master

   # 安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME
# 启用半同步：
SET @@GLOBAL.rpl_semi_sync_master_enabled=1;
rpl_semi_sync_master_wait_point的值改为：AFTER_COMMIT

Slave

INSTALL PLUGIN rpl_semi_sync_slave SONAME

SET GLOBAL rpl_semi_sync_slave_enabled=1;
STOP SLAVE IO THREAD;
START SLAVE IO THREAD;

多主复制配置

CHANGE MASTER TO MASTER_HOST='1.1.1.1',MASTER_USER='user',MASTER_PASSWORD='password',master_auto_position=1 FOR CHANNEL 'm1';
START SLAVE FOR CHANNEL 'm1';


CHANGE MASTER TO MASTER_HOST='2.2.2.2',MASTER_USER='user',MASTER_PASSWORD='password',master_auto_position=1 FOR CHANNEL 'm2';
START SLAVE FOR CHANNEL 'm2';

安全加固

1	mysql_secure_installation

主要包含以下操作：

为root用户设置密码
删除匿名账号
取消root用户远程登录
删除test库和对test库的访问权限
刷新授权表使修改生效

Quick Notes

MySQL总结

组成原理

组件

查询过程

存储引擎

锁

三种类型

两种模式

索引

算法介绍

平衡二叉树

B-树

B+树

索引类型

操作命令

SQL语句

主从复制

基本流程

配置流程

常用操作

帮助命令

查看系统时间

创建表

查询表结构

查询表创建语句

插入部分数据

插入完整数据

更新数据

清空表

查询状态

修改存储引擎

修改慢查询

查看进程

性能分析

半同步部署

多主复制配置

安全加固