首页  思维导图  详情

MySQL

2022-08-12 17:54:46   0  举报





AI智能生成

MySQL基础知识梳理

mysql

作者其他创作

大纲/内容

其他面试题

MySQL查询过程

客户端与MySQL服务器通过连接器建立连接（TCP连接）——查缓存，缓存命中直接返回结果，缓存找不到——分析器语法分析——优化（索引）——执行器执行（返回结果，缓存到查询缓存中）

为什么使用B+树而不是用B- 树，Hash，红黑树，二叉树

首先答B+树的两个优点

磁盘读写代价低

只需遍历叶子结点

Hash缺点

虽然可以快速查找，但是无序，只适合等值查询，不适合范围查找，排序时也无法像B+树一样利用索引

存储引擎中只有Memory显式支持哈希索引

在大量重复数据的情况下，哈希碰撞，查询效率极低

不支持部分索引列的匹配查找（因为索引始终索引的所有列的全部内容）

B-树缺点

B+树本来就是B-树的改进版本，B+树数据都在叶子结点上，并且叶子结点间有顺序访问指针，更支持范围查找

红黑树缺点

树的高度随数量的增加而增加

IO代价高

二叉树缺点

不是平衡树，树易于一边倾斜效率低下

IO代价也高

回表查询

什么是回表

先在非聚簇索引中查找到主键，再根据主键聚簇索引查找数据的过程

非聚簇索引一定回表吗

不一定哦，当查询字段全部命中索引时，就直接查找到了，不必再回表，比如对age建立索引，则在select age from s where age > 2;时，直接就找到了

联合索引

使用多个字段同时建立一个索引

最左前缀原则

在多列索引时，MySQL会一直向右匹配直到遇到范围查询

慢SQL

指运行时间超过long_query_time值的SQL

如何优化

1.设置慢查询

2.分析慢查询日志

3.定位低效率的SQL

4.expain分析执行计划（如命中索引没）

5.优化

索引优化

尽量覆盖索引

索引符合最左匹配原则

避免索引失效

SQL语句优化

分页查询优化

插入成组插入

数据有序插入等

数据库结构优化

分表

合表

中间表等

优化器优化、架构优化等（磁盘预读，读写分离等）

读写分离？主从同步？

读写分离——一个主库附有多个从库，每次只需写入主库，主库自动把数据同步给从库

主从同步——数据可以从一个数据库服务器复制到其他服务器上

锁

当多用户并发存取数据时，即多个事务对同一批数据操作时，为了保证数据的正确性一致性完整性，就需要考虑锁

分类

按锁的粒度

行锁

并发度最高，粒度最细，开销大，加锁慢，发生锁冲突的概率最低，但有可能发生死锁

页锁

指一次锁定相邻的一组记录，注意，InnoDB是不支持页锁的，只支持另两种

表锁

并发度最低，粒度最粗，开销小，加锁快，锁冲突的概率最高，但不会发生死锁

按锁的类别

共享锁（读锁）

排他锁（写锁）

悲观锁

乐观锁

锁优化

使用较低的隔离级别

尽量使用索引使加锁更准确

只读无需加锁

选择合理的事务大小，尽量一次性请求足够级别的锁

事务

逻辑上的一组操作，要么都执行要么都不执行

四大特性

原子性

一致性

靠“我们”共同保证

隔离性

串读（可串行化）

可重复读

幻读

读已提交

不可重复读

读未提交

脏读

持久性

概念

关系型数据库、开源免费、不适合复杂业务

各种码/键

超码——能唯一标识一个元组的一个/一组属性

主码——最小的超码是主码，主码可以有多个

候选码——从主码中选一个做主码

外码——在另一个表中是主码

范式

第一范式——原子性

第二范式——非主属性对主属性完全函数依赖（排除了部分函数依赖）

第三范式——非主属性直接依赖于非主属性（排除了传递函数依赖）

BC范式——排除了任何属性（包括主属性）传递函数依赖和部分函数依赖

约束

Primary Key——主键

Foreign Key——外键

Not Null——不允许为空

Unique——唯一不重复

AI——自增

SQL语句

DDL——数据定义语言，结构处理，如新建库表

建库删库

-- 如果 taobao_0326 库存在，就删掉这个库 DROP DATABASE IF EXISTS `taobao_0326`;-- 重新创建一个新库 CREATE DATABASE `taobao_0326` DEFAULT CHARACTER SET utf8mb4;

建表删表

建表

create table student ( id INT, sn INT comment '学号', name VARCHAR(20) comment '姓名', qq_mail VARCHAR(20) comment 'QQ邮箱' );

删表

drop

drop table if exists student;

truncate

truncate table stutent;

DCL——数据控制语言，如添加用户，修改密码

DML——数据操纵语言，CURD

增

insert into students(id,name) values (1,aa),(2,'哈哈'）；

删

delete from students;删除整张表delete from students where……删除指定的几行

查

where,between and,like % _

group by,having

order by,默认是升序

in——外表大内表小适用in

exists——外表小内表大适用exists

连表查询（笛卡儿积）

内连接（默认）

inner join

外连接

left join

right join

自连接

limit m offset n——从n开始取m条，n可以取到0

distinct去重

union——合并重复行

union all ——不合并重复行

改——update students set name = b where

存储引擎

5.5以前默认是MyISAM，5.5及之后默认是InnoDB，还有其他比如Memory，,Archive等

InnoDB VS MyISAM

InnoDB支持事务、外键，而MyISAM并不支持；InnoDB的主键索引是聚簇索引，而MyISAM无论主键还是二级都是非聚簇索引；InnoDB采取行锁（默认）和表锁，而MyISAM采取表锁；InnoDB不支持全文索引而MyISAM支持；InnoDB不保存表的具体行数，而MyISAM设有一个变量保存行数

索引

概念

特殊的数据文件，包含数据库里所有记录的引用指针；就是为了提升查询速度

优点

提升查询速度

缺点

索引的创建维护需消耗额外的时间

索引也占据了一定物理空间

增删改操作也要额外维护索引，性能下降

分类

按应用层次划分

普通索引