Mysql高级篇1

Mysql存储引擎

MyISAM

不支持事物，不支持外键，索引采用非聚集索引

优势：访问速度快，对事物完整性没有要求

以select和insert为主的应用基本上都可以使用这个存储引擎来创建表

MyISAM的表在磁盘存储为3个文件：

.frm(表的结构)   .MYD(存储数据)   .MYI(存储索引)

InnoDB

提供具有提交，回滚和崩溃恢复能力的事物安全，支持自增列，外键等

索引采用聚集索引，索引和数据存储在同一个文件

InnoDB的表在磁盘上有2个文件：

.frm(存储表结构)    .ibd(存储数据和索引)

MyISAM：支持表锁，支持B+树索引，支持索引缓存   

InnoDB：支持行锁，支持B+树索引，支持外键，支持事物，支持索引缓存，支持数据缓存

锁机制：表示数据库在并发请求访问时，多个事物在操作时，并发操作的粒度

B+树索引：加快SQL的查询速度

索引缓存和数据缓存：和MySQL Server的查询缓存相关，在没有对数据和索引做修改之前，重复查询可以不用进行磁盘IO，提升数据库的访问效率，读取上一次内存中查询的缓存就可以了

MySQL索引

索引也是需要存储成索引文件，因此对索引的使用也会涉及磁盘I/O操作

索引创建过多，使用不当，会造成SQL查询，进行大量无用的磁盘I/O操作，降低SQL的查询效率

索引的核心是：提高查询速度

索引的缺点：索引并非越多越好，过多的索引会导致CPU使用率居高不下，由于数据的改变，会造成索引文件的改动，过多的磁盘I/O，会浪费CPU资源

索引分类

 1.普通索引：没有任何限制条件，可以给任何字段创建普通索引，数量不限，一张表的一次sql查询只能用一个索引字段

2.唯一性索引：使用unique修饰的字段，值不能够重复，但允许在唯一索引字段中添加多个NULL值

3.主键索引：使用primary key修饰的字段会自动创建索引（MyISAM，InnoDB）

4.单列索引：在一个字段上创建索引

5.多列索引：在表的多个字段上创建索引（uid+cid，多列索引必须使用到第一个列，才能用到索引，否则索引用不上）

索引创建和删除

创建表时指定索引字段：

create table student
(
    id     INT,
    name   VARCHAR(20),
    sex    ENUM('male','female'),
    INDEX(id,name)  -- 添加索引
);

在已经创建的表上添加索引：

create [unique] index 字段名 on 表名;

删除索引：

drop index 字段名 on 表名;

1.经常作为where过滤条件的字段加索引

2.字符串列创建索引时，尽量规定索引长度，而不能让索引值的长度过长

3.索引字段涉及类型强转，mysql函数调用，表达式计算等，索引就用不上了

explain查看执行计划

using index：表示直接从索引树上查询到结果，不需要回表

select_type

simple：表示不须要union操作或者不包含子查询的select语句

primary：一个需要union操作或者含有子查询的select

union：union连接的两个select查询

union result：包含union的结果集

table

显示查询的表名

如果不涉及对数据库操作，显示null

type

const：使用唯一性索引或者主键，返回记录一定是1行记录的等值where条件时，通常是const

ref：常见于辅助索引的等值查找；或者多列主键，唯一索引中，使用第一个列之外的列作为等值查找会出现；返回数据不唯一的等值查找也会出现

range：索引范围搜索，常见于使用<，>，is null，between，in，like等运算符的查询中

index：索引全表扫描，常见于使用索引列就可以处理不需要读取数据文 件的查询，可以使用索引排序或者分组的查询

all：全表扫描数据文件，然后在server层进行过滤返回符合要求的记录

Extra

using filesort：排序时无法用到索引，常见于order by和group by语句中

using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据

索引的底层实现原理

数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一块磁盘块（对应索引树的结点），索引树越低，磁盘IO次数就越少

MySQL支持两种索引，B+树索引和哈希索引

B-树

B-树是m阶平衡树，叶子结点都在同一层，由于每一个结点存储的数据量比较大，所以整个B-树的层数比较低

由于磁盘的读取是按块操作的，内存也是按页面操作的，因此B-树的结点大小一般设置为和磁盘块大小一致，这样一个B-树结点，就可以通过一次磁盘IO把一个磁盘块的数据全部存储下来，所以当使用B-树存储索引时，磁盘IO的操作次数最少，MySQL的读写效率，主要集中在磁盘IO上

