MySQL数据库索引

为什么使用索引？

在无索引的情况下，MySQL会扫描整张表来查找符合sql条件的记录，其时间开销与表中数据量呈正相关。对关系型数据表中的某些字段建索引可以极大提高查询速度（当然，不同字段是否selective会导致这些字段建立的索引对查询速度的提升幅度不同，而且索引也并非越多越好，因为写入或删除时需要更新索引信息）。

对于MySQL的Innodb储存引擎来说，大部分类型的index均以B-Tree数据结构的变种B+Tree来存储（MEMORY类型的表还支持hash类型的索引）。B-Tree是数据库或文件系统中常用的一种数据结构，它是一种N叉平衡树，这种树结构保证了同层节点保存的key有序，对于某个节点来说，其左子树保存的所有key均小于该节点保存的key，其右子树保存的所有key均大于该节点保存的key。此外，在工程实现上，还结合操作系统的局部性原理做了很多优化，总之，b-tree的各种特性或优化技巧能保证：1) 查询磁盘记录时，读盘次数最少；2) 任何insert和delete操作对树结构的影响均很小；3) 树本身的rebalance操作很高效。

 

MySQL使用索引的场景

MySQL在以下操作场景下会使用索引：

1) 快速查找符合where条件的记录

2) 快速确定候选集。若where条件使用了多个索引字段，则MySQL会优先使用能使候选记录集规模最小的那个索引，以便尽快淘汰不符合条件的记录。

3) 如果表中存在几个字段构成的联合索引，则查找记录时，这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找。

例如，若为某表创建了3个字段(c1, c2, c3)构成的联合索引，则(c1), (c1, c2), (c1, c2, c3)均会作为索引，(c2, c3)就不会被作为索引，而(c1, c3)其实只利用到c1索引。

4) 多表做join操作时会使用索引（如果参与join的字段在这些表中均建立了索引的话）

5) 若某字段已建立索引，求该字段的min()或max()时，MySQL会使用索引

6) 对建立了索引的字段做sort或group操作时，MySQL会使用索引

 

哪些SQL语句会真正利用索引

从MySQL官网文档"Comparison of B-Tree and Hash Indexes"可知，下面这些类型的SQL可能会真正用到索引：

1) B-Tree可被用于sql中对列做比较的表达式，如=, >, >=, <, <=及between操作

2) 若like语句的条件是不以通配符开头的常量串，MySQL也会使用索引

比如，SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'或SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'可以利用索引，而SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%'（以通配符开头）和SELECT * FROM tbl_name WHERE key_col LIKE other_col（like条件不是常量串）无法利用索引。

对于形如LIKE '%string%'的sql语句，若通配符后面的string长度大于3，则MySQL会利用Turbo Boyer-Moore algorithm算法进行查找。

3) 若已对名为col_name的列建了索引，则形如"col_name is null"的SQL会用到索引

4) 对于联合索引，sql条件中的最左前缀匹配字段会用到索引

5) 若sql语句中的where条件不只1个条件，则MySQL会进行Index Merge优化来缩小候选集范围

 

 

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

1）普通索引

创建索引：

CREATE INDEX indexName ON tableName(columName);

删除索引：

DROP INDEX [indexName] ON mytable; 

 

2) 唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建索引：

CREATE UNIQUE INDEX indexName ON tableName(columName); 

 

3) 主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。

 

4) 组合索引

 

这里，着重讲一下，单列索引和组合索引的区别和各自应该使用的场合。

1.首先要确定优化的目标，在什么样的业务场景下，表的大小等等。如果表比较小的话，可能都不需要加索引。

2.哪些字段可以建索引，一般都where、order by 或者 group by 后面的字段。

3.记录修改的时候需要维护索引，所以会有开销，要衡量建了索引之后的得与失。

 

例如：学生表。可以认为name的重复度比较小，而age的重复度比较大，对于单列索引来说，比较适合建在重读度低的列上。

对于select * from students where name='张三’and age=18; 分为两种情况 ：
A. name 和 age 各自单独建立索引
一般来说mysql会选择其中一个索引，name的可能性比较大，因为mysq会统计每个索引上的重复度，选用低重复度的字段。另外一个age的索引就不会用到，但还有维护索引的开销，所以age的索引不需要创建。

B. name和age的联合索引
这种索引的切合度最好，mysql会直接选用这个索引。但相对单独的name索引来说，维护的成本要大一些，并且索引数据占用的存储空间也要更大一些。

 

那么综合看来，有必要使用联合索引吗？ 我的看法是没有必要，因为学校里可能会重名的人比较少。用name就可以比较精准的找到记录，即使有重复的也会比较少。

 

什么情况下使用联合索引比较好呢？

举一个例子，大学选认课老师，需要创建一个关系对应表，有2个字段,student_id 和 teacher_id，想要查询某个老师和某个学生是否存在师生关系。 

一个学生会选几十个老师，一个老师会带几百个学生。
如果只为student_id建立索引的情况下，经过索引会选出几十条记录，然后在内存中where一下，去除其余的老师。
相反如果只为teacher_id建立索引，经过索引会选出几百条记录，然后在内存中where一下，去除其余的学生。
两种情况都不是最优的，这个时候使用联合索引最合适，通过索引直接找到对应记录。

 

再进行一个例子的分析：

 

CREATE TABLE myIndex (
testID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(50) NOT NULL, 
city VARCHAR(50) NOT NULL, 
age INT NOT NULL, 
schoolID INT NOT NULL
);

 

 

插入10000条数据，其中有六条name = 'jack' 的记录，但city,age,school 的组合各不相同。

来看这条T-SQL：SELECT testID FROM myIndex WHERE name='jack' AND city='上海' AND age=28;

首先考虑单列索引：

在name列上建立了索引。执行 T-SQL 时，MYSQL 很快将目标锁定在了name=jack的 6 条记录上，取出来放到一中间结果集。在这个结果集里，先排除掉 city 不等于"上海"的记录，再排除 age 不等于 28 的记录，最后筛选出唯一的符合条件的记录。

虽然在 vc_Name 上建立了索引，查询时MYSQL不用扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样的，在 city 和age 分别建立的MySQL单列索引的效率相似。

 

 

为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。就是将 name,city，age 建到一个索引里：

ALTER TABLE myIndex ADD INDEX name_city_age (name(10), city, age);

 

建表时，name 长度为 50，这里为什么用 10 呢？因为一般情况下名字的长度不会超过 10，这样会加速索引查询速度，还会减少索引文件的大小，提高 INSERT 的更新速度。

执行 T-SQL 时，MySQL 无须扫描任何记录就到找到唯一的记录。

 

如果分别在 name,city，age 上建立单列索引，让该表有 3 个单列索引，查询时和上述的组合索引效率一样吗？大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。

 

建立这样的组合索引，其实是相当于分别建立了：(name, city, age) 、(name, city) 、(name) 这样的三个组合索引。因为 mysql 组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引。

 

http://blog.csdn.net/weiwangchao_/article/details/50256673