什么是MySQL
数据库的三大范式
第一范式1NF
比如字段 userInfo
: 广东省 10086'
,依照第一范式必须拆分成 userInfo
: 广东省
userTel
: 10086
两个字段。
第二范式2NF
举个例子。假定选课关系表为student_course
(student_no, student_name, age, course_name, grade, credit),主键为(student_no, course_name)。其中学分完全依赖于课程名称,姓名年龄完全依赖学号,不符合第二范式,会导致数据冗余(学生选n门课,姓名年龄有n条记录)、插入异常(插入一门新课,因为没有学号,无法保存新课记录)等问题。
student(stuent_no, student_name, 年龄);课程:course
(course_name, credit);选课关系:student_course_relation
(student_no, course_name, grade)。
第三范式3NF
假定学生关系表为Student(student_no, student_name, age, academy_id, academy_telephone),主键为"学号",其中学院id依赖于学号,而学院地点和学院电话依赖于学院id,存在传递依赖,不符合第三范式。
2NF和3NF的区别?
- 2NF依据是非主键列是否完全依赖于主键,还是依赖于主键的一部分。
- 3NF依据是非主键列是直接依赖于主键,还是直接依赖于非主键。
计算机基础、Java基础、多线程、JVM、数据库、Redis、Spring、Mybatis、SpringMVC、SpringBoot、分布式、微服务、设计模式、架构、校招社招分享等核心知识点,欢迎star~
如果访问不了Github,可以访问gitee地址。
事务的四大特性?
事务特性ACID:原子性(Atomicity
)、一致性(Consistency
)、隔离性(Isolation
)、持久性(Durability
)。
-
原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚。
- 一致性是指一个事务执行之前和执行之后都必须处于一致性状态。比如a与b账户共有1000块,两人之间转账之后无论成功还是失败,它们的账户总和还是1000。
-
隔离性。跟隔离级别相关,如
read committed
,一个事务只能读到已经提交的修改。 - 持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
事务隔离级别有哪些?
-
脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
- 不可重复读是指在对于数据库中的某行记录,一个事务范围内多次查询却返回了不同的数据值,这是由于在查询间隔,另一个事务修改了数据并提交了。
- 幻读是当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录。对幻读的正确理解是一个事务内的读取操作的结论不能支撑之后业务的执行。假设事务要新增一条记录,主键为id,在新增之前执行了select,没有发现id为xxx的记录,但插入时出现主键冲突,这就属于幻读,读取不到记录却发现主键冲突是因为记录实际上已经被其他的事务插入了,但当前事务不可见。
不可重复读和脏读的区别是,脏读是某一事务读取了另一个事务未提交的脏数据,而不可重复读则是读取了前一事务提交的数据。
MySQL数据库为我们提供的四种隔离级别:
-
Serializable (串行化):通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。
- Repeatable read (可重复读):MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行,解决了不可重复读的问题。
- Read committed (读已提交):一个事务只能看见已经提交事务所做的改变。可避免脏读的发生。
- Read uncommitted (读未提交):所有事务都可以看到其他未提交事务的执行结果。
select @@transaction_isolation;
设置隔离级别:
set session transaction isolation level read uncommitted;
生产环境数据库一般用的什么隔离级别呢?
生产环境大多使用RC。为什么不是RR呢?
读已提交(Read Commited),简称为RC
缘由二:在RR隔离级别下,条件列未命中索引会锁表!而在RC隔离级别下,只锁行!
并且大部分场景下,不可重复读问题是可以接受的。毕竟数据都已经提交了,读出来本身就没有太大问题!
编码和字符集的关系
我们平时可以在编辑器上输入各种中文英文字母,但这些都是给人读的,不是给计算机读的,其实计算机真正保存和传输数据都是以二进制0101的格式进行的。
ASCII编码格式。
一个字节,也就是8位
来标识字符,基础符号有128个,扩展符号也是128个。也就只能表示下英文字母和数字。
中文,出现了GB2312的编码格式。为了标识希腊语,出现了greek编码格式,为了标识俄语,整了cp866编码格式。
Unicode编码格式,它用了2~4个字节来表示字符,这样理论上所有符号都能被收录进去,并且它还完全兼容ASCII的编码,也就是说,同样是字母d,在ASCII用64表示,在Unicode里还是用64来表示。
不同的地方是ASCII编码用1个字节来表示,而Unicode用则两个字节来表示。
D ASCII: 01100100
D Unicode: 00000000 01100100
可以看到,上面的unicode编码,前面的都是0,其实用不上,但还占了个字节,有点浪费。如果我们能做到该隐藏时隐藏,这样就能省下不少空间,按这个思路,就是就有了UTF-8编码。
编码。而把n多这种已经编码的字符聚在一起,就是我们常说的字符集。
想看下mysql支持哪些字符集。可以执行 show charset;
utf8和utf8mb4的区别
大utf8。
先说utf8mb4编码,mb4就是most bytes 4的意思,从上图最右边的Maxlen
可以看到,它最大支持用4个字节来表示字符,它几乎可以用来表示目前已知的所有的字符。
utf8,它是数据库的默认字符集。但注意,此utf8非彼utf8,我们叫它小utf8字符集。为什么这么说,因为从Maxlen可以看出,它最多支持用3个字节去表示字符,按utf8mb4的命名方式,准确点应该叫它utf8mb3。
emoji表情,它就不支持。
collation,它是指字符集的比较规则。
这时候就需要用到collation了。
SHOW COLLATION WHERE Charset = 'utf8mb4';可以查看到utf8mb4
下支持什么比较规则。
collation = utf8mb4_general_ci,是指使用utf8mb4字符集的前提下,挨个字符进行比较(general
),并且不区分大小写(_ci,case insensitice
)。
如果改成collation=utf8mb4_bin
,就是指挨个比较二进制位大小。
那utf8mb4对比utf8有什么劣势吗?
char(2)的话,里面的2
是指字符个数,也就是说不管这张表用的是什么编码的字符集,都能放上2个字符。
固定长度,为了能放下2个utf8mb4的字符,char会默认保留2*4(maxlen=4)= 8
个字节的空间。
2 * 3 (maxlen=3) = 6个字节的空间。也就是说,在这种情况下,utf8mb4会比utf8mb3多使用一些空间。
索引
什么是索引?
数据结构。它可以比作一本字典的目录,可以帮你快速找到对应的记录。
索引的优缺点?
优点:
-
加快数据查找的速度
- 为用来排序或者是分组的字段添加索引,可以加快分组和排序的速度
- 加快表与表之间的连接
- 建立索引需要占用物理空间
- 会降低表的增删改的效率,因为每次对表记录进行增删改,需要进行动态维护索引,导致增删改时间变长
最后给大家分享一个Github仓库,上面有大彬整理的300多本经典的计算机书籍PDF,包括C语言、C++、Java、Python、前端、数据库、操作系统、计算机网络、数据结构和算法、机器学习、编程人生等,可以star一下,下次找书直接在上面搜索,仓库持续更新中~
Github地址
索引的作用?
什么情况下需要建索引?
- 经常用于查询的字段
- 经常用于连接的字段建立索引,可以加快连接的速度
- 经常需要排序的字段建立索引,因为索引已经排好序,可以加快排序查询速度
什么情况下不建索引?
- 表记录较少。比如只有几百条数据,没必要加索引。
- 需要经常增删改。需要评估是否适合加索引
- 参与列计算的列不适合建索引
- 区分度不高的字段不适合建立索引,如性别,只有男/女/未知三个值。加了索引,查询效率也不会提高。
where
条件中用不到的字段不适合建立索引
索引的数据结构
索引的数据结构主要有B+树和哈希表,对应的索引分别为B+树索引和哈希索引。InnoDB引擎的索引类型有B+树索引和哈希索引,默认的索引类型为B+树索引。
B+树索引
在 B+ 树中,节点中的 key
从左到右递增排列,如果某个指针的左右相邻 key
分别是 keyi 和 keyi+1,则该指针指向节点的所有 key
大于等于 keyi 且小于等于 keyi+1。
key所在的指针,然后递归地在指针所指向的节点进行查找。直到查找到叶子节点,然后在叶子节点上进行二分查找,找出key
所对应的数据项。
BTREE索引,底层基于B+树数据结构来实现。
mysql> show index from blog\G;
*************************** 1. row ***************************
Table: blog
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: blog_id
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
哈希索引
Hash索引和B+树索引的区别?
- 哈希索引不支持排序,因为哈希表是无序的。
- 哈希索引不支持范围查找。
- 哈希索引不支持模糊查询及多列索引的最左前缀匹配。
- 因为哈希表中会存在哈希冲突,所以哈希索引的性能是不稳定的,而B+树索引的性能是相对稳定的,每次查询都是从根节点到叶子节点。
为什么B+树比B树更适合实现数据库索引?
-
B+树的查询效率更加稳定,任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。
由于B+树的数据都存储在叶子结点中,叶子结点均为索引,方便扫库,只需要扫一遍叶子结点即可,但是B树因为其分支结点同样存储着数据,我们要找到具体的数据,需要进行一次中序遍历按序来扫,所以B+树更加适合在区间查询的情况,而在数据库中基于范围的查询是非常频繁的,所以通常B+树用于数据库索引。
索引有什么分类?
主键索引:名为primary的唯一非空索引,不允许有空值。
唯一索引:索引列中的值必须是唯一的,但是允许为空值。唯一索引和主键索引的区别是:唯一索引字段可以为null且可以存在多个null值,而主键索引字段不可以为null。唯一索引的用途:唯一标识数据库表中的每条记录,主要是用来防止数据重复插入。创建唯一索引的SQL语句如下:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);
3、组合索引:在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,使用组合索引时需遵循最左前缀原则。
全文索引:只能在CHAR
、VARCHAR
和TEXT
类型字段上使用全文索引。
普通索引:普通索引是最基本的索引,它没有任何限制,值可以为空。
什么是最左匹配原则?
>、<
、between
、like
)就会停止匹配,后面的字段不会用到索引。
(a,b,c)建立索引,查询条件使用 a/ab/abc 会走索引,使用 bc 不会走索引。
(a,b,c,d)建立索引,查询条件为a = 1 and b = 2 and c > 3 and d = 4
,那么a、b和c三个字段能用到索引,而d无法使用索引。因为遇到了范围查询。
b = 2这种查询条件无法使用索引。
a = 1时,b值为1,2是有序的状态。当a = 2
时候,b的值为1,4也是有序状态。 当执行a = 1 and b = 2
时a和b字段能用到索引。而执行a > 1 and b = 2
时,a字段能用到索引,b字段用不到索引。因为a的值此时是一个范围,不是固定的,在这个范围内b值不是有序的,因此b字段无法使用索引。
什么是聚集索引?
聚集索引的叶子节点就是整张表的行记录。InnoDB 主键使用的是聚簇索引。聚集索引要比非聚集索引查询效率高很多。
InnoDB来说,聚集索引一般是表中的主键索引,如果表中没有显示指定主键,则会选择表中的第一个不允许为NULL
的唯一索引。如果没有主键也没有合适的唯一索引,那么InnoDB
内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键长度为6个字节,它的值会随着数据的插入自增。
什么是覆盖索引?
select的数据列只用从索引中就能够取得,不需要回表进行二次查询,也就是说查询列要被所使用的索引覆盖。对于innodb
表的二级索引,如果索引能覆盖到查询的列,那么就可以避免对主键索引的二次查询。
对于使用了覆盖索引的查询,在查询前面使用explain
,输出的extra列会显示为using index
。
user_like 用户点赞表,组合索引为(user_id, blog_id)
,user_id
和blog_id
都不为null
。
explain select blog_id from user_like where user_id = 13;
explain
结果的Extra
列为Using index
,查询的列被索引覆盖,并且where筛选条件符合最左前缀原则,通过索引查找就能直接找到符合条件的数据,不需要回表查询数据。explain select user_id from user_like where blog_id = 1;
explain
结果的Extra
列为Using where; Using index
,查询的列被索引覆盖,where筛选条件不符合最左前缀原则,无法通过索引查找找到符合条件的数据,但可以通过索引扫描找到符合条件的数据,也不需要回表查询数据。索引的设计原则?
- 对于经常作为查询条件的字段,应该建立索引,以提高查询速度
- 为经常需要排序、分组和联合操作的字段建立索引
- 索引列的区分度越高,索引的效果越好。比如使用性别这种区分度很低的列作为索引,效果就会很差。
- 避免给"大字段"建立索引。尽量使用数据量小的字段作为索引。因为
MySQL
在维护索引的时候是会将字段值一起维护的,那这样必然会导致索引占用更多的空间,另外在排序的时候需要花费更多的时间去对比。 - 尽量使用短索引,对于较长的字符串进行索引时应该指定一个较短的前缀长度,因为较小的索引涉及到的磁盘I/O较少,查询速度更快。
- 索引不是越多越好,每个索引都需要额外的物理空间,维护也需要花费时间。
- 频繁增删改的字段不要建立索引。假设某个字段频繁修改,那就意味着需要频繁的重建索引,这必然影响MySQL的性能
- 利用最左前缀原则。
索引什么时候会失效?
- 对于组合索引,不是使用组合索引最左边的字段,则不会使用索引
- 以%开头的like查询如
%abc
,无法使用索引;非%开头的like查询如abc%
,相当于范围查询,会使用索引 - 查询条件中列类型是字符串,没有使用引号,可能会因为类型不同发生隐式转换,使索引失效
- 判断索引列是否不等于某个值时
- 对索引列进行运算
- 查询条件使用
or
连接,也会导致索引失效
什么是前缀索引?
有时需要在很长的字符列上创建索引,这会造成索引特别大且慢。使用前缀索引可以避免这个问题。
创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的数据行。
// email列创建前缀索引
ALTER TABLE table_name ADD KEY(column_name(prefix_length));
索引下推
参考我的另一篇文章:图解索引下推!
常见的存储引擎有哪些?
MyISAM、InnoDB、MEMORY、ARCHIVE。MySQL 5.5版本后默认的存储引擎为InnoDB
。
InnoDB存储引擎
默认的事务型存储引擎,使用最广泛,基于聚簇索引建立的。InnoDB内部做了很多优化,如能够自动在内存中创建自适应hash索引,以加速读操作。
优点:支持事务和崩溃修复能力;引入了行级锁和外键约束。
缺点:占用的数据空间相对较大。
适用场景:需要事务支持,并且有较高的并发读写频率。
MyISAM存储引擎
.MYD和索引文件.MYI
。
优点:访问速度快。
缺点:MyISAM不支持事务和行级锁,不支持崩溃后的安全恢复,也不支持外键。
适用场景:对事务完整性没有要求;表的数据都会只读的。
MEMORY存储引擎
MEMORY引擎默认使用哈希索引,将键的哈希值和指向数据行的指针保存在哈希索引中。
优点:访问速度较快。
缺点:
- 哈希索引数据不是按照索引值顺序存储,无法用于排序。
- 不支持部分索引匹配查找,因为哈希索引是使用索引列的全部内容来计算哈希值的。
- 只支持等值比较,不支持范围查询。
- 当出现哈希冲突时,存储引擎需要遍历链表中所有的行指针,逐行进行比较,直到找到符合条件的行。
ARCHIVE存储引擎
MyISAM和InnoDB的区别?
-
存储结构的区别。每个MyISAM在磁盘上存储成三个文件。文件的名字以表的名字开始,扩展名指出文件类型。 .frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。
- 存储空间的区别。MyISAM支持支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后,不会再进行修改操作,可以使用压缩表,极大的减少磁盘的空间占用。InnoDB需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
- 可移植性、备份及恢复。MyISAM数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。对于InnoDB,可行的方案是拷贝数据文件、备份 binlog,或者用mysqldump,在数据量达到几十G的时候就相对麻烦了。
- 是否支持行级锁。MyISAM 只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。而InnoDB 支持行级锁和表级锁,默认为行级锁。行锁大幅度提高了多用户并发操作的性能。
- 是否支持事务和崩溃后的安全恢复。 MyISAM 不提供事务支持。而InnoDB 提供事务支持,具有事务、回滚和崩溃修复能力。
- 是否支持外键。MyISAM不支持,而InnoDB支持。
- 是否支持MVCC。MyISAM不支持,InnoDB支持。应对高并发事务,MVCC比单纯的加锁更高效。
- 是否支持聚集索引。MyISAM不支持聚集索引,InnoDB支持聚集索引。
- 全文索引。MyISAM支持 FULLTEXT类型的全文索引。InnoDB不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。
- 表主键。MyISAM允许没有任何索引和主键的表存在,索引都是保存行的地址。对于InnoDB,如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见)。
-
表的行数。MyISAM保存有表的总行数,如果
select count(*) from table
;会直接取出该值。InnoDB没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了where条件后,MyISAM和InnoDB处理的方式都一样。
MySQL有哪些锁?
按锁粒度分类,有行级锁、表级锁和页级锁。
- 行级锁是mysql中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突,其加锁粒度最小,但加锁的开销也最大。行级锁的类型主要有三类:
- Record Lock,记录锁,也就是仅仅把一条记录锁上;
- Gap Lock,间隙锁,锁定一个范围,但是不包含记录本身;
- Next-Key Lock:Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定记录本身。
按锁级别分类,有共享锁、排他锁和意向锁。
- 共享锁又称读锁,是读取操作创建的锁。其他用户可以并发读取数据,但任何事务都不能对数据进行修改(获取数据上的排他锁),直到已释放所有共享锁。
- 排他锁又称写锁、独占锁,如果事务T对数据A加上排他锁后,则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据,又能修改数据。
- 意向锁是表级锁,其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁:
意向排他锁(IX):类似上面,表示事务准备给数据行加入排他锁,说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
对于INSERT、UPDATE和DELETE,InnoDB 会自动给涉及的数据加排他锁;对于一般的SELECT语句,InnoDB 不会加任何锁,事务可以通过以下语句显式加共享锁或排他锁。
SELECT … LOCK IN SHARE MODE;
SELECT … FOR UPDATE;
MVCC 实现原理?
Multiversion concurrency control) 就是同一份数据保留多版本的一种方式,进而实现并发控制。在查询的时候,通过read view
和版本链找到对应版本的数据。
MVCC 实现原理如下:
-
DB_ROLL_PTR
:回滚指针,指向当前行记录的上一个版本,通过这个指针将数据的多个版本连接在一起构成undo log
版本链。 -
DB_ROW_ID
:主键,如果数据表没有主键,InnoDB会自动生成主键。
DB_TRX_ID
:当前事务id,通过事务id的大小判断事务的时间顺序。
每条表记录大概是这样的:
- 用排他锁锁住该行;
- 将该行原本的值拷贝到
undo log
,作为旧版本用于回滚; - 修改当前行的值,生成一个新版本,更新事务id,使回滚指针指向旧版本的记录,这样就形成一条版本链。
下面举个例子方便大家理解。
DB_ROW_ID和DB_ROLL_PTR
为空。
age修改为12,效果如下:
age修改为8,效果如下:
接下来了解下read view的概念。
read view可以理解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻t的数据时,到t时间点拍的“照片”上取数据。
read view内部维护一个活跃事务链表,表示生成read view
的时候还在活跃的事务。这个链表包含在创建read view
之前还未提交的事务,不包含创建read view
之后提交的事务。
read committed:每次执行select都会创建新的read_view,保证能读取到其他事务已经提交的修改。
read view的记录筛选方式
前提:DATA_TRX_ID
表示每个数据行的最新的事务ID;up_limit_id
表示当前快照中的最先开始的事务;low_limit_id
表示当前快照中的最慢开始的事务,即最后一个事务。
- 如果
- 如果
DATA_TRX_ID
>=low_limit_id
:说明当前版本的记录的事务是在创建read view
之后生成的,该版本的数据行不可以被当前事务访问。此时需要通过版本链找到上一个版本,然后重新判断该版本的记录对当前事务的可见性。 - 如果
up_limit_id
<=DATA_TRX_ID
<low_limit_i
:- 需要在活跃事务链表中查找是否存在ID为
DATA_TRX_ID
的值的事务。 - 如果存在,因为在活跃事务链表中的事务是未提交的,所以该记录是不可见的。此时需要通过版本链找到上一个版本,然后重新判断该版本的可见性。
- 如果不存在,说明事务trx_id 已经提交了,这行记录是可见的。
DATA_TRX_ID
< up_limit_id
:说明在创建read view
时,修改该数据行的事务已提交,该版本的记录可被当前事务读取到。
总结:InnoDB 的MVCC
是通过 read view
和版本链实现的,版本链保存有历史版本记录,通过read view
判断当前版本的数据是否可见,如果不可见,再从版本链中找到上一个版本,继续进行判断,直到找到一个可见的版本。
快照读和当前读
-
UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。
快照读:读取的是快照版本。普通的SELECT
就是快照读。通过mvcc来进行并发控制的,不用加锁。
mvcc机制避免了幻读现象。而mvcc
机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据,这时如果两次查询中间有其它事务插入数据,就会产生幻读。
1、首先,user表只有两条记录,具体如下:
start transaction;
insert into user(user_name, user_password, user_mail, user_state) values('tyson', 'a', 'a', 0);
4、事务b执行全表的update;
update user set user_name = 'a';
5、事务b然后执行查询,查到了事务a中插入的数据。(下图左边是事务b,右边是事务a。事务开始之前只有两条记录,事务a插入一条数据之后,事务b查询出来是三条数据)
那么MySQL是如何避免幻读?
- 在快照读情况下,MySQL通过
- 在当前读情况下,MySQL通过
next-key
来避免幻读(加行锁和间隙锁来实现的)。
mvcc
来避免幻读。
Serializable
隔离级别也可以避免幻读,会锁住整张表,并发性极低,一般不会使用。共享锁和排他锁
select * from table where id<6 lock in share mode;--共享锁 select * from table where id<6 for update;--排他锁
这两种方式主要的不同在于
LOCK IN SHARE MODE
多个事务同时更新同一个表单时很容易造成死锁。commit语句或
rollback
语句结束为止。SELECT... FOR UPDATE 使用注意事项:
for update
仅适用于innodb,且必须在事务范围内才能生效。- 根据主键进行查询,查询条件为
like
或者不等于,主键字段产生表锁。- 根据非索引字段进行查询,会产生表锁。
bin log/redo log/undo log
bin log(二进制日志)和
redo log
(重做日志)和undo log
(回滚日志)。bin log
bin log是MySQL数据库级别的文件,记录对MySQL数据库执行修改的所有操作,不会记录select和show语句,主要用于恢复数据库和同步数据库。
redo log
redo log是innodb引擎级别,用来记录innodb存储引擎的事务日志,不管事务是否提交都会记录下来,用于数据恢复。当数据库发生故障,innoDB存储引擎会使用
redo log
恢复到发生故障前的时刻,以此来保证数据的完整性。将参数innodb_flush_log_at_tx_commit
设置为1,那么在执行commit时会将redo log
同步写到磁盘。undo log
redo log外,当进行数据修改时还会记录
undo log
,undo log
用于数据的撤回操作,它保留了记录修改前的内容。通过undo log
可以实现事务回滚,并且可以根据undo log
回溯到某个特定的版本的数据,实现MVCC。bin log和redo log有什么区别?
bin log
会记录所有日志记录,包括InnoDB、MyISAM等存储引擎的日志;redo log
只记录innoDB自身的事务日志。bin log
只在事务提交前写入到磁盘,一个事务只写一次;而在事务进行过程,会有redo log
不断写入磁盘。bin log
是逻辑日志,记录的是SQL语句的原始逻辑;redo log
是物理日志,记录的是在某个数据页上做了什么修改。讲一下MySQL架构?
-
Server 层:主要包括连接器、查询缓存、分析器、优化器、执行器等,所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图,函数等,还有一个通用的日志模块 binglog 日志模块。
- 存储引擎: 主要负责数据的存储和读取。server 层通过api与存储引擎进行通信。
Server 层基本组件
-
连接器: 当客户端连接 MySQL 时,server层会对其进行身份认证和权限校验。
- 查询缓存: 执行查询语句的时候,会先查询缓存,先校验这个 sql 是否执行过,如果有缓存这个 sql,就会直接返回给客户端,如果没有命中,就会执行后续的操作。
- 分析器: 没有命中缓存的话,SQL 语句就会经过分析器,主要分为两步,词法分析和语法分析,先看 SQL 语句要做什么,再检查 SQL 语句语法是否正确。
- 优化器: 优化器对查询进行优化,包括重写查询、决定表的读写顺序以及选择合适的索引等,生成执行计划。
- 执行器: 首先执行前会校验该用户有没有权限,如果没有权限,就会返回错误信息,如果有权限,就会根据执行计划去调用引擎的接口,返回结果。
分库分表
数据切分可以分为两种方式:垂直划分和水平划分。
垂直划分
优点:行记录变小,数据页可以存放更多记录,在查询时减少I/O次数。
缺点:
- 主键出现冗余,需要管理冗余列;
- 会引起表连接JOIN操作,可以通过在业务服务器上进行join来减少数据库压力;
- 依然存在单表数据量过大的问题。
水平划分
优点:单库(表)的数据量得以减少,提高性能;切分出的表结构相同,程序改动较少。
缺点:
- 分片事务一致性难以解决
- 跨节点
join
性能差,逻辑复杂 - 数据分片在扩容时需要迁移
什么是分区表?
当查询条件的数据分布在某一个分区的时候,查询引擎只会去某一个分区查询,而不是遍历整个表。在管理层面,如果需要删除某一个分区的数据,只需要删除对应的分区即可。
分区表类型
range分区,按照范围分区。比如按照时间范围分区
CREATE TABLE test_range_partition(
id INT auto_increment,
createdate DATETIME,
primary key (id,createdate)
)
PARTITION BY RANGE (TO_DAYS(createdate) ) (
PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
);
在/var/lib/mysql/data/
可以找到对应的数据文件,每个分区表都有一个使用#分隔命名的表文件:
-rw-r----- 1 MySQL MySQL 65 Mar 14 21:47 db.opt
-rw-r----- 1 MySQL MySQL 8598 Mar 14 21:50 test_range_partition.frm
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201801.ibd
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201802.ibd
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201803.ibd
...
list分区
create table test_list_partiotion
(
id int auto_increment,
data_type tinyint,
primary key(id,data_type)
)partition by list(data_type)
(
partition p0 values in (0,1,2,3,4,5,6),
partition p1 values in (7,8,9,10,11,12),
partition p2 values in (13,14,15,16,17)
);
hash分区
create table test_hash_partiotion
(
id int auto_increment,
create_date datetime,
primary key(id,create_date)
)partition by hash(year(create_date)) partitions 10;
分区的问题?
- 打开和锁住所有底层表的成本可能很高。当查询访问分区表时,MySQL 需要打开并锁住所有的底层表,这个操作在分区过滤之前发生,所以无法通过分区过滤来降低此开销,会影响到查询速度。可以通过批量操作来降低此类开销,比如批量插入、
- 维护分区的成本可能很高。例如重组分区,会先创建一个临时分区,然后将数据复制到其中,最后再删除原分区。
- 所有分区必须使用相同的存储引擎。
LOAD DATA INFILE
和一次删除多行数据。
查询语句执行流程?
查询语句的执行流程如下:权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。
select * from user where id > 1 and name = '大彬';
- 首先检查权限,没有权限则返回错误;
- MySQL8.0以前会查询缓存,缓存命中则直接返回,没有则执行下一步;
- 词法分析和语法分析。提取表名、查询条件,检查语法是否有错误;
- 两种执行方案,先查
id > 1
还是name = '大彬'
,优化器根据自己的优化算法选择执行效率最好的方案; - 校验权限,有权限就调用数据库引擎接口,返回引擎的执行结果。
更新语句执行过程?
更新语句执行流程如下:分析器、权限校验、执行器、引擎、redo log
(prepare
状态)、binlog
、redo log
(commit
状态)
update user set name = '大彬' where id = 1;
- 先查询到 id 为1的记录,有缓存会使用缓存。
- 拿到查询结果,将 name 更新为大彬,然后调用引擎接口,写入更新数据,innodb 引擎将数据保存在内存中,同时记录
redo log
,此时redo log
进入prepare
状态。 - 执行器收到通知后记录
binlog
,然后调用引擎接口,提交redo log
为commit
状态。 - 更新完成。
为什么记录完redo log
,不直接提交,而是先进入prepare
状态?
redo log直接提交,然后写binlog
,写完redo log
后,机器挂了,binlog
日志没有被写入,那么机器重启后,这台机器会通过redo log
恢复数据,但是这个时候binlog
并没有记录该数据,后续进行机器备份的时候,就会丢失这一条数据,同时主从同步也会丢失这一条数据。
exist和in的区别?
exists用于对外表记录做筛选。exists
会遍历外表,将外查询表的每一行,代入内查询进行判断。当exists
里的条件语句能够返回记录行时,条件就为真,返回外表当前记录。反之如果exists
里的条件语句不能返回记录行,条件为假,则外表当前记录被丢弃。
select a.* from A awhere exists(select 1 from B b where a.id=b.id)
in
是先把后边的语句查出来放到临时表中,然后遍历临时表,将临时表的每一行,代入外查询去查找。select * from Awhere id in(select id from B)
子查询的表比较大的时候,使用
exists
可以有效减少总的循环次数来提升速度;当外查询的表比较大的时候,使用in
可以有效减少对外查询表循环遍历来提升速度。MySQL中int(10)和char(10)的区别?
truncate、delete与drop区别?
相同点:
truncate和不带
where
子句的delete
、以及drop
都会删除表内的数据。drop、
truncate
都是DDL
语句(数据定义语言),执行后会自动提交。不同点:
truncate 和 delete 只删除数据不删除表的结构;drop 语句将删除表的结构被依赖的约束、触发器、索引;
- 一般来说,执行速度: drop > truncate > delete。
having和where区别?
- 二者作用的对象不同,
-
where
在数据分组前进行过滤,having
在数据分组后进行过滤。
where
子句作用于表和视图,having
作用于组。
为什么要做主从同步?
- 读写分离,使数据库能支撑更大的并发。
- 在主服务器上生成实时数据,而在从服务器上分析这些数据,从而提高主服务器的性能。
- 数据备份,保证数据的安全。
什么是MySQL主从同步?
master),其余的服务器充当从服务器(slave
)。
乐观锁和悲观锁是什么?
数据库中的并发控制是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁是并发控制主要采用的技术手段。
- 悲观锁:假定会发生并发冲突,会对操作的数据进行加锁,直到提交事务,才会释放锁,其他事务才能进行修改。实现方式:使用数据库中的锁机制。
- 乐观锁:假设不会发生并发冲突,只在提交操作时检查是否数据是否被修改过。给表增加
version
字段,在修改提交之前检查version
与原来取到的version
值是否相等,若相等,表示数据没有被修改,可以更新,否则,数据为脏数据,不能更新。实现方式:乐观锁一般使用版本号机制或CAS
算法实现。
用过processlist吗?
show processlist 或 show full processlist
可以查看当前 MySQL 是否有压力,正在运行的SQL
,有没有慢SQL
正在执行。返回参数如下:
-
id:线程ID,可以用
- db:数据库名称
- user:数据库用户
- host:数据库实例的IP
-
command:当前执行的命令,比如
Sleep
,Query
,Connect
等 - time:消耗时间,单位秒
-
state:执行状态,主要有以下状态:
- Sleep,线程正在等待客户端发送新的请求
- Locked,线程正在等待锁
- Sending data,正在处理
SELECT
查询的记录,同时把结果发送给客户端 - Kill,正在执行
kill
语句,杀死指定线程 - Connect,一个从节点连上了主节点
- Quit,线程正在退出
- Sorting for group,正在为
GROUP BY
做排序 - Sorting for order,正在为
ORDER BY
做排序 -
info:正在执行的
SQL
语句
kill id
杀死某个线程
MySQL查询 limit 1000,10 和limit 10 速度一样快吗?
limit offset, size 和 limit size
两种方式。
limit size,相当于 limit 0, size
。也就是从0开始取size条数据。
区别在于offset是否为0。
MySQL内部分为server层和存储引擎层。一般情况下存储引擎都用innodb。
执行器是用于跟存储引擎打交道的组件。
结果集中,最后返回给调用mysql的客户端。
执行select * from xxx order by id limit 0, 10;
,select后面带的是星号,也就是要求获得行数据的所有字段信息。
完整行数据,依次返回给server层,并放到server层的结果集中,返回给客户端。
select * from xxx order by id limit 500000, 10;
完整行数据,返回给server层之后根据offset的值挨个抛弃,最后只留下最后面的size条,也就是10条数据,放到server层的结果集中,返回给客户端。
很多无用的数据,而获取的这些无用数据都是要耗时的。
高度为3的B+树,可以存放多少数据?
InnoDB存储引擎有自己的最小储存单元——页(Page)。
mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
可以看出 innodb 默认的一页大小为 16384B = 16384/1024 = 16kb。
B+树中非叶子节点存的是key + 指针;叶子节点存的是数据行。
对于非叶子节点,如果key使用的是bigint,则为8字节,指针在MySQL中为6字节,一共是14字节,则16k能存放 16 * 1024 / 14 = 1170 个索引指针。
两千多万条数据),也就是对于两千多万条的数据,我们只需要高度为3的B+树就可以完成,通过主键查询只需要3次IO操作就能查到对应数据。
深分页怎么优化?
还是以上面的SQL为空:select * from xxx order by id limit 500000, 10;
方法一:
拷贝完整数据相比只拷贝行数据里的其中一两个列字段更耗费时间。
select * from xxx where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;
先执行子查询 select id from xxx by id limit 500000, 1
, 这个操作,其实也是将在innodb中的主键索引中获取到500000+1
条数据,然后server层会抛弃前500000条,只保留最后一条数据的id。
在拿到了上面的id之后,假设这个id正好等于500000,那sql就变成了
select * from xxx where id >=500000 order by id limit 10;
这样innodb再走一次主键索引,通过B+树快速定位到id=500000的行数据,时间复杂度是lg(n),然后向后取10条数据。
方法二:
根据id主键进行排序,然后分批次取,将当前批次的最大id作为下次筛选的条件进行查询。
select * from xxx where id > start_id order by id limit 10;
通过主键索引,每次定位到start_id的位置,然后往后遍历10个数据,这样不管数据多大,查询性能都较为稳定。
大表查询慢怎么优化?
当MySQL单表记录数过大时,数据库的性能会明显下降,一些常见的优化措施如下:
- 合理建立索引。在合适的字段上建立索引,例如在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描
- 索引优化,SQL优化。最左匹配原则等,参考:https://topjavaer.cn/database/mysql.html#什么是覆盖索引
- 建立分区。对关键字段建立水平分区,比如时间字段,若查询条件往往通过时间范围来进行查询,能提升不少性能
- 利用缓存。利用Redis等缓存热点数据,提高查询效率
- 限定数据的范围。比如:用户在查询历史信息的时候,可以控制在一个月的时间范围内
- 读写分离。经典的数据库拆分方案,主库负责写,从库负责读
- 通过分库分表的方式进行优化,主要有垂直拆分和水平拆分
- 合理建立索引。在合适的字段上建立索引,例如在WHERE和ORDERBY命令上涉及的列建立索引
- 数据异构到es
- 冷热数据分离。几个月之前不常用的数据放到冷库中,最新的数据比较新的数据放到热库中
- 升级数据库类型,换一种能兼容MySQL的数据库(OceanBase、tidb)
MySQL单表多大进行分库分表?
- MySQL 单表数据量大于 2000 万行,性能会明显下降,考虑进行分库分表。
- 阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。
事实上,这个数值和实际记录的条数无关,而与 MySQL 的配置以及机器的硬件有关。因为MySQL为了提高性能,会将表的索引装载到内存中。在InnoDB buffer size 足够的情况下,其能完成全加载进内存,查询不会有问题。但是,当单表数据库到达某个量级的上限时,导致内存无法存储其索引,使得之后的 SQL 查询会产生磁盘 IO,从而导致性能下降。当然,这个还有具体的表结构的设计有关,最终导致的问题都是内存限制。
至于MySQL单表多大进行分库分表,应当根据机器资源进行评估。
说说count(1)、count(*)和count(字段名)的区别
两者的主要区别是
- count(1) 会统计表中的所有的记录数,包含字段为null 的记录。
- count(字段名) 会统计该字段在表中出现的次数,忽略字段为null 的情况。即不统计字段为null 的记录。
执行效果上:
- count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL
- count(1)包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL
- count(字段名)只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者0,而是表示null)的计数,即某个字段值为NULL时,不统计。
- 列名为主键,count(字段名)会比count(1)快
- 列名不为主键,count(1)会比count(列名)快
- 如果表多个列并且没有主键,则 count(1) 的执行效率优于 count(*)
- 如果有主键,则 select count(主键)的执行效率是最优的
- 如果表只有一个字段,则 select count(*)最优。
MySQL中DATETIME 和 TIMESTAMP有什么区别?
嗯,TIMESTAMP
和DATETIME
都可以用来存储时间,它们主要有以下区别:
- DATETIME:1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999
- TIMESTAMP:'1970-01-01 00:00:01.000000' UTC 到 '2038-01-09 03:14:07.999999' UTC
TIMESTAMP
支持的时间范围比DATATIME
要小,容易出现超出的情况。
- TIMESTAMP :占 4 个字节
- DATETIME:在 MySQL 5.6.4 之前,占 8 个字节,之后版本,占 5 个字节
3.存入时间是否会自动转换
TIMESTAMP类型在默认情况下,insert、update 数据时,TIMESTAMP
列会自动以当前时间(CURRENT_TIMESTAMP
)填充/更新。DATETIME
则不会做任何转换,也不会检测时区,你给什么数据,它存什么数据。
TIMESTAMP比较受时区timezone的影响以及MYSQL版本和服务器的SQL MODE的影响。因为TIMESTAMP
存的是时间戳,在不同的时区得出的时间不一致。
- TIMESTAMP:会自动存储当前时间 now() 。
- DATETIME:不会自动存储当前时间,会直接存入 NULL 值。
说说为什么不建议用外键?
外键是一种约束,这个约束的存在,会保证表间数据的关系始终完整。外键的存在,并非全然没有优点。
虽然外键能够保证数据的完整性,但是会给系统带来很多缺陷。
2、扩展性问题。比如从MySQL
迁移到Oracle
,外键依赖于数据库本身的特性,做迁移可能不方便。
使用自增主键有什么好处?
自增主键可以让主键索引尽量地保持递增顺序插入,避免了页分裂,因此索引更紧凑,在查询的时候,效率也就更高。
InnoDB的自增值为什么不能回收利用?
假设有两个并行执行的事务,在申请自增值的时候,为了避免两个事务申请到相同的自增 id,肯定要加锁,然后顺序申请。
事务 B 正确提交了,但事务 A 出现了唯一键冲突。
接下来,继续执行的其他事务就会申请到 id=2,然后再申请到 id=3。这时,就会出现插入语句报错“主键冲突”。
- 每次申请 id 之前,先判断表里面是否已经存在这个 id。如果存在,就跳过这个 id。但是,这个方法的成本很高。因为,本来申请 id 是一个很快的操作,现在还要再去主键索引树上判断 id 是否存在。
- 把自增 id 的锁范围扩大,必须等到一个事务执行完成并提交,下一个事务才能再申请自增 id。这个方法的问题,就是锁的粒度太大,系统并发能力大大下降。
可见,这两个方法都会导致性能问题。
自增主键保存在什么地方?
不同的引擎对于自增值的保存策略不同:
- MyISAM引擎的自增值保存在数据文件中。
- 在MySQL8.0以前,InnoDB引擎的自增值是存在内存中。MySQL重启之后内存中的这个值就丢失了,每次重启后第一次打开表的时候,会找自增值的最大值max(id),然后将最大值加1作为这个表的自增值;MySQL8.0版本会将自增值的变更记录在redo log中,重启时依靠redo log恢复。
自增主键一定是连续的吗?
1、唯一键冲突导致自增主键不连续。当我们向一个自增主键的InnoDB表中插入数据的时候,如果违反表中定义的唯一索引的唯一约束,会导致插入数据失败。此时表的自增主键的键值是会向后加1滚动的。下次再次插入数据的时候,就不能再使用上次因插入数据失败而滚动生成的键值了,必须使用新滚动生成的键值。
3、批量插入导致自增值不连续。MySQL有一个批量申请自增id的策略:
- 语句执行过程中,第一次申请自增id,分配1个自增id
- 1个用完以后,第二次申请,会分配2个自增id
- 2个用完以后,第三次申请,会分配4个自增id
- 依次类推,每次申请都是上一次的两倍(最后一次申请不一定全部使用)
4、自增步长不是1,也会导致自增主键不连续。
MySQL数据如何同步到Redis缓存?
有两种方案:
MySQL触发器+UDF函数实现。
- 在MySQL中对要操作的数据设置触发器Trigger,监听操作
- 客户端向MySQL中写入数据时,触发器会被触发,触发之后调用MySQL的UDF函数
- UDF函数可以把数据写入到Redis中,从而达到同步的效果
2、解析MySQL的binlog,实现将数据库中的数据同步到Redis。可以通过canal实现。canal是阿里巴巴旗下的一款开源项目,基于数据库增量日志解析,提供增量数据订阅&消费。
- canal模拟mysql slave的交互协议,伪装自己为mysql slave,向mysql master发送dump协议
- mysql master收到dump请求,开始推送binary log给canal
- canal解析binary log对象(原始为byte流),将数据同步写入Redis。
为什么阿里Java手册禁止使用存储过程?
先看看什么是存储过程。
存储过程主要有以下几个缺点。
-
存储过程难以调试。存储过程的开发一直缺少有效的 IDE 环境。SQL 本身经常很长,调试式要把句子拆开分别独立执行,非常麻烦。
- 移植性差。存储过程的移植困难,一般业务系统总会不可避免地用到数据库独有的特性和语法,更换数据库时这部分代码就需要重写,成本较高。
- 管理困难。存储过程的目录是扁平的,而不是文件系统那样的树形结构,脚本少的时候还好办,一旦多起来,目录就会陷入混乱。
- 存储过程是只优化一次,有的时候随着数据量的增加或者数据结构的变化,原来存储过程选择的执行计划也许并不是最优的了,所以这个时候需要手动干预或者重新编译了。
300多本经典的计算机书籍PDF,包括C语言、C++、Java、Python、前端、数据库、操作系统、计算机网络、数据结构和算法、机器学习、编程人生等,可以star一下,下次找书直接在上面搜索,仓库持续更新中~
Github地址