mysql数据库之:存储引擎、索引的使用

开头：本人总结，大神勿喷，欢迎指教。谢谢

一：mysql的存储引擎分MyISAM InnoDB Memory Mrg_Myisam Blackhole

    mysql数据通过各种不同的技术存储在磁盘中，这些技术每一种技术使用的存储机制、索引技巧、锁定水平各不相同。

   （1）MyISAM 默认的存储引擎

        特点:不支持事务、不支持外键。当查询多的时候使用，增加效率。

       MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。

      适用场景:

             1）不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。

             2）不支持外键的表设计。

             3）查询速度很快。

             4）整天 对表进行加锁的场景。

             5）MyISAM极度强调快速读取操作。

             6）MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。

   （2）InnoDB存储引擎（可修改my.ini文件，在[mysqld]中修改为default-storage-engine=InnoDB） 

        特点：提供了具有提交、奔溃、回滚的事务特点、支持外键约束、行级锁、

        Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这类型的文章。该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。（修改数据是条件用like等）

       适用场景： 1）支持事务

                          2）适用于多重更新表记录的操作

                          3）支持外键约束

                          4）支持自动增加列属性auto_increment

                          5）可以从灾难中恢复（通过bin-log日志等）。

                          6）更新数据频繁，对事务处理有一定要求。

       但是相对MyISAM引擎写入数据的速度要慢。

（3）Memory（也叫HEAP）堆内存：

          使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。

但是一旦服务关闭，表中的数据就会丢失掉。 HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。

（4）Mrg_Myisam：（分表的一种方式–水平分表）

         是一个相同的可以被当作一个来用的MyISAM表的集合。“相同”意味着所有表同样的列和索引信息。

也就是说，他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作。

比如：我们可能会遇到这样的问题，同一种类的数据会根据数据的时间分为多个表，如果这时候进行查询的话，就会比较麻烦，Merge可以直接将多个表聚合成一个表统一查询，然后再删除Merge表（删除的是定义），原来的数据不会影响。

（5）Blackhole（黑洞引擎）

        任何写入到此引擎的数据均会被丢弃掉， 不做实际存储；Select语句的内容永远是空。

             他会丢弃所有的插入的数据，服务器会记录下Blackhole表的日志，所以可以用于复制数据到备份数据库。

借鉴:

      于互联网和https://www.cnblogs.com/sunsky303/p/8274586.html

二 : mysql索引的使用及底层数据结构

        只讲解b+tree 和 hash 结构，其余结构请自行搜索！

索引是一个单独存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针，使用索引可以提高数据库特定数据的查询速度.索引时在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同,并且每种存储引擎也不一定支持所有索引类型．

    索引的优点：

     1.通过创建唯一索引，可以确保数据库数据的唯一性

     2.可以加快数据库查询的速度

     3.通过使用索引，可以在查询中使用优化隐藏器，提高系统的性能

     4.加速表和表之间的连接；

    索引的缺点：

    1.索引是存储在磁盘上的数据结构，它们包含着对数据表里所有记录的引用指针。所以创建索引需要耗费空间内存。

    2.创建索引和维护索引需要耗费时间。并且随着数据量的增长耗费越来越大。

    3.对表中数据删改的时候，索引也需要动态的进行维护，这样降低了数据的维护速度。

   说了这么多，现在常用的MySQL数据库 ，索引的底层实现又是怎样的呢？

    MySQL MyISAM InnoDB引擎的索引底层实现采用的是B+ tree。什么是B+ tree？为什么是B+ tree？B+ tree相较于二叉搜索树 AVL 红黑树又有什么样的优势？

      首先，要说B+ tree,得先说B- tree。B- tree读做 b  tree。不是 b减tree（音译）。从数据查找搜索来看。二叉搜索树，采取二分查找的思想，O（log N）的复杂度就可以完成对数据的查找任务。那么为什么不用二叉搜索树？

       一句话：因为磁盘IO问题。之前说过，索引是存储在磁盘上的，对数据库表中一列或多列进行排序的数据结构。数据库中的数据可能很大，在大量的数据存储在磁盘上时。计算机无法一次性将数据全部加载进内存。而是通过逐一加载每一磁盘页。而加载磁盘页对应着索引树的节点。那么一次查找所经历的索引树的深度对应着磁盘IO交互的次数。如果采取二叉树结构，那么显而易见，可能导致遍历的深度太大导致磁盘IO交互的次数太多。而相较于内存查询，磁盘IO才是影响查询以及更新表中数据的关键。那么由二叉树这样的瘦长结构自然容易联想到如何将它变的矮胖。这样做虽然没有降低比较次数，由于深度的降低，可以极大的减少磁盘IO次数。从而可以提升查询以及更新数据的性能。

备注: 觉得这篇关于索引、外键的使用写的不错 https://blog.csdn.net/yw_1207/article/details/81326807 和https://blog.csdn.net/jackliu16/article/details/80727852