MySQL知识

数据库相关

MYSQL的四种引擎

1、MyISAM

​ MyIsam引擎不支持事务、不支持外键、不支持细粒度的锁(行锁)，优势是访问速度快，在高并发的环境下可能会遇到瓶颈。适合对事物的完整性没有要求或主要业务操作以select、insert为主的应用再创建表时使用。MyIsam表中自动存储了表的行数，可以直接获取。

​ 支持3中不同的存储格式：静态表、动态表、压缩表

​ 静态表：表中的字段都是非变长字段，这样每个记录都是固定的长度，优点是存储速度快，容易缓存、出故障时容易恢复；缺点是占用空间往往比动态表多(因为存储时会按照列的长度定义补足的空格) ps: 在取数据的时候，默认会把字段后面的空格去掉，如果不注意会将数据本身带的空格也会忽略。

​ 动态表：记录不是固定的长度，这样存储的优点是占用的空间相比静态表要少，但是复杂度更高，每条记录都有一个header用于表明该记录的长度。如果记录为空，那么header的值为0，此时不向磁盘进行存储。缺点：频繁的更新、删除数据容易产生碎片。需要定期执行OPYIMIZE TABLE或者myisamchk-r命令来改善性能。ps：如果表中存在varchar、blob、text字段，那么此表为动态表。

​ 压缩表：只读，使用很少的空间，因为每个记录都是被单独压缩的，不能同时访问，所以只有非常小的访问开支。ps：可以将静态表和动态表进行压缩。

2、InnoDB

​ InnoDB是一个事务型存储引擎，提供了对数据量ACID事务的支持，并实现了SQL标准的四种隔离级别支持行锁和外键，支持自动增加列。ps：支持行锁说明锁的粒度小，在写数据时，不需要锁住整个表，因此适用于高并发场景。所有数据库引擎中只有InnoDB支持外键。

​ 缺点：MySql5.6前InnoDB不支持FullText类型的索引,5.6以后支持。没有保存表的行数，在执行select count(*) from 语句时需要遍历扫描全表。

​ 补充：InnoDB引擎中有页(page)的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认的每个页的大小为16KB，可通过参数innodb_page_size设置页的大小，可设置为4k、8k、16k。在mysql服务中可以通过如下命令查看页的大小：mysql> show variables like ‘innodb_page_size’;

3、Memory

​ Memory存储引起使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为他的数据保存在内存中，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失。Memory存储引擎的表可以选择使用BTREE索引或者HASH索引，两种索引存在不同的使用范围。

​ HASH索引：由于HASH索引结构的特殊性，其检索效率非常高，索引的检索能够一次定位，不用像BTREE索引需要从根节点到枝节点，最后才难呢过访问到页节点这样多次IO操作，所以HASH索引的查询效率比BTREE高。但是HASH算法是基于等值计算的，所以不精确查找的操作，例如“like”等范围查找hash索引无效，不支持。

4、MERGE

​ Merge存储引擎是一组MyIsam表的组合，这些MyISam表必须结构完全相同，merge表并没有数据，对merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。

MyISAM 与 InnoDB 的区别

1、存储结构

​ MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为 .MYD(MYData)。索引文件的扩展名为 .MYI(MYIndex)

​ InnoDB：所有的表都保存在同一个数据文件中(也可能是多个文件，或者是独立的表空间文件)，InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

2、存储空间

​ MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，数据末尾不能有空格会被去掉)、动态表、压缩表。

​ InnoDB：需要更多的内存和存储空间，它会在主存中建立其专用的缓冲池用于高速缓存数据和索引。

3、可移植性、备份和恢复

​ MyISAM：数据通过文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

​ InnoDB：拷贝数据文件、备份binlog，或者使用mysqldump，当数据量大时相对麻烦。

4、事物支持

​ MyISAM：强调的是性能，每次查询都具有原子性，其执行速度比InnoDB更快，但不支持事物。

​ InnoDB：提供事物支持，外键等高级数据库功能。具有事物的提交和回滚的崩溃修复能力。

5、AUTO_INCREMENT

​ MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，它可以根据前面几列进行排序后递增。

​ InnoDB：InnoDB中必须包含只有该字段的索引，引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

6、表锁差异

​ MyISAM：只支持表级别的锁。用户在操作myisam表时，select、update、delete、insert等语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新数据。

​ InnoDB：支持事物和行级别锁。行锁大幅度提高了多用户并发操作的性能。但是InnoDB的行锁，只是在WHERE条件中主键为筛选条件时有效，非主键的WHERE还是会锁全表。

7、全文检索

​ MyISAM：支持FULLTEXT类型的全文检索。

​ InnoDB：不支持FULLTEXT类型的全文检索，但是InnoDB可以使用sphinx插件支持全文检索。

8、表主键

​ MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。

​ InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)。

9、表的具体行数

​ MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出该值

​ InniDB：没有保存表的总行数，如果使用select count(*) from table;就会遍历整个表进行计数。

10、CRUD操作

​ MyISAM：如果执行大量的select操作，MyISAM是更好的选择。

