Mysql专题

Innodb引擎和myIsam引擎区别

mysql5.5以下默认的是myisam引擎，5.6及其其上默认的是innodb引擎

存储引擎定义的是数据的存储方式和读取实现逻辑。不同的存储方式和读取方式，可以帮助我们针对不同的场景，来选择合适的引擎，从而获取更好的性能

存储区别

myisam有两个文件存储，一个存储数据文件（.MYD），一个存储索引文件(.MYI)

因为存储的索引文件和数据文件是分离的，所以是通过先去读取数据的索引文件，找到数据的存储文件，再到数据文件里面根据索引找到对应的数据内容。

innodb同样是存储在磁盘上面，他的文件是ibd文件。存储了索引和数据文件。在通过检索索引，命中叶子节点，就可以直接从叶子节点里面讲数据取出来。

1.数据存储方式不同。

2.对于事务的支持不同。myisam不支持事务，innodb支持ACID事务特性的事务处理。

3.对锁支持的不同。myisam只支持表锁，innodb支持行锁，表锁，间隙锁，临键锁。

4.myisam不支持外键。innodb支持外键

如果大量的表操作都是支持查询，则可以选择myisam,如果需要支持事务，则选择innodb

聚簇索引和非聚簇索引

聚簇索引是指主键索引，如果没有设置主键，会自动创建一个隐式的rowId(6字节)作为主键索引。

        聚簇索引的叶子节点里面的data域里面保存了该行的所有信息

非聚簇索引

        非聚簇索引保存的是该索引，data域里是主键索引，再根据主键索引去查找改行的所有信息。

B树和B+树的区别

        首先常规的数据库引擎都是用B树和B+树来实现索引的存储。因为B树是一种多路平衡树，用这种结构来存储大量数据的时候，他的整体高度会相对二叉树来说会矮很多，而对于数据库来说，所有数据必然存储在磁盘上，而磁盘IO的效率实际上是很低的，特别是在随机磁盘IO的一个情况下，效率更低，所以树的高度就能决定磁盘IO的一个次数，磁盘IO次数越少，对性能的提升就越好，这也是为什么采用B树作为存储结构的原因，但是在mysql的innodb里面它采用的是一种增强型B树结构，也就是B+树，来存储索引和数据的存储结构。B+树做了以下几个优化。

        第一个是B+树的所有数据都存储在叶子节点上，非叶子节点只存储索引。

        第二个是叶子节点里面的数据是采用双向链表的方式来进行关联的。

所以使用B+树来实现索引的一个存储结构我认为有以下几个原因：

        1.非叶子节点上只存储索引，相对于B树来说，在层高相同的情况下，B+树能存储更多的索引，有效的减少IO次数。

        2.在数据库的查询里面范围查询是一个常用操作，而叶子节点的双向链表设计可以只需要查询到两个边界索引的位置，然后进行遍历就行，而B树需要获取所有节点。所以B+树在范围查询上的效率会更高一些。

        3.B+树的所有数据都是存储在叶子节点上面，所以它的IO次数会更稳定一些。

        4.B+树的所有数据都存储在叶子节点上面，所以它的全局扫描只需要扫描所有的叶子节点，而B树需要扫描全表。

        另外B+树的主键最好是使用自增的整型数据来做，因为相比其它类型，能在新增数据的时候，能够有效避免因叶子节点的分裂，带来的大量计算的问题。

        技术方案的选择是为了更好的解决当前的问题，并不是说B+树是最好的选择，就像MongDB里面采用B树的结构。本质上来说是关系型数据库和非关系型数据库之间的一个差异。

mysql有哪几种索引

       FullText 全文索引只有myisam支持

        Hash      可以一次定位，但是只有在等于和包含条件下高效

        BTree        

        RTree       很少使用，仅支持数据类型geometry数据类型，该数据类型只有在myisam中有

mysql的主从同步原理

1.master将数据的改变写入二进制binlog文件中

2.slave定时去探测binlog文件是否有发生改变，如果发生改变则启动一个I/O Thread请求master二进制文件事件

3.同时master会向每个I/O请求启动一个dump Thread,用于发送二进制事件，并保存至从节点的relaylog.从节点将启动一个SQL线程读取relaylog,将relaylog进行本地重放，使得本机数据和master节点数据一致，然后休眠线程，等待下一次被唤醒。

mysql与Redis数据同步

基本原理： 用mysql的行锁和redis的分布式锁来保证数据不被其它线程给覆盖。

1.延迟双删，在做更新数据的时候先把mysql和Redis里的数据给删除掉

2.双写，在做更新数据操作的时候同步更新mysql和Redis里的数据给重新修改，两者之间会存在一个几十ms的延迟，在分布式架构中，这种弱一致性是被允许的。时间如果太长的话就是链路中某个地方节点挂掉了，或者出了一些其它故障。

回表查询

查询数据的时候需要二次查询聚簇索引

覆盖索引

查询的列值可以在索引列中查到，不用回表查行数据

如单列索引A

select A from table where A = ?

联合索引 A,B,C

select B,C from table where A = ?

char跟varchar的区别

char是定长列。如果存储的数据长度不够定义的长度，则补空格去存储。

varchar是变长列。如定义的varchar(8),则存储数据"abc"的长度是3.如果“abc” 变更为“abcde"则会发生‘行迁移’现象，多几次I/O。

存储定长数据用char,查询效率会比varchar高。varchar会比char省空间