MySql笔记整理

MySql 基础

MySql 引擎

MySQL 有多种存储引擎，每种存储引擎有各自的优缺点，可以择优选择使用：

MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

---

常用的MyISAM 和 InnoBD 区别

|

|   MyISAM

|   InnoDB

构成上的区别：

|   每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。

.frm文件存储表定义。

数据文件的扩展名为.MYD (MYData)。

索引文件的扩展名是.MYI (MYIndex)。

|   基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB
  |
|   事务处理上方面:

|   MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持

|   InnoDB提供事务支持事务，外部键（foreign key）等高级数据库功能

|
|   SELECT   UPDATE,INSERT，Delete操作
  |   如果执行大量的SELECT，MyISAM是更好的选择

|   1.如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表

2.DELETE   FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

3.LOAD   TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用

|
|   对AUTO_INCREMENT的操作

  |   每表一个AUTO_INCREMEN列的内部处理。

MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况）。

AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置

对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引

更好和更快的auto_increment处理

|   如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。

自动增长计数器仅被存储在主内存中，而不是存在磁盘上

关于该计算器的算法实现，请参考

AUTO_INCREMENT列在InnoDB里如何工作

|
|   表的具体行数
  |   select count() from table,MyISAM只要简单的读出保存好的行数，注意的是，当count()语句包含   where条件时，两种表的操作是一样的

|   InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行

|
|   锁
  |   表锁

|   提供行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in
  SELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表， 例如update table set num=1 where name like “%aaa%” |

下面我们分别来看两种存储引擎的区别。

• 一、InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
• 二、MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
• 三、InnoDB支持外键，MyISAM不支持
• 四、MyISAM是默认引擎，InnoDB需要指定
• 五、InnoDB不支持FULLTEXT类型的索引
• 六、InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表
• 七、对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引
• 八、清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
• 九、InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

---

MySql 三大范式

第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。
这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。
而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。

第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

---

MySql 优化

字段

• VARCHAR 的⻓度只分配真正需要的空间
• 使⽤枚举或整数代替字符串类型
• 尽量使⽤TIMESTAMP⽽⾮DATETIME（int类型占用4字节，datetime占用8字节，timestamp占用4字节）
• 单表不要有太多字段，建议在20以内
• 避免使⽤NULL字段，很难查询优化且占⽤额外索引空间
• ⽤整型来存IP(inet_aton(192.168.1.134) = )

字符串ip转数字（select inet_aton(‘192.168.1.134’) == 3232235910）
数字转ip(select inet_ntoa(3232235910) == 192.168.1.134)

---

索引

• 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建⽴索引，可根据EXPLAIN来查看是否⽤了索引还是全表扫描
• 应尽量避免在WHERE⼦句中对字段进⾏NULL值判断，否则将导致引擎放弃使⽤索引⽽进⾏全表扫描
• 值分布很稀少的字段不适合建索引，例如"性别"这种只有两三个值的字段
• 字符字段只建前缀索引
• 字符字段最好不要做主键
• 不⽤外键，由程序保证约束
• 尽量不⽤UNIQUE，由程序保证约束
• 使⽤多列索引时主意顺序和查询条件保持⼀致，同时删除不必要的单列索引

---

查询SQL

• 可通过开启慢查询⽇志来找出较慢的SQL
• 不做列运算：SELECT id WHERE age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移⾄等号右边
• sql语句尽可能简单：⼀条sql只能在⼀个cpu运算；⼤语句拆⼩语句，减少锁时间；⼀条⼤sql可以堵死整个库
• 不⽤SELECT *
• OR改写成IN：OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
• 不⽤函数和触发器，在应⽤程序实现
• 避免%xxx式查询
• 少⽤JOIN
• 使⽤同类型进⾏⽐较，⽐如⽤’123’和’123’⽐，123和123⽐
• 尽量避免在WHERE⼦句中使⽤!=或<>操作符，否则将引擎放弃使⽤索引⽽进⾏全表扫描
• 对于连续数值，使⽤BETWEEN不⽤IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
• 列表数据不要拿全表，要使⽤LIMIT来分⻚，每⻚数量也不要太⼤

---

MySql事务

事务介绍

定义

事务是由一步或几步数据库操作序列组成逻辑执行单元，这系列操作要么全部执行，要么全部放弃执行。

注意

1.在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
2.一段程序中可能包含多个事务。（说白了就是几步的数据库操作而构成的逻辑执行单元）

---

事务的特性

原子性（Atomicity）

事务是应用中最小的执行单位，就如原子是自然界最小颗粒，具有不可再分的特征一样。事务是应用中不可再分的最小逻辑执行体。（最小了，不可再分了）

一致性（Consistency）

一致性是说无论事务提交还是回滚，不会破坏数据的完整性。
A给B转钱，A 的账户-1000 元，如果成功了 B 的账户就要+1000 元，扣除的钱（-1000）和增加的钱（+1000）相加应该为0；如果失败了A和B的余额不会改变。

