MYSQL教程:索引和查询优化程序

　2009-02-19 16:06:58　来源：WEB开发网　　　

核心提示：数据库是数据的集合，与数学的集合论有密不可分的关系，MYSQL教程:索引和查询优化程序，为提高查询速度，我们可以：对数据表添加索引，比较运行情况，不要滥用MySQL的类型自动转换功能，以加快搜索速度；通过编程技巧最大限度地利用索引；

数据库是数据的集合，与数学的集合论有密不可分的关系。

为提高查询速度，我们可以：

2.1. 索引

索引技术是关系数据查询中最重要的技术。如果要加提升数据库的性能，索引优化是首先应该考虑的。因为它能使我们的数据库得到最大性能方面的提升。

索引的优点：

没有索引的表是没有排序的数据集合，如果要查询数据需进行全表扫描。有索引的表是一个在索引列上排序了数据表，可通过索引快速定位记录。在MyISAM和ISAM数据表中，数据行保存在数据文件中，索引保存在索引文件中。BDB与InnoDB数据表把数据与索引放在同一个文件中。
在多表关联查询中，索引的作用就更大。如果没有索引，在最坏的情况下，全表扫描的次数可能是各表数据行的组合个数，可能是一个天文数字。这样的查询是破坏性的，可能会造成数据库瘫痪。
对于使用了MIN()或是MAX()函数的查询，如果相关的数据列上有索引，MySQL能直接找到该最大、最小值的行，根本不用一个一个地去检查数据行。
索引加快ORDER BY 和 GROUP BY子句的操作。
当在数值型数据列上查询数据，而该列有索引，索引能使MySQL根本不用去读取数据行，直接从索引取值。

索引的缺点：

索引列的选择

索引应该创建在搜索、排序、分组等操作所涉及的数据列上。也就是说，在where子句，关联检索中的from子句、order by或group by子句中出现过的数据列最适合用来创建索引。
尽量使用唯一索引，它能使索引发挥最好的效能。
尽量用比较短的值进行索引。当对字符串进行索引时，应该指定一个前缀长度，比如对字符串的前10位或20位的字符进行排序，而不用把整个字符串几十个字符用来索引排序。这样能减少磁盘I/O，提高处理速度。最重要的一点是，键值越短，索引缓冲区里容纳的键值也就越多，而MySQL同时保存在内存里的索引越多，索引缓冲区的命中率也就越高。当然，只对数据列第一个字符进行索引是没什么意义的。
充分利用最左前缀。所谓最左前缀也就是在复合索引中最边的索引列。如复合索引(a,b,c) ，其中a就是最左前缀。它是使用率最高的索引，需认真选择。
不要建太多索引，索引是会消耗系统资源的，要适可而止。
索引主要用于<、<=、=、>=、>、BETWEEN等的比较操作中，所以索引应该建立在与这样操作相关的数据列上。
利用慢查询日志来找出性能差的查询，通过mysqldumpslow可查看该日志。针对性能差的查询可利用索引来加快查询速度。

当我们发一条查询命令时，MySQL分对它进行分析，以优化查询。把explain语名放到查询前面可显示查询的执行路线，对优化查询提供有用的信息。以下几个原则可帮助系统挑选和使用索引：

尽量对同类型的数据列进行比较。如：VARCHAR(5)和VARCHAR(5)是同类型的，CHAR(5)和VARCHAR(5)是不同类型的。
尽量让索引的数据列在比较表达式中单独出现，不要把它包含在函数或复杂表达式。否则索引会不起作用。
尽量不要在LIKE模式的开头使用通配符。如：%string%。
对于MyISAM和BDB数据表，用ANALYZE TABLE语句让服务器对索引键值的分布进行分析，为优化程序提供更有价值的信息。另一个方法是用myisamchk --analyze(适用于MyISAM表)或isamchk --analyze(适用于ISAM表)命令。
用EXPLAIN语句来分析查询语句的执行效率。检查查询所使用的索引是不是能够迅速地排除不符合条件的数据行，如果不是，可以试着用STRAIGHT_JOIN强制各有关数据表按指定顺序进行关联。
尝试查询的不同写法，比较运行情况。
不要滥用MySQL的类型自动转换功能。自动转换会减慢查询的速度并会使有关的索引失效。