MySQL总结

本文主要涉及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-

B+树

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

B+

索引类型

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

操作命令

  • 创建索引
1
2
3
4
5
6
7
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
  • 查看索引
1
2
3
4
5
6
7
8
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)
  • 删除索引
1
2
3
4
5
6
7
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库执行
1
2
3
4
5
6
7
8
9
10
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
1
2
3
4
5
6
[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将复制事件写进自己的二进制日志
1
2
3
4
5
6
7
8
9
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)

常用操作

帮助命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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

查看系统时间

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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)

创建表

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

查询表结构

1
2
3
4
5
6
7
8
9
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)

查询表创建语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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

插入部分数据

1
2
3
4
5
6
7
8
9
10
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)

插入完整数据

1
2
3
4
5
6
7
8
9
10
11
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)

更新数据

1
2
3
4
5
6
7
8
9
10
11
12
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)

清空表

1
2
3
4
5
6
7
8
9
10
11
12
13
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)

查询状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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
--------------

修改存储引擎

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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)

修改慢查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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)

查看进程

1
2
3
4
5
6
7
8
9
10
11
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)

性能分析

1
2
3
4
5
6
7
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查看方法:

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

半同步部署

  • Master

    1
    2
    3
    4
    5
       # 安装插件
    INSTALL PLUGIN rpl_semi_sync_master SONAME
    # 启用半同步:
    SET @@GLOBAL.rpl_semi_sync_master_enabled=1;
    rpl_semi_sync_master_wait_point的值改为:AFTER_COMMIT
  • Slave

    1
    2
    3
    4
    5
    INSTALL PLUGIN rpl_semi_sync_slave SONAME

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

多主复制配置

1
2
3
4
5
6
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库的访问权限
  • 刷新授权表使修改生效
0%