首页  思维导图  详情

MySQL

2021-10-09 11:06:28   1  举报





AI智能生成

MySQL面经大全

MySQL原理

mysql原理解析

MySQL

作者其他创作

大纲/内容

MySQL体系结构

存储引擎

MyISAM

早些年使用的
节约空间，速度较快

InnoDB

安全性高，事务的处理，多表多用户操作
每次磁盘IO会利用局部性取出，一页数据大小默认为16kb，，每页的数据都是默认按主键顺序存储，包含内容：
页头：头尾指针
页目录：数组结构，给数据区分组，每6条数据为一组，按分组查找对应数据区，目录也会用二分法进行查找
用户数据区：链表结构，每一条数据，数据库会根据字段设置设置好固定的空间，varchar等不确定的空间会直接存放指针

区别

MYISAM INNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大，约两倍

索引：
索引（Index）是帮助MySQL高效获取数据的数据结构。
提取句子主干，就可以得到索引的本质：索引是数据结构
1.在创建表的时候给字段增加索引
2.创建完毕后，增加索引
MYSQL8索引可以进行ESC DESC升序降序排列

数据结构角度

B+树索引

B树为什么性能高：
主存读取时不存在机械操作，即经过地址总线请求，输出的数据总线无论数据所在什么为止都是固定的时间
磁盘存取存在机械操作，即移动磁头至磁道，移动盘片至扇区才可以读取数据，每次加载一个或多个页的数据至主存
一次B树检索最多访问h个节点（树高），数据库的设计利用了磁盘预读原理（一次从磁盘读取一页数据），将一个节点的大小设为等于一个页，这样每个节点只需要一次IO就可以完全载入。
特点：1.叶子节点有指针，2.一个节点里面可以存多个元素，3.所有的非叶子节点不存储数据
MYSQL的B+树叶子节点是双向指针，为了优化查询<

B-树与B+树的区别：
B+叶子节点有指针，B没有
B+数据都在叶子节点上，B不是

Hash索引

查询单条快，范围查询慢

Full-Text索引

R-Tree索引

物理存储角度

聚集索引

InnoDB的索引方式就是聚集索引，即数据文件本身就是索引文件
在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

非聚集索引，也叫辅助索引

MyISAM的索引方式为“非聚集的”，索引文件和数据文件是分离的
data域保存数据记录的地址，索引文件仅保存数据记录的地址

逻辑角度

普通索引
Index

加速查找

唯一索引

主键索引
PRIMARY　KEY

加速查找+约束（不为空且唯一）
B+树的主键索引：唯一的标识，不可重复，只能有一个列作为主键，若没有主键，会创建隐藏主键
主键索引会以主键的顺序来排序生成一个索引目录，为主键+字段为用户数据区
而每个数据区会链表结构以6条为一组分为数组结构的页目录，每个页中存储的为数固定的数据区+上一个主键索引页目录
如果数据大于16K，则以页为单位，页又是一个链表，就需要再建立一个以页首个数据的主键值组成的一个页目录。

唯一索引
UNIQUE KEY

加速查找+约束（唯一）

联合索引

联合主键索引
PRIMARY KEY (id,name)

联合唯一索引
UNIQUE (id,name)

联合普通索引
INDEX (id,name)

联合索引就是把主键索引的主键顺序替换为 A/B/C 顺序，即先比较A再比较B再比较C的顺序
再这个排序基础上建立索引，且建立的索引只包含bcd并会指向真实数据的主键

最左原则：
因为多列索引是按照ABC的顺序排序的
索引以A或AB来查询会走多列索引的数据顺序，索引可以利用索引查询
但BC 或 C 无法判断出多列索引中数据的顺序，索引无法查询

全文索引
FullText

特定的数据库引擎才有，MyISMA
用于搜索很长一篇文章

空间索引
SPATIAL

几乎不用

事务

四大特性

1.原子性（Atomicity）：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。失败回滚的操作事务，将不能对事务有任何影响。

2. 一致性（Consistency）：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

3. 隔离性（Isolation）：　　隔离性是指当多个用户并发访问数据库时，比如同时访问一张表，数据库每一个用户开启的事务，不能被其他事务所做的操作干扰(也就是事务之间的隔离)，多个并发事务之间，应当相互隔离。

4. 持久性（Durability）
　　持久性是指事务的操作，一旦提交，对于数据库中数据的改变是永久性的，即使数据库发生故障也不能丢失已提交事务所完成的改变。

四个隔离级别

读未提交
READ UNCOMMITTED

一个事务能够读取到别的事务中没有提交的更新数据。事务可以读取到未提交的数据，这也被称为脏读(dirty read)。
问题：脏读，不可重复读，幻读

读已提交
READ COMMITTED

一个事务只能读取到别的事务提交的更新数据。不可重复读：同一个事务对数据的多次读取的结果不一致。
每次读取都生成一个ReadView
问题：不可重复读，幻读解决：脏读

可重复读
REPEATBABLE READ

保证同一事务中先后执行的多次查询将返回同意结果，不受其他事务的影响。这种隔离级别可能出现幻读。（mysql使用行锁解决幻读）
第一次读取生成ReadView
问题：幻读解决：脏读。不可重复读

串行化
SERIALIZABLE

不允许事务并发执行，强制事务串行执行，就是在读取的每一行数据上都加上了锁，读写相互都会阻塞。这种隔离级别最高，是最安全的，性能最低，不会出现脏读，不可重复读，幻读，丢失更新。

