MySQL之存储引擎和索引原理

存储引擎

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

MyISAM存储引擎

使用MyISAM存储引擎，创建user表，建表语句如下：

CREATE TABLE `user` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL COMMENT '用户名',
  `mobile` CHAR(16) NOT NULL COMMENT '手机号码',
  `sex` VARCHAR(8) NOT NULL COMMENT '性别',
   PRIMARY KEY (`id`),
   INDEX `idx_user_name` (`name` ASC) VISIBLE)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COMMENT = '用户信息表';

MyISAM存储引擎表会创建3个文件。
在 /usr/local/mysql/data/user_db 下查看user表的文件信息（user_db为数据库名）

文件名	描述
user.MYD	数据文件
user.MYI	索引文件
user.frm	表结构定义文件

MyISAM存储引擎的数据文件和索引文件是分开存放的。

给用户表user添加8条数据，如下：

磁盘地址	Id	name	mobile	sex
0x07	1	喜🐑🐑	1234567	男
0x56	2	懒🐑🐑	1234567	男
0x6A	3	美🐑🐑	1234567	女
0xF3	4	慢🐑🐑	1234567	男
0x90	5	沸🐑🐑	1234567	男
0x77	6	瘦🐑🐑	1234567	女
0xA1	7	花🐑🐑	1234567	女
0xB1	8	奔🐑🐑	1234567	女

主键索引结构
MyISAM引擎使用B+树作为索引结构，叶节点的data域存放的是数据记录的地址。下图为MyISAM表的主键索引。

普通索引结构
在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。下图在user表的name 列上建立一个辅助索引

索引的数据结构是一颗B+Tree，叶节点的data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB

使用InnoDB存储引擎创建　user表，建表语句如下：

CREATE TABLE `user_info` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT '' COMMENT '名字',
  `sex` int unsigned NOT NULL DEFAULT '1' COMMENT '性别',
  `age` int unsigned NOT NULL COMMENT '年纪',
  `mobile` varchar(32) NOT NULL COMMENT '手机号码',
  PRIMARY KEY (`id`),
  KEY `idx_user_info_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='用户信息表';

InnoDB存储引擎表会创建2个文件。
在 /usr/local/mysql/data/user_db 下查看user_info表的文件(user_db为数据库名)

文件名	描述
user_info.ibd	数据和索引文件
user_info.frm	表结构定义文件

InnoDB存储引擎的数据和索引是在同一个文件中。

主键索引结构
同样是B+树，实现方式却完全不同。InnoDB表数据文件本身就是一个索引结构，树的叶节点data域保存了完整的数据记录，这种索引叫做聚集索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键(MyISAM可以没有)，如果没有显式指定，则mysql会自动选择一个可以唯一标识数据记录的列作为主键。如果不存在这种列，则mysql自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

查询_rowId信息：

普通索引结构
InnoDB的所有辅助索引都引用主键作为data域，下图是user 表 name索引结构。

因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引也会包含主键列，所以如果主键定义的比较大，其他索引也将很大。InnoDB 不会压缩索引。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录，这种查找方式叫回表。

不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

模拟数据结构网站：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

MyISAM 和 InnoDB区别

	索引结构	锁	支持事务	外键
MyISAM	B+树(非聚集)	表锁	否	无
InnoDB	B+树(聚集)	表锁和行锁	是	有

索引优化

覆盖索引
覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

select_type	type	possible_keys	key	key_len	ref	rows	Extra
简单SELECT，不使用UNION或子查询等	Join 语句中被驱动表索引引用查询	可能使用到的索引	实际查询使用到的索引	varchar(n)类型索引字段,key_len=n(字符)*3+2(字节) + 1(可为null)	列出是通过常量（const）	MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。	Using index 表示使用到覆盖索引

字符串查询加引号
字符串加引号查询 explain select name from user.user_info where name = ‘2’;

字符串不加引号查询 explain select name from user.user_info where name = 2;

联合索引
联合索引是由多个字段组成的索引。

ALTER TABLE `user`.`user_info` 
ADD INDEX `idx_name_mobile` (`name` ASC, `mobile` ASC) VISIBLE;

则以下查询均可以使用到索引

select * from user_info where name='沸羊羊' and mobile='13123456789'
select * from user_info where name='沸羊羊' ;

使用name 和 mobile 查询数据
explain select * from user_info where name=‘沸羊羊’ and mobile=‘13123456789’;

使用name 查询数据
explain select * from user_info where name=‘沸羊羊’ ;

使用 mobile 查询数据(未使用到索引)
explain select * from user_info where mobile=‘13123456789’;