隔离性（Isolation）

各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都是隔离的。也就是说：并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间不能相互影响。（说白了，就是你做你的，我做我的！）

持续性（Durability）-- 有的称为永久性

事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

---

事务隔离级别

事务一致性术语解读（InnoDB默认是可重复读级别的）

• 脏读: 脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。（A:别跑呀，我还没有改完。B:我特么已经取走了，结果数据不对）

• 不可重复读:是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（A：我来拿数据啦；B：我要改数据；A：我又来读数据啦；结果：第二次读得数据不对。） – 针对数据修改

• 幻读:第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样，幻读是数据行记录变多了或者少了。（A：我要修改所有数据啦；B：我来添加/删除数据啦；A：我去怎么还有没有被修改到数据；原来幻读了~）-- 针对数据新增

事务隔离性术语解读

• read uncommitted 未提交读 – 所有事务都可以看到没有提交事务的数据

• 事物A和事物B，事物A未提交的数据，事物B可以读取到
  - 这里读取到的数据叫做“脏数据”
  - 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别
  

• read committed 提交读 – 事务成功提交后才可以被查询到

• 事物A和事物B，事物A提交的数据，事物B才能读取到
  - 这种隔离级别高于读未提交
  - 换句话说，对方事物提交之后的数据，我当前事物才能读取到
  - 这种级别可以避免“脏数据”
  - 这种隔离级别会导致“不可重复读取”
  - Oracle默认隔离级别
  

• repeatable 重复读 – 同一个事务内多次查询却返回了不同的数据值，即 可能将未提交的记录查询出来，而出现幻读。注：Mysql的默认隔离级别就是Repeatable read

• 事务A和事务B，事务A提交之后的数据，事务B读取不到
  - 事务B是可重复读取数据
  - 这种隔离级别高于读已提交
  - 换句话说，对方提交之后的数据，我还是读取不到
  - 这种隔离级别可以避免“不可重复读取”，达到可重复读取
  - 比如1点和2点读到数据是同一个
  - MySQL默认级别
  - 虽然可以达到可重复读取，但是会导致“幻像读”
  

• serializable 可串行化 – 强制的进行排序，在每个读读数据行上添加共享锁。会导致大量超时现象和锁竞争。这是花费最高代价但是最可靠的事务隔离级别。事务被处理为顺序执行。除了防止脏读，不可重复读外，还避免了幻读。

• 事务A和事务B，事务A在操作数据库时，事务B只能排队等待
  - 这种隔离级别很少使用，吞吐量太低，用户体验差
  - 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发
  

隔离级别和一次性的关系

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

---

索引

索引介绍

举例说明

假设我们有一个数据库表Employee，这个表分别有三个字段：name，age，address。假设表中有1000条记录。假如没有使用索引，当我们查询名为“Jesus”的雇员的时候，即调用：

select name,age,address from Employee where name = 'Jesus';

此时数据库不得不在Employee表中对这1000条记录一条一条的进行判断name字段是否为“Jesus”。这也就是所谓的全表扫描。而当我们在Employee表上的name字段上创建索引时，当我们查询名为“Jesus”的雇员时，会通过索引查找去查询名为“Jesus”的雇员，因为该索引已经按照字母顺序排列，因此要查找名为“Jesus”的记录时会快很多，因为名字首字母为“J”的雇员都是排列在一起的。通过该索引，能获取到表中对应的记录。

定义

索引是为了快速查询的一种排好序的数据结构，通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件。索引的功能就是加速查询；primary key，unique索引还有约束功能。

优缺点：

• 优点
  • 加快数据的检索速度
• 缺点
  • 维护索引需要耗费时间
  • 索引需要占用硬盘空间
• 索引覆盖
  • 查询的所有字段，在索引中都包含了，说明索引覆盖到了所有需要的信息，不需要回表查询。

---

索引的数据结构

Hash索引

主要就是通过Hash算法，将索引转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。
InnoDB使用的索引结构是B+树，但其实它还支持另一种索引：自适应哈希索引。
哈希表是数组+链表的形式。通过哈希函数计算每个节点数据中键所对应的哈希桶位置，如果出现哈希冲突，就使用拉链法来解决。
哈希表查找最优情况下是查找一次.而InnoDB使用的是B+树，最优情况下的查找次数根据层数决定。因此为了提高查询效率，InnoDB便允许使用自适应哈希来提高性能。
可以通过参数 innodb_adaptive_hash_index 来决定是否开启。默认是打开的。
hash索引仅支持等值查询。

二叉树索引

查找Col2为3的行，没有创建索引全表扫描需要扫描7次。创建二叉树索引34-5-3扫描3次。创建二叉树（普通二叉树、平衡二叉树AVL、红黑树）索引，能够提高一定的查询效率，但是如果数据量大，树的高度就会很高，表现为“瘦高”，查找数据会经过很多次磁盘IO，会影响查询效率。
创建二叉树的过程：

