mysql8.0 innodb 存储引擎介绍 加俩类索引方法 btree hash ，三类索引类型 Normal Unique Full Text 业务应用中选择思路，及官方各类存储引擎对服务支持情况

目录

​​1.介绍​​

​​2.使用InnoDB表的好处​​

​​2.1恢复-具体怎么恢复有这么几种类型：​​

​​2.2扩展知识：BLOB和TEXT类型 ^​​

​​3.要是想要优化innodb可以看具体的8.0innodb表优化​​

​​3.1优化类型有这么几项​​

​​4.各类型索引及各类功能 对应 各类型存储引擎支持表，总览​​

​​4.1可以主看标红的​​

​​4.2innodb 存储引擎 和对应的给类型索引支持情况​​

​​5.索引类型​​

​​5.1Full Text​​

​​5.2Unique​​

​​5.3Normal​​

​​6.索引方法​​

​​7.mysql8.0 的 优化和索引​​

​​8.索引使用​​

​​9.Btree和hash索引的比较​​

​​9.1Btree​​

​​9.1.1以下WHERE子句使用索引：​​

​​9.1.2这些WHERE子句 不使用索引：​​

​​9.2哈希指数特征​​

​​10.索引方法、索引类型 总结策略​​

​​10.1索引方法​​

​​10.1.1Btree​​

​​10.1.2hash​​

​​10.2索引类型​​

​​10.2.1Normal​​

​​10.2.2Unique​​

​​10.2.3Full Text​​

​​11.应用分析​​

1.介绍

最近系统服务的数据越来越多开始考虑升级扩展，现将mysql数据库的存储引擎和索引类型，索引方法进行研究分析一下。

便于之后扩展使用，这里进行一下官方文档的一下介绍和总结的一些思路便于使用，这篇文章是mysql8.0版本的信息。

有些事msyql5.6延续下来的，这里8.0不错 ok看内容哇

2.使用InnoDB表的好处

• 如果服务器由于硬件或软件问题而意外退出，无论当时数据库中发生了啥，重新启动数据库后都无需执行任何特殊操作。​

  ​InnoDB​

  ​崩溃恢复会自动完成在崩溃之前提交的更改，并撤消正在处理但尚未提交的更改，从而使您可以重新开始并从上次中断的地方继续。

2.1恢复-具体怎么恢复有这么几种类型：

• 时间点恢复
• 从数据损坏或磁盘故障中恢复
• innoDB崩溃恢复
• 故障恢复期间的表空间恢复

几项优势介绍

• ​

  ​InnoDB​

  ​存储引擎维护自己的缓冲池，在主内存缓存表和索引数据作为数据被访问。经常使用的数据直接从内存中处理。此缓存适用于多种类型的信息，并加快了处理速度。在专用数据库服务器上，通常最多将80％的物理内存分配给缓冲池。
• 如果将相关数据拆分到不同的表中，则可以设置强制引用完整性的外键。
• 如果数据在磁盘或内存中损坏，则校验和机制会在使用前提醒您注意虚假数据  innodb_checksum_algorithm 变量定义由所用的校验和算法 InnoDB。

• 当为每个表设计具有适当主键列的数据库时，涉及这些列的操作会自动进行优化。在WHERE 子句，ORDER BY子句， GROUP BY 子句和联接操作中引用主键列非常快速 。
• 插入，更新和删除通过称为更改缓冲的自动机制进行了优化。​

  ​InnoDB​

  ​ 不仅允许对同一表的并发读写访问，而且还缓存更改的数据以简化磁盘I / O。
• 性能优势不仅限于具有长时间运行的查询的大型表。当从表中一遍又一遍地访问相同的行时，自适应哈希索引将接管这些查询，使它们的查找速度更快，就好像它们来自哈希表一样。
• 可以压缩表和关联的索引
• 可以加密数据
• 可以创建和删除索引并执行其他DDL操作，而对性能和可用性的影响要小得多
• 截断每表文件表空间非常快，可以释放磁盘空间供操作系统重用，而不是仅重用​

  ​InnoDB​

  ​
• BLOB使用DYNAMIC行格式， 对于长文本字段，表数据的存储布局更为有效

2.2扩展知识：BLOB和TEXT类型 ^

• ABLOB是一个二进制大对象，可以容纳可变数量的数据。这四个BLOB 类型TINYBLOB，BLOB， MEDIUMBLOB，和LONGBLOB。这些仅在它们可以容纳的值的最大长度上有所不同。这四个TEXT类型 TINYTEXT，TEXT， MEDIUMTEXT，和LONGTEXT。这些对应于四种BLOB类型，并且具有相同的最大长度和存储要求 。
• BLOB值被视为二进制字符串（字节字符串）。它们具有binary 字符集和排序规则，并且比较和排序基于列值中字节的数字值。 TEXT值被视为非二进制字符串（字符字符串）。它们具有以外的字符集 binary，并且根据字符集的排序规则对值进行排序和比较。

