数据库MySQL

4 minutes read
计算机体系知识
面试

常问问题：

  1. 可能会手撕数据库语句，一般是查询，这个看你运气了
  2. 一些关键字
  3. 四个特性，三个“读”（脏读、幻读、不可重复读）、四个隔离级别、乐观悲观锁、表锁行锁之类的
  4. 索引是什么、创建索引原则、索引类型
  5. 数据库引擎、底层（B+树）及好处
  6. 其余的自己看下面的，多理解几遍就能讲出来了
  7. 另外还可能要去了解下主从数据库（读写分离）、分库分表、平滑扩容 相关的大致流程，我下面没写

知识内容

InnoDB和MySAM

  • InnoDB和MySAM
  • 操作性：
    • InnoDB具有事务，支持四个事务隔离级别。
      • 适用于大量INSERT或UPDATE操作。
      • 不支持全文索引，新版本支持。
      • 支持外键
    • MyISAM管理非事务表
      • 它提供高速存储和检索，以及全文搜索能力。
      • 如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择
      • 不支持事务、外键
  • 存储：
    • MyISAM在磁盘上存储成三个文件。表定义 .frm，数据文件.MYD， 索引文件.MYI。跨平台转移麻烦
    • InnoDB：空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小
  • 索引：
    • InnoDB（索引组织表）：能缓存索引，也能缓存数据，必须得有主键
    • MyISAM（堆组织表）：只能缓存索引。非聚类
  • InnoDB 在做SELECT的时候，要维护缓存数据和索引和其余的，慢一些。MyISAM只缓存索引块
  • MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
    • B+树搜索算法搜索索引-取出data值-以此为地址读取数据记录
    • 主索引要求key是唯一的，而辅助索引的key可以重复
  • InnoDB也使用B+Tree作为索引结构，数据文件本身就是索引文件。
    • 叶节点data域就是数据记录，称之聚类索引
    • 本身要按主键聚类，所以必须要主键（设置自增主键），没有的话分裂维持特性会十分低效。没指定的话会自动选择合适的或自动生成一个隐含字段。长度为6的长整型
    • 辅助索引data存储的主键值

索引

为了 加快对数据表中的检索而创建的分布存储的数据结构

  • 索引可以看作一张表，指向实体表记录（书的目录，便于查找）

  • 唯一索引，保证每行数据的唯一性

  • 加大检索速度，特别是分组和排序

  • 加速表和表之间的连接速度

  • 缺点：创建和维护要耗时（增删时要动态维护），占用物理空间

  • 总结：会提高查询速度，但是DML会变慢（更新维护索引）

范式

  • 第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
  • 第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。
  • 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.
  • 注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性
  • 第四范式：要求把同一表内的多对多关系删除。
  • 第五范式：从最终结构重新建立原始结构。
  • BC范式（BCNF）：符合3NF，并且，主属性不依赖于主属性。若关系模式R属于第一范式，且每个属性都不传递依赖于键码，则R属于BC范式。

事务

多条语句同时成功或同时不成功，有一条失败会回滚，所有事务操作取消

  • 用 BEGIN, ROLLBACK, COMMIT 来实现
    • BEGIN 或 START TRANSACTION; 开始一个事务
    • ROLLBACK 事务回滚
    • COMMIT 事务确认
  • 直接用 SET 来改变 MySQL 的自动提交模式:
    • SET AUTOCOMMIT=0 禁止自动提交
    • SET AUTOCOMMIT=1 开启自动提交
START TRANSACTION; // 开启事务
INSERT INTO stu (class_id,sname,sex)VALUES(2,'xx','x');
COMMIT;

四大特性

  • 原子性：不能部分执行，事务不能完成也要回滚消除影响
    • 侧重事务本身的职责，不管具体内容，只管能不能完成事务。
  • 一致性：事务操作就是把数据库从一个状态到另一个状态。
    • 侧重事务完成的结果
    • 比如A和B都有1000，总共2000。A向B转账500，那么必须保持事务发生后A B都为1000
      • A 减少500 和 B增加500
      • 这两个操作必须在事务内全部得到实现
  • 隔离性：多事务并发运行，各自执行各自的，互不影响
  • 持久性：数据库出错也能恢复，或者事务提交后不会再发生意外改变

隔离问题

  • 脏读：A读取了B事务过程中（未提交）的数据，但B后面进行了回滚。A读取到的即为脏数据

  • 不可重复读：相对于update

    • A多次读取同一数据
    • B的事务在A事务多次读取的过程中进行了更新并提交
    • A多次读取的结果不一致

  • 幻读：相对于 insert和delete操作

    • A将数据库中所有成绩从具体分数改为ABCD等级
    • 但B这个时候插入了具体分数记录
    • A改结束，发现有一条记录没改过来，像出现幻觉一样

    不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。

    解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表四种隔离级别