B-树的缺点：

1）每个结点中有索引，也有数据，但是每一个结点的存储空间是有限的，如果数据较大时，会导致每个结点能存储的索引很少

2）当存储的数据量很大时，同样会导致B-树的高度较大，磁盘IO次数花费增大，效率较低

B+树

MySQL最终采取B+树存储索引结构的原因：

1）B-树的每一个结点，存了关键字和对应的数据地址(或者数据本身)，而B+树的非叶子结点只存储索引值，不存储对应的数据地址或者（数据本身），一个非叶子结点能存储更多的索引信息，B+树的高度要低于B-树，使用的磁盘IO次数少，查询会更快一点

2）B-树由于每个结点都存储了索引和对应的数据地址或者(数据本身)，距离根节点近的数据，查询的快，距离根节点远的数据，查询的慢；B+树所有的数据都存在了叶子节点上，B+树上使用索引查询数据，找到对应数据的时间比较平均的，没有快慢之分

3）在B-树上如果做区间查找，遍历的结点非常多；B+树的叶子结点被连接成了有序的双向链表，因此做整表遍历或者区间查找非常容易

MyISAM  --- 非聚集索引

主键索引：MyISAM存储引擎使用B+树作为索引结构，叶子节点的data域存放的是数据记录的地址

在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复

使用索引查询数据记录时，会在B+Tree上搜索索引，索引存在，取出其data域的值，然后以data域的值为地址，读取相应数据记录

MyISAM存储引擎的索引关键字和数据没有在一起存放，体现在磁盘上，索引在一个文件存储，数据在另一个文件存储，.frm(表结构)     .MYD(表的数据文件)    .MYI(表的索引文件)

InnDB --- 聚集索引

主键索引

InnoDB存储的主键索引，叶子节点中，索引关键字和数据是一起存放的

辅助索引

 InnoDB的辅助索引，叶子节点存放的是索引关键字和对应的主键

辅助索引的B+树，先根据辅助索引值找到对应的主键，再去主键树上找到对应的行记录数据

InnoDB的索引关键字和数据都是在一起存放的，体现在磁盘上，.frm(表结构)   .ibd(数据+索引)

InnoDB的索引树，叶子点包含了完整的数据记录，InnDB的数据文件本身要按照主键索引聚集，所引InnDB要求表必须要有主键，没有显示指定，则MySQL系统选择一个合适的列作为主键，如果不存在，则自动生成一个隐含字段作为主键，这个字段长6个字节，类型为长整型

MySQL事务

事物：由一条或者多条对数据库操作的SQL语句所组成的一个不可分割的单元，要么全部执行成功，要么全部执行失败

事物的所有SQL语句全部执行成功，才能提交事物，把结果写回磁盘上；

事物执行过程中，有的SQL出现错误，那么事物必须回滚到最初的错误；

事物的特点

1. 事物的原子性

2. 事物的一致性 （一个事物执行之前和执行之后，数据库数据必须保持一致）

3. 事物的隔离性（多个事务并发执行时，为了保证数据的安全性，将一个事物内部的操作与其它事务的操作隔离起来，不被其它正在执行的事务所看到，使得并发执行的各个事务之间不能互相影响）

4. 事物的持久性（事务完成以后，保证它对数据库中的数据的修改是永久性的）

事物并发存在的问题

脏读：一个事物读取了另一个事物未提交的数据

不可重复读：一个事务的操作导致另一个事务前后两次读取到不同的数据，事物B读取了事物A已提交的数据 

幻读：一个事务的操作导致另一个事务前后两次查询的结果数据量不同，事物B读取了事物A新增加的数据或者读不到事物A删除的数据

事物隔离级别

MySQL支持的四种隔离级别：

TRANSACTION_READ_UNCOMMITTED 未提交读，允许脏读，允许不可重复读，允许幻读

TRANSACTION_READ_COMMITTED  已提交读，不允许脏读，允许不可重复读，允许幻读

TRANSACTION_REPEATABLE_READ  可重复读，不允许脏读，允许幻读

TRANSACTION_SERIALIZABLE  串行化，防止脏读，不可重复度，幻读

注：事物隔离级别越高，为避免冲突所花费的性能也越多

在“可重复读”级别，实际上可以解决部分的虚读问题，但是不能防止update更新产生的虚读问题，要禁止虚读产生，还是需要设置串行化隔离级别。

MySQL默认工作在可重复读级别