胡咧咧之MySQL

MySQL基础知识

三大范式：

• 每个列都不可以再拆分。（例如姓名只能有一个，不可拆分）
  
• 在第一范式基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。（一个表只描述一件事情）

1、皮肤价格依赖于皮肤，英雄价格依赖于英雄。不满足非主键完全依赖于主键。

拆分之后：第一种满足第二范式，第二种通过添加皮肤id，让其他逐渐完全依赖于id

2、不同时间下了相同的单，所以主键必须是“订单号”+“产品号” ，订单时间和订单金额不完全依赖于订单号

• 在第二范式基础上，非主键列只能依赖于主键，不依赖于其他非主键（消除传递依赖）。
  （每一列数与域主键直接相关，不能是间接相关，之间必须相互独立，不存在其他函数关系）
  
  规范性和性能
  关联查询的表不得超过三张表
• 规范性
• 故意增加一些冗余的字段（多表查询变为单表查询）
• 故意增加一些计算列（几百万数据从大数据量降低为小数据量）

数据类型

• 整数类型
  包括：tinyint（1字节 极小的）、smallint（2字节 小的）、mediumint（3字节 中等的）、int(4字节)、bigint(8字节)
  加上unsigned修饰，表示数据是无符号的，即非负整数。
  int(5) 属性为unsigned、zerofill（补零）：插入12，数据库实际存储数据为00012
• 实数类型
  float、double、decimal
  decimal:压缩严格的定点数（存储比bigint还大的整数，能存储精确的小数）
  float、double 比 decimal 效率高，decimal理解为字符串处理

java中3*0.1==0.3将返回fale：因为浮点数不能完全精确的表示出来，一般会损失精度。

• 字符串类型
  varchar、char、text、blob
  varchar：可变字符串，必定长类型更加节省空间
  使用额外1/2字节存储字符串长度（小于255 使用1字节表示）
  超出设置长度，内容会被截断
  char：定长，根据字符串长度分配内存
  根据需要填充空格方便比较
  适合存储较短的字符串/所有值都接近一个长度
  存储内存超过设长，内容会被截断
  使用策略：
  经常变更，char更好，因为不易产生碎片
  非常短的列，char在存储空间上更有效率
  只分配需要的空间，更长的排序时会消耗更多的内存
  尽量避免使用text/blob，查询时会使用临时表，导致严重性能开销
• 枚举类型：把不重复的数据存储为预定义的集合
  有时可以enum代替字符串类型
  enum存储非常紧凑，会把列表值压缩到1-2字节
  内部存储时存的是整数，所以尽量避免数字作为enum的常量，会造成混乱
  排序使用的内部的整数
• 日期和时间类型
  尽量使用timestamp，空间效率高于datetime，
  用整数保存时间戳通常不方便处理。
  如果需要存储微妙，可以使用bigint存储。
  year YYYY 1901~2155
  time HH:MM:SS -838:59:59~838:59:59
  date YYYY-MM-DD 1000-01-01~9999-12-3
  datetime YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
  timestamp YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC

引擎

InnoDB:提供对数据库的ACID事务支持。提供了行级锁和外键约束。处理大数据容量的数据库系统
存储结构：所有的表存在有一个数据文件中
存储空间：需要更多的内存和存储，会在内存中建立专用的缓冲池，用于高速缓存数据和索引

文件格式：索引和数据是集中存储的，.ibd

存储顺序：按主键大小有序插入

外键：支持
事务：支持

锁支持：默认行级锁，支持表级锁，锁定力度小并发能力高

insert、update、delect：更优

索引
实现方式：B+树索引，索引组织表，
哈希索引：支持
全文索引：不支持
聚簇索引，主键索引的叶子节点存储的是行数据，主键索引非常高效、非主键索引的叶子节点存储的是主键和其他索引的列数据，查询时做到覆盖索引会非常高效
MyISAM:（原MySQL的默认存储引擎）不提供事务，不支持行级锁和外键
存储结构：每张表存放在三个文件 frm 表定义、MYD 数据文件、MYI 索引文件
存储空间：可被压缩，存储空间小
文件格式：数据和存储是分开存储

存储顺序：按记录插入顺序保存

外键：不支持
事务：不支持

锁（避免资源争用的机制）支持：表锁定