隔离级别

事务隔离级别 脏读 不可重复读 幻读 说明
读未提交（read-uncommitted） 是 是 是 最低的事务隔离级别，一个事务还没提交时，它做的变更就能被别的事务看到
读已提交（read-committed） 否 是 是 保证一个事物提交后才能被另外一个事务读取。另外一个事务不能读取该事物未提交的数据。
可重复读（repeatable-read） 否 否 是 多次读取同一范围的数据会返回第一次查询的快照，即使其他事务对该数据做了更新修改。事务在执行期间看到的数据前后必须是一致的。
串行化（serializable） 否 否 否 事务 100% 隔离，可避免脏读、不可重复读、幻读的发生。花费最高代价但最可靠的事务隔离级别。

控制和查询

select @@tx_isolation; // 查询隔离级别
set session transaction isolation level read uncommitted; // 设置隔离级别

锁机制

因为Mysql支持多线程方式，所以可以同时处理多个客户端请求。有时为了防止客户端同时修改数据，我们使用锁操作完成。

InnoDB 是主流储存引擎并支持行级锁的，有更高的并发处理性能，下面来演示行锁的运行过程。

MyIsam引擎在最新版本的MYSQL中已经废弃所以不过多讨论了。

  • 行锁开销大，锁表慢
  • 行锁高并发下可并行处理，性能更高
  • 行锁是针对索引加的锁，在通过索引检索时才会应用行锁，否则使用表锁
  • 在事务执行过程中，随时都可以执行锁定，锁在执行 COMMIT或者ROLLBACK的时候释放

悲观锁

对数据被外界修改持保守态度，在整个数据处理过程中，将数据处于锁定状态，可以很好地解决并发事务的更新丢失问题。

  • 事务A执行悲观锁（for update）操作后，其他事务执行同一代码时将阻塞

    BEGIN;
SELECT * FROM goods WHERE id=1 FOR UPDATE;
UPDATE goods SET num=num-2 WHERE id=1; 
...

  • 事务B执行以下代码将不能查询库存，必须等事务A提交或回滚事务

    BEGIN;
SELECT * FROM goods WHERE id=1 FOR UPDATE;
-- 阻塞中...

  • 事务A提交后，事务B会得到事务A操作后的结果

乐观锁

（高并发环境使用乐观锁，悲观锁锁表不利于高并发）

在每次去拿数据的时候认为别人不会修改，不对数据上锁，但是在提交更新的时候会判断在此期间数据是否被更改，如果被更改则提交失败。用记录号来标明是否发生更改

表锁机制

针对一些不支持事务的处理引擎可以使用锁表的方式控制业务。

  • 读锁：为表设置读锁后，当前会话和其他会话都不可以修改数据。
  • 写锁：为表设置了写锁后，当前会话可以修改，查询表，其他会话将无法操作。

外键约束

一个表中的字段被另一个表中的一个字段引用，对相关表中的数据造成了限制，可以保持参照完整性

条件：

  • 父子表引擎一致
  • InnoDB支持外键
  • 类型一致，使用索引实现
CREATE TABLE class (id int PRIMARY KEY AUTO_INCREMENT,name varchar(50));

ALTER TABLE stu ADD 
CONSTRAINT stu_class
FOREIGN KEY (class_id) 
REFERENCES class(id) 
ON DELETE SET NULL
ON UPDATE CASCADE;

ALTER TABLE stu DROP FOREIGN KEY stu_class; // 删除外键
选项 说明
CONSTRAINT 为外键约束定义名称
FOREIGN KEY 子表与父表关联的列
REFERENCES 子表关联的父表字段
ON DELETE 父表删除时的处理方式
ON UPDATE 父表更新时的处理方式

对于ON UPDATE和ON DELETE

ON DELETE CASCADE    删除父表记录时，子表记录同时删除
ON DELETE SET NULL    删除父表记录时，子表记录设置为NULL（子表字段要允许NULL）
ON DELETE NO ACTION
ON DELETE RESTRICT    删除父表记录时，子表不做任何处理，必须把子表处理完才可以删除主表

规范性

https://blog.csdn.net/hh680821/article/details/79040264

其他

DML（data manipulation language）： 它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言 DDL（data definition language）： DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用 DCL（Data Control Language）： 是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

使用PDO对象中的prepare()和PDOStatement对象中的execute()两个方法结合，避免注入

SQL刷题

T175 组合两个表

表1: Person
+-------------+---------+
| 列名         | 类型     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+
PersonId 是上表主键

表2: Address
+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+
AddressId 是上表主键

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：
FirstName, LastName, City, State

思路：连接查询，xxx join 和 on

SELECT p.FirstName,p.LastName,a.City,a.State
FROM Person p LEFT JOIN Address a
ON p.PersonId=a.PersonId;

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 在使用left jion时，on和where条件的区别如下：

