MySQL索引篇

IT浪子_燕青
51 阅读5分钟

“携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第1天，点击查看活动详情”

一、MySQL的架构

逻辑架构

image-20200410110806171.png

连接器（Connectors）

系统管理和控制工具（Management Serveices & Utilities）

连接池（Connection Pool）

SQL接口（SQL Interface）

解析器（Parser）

查询优化器（Optimizer）

查询缓存（Cache和Buffer）

存储引擎（Pluggable Storage Engines）

InnoDB和MyISAM存储引擎区别：

InnodbMyisam
存储文件.frm 表定义文件 .ibd 数据文件和索引文件.frm 表定义文件 .myd 数据文件 .myi 索引文件
锁表锁、行锁表锁
事务支持不支持
CRDU读、写读多
count扫表专门存储的地方 （加where也扫表）
索引结构B+ TreeB+ Tree
外键支持不支持

执行流程图 image.png

物理架构

MySQL是通过文件系统对数据和索引进行存储的。

MySQL从物理结构上可以分为日志文件和数据索引文件。

MySQL在Linux中的数据索引文件和日志文件都在/var/lib/mysql目录下。

日志文件采用顺序IO方式存储(记录速度快，只能追加，浪费空间)

数据文件采用随机IO方式存储。

日志文件

错误日志（errorlog）

二进制日志（bin log）

binlog记录了数据库所有的ddl语句和dml语句，但不包括select语句内容，数据备份、恢复、主从

通用查询日志（general query log）

啥都记录 耗性能

慢查询日志（slow query log）

默认是关闭的

重做日志（redo log） 回滚日志（undo log） 中继日志（relay log）

数据文件

InnoDB数据文件

  • .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
  • .ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件。
  • ibdata文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文 件。

MyIsam数据文件

  • .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
  • .myd文件：主要用来存储表数据信息。
  • .myi文件：主要用来存储表数据文件中任何索引的数据树。

二、MySQL索引篇

索引的优势和劣势

优势：

  • 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。 -- 检索
  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。 --排序
  • 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。
  • where 索引列 在存储引擎层 处理 索引下推 ICP
  • 覆盖索引 select 字段 字段是索引

劣势：

  • 索引会占据磁盘空间
  • 索引虽然会提高查询效率，但是会降低更新表的效率**。比如每次对表进行增删改操作，
  • MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

索引的分类

单列索引 组合索引 全文索引(5.6以后) 空间索引 位图索引 Oracle

索引原理分析

索引的存储结构

  • 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
  • MyISAM和InnoDB存储引擎：只支持B+ TREE索引， 也就是说默认使用BTREE，不能够更换
  • MEMORY/HEAP存储引擎：支持HASH和BTREE索引

B树和B+树

数据结构示例网站：www.cs.usfca.edu/~galles/vis…

B树和B+树的最大区别在于非叶子节点是否存储数据的问题

B+数据是顺序链表

非聚集索引（MyISAM）

主键和辅助索引

image-20200410135126072

聚集索引（InnoDB）

主键索引

image-20200410135416124

辅助索引（存的主键值，不是地址）

image-20200410135510734

解释覆盖索引

select id,name from t where name='Alice'

为什么使用组合索引

image-20200410140307608

索引失效分析

口诀

image-20200410140716308

查看执行计划

explain出来的信息有10列，分别是

id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

type:使用索引情况

依次从好到差：

system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery， index_subquery，range，index_merge，index，ALL

除了all之外，其他的type都可以使用到索引

最少要索引使用到range级别

extra:这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十 种

  • using index:查询时不需要回表查询
  • using where:表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤
  • 查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然 后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检 查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了 存储引擎扫描的记录数量。extra列显示using index condition

image-20200410142003022

总结

image-20200410143550505

\

avatar
文章
阅读
粉丝