select：更优
select count(*):更快，内部维护了一个计数器，可直接调取

索引
实现方式：B+树索引，堆表，
哈希索引：不支持
全文索引：支持
非聚簇索引，索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据
Memory：数据在内存中，处理速度快，安全性不高
InnoDB四大特征：插入缓存、二次写、自适应哈希索引、预读
存储引擎选择：
没有特殊需求，默认InnoDB即可
MyISAM：以读写插入为主的应用程序，例如博客、新闻门户
InnoDB：更新（删除）操作频率高，需要保证数据完整性；并发量高，支持事务和外键；例OA自动化办公系统

索引（数据库排序的数据结构，相当于目录，是一个文件）

**缺点：**创建索引和维护索引需要时间，对表进行增加、删除、修改的时候，索引也要动态维护，而且需要占用物理内存

创建索引方式：

• create table 时创建：
  

• 使用alter table 去增加索引
  alter table 表名 add index 索引名（字段名）；

• 使用create index命令创建，增加普通索引或唯一索引，不能创建主键索引
  create index 表名 on 索引名（column）
  删除索引：
  alter table 表名 drop key 索引名

索引类型：

• 主键索引：数据列不允许重复，不允许null，PRIMARY KEY(主键);
• 唯一索引：数据列不允许重复，不允许null，alter table 表名 add unique（column）/unique（column,column2）唯一联合索引
• 普通索引：允许重复，可以为null，alter table 表名 add index 索引名（索引字段）/index 索引名（字段名1，字段名2）普通组合索引
• 全文索引：搜索引擎关键技术，alter table 表名 add 索引名（字段名）

使用场景（explain）：

• where ：仅建立主键索引，可选索引只有主键索引，如果有多个会选择较优的作为检索的依据
• order by：排序时，如果没有建立索引，会使用外部排序，很耗性能。建立索引：alter table 表名 add index（字段名），索引本身是有序的，直接按照索引的顺序和映射关系逐条取出数据。
  如果有分页，只需要取出索引表某个范围内的索引对应数据
• join：
• 索引覆盖：如果查询的字段都建立过索引，引擎直接会在索引表中查询而不会访问原始数据，否则只要有一个字段未建立索引就会做全表扫描。
  因此select 后只写必要的查询字段，增加覆盖索引的几率

索引的数据结构：

• hash索引：底层树结构为哈希表，绝大多数需求为单条记录查询时，可以选择hash索引，查询性能快
  

• B+树索引（InnoDB默认实现）：大部分场景适用于BTree索引
  
  主键索引区：PI（关联保存的实时数据地址）按主键查询
  普通索引区：si（关联id的地址，然后在到达上图地址）所以按照主键查询，速度最快

B+tree性质：
1、n颗子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引
2、所有的叶子节点包含了全部关键字信息，及指向关键字记录的指针，叶子节点本身依关键字的大小自小而大顺序链接
3、所有非终端结点可以看成索引部分，结点仅包含子树中最大（或最小）关键字
4、数据插入和删除仅在叶子节点进行
5、有两个头指针，一个是树的根节点，一个是 最小关键码的叶节点

创建索引的原则：
1、最左前缀匹配原则，mysql一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配
例如： a = 1 and b = 2 and c > 3 and d = 4 如果建立（a,b,c,d） 顺序的索引，d用不到索引，如果建立（a,b,d,c）的索引则都可以用到，因为abd顺序可以调整
2、较为频繁作为查询条件的字段才去建立索引
3、更新频繁的字段不适合建立索引
4、不能有效区分数据的列不适合做索引列（性别）
5、尽量的拓展索引，不要新建索引。
例如表中已有a的索引，要加（a，b）索引，只需要修改原来的索引即可
6、定义有外键的数据列一定要建立索引
7、对于查询中很少涉及的列，重复值比较多的不要建立索引
8、定义为text、image和bit的数据类型的列不要建立索引

百万级别或以上的数据如何删除：
MySQL官方手册告诉我们删除数据的速度和创建索引的速度是成正比的。
1、先删除索引
2、删除无用数据
3、重新创建索引
比之前删除绝对快，之前的删除中断，会回滚删除

事务

ACID 四大特征：一致性，要么做，要么不做