1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

优化

优化方法

  • 整理

    • null值容易引发灾难，id is null而不是id=null
    • 用union代替or

  • 数据库设计

  1. 避免全表扫描，在where 及 order by 涉及的列上建立索引

  2. 避免在 where 子句中对字段进行 null 值判断，引擎会放弃索引而使用全局扫描。可以考虑把null默认值设为0

  3. 一个表的索引数最好不要超过6个，在一些不常用到的的列不需要索引。因为会降低插入、修改的速度

  4. 避免更新索引数据，若该列频繁更新，考虑索引的必要性

  5. 字符会一一比较，数字只需要比较一次。所以能用数值尽量不要用字符

  6. 尽可能的使用 varchar/nvarchar 代替 char/nchar，变长字段存储空间小，可以节省存储空间

  7. 尽量少使用临时表

  • SQL语句

  1. 避免在Where使用 != 或 <>，以及or，否则引擎放弃使用索引而进行全表扫描。

    select id from t where num=10 or num=20
select id from t where num=10 union all select id from t where num=20

  2. 能用 between 就不要用 in，not in

  3. where 子句中使用参数，也会导致全表扫描。强制加上索引

    select id from t where num=@num
select id from t with(index(索引名)) where num=@num

  4. 避免在 where 子句中对字段进行表达式操作：=”左边进行函数、算术运算或其他表达式运算

    select id from t where num/2=100 
select id from t where num=100*2

  5. 具体的字段列表代替“*”，不要返回用不到的任何字段。

  6. 避免使用游标，超过一万行就考虑改写

  7. 避免大事务操作

基础操作

库表操作

  • 登录：

    • -h：主机名
    • -u：用户名
    • -p：使用密码登录

  • 管理：

    • use DB；
    • show database
    • show tables
    • show columns form 表
    • show index form 表
    • exit

  • 数据库操作：

    • 创建：CREATE DATABASE 数据库名;
    • 删除：drop database 数据库名;

  • 创建表：CREATE TABLE table_name ('列名' 类型 属性, 列名 类型, 主键('xxx'));

    CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT, // 自增
   `runoob_title` VARCHAR(100) NOT NULL, // 非空
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` ) // 主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CURD

  • 删除：DROP TABLE table_name ;

  • 插入：INSERT INTO xxx (field1, field2,...fieldN) VALUES(field1, field2,...,fieldN)

  • 查询：

    SELECT column_name, column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
    • LIMIT 4读取四条；LIMIT 2, 4:第三条起读取四条。分页LIMIT startRow,pageSize;
    • limit 4 offset 9：返回四行，从第十行开始

  • 更新：UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

  • 删除：DELETE FROM table_name WHERE age<20

  • Like：条件， %字符来表示任意字符，类似正则*。'%COM'

  • UNION：连接两个查询的集合，删除重复数据。

    • SELECT A, B FROM tables WHERE XX
UNION [ALL | DISTINCT]
SELECT A, C FROM tables WHERE XX;

    • DISTINCT默认就是删除重复，加不加无用。ALL返回所有结果集，包含重复的

  • ORDER BY： ORDER BY XXX DESC，默认升序。

  • GROUP BY：按字段分组，并统计数目。GROUP BY name得到：name | count数目

  • 连接：

    • INNER JOIN（内连接, 或等值连接）：获取两个表中字段匹配关系的记录。
    • LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
    • RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

  • Alter：修改数据表名或字段。

    • ALTER TABLE testalter_tbl DROP i; 删除表的i字段
    • ALTER TABLE testalter_tbl ADD i INT; 表添加字段，默认是末尾
    • ALTER TABLE testalter_tbl ADD i INT FIRST;第一列添加
    • ALTER TABLE testalter_tbl ADD i INT AFTER c;c列之后添加
    • ALTER TABLE testalter_tbl MODIFY c CHAR(10);改类型
    • ALTER TABLE testalter_tbl CHANGE i j BIGINT;改i为j，类型为BIGINT
    • ALTER TABLE testalter_tbl RENAME TO alter_tbl; 改表名

  • 索引：

    • CREATE INDEX indexName ON mytable(username(length));
    • ALTER table tableName ADD INDEX indexName(columnName)
    • DROP INDEX [indexName] ON mytable;

  • 添加主键命令：

    mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

  • 索引类型：（有的地方是其他几种，建议百度）

    • 普通索引：没有任何限制
    • 唯一索引：索引列的值必须唯一，但允许有空值。
    • 主键索引：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引
    • 组合索引：平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。

导出数据

mysqldump -u root -p dbs_name table_name > dump.sql [--all-databases]
mysql -u root -p dbs_name < dump.sql

create database adc;
use abc;
set names ytf8;
source /xx/xx/xx/abc.sql

分享：