《深入浅出Mysql》——索引的设计和使用

时间:2022-06-15 10:30:50

创建新索引的语法:

create [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tal_name(index_col_name,...);

也可以用ALTER TABLE语法来增加索引。

为city表创建10个字节的前缀索引,代码如下:

create index cityname on city(city(10));

如果以city为条件进行查询,可以发现索引cityname被使用

explain select * from city where city = 'FUZHOU' \G;

索引的删除语法为:

DROP INDEX index_name ON tbl_name

例如:

drop index cityname on city;

 

设计索引的原则:

1.搜索的索引列,最适合索引的列是出现在where子句中的列,或连接子句中指定的列

2.使用唯一索引,考虑某列中的值得分布,索引的列的基数越大,索引的效果越好,例如存放出生日期的列具有不同值,很容易区分各行。而性别列则不适合进行索引,因为男女通常各半,建立索引没有意义。

3.使用短索引,如果对一个字符串列进行索引,应该指定一个前缀长度。尽量选取前面较少的字符作为索引。

4.利用最左前缀,在创建一个n列的索引时,实际是创建了Mysql可利用的n个索引,多列索引可起几个索引的作用,因为可利用索引种最左边的列集来匹配行。这样的列集称为最左前缀。

5.不要过度索引,每个额外的索引都要占用额外的磁盘空间,并且降低写操作的性能,在修改表内容的时候,索引必须进行更新,有时可能需要重构,因此索引越多维护所花时间越长。

6.对于innoDB存储引擎的表,如果没有主键,则按照唯一索引顺序保存,如果都没有,表中会自动生成一个内部列,按照这个列的顺序保存,按照主键或者内部列进行的访问是最快的,所以innoDB表尽量自己指定主键,主键尽可能选择较短的数据类型,可以有效的减少索引的磁盘占用,提高索引的缓存效果。

 

BTREE索引与HASH索引:

1.只用于使用=或<>操作符的等式比较

2.优化器不能使用HASH索引来加速ORDER BY操作

3.Mysql不能确定在两个值之间大约有多少行,如果将一个MyISAM表改为Hash索引的MEMORY表,会影响一些查询的执行效率。

4.只能使用整个关键字来搜索下一行

 

而对于BTREE索引,当使用>、<、<=、>=、BETWEEN、!= 或者 <> 或者 LIKE‘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%'  AND key_col BETWEEN 'lisa' AND 'simon';