本文主要涉及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:有值且唯一
操作命令
- 创建索引
1 | mysql> create index index1 on t_table(name); |
- 查看索引
1 | mysql> show index from t_table; |
- 删除索引
1 | mysql> alter table t_table drop index index1; |
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 | mysql> flush tables with read lock; |
1 | [root@master ~]# mysqldump -uroot -p rep > /usr/local/mysql/rep.sql |
1 | mysql> unlock tables; |
- 在slave库执行
1 | mysql < /usr/local/mysql/rep.sql |
1 | [root@slave ~]# vim /etc/my.cnf |
1 | mysql> change master to |
常用操作
帮助命令
1 | mysql> ? index; |
查看系统时间
1 | mysql> select now(); |
创建表
1 | mysql> create table t_table(id int(11), |
查询表结构
1 | mysql> describe t_table; |
查询表创建语句
1 | mysql> show create table t_table; |
插入部分数据
1 | mysql> insert into t_table(id,name) values(1,"henry"); |
插入完整数据
1 | mysql> insert into t_table values(2,"cc", 3.30); |
更新数据
1 | mysql> update t_table set grade=1.10 where id=1; |
清空表
1 | mysql> select * from table2; |
查询状态
1 | mysql> status; |
修改存储引擎
1 | mysql> alter table t_table engine=myisam; |
修改慢查询
1 | mysql> show variables like '%slow%'; |
查看进程
1 | mysql> show full processlist\G |
性能分析
1 | mysql> explain select * from t_table; |
explain查看方法:
- 先看查询类型“type”列,如果为ALL就不用看后面的了,因为是全表扫描;
- 再看“key”列,看是否使用了索引;
- 再看“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_COMMITSlave
1
2
3
4
5INSTALL PLUGIN rpl_semi_sync_slave SONAME
SET GLOBAL rpl_semi_sync_slave_enabled=1;
STOP SLAVE IO THREAD;
START SLAVE IO THREAD;
多主复制配置
1 | CHANGE MASTER TO MASTER_HOST='1.1.1.1',MASTER_USER='user',MASTER_PASSWORD='password',master_auto_position=1 FOR CHANNEL 'm1'; |
安全加固
1 | mysql_secure_installation |
主要包含以下操作:
- 为root用户设置密码
- 删除匿名账号
- 取消root用户远程登录
- 删除test库和对test库的访问权限
- 刷新授权表使修改生效