事务的传播机制

propagation_requierd：如果当前没有事务，就新建一个事务，如果已存在一个事务中，加入到这个事务中，这是最常见的选择。
propagation_supports：支持当前事务，如果没有当前事务，就以非事务方法执行。
propagation_mandatory：使用当前事务，如果没有当前事务，就抛出异常。
propagation_required_new：新建事务，如果当前存在事务，把当前事务挂起。
propagation_not_supported：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
propagation_never：以非事务方式执行操作，如果当前事务存在则抛出异常。
propagation_nested：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与propagation_required类似的操作

MVCC
多版本并发控制

指的就是再使用READ COMMITTD , REPEATABLE READ 这两种隔离级别的事务再执行普通的SELECT操作时访问记录的版本链的过程。可以使用不同的事务的读-写，写-读操作并发执行，从而提升系统性能。READ COMMITTD ， REPEATABLE READ这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，READ COMMITTD在一次进行普通SELECT操作前都会生成一个ReadView ，而REPEATABLE READ只是在第一次进行普通SELECT操作前生成了一个ReadView，之后的查询操作都重复使用这个ReadView就好了。

版本链

对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，我们创建的表中又主键或者非NULL唯一键时都不会包含row_id列）
tx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列
roll_pointer：每次都某条记录进行改动时，这个隐藏列会存一个指针，可以通过这个指针找到该记录修改前的信息

ReadView

对于使用Read UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版就好了，对于使用SERIALIZABLE隔离级别的事务来说。使用加锁的方式来访问记录。对于使用READ COMMITTED和REPEATABLE READ隔离级别来说，就需要使用版本链了。
核心问题：需要判断一下版本链中那个版本是当前事务可见的。

四个重要内容：
m_ids : 表示生成ReadView时当前系统中活跃的读写事务的事务id列表
min_trx_id : 表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值
max_trx_id : 表示生成ReadView时系统中应该分配给下一个事务的id值
creator_trx_id ：表示生成该ReadView的事务的事务id

锁

读写锁

读锁：共享锁，Shared Locks，S锁
写锁：排他锁，Exclusive Locks，X锁
select：不加锁

S锁 X锁
S锁不冲突冲突
X锁冲突冲突

读锁：对于普通的SELECT语句，InnoDB不会加任何锁
加一个S锁 select ... lock in share mode
使用场景：读出数据后，其他事务不能修改，但是自己也不一定能修改，因为读锁可以多个事务添加。

写锁：将查找的数据加一个X锁，不允许其他事务进行加锁，提交或回滚事务时候释放锁
加一个X锁：select ... for update
使用场景: 读出数据后，其他事务既不能写，也不能加读锁，那么就导致只有自己可以修改数据。

行锁

LOCK_REC_NOT_GAP : 单个行记录上的锁
LOCK_GAP : 间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
LOCK_ORDINARY : 锁定一个范围，并且锁定记录本身。对于行的插入，都是采用该方法，主要目的是解决幻读问题

读已提交：加的是单行锁
锁只会加在查询出来的数据上，行锁
Insert 可以插入数据

可重复读：
基于索引加的是一个间隙锁为了解决幻读 Insert 插入不了已经加锁的数据区
全表扫描情况下因为所有的都可以修改，相当于把所有的行锁间隙锁都锁住

写操作

DELETE : 删除一条数据时，先加X锁，再执行删除操作
INSERT : 先加隐式锁保护这条数据再事务提交前不被其他事务访问到
UPDATE：如果被更新的列修改签后没有导致存储空间变化，那么先加X锁，再对其修改。如果变化，先加X锁，再删除，再INSERT一条新数据

MySQL调优

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析

MySQL语句

数据表操作

索引

显示所有索引信息：Show INDEX FROM xxx
增加一个全文索引：ALTER TABLE xxx库.xxx表 ADD FULLTEXT INDEX `索引名`(`索引列`);
分析sql执行的状况：EXPLAIN sql语句

创建索引

create index index_name on table_name (column_list [DSC/DESC]) ;
create unique index index_name on table_name (column_list) ;

删除索引

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

表链接JOIN

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
例 SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author

分组 GROUP BY

GROUP BY 语句根据一个或多个列对结果集进行分组。每种结果显示一个（第一条记录）
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…

排序 ORDER BY

默认ASC升序或 DESC降序关键字来设置查询结果排列。
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

多表查询 UNION

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
参数
expression1, expression2, ... expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选，检索条件。
DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL: 可选，返回所有结果集，包含重复数据。

NULL值处理

IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

插入数据 INSERT INTO

INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );

删除数据 DELETE

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
DELETE FROM table_name [WHERE Clause]

更新 UPDATE

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

LIKE 子句

SQL LIKE 子句中使用百分号 %字符来表示任意字符如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。
SELECT field1, field2,...fieldN FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

数据库操作

使用mysql二进制方式连接

[root@host]# mysql -u root -p
Enter password:******

创建数据库

CREATE DATABASE 数据库名;

删除数据库

drop database <数据库名>;

选择数据库

[root@host]# mysql -u root -p
Enter password:******
mysql> use RUNOOB;
Database changed
mysql>

数据类型

类型范围格式用途
DATE 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1901/2155 YYYY 年份值
DATETIME 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 1970-01-01 00:00:00/2038 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

数据库链接