继续说优势：

• 可以通过查询​

  ​INFORMATION_SCHEMA​

  ​表来监视存储引擎的内部工作情况
• 可以通过查询性能架构表来监视存储引擎的性能详细信息
• 可以将InnoDB表与其他MySQL存储引擎的表混合使用，即使在同一条语句中也可以。例如，可以使用联接操作在单个查询中合并来自InnoDB和 MEMORY表的数据 。
• ​

  ​InnoDB​

  ​ 设计用于处理大数据量时的CPU效率和最佳性能。
• ​

  ​InnoDB​

  ​ 表可以处理大量数据，即使在文件大小限制为2GB的操作系统上也一样。

3.要是想要优化innodb可以看具体的8.0innodb表优化

3.1优化类型有这么几项

• 1优化InnoDB表的存储布局
• 2优化InnoDB事务管理
• 3优化InnoDB只读事务
• 4优化InnoDB重做日志
• 5 InnoDB表的批量数据加载
• 6优化InnoDB查询
• 7优化InnoDB DDL操作
• 8优化InnoDB磁盘I / O
• 9优化InnoDB配置变量
• 10为具有多个表的系统优化InnoDB

主要优势就是上边这些了，这里还加了部分的知识扩展，便于了解，如优化、BLOB、恢复

4.各类型索引及各类功能 对应 各类型存储引擎支持表，总览

4.1可以主看标红的

特征、功能、索引	特征、功能、索引	MyISAM	Memory	InnoDB	Archive	NDB
B-tree indexes	B树索引	是	是	是	没有	没有
Backup/point-in-time recovery (note 1)	备份/时间点恢复（注释1）	是	是	是	是	是
Cluster database support	集群数据库支持	没有	没有	没有	没有	是
Clustered indexes	聚集索引	没有	没有	是	没有	没有
Compressed data	压缩数据	是（注释2）	没有	是	是	没有
Data caches	资料快取	没有	不适用	是	没有	是
Encrypted data	加密数据	是（注释3）	是（注释3）	是（注释4）	是（注释3）	是（注释3）
Foreign key support	外键支持	没有	没有	是	没有	是（注释5）
Full-text search indexes	全文搜索索引	是	没有	是（注释6）	没有	没有
Geospatial data type support	地理空间数据类型支持	是	没有	是	是	是
Geospatial indexing support	地理空间索引支持	是	没有	是（注释7）	没有	没有
Hash indexes	哈希索引	没有	是	否（注释8）	没有	是
Index caches	索引缓存	是	不适用	是	没有	是
Locking granularity	锁定粒度	表	表	行	行	行
MVCC	MVCC	没有	没有	是	没有	没有
Replication support (note 1)	复制支持（注释1）	是	限量（附注9）	是	是	是
Storage limits	储存限制	256TB	内存	64TB	没有	384EB
T-tree indexes	T树索引	没有	没有	没有	没有	是
Transactions	交易次数	没有	没有	是	没有
Update statistics for data dictionary	更新数据字典的统计信息	是	是	是	是	是

4.2innodb 存储引擎 和对应的给类型索引支持情况

特征、功能、索引	特征、功能、索引	支持情况
B-tree indexes	B树索引	是
Backup/point-in-time recovery (Implemented in the server, rather than in the storage engine.)	备份/时间点恢复（在服务器中而不是在存储引擎中实现。）	是
Cluster database support	集群数据库支持	没有
Clustered indexes	聚集索引	是
Compressed data	压缩数据	是
Data caches	资料快取	是
Encrypted data	加密数据	是（通过加密功能在服务器中实现；在MySQL 5.7和更高版本中，支持静态数据加密。）
Foreign key support	外键支持	是
Full-text search indexes	全文搜索索引	是（MySQL 5.6及更高版本中提供了对FULLTEXT索引的支持。）
Geospatial data type support	地理空间数据类型支持	是
Geospatial indexing support	地理空间索引支持	是（在5.7和更高版本中提供了对地理空间索引的支持。）
Hash indexes	哈希索引	否（InnoDB在内部将哈希索引用于其自适应哈希索引功能。）
Index caches	索引缓存	是
Locking granularity	锁定粒度	行
MVCC	MVCC	是
Replication support (Implemented in the server, rather than in the storage engine.)	复制支持（在服务器中而不是在存储引擎中实现。）	是
Storage limits	储存限制	64TB
T-tree indexes	T树索引	没有
Transactions	交易次数	是
Update statistics for data dictionary	更新数据字典的统计信息	是

5.索引类型

5.1Full Text

全文索引，一般数据文本多的使用

​

​FULLTEXT​

​​索引用于全文搜索。只有InnoDB​和 MyISAM​存储引擎支持 ​

