一、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语句执行结果：
网友评论