Mysql 存储引擎

推荐：http://blog.jobbole.com/24006/

 

一  存储引擎(建表时默认使用InnoDB)

 

 1. MyISAM （数据与索引分离）（非聚集索引）

使用B+Tree索引。 B+Tree叶节点的data域存放的是数据记录的地址。

在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。辅助索引也以相同的方式读取数据。

 主索引要求key是唯一的，而辅助索引的key可以重复。

主索引

辅助索引

特点：

• 不支持行锁(MyISAM只有表锁)，读取时对需要读到的所有表加锁，写入时则对表加排他锁；
• 不支持事务
• 不支持外键
• 不支持崩溃后的安全恢复
• 在表有读取查询的同时，支持往表中插入新纪录
• 支持BLOB和TEXT的前500个字符索引，
• 支持全文索引
• 支持延迟更新索引，极大地提升了写入性能
• 对于不会进行修改的表，支持 压缩表 ，极大地减少了磁盘空间的占用

 

 

 

2 .InnoDB（数据就是索引）（聚集索引）

使用B+Tree索引。其数据文件本身就是索引文件。树的叶节点 data域保存了完整的数据记录。

这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而 其余的索引都作为辅助索引。

辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。

主索引

辅助索引

      InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键。

 

特点：

•     支持行锁，采用MVCC来支持高并发，有可能死锁
•     支持事务
•     支持外键
•     支持崩溃后的安全恢复
•     不支持全文索引

 

 

3.两种引擎的区别

• InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

• InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
• InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因 此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索 引是独立的。

• InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

• Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

4 不同的使用场景

 采用MyISAM引擎

•  R/W > 100:1 且update相对较少
•  并发不高
•  表数据量小
•  硬件资源有限

采用InnoDB引擎

• R/W比较小，频繁更新大字段
• 表数据量超过1000万，并发高
• 安全性和可用性要求高

采用Memory引擎

• 有足够的内存
• 对数据一致性要求不高，如在线人数和session等应用
• 需要定期归档数据

 

 

二 索引

索引：通俗的讲，索引是一种数据结构，用来存储数据，读取时可以快速的获取到想要的数据。

1.hash索引

哈希索引是基于哈希表实现的，只有精确匹配索引所有的列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的 值，并且不同键值的行计算出来的哈希码也不一样。哈希索引是将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

在MySQL中，只有Memory 引擎显示支持哈希索引。这也是Memory 引擎表的默认索引类型，Memory 引擎同时也支持B-Tree索引。

例：

CREATE TABLE testhash(

        fname VARCHAR(50)  NOT NULL,

        lname VARCHAR(50) NOT NULL,

        KEY USING HASH(fname)

) ENGINE = MEMORY;

假设索引使用假象的哈希函数 f( )，它返回下面的值（都是示例数据，非真实数据）：

        f('Arjen')=2323;

        f('Liming')=7434;

        f('Hanfeng')=6745;

        f('Mars')=3245;

则哈希索引的数据结构如下：

槽（Slot）        值（Value）

2323                指向第 1 行的指针

3245                指向第 4 行的指针

6745                指向第 3 行的指针

7434                指向第 2 行的指针

注意每个槽的编号是顺序的，但是数据行不是。现在，来看如下查询：

    mysql> SELECT lname FROM testhash WHERE fname= 'Liming';

MySQL 先计算 ‘Liming’的哈希值，并使用该值寻找对应的记录指针。因为f('Liming')=7434，所以MySQL 在索引中查找 7434 ，可以找到指向第 2 行的指针，最后一步是比较第三行的值是否是 'Liming'，以确保就是要查找的行。

缺点：

A    哈希索引只包含哈希值和行指针，而不存储字段值，所以不能上使用索引中的值来避免读取行。
B    哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
C    哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
E    哈希索引只支持等值比较查询，包括=、IN( )、<=>（注意<>和<=>是不同的操作）。也不支持任何范围查询，如           WHERE price > 100 ;
F    访问哈希索引的数据结构非常快，除非有许多哈希冲突
G    如果哈希冲突很多，一些索引维护操作的代价也会很高。

Innodb引擎有一个特殊的功能叫做“自适应哈希索引”。当Innodb注意到某些索引值使用非常频繁时，它会在内存中基于B-tree索引之上再建立一个哈希索引。这是一个完全自动、内部的行为，用户无法配置或者设置，不过有必要可以关闭此功能。

 

2.B+Tree 索引

B-Tree 索引能过加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可找到合适的指针进入下层子节点，大的往右，小的就往左。叶子节点的指针指向的是被索引的数据，而不是其他的节点页。

B-Tree 索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。前面所述的索引类型对如下类型的查询有效：

                全值匹配：全值匹配指的是和索引中的所有列进行匹配。

                匹配最左前缀：在有多列索引的情况下，只使用索引的第一列。

                匹配列前缀：可以只匹配某一列的值的开头部分。例如某个字符串以某个字母开头。

                匹配范围值：在有多列索引的情况下，只使用索引的第一列匹配一定范围内的值。

                精确匹配某一列并范围匹配另外一列：例如在某个多列索引表中，第一列全匹配，第二列范围匹配。

               只访问索引的查询：即查询只需要访问索引，而无须访问数据行

因为索引树的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY 操作（按顺序查找）。一般来说，如果 B-Tree 可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果 ORDER  BY 子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

限制：

    A      如果不是按照索引的最左列开始查找，则无法使用索引。
    B     不能跳过索引中的列。
    C     如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。
 

3.全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文索引和其他几类索引的匹配方式完全不一样。它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。

 

 

建索引的策略：

              https://www.cnblogs.com/luyucheng/p/6289714.html（推荐）

               https://blog.csdn.net/qq_36647176/article/details/85524462

 

 

推荐：《高性能Mysql 3》

部分转自：https://blog.csdn.net/godop/article/details/80198679

                 https://juejin.im/post/5b1685bef265da6e5c3c1c34

                https://www.jianshu.com/p/a957b18ba40d