Mysql聚簇索引和索引覆盖

2019独角兽企业重金招聘Python工程师标准>>>

一、myisam与innodb引擎索引文件的异同：

1.myisam中, 主索引和次索引都指向物理行（磁盘位置）；

2.innodb的主索引文件上，直接存放该行数据，称为聚簇索引，次索引指向对主键的引用；

注意: innodb来说

1.主键索引既存储索引值，又在叶子中存储行的数据

2.如果没有主键，则会Unique key做主键

3.如果没有unique，则系统生成一个内部的rowid做主键

4.像innodb中主键的索引结构中，既存储了主键值，又存储了行数据，这种结构称为“聚簇索引”

聚簇索引优劣：

优势: 根据主键查询条目比较少时，不用回行（数据就在主键节点下）

劣势: 如果碰到不规则数据插入时，造成频繁的页分裂，插入速度变慢

高性能索引策略 ：对于innodb而言，因为节点下有数据文件，因此节点的分裂将会比较慢。因此对于innodb的主键尽量用整型，而且是递增的整型，如果是无规律的数据，将会产生的页的分裂，影响速度。

二、索引覆盖：

索引覆盖：是指如果查询的列恰好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行到磁盘再找数据，这种查询速度非常快，这个现象称为“索引覆盖”。

三、索引覆盖实验：

create table A {

id varchar(64) primary key,

ver int,

...

}

条件：在id、ver上有联合索引，表中有几个很长的字段，总共100000条数据

问题：为什么select id from A order by id特别慢？而select id from A order by id,ver特别快？

原因：

1.如果是myisam引擎的话，会将id和ver都存放在索引文件中，所以order by id和order by id,ver不会出现速度上的差别，两次都产生索引覆盖，所以判断引擎为innodb；

2.由于innodb是聚簇索引，主索引id文件上，存放了该行的数据，当表中某个字段的数据很大时，在硬盘上一个数据块所能存放的行数就变少，所以数据块变多。当order by id时，会扫描很多个不同的数据块，导致性能降低。而order by id,ver为联合索引（次索引），次索引不用扫描很大的数据量，并且只筛选id，产生索引覆盖，所以速度快很多。

实验步骤：

1.首先查看是否开启profiling功能：SHOW VARIABLES LIKE '%pro%';

2.开启profiling：SET profiling=1;

3.查看sql语句执行结果：SHOW profiles;

4.建立数据表：

CREATE TABLE `t7` (

`id` char(64) NOT NULL,

`ver` int(11) NOT NULL DEFAULT '0',

`str1` varchar(3000) DEFAULT NULL,

`str2` varchar(3000) DEFAULT NULL,

`str3` varchar(3000) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idver` (`id`,`ver`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `t8` (

`id` char(64) NOT NULL,

`ver` int(11) NOT NULL DEFAULT '0',

`str1` varchar(3000) DEFAULT NULL,

`str2` varchar(3000) DEFAULT NULL,

`str3` varchar(3000) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `idver` (`id`,`ver`)

) ENGINE=innodb DEFAULT CHARSET=utf8;

5.创建php文件批量插入数据：

$mysqli = new mysqli("127.0.0.1", "root", "", "test");

$mysqli->query("set names utf8");

$str = str_repeat('m', 3000);

for ($i=1;$i<=10000;$i++) {

$id = dechex($i);

$sql = sprintf("insert into t8 values ('%s',%d,'%s','%s','%s')", $i,$i,$str,$str,$str);

$mysqli->query($sql);

}

echo "insert success";

$mysqli->close();

?>

6.分别执行t7和t8，查看sql语句执行结果：

转载于:https://my.oschina.net/u/1169535/blog/884834