​ InnoDB：如果你的数据执行大量的Insert或update操作，处于性能考虑应该使用InnoDB。Delete从性能上InnoDB更好。但是delete from table,InnoDB不会重新建表，而是一行一行的删除，在InnoDB上如果要清空保存有大量数据的表，最好执行truncate table的命令。

Mysql删除数据表的三种方式

1.使用方式

​ 当你不再需要该表时，用drop。

​ 当你仍要保留该表，但是要删除所有记录时，用truncate。

​ 当你要删除部分记录单有可能进行数据回滚的话，用delete。

2.删除强度分析

​ drop table 表名；

​ drop是直接将表格删除，无法找回。

​ truncate 表名；

​ truncate是删除表中的所有数据，但不能与where一起使用。

​ delete from 表名 where …;

​ delete删除表中的数据，可以与where一起使用，通过where指定条件，删除特定行。

3. truncate 与 delete 的区别

​ (1) 事物

​ truncate删除后不记录mysql日志，因此操作执行后不能回滚，不能恢复数据。delete可以回滚。

​ 原因：truncate相当于保留原mysql表的结果，相当于重新创建了这个表，所有的状态都是新的，而 delete的效果相当于一行一行的删除，所以可以rollback。

​ (2)效果

​ 效率上truncate比delete块，而且truncate在删除后将重建索引(新插入的数据id从0开始记起)，而 delete不会删除索引(新插入的数据在删除数据的索引后继续增加)

​ (3) truncate 不会触发任何 delete 的触发器

​ (4) 返回值

​ delete操作后返回删除的记录数，而truncate返回的是 0 或 -1(成功则返回0，失败返回-1)

​ PS： delete 和 delete from 之间的区别

​ 单表操作无区别，涉及到子查询时必须加from

B-Tree

​ B-Tree是为了磁盘等外存存储设备设计的一种平衡查找树。由于关键字key在整个数据结构中是从左到右按照升序进行排序的，所以在进行范围查询时效率高。

​ ps：系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的，位于同一个磁盘块的数据会被一次性读取出来。

​ InnoDB存储引擎中有页的概念，页是其磁盘管理的最小单位。InnoDB引擎中默认的每个页的大小为16KB。而系统的一个磁盘块的存储空间往往没有16KB大，所以InnoDB每次申请磁盘空间时会将若干个地址连续的磁盘块来达到页的大小16kb。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查阅数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O的次数，提高查询的效率。

​ B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。

​ B-Tree中根节点和子节点中既保存键值key和数据data，也保存当前节点的子节点地址信息。叶子节点中只保存key指向数据的关键字和数据data。

​ 一颗m阶的B-Tree有如下特性：

​ 1.每个节点最多只能有m个孩子。

​ 2.除了根节点和页子节点外，其他每个节点至少有两个孩子。

​ 3.所有叶子节点都在同一层，且不包含其他的关键字数据。

​ 4.除叶子节点外的其他节点中既保存指向当前节点的子节点的指针也保存指向数据的关键字。

​ 5.关键字在整个B-Tree结构中从左到右升序排序。

​ 下图为一个3阶的B-Tree：

​ 图中：每个节点占用一个磁盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子节点的指针，指针存储的是子节点所在的磁盘块的地址。两个关键字划分成的三个范围域对应的三个指针指向的子树的数据范围。

B+Tree

​ B+Tree是B-Tree的变种，在B-Tree的基础上进行了优化，更加适合外存储索引结构，InnoDB存储引擎就是用B+Tree实现的。

​ B-Tree中除叶子节点外的其他节点中不仅保存了包含数据的key值还有data值，而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点能够存储的键值数量很小，此时如果存储的数据量很大会导致B-Tree的深度大，增加查找数据时的磁盘I/O次数，影响查询效率。

​ B+Tree中所有数据记录节点都是按照键值大小顺序放在同一层的叶子节点上，而非叶子节点上只存储key值信息。这样可以降低树的深度，同时可以增加每个非叶子节点存储关键字key值得数量。

​ B+Tree相对于B-Tree：

​ 1.非叶子节点中只存储指向子节点的指针和key关键字信息不存储数据data。

​ 2.所有的叶子节点中都有一个链指针。

​ 3.所有数据记录都存放在叶子节点。

​ B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，其结构如下图所示：

​ 所有的叶子节点(数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查询和分页查询，一种是从根节点开始进行随机查找。

​ ps：InnoDB存储引擎中页的大小为16KB，一般表的主键类型为 int 4字节 bigint 8字节，指针类型也一般为4或8个字节，一页中大概能存储 16KB/(8B+8B)=1K个键值。一个深度为3的B+Tree索引能够存储10^9=10亿条数据。实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。MySQL的InnoDB存储引擎将根节点常驻内存中，所以在查询某个键值的行记录时最多只需要1~3次磁盘I/O操作。

​ ps：数据库中的B+Tree索引可以分为聚集索引和辅助索引。上面的B+Tree示例图在数据库中的实现为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引和聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应的行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

s：数据库中的B+Tree索引可以分为聚集索引和辅助索引。上面的B+Tree示例图在数据库中的实现为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引和聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应的行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

​