1. 根节点插入34
2. 77比34大，成为34的右子树
3. 5比34小，成为34的左子树
4. 91比34大，走右边，91比77大，成为77的右子树
5. 22比34小，走左边，22比5大，成为5的右子树
6. 59比77大，走右边，59比77小，成为77的左子树
7. 3比34小，走左边，3比5小，成为5的左子树

但是普通的二叉查找树如果是顺序插入的，那么会演变成线性表

B数索引

• 定义

B树，这里的B表示balance（平衡），B树是一种多路自平衡的搜索树，它类似普通的平衡二叉树，不同的一点是B树允许每个节点有更多的子节点。B树，每层所容纳的节点比二叉树更多，树的高度相对二叉树更低，表现为比二叉树更矮胖，搜索效果比二叉树更好。

• 介绍

以4叉树为例，插入C N G A H E K Q M F W L T Z D P R X Y S。n>4时，中间节点分裂到父节点，两边节点分裂


I.插入前4个字母 C N G A
a. 插入C；
b. N在C后，C后插入N；
c. G介于C和N之间，插入G；
d. A在C前，插入A


II. 插入 H
H介于G和N之间，插入H，n>4中间元素G向上分裂到新的节点
III. 插入 E，K，Q不需要分裂
a. E在G之前，C之后，插入E；
b. K在G之后，介于H和N之间，插入K；
c. Q在G之后，N之后，插入Q


IV.插入 M
M在G之后，介于K和N之间，插入M，n>4，中间元素M向上分裂到父节点
V.插入 F，W，L，T不需要分裂
a. F在G之前，E之后，插入F;
b. W在M之后，Q之后，插入W;
c. L在G和M之间，在K之后，插入L；
d. T在M之后，在Q和W之间，插入T
VI.插入Z
Z在M之后，W之后，插入Z，n>4，中间元素T向上分裂到父节点中
VII.插入D P R X Y
a. D在G之前，在C和E之间，插入D，N>4，中间元素D向上分裂到父节点中；
b. 插入P R X Y不需要分裂
VII.插入S
S在M和T之间，在R之后，插入S，n>4，中间元素Q向上分裂到父节点中，插入到M和T之间，此时n>4，中间元素M向上分裂到父节点中

• 弊端

B树每个节点都存放索引和数据，非叶子节点还存放了搜索方向的指针。因此带来增加IO的访问
我们来看看B-树的查找，假设每个节点有 n 个 key值，被分割为 n+1 个区间，注意，每个 key 值紧跟着 data 域，这说明B-树的 key 和 data 是聚合在一起的。一般而言，根节点都在内存中，B-树以每个节点为一次磁盘 IO，比如上图中，若搜索 key 为 25 节点的 data，首先在根节点进行二分查找（因为 keys 有序，二分最快），判断 key 25 小于 key 50，所以定位到最左侧的节点，此时进行一次磁盘 IO，将该节点从磁盘读入内存，接着继续进行上述过程，直到找到该 key 为止。

B+树

B树索引在每个节点都存放了数据项，会使每页所能存储的索引不多，而B+树把数据放在叶子节点（主键索引的情况），非叶子节点不存放数据，每页能够存放更多的索引，比B树表现为更加“矮胖”，磁盘IO更少，更加有利于查询。
innodb底层使用B+树实现，B+树一个节点默认的大小为16kb
如下图，是一颗b+树，可以看到每个磁盘块包含几个数据项和指针，如磁盘块1包含数据项17和35，包含指针P1、P2，P1表示小于28的磁盘块，P2表示大于28的磁盘块。真实的数据存在于叶子节点即3、5、9、10、12、13、15、16、19、26、29、31、32、35、39、60、65、75、81、87、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如28、11并不真实存在于数据表中。
如果要寻找索引为26的数据，首先会加载磁盘块1的内容到内存，此时发送一次IO，在内存中用二分查找确定26在28左边，锁定磁盘块1中的p1指针，然后把磁盘块2加载到内存中，发生第二次IO，26比17大，锁定磁盘块2中的p3指针，加载磁盘块6到内存，发送第三次IO，二分查找找到26，结束查询。

B树 和 B+树的比较

B+树提高区间访问能力

B+树的非叶子节点用来做索引，叶子节点存放数据，而叶子节点中有双向链表，这个相对于B树优化的目的是提高区间访问性能。（说白了就是请求某个范围的时候，B+树叶子节点是一个双向链表，访问区间可以顺着指针往下请求，带来便利性）

减少磁盘IO

因为访问树中一个结点就是一次磁盘IO，B树非叶结点中记录了数据信息，而B+树非叶结点中没有记录数据信息，记录了更多的索引信息，使树的高度更低，减少磁盘IO的次数。（也就是说B树边查找边查找数据，而B+树则是查找完了在树节点IO查数据）

---