mySql 基础 | LaiYong Wang

数据库三范式

可以逆范式
第一范式（1NF）：字段具有原子性,不可再分。(所有关系型数据库系统都满足第一范式数据库表中的字段都是单一属性的，不可再分)
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。
满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 >所以第三范式具有如下特征： >>1. 每一列只有一个值 >>2. 每一行都能区分。 >>3. 每一个表都不包含其他表已经包含的非主关键字信息。

索引类型

普通索引: 即针对数据库表创建索引
唯一索引: 与普通索引类似，不同的就是：MySQL 数据库索引列的值必须唯一，但允许有空值
主键索引: 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引
组合索引: 为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。即将数据库表中的多个字段联合起来作为一个组合索引。

索引的工作机制
是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B 树及其变种 B+树（mysql innodb 为 B+ 树，因为同样深度，能保存更多数据。所以查询速度也会更快）

回表
普通索引查询主键后，再由主键查询具体想查的数据
若想查的数据为主键，则无需回表，若是组合索引，想查的数据在组合索引里，也无需回表
1.B+树的的非叶子节点只存储索引,叶子节点存储数据（所以B+树能存储更多的索引，并且查询次数也是一样的）
2.B+树每个叶子节点都包含了根节点的键值数据，每个叶子节点的关键字从小到大链

mysql 的复制原理、类型以及流程

原理
Mysql 内建的复制功能是构建大型，高性能应用程序的基础。将 Mysql 的数据分布到多个系统上去，这种分布的机制，是通过将 Mysql 的某一台主机的数据（binglog）复制到其它主机（slaves）上，并重新执行一遍来实现
复制类型
1、基于语句的复制：在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制
2、基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍. 从 mysql5.0 开始支持
3、混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制
流程
一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。过程如下 1. 主服务器把更新记录到二进制日志文件中。 2. 从服务器把主服务器的二进制日志拷贝到自己的中继日志（replay log）中。 3. 从服务器重做中继日志中的时间，把更新应用到自己的数据库上

myisam 与 innodb 的区别

InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
InnoDB 是聚集索引，MyISAM 是非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；

InnoDB 事务的四种隔离级别

读未提交（READ UNCOMMITTED）
READ UNCOMMITTED 提供了事务之间最小限度的隔离。除了容易产生虚幻的读操作和不能重复的读操作外，处于这个隔离级的事务可以读到其他事务还没有提交的数据，如果这个事务使用其他事务不提交的变化作为计算的基础，然后那些未提交的变化被它们的父事务撤销，这就导致了大量的数据变化。（脏读：赌取的数据不准确，刚读取被回滚）
读已提交（READ COMMITTED）
处于 READ COMMITTED 级别的事务可以看到其他事务对数据的修改。也就是说，在事务处理期间，如果其他事务修改了相应的表，那么同一个事务的多个 SELECT 语句可能返回不同的结果。在一个事务内，能看到别的事务提交的数据（不可重复读：同一行数据多次查询结果不一致）
可重复读（REPEATABLE READ）默认
在可重复读在这一隔离级别上，事务不会被看成是一个序列。不过，当前正在执行事务的变化仍然不能被外部看到，也就是说，如果用户在另外一个事务中执行同条 SELECT 语句数次，结果总是相同的。（因为正在执行的事务所产生的数据变化不能被外部看到）。别的事务提交的数据，也看不到（幻读：事务过程中每次查询结果都是不变的，但是提交时会根据当前的实际数据来判断，例如执行时没有id=1的数据，但是事务B插入了id=1的数据，会导致，执行过程中每次查询都没有，但是提交时报错，这个东西简直日了狗，网上好多文档写的真差，误导我浪费时间）
串行化/序列化（SERIALIZABLE）
如果隔离级别为序列化，则用户之间通过一个接一个顺序地执行当前的事务，这种隔离级别提供了事务之间最大限度的隔离。

upload successful

InnoDB 引擎的行锁怎么实现的
通过给索引上的索引项加锁来实现的，若未使用索引则是表锁了
[SELECT *] 和[SELECT 全部字段]的 2 种写法有何优缺点

前者要解析数据字典，后者不需要
结果输出顺序，前者与建表列顺序相同，后者按指定字段顺序。
表字段改名，前者不需要修改，后者需要改
后者可以建立索引进行优化，前者无法优化
后者的可读性比前者要高

HAVNG 子句和 WHERE 的异同点
“Where” 是一个约束声明，使用Where来约束来自数据库的数据，Where是在结果返回之前起作用的，且Where中不能使用聚合函数。
“Having” 是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在Having中可以使用聚合函数
当记录不存在时 insert,当记录存在时 update
ON DUPLICATE KEY UPDATE c=
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY
UPDATE c=s
一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，再把 Mysql 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15 ？

