MySQL 索引类型与数据结构

mysql索引类型与数据结构

①什么是索引

索引是供服务器快速在表中查询一行数据的数据库结构,默认是 B+Tree 结构

索引适合在 高基数列 ( 不相同的值多的列 ), 或者数据量很大的列上添加

索引列不能经常变换(增删改) , 维护索引需要花费时间

②索引类型

• 普通索引 index

  最基本的索引没有任何限制

  01  –直接创建索引
  02  CREATE INDEX index_name ON table(column(length))
  
  03  –修改表结构的方式添加索引
  04  ALTER TABLE table_name ADD INDEX index_name ON (column(length))
  
  05  –创建表的时候同时创建索引
  06  CREATE TABLE `table` (
  07      `id` int(11) NOT NULL AUTO_INCREMENT ,
  08      `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  09      `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
  10      `time` int(10) NULL DEFAULT NULL ,
  11       PRIMARY KEY (`id`),
  12       INDEX index_name (title(length))
  13  )
  14  –删除索引
  15  DROP INDEX index_name ON table
  

• 唯一索引 UNIQUE

  与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）

  01  –创建唯一索引
  02  CREATE UNIQUE INDEX indexName ON table(column(length))
  
  03  –修改表结构
  04  ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
  
  05  –创建表的时候直接指定
  06  CREATE TABLE `table` (
  07      `id` int(11) NOT NULL AUTO_INCREMENT ,
  08      `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  09      `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
  10      `time` int(10) NULL DEFAULT NULL ,
  11      PRIMARY KEY (`id`),
  12      UNIQUE indexName (title(length))
  13  );
  

• 全文索引 FULLTEXT

  全文检索快

  01  –创建表的适合添加全文索引
  02  CREATE TABLE `table` (
  03    `id` int(11) NOT NULL AUTO_INCREMENT ,
  04    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  05    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
  06    `time` int(10) NULL DEFAULT NULL ,
  07    PRIMARY KEY (`id`),
  08    FULLTEXT (content)
  09  );
  
  10  –修改表结构添加全文索引
  11  ALTER TABLE article ADD FULLTEXT index_content(content)
  
  12  –直接创建索引
  13  CREATE FULLTEXT INDEX index_content ON article(content)
  

• 单列索引,多列索引

  多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引

• 组合索引

  例如表中针对 title 和 time 建立一个组合索引：

  ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))

  建立这样的组合索引，其实是相当于分别建立了两组组合索引

③索引的数据结构

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构

数据库主要是查询, 设计者为提升速度 从算法角度进行优化, 但是不同算法适用于不同数据结构

1. 顺序查找 数据量很大时效率极其低下
2. 二分查找 要求被检索文件有序
3. 二叉树查找 只能应用于二叉树上

但是数据本身的组织结构不可能完全满足各种数据结构

所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引.

常用索引数据结构

• B - Tree
• B + Tree

问题: 红黑树等数据结构也可实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构?

• 一般来说, 索引文件也很大, 不能完全存在内存中, 往往以索引文件方式存储在磁盘中
• 故, 查询会产生 I/O 消耗, 评价索引优劣的重要指标是查找过程中磁盘I/O操作次数的渐进复杂度
• 故, 索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数

B-Tree中一次检索最多需要h-1次 I/O（根节点常驻内存），渐进复杂度为(O(h)=O(log_dN))

红黑树的I/O渐进复杂度也为O(h)，效率就明显比B-Tree差很多

④MySQL索引实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的

MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）

这块内容已经涉及本人能力边界, 需要补充新知识 再来进一步探索

参考网站: https://blog.codinglabs.org/articles/theory-of-mysql-index.html