• 原子性A：最小执行单位，不允许分割。确保动作要么全部完成，要么全部不起作用。
• 一致性C：执行事务前后，数据保持一致。（多个事务读取数据结果一致）
• 隔离性I：一个事务不被其他事务干扰，各并发事务之间数据库是独立的
• 持久性D：一个事务提交后，对数据库中数据改变是持久的，即使数据库发生故障也不应该对其有任何影响。

脏读、幻读、不可重复读：

• 脏读：A事务update数据，B事务读取了同一份数据。A事务RollBack后，B事务读取的不正确。
• 不可重复读：B事务两次读取数据不一致，中间 A事务update数据并且commit了
• 幻读：后来读取到的数据比第一次读取到的条目多。A事务查询了N条数据，B事务此时插入了N条数据之外的M条或者添加了M条符合A搜索条件的数据，导致A事务再次查询发现N+M条数据，（并且插入相同数据失败。）

不可重复读与幻读比较：前者争对update、delete，后者争对insert

事务的隔离级别：

• 读取未提交 READ-UNCOMMITTED；最低的隔离级别，允许读取尚未提交的数据变更，会导致脏读、幻读、不可重复读
• 读取已提交 READ-COMMITTED（Oracle默认）：允许读取并发事务已经提交的数据，会产生幻读和不可重复读
• 可重复读 REPEATABLE-READ（Mysql默认）：多次读取结果一致，会产生幻读
• 可串行化 SERIALIZABLE： 完全符合ACID，事务逐个执行互不干扰

锁

对ＭｙＳＱＬ锁了解吗？
并发事务可能会产生数据不一致，需要锁机制来保证访问次序。
就比如酒店房间，给房间上锁，拿到钥匙的人才能入住并且锁起来，其他人只能到到使用完毕才能再次申请使用。
隔离级别与锁的关系：
Read Uncommitted级别下，读取数据不需要加共享锁，不会跟被修改的数据的排他锁冲突
Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；
Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。
SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

按照锁的粒度分数据库锁有哪儿些？行级锁、表级锁、页级锁

开销从大到小，并发能力从大到小

• 行级锁：（InnoDB引擎默认，同时支持表级锁）粒度最细，表示只针对当前操作进行加锁　
  行级锁分为 共享锁和排他锁
  特点：开销大，加锁慢，锁定粒度最小，或出现死锁，发生锁冲突的概率最低，并发度最高

• 页级锁：（BDB引擎）锁定粒度介于行级锁和表级锁中间的一种锁。
  特点：开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度界于行级锁和表级锁之间，并发能力一般

• 表级锁：（MyISAM引擎）粒度最大的锁，对当前操作的整张表加锁
  表级锁分为：表共享读锁（共享锁）、表独占写锁（排他锁）
  特点：开销小，加锁快，不会出现死锁，锁粒度大，发生冲突概论最高，并发度最低

从锁的类别来讲分为：共享锁、排他锁
-　共享锁（读锁）：当用户要进行数据读取时，对数据加上共享锁。可以同时加上多个

-　排他锁（写锁）：当用户要进行数据的写入时，对数据加上排他锁。只可以加一个，和其他排他锁、共享锁都排斥

酒店来举例的话，多个用户一起看房可以接受，但是入住了以后，住房和看房的都不可以了。

MySQL中的InnoDB引擎的行锁是怎么实现的？
InnoDB基于索引来完成行锁：select * from tab_with_index where id=1 for update；
for update可以根据条件来完成行锁 ，并且id是有索引键的列

什么是死锁？
两个或多个事务在同一资源上互相占用，并请求锁定对方的资源。

死锁解决办法：

• 不同程序并发存取多个表，尽量约定以相同的顺序访问表
• 同一个事物，尽可能做到一次锁定所需要的所有资源
• 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁概率

乐观锁和悲观锁？

• 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。查询完数据的时候就把事务锁起来，直到事务提交。
  实现方式：数据库锁机制
• 乐观锁：假定不会发生冲突，只在提交操作时检查是否违反数据库完整性 。再修改数据的时候把事务锁起来，通过version的方式锁定。
  实现方式：使用版本号机制或CAS算法
  使用场景：
  乐观锁适用于写比较少情况，即冲突很少发生。
  悲观锁适用于多写的情况，即冲突经常发生。

视图

存储过程与函数

触发器

SQL优化

谈谈explain
对于查询语句，最重要的优化方式就是使用索引。