-
聚簇索引概念
innodb的主索引文件上，直接存放该行数据，称为聚簇索引，次索引指向对主键的引用
myisam中，主索引和次索引都指向物理行对innodb来说，
1.主索引文件，既存储索引值，又在叶子中存储行的数据
2.如果没有主键，则会unique key做主键
3.如果没有unique，则系统生成一个内部的rowid做主键
4.像innodb中，主键的索引文件，既存储了主键值，又存储了行数据，这种结构称为“聚簇索引”
-
聚簇索引随机主键值的效率
对innodb而言，因为节点下有数据文件，因此节点的分裂将会比较慢
对于innodb的主键，尽量用递增的整型
如果是无规律的数据，将会产生叶的分裂，影响速度
测试
规律的插入数据
[root@localhost 192.168.6.133]# cat /www/wwwroot/192.168.6.133/innodb/grow.php
<?php
/*
CREATE TABLE `split` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
*/
set_time_limit(0);
$conn = mysqli_connect('127.0.0.1','root','123456');
mysqli_query($conn,'use test');
mysqli_query($conn,'set names utf8');
$str = 'abcdefghijklmnopqrstuvwxyz0123456789';
$str = str_shuffle($str).str_shuffle($str).str_shuffle($str);
$str = $str.$str;
$arr = range(1,1000001,1000);
shuffle($arr);
$start = microtime_float();
foreach($arr as $i){
$sql = sprintf("insert into split1 values(%d,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",$i,$str,$str,$str,$str,$str,$str,$str,$str,$str,$str);
// echo $sql."<br/>";
if(!mysqli_query($conn,$sql)){
echo $i."failure<br/>";
}
}
$end = microtime_float();
echo "随机插入1K行的时间为",$end - $start,"秒\n";
mysqli_close($conn);
function microtime_float(){
list($usec,$sec) = explode(" ",microtime());
return ((float) $usec + (float) $sec);
}
[root@localhost 192.168.6.133]# /usr/bin/php /www/wwwroot/192.168.6.133/innodb/grow.php
随机插入1K行的时间为28.376071929932秒
无规律的插入数据
[root@localhost 192.168.6.133]# cat /www/wwwroot/192.168.6.133/innodb/rand.php
<?php
/*
CREATE TABLE `split` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
*/
set_time_limit(0);
$conn = mysqli_connect('127.0.0.1','root','123456');
mysqli_query($conn,'use test');
mysqli_query($conn,'set names utf8');
$str = 'abcdefghijklmnopqrstuvwxyz0123456789';
$str = str_shuffle($str).str_shuffle($str).str_shuffle($str);
$str = $str.$str;
$start = microtime_float();
for($i = 1;$i<=1000;$i++){
$sql = sprintf("insert into split2 values(%d,'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",$i,$str,$str,$str,$str,$str,$str,$str,$str,$str,$str);
// echo $sql."<br/>";
if(!mysqli_query($conn,$sql)){
echo $i."failure<br/>";
}
}
$end = microtime_float();
echo "随机插入1K行的时间为",$end - $start,"秒\n";
mysqli_close($conn);
function microtime_float(){
list($usec,$sec) = explode(" ",microtime());
return ((float) $usec + (float) $sec);
}
[root@localhost 192.168.6.133]# /usr/bin/php /www/wwwroot/192.168.6.133/innodb/rand.php
随机插入1K行的时间为42.948094129562秒
可以看到乱序插入数据时间更长
- 索引覆盖
索引覆盖是指，如果查询的列恰好是索引的一部分，那么查询只需要在文件上进行，不需要回行到磁盘上找数据
这种查询速度非常快，称为覆盖索引
测试
mysql> set profiling=1;
mysql> explain select c1 from t5 ;
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | t5 | index | NULL | c1234 | 4 | NULL | 3 | Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+
mysql> explain select c5 from t5 ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t5 | ALL | NULL | NULL | NULL | NULL | 3 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
mysql> show profiles;
+----------+------------+---------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------+
| 1 | 0.00016650 | explain select c5 from t5 |
| 2 | 0.00011200 | explain select c1 from t5 |
+----------+------------+---------------------------+
Extra出现Using index，说明使用了覆盖索引，可以看到使用覆盖索引的查询速度更快
