假定你发出下列SELECT语句:
mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
如果一个多列索引存在于col1和col2上,适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上,优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。
你可以这样创建一个多列索引:
mysql>ALTER TABLE tbl_name ADD INDEX(col1,col2);
而你应该这样创建分开的单行列索引:
mysql>ALTER TABLE tble_name ADD INDEX(col1);
mysql>ALTER TABLE tble_name ADD INDEX(col1);
l
如果表有一个多列索引,任何最左面的索引前缀能被优化器使用以找出行。例如,如果你有一个3行列索引(col1,col2,col3),你已经索引了在(col1)、(col1,col2)和(col1,col2,col3)上的搜索能力。
如果列不构成索引的最左面前缀,MySQL不能使用一个部分的索引。假定你下面显示的SELECT语句:
mysql> SELECT * FROM tbl_name WHERE col1=val1;
mysql> SELECT * FROM tbl_name WHERE col2=val2;
mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
如果一个索引存在于(col1、col2、col3)上,只有上面显示的第一个查询使用索引。第二个和第三个查询确实包含索引的列,但是(col2)和(col2、col3)不是(col1、col2、col3)的最左面前缀。
l
如果LIKE参数是一个不以一个通配符字符起始的一个常数字符串,MySQL也为LIKE比较使用索引。例如,下列SELECT语句使用索引:
mysql> select * from tbl_name where key_col LIKE "Patrick%";
mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";
在第一条语句中,只考虑有"Patrick" <= key_col < "Patricl"的行。在第二条语句中,只考虑有"Pat" <= key_col < "Pau"的行。
下列SELECT语句将不使用索引:
mysql> select * from tbl_name where key_col LIKE "%Patrick%";
mysql> select * from tbl_name where key_col LIKE other_col;
在第一条语句中,LIKE值以一个通配符字符开始。在第二条语句中,LIKE值不是一个常数。
l
如果 column_name 是一个索引,使用column_name IS NULL的搜索将使用索引。
l
MySQL通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列:=、>、>=、<、<=、BETWEEN和一个有一个非通配符前缀象'something%'的LIKE的列。
l
对于一个多列索引,如果在WHERE子句的所有AND层次使用索引,将不使用来索引优化查询。为了能够使用索引优化查询,必须把一个多列索引的前缀使用在一个AND条件组中。
下列WHERE子句使用索引:
... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
/* optimized like "index_part1='hello'" */
这些WHERE子句不使用索引:
... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */
... WHERE index=1 OR A=10 /* No index */
... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */
四:索引的作用
索引用于:
l
快速找出匹配一个WHERE子句的行。
l
在多个表的查询时,执行连接时加快了与其他表中的行匹配的行的搜索。
l
对特定的索引列找出MAX()或MIN()值。
l
如果排序或分组在一个可用索引的最左面前缀上进行(例如,ORDER BY key_part_1,key_part_2),排序或分组一个表。如果所有键值部分跟随DESC,键以倒序被读取。
l
在一些情况中,一个查询能被优化来检索值,不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀,为了更快,值可以从索引树被检索出来。
五:索引的弊端
首先,索引文件要占磁盘空间。如果有大量的索引,索引文件可能会比数据文件更快地达到最大的文件尺寸。其次,索引文件加快了检索,但增加了插入和删除,以及更新索引列中的值的时间(即,降低了大多数涉及写入的操作的时间),因为写操作不仅涉及数据行,而且还常常涉及索引。一个表拥有的索引越多,则写操作的平均性能下降就越大。在8.4.4节记录装载和修改的速度中,我们将更为详细地介绍这些性能问题,并讨论怎样解决。
选择索引的准则
创建索引的语法已经在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( )),对其进行索引没有价值。
|
相关推荐
索引优化优化优化优化
Oracle索引优化。Oracle索引优化。Oracle索引优化
在一些情况下,MySQL可以使用索引优化DISTINCT操作,但需要活学活用.本文涉及一个不能利用索引完成DISTINCT操作的实例. 实例1 使用索引优化DISTINCT操作 create table m11 (a int, b int, c int, d int, primary ...
04-VIP-Mysql索引优化实战一.pdf
MySQl索引优化课件,详细得介绍了MySQl索引优化数据库
数据库 索引 优化 数据库索引好比是一本书前面的目录,能加快数据库的查询速度。 例如这样一个查询:select * from table1 where id=44。如果没有索引,必须遍历整个表,直到ID等于44的这一行被找到为止;有了索引...
有关mysql索引优化方面的文档,比较实用,希望能够对大家有帮助。
05-VIP-Mysql索引优化实战二.pdf
Oracle数据库经典优化之索引原理篇 Oracle中建立索引并强制优化器 基于索引的SQL语句优化之降龙十八掌 30个Oracle语句优化规则详解-性能调优
数据库优化工具,连接后选择数据库会自动在大的表添加索引
数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引sql优化数据库面试题索引...
JAVA面试题MySQL索引原理及索引优化校招面试找工作笔试 目录: 基本概念 MySQL索引结构的分类 Hash索引、B+树索引、全文索引、RTree索引。 B+树索引 B+树介绍,为什么选择B+树,非聚集索引 聚集索引 第一点、第二点...
关于mysql索引一些优化介绍与创建原则,还有对order by排序的算法的介绍等等
sql数据库优化--使用索引优化存储过程,相信你对数据库的理解一定不会少!对于你开发和设计数据库是不可少的哟!
数据库索引设计和优化 经典的数据库索引的书籍......................
在进行索引优化时,有几个关键点需要注意。首先是选择合适的索引列,通常选择经常用于查询的列作为索引列,尽量避免使用长文本或二进制类型的列作为索引列。其次是合理设置索引的顺序,根据查询的频率和字段选择合适...
Mysql索引优化案例.pdf
Oracle数据库索引优化方法探析.pdf
以及表和索引的扫描方式,详尽地讲解了如何快速地估算SQL 运行的CPU 时间及执行时间,帮助读者从原理上理解SQL、表及索引结构、访问方式等对关系型数据库造成的影响,并能够运用量化的方法进行判断和优化,指导关系...
mysql索引的优化方案技术分享,珍贵资料收藏下