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

2009-02-19 16:06:58  来源:网页教学网 

WebjxCom提示:索引技术是关系数据查询中最重要的技术。如果要加提升数据库的性能,索引优化是首先应该考虑的。因为它能使我们的数据库得到最大性能方面的提升。当我们发一条查询命令时,MySQL分对它进行分析,以优化查询。把explain语名放到查询前面可显示查询的执行路线,对优化查询

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

为提高查询速度,我们可以:

  • 对数据表添加索引,以加快搜索速度;

  • 通过编程技巧最大限度地利用索引;

  • 优化查询语句,以使服务器最快响应多客户的请求。

  • 研究硬件处理过程,减少物理约束。

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可查看该日志。针对性能差的查询可利用索引来加快查询速度。

2.2. 查询优化程序

当我们发一条查询命令时,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的类型自动转换功能。自动转换会减慢查询的速度并会使有关的索引失效。

更多

推荐文章