1,概念
1)通信协议
MySQL实现了四种通信协议:
i>TCP/IP协议
连接MySQL。
ii>Unix Socket协议
登入MySQL服务器时使用。
使用这个协议连接MySQL需要一个物理文件,文件的存放位置在配置文件中有定义,值得一提的是,这是所有协议中最高效的一个。
iii>Share Memory协议(共享内存协议)
这个只有windows可以使用,使用这个协议需要在配置文件中在启动的时候使用–shared-memory参数,注意的是,使用此协议,一个host上只能有一个server,所以这个东西一般没啥用的,除非你怀疑其他协议不能正常工作,实际上微软的SQL Sever也支持这个协议
iv>Named Pipes协议(命名管道协议)
这个协议也是只有windows才可以用。
同shared memory一样,使用此协议,一个host上依然只能有一个server,即使是使用不同的端口也不行,Named Pipes 是为局域网而开发的协议。内存的一部分被某个进程用来向另一个进程传递信息,因此一个进程的输出就是另一个进程的输入。第二个进程可以是本地的(与第一个进程位于同一台计算机上),也可以是远程的(位于联网的计算机上)。正因为如此,假如你的环境中没有或者禁用TCP/IP环境,而且是windows服务器,那么好歹你的数据库还能工作。使用这个协议需要在启动的时候添加–enable-named-pipe选项
2)MyISAM和InnoDB区别
MyISAM | InnoDB |
---|---|
不支持事务,但是每次查询都是原子的 | 支持ACID事务,支持事务4种隔离级别 |
支持表级锁,每次操作对表加锁。 | 支持行级锁、外检约束。支持写并发。 |
存储表的总行数 | 不存储表的总行数 |
采用非聚集索引 | 主键采用聚集索引,最好使用自增主键 |
一个MyISAM有三个文件:索引文件、表结构文件、数据文件 | 一个InnoDB引擎存储在数据和主键索引存储在一起,其他辅助索引存放的是辅助索引和主键id。 |
2,安装与配置
1)mac安装及配置
i>下载及安装
注意安装过程中记录下来弹窗中的密码。如果没有记住密码需要进入mysql修改,请查看后续。
ii>配置环境变量
在终端中,open .bash_profile
(没有需要touch该文件)
在该文件中添加mysql/bin的目录
PATH=$PATH:/usr/local/mysql/bin
source ~/.bash_profile
2)本地数据库连接
3)windows安装
- 检测是否安装:win+R,services.msc 弹出窗口如果有mysql说明已经安装过。
- 下载:https://dev.mysql.com/downloads/mysql/
- zip包直接解压到指定目录。
- 添加my.ini文件
注意:basedir和datadir一定要带上双引号,双反斜杠。否则会初始化报错。
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录 ---这里输入你安装的文件路径----
basedir="D:\\soft\\mysql-8.0.28-winx64"
# 设置mysql数据库的数据的存放目录
datadir="D:\\mysql\\data"
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为utf8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8
- 执行安装命令。安装mysql 安装完成后Mysql会有一个随机密码.
## cmd(管理员模式!!!)进入bin路径
C:\Windows\system32>d:
D:\>cd soft\mysql-8.0.28-winx64\bin
## 初始化命令 安装完成后Mysql会有一个随机密码.
mysqld --initialize --console
## 安装命令
mysqld -install
## 启动命令 启动之后用初始的随机密码就可以登录,初次登录会修改密码。
net start mysql
安装过程中提示找不到vcruntime140_1.dll,下载后得到dll放入C:\Windows\SysWOW64下可以解决问题
3,常见命令行操作
1)登录
# 登录 -p表示要输入密码
mysql -uroot -p
# 修改密码(登录后)
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
## 关闭命令
net stop mysql
# 远程登录 默认端口:3306
mysql -h 192.168.1.2 -P 3306 -u 用户名 -p密码 dbName
2)忘记密码
# 1. 偏好设置关闭mysql(必须)
# 2. 输入mac管理员密码进入sh-3.2#
$ cd /usr/local/mysql/bin
$ sudo su
sh-3.2#./mysqld_safe --skip-grant-tables &
# 3.在新的终端登录mysql(此时不需要密码即可登录)
mysql -u -root
mysql> FLUSH PRIVILEGES; #刷新
# 修改密码
# 再刷新即可。
3)增删改查
# 建库
create database testDB character set utf8;
# 修改库编码
alter database app_relation character set utf8;
# 表的属性
# sql server的varchar(MAX)类型替换为text。
id int AUTO_INCREMENT primary key not null
4)备份与恢复
i> 打开Terminal,输入mysqldump,发现Terminal提示mysqldump: command not found
则进入ii,否则进入iii
ii> 之所以会出现mysql或者mysqldump这样的命令找不到,我们可以打开/usr/bin文件夹,发现bin目录中并没有Mysql打头的UEF文件,而在/usr/local/mysql/bin中可以找到这样的文件,说明mysql的命令默认安装路径是不在bin目录中的,因而我们需要在环境变量中配置mysql的所有命令。按照如下步骤配置:
打开terminal输入 open ~/.bash_profile
添加如下代码:
#mysql
PATH=$PATH:/usr/local/mysql/bin
export
保存并关闭。Terminal中执行以下代码:
source ~/.bash_profile
再次输入mysqldump确定可以使用。
iii> 备份
cd 要导出到的目录 (如:cd /Users/lwh/Desktop)
mysqldump -u root -p databaseName>test.sql
iv>还原
进入mysql(由于修改过~/.bash_profile,所以可以直接在终端输入以下代码):
mysql -u root -p
use newDatabaseName;
source /Users/lwh/Desktop/test.sql;
5)可视化工具
i>Sequel Pro
免费的工具。这个工具界面比较简单。
先创建数据库characterDB,然后进行连接,如下图所示:
其中3306为默认端口。
字段类型
1>字符串
在mysql中,如果字符串大于 max_allowed_packet 时,字符串值函数的返回值为NULL。需要调整服务器参数才可以继续使用。
1)库操作
2)表操作
1>建表
2>del
3>modify
4>select
- 分页查询
limit (page-1)*size, size;
3)索引
1>创建
-- 根据length可创建前缀索引
CREATE INDEX index_name ON table_name (column_name(length));
2>删
drop index index_test on employee;
3>查
SHOW INDEX FROM employee;
4)常用函数
1>日期
计算日期差值
使用DATEDIFF()函数
2>排名函数
rank() over
作用:查出指定条件后的进行排名,条件相同排名相同,排名间断不连续。
说明:例如学生排名,使用这个函数,成绩相同的两名是并列,下一位同学空出所占的名次。即:1 1 3 4 5 5 7
rank() over(order by id)
dense_rank() over
作用:查出指定条件后的进行排名,条件相同排名相同,排名连续。
说明:和rank() over 的作用相同,区别在于dense_rank() over 排名是密集连续的。例如学生排名,使用这个函数,成绩相同的两名是并列,下一位同学接着下一个名次。即:1 1 2 3 4 5 5 6
select salary, dense_rank() over (order by salary desc) as "rank" from tbl_lwh_employee;
row_number() over
作用:查出指定条件后的进行排名,条件相同排名也不相同,排名间断不连续。
说明:这个函数不需要考虑是否并列,即使根据条件查询出来的数值相同也会进行连续排序。即:1 2 3 4 5 6
4. top
--显示前100行
select top 100 * from student where no=11;
3>IF()
mysql特有,pgsql的直接查询select没有if()函数,可以用case when then替代。函数里可以使用if then。
SELECT
atree.id,
IF(ISNULL(atree.p_id),
'Root',
IF(atree.id IN (SELECT p_id FROM tree), 'Inner','Leaf')) Type
FROM
tree atree
ORDER BY atree.id
4>IFNULL (sql, NULL)
mysql通过ifnull函数判断sql结果,如果sql执行不存则返回null;
5>聚合函数
count | name |
---|---|
1 | 10 |
1 | 20 |
1 | 20 |
2 | 20 |
3 | 200 |
3 | 500 |
输出:
count | name |
---|---|
1 | 10,20,20 |
2 | 20 |
3 | 200,500 |
-- 平台:mysql
-- 直接聚合 -- 1 10,20,20
select count,group_concat(name) from test_a group by count;
-- 可以去重排序 -- 1 20,10
select count,group_concat(distinct name order by name desc) from test_a group by count;
-- 约定分隔符 -- 1 20;10
select count,group_concat(distinct name order by name desc separator ';') from test_a group by count;
5,优化
1)optimize table
delete大量数据、update大量可变长度行的数据(VARCHAR, BLOB或TEXT)、大量插入数据导致聚簇索引更新时,会导致表膨胀的问题。
被删除的记录被保持在链接清单中,这些数据页被标记为“可复用”,但是磁盘文件大小不会改变,即表空间不会被回收。后续的insert会复用旧的记录位置。通过optimize table重新整理文件碎片,释放表空间。
推荐只对特定表运行,一周或一月一次。
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
optimize table [$Database1].[Table1],[$Database2].[Table2]
注意,在OPTIMIZE TABLE运行过程中,MySQL会锁表。
OPTIMIZE TABLE语句被写入到二进制日志中,除非使用了自选的NO_WRITE_TO_BINLOG关键词(或其别名LOCAL)。
2)explain
通过使用explain知道什么时候必须为表加入索引以提高查询效率。
explain sql语句
结果说明:
- id:SELECT的查询序列号
- select_type:SELECT类型
类型 | 说明 |
---|---|
SIMPLE | 简单SELECT(不使用UNION或子查询); |
PRIMARY | 最外面的SELECT); |
UNION | UNION中的第二个或后面的SELECT语句; |
DEPENDENT UNION | UNION中的第二个或后面的SELECT语句,取决于外面的查询); |
UNION RESULT | UNION的结果; |
SUBQUERY | 子查询中的第一个SELECT; |
DEPENDENT SUBQUERY | 子查询中的第一个SELECT,取决于外面的查询; |
DERIVED | 导出表的SELECT(FROM子句的子查询) |
- table:当前输出结果所属表。
- partitions:
- type:联接类型
从最佳到最差:
类型 | 说明 | 举例 |
---|---|---|
system | 表只有一行,通常为系统表。 | |
const | 表最多有一个匹配行,查询非常快。通常用于比较PRIMARY KEY或UNIQUE索引。 | SELECT * from tbl_name WHERE primary_key=1; |
eq_ref | 联表查询时,连接键为主键或者唯一索引。 | SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; |
ref | 对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。 | SELECT * FROM ref_table WHERE key_column=expr; |
ref_or_null | ref + 可以检索NULL行 | SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL; |
index_merge | ||
unique_subquery | ||
index_subquery | ||
range | 只检索给定范围的行,使用一个索引来选择行。 | 当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时 |
index | 全表扫描但只扫描索引树 | |
ALL | 全表扫描 |
- possible_keys:指出MySQL能使用哪个索引在该表中找到行。
- key:实际决定使用的索引
- key_len:使用的索引列的长度
- ref:使用哪个列或常数与key一起从表中选择行
- rows:执行查询时扫描的行数。
- filtered:效率,百分比
- Extra:解决查询的详细信息。
Extra类型 | 说明 |
---|---|
Distinct | 发现第1个匹配行后,停止搜索 |
Not exists | 发现1个匹配LEFT JOIN的行后,不再检查 |
Using filesort | 额外使用排序顺序进行行的检索 |
Using index | 覆盖索引检索 |
Using temporary | 创建一个临时表来容纳结果 |
Using where | where语句用于限制查询结果 |
Using sort_union(…), Using union(…), Using intersect(…) | |
Using index for group-by | 发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表 |
1>索引
CREATE TABLE employee (
id INT ( 0 ) NOT NULL AUTO_INCREMENT,
NAME VARCHAR ( 10 ),
sex VARCHAR ( 10 ),
salary INT4,
dept VARCHAR ( 10 ),
PRIMARY KEY ( `id` ));
insert into employee(name,sex,salary,dept) VALUES
('张1','男',3500,'部门A'),
('张2','男',4500,'部门C'),
('张3','男',5500,'部门A'),
('张4','男',7500,'部门C'),
('张5','男',6500,'部门B'),
('张6','男',12000,'部门B'),
('张7','男',8500,'部门A'),
('张8','男',2500,'部门C'),
('张9','男',5500,'部门B'),
('张10','男',10500,'部门C'),
('张11','男',11500,'部门C'),
('张12','男',9500,'部门A'),
('张13','男',8500,'部门A');
explain SELECT * FROM employee WHERE name = '张7';
没有添加索引之前,全表扫描,效率10%。
-- 创建联合索引
create index index_test on employee(name, salary, dept);
添加索引后,type=ref,按索引index_test 进行检索,只检索了一次,检索效率100。
由于查询的是*
,索引定位后还需要回表查询数据,Extra为NULL。
explain SELECT name, salary FROM employee WHERE name = '张7';
查询的两个字段都是索引的内容,覆盖索引查询,不需要再次回表取数据。
3)数据库表上限
mysql单表数据超过500w建议分表,超过2000w性能急剧下降。
主要原因:SMO无法并发,MySQL无法支持大数据量下并发修改的根本原因,是由于其索引并发控制协议的缺陷造成的,而MySQL选择索引组织表,又放大了这一缺陷。
B+Tree操作非原子,所以当一个线程做结构调整(SMO,Struction-Modification-Operation)时一般会涉及多个节点的改动。为了控制并发情况下访问到正确的节点,InnoDB采用了乐观锁和悲观锁的并发控制协议:
1)先采用乐观锁的方式尝试进行修改
对根节点加S锁(shared lock,叫共享锁,也称读锁),依次对非叶子节点加S锁。
如果叶子节点的修改不会引起B+Tree结构变动,如分裂、合并等操作,那么只需要对叶子节点进行加X锁(exclusive lock,叫排他锁,也称为写锁)即可完成修改。
2)采用悲观锁的方式
如果对叶子结点的修改会触发SMO,那么会采用悲观锁的方式。
采用悲观锁,需要重新遍历B+Tree,对根节点加全局SX锁(SX锁是行锁),然后从根节点到叶子节点可能修改的节点加X锁。
在整个SMO过程中,根节点始终持有SX锁(SX锁表示有意向修改这个保护的范围,SX锁与SX锁、X锁冲突,与S锁不冲突),此时其他的SMO则需要等待。
因此,InnoDB对于简单的主键查询比较快,因为数据都存储在叶子节点中,但对于数据量大且改操作比较多的TP型业务,并发会有很严重的瓶颈问题。
3)解决方案:
目前业界有一个更好的方案B-Link Tree,与B+Tree相比,B-Link Tree优化了B+Tree结构调整时的锁粒度,只需要逐层加锁,无需对root节点加全局锁。因此,可以做到在SMO过程中写操作的并发执行,保持高并发下性能的稳定。GaussDB当前采用的就是B-Link Tree索引数据结构。
6,常用锁
1)基于锁的属性分类
1>共享锁(Share Lock,S锁、读锁)
事务读数据时创建读锁。
其他事务可以添加读锁,但任何事务都不能获取数据上的写锁。==》支持并发读取,避免重复读
2>排它锁(eXclusive lock,X锁、写锁)
若一个事务给数据加上写锁,就可以读取和修改所有数据;
其它事务不可获得任何锁。
==》避免脏数据和脏读情况。
2)基于锁的粒度分类
粒度越大,冲突概率越大。支持并发越小。
1>表锁
锁住的是整个表。
当前事务不释放锁,其它事务不可访问。
支持引擎:INNODB、MYISAM。
不会存在表锁。
2>行锁
锁住一行或多行记录。其它事务可以访问其它行的数据。
支持引擎:INNODB。
可能存在死锁。
3>记录锁(Record Lock)
行锁的一种,只锁一行。命中的条件一定是唯一索引。
4>页锁(BDB引擎)
介于行锁表锁之间的粒度。页的大小是可以设置的。
支持引擎:BDB引擎。
可能存在死锁。
5>间隙锁(Gap Lock)
是行锁的一种:当表的相邻ID之间存在空隙,会形成一个区间,遵循左开右闭原则。间隙锁就会锁住这些区间。
举例:查询到数据id:1,2,5,间隙为: (2,5]
,3 4 5会被锁住。
防止出现幻读。
6>临键锁(Next-key Lock)
INNODB默认行锁算法。
是行锁的一种:查询出来的记录锁住 + 该范围内所有间隙空间也锁住。
举例:查询到数据id:1,2,5,间隙为: (2,5]
,3 4 5会被锁住。会把1-5全锁住。
避免脏读、幻读、重复读问题。
3)基于锁的状态分类
如果事务1加了行锁排它锁,事务2要加表锁,那么需要遍历整个表知道没有行锁后才能加表锁。事务1如果添加了意向排它锁,那么事务2加锁前如果拿到了表的意向排它锁,才能加表锁。
1>意向共享锁
当事务准备对整个表加共享锁时,需要获得表的意向共享锁。
2>意向排它锁
7,事务
mysql默认隔离级别:可重复读。
1)ACID靠什么来保证
- 原子性 A
undo log 日志,记录了需要回滚的日志信息,事务回滚时撤销已执行了的sql。 - 一致性 C
由其它三大特性来保证,程序代码要保证业务一致性。 - 隔离性 I
由MVCC保证。
每个事务第一次读取数据会读到一个Read View,本次事务读取都是从这个Read View来拿数据。其他事务变更不影响Read View。 - 持久性 D
内存+redo log来保证。mysql修改数据同时在内存和redo log记录本次操作(binlog也会记录每次数据),宕机时从redo log恢复。
恢复步骤:
redo log后面有commit表示事物持久化成功。
1. InnoDB: redo log写盘,事务进入prepare状态;
2. 如果prepare成功,binlog去写盘,将事务持久化到binlog;
3. 如果持久化成功,redo log 后面追加commit记录。(这个刷盘操作会在系统空闲时执行)
2)MVCC(多版本并发控制)
读取数据的时候,每次读取快照,不同的session读到特定版本的数据(版本链控制)。这样读锁和写锁就不会冲突了。
聚簇索引记录中的两个隐藏链
trx_id:用来存储每次对每条聚簇索引记录进行修改时的事务id;
roll_pointer(维护了版本链):每次修改聚簇索引时会把老版本写入undo日志中,roll_pointer保存了一个指向上一个版本位置的指针。
1>工作原理
mysql的MVCC只在已提交读、可重复读两个隔离级别下工作。
- 事务开始时创建read view
read view记录了每个活动事务(未提交事务)的事务id,根据事务id asc排序生成一个数组。 - 事务访问数据,获取数据的事务id(聚簇索引的版本链中最大的trx_id)
- 将事务id和read view中的事务id数组做对比:
1)如果事务id在read view的数组左边,即事务id小于所有的活动事务,那么说明事务已经提交了;
2)如果事务id在read view的数组右边,说明事务在创建read view之后才创建的,此时不可访问。
3)如果事务id命中read view的数组中某一个,也不可访问。
针对2和3,通过roll_pointer拿它的上一条记录,重新比对。直到碰到1)为止。 - 在已提交读隔离级别,每次查询生成一个read view。
在可重复读隔离级别,第一次查询生成一个read view,后面复用之前的read view。
8,主从同步原理
1)同步过程
mysql的主从复制主要有以下三个线程:
- binlog dump thread(master结点)
主库记录数据库的所有变更操作到binlog。
当binlog文件变更时,log dump线程读取其内容并发送给从结点。
binlog
数据库服务器启动时,保存所有修改数据库结构或内容的一个文件。
- I/O thread(slave结点)
接收binlog内容,将其写入relay log文件中。 - SQL thread(slave结点)
从relay log文件中读取内容并对数据更新进行重放,最终保存主从数据的一致性。
2)同步机制
- 增量同步
主从结点使用binlog文件+position偏移量来定位主从同步的位置,从节点保存已接收到的偏移量。 - 异步同步
主库把数据发给从库就不管了,不会等待从库回复(不然会影响性能)。如果主库挂了,从库处理失败就会导致主从复制数据丢失。
全同步机制
10个从结点,都返回ACK信息后,主库才认为数据同步完成。
半同步机制
10个从结点,有一个返回ACK信息后,主库就认为数据同步完成。