索引优点:
- 索引大大的减少了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机I/O变为顺序I/O
Explain命令属性说明:
mysql> desc select * from test where id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | test | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
id | SELECT识别符,这是SELECT的查询序列号 |
select_type | SELECT类型,可以为以下任何一种:
|
table | 输出的行所引用的表 |
type | 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
|
possible_keys | 指出MySQL能使用哪个索引在该表中找到行 |
key | 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 |
key_len | 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。 |
ref | 显示使用哪个列或常数与key一起从表中选择行。 |
rows | 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 |
filtered | 显示了通过条件过滤出的行数的百分比估计值。 |
Extra | 该列包含MySQL解决查询的详细信息
|
使用索引:
- 当表数据量很少的时候可以不使用索引,此时使用索引反而会影响速度(字典表等)
- 查询使用group by或order by时,在该属性上建立索引
- 联合join查询时,在关联属性上建立索引
- 索引列尽量不要存在null值,可以使用not null创建列,因为Mysql难以优化可null列的查询,它会使索引、索引的统计信息以及比较运算更加复杂。可null列需要更多的存储空间,还需要mysql内部进行特殊处理。可null列被索引后,每条记录都需要一个额外的字节,还会导致MyIsam引擎中固定大小的索引变成可变大小的索引。
- 使用短索引,索引应该尽量小,在字节少的列上建立索引。如果是大文本字段上需要索引,可建立前缀索引(建立索引时指定索引列的长度,如:alter table test add idx_name(name(10)),只取name的前10来创建索引,这样做加快了查询,减小了索引文件大小,insert速度得到提升)
- like查询时注意写法,like “%zha%”不使用索引,like “zha%”使用索引(尽量不要使用like查询)
- 不要在查询列上进行计算(如:where a+1 > 1)
- 注意多列索引的使用,介绍如下:
B-TREE多列索引限制:
- 如果不是按照索引的最左列开始查找,则无法使用索引
- 不能跳过索引中的列查找,否则无法使用该列索引(如where c1=1 and c3=2跳过了c2,则只使用c1索引,跳过的列c3无法使用索引)
- 如果查询中有某个列的范围查询,则其右边所有列都无法使索引
B-TREE多列索引使用:
- 当服务器对多个索引做相交操作时(通常为多个and),意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引(Explain中Extra出现Using intersect(...)时即为索引交叉操作)
- 当服务器对多个索引做联合操作时(通常为多个or),需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高,需要合并扫描返回的大量数据的时候
- 将选择性最高的列放到索引最前列