Mysql索引小结

建表语句：

CREATE TABLE `t_user` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`bu_id` INT(20) NOT NULL,
	`name` VARCHAR(255) NOT NULL,
	`age` INT(11) NOT NULL,
	`sex` VARCHAR(255) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `index_uid_name_age` (`bu_id`, `name`, `age`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=22014
;

批量插入数据：

CREATE PROCEDURE batch_insert() 
BEGIN 
	DECLARE a INT DEFAULT 1; 

	WHILE (a <= 10000) DO 
	SET a = a + 1; 
	insert into t_user set name='name2', age=a, bu_id=a+5;
	END WHILE;

	COMMIT;
END;
CALL batch_insert();

Explain SQL：
1)explain select bu_id,name,age,sex from t_user where bu_id=17

  结果：id	select_type	 table	type	possible_keys			key						key_len	 ref	rows 
		1	SIMPLE		 t_user	ref		index_buid_name_age		index_buid_name_age		4		 const	10853	
  结论：使用了index_buid_name_age索引，并且只使用了复合索引中的bu_id列。

2)explain select bu_id,name,age,sex from t_user where name=‘jack’

  结果：id	select_type	 table	type	possible_keys			key 					key_len  ref	rows	Extra 
		1	SIMPLE		 t_user	ALL																		21706	Using where
  结论：没有使用索引，进行了全表扫描。

3)explain select bu_id,name,age,sex from t_user where bu_id=17 and name=‘jack’

  结果：id	select_type	 table	type	possible_keys			key 					key_len  ref			rows	Extra 
		1	SIMPLE		 t_user	ref		index_buid_name_age		index_buid_name_age		771		 const,const	1		Using index condition
		
  结论：使用了index_buid_name_age索引，并且(根据key_len可以推断出)使用了该复合索引中的bu_id列和name列。

4)explain select bu_id,name,age,sex from t_user where bu_id=17 and name=‘jack’ and age=18

  结果：id	select_type	 table	type	possible_keys			key 					key_len  ref				rows	Extra 
		1	SIMPLE		 t_user	ref		index_buid_name_age		index_buid_name_age		775		 const,const,const	1		Using index condition
		
  结论：使用了index_buid_name_age索引，并且(根据key_len可以推断出)使用了该复合索引中的bu_id列、name列和age列。

5)explain select bu_id,name,age,sex from t_user where bu_id=17 and name like ‘%jack’

  结果：id	select_type	 table	type	possible_keys			key 					key_len  ref			rows	Extra 
		1	SIMPLE		 t_user	ref		index_buid_name_age		index_buid_name_age		4		 const			10853	Using index condition
  结论：使用了index_buid_name_age索引，但是只使用了该复合索引中的bu_id列。

6)explain select bu_id,name,age,sex from t_user where bu_id=17 and name like ‘jack%’

  结果：id	select_type	 table	type	possible_keys			key 					key_len  ref			rows	Extra 
		1	SIMPLE		 t_user	range	index_buid_name_age		index_buid_name_age		771						1		Using index condition
  结论：使用了index_buid_name_age索引，并且(根据key_len可以推断出)使用了该复合索引中的bu_id列和name列。

7)explain select bu_id,name,age,sex from t_user where bu_id=17 and age=18

  结果：id	select_type	 table	type	possible_keys			key 					key_len  ref			rows	Extra 
		1	SIMPLE		 t_user	ref		index_buid_name_age		index_buid_name_age		4		 const			10852	Using index condition
  结论：使用了index_buid_name_age索引，但是只使用了该复合索引中的bu_id列。

8)explain select name from t_user where name=‘jack’ 或 explain select bu_id,name,age from t_user where name=‘jack’

  结果：id	select_type	 table	type	possible_keys			key 					key_len  ref			rows	Extra 
		1	SIMPLE		 t_user	index							index_buid_name_age		775						21705	Using where; Using index
  结论：使用了index_buid_name_age索引，并且(根据key_len可以推断出)使用了该复合索引中的bu_id列、name列和age列。
  注意：比较一下第2个sql语句和第8个sql语句，二者的区别在于：
		1>第2个sql语句查询了非索引列sex，故根据最左原则，无法使用该索引；
		2>第8个sql语句只查询了索引列，(若只查询索引列，则只需扫描索引树即可)故可以使用该索引。
		key_len表示索引使用的字节数，根据这个值可以判断索引的使用情况,特别是在组合索引的时候,判断该索引有多少部分被使用到非常重要。

在计算key_len时，下面是一些需要考虑的点:

索引字段的附加信息:可以分为变长和定长数据类型讨论，当索引字段为定长数据类型时,如char，int，datetime,需要有是否为空的标记,这个标记占用1个字节(对于not null的字段来说,则不需要这1字节);对于变长数据类型,比如varchar,除了是否为空的标记外,还需要有长度信息,需要占用两个字节。

对于,char、varchar、blob、text等字符集来说，key len的长度还和字符集有关,latin1一个字符占用1个字节,gbk一个字符占用2个字节,utf8一个字符占用3个字节。

综上，下面来看一些例子:

备注：key_len只指示了where中用于条件过滤时被选中的索引列，是不包含order by/group by这一部分被选中的索引列的。
例如,有个联合索引idx(c1,c2,c3),3列均是int not null,那么下面的SQL执行计划中,key_len的值是8而不是12:

select … from tb where c1=? and c2=? order by c1;
参考文章:
mysql explain 中key_len的计算
解读EXPLAIN执行计划中的key_len

771=255x3+2 变长+2，非空