mysql优化-聚簇索引，索引覆盖的使用案例

 

 

 

 

表smth中有10000行数据，有两个3000长度的字段；

select id  from smth order by id ;很慢

select id from  smth order by id,ver;比较快

原因？

 

 

create  table smth(

id int(11) not null default 0,

ver int(11) default null,

content varchar(3000)  default null,

intro varchar(3000) default null,

primary key (id),

key idver(id,ver)

)engine =InnoDB default charset=utf8;



create  table mysmth(

id int(11) not null default 0,

ver int(11) default null,

content varchar(3000)  default null,

intro varchar(3000) default null,

primary key (id),

key idver(id,ver)

)engine =myisam default charset=utf8;

 

delimiter $$

create procedure myproc ()

begin

declare num int;

set num=1;

while num< 10001 do

insert into smth(id,ver,content,intro)

values

(num,num+200,concat('name',num),concat('in',num));

set num=num+1;

end

while;

end$$

 

生成10000行数据；

 

call myproc()$$

 

 

mysql> show create table smth;

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                                          |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| smth  | CREATE TABLE `smth` (

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

  `ver` int(11) DEFAULT NULL,

  `content` varchar(3000) DEFAULT NULL,

  `intro` varchar(3000) DEFAULT NULL,

  PRIMARY KEY (`id`),

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

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set

mysql> select count(1) from  smth;

+----------+

| count(1) |

+----------+

|    10000 |

+----------+

1 row in set

 

打开profilling

mysql> set profiling =1;

Query OK, 0 rows affected







mysql> show profiles;

+----------+------------+--------------------------------------+

| Query_ID | Duration   | Query                                |

+----------+------------+--------------------------------------+

|        1 | 0.01072775 | select id  from smth order by id     |

|        2 |   0.036068 | select id from  smth order by id,ver |

+----------+------------+--------------------------------------+

3 rows in set











mysql> show profile for  query 1;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000122 |

| checking permissions | 2.7E-5   |

| Opening tables       | 3.1E-5   |

| init                 | 2.8E-5   |

| System lock          | 1.5E-5   |

| optimizing           | 6E-6     |

| statistics           | 2.3E-5   |

| preparing            | 3.1E-5   |

| Sorting result       | 8E-6     |

| executing            | 3E-6     |

| Sending data         | 0.009794 |

| end                  | 8.2E-5   |

| query end            | 1.8E-5   |

| closing tables       | 1.2E-5   |

| freeing items        | 0.000498 |

| cleaning up          | 3.4E-5   |

+----------------------+----------+

16 rows in set





mysql> show profile for  query 2;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000116 |

| checking permissions | 1E-5     |

| Opening tables       | 6.1E-5   |

| init                 | 2.5E-5   |

| System lock          | 1.1E-5   |

| optimizing           | 4E-6     |

| statistics           | 1.6E-5   |

| preparing            | 1.1E-5   |

| Sorting result       | 3E-6     |

| executing            | 2E-6     |

| Sending data         | 0.009261 |

| end                  | 1.3E-5   |

| query end            | 2.7E-5   |

| closing tables       | 1.1E-5   |

| freeing items        | 0.000354 |

| cleaning up          | 0.026144 |

+----------------------+----------+

16 rows in set

 

涉及的问题

1）：innodb，myisam索引的各自的指向问题；

myisam的索引指向的是数据在磁盘的位置

innodb的索引指向的是主键值的引用

innodb没有连续的数据块，数据文件

 

 

 

2）聚簇索引

1.聚簇索引并不是一种单独所以，而是一种数据储存方式。

InnoDB 的聚簇索引实际上在同一结构中保存了B-Tree 索引和数据行。

2.当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。“聚簇”表示数据行和相邻的键值紧凑的储存在一起。

3.对应InnoDB 来说如果表没有定义主键，会选择一个唯一的非空索引代替。如果没有这样的索引InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一页面中的记录。

4.聚簇索引的优势：

（1）可以把相关数据保存在一起。

（2）数据访问更快。数据和索引保存在同一个 B-Tree 。

（3）使用覆盖索引扫描的查询可以直接使用页节点的主键值

 

5.聚簇索引的缺点：

(1)聚簇索引最大的提高了I/O密集型应用的性能，但如果数据全部都放到内存中，则数据的顺序就没有那么重要了，聚簇索引也就没什么优势了。

 

（2）插入速度严重依赖插入顺序。按照主键插入的方式是InnoDB 速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载后最好使用OPTIMIZE TABLE 命令重新组织一2下表

（3）更新聚簇索引列的代价很高。因为会强制InnoDB 将每个被更新的行移动到新的位置

3） 覆盖索引

 

1.mysql 可以使用索引直接来获取列的数据，这样就可以不再需要读取数据行。

如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？如果一个索引包含（覆盖）所有要查询的字段的值，那么就称为“覆盖索引”

2.覆盖索引可以提高查询的性能，不需要会表，好处是：

（1）索引条目通常小于数据行，如果只需读取索引，那么mysql 就会减少访问量

（2）索引是按照列值顺序存储的，索引I/O 密集型的范围查询会比随机从磁盘读取每一行数据的I/O 要少得多

（3）一些存储引擎如MyISAM 在内存只缓存索引，数据则依赖操作系统来缓存，因此要访问数据需要一次系统调用，这可能导致严重的性能问题，尤其是那些系统调用占了数据访问中最大开销的场景

（4)InnoDB 的聚簇索引，覆盖索引对InnoDB 表的特别有用。InnoDB 的二级索引在叶子节点保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

 

3

select id from order where user_id between 1 and 3

这时候只需要查ID 的值，而ID 已经在user_id 索引树上，因此可以直接提供查询结果，不需要回表。

 

select * from order where user_id between 1 and 3

一旦用了select *，就会有其他列需要读取，这时在读完index以后还需要去读data才会返回结果。

 

这两种处理方式性能差异非常大，特别是返回行数比较多，并且读数据需要 I/O 的时候，可能会有几十上百倍的差异。因此建议根据需要用select *