Mysql入门[MySQL FAQ]系列 — MySQL联合索引是否支持不同排序规
|
《Mysql入门[MySQL FAQ]系列 — MySQL联合索引是否支持不同排序规则》要点： 先来了解下MySQL关于索引的一些基础知识要点：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,col3 DESC),这里的DESC只是个预留的关键字,目前还不能真正有作用
? g、联合索引中,如果查询条件中最左边某个索引列使用范围查找,则只能使用前缀索引,无法使用到整个索引；
例如：SELECT * FROM t WHERE col1 = ? AND col2 >= ? AND col3 = ?; 这时候,只能用到 idx 索引的最左2列进行检索,而col3条件则无法利用索引进行检索
? h、InnoDB引擎中,二级索引实际上包含了主键索引值；
关于 key_len 的计算规则：MYSQL必读
? 当索引字段为定长数据类型,比如：char,int,datetime,需要有是否为空的标记,这个标记需要占用1个字节；
? 当索引字段为变长数据类型,比如：varchar,除了是否为空的标记外,还需要有长度信息,需要占用2个字节；
? 当字段定义为非空的时候,是否为空的标记将不占用字节；
? 同时还需要考虑表所使用字符集的差异,latin1编码一个字符1个字节,gbk编码一个字符2个字节,utf8编码一个字符3个字节；
因此,key_len长度的计算公式MYSQL必读
? varchr(10)变长字段且允许NULL : 10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL标记位)+2(变长字段)
? varchr(10)变长字段且不允许NULL : 10*(Character Set：utf8=3,latin1=1)+2(变长字段)
? char(10)固定字段且允许NULL : 10*(Character Set：utf8=3,latin1=1)+1(NULL标记位)
? char(10)固定字段且不允许NULL : 10*(Character Set：utf8=3,latin1=1)
附,关于?filesort?排序算法： 对于filesort,MySQL有两种排序算法： 2、一次扫描算法(single pass) 注：从 MySQL 4.1 版本开始支持该算法.它减少了I/O的次数,效率较高,但是内存开销也较大.如果我们将并不需要的列也取出来,就会极大地浪费排序过程所需要的内存.在 MySQL 4.1 之后的版本中,可以通过设置?max_length_for_sort_data?参数来控制 MySQL 选择第一种排序算法还是第二种.当取出的所有大字段总大小大于?max_length_for_sort_data?的设置时,MySQL 就会选择使用第一种排序算法,反之,则会选择第二种.为了尽可能地提高排序性能,我们自然更希望使用第二种排序算法,所以在SQL中仅仅取出需要的列是非常有必要的.MYSQL必读 当对连接操作进行排序时,如果ORDER BY仅仅引用第一个表的列,MySQL对该表进行filesort操作,然后进行连接处理,此时,EXPLAIN输出“Using filesort”；否则,MySQL必须将查询的结果集生成一个临时表,在连接完成之后进行filesort操作,255);">Using temporary;Using filesort”.MYSQL必读 后面是几个几个测试结果,有兴趣不怕累的可以看看,哈哈.MYSQL必读 测试MySQL版本：5.5.37-log MySQL Community Server (GPL)MYSQL必读 #创建一个测试表,id是主键字段,(a1,a2) 组成联合索引MYSQL必读
(yejr@imysql.com)> show create table tG
*************************** 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条测试数据MYSQL必读
(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两个字段,可以直接使用该联合索引取回结果,并且排序完成
(yejr@imysql.com)> explain select a1,a2 from t order by a1,a2G
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字段,并且排序完成 #对 a1 正序排序,对 a2 倒序排序,但排序时需要进行filesort排序,不能利用索引直接得到排序结果 #只取a1字段,同时只对 a1 字段正序排序,这时可用联合索引取得结果,同时也可以利用前缀索引的原则进行排序 |