MySql(二)索引的设计与使用
一、索引概述
所有Mysql列类型都可以被索引,对相关列使用索引时提高select操作性能的最佳途径。
根据存储引擎可以定义每个表的最大索引数和最大索引长度,每种存储引擎对每个表至少支持16个索引,总索引长度至少为256字节。
MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引。
索引在创建表的时候可以同时创建,也可以随时增加新的索引。
创建新索引的语法为:
create [unique|fulltext|spatial] index index_name
[using index_type]
on tb1_name (index_col_name,...)
index_col_name:
col_name[(length)][ASC|DESC]
也可使用alter table的语法来增加索引。
例如:为city表创建10个字节的前缀索引:
mysql> create index cityname on city (city(10));
如果以city为条件进行查询,可以发现索引cityname被使用:
mysql> explain select * from city where city = 'yt' \G
索引删除语法为:
drop index index_nama on tb1_name
例如:想要删除city表上的索引cityname,可以:
mysql> drop index cityname on city;
二、设计索引的原则
- 搜索的索引列,不一定是要选择的列。也就是说,最适合索引的列是出现在where字句中的列,或连接子局中指定的列,而不是出现在select关键字后的选择列表中的列。
- 使用唯一索引。考虑某列中值的分布。索引的列的基数越大,索引的效果越好。例如:存放出生日期的列具有不同的值,易于区分各行。而用来记录性别的列,只含有“M”和“F”,则对此列进行索引没有多大用处。
- 使用短索引。如果对字符串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,一个char(200)列,如果在前10或20个字符内,多数值是唯一的,那么就不要对整个列进行索引。对前10个或20个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘IO较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
- 利用最左前缀。在创建一个n列的索引时,实际是创建了mysql可利用的n个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。
- 不要过度索引。不要以为索引越多越好。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。创建多余的索引给查询优化带来了更多的工作。
- 对于InnoDB存储引擎的表,记录默认会按照一定的顺序保存,如果有明确定义的主键,则按照主键顺序保存,如果没有主键,但是有唯一索引,那么就按照唯一索引的顺序保存。如果既没有主键又没有唯一索引,那么表中会自动生成一个内部列,按照这个列的顺序保存。按照主键或内部列进行的访问是最快的,所以InnoDB表尽量自己指定主键,当表中同时有几个列都是唯一的,都可以作为主键的时候,要选择最常作为访问条件的列作为主键,提高查询的效率。此外,InnoDB表的普通索引都会保存主键的键值,所以主键要尽可能选择较短的数据类型,可以有效地减少索引的磁盘占用,提高索引的缓存效果。
三、BTREE索引与HASH索引
对于HASH索引:
- 只用于使用=或<=>操作符的等式比较。
- 优化器不能使用HASH索引来加速ORDER BY操作。
- MySQL不能确定在两个值之间大约有多少行。如果将一个MyISAM表改为HASH索引的Memory表,会影响一些查询的执行效率。
- 只能使用整个关键字来搜索一行。
对于BTREE索引,当使用>、<、>=、<=、BETWEEN、!=或者<>,或者like ‘pattern’(pattern不以通配符开始)操作符时,都可以使用相关列上的索引。
下列范围查询适用于BTREE索引和HASH索引:
select * from t1 where key_col = 1 or key_col in (15,18,20);
下列范围查询只适用于BTREE索引:
select * from t1 where key_col > 1 and key_col < 10;
select * from t1 where key_col like 'ab%' or key_col between 'lisa' and 'simon';
例如:创建一个和city表完全相同的memory存储引擎的表city_memory:
mysql> create table city_memory(
city_id smallint unsigned not null auto_increment,
city varchar(50) not null,
country_id smallint unsigned not null,
last_update timestamp not null default current_timestamp on update current_timestamp,
primary key (city_id),
key idx_fx_country_id(country_id)
)engine=memory default charset=urf-8;
mysql> insert into city_momory select * from city;
当对索引字段进行范围查询的时候,只有BTREE索引可通过索引访问:
mysql> explain select * from city where country_id > 1 and country_id < 10 \G
而HASH索引实际上是全表扫描的:
mysql> explain select * from city_memory where country_id > 1 and country_id < 10 \G
索引用于快速找出在某个列中有一特定值的行。如果不使用索引,Mysql必须从第一条记录开始然后读完整个表直到找出相关的行。表越大,花费的时间越多。如果表中查询的列有一个索引,Mysql能快速到达一个位置去搜寻数据文件的中间,没有必要看所有数据。
大多数Mysql索引(如PRIMARY KEY、UNIQUE、INDEX和FULLTEXT等)在BTREE中存储。
空间列类型的索引使用RTREE,且MEMORY表还支持HASH索引。