MySQL进阶之存储引擎和索引

1. 存储引擎

1.1 体系结构

MySQL体系结构主要分为四层：

1. 连接层：完成连接处理、授权认证等相关的安全方案；服务器也会验证接入的客户端的操作权限；
2. 服务层：完成大多数的核心服务功能，SQL接口、缓存的查询、SQL分析和优化、函数等。
3. 引擎层：负责MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同存储引擎功能不同。
4. 存储层：讲数据存储在文件系统上，完成和存储引擎之间的交互。
   

1.2 存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据的实现方式。
存储引擎是基于表的，也被称为表类型。

• 创建表时指定存储引擎：

  create table table_name(
  )ENGINE = INNODB;
  

• 查看当前数据库支持的存储引擎：

  show engines;
  

  

1.3 存储引擎特点

1.3.1 InnoDB

兼顾高可靠性和高性能的存储引擎，在MySQL5.5之后是默认存储引擎。

特点：

1. DML遵循ACID模型，支持事务；
2. 行级锁，提高并发访问性能；
3. 支持外键。

文件结构：table_name.idb文件：每张表都会对应一个表空间文件，存储表的表结构数据以及索引。

1.3.2 MyISAM

早期默认存储引擎。

特点：

1. 不支持事务和外键；
2. 支持表锁，不支持行锁；
3. 访问速度快。

文件结构：

• table_name.sdi：存储表结构数据；
• table_name.MYD：存储数据；
• table_name.MYI：存储索引。

1.3.3 Memory

数据存储在内存中，只能作为临时表或者缓存使用。

特点：

1. 内存存放；
2. hash索引。

文件结构：table_name.sdi：存储表结构信息。

1.3.4 总结

1. 如果对事务的完整性有要求，且并发下数据一致建议选择InnoDB；
2. 以读写和插入为主，很少更新和删除，对事务完整性不高，选择MyISAM；
3. 临时表和缓存选择Memeory。

2. 索引

2.1 索引简介

帮助MySQL高效获取数据的有序的数据结构，除数据外，数据库还维护着满足特定查找算法的数据结构，以某种方式引用数据。

优点：提高数据检索效率，降低数据库的IO成本；通过索引列对数据进行排序，降低排序的成本，降低CPU消耗。
缺点：占空间；降低了原子性操作的速度。

2.2 索引结构

MySQL的索引是在存储引擎称实现的。

2.2.1 B-Tree

多叉平衡搜索树
https://blog.csdn.net/weixin_44289860/article/details/124228530

2.2.2 B+Tree

所有的元素都会叶子节点中，其它位置的元素只是起到索引的作用；
所有的叶子节点都有一个指针指向下一个元素。

MySQL在B+树的基础上进行了优化，将叶子节点的单向链表变为了双向循环链表。就形成了带有顺序指针的B+Tree，提高区间的访问性能。

2.2.3 Hash

采用一定的hash算法，将值换算成新的hash值，映射到对应槽位上，存在hash表中，与Java的HashMap解决方案相同，出现哈希碰撞时使用链表结合。

特点：

1. 只能用于对等比较，不支持范围查询；
2. 无法利用索引完成排序；
3. 查询效率高，通常一次检索即可。

2.3 索引分类

1. 主键索引：针对表中主键创建的索引，为系统默认创建的，只能有一个，关键字：PRIMARY；
2. 唯一索引：避免同一个表中某数据列中的值重复，可以有多个，关键字：UNIQUE；
3. 常规索引：快速定位特定数据，可以有多个；
4. 全文索引：全文索引查找的是文本中的关键词，而不是比较索引中的值，关键字：FULLTEXT。

2.3.1 InnoDB分类

在InnoDB存储引擎中，根据索引的存储形式，可以分为：

1. 聚集索引：将数据存储与索引放到一块，索引结构的叶子节点保存了行数据，必须有且只有一个；默认主键为聚集索引；如果不存在主键，使用第一个唯一索引作为聚集索引；如果都没有，自动生成一个rowid作为隐藏的聚集索引。
2. 二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个。

存储图示：

2.4 索引语法

• 创建索引

create [unique|fulltext] index [index_name] on [table_name] (col_name,...);

• 查看索引

show index from table_name;

• 删除索引

drop index index_name on table_name;

2.5 性能分析

• 查看索引执行频率：

show GLOBAL status like 'Com_____';

• 慢查询日志：记录所有执行时间超过指定参数的所有SQL语句的日志，达到定位效率低的查询语句的作用，默认不开启。

# 开启慢查询：
slow_query_log=1

# 设置超时时间为 2 秒
long_query_time=2

开启之后重启MySQL。

• profile：show profile可以查看sql语句的耗时时间。
  查看是否支持profile：select @@have_profiling
  开启profiling：set profiling = 1

1. show profile ：查看每一条耗时情况；
2. show profile for query query_id：查看指定query_id各个阶段的耗时情况；
3. show profile cpu for query query_id：查看指定query_idCPU使用情况。