《mysql数据库索引优化》由会员分享，可在线阅读，更多相关《mysql数据库索引优化（6页珍藏版）》请在金锄头文库上搜索。
1、我们首先讨论索引，因为它是加快查询的最重要的工具。还有其他加快查询的url=javascript:; 技术/url,但是最有效的莫过于恰当地使用索引了。在MySQL的邮件清单上，人们通常 询问关于使查询更快的问题。在大量的案例中，都是因为表上没有索引，一般只要加上索引 就可以立即解决问题。但这样也并非总是有效，因为优化并非总是那样简单。然而，如果不 使用索引，在许多情形下，用其他手段改善性能只会是浪费时间。应该首先考虑使用索引取 得最大的性能改善，然后再寻求其他可能有帮助的技术。本节介绍索引是什么、它怎样改善查询性能、索引在什么情况下可能会降低性能，以及怎样 为表选择索引。下一节，我们将讨论
2、MySQL 的查询优化程序。除了知道怎样创建索引外， 了解一些优化程序的知识也是有好处的，因为这样可以更好地利用所创建的索引。某些编写 查询的方法实际上会妨碍索引的效果，应该避免这种情况出现。（虽然并非总会这样。有时 也会希望忽略优化程序的作用。我们也将介绍这些情况。）索引对单个表查询的影响索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条 记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询 的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所 有数据。如果一个表有1000 行，这比顺序读取至少快100
3、倍。注意你需要存取几乎所有1000 行，它较快的顺序读取，因为此时我们避免磁盘寻道。例如对下面这样的一个 student 表：mysqlSELECT * FROM student | 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 |3
4、7 | Black |49 |63 |47 |89 | White |94 |31 |52 |+-+-| +-这样，我们试图对它进行一个特定查询时，就不得不做一个全表的扫描，速度很慢。例如 我们查找出所有english成绩不及格的学生：mysqlSELECT name,english FROM student WHERE englishALTER TABLE student ADD INDEX (english) ;+| index for english | +|42 |43 |49 |49 |54 |66 |78 |94 |99 |+如上表，此索引存储在索引文件中，包含表中每行的 engl
5、ish 列值，但此索引是在 english 的基础上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。 假如我们要查找分数小于60的所有行，那么可以扫描索引，结果得出5行。然后到达分数 为66的行，及Tom的记录，这是一个比我们正在查找的要大的值。索引值是排序的，因此 在读到包含Tom的记录时，我们知道不会再有匹配的记录，可以退出了。如果查找一个值, 它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而 不用进行表的顺序扫描(如二分查找法)。这样，可以快速定位到第一个匹配的值，以节省 大量搜索时间。数据库利用了各种各样的快速定位索引值的技术，
6、这些技术是什么并不重要， 重要的是它们url=javascript:;工作/url正常，索引技术是个好东西。因此在执行下述查询mysqlSELECT name,english FROM user WHERE english SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索 引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制 性的索引并且使用该索引取行。你可以这样创建一个多列索引：mysqlALTER TABLE tb
7、l_name ADD INDEX(col1,col2);而你应该这样创建分开的单行列索引：mysqlALTER TABLE tble_name ADD INDEX(col1);mysqlALTER TABLE tble_name ADD INDEX(col1);如果表有一个多列索引，任何最左面的索引前缀能被优化器使用以找出行。例如，如果你有 一个 3 行列索弓I(col1,col2,col3),你已经索引了在(col1)、(col1,co 12)和(col1,col2,co 13)上的搜 索能力。如果列不构成索引的最左面前缀， MySQL 不能使用一个部分的索引。假定你下面显示的 SELECT
8、 语句：mysq1 SELECT * FROM tb1_name WHERE co11=va11;mysq1 SELECT * FROM tb1_nameWHERE co12=va12;mysq1 SELECT * FROM tb1_name WHERE co12=va12 AND co13=va13; 如果一个索引存在于(co11、co12、co13)上，只有上面显示的第一个查询使用索引。第二个和 第三个查询确实包含索引的列，但是(co12)和(co12、co13)不是(co11、co12、co13)的最左面前 缀。如果LIKE参数是一个不以一个通配符字符起始的一个常数字符串，MySQL也为
9、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 select * from tbl_name where key_col LIKE %Patrick%;mysql select * from tbl_name where ke
10、y_col LIKE other_col;在第一条语句中， LIKE 值以一个通配符字符开始。在第二条语句中， LIKE 值不是一个常 数。如果column_name是一个索引，使用column_name IS NULL的搜索将使用索引。MySQL通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列： =、=、=、BETWEEN和一个有一个非通配符前缀象something%的LIKE的列。 对于一个多列索引，如果在WHERE子句的所有AND层次使用索引，将不使用来索引优化 查询。为了能够使用索引优化查询，必须把一个多列索引的前缀使用在一个AND条件组中。 下列WHERE子句使
11、用索引：. 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/* optimizedlike index_partl=hello */这些 WHERE 子句不使用索引：. WHERE index_part2=l AND index_part3=2 /* index_part_1 is not used */. WHERE index=1 OR A=10/* Noindex */. WHERE index_partl=l OR index_part
