构建适合的索引

索引的作用

1. 减少服务器需要扫描的数据量
2. 避免排序和临时表
3. 将随机I/O变成顺序I/O

如何利用索引优化查询

-- 建表
create table index_demo(
	id int auto_increment primary key, 
	int_column_one int not null, 
	int_column_two int not null,
	str_column_one varchar(32) not null, 
	str_column_two varchar(32) not null
);

-- 添加数据函数
drop procedure if exists index_demo_func;  
create procedure index_demo_func()  
	begin  
		declare i int;  
		set i = 1; 
		while i < 10000 DO  				
			insert into test.`index_demo` (`int_column_one`, `int_column_two`, `str_column_one`, `str_column_two`)  
				value (rand()*1000, rand()*1000, substring(md5(rand()), 1, rand()*15), substring(md5(rand()), 1, rand()*15)); 
			set i = i+1; 
		end while; 
	end;

-- 执行
call index_demo_func;

1、前缀索引与索引选择性

前缀索引：取字符串字段的前(后)一定数量的字符构建索引用于数据检索。
通常用于过长的字符串类型字段。
缺点：无法用于order by 与 group by，以及覆盖扫描。

索引选择性：不重复的索引值(也称为基数，cardinality)与数据表的记录总数的比值。
上面提到索引的作用之一：减少服务器需要扫描的数据量。所以，通常选择区分度好(越接近数字 1 越好)的字段构建索引。

如何选择合适的字符串位数？

-- 平均选择性
select count(distinct left(column_name, lenght)) / count(*) where table_name;

select count(distinct right(column_name, lenght)) / count(*) where table_name;

不能光看平均选择性，还需要关注数据分布是否均匀

select left(column_name, 5) pref, count(*) count from table_name group by pref order by count desc limit 5

创建索引

alter index table_name add index index_name (column_name(lenght))

2、合适的索引顺序

3、覆盖索引

4、使用索引做排序

参考

高性能mysql 第三版