Mysql数据库索引

什么时候需要创建索引？
(1) 主键自动建立唯一索引;
(2) 频繁作为查询条件的字段应该创建索引;
(3) 查询中排序的字段创建索引将大大提高排序的速度(索引就是排序加快速查找)
(4) 查询中统计或者分组的字段;

什么时候不需要创建索引?
(1) 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件
(2) where条件里用不到的字段，不创建索引;
(3) 表记录太少，不需要创建索引;
(4) 经常增删改的表;
(5) 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。

索引的优缺点?
优点：
(1) 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性;
(2) 可以加快数据的检索速度;
(3) 可以加速表与表之间的连接;
(4) 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间;
缺点:
(1) 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加;
(2) 索引需要占用物理空间，数据量越大，占用空间越大;
(3) 会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护;

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构，它的具体实现就在我们上面提到的引擎层的存储引擎中。像上面提到的MyISAM存储引擎就使用B-Tree来实现主键索引、唯一索引和非主键索引等。而InnoDB中的非主键索引使用的是B-Tree数据结构，主键索引则使用的是B+Tree。

B-Tree和B+Tree异同？
B-Tree：它类似于像上面构建的普通二叉树，但是B-树允许每个节点有更多的子节点(二叉树只允许有两个)。
B-树示意图如下：

B-树的特点：
(1) 所有键值分布在整个树中(B+Tree只分布在叶子节点中);
(2) 任何关键字出现且只出现在一个节点中;
(3) 搜索有可能在非叶子节点结束;
(4) 在关键字全集内做一次查找，性能逼近二分查找算法;

B+Tree：B+树是B-树的变体。
B+树的示意图为：

从图中也可以看到，B+树与B-树的不同在于：
(1) 所有关键字存储在叶子节点，非叶子节点不存储真正的data;
(2) 为所有叶子节点增加了一个链指针;
B+树相对于B树在查询上会更加优胜。
理由如下：
(1) B+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素;
(2) B+树查询必须查找到叶子节点，B树只要匹配到即可不管元素位置，因此B+树的查询性能稳定;
(3) 对于范围查找来说，B+树只需遍历叶子节点链表即可，B树却需要重复地中序遍历;

索引的分类:
(1) 主键索引
(2) 唯一索引
(3) 常规索引
(4) 全文索引

例如下句会使用索引：
SELECT * FROM mytable WHERE username like’admin%’;
而下句就不会使用：
SELECT * FROM mytable WHEREt Name like‘%admin’;
因此，在使用LIKE时应注意以上的区别。

使用索引的细节问题？
1、索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
2、使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3、索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4、like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5、避免在索引上使用计算
select id from userinfo where YEAR(adddate)<2007;//将在每个行上进行运算，这将导致索引失效而进行全表扫描
select id from userinfo where adddate<‘2007-01-01’;//可改成
6、尽量避免使用in和NOT IN和<>操作，否则会导致全表扫描。因为逻辑判断会让索引失效。
7、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如
select id from t where num=10 or num=20;
可改成：
select id from t where num=10
union all
select id from t where num=20