mysql目录
-
/var/lib/mysql mysql数据库文件的存放路径
- .frm文件,存放表结构
- .ibd文件,数据文件、索引文件,主要用于存储数据
-
/etc/my.cnf 配置文件
包含主从复制
事务
事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败.
事务作用:保证在一个事务中多次SQL操作要么全都成功,要么全都失败
mysql事务
# 开启事务
start transaction
# 提交事务
commit
# 回滚事务
rollback
MYSQL中两种方式进行事务的管理
自动提交:MySql默认自动提交。及执行一条sql语句提交一次事务。
手动提交:先开启,再提交。
# 手动提交
start transaction;
update account set money=money-1000 where name='jack';
update account set money=money+1000 where name='rose';
commit;
#或者
rollback;
# 自动提交,通过修改mysql全局变量“autocommit”进行控制
show variables like '%commit%';
# 设置自动提交的参数为OFF
set autocommit = 0; -- 0:OFF 1:ON
事务的特性
-
原子性(Atomicity)原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
-
一致性(Consistency)事务前后数据的完整性必须保持一致。
-
隔离性(Isolation)事务的隔离性是指多个用户并发访问数据库时,一个用户的事务不能被其它用户的事务所干扰,多个并发事务之间数据要相互隔离。
-
持久性(Durability)持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。
隔离级别
数据库规范规定了4种隔离级别,分别用于描述两个事务并发的所有情况。
- read uncommitted 读未提交,一个事务读到另一个事务没有提交的数据。
a)存在:3个问题(脏读、不可重复读、虚读)。
b)解决:0个问题 - read committed 读已提交,一个事务读到另一个事务已经提交的数据。
a)存在:2个问题(不可重复读、虚读)。
b)解决:1个问题(脏读) - repeatable read:可重复读,在一个事务中读到的数据始终保持一致,无论另一个事务是否提交。
a)存在:1个问题(虚读)。
b)解决:2个问题(脏读、不可重复读) - serializable 串行化,同时只能执行一个事务,相当于事务中的单线程。
a)存在:0个问题。
b)解决:3个问题(脏读、不可重复读、虚读)
-
安全和性能对比
- 安全性: serializable > repeatable read > read committed > read uncommitted
- 性能 : serializable < repeatable read < read committed < read uncommitted
-
常见数据库的默认隔离级别:
MySql: 支持repeatable read(default),四种都支持
Oracle: 支持read committed(default)、serializable
隔离级别问题详解
-
脏读又称无效数据读出(读出了脏数据)。一个事务读取另外一个事务还没有提交的数据叫脏读。
例如:事务T1修改了某个表中的一行数据,但是还没有提交,这时候事务T2读取了被事务T1修改后的数据,之后事务T1因为某种原因回滚(Rollback)了,那么事务T2读取的数据就是脏的(无效的)。
-
不可重复读是指在同一个事务内,两次相同的查询返回了不同的结果。
例如:事务T1会读取两次数据,在第一次读取某一条数据后,事务T2修改了该数据并提交了事务,T1此时再次读取该数据,两次读取便得到了不同的结果。
-
幻读(虚读)
幻读是指同一个事务,两次查询,结果集不一样,原因是另一个事务增加了或者减少了行数据。
乐观锁和悲观锁
用处:保证数据安全,处理多用户并发访问。
-
乐观锁
每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据。例如版本号或时间戳控制,适用于多读少写的场景。 -
悲观锁
每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会block直到它拿到锁 DB的行锁、表锁等,适用于数据一致性比较高的场景
悲观锁会造成访问数据库时间较长,并发性不好,特别是长事务。乐观锁在现实中使用得较多,厂商较多采用
存储引擎
概念:在mysql中的数据用各种不同的技术存储在文件(或内存)中。
有十几种,常用的就是MyISAM和Innodb。
MyISAM和Innodb的区别
InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型,这两个表类型各有优劣,视具体应用而定。
MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提
- MyISAM不支持主外键和事务,InnoDB支持。
- MyISAM是表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作。
innodb是行锁,操作时只锁某一行,不对其他行有影响,适合高并发的操作
- MyISAM关注的是性能,innodb关注的是事务
应用场景
MyISAM管理非事务表。它提供高速存储和检索,以及全文搜索能力。如果应用中需要执行大量的SELECT查询,那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序,具有众多特性,包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作,则应该使用InnoDB,这样可以提高多用户并发操作的性能。
MyISAM很鸡肋,数据快的话可以用redis,全文检索的话可以用es
innodb
我一般了解到的是,innodb用的多一些,目前为止,没有见过用MyISAM的
Innodb 存储引擎是事务安全
的, 因此如果需要一个事务安全的存储引擎,建议使用它。如果你的数据执行大量
的INSERT或UPDATE,出于性能方面的考虑应该使用InnoDB表。
InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁
(locking on row level),提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。
InnoDB 的设计目标是处理大容量数据库系统,它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上,InnoDB 是一套放在 MySQL 后台的完整数据库系统,InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
InnoDB 把数据和索引存放在表空间里,可能包含多个文件,这与MyISAM不一样。InnoDB 表的大小只受限于操作系统的文件大小,一般为 2 GB。InnoDB所有的表都保存在同一个数据文件 ibdata1 中(也可能是多个文件,或者是独立的表空间文件),相对来说比较不好备份。备份的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump。
innodb存储优化
-
使用LOAD DATA INFILE
当需要批量导入数据时,使用LOAD DATA INFILE语句比INSERT语句插入速度快很多。
-
禁用唯一性检查
唯一性校验会降低插入记录的速度,可以在插入记录之前禁用唯一性检查,插入数据完成后再开启。
禁用唯一性检查的语句:SET UNIQUE_CHECKS = 0;
开启唯一性检查的语句:SET UNIQUE_CHECKS = 1;
-
禁用外键检查
插入数据之前执行禁止对外键的检查,数据插入完成后再恢复,可以提供插入速度。禁用:SET foreign_key_checks = 0;
开启:SET foreign_key_checks = 1; -
禁止自动提交
插入数据之前执行禁止事务的自动提交,数据插入完成后再恢复,可以提高插入速度。禁用:SET autocommit = 0;
开启:SET autocommit = 1;
mysql锁机制
锁的分类
从对数据操作的粒度分,表锁/行锁
从对数据操作的类型(读\写)分
- 表锁(偏读)
- 行锁(偏写)
- 页锁(介于以上两者之间,了解即可)
表锁
CREATE table mylock(
id INT NOT NULL PRIMARY KEY auto_increment,
name varchar(20)
) ENGINE myisam;
INSERT INTO mylock(name) VALUES('a');
INSERT INTO mylock(name) VALUES('b');
INSERT INTO mylock(name) VALUES('c');
INSERT INTO mylock(name) VALUES('d');
INSERT INTO mylock(name) VALUES('e');
SELECT * FROM mylock;
# 查看表是否被锁
SHOW OPEN tables;
# 手动增加表锁
# 给mylock上读锁,book上写锁
lock TABLE mylock read,book write;
# 解锁
UNLOCK TABLES;
# 读锁案例
LOCK table mylock read;
# 两个session都可以查看mylock,因为读锁是共享锁
SELECT * FROM mylock;
# > 1100 - Table 'book' was not locked with LOCK TABLES
# 其他session可以读其他表,但锁表的session只能读 被锁的那个表,直至解锁
SELECT * FROM book;
# > 1099 - Table 'mylock' was locked with a READ lock and can't be updated
# 其他session更新该表的时候,会阻塞等待,直到该表的读锁被解锁
UPDATE mylock set name='a2' WHERE id=1;
# 写锁案例
LOCK TABLE mylock write;
# 加锁的session可以 读/更新 已经加写锁的表
# 也可以读其他表
# 其他session读/写该表会阻塞等待
SELECT * FROM mylock;
UPDATE mylock set name='a2' WHERE id=1;
# 同样的,解锁之后才能读其他表
SELECT * FROM book;
简而言之,就是读锁会阻塞写,但是不会阻塞读。而写锁则会把读和写都阻塞。
如何分析表锁定
行锁
CREATE TABLE test_innodb_lock(a int(11),b VARCHAR(16)) ENGINE=innodb;
INSERT INTO test_innodb_lock VALUES(1,'b2');
INSERT INTO test_innodb_lock VALUES(3,'3');
INSERT INTO test_innodb_lock VALUES(4,'4000');
INSERT INTO test_innodb_lock VALUES(5,'5000');
INSERT INTO test_innodb_lock VALUES(6,'6000');
INSERT INTO test_innodb_lock VALUES(7,'7000');
INSERT INTO test_innodb_lock VALUES(8,'8000');
INSERT INTO test_innodb_lock VALUES(9,'9000');
INSERT INTO test_innodb_lock VALUES(1,'b1');
SELECT * FROM test_innodb_lock;
CREATE INDEX test_innodb_a_ind ON test_innodb_lock(a);
CREATE INDEX test_innodb_lock_b_ind ON test_innodb_lock(b);
SET autocommit=0;
UPDATE test_innodb_lock SET b='4001' WHERE a=4;
# 在该session commit之后,其他session也得commit才能读到这条更新的数据
# 证明了mysql的隔离级别是 可重复读,存在虚读的问题
COMMIT;
UPDATE test_innodb_lock SET b='4002' WHERE a=4;
# 该session更新未提交,其他session更新的话就进入阻塞
# 等该session提交之后,解除阻塞,其他session才能执行更新
COMMIT;
UPDATE test_innodb_lock SET b='4005' WHERE a=4;
# 该session更新的是 4 这行
# 其他session更新其他行可以成功执行,不进入阻塞
COMMIT;
索引失效行锁变表锁
# b是有索引的,而且是varchar类型
# varchar类型在where后如果不加 '' ,依然能执行成功,
# 这是由于mysql内部做了类型转换,但该操作就引起了索引失效
# 进一步使行锁变成表锁,其他session在更新其他行数据就会造成阻塞
update test_innodb_lock set a=41 where b=4000;
间隙锁危害
# 下面的id没有2,不是连续的
mysql> select * from test_innodb_lock;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4003 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9001 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
# 更新了这条数据后,其他的session新增数据,id为2,
# 理论上行锁是不影响的,但实际上进入阻塞了
mysql> update test_innodb_lock set b='0629' where a>1 and a<6;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
面试题:常考如何锁定一行
假设8号记录的字段很多,发现某些值是不对的,需要更正,但在更正的过程中其他人也可能来修改,就需要锁定该行。
页锁
主从复制
一主一从配置开始
- mysql版本一致,且网络互通
- 主从配置都在 /etc/my.cnf
# 主服务器唯一id
server-id = 1
# 启用二进制日志
log-bin=/etc/mysql/data/mysqlbin
log-err=/etc/mysql/data/mysqlerr
# 安装mysql的路径
basedir=""
# 临时目录
tempdir=""
# 数据库的数据目录
datadir=""
# 主机,读写都可以
read-only=0
# 设置不要复制的数据库
binlog-ignore-db=awusql
# 需要复制的数据库名字
binlog-do-db=awusql
- 重启mysql服务
- service firewalld stop
- 配置
# 配置主机
# 允许从机以zs的用户名和123456的密码登录到主机
grant replication slave on *.* to 'zs'@'172.17.0.2' identified by '123456'
# 刷新
flush privileges;
# 从mysql-bin.000002中的596行的位置开始抄,Binlog_Do_DB为空代表所有库都复制
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 596 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 配置从机
change master to master_host='172.17.0.3',master_user='zs',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=596;
# 启动从服务器复制功能
mysql> start slave;
mysql> show slave status\G;
# 以下两个参数均为yes则从机配置成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 停止从服务复制功能
stop slave;
完成后,主机的操作就全部会同步到从机上。
暂时不属于我这个水平的回答
不就是SELECT COUNT语句吗,竟然能被面试官虐的体无完肤
1、COUNT有几种用法?
2、COUNT(字段名)和COUNT(*)的查询结果有什么不同?
3、COUNT(1)和COUNT(*)之间有什么不同?
4、COUNT(1)和COUNT(*)之间的效率哪个更高?
5、为什么《阿里巴巴Java开发手册》建议使用COUNT(*)
6、MySQL的MyISAM引擎对COUNT(*)做了哪些优化?
7、MySQL的InnoDB引擎对COUNT(*)做了哪些优化?
8、上面提到的MySQL对COUNT(*)做的优化,有一个关键的前提是什么?
9、SELECT COUNT(*) 的时候,加不加where条件有差别吗?
10、COUNT(*)、COUNT(1)和COUNT(字段名)的执行过程是怎样的?
认识count
关于COUNT函数,在MySQL官网中有详细介绍:
1、COUNT(expr) ,返回SELECT语句检索的行中expr的值不为NULL的数量。结果是一个BIGINT值。
2、如果查询结果没有命中任何记录,则返回0
3、但是,值得注意的是,COUNT(*)
的统计结果中,会包含值为NULL的行数。
create table #bla(id int,id2 int)
insert #bla values(null,null)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,1)
insert #bla values(1,null)
insert #bla values(null,null)
select count(*),count(id),count(id2)
from #bla
results 7 3 2
除了COUNT(id)
和COUNT( * )
以外,还可以使用COUNT(常量)
(如COUNT(1))来统计行数,那么这三条SQL语句有什么区别呢?到底哪种效率更高呢?为什么《阿里巴巴Java开发手册》中强制要求不让使用 COUNT(列名)
或 COUNT(常量)
来替代 COUNT(*)
呢?
COUNT(列名)、COUNT(常量)和COUNT(*)之间的区别
前面我们提到过COUNT(expr)
用于做行数统计,统计的是expr不为NULL的行数,那么COUNT(列名)
、 COUNT(常量)
和 COUNT(*)
这三种语法中,expr分别是列名、 常量 和 *。
那么列名
、 常量
和 *
这三个条件中,常量
是一个固定值,肯定不为NULL。*可以理解为查询整行,所以肯定也不为NULL,那么就只有列名
的查询结果有可能是NULL了。
sql92
SQL92,是数据库的一个ANSI/ISO标准。它定义了一种语言(SQL)以及数据库的行为(事务、隔离级别等)。
count*的优化
前面提到了COUNT(*)
是SQL92定义的标准统计行数的语法,所以MySQL数据库对他进行过很多优化。那么,具体都做过哪些事情呢?
这里的介绍要区分不同的执行引擎。MySQL中比较常用的执行引擎就是InnoDB和MyISAM。
MyISAM和InnoDB有很多区别,其中有一个关键的区别和我们接下来要介绍的COUNT(*)
有关,那就是MyISAM不支持事务,MyISAM中的锁是表级锁;而InnoDB支持事务,并且支持行级锁。
因为MyISAM的锁是表级锁,所以同一张表上面的操作需要串行进行,所以,MyISAM做了一个简单的优化,那就是它可以把表的总行数单独记录下来,如果从一张表中使用COUNT(*)进行查询的时候,可以直接返回这个记录下来的数值就可以了,当然,前提是不能有where条件。
MyISAM之所以可以把表中的总行数记录下来供COUNT(*)查询使用,那是因为MyISAM数据库是表级锁,不会有并发的数据库行数修改,所以查询得到的行数是准确的。
但是,对于InnoDB来说,就不能做这种缓存操作了,因为InnoDB支持事务,其中大部分操作都是行级锁,所以可能表的行数可能会被并发修改,那么缓存记录下来的总行数就不准确了。
但是,InnoDB还是针对COUNT(*)语句做了些优化的。
在InnoDB中,使用COUNT(*)查询行数的时候,不可避免的要进行扫表了,那么,就可以在扫表过程中下功夫来优化效率了。
从MySQL 8.0.13开始,针对InnoDB的SELECT COUNT(*) FROM tbl_name
语句,确实在扫表的过程中做了一些优化。前提是查询语句中不包含WHERE或GROUP BY等条件。
我们知道,COUNT(*)的目的只是为了统计总行数,所以,他根本不关心自己查到的具体值,所以,他如果能够在扫表的过程中,选择一个成本较低的索引进行的话,那就可以大大节省时间。
我们知道,InnoDB中索引分为聚簇索引(主键索引)和非聚簇索引(非主键索引),聚簇索引的叶子节点中保存的是整行记录,而非聚簇索引的叶子节点中保存的是该行记录的主键的值。
所以,相比之下,非聚簇索引要比聚簇索引小很多,所以MySQL会优先选择最小的非聚簇索引来扫表。所以,当我们建表的时候,除了主键索引以外,创建一个非主键索引还是有必要的。
至此,我们介绍完了MySQL数据库对于COUNT(*)的优化,这些优化的前提都是查询语句中不包含WHERE以及GROUP BY条件。
COUNT(*)和COUNT(1)哪个更快?
看下MySQL官方文档是怎么说的:
InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.
画重点:same way
, no performance difference
。所以,对于COUNT(1)和COUNT(*),MySQL的优化是完全一样的,根本不存在谁比谁快!
那既然COUNT(*)和COUNT(1)一样,建议用哪个呢?
建议使用COUNT(*)
!因为这个是SQL92定义的标准统计行数的语法,而且本文只是基于MySQL做了分析,关于Oracle中的这个问题,也是众说纷纭的呢。
COUNT(字段)
后,就是我们一直还没提到的COUNT(字段),他的查询就比较简单粗暴了,就是进行全表扫描,然后判断指定字段的值是不是为NULL,不为NULL则累加。
相比COUNT(* )
,COUNT(字段)多了一个步骤就是判断所查询的字段是否为NULL,所以他的性能要比COUNT(*)慢。
总结
本文介绍了COUNT函数的用法,主要用于统计表行数。主要用法有COUNT(*)、COUNT(字段)和COUNT(1)。
因为COUNT()是SQL92定义的标准统计行数的语法,所以MySQL对他进行了很多优化,MyISAM中会直接把表的总行数单独记录下来供COUNT()查询,而InnoDB则会在扫表的时候选择最小的索引来降低成本。当然,这些优化的前提都是没有进行where和group的条件查询。
在InnoDB中COUNT(*)和COUNT(1)实现上没有区别,而且效率一样,但是COUNT(字段)需要进行字段的非NULL判断,所以效率会低一些。
因为COUNT()是SQL92定义的标准统计行数的语法,并且效率高,所以请直接使用COUNT()查询表的行数!
不就是SELECT COUNT语句吗,竟然能被面试官虐的体无完肤
引用自https://blog.csdn.net/hollis_chuang/article/details/102657937
部分知识引用自:https://blog.csdn.net/xianlvfan2224/article/details/102722298
https://www.geek-share.com/detail/2611672984.html
https://www.cnblogs.com/cocoxu1992/p/11031908.html
https://www.cnblogs.com/youngdeng/p/12855570.html
到了大约7万年前,一些属于智人这一物种的生物,开始创造出更复杂的架构,称为文化。而这些人类文化继续发展,就成了历史学。
人类简史