数据库索引

索引原理

索引的优缺点

优点

1）索引大大减小了服务器需要扫描的数据量
2）索引可以帮助服务器避免排序和临时表
3）索引可以将随机IO变成顺序IO
4）索引对于InnoDB非常重要，因为他可以让查询锁更少的数据。如果索引过滤掉不需要的数据，且允许InnoDB行级锁（索引起作用，达到了过滤的目的）。如果查询不能使用索引，MySQL查询就是锁表。
注：只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数    据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 
    MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。

缺点

1）虽然索引大大提高了查询速度，同时却会降低更新表的速度。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
2）简历索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但是如果你在一个大表上创建了多种组合索引，索引文件会膨胀很快。
3）如果某个数据列包含许多重复的能容，为它建立索引就没有太大的实际效果。
4）对于非常小的表，大部分情况下简单的全表臊面更搞笑。

注：

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间简历最优秀的索引，或者优化查询语句。
因此：应该职位最近常查询和最进场排序的数据列简历索引。MySQL同一张数据表索引总数限制为16。

索引的存储类型

B-Tree索引

InnoDB使用的是B+Tree
B+Tree：每个叶子结点都包含指向下一个子节点的指针，从而方便叶子结点的范围遍历
B+Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适查找范围数据。

索引查询的条件

create index 索引名 on 表名（字段名[,字段名]...）;
1）查询必须从索引（复合索引）的最左边列开始，否则无法使用索引。不能利用索引查找直接班级。
2）不能跳过某一个索引列。不能利用索引查找名字为yangchen且班级是2班的人。
3）存储引擎不能使用索引中某列范围查找的右边的列。
    例：where name=aaaa and gender like 'a%' and class=2
        该查询只会使用索引中的前两列，因为like查询是范围查询。
注：LIKE查询 ‘%ddd%’ 不能使用索引，但是LIKE ‘JJJJJ%’ 能使用索引

排序

MySQL排序扫描方式：
    1）按索引排序
    2）外排序（临时表[不使用索引]）
#####按索引排序
    1）当索引的顺序与ORDER BY中的列顺序相同时，且所有列的排序方式相同（升降）时，可以使用索引。
    2）排序同样满足索引最左前缀要求。
    3）可以给左边的列设置常量，也可以使用索引。
    4）多表查询时，仅当ORDER BY中的所有列都是第一个表的列时才使用索引。
#####默认排序（外排序）
    利用自己的快速排序算法，在内存中对数据进行排序，如果内存装载不下，将对磁盘上的数据进行分块，再对各个数据块排序，然后拼成一个有序的结果集。