+------+---------+---------+---------+---------+
| id | name | english | chinese | history |
+------+---------+---------+---------+---------+
| 12 | Tom | 66 | 93 | 67 |
| 56 | Paul | 78 | 52 | 75 |
| 10 | Marry | 54 | 89 | 74 |
| 4 | Tina | 99 | 83 | 48 |
| 39 | William | 43 | 96 | 52 |
| 74 | Stone | 42 | 40 | 61 |
| 86 | Smith | 49 | 85 | 78 |
| 37 | Black | 49 | 63 | 47 |
| 89 | White | 94 | 31 | 52 |
+------+---------+---------+---------+---------+
+-------------------+
| index for english |
+-------------------+
| 42 |
| 43 |
| 49 |
| 49 |
| 54 |
| 66 |
| 78 |
| 94 |
| 99 |
+-------------------+
如上表,此索引存储在索引文件中,包含表中每行的
english
列值,但此索引是在
english
的基础上排序的。现在,不需要逐行搜索全表查找匹配的条款,而是可以利用索引进行查找。假如我们要查找分数小于
60
的所有行,那么可以扫描索引,结果得出
5
<divre style="MARGIN-LEFT: 20.8pt"></divre>
行。然后到达分数为66的行,及Tom的记录,这是一个比我们正在查找的要大的值。索引值是排序的,因此在读到包含Tom的记录时,我们知道不会再有匹配的记录,可以退出了。如果查找一个值,它在索引表中某个中间点以前不会出现,那么也有找到其第一个匹配索引项的定位算法,而不用进行表的顺序扫描(如二分查找法)。这样,可以快速定位到第一个匹配的值,以节省大量搜索时间。数据库利用了各种各样的快速定位索引值的技术,这些技术是什么并不重要,重要的是它们工作正常,索引技术是个好东西。
+---------+---------+
| name | english |
+---------+---------+
| Stone | 42 |
| William | 43 |
| Smith | 49 |
| Black | 49 |
| Marry | 54 |
+---------+---------+
|
选择索引的准则
创建索引的语法已经在4.5
索引属性
中进行了介绍。这里,我们假定您已经阅读过该节。但是知道语法并不能帮助确定表怎样进行索引。要决定表怎样进行索引需要考虑表的使用方式。本节介绍一些关于怎样确定和挑选索引列的准则:
1、搜索的索引列,不一定是所要选择的列
换句话说,最适合索引的列是出现在 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 能利用连接列来优化一个查询,表示它通过消除全表扫描相当可观地减少了表行的组合。
2、使用惟一索引
考虑某列中值的分布。对于惟一值的列,索引的效果最好,而具有多个重复值的列,其索引效果最差。例如,存放年龄的列具有不同值,很容易区分各行。而用来记录性别的列,只含有“M”和“F”,则对此列进行索引没有多大用处(不管搜索哪个值,都会得出大约一半的行)。
3、使用短索引
如果对串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,如果有一个 CHAR(200) 列,如果在前 10 个或 20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。对前 10 个或 20 个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘 I/O 较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL 也可以在内存中容纳更多的值。这增加了找到行而不用读取索引中较多块的可能性。(当然,应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的,因为这个索引中不会有许多不同的值。)
4、利用最左前缀
在创建一个 n 列的索引时,实际是创建了 MySQL 可利用的 n 个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。(这与索引一个列的前缀不同,索引一个列的前缀是利用该的前 n 个字符作为索引值。)
假如一个表在分别名为 state、city 和 zip 的三个列上有一个索引。索引中的行是按 state/city/zip 的次序存放的,因此,索引中的行也会自动按 state/city 的顺序和 state 的顺序存放。这表示,即使在查询中只指定 state 值或只指定 state 和 city 的值,MySQL 也可以利用索引。因此,此索引可用来搜索下列的列组合:
MySQL 不能使用不涉及左前缀的搜索。例如,如果按 city 或 zip 进行搜索,则不能使用该索引。如果要搜索某个州以及某个 zip 代码(索引中的列1和列3),则此索引不能用于相应值的组合。但是,可利用索引来寻找与该州相符的行,以减少搜索范围。
5、不要过度索引
不要以为索引“越多越好”,什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能,这一点我们前面已经介绍过。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度。此外,MySQL 在生成一个执行计划时,要考虑各个索引,这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。
如果想给已索引的表增加索引,应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是,则就不要费力去增加这个索引了,因为已经有了。
6、考虑在列上进行的比较类型
索引可用于“<”、“<=”、“=”、“>=”、“>”和 BETWEEN 运算。在模式具有一个直接量前缀时,索引也用于 LIKE 运算。如果只将某个列用于其他类型的运算时(如 STRCMP( )),对其进行索引没有价值。
|
815



被折叠的 条评论
为什么被折叠?



