当前位置:Gxlcms > mysql > MySQL学习笔记之十七优化之选择索引的准则_MySQL

MySQL学习笔记之十七优化之选择索引的准则_MySQL

时间:2021-07-01 10:21:17 帮助过:47人阅读

一、搜索的索引列,不一定是所要选择的列

换句话说,最适合索引的列是出现在WHERE 子句中的列,或连接子句中指定的列,而不是出现在SELECT 关键字后的选择列表中的列,例如:

SELECT
col_a ←不适合作索引列
FROM
Tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c ←适合作索引列
WHERE
col_d = expr ←适合作索引列

当然,所选择的列和用于 WHERE 子句的列也可能是相同的。关键是,列出现在选择列表中不是该列应该索引的标志。

出现在连接子句中的列或出现在形如 col1 = col2 的表达式中的列是很适合索引的列。查询中的col_b 和col_c 就是这样的例子。如果MySQL 能利用连接列来优化一个查询,

表示它通过消除全表扫描相当可观地减少了表行的组合。

二、使用唯一索引

考虑某列中值的分布。对于惟一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分各行。而用来记录性别的

列,只含有“M”和“F”,则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)。

三、使用短索引

如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,如果有一个CHAR(200) 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对

整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘I/O 较少,较短的值比较起来更快。更为重要的是,对于

较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL 也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。(当然,应该利用一些常

识。如仅用列值的第一个字符进行索引是不可能有多大好处的,因为这个索引中不会有许多不同的值。)

四、利用最左前缀

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

(这与索引一个列的前缀不同,索引一个列的前缀是利用该的前n 个字符作为索引值。)

假如一个表在分别名为state、city 和zip 的三个列上有一个索引。索引中的行是按state/city/zip 的次序存放的,因此,索引中的行也会自动按state/city 的顺序和

state 的顺序存放。这表示,即使在查询中只指定state 值或只指定state 和city 的值,MySQL 也可以利用索引。因此,此索引可用来搜索下列的列组合:

MySQL 不能使用不涉及左前缀的搜索。例如,如果按city 或zip 进行搜索,则不能使用该索引。如果要搜索某个州以及某个zip 代码(索引中的列1和列3),则此索引不

能用于相应值的组合。但是,可利用索引来寻找与该州相符的行,以减少搜索范围。

五、不要过度索引

不要以为索引“越多越好”,什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍过。在修改表的内容

时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度。此外,MySQL 在生

成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使MySQL 选择不到所要使用的最好索引。只保持所需

的索引有利于查询优化。

如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是,则就不要费力去增加这个索引了,因为已经有了。

六、考虑在列上进行的比较类型

索引可用于“<”、“<=”、“=”、“>=”、“>”和BETWEEN 运算。在模式具有一个直接量前缀时,索引也用于LIKE 运算。如果只将某个列用于其他类型的运算时(如

STRCMP( )),对其进行索引没有价值。

人气教程排行