如果表的类型是 MyISAM，那么是 18
因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里，重启 MySQL 自增主键的最大ID 也不会丢失
如果表的类型是 InnoDB，那么是 15
InnoDB 表只是把自增主键的最大 ID 记录到内存中，所以重启数据库或者是对表进行OPTIMIZE 操作，都会导致最大 ID 丢失

FLOAT 和 DOUBLE 的区别
浮点数以 8 位精度存储在 FLOAT 中，并且有四个字节。
浮点数存储在 DOUBLE 中，精度为 18 位，有八个字节
主键、外键、超键、候选键
超键(super key):在关系中能唯一标识元组的属性集称为关系模式的超键
候选键(candidate key):不含有多余属性的超键称为候选键
主键(primary key):用户选作元组标识的一个候选键程序主键
外键(foreign key)如果关系模式R1中的某属性集不是R1的主键，而是另一个关系R2的主键则该属性集是关系模式R1的外键
https://blog.51cto.com/u_15127563/4060512
列设置为 AUTO INCREMENT 时，如果在表中达到最大值，会发生什么情况
会停止递增，任何进一步的插入都将产生错误
查看索引
SHOW INDEX FROM DATATABLE;
mysql innoDB锁的类型
按照锁的粒度划分为表锁，页锁，行锁
按照对数据库中数据访问类型分为读锁以及写锁，也就是共享锁和排他锁
按照思想上的锁：悲观锁、乐观锁
死锁处理及避免

查看
show engine innodb status
处理
杀进程
避免
1、不同的应用访问同一组表时，应尽量约定以相同的顺序访问各表
2、在主键等值更新的时候，尽量先查询看数据库中有没有满足条件的数据，如果不存在就不用更新，存在才更新
3、尽量使用主键更新数据,因为主键是唯一索引，在等值查询能查到数据的情况下只会产生行锁，不会产生间隙锁，这样产生死锁的概率就减少了。当然如果是范围查询，一样会产生间隙锁
4、避免长事务，小事务发送锁冲突的几率也小
5、尽量使用RC的隔离级别

数据库事务的四个特性及含义

Atomicity:原子性
原子性确保数据库事务是一个不可分割的操作单元，要么全部执行成功，要么全部失败回滚。如果一个事务的任何部分失败，整个事务都会被回滚，数据库状态会回到事务开始之前的状态。
Consistency:一致性
一致性确保事务将数据库从一个一致状态转移到另一个一致状态。这意味着在执行事务过程中，数据库始终保持有效的状态。事务的执行不会破坏数据库的完整性约束、触发器或其他与数据一致性相关的规则。
Isolation:隔离性
隔离性确保同时运行的多个事务之间相互隔离，每个事务都不能看到其他事务的中间状态。这意味着并发执行的事务不会相互影响，每个事务都感觉就像在独立运行一样。这可以防止数据不一致和并发问题，例如脏读、不可重复读和幻读。
Durability:持久性
持久性确保一旦事务被提交，其对数据库的改变将是永久性的，即使系统发生故障或重启。即使在系统崩溃或断电的情况下，已提交的事务对数据库的改变也会被永久保存。
实现原理
https://blog.csdn.net/weixin_34620163/article/details/113478526

SQL 优化思路

应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
Where 子句替换 HAVING 子句因为 HAVING 只会在检索出所有记录之后才对结果集进行过滤
索引优化针对业务优化
数据库结构优化
1、范式优化：比如消除冗余（节省空间。。）
2、逆范式优化：比如适当加冗余等（减少 join）
3、拆分表：拆分又分垂直拆分和水平拆分（垂直拆分是把不同的表拆到不同的数据库中，而水平拆分是把同一个表拆到不同的数据库中）
4、服务器硬件优化（加钱买）

现实问题:

千万条数据的表, 如何分页查询
数据量过大的情况下, limit offset 分页会由于扫描数据太多而越往后查询越慢. 可以配合当前页最后一条ID进行查询, SELECT * FROM T WHERE id > #{ID} LIMIT #{LIMIT} . 当然, 这种情况下ID必须是有序的, 这也是有序ID的好处之一
订单表数据量越来越大导致查询缓慢, 如何处理
分库分表. 由于历史订单使用率并不高, 高频的可能只是近期订单, 因此, 将订单表按照时间进行拆分, 根据数据量的大小考虑按月分表或按年分表. 订单ID最好包含时间(如根据雪花算法生成), 此时既能根据订单ID直接获取到订单记录, 也能按照时间进行查询.
SQL 注入

原因
程序开发过程中不注意规范书写 sql 语句和对特殊字符进行过滤，导致客户端可以通过全局变量 POST 和 GET 提交一些 sql 语句正常执行
方案
开启配置文件中的 magic_quotes_gpc 和 magic_quotes_runtime 设置执行 sql 语句时使用 addslashes 进行 sql 语句转换Sql 语句书写尽量不要省略双引号和单引号。过滤掉 sql 语句中的一些关键词： update、insert、delete、select、 * 。提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的
我的方法
别写原生sql，用ORM

针对单个 sql 语句进行优化
explain 命令