mysql创建高性能的索引

索引在mysql中也叫做'键'(key)是存储引擎用于快速找到记录的一种数据结构。这是索引的基本功能。

索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时,索引对性能的影响越发重要。在数据量较小切负载较低时,不恰当的索引对性能的影响可能还不明显,但是当数据量逐渐增大时,性能则会极具下降。

索引优化是对查询性能优化最有效的手段,索引能够轻易将查询性能提高几个数量级,最优的索引有时比一个好的索引性能要好两个数量级。创建一个真正的最优的索引经常需要重写查询。

索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点。考虑如下情况,假设数据库中一个表有10^6条记录,DBMS的页面大小为4K,并存储100条记录。如果没有索引,查询将对整个表进行扫描,最坏的情况下,如果所有数据页都不在内存,需要读取10^4个页面,如果这10^4个页面在磁盘上随机分布,需要进行10^4次I/O,假设磁盘每次I/O时间为10ms(忽略数据传输时间),则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引,则只需要进行log100(10^6)=3次页面读取,最坏情况下耗时30ms。这就是索引带来的效果,很多时候,当你的应用程序进行SQL查询速度很慢时,应该想想是否可以建索引。

在MySQL中,存储引擎用一本书的“索引”找到对应页码类似的方法使用索引,其先在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行。

索引可以包含一个或多个列的值。如果索引包含多个列,那么列的顺序也十分重要,因为MySQL只能高效地使用索引的最左前缀列。

选择索引的数据类型

MySQL支持很多数据类型,选择合适的数据类型存储数据对性能有很大的影响。通常来说,可以遵循以下一些指导原则:

(1)越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。
(2)简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。
(3)尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型,而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型,应该保证所有相关的表都使用相同的数据类型。

asd

2017-09-23

0 个评论