标签归档:索引

FAQ系列 | B+树索引和哈希索引的区别

导读

在MySQL里常用的索引数据结构有B+树索引和哈希索引两种,我们来看下这两种索引数据结构的区别及其不同的应用建议。

二者区别

备注:先说下,在MySQL文档里,实际上是把B+树索引写成了BTREE,例如像下面这样的写法:

CREATE TABLE t(
aid int unsigned not null auto_increment,
userid int unsigned not null default 0,
username varchar(20) not null default ‘’,
detail varchar(255) not null default ‘’,
primary key(aid),
unique key(uid) USING BTREE,
key (username(12)) USING BTREE此处 uname 列只创建了最左12个字符长度的部分索引
)engine=InnoDB;

一个经典的B+树索引数据结构见下图:
20160106B树索引
(图片源自网络)

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接。

在B+树上的常规检索,从根节点到叶子节点的搜索效率基本相当,不会出现大幅波动,而且基于索引的顺序扫描时,也可以利用双向指针快速左右移动,效率非常高。

因此,B+树索引被广泛应用于数据库、文件系统等场景。顺便说一下,xfs文件系统比ext3/ext4效率高很多的原因之一就是,它的文件及目录索引结构全部采用B+树索引,而ext3/ext4的文件目录结构则采用Linked list, hashed B-tree、Extents/Bitmap等索引数据结构,因此在高I/O压力下,其IOPS能力不如xfs。

详细可参见:

https://en.wikipedia.org/wiki/Ext4
https://en.wikipedia.org/wiki/XFS

哈希索引的示意图则是这样的:
20160106哈希索引
(图片源自网络)

简单地说,哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。

从上面的图来看,B+树索引和哈希索引的明显区别是:

  • 如果是等值查询,那么哈希索引明显有绝对优势,因为只需要经过一次算法即可找到相应的键值;当然了,这个前提是,键值都是唯一的。如果键值不是唯一的,就需要先找到该键所在位置,然后再根据链表往后扫描,直到找到相应的数据;
  • 从示意图中也能看到,如果是范围查询检索,这时候哈希索引就毫无用武之地了,因为原先是有序的键值,经过哈希算法后,有可能变成不连续的了,就没办法再利用索引完成范围查询检索;
  • 同理,哈希索引也没办法利用索引完成排序,以及like ‘xxx%’ 这样的部分模糊查询(这种部分模糊查询,其实本质上也是范围查询);
  • 哈希索引也不支持多列联合索引的最左匹配规则
  • B+树索引的关键字检索效率比较平均,不像B树那样波动幅度大,在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题

后记

在MySQL中,只有HEAP/MEMORY引擎表才能显式支持哈希索引(NDB也支持,但这个不常用),InnoDB引擎的自适应哈希索引(adaptive hash index)不在此列,因为这不是创建索引时可指定的。

还需要注意到:HEAP/MEMORY引擎表在mysql实例重启后,数据会丢失。

通常,B+树索引结构适用于绝大多数场景,像下面这种场景用哈希索引才更有优势:

在HEAP表中,如果存储的数据重复度很低(也就是说基数很大),对该列数据以等值查询为主,没有范围查询、没有排序的时候,特别适合采用哈希索引

例如这种SQL:
SELECT … FROM t WHERE C1 = ?; — 仅等值查询

在大多数场景下,都会有范围查询、排序、分组等查询特征,用B+树索引就可以了。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠

FAQ系列 | MySQL索引之主键索引

导读

在MySQL里,主键索引和辅助索引分别是什么意思,有什么区别?

上次的分享我们介绍了聚集索引和非聚集索引的区别,本次我们继续介绍主键索引和辅助索引的区别。

1、主键索引

主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录。一个表可以没有主键,但最多只能有一个主键,并且主键值不能包含NULL。

在MySQL中,InnoDB数据表的主键设计我们通常遵循几个原则:

  1. 采用一个没有业务用途的自增属性列作为主键;
  2. 主键字段值总是不更新,只有新增或者删除两种操作;
  3. 不选择会动态更新的类型,比如当前时间戳等。