​FULLTEXT​

​​索引和仅适用于 CHAR​， VARCHAR​和 TEXT列。索引始终在整个列上进行，并且不支持列前缀索引。

5.2Unique

这个是唯一索引，全表唯一的一个

5.3Normal

这个是普通索引

6.索引方法

7.mysql8.0 的 优化和索引

• 改善操作性能的最佳方法 SELECT是在查询中测试的一个或多个列上创建索引。索引条目的作用类似于指向表行的指针，从而使查询可以快速确定哪些行与WHERE子句中的条件匹配，并检索这些行的其他列值。所有MySQL数据类型都可以建立索引。
• 尽管可能会为查询中使用的每个可能的列创建索引，但不必要的索引会浪费空间和时间，使MySQL难以确定要使用的索引。索引还会增加插入，更新和删除的成本，因为必须更新每个索引。您必须找到适当的平衡，才能使用最佳索引集来实现快速查询。

8.索引使用

大多数MySQL索引（PRIMARY KEY， UNIQUE，INDEX和 FULLTEXT）存储在 B树。例外：空间数据类型的索引使用R树；MEMORY 表还支持哈希索引; InnoDB对FULLTEXT索引使用倒排列表。

​

​WHERE​

​快速 查找与子句匹配的行

从考虑中消除行。如果可以在多个索引之间进行选择，MySQL通常会使用找到最少行数的索引

如果表具有多列索引，那么优化器可以使用索引的任何最左前缀来查找行。举例来说，如果你有一个三列的索引 ​

​(col1, col2, col3)​

​​，你有索引的搜索功能​

​(col1)​

​​， ​

​(col1, col2)​

​​以及​

​(col1, col2, col3)​

​。

不是同一款字符集，索引会被排除使用 如utf-8 和 ​

​latin1​

​列进行比较会排除使用索引

9.Btree和hash索引的比较

9.1Btree

A B树索引可以在使用表达式中使用的对列的比较 =， >， >=， <， <=，或BETWEEN运营商。

LIKE 如果to的参数LIKE是不以通配符开头的常量字符串，则索引也可以用于比较 。

9.1.1以下WHERE子句使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2

    /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5

    /* Can use index on index1 but not on index2 or index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;      

9.1.2这些WHERE子句 不使用索引：

/* index_part1 is not used */
... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */
... WHERE index=1 OR A=10

    /* No index spans all rows  */
... WHERE index_part1=1 OR index_part2=10      

有时，即使索引可用，MySQL也不使用索引。发生这种情况的一种情况是，优化器估计使用索引将需要MySQL访问表中很大比例的行。（在这种情况下，表扫描可能会更快，因为它需要更少的查找。）但是，如果这样的查询​

​LIMIT​

​仅用于检索某些行，则MySQL仍将使用索引，因为它可以更快地找到索引。几行返回结果。

9.2哈希指数特征

哈希索引与Btree索引具有一些不同的特征：

• 它们仅用于使用​

  ​=​

  ​or​

  ​<=>​

  ​ 运算符的相等比较 （但非常快）。它们不用于比较运算符，例如​

  ​<​

  ​用于查找值范围的运算符 。依赖于这种单值查找类型的系统称为“键值存储”；要将MySQL用于此类应用程序，请尽可能使用哈希索引。
• 优化器无法使用哈希索引来加速​

  ​ORDER BY​

  ​操作。（此索引类型不能用于按顺序搜索下一个条目。）
• MySQL无法确定两个值之间大约有多少行（范围优化器使用它来决定要使用哪个索引）。如果将​

  ​MyISAM​

  ​或 ​

  ​InnoDB​

  ​表更改为哈希索引 ​

  ​MEMORY​

  ​表，这可能会影响某些查询。
• 仅整个键可用于搜索行。（对于B树索引，键的任何最左边的前缀都可用于查找行。）

10.索引方法、索引类型 总结策略

10.1索引方法

10.1.1Btree

业务项目，有需要对数据范围比大小的方式查询数据，这种类型可以采用Btree 索引方法 

btree 可以比较范围 >  <  <=  >= 这类型比较方便

10.1.2hash

业务项目，如有的金额，单独金额对应单独金额查询，或单独的，通过组件查询对应的值，这种可以使用Hash 索引方法

hash 索引将索引内容进行了hash 索引索引值比较比= 或<=> 会很快

10.2索引类型

10.2.1Normal

普通索引，没啥限制

10.2.2Unique

唯一值得实惠便于使用 key 使用

10.2.3Full Text

全文索引，这玩意文字特别多的时候使用比较好

11.应用分析

所以使用的时候更具10.索引方法、索引类型 总结策略，选择需要使用的类型

更具具体的业务需要使用索引，项目书记大了之后速度也就上来了

ok

ok