《mysql索引》.pdfVIP

Mysql 索引学习 理解 MySQL——索引与优化 写在前面：索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起 点。考虑如下情况，假设数据库中一个表有 10^6 条记录，DBMS 的页面大小为4K，并存储 1 00 条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都 不在内存，需要读取 10^4 个页面，如果这 10^4 个页面在磁盘上随机分布，需要进行 10^4 次 I/O，假设磁盘每次 I/O 时间为 10ms(忽略数据传输时间)，则总共需要 100s(但实际上要 好很多很多)。如果对之建立B-Tree 索引，则只需要进行 log100(10^6)=3 次页面读取，最 坏情况下耗时 30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL 查询速 度很慢时，应该想想是否可以建索引。进入正题： 第二章、索引与优化 1、选择索引的数据类型 MySQL 支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说， 可以遵循以下一些指导原则： (1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和 CPU 缓存中都需要更少 的空间，处理起来更快。 (2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。 在 MySQL 中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型 数据类型存储 IP 地址。 (3)尽量避免 NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL 中，含有空值 的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应 该用 0、一个特殊的值或者一个空串代替空值。 1.1、选择标识符 选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型，而且应该考虑 MySQL 是怎 样进行运算和比较的。一旦选定数据类型，应该保证所有相关的表都使用相同的数据类型。 (1) 整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为 A UTO_INCREMENT。 (2) 字符串：尽量避免使用字符串作为标识符，它们消耗更好的空间，处理起来也较 慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机的，这会导致 页面分裂、随机访问磁盘，聚簇索引分裂（对于使用聚簇索引的存储引擎）。 2、索引入门 对于任何 DBMS，索引都是进行优化的最主要的因素。对于少量的数据，没有合适的索引影 响不是很大，但是，当随着数据量的增加，性能会急剧下降。 如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL 仅能对索引最左边的前缀进行 有效的查找。例如： 假设存在组合索引 it1c1c2(c1,c2)，查询语句 select * from t1 where c1=1 and c2=2 能 够使用该索引。查询语句 select * from t1 where c1=1 也能够使用该索引。但是，查询语 句 select * from t1 where c2=2 不能够使用该索引，因为没有组合索引的引导列，即，要 想使用 c2 列进行查找，必需出现 c1 等于某值。 2.1、索引的类型 索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不 一定完全相同，并不是所有的存储引擎都支持所有的索引类型。 2.1.1、B-Tree 索引 假设有如下一个表： CREATE TABLE People ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum(m, f) not null, key(last_name, first_name, dob) ); 其索引包含表中每一行的 last_name、first_name 和 dob 列。其结构大致如下： 索引存储的值按索引列中的顺序排列。可以利用 B-Tree 索引进行全关键字、关键字范围 和关键字前缀查询，当然，如果想使用索引，你必须保证按索引的最左边前缀 (leftmost p refix of the index)来进行查询。 (1)匹配全值(Match the full value)：对索引中的所有列都指定具