这么做的好处有几点:

  1. 新增数据时,由于主键值是顺序增长的,innodb page发生分裂的概率降低了;可以参考以往的分享“[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键”;
  2. 业务数据有变更时,不修改主键值,物理存储位置发生变化的概率降低了,innodb page中产生碎片的概率也降低了。

MyISAM表因为是堆组织表,主键类型设计方面就可以不用这么讲究了。

2、辅助索引

辅助索引,就是我们常规所指的索引,原文是SECONDARY KEY。辅助索引里还可以再分为唯一索引非唯一索引

唯一索引其实应该叫做唯一性约束,它的作用是避免一列或多列值存在重复,是一种约束性索引。

3、主键索引和辅助索引的区别

在MyISAM引擎中,唯一索引除了key值允许存在NULL外,其余的和主键索引没有本质性区别。也就是说,在MyISAM引擎中,不允许存在NULL值的唯一索引,本质上和主键索引是一回事

而在InnoDB引擎中,主键索引和辅助索引的区别就很大了。主键索引会被选中作为聚集索引,而唯一索引和普通辅助索引间除了唯一性约束外,在存储上没本质区别

从查询性能上来说,在MyISAM表中主键索引和不允许有NULL的唯一索引的查询性能是相当的在InnoDB表通过唯一索引查询则需要多一次从辅助索引到主键索引的转换过程InnoDB表基于普通索引的查找代价更高,因为每次检索到结果后,还需要至少再多检索一次才能确认是否还有更多符合条件的结果,主键索引和唯一索引就不需要这么做了。

经过测试,对100万行数据的MyISAM做随机检索(整数类型),主键和唯一索引的效率基本一样,普通索引的检索效率则慢了30%以上。换成InnoDB表的话,唯一索引比主键索引效率约慢9%,普通索引比主键索引约慢了50%以上。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠

 

FAQ系列 | MySQL索引之聚集索引

导读

在MySQL里,聚集索引和非聚集索引分别是什么意思,有什么区别?

在MySQL中,InnoDB引擎表是(聚集)索引组织表(clustered index organize table),而MyISAM引擎表则是堆组织表(heap organize table)。

也有人把聚集索引称为聚簇索引。

当然了,聚集索引的概念不是MySQL里特有的,其他数据库系统也同样有。

简言之,聚集索引是一种索引组织形式,索引的键值逻辑顺序决定了表数据行的物理存储顺序,而非聚集索引则就是普通索引了,仅仅只是对数据列创建相应的索引,不影响整个表的物理存储顺序。

我们先来看看两种存储形式的不同之处:
简单说,IOT表里数据物理存储顺序和主键索引的顺序一致,所以如果新增数据是离散的,会导致数据块趋于离散,而不是趋于顺序。而HOT表数据写入的顺序是按写入时间顺序存储的。
IOT表相比HOT表的优势是:

  • 范围查询效率更高;
  • 数据频繁更新(聚集索引本身不更新)时,更不容易产生碎片;
  • 特别适合有一小部分热点数据频繁读写的场景;
  • 通过主键访问数据时快速可达;

IOT表的不足则有:

  • 数据变化如果是离散为主的话,那么效率会比HOT表差;

HOT表的不足有:

  • 索引回表读开销很大;
  • 大部分数据读取时随机的,无法保证被顺序读取,开销大;

每张InnoDB表只能创建一个聚集索引,聚集索引可以由一列或多列组成。

上面说过,InnoDB是聚集索引组织表,它的聚集索引选择规则是这样的:

  1. 首先选择显式定义的主键索引做为聚集索引;
  2. 如果没有,则选择第一个不允许NULL的唯一索引;
  3. 还是没有的话,就采用InnoDB引擎内置的ROWID作为聚集索引;

我们来看看InnoDB主键索引的示意图:

图片来自高性能MySQL

可以看到,在这个索引结构的叶子节点中,节点key值是主键的值,而节点的value则存储其余列数据,以及额外的ROWID、rollback pointer、trx id等信息。

结合这个图,以及上面所述,我们可以知道:在InnoDB表中,其聚集索引相当于整张表,而整张表也是聚集索引。主键必然是聚集索引,而聚集索引则未必是主键

MyISAM是堆组织表,它没有聚集索引的概念。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠

 

[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入速度的影响

本次,我们来看看索引、提交频率对InnoDB表写入速度的影响,了解有哪些需要注意的。

先直接说几个结论吧:

1、关于索引对写入速度的影响:
a、如果有自增列做主键,相对完全没索引的情况,写入速度约提升 3.11%;
b、如果有自增列做主键,并且二级索引,相对完全没索引的情况,写入速度约降低 27.37%;

因此,InnoDB表最好总是有一个自增列做主键。

2、关于提交频率对写入速度的影响(以表中只有自增列做主键的场景,一次写入数据30万行数据为例):

a、等待全部数据写入完成后,最后再执行commit提交的效率最高;
b、每10万行提交一次,相对一次性提交,约慢了1.17%;
c、每1万行提交一次,相对一次性提交,约慢了3.01%;
d、每1千行提交一次,相对一次性提交,约慢了23.38%;
e、每100行提交一次,相对一次性提交,约慢了24.44%;
f、每10行提交一次,相对一次性提交,约慢了92.78%;
g、每行提交一次,相对一次性提交,约慢了546.78%,也就是慢了5倍;

因此,最好是等待所有事务结束后再批量提交,而不是每执行完一个SQL就提交一次。
曾经有一次对比测试mysqldump启用extended-insert和未启用导出的SQL脚本,后者比前者慢了不止5倍。
重要:这个建议并不是绝对成立的,要看具体的场景。如果是一个高并发的在线业务,就需要尽快提交事务,避免锁范围被扩大。但如果是在非高并发的业务场景,尤其是做数据批量导入的场景下,就建议采用批量提交的方式。

下面是详细的测试案例过程,有兴趣的同学可以看看:

DROP TABLE IF EXISTS `mytab`;
CREATE TABLE `mytab` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL DEFAULT ‘0’,
`c2` int(11) NOT NULL DEFAULT ‘0’,
`c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`c4` varchar(200) NOT NULL DEFAULT ”,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DELIMITER $$$
DROP PROCEDURE IF EXISTS `insert_mytab`;

CREATE PROCEDURE `insert_mytab`(in rownum int, in commitrate int)
BEGIN
DECLARE i INT DEFAULT 0;

SET AUTOCOMMIT = 0;

WHILE i < rownum DO INSERT INTO mytab(c1, c2, c3,c4) VALUES( FLOOR(RAND()*rownum),FLOOR(RAND()*rownum),NOW(), REPEAT(CHAR(ROUND(RAND()*255)),200)); SET i = i+1; /* 达到每 COMMITRATE 频率时提交一次 */ IF (commitrate > 0) AND (i % commitrate = 0) THEN
COMMIT;
SELECT CONCAT(‘commitrate: ‘, commitrate, ‘ in ‘, I);
END IF;

END WHILE;

/* 最终再提交一次,确保成功 */
COMMIT;
SELECT ‘ALL COMMIT;’;

END; $$$

#测试调用
call insert_mytab(300000, 1); — 每次一提交
call insert_mytab(300000, 10); — 每10次一提交
call insert_mytab(300000, 100); — 每100次一提交
call insert_mytab(300000, 1000); — 每1千次一提交
call insert_mytab(300000, 10000); — 每1万次提交
call insert_mytab(300000, 100000); — 每10万次一提交
call insert_mytab(300000, 0); — 一次性提交

测试耗时结果对比:
mysql-optimization-case-how-index-and-commit-rate-affect-innodb-insert

[MySQL FAQ]系列 — MySQL联合索引是否支持不同排序规则

篇首语:
截止到目前的5.7.4版本为止,MySQL的联合索引仍无法支持联合索引使用不同排序规则,例如:ALTER TABLE t ADD INDEX idx(col1, col2 DESC)。

先来了解下MySQL关于索引的一些基础知识要点:

• a、EXPLAIN结果中的key_len只显示了条件检索子句需要的索引长度,但 ORDER BY、GROUP BY 子句用到的索引则不计入 key_len 统计值;
• b、联合索引(composite index):多个字段组成的索引,称为联合索引;
例如:ALTER TABLE t ADD INDEX `idx` (col1, col2, col3)
• c、覆盖索引(covering index):如果查询需要读取到索引中的一个或多个字段,则可以从索引树中直接取得结果集,称为覆盖索引;
例如:SELECT col1, col2 FROM t;
• d、最左原则(prefix index):如果查询条件检索时,只需要匹配联合索引中的最左顺序一个或多个字段,称为最左索引原则,或者叫最左前缀;
例如:SELECT * FROM t WHERE col1 = ? AND col2 = ?;
• e、在老版本(大概是5.5以前,具体版本号未确认核实)中,查询使用联合索引时,可以不区分条件中的字段顺序,在这以前是需要按照联合索引的创建顺序书写SQL条件子句的;
例如:SELECT * FROM t WHERE col3 = ? AND col1 = ? AND col2 = ?;
• f、MySQL截止目前还只支持多个字段都是正序索引,不支个别字段持倒序索引;
例如:ALTER TABLE t ADD INDEX `idx` (col1, col2, col3 DESC),这里的DESC只是个预留的关键字,目前还不能真正有作用
• g、联合索引中,如果查询条件中最左边某个索引列使用范围查找,则只能使用前缀索引,无法使用到整个索引;
例如:SELECT * FROM t WHERE col1 = ? AND col2 >= ? AND col3 = ?; 这时候,只能用到 idx 索引的最左2列进行检索,而col3条件则无法利用索引进行检索
• h、InnoDB引擎中,二级索引实际上包含了主键索引值;

关于 key_len 的计算规则:

• 当索引字段为定长数据类型,比如:char,int,datetime,需要有是否为空的标记,这个标记需要占用1个字节;
• 当索引字段为变长数据类型,比如:varchar,除了是否为空的标记外,还需要有长度信息,需要占用2个字节;
• 当字段定义为非空的时候,是否为空的标记将不占用字节;
• 同时还需要考虑表所使用字符集的差异,latin1编码一个字符1个字节,gbk编码一个字符2个字节,utf8编码一个字符3个字节;

因此,key_len长度的计算公式

• varchr(10)变长字段且允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL标记位)+2(变长字段)
• varchr(10)变长字段且不允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)+2(变长字段)
• char(10)固定字段且允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL标记位)
• char(10)固定字段且不允许NULL : 10*(Character Set:utf8=3,gbk=2,latin1=1)

附,关于 filesort 排序算法:
光看 filesort 字面意思,可能以为是要利用磁盘文件进行排序,实则不全然。
当MySQL不能使用索引进行排序时,就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序,如果内存装载不下,它会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个块合并成有序的结果集(实际上就是外排序)。

对于filesort,MySQL有两种排序算法:
1、两遍扫描算法(Two passes)
实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出,然后在设定的内存(通过参数 sort_buffer_size 设定)中进行排序,完成排序之后再次通过行指针信息取出所需的列。
注:该算法是4.1之前只有这种算法,它需要两次访问数据,尤其是第二次读取操作会导致大量的随机I/O操作。不过,这种方法内存开销较小。

2、一次扫描算法(single pass)
该算法一次性将所需的列全部取出,在内存中排序后直接将结果输出。

注:从 MySQL 4.1 版本开始支持该算法。它减少了I/O的次数,效率较高,但是内存开销也较大。如果我们将并不需要的列也取出来,就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中,可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时,MySQL 就会选择使用第一种排序算法,反之,则会选择第二种。为了尽可能地提高排序性能,我们自然更希望使用第二种排序算法,所以在SQL中仅仅取出需要的列是非常有必要的。

当对连接操作进行排序时,如果ORDER BY仅仅引用第一个表的列,MySQL对该表进行filesort操作,然后进行连接处理,此时,EXPLAIN输出“Using filesort”;否则,MySQL必须将查询的结果集生成一个临时表,在连接完成之后进行filesort操作,此时,EXPLAIN输出“Using temporary;Using filesort”。

后面是几个几个测试结果,有兴趣不怕累的可以看看,哈哈。

测试MySQL版本:5.5.37-log MySQL Community Server (GPL)

#创建一个测试表,id是主键字段,(a1, a2) 组成联合索引

(yejr@imysql.com)> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`a1` int(10) unsigned NOT NULL DEFAULT '0',
`a2` int(10) unsigned NOT NULL DEFAULT '0',
`aa` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx` (`a1`,`a2`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8

#填充了64条测试数据

(yejr@imysql.com)> show table status like 't'\G
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 64
Avg_row_length: 256
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 122
Create_time: 2014-09-15 17:17:09
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:

#对 a1、a2 正序排序,同时取a1、a2两个字段,可以直接使用该联合索引取回结果,并且排序完成
#符合规则c

(yejr@imysql.com)> explain select a1, a2 from t order by a1, a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1、a2 倒序排序,同时取a1、a2两个字段,可以直接使用该联合索引取回结果,并且排序完成
#由于同时对a1、a2都是倒序排序,因此完全可以用到索引的顺序,只是反向扫描而已
#符合规则c

(yejr@imysql.com)> explain select a1, a2 from t order by a1 desc, a2 desc\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1、a2正序排序,只取a1字段,可以直接使用该联合索引取回结果,并且排序完成
#匹配规则c

(yejr@imysql.com)> explain select a1 from t order by a1, a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1、a2 正序排序,只取a2字段,可以直接使用该联合索引取回结果,并且排序完成
#符合规则c

(yejr@imysql.com)> explain select a2 from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#只对 a1 正序排序,同时取a1、a2两个字段,可以直接使用该联合索引取回结果,并且排序完成
#符合规则c

(yejr@imysql.com)> explain select a1, a2 from t order by a1\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1 正序排序,对 a2 倒序排序,只取a1字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#只取a1字段,同时只对 a1 字段正序排序,这时可用联合索引取得结果,同时也可以利用前缀索引的原则进行排序
#符合规则c

(yejr@imysql.com)> explain select a1 from t order by a1\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#只取a1字段,同时只对 a2 字段正序排序,这时虽然可用联合索引取得结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a2\G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 正序排序,对a2 倒序排序,只取a1字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 正序排序,对a2 倒序排序,只取a2字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a2 from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 正序排序,对a2 倒序排序,只取a2字段,可以直接使用该联合索引取回结果,但排序时需要进行filesort排序,不能利用索引直接得到排序结果
#这时虽然只读取一个字段,但实际还是扫描了整个索引,并非使用前缀索引
#符合规则c、f

(yejr@imysql.com)> explain select a1 from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1 、a2顺序排序,取得主键id字段,可以直接使用该联合索引取回结果并完成排序。
#这里需要注意下,二级索引其实是包括主键索引的,因此用idx索引即可取到全部结果。
#下面这个SQL也是一样的效果:select a1,a2,id from t order by a1, a2;
#符合规则c、h

(yejr@imysql.com)> explain select id from t order by a1, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index

#对 a1 正序排序,对a2 倒序排序,取得主键id字段,可以直接使用该联合索引取回结果,但需要进行filesort排序。
#符合规则c、f、h

(yejr@imysql.com)> explain select id from t order by a1, a2 desc \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#对 a1 倒序排序,对a2 正序排序,取得主键id字段,可以直接使用该联合索引取回结果,但需要进行filesort排序。
#符合规则c、f、h

(yejr@imysql.com)> explain select id from t order by a1 desc, a2 \G
id: 1
select_type: SIMPLE
table: t
type: index
possible_keys: NULL
key: idx
key_len: 8
ref: NULL
rows: 64
Extra: Using index; Using filesort

#过滤条件a1字段(使用前缀索引扫描,key_len为4),对a2字段进行正序排序,取得主键id字段,可以直接使用联合索引取回结果
#符合规则a、c、d、h

(yejr@imysql.com)> explain select id from t where a1 = 219 order by a2\G
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: idx
key: idx
key_len: 4
ref: const
rows: 2
Extra: Using where; Using index

个人PPT分享

个人最近几年内整理过的PPT,都放在百度文库上了,大家可以看看 :)

M​y​S​Q​L​ ​t​p​c​h​测​试​工​具​简​要​手​册

高​效​L​i​n​u​x​ ​S​A​

P​C​服​务​器​阵​列​卡​管​理​简​易​手​册​

服​务​器​基​准​测​试

M​y​S​Q​L​数​据​库​设​计​、​优​化 

M​y​S​Q​L​之​设​计​、​优​化​、​运​维

 

[MySQL优化案例]系列 — 典型性索引引发CPU负载飙升问题

收到一个mysql服务器负载告警,上去一看,load average都飙到280多了,用top一看,CPU跑到了336%,不过IO和内存的负载并不高,根据经验,应该又是一起索引引起的惨案了。

看下processlist以及slow query情况,发现有一个SQL经常出现,执行计划中的扫描记录数看着还可以,单次执行耗时为0.07s,还不算太大。乍一看,可能不是它引发的,但出现频率实在太高,而且执行计划看起来也不够完美:

mysql> explain SELECT count(1) FROM a , b WHERE a.id = b.video_id and b.state = 1 AND b.column_id = ’81’\G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
type: index_merge
possible_keys: columnid_videoid,column_id,state,video_time_stamp,idx_videoid
key: column_id,state
key_len: 4,4
ref: NULL
rows: 100
Extra: Using intersect(column_id,state); Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: b.video_id
rows: 1
Extra: Using where; Using index

再看下该表的索引情况:

mysql> show index from b\G

*************************** 1. row ***************************
Table: b
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 167483
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: b
Non_unique: 1
Key_name: column_id
Seq_in_index: 1
Column_name: column_id
Collation: A
Cardinality: 8374
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: b
Non_unique: 1
Key_name: state
Seq_in_index: 2
Column_name: state
Collation: A
Cardinality: 5
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:

可以看到执行计划中,使用的是index merge,效率自然没有用联合索引(也有的叫做覆盖索引)来的好了,而且 state 字段的基数(唯一性)太差,索引效果很差。删掉两个独立索引,修改成联合看看效果如何:

mysql> show index from b;

*************************** 1. row ***************************
Table: b
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 128151
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: b
Non_unique: 1
Key_name: idx_columnid_state
Seq_in_index: 1
Column_name: column_id
Collation: A
Cardinality: 3203
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: b
Non_unique: 1
Key_name: idx_columnid_state
Seq_in_index: 2
Column_name: state
Collation: A
Cardinality: 3463
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:

mysql> explain SELECT count(1) FROM a , b WHERE a.id = b.video_id and b.state = 1  AND b.column_id = ’81’ \G

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: columnid_videoid,idx_videoid,idx_columnid_state
key: columnid_videoid
key_len: 4
ref: const
rows: 199
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: b.video_id
rows: 1
Extra: Using where; Using index

 可以看到执行计划变成了只用到了 idx_columnid_state 索引,而且 ref 类型也变成了 const,SQL执行耗时也从0.07s变成了0.00s,相应的CPU负载也从336%突降到了12%不到。

总结下,从多次历史经验来看,如果CPU负载持续很高,但内存和IO都还好的话,这种情况下,首先想到的一定是索引问题,十有八九错不了。