索引的定义
索引的作用
B-Tree与B-Tree异同
什么场景下不适合创建索引
什么的字段适合创建索引
索引的分类
- 1.主键索引
- 2.唯一索引
或 UNIQUE KEY GradeID(GradeID)
- 4.全文索引
- 补充唯一索引：
索引的不足
在使用中应当注意的细节

索引的定义

索引是数据库中一个用于排序的数据结构，用来快速的查询数据库中的数据。MySQL数据库使用B+Tree来实现索引的。索引相当于一本书的目录，通过目录我们可以迅速定位到书中要找的内容。MySQL中的也是同理，它是一种帮助MySQL高效获取数据的数据结构（树Tree）；

MySQL数据库中的索引主要依赖于两个存储引擎，MyISAM和InnoDB，都是使用B+Tree作为索引结构。
区别：就是MyISAM中使用B+Tree的叶子节点的data域存放数据的内存地址；
而InnoDB中树的叶节点data域保存了完整的数据。

B+Tree的特点
就是叶子节点包含了所有的关键字信息和data数据，非叶子节点只包含子叶点的最大或者最小关键字，用来实现索引；
所有的叶子节点之间有一个链指针；
数据记录都存放在叶子节点中；
好处：既能实现快速查找，相比于B树又节约啦空间。
了解：
B-Tree的特点
每个节点都存储Key和data；
所有的节点组成这棵树，并且叶子节点指针为null；
叶子节点不包含任何关键字的信息；

建索引的优缺点：
优点：大大加快了对数据的查询速度；
缺点：占物理空间，对数据库进行操作DML语句的时候也要动态的维护索引；

索引的作用

提高查询的速度；
确保数据的唯一性；
可以加速表和表之间的连接性，实现表和表之间的参照完整性；
使用分组和排序子句进行数据检索时，可以减少分组和排序的时间；
全文检索字段进行搜索优化；

索引如何提高查询的速度呢

eg：假如我们有如下的一张表，在没有引入索引之前，假如我们想查询表中的一条年龄为20的数据，那么几乎要遍历完整个表才能查询到这条数据；现在我们引入索引，并且以age列作为索引列来构建出一个二叉树的索引结构来，具体的构造过程是先把表中第一行的age（49）作为二叉树的头节点，然后顺序往下遍历，把小于头节点的age方法哦左边，大于头节点的age放到右边，表中的数据遍历完成后就构造出了右图所示的二叉树索引结构；

现在试着添加完索引之后，再来查找年龄为20的数据：
1.将age为20的数据先和头节点对比（20<49）所以往左边走；
2.来到树形结构的第二层，继续和节点21对比（20<21）所以往左边走；
3.来到树形结构的第三层，和20比较（20=20）查成功！
添加完索引只需三次就查询成功啦，可见索引确定可以提供SQL的查询效率。

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

B-Tree与B-Tree异同

B-Tree：它类似于像上面构造的二叉树，但是B-Tree允许每个节点有更多的子节点（二叉树只允许有两个），B-Tree示意图如下：
B-Tree的特点：

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

B+Tree：B+Tree相对于B-Tree在查询上会更加优胜。
因为:

B+Tree的树的节点不在保存数据，所以磁盘页能容纳更多节点元素；
B+Tree查询必须查到叶子节点，B-Tree只要匹配到即可不管元素的位置，因此B+Tree查询性能更加稳定；
对于范围查找来说，B+Tree秩序遍历叶子节点链表即可，B-Tree却需要重复的遍历（先访问根节点再访问左右节点）。

从以上两张图上也可以看出B+Tree与B-Tree的不同在于：

所有关键字存储在叶子节点，非叶子节点不存储真正的data；
为所有的叶子节点增加了一个链指针；
什么场景下不适合创建索引
在查询中很少使用或者是参考的列不适合创建索引。因为既然这些列很少使用到，因此有无索引，并不能提高查询的速度。相反，增加了索引反而降低了系统的维护速度且增大了空间需求。

只有很少数据值的列不适合创建索引。因为本来结果集合就相当于全表查询，因为由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引并不能明显加快检索的速度。

被定义为text，image和bit数据类型的列不适合创建索引。因为这些列的数据量要么相当大，要么取值很少。

当修改性能远远大于检索性能时不适合创建索引。修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索的性能，但会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能的时候，不应该在创建索引。

经常插入、删除、修改的表；
不会出现where条件中的字段不适合建立索引；

什么的字段适合创建索引

表的主键，外键必须有索引

外键是唯一的，而且经常会用来查询

数据量超过300的表

增强查询的速度

经常与其他表进行连接的表，在连接字段上应该建立索引

经常连接查询，需要有索引

经常出现where子句中的字段

加快判断速度，特别是大表的字段，应该建立索引，一般用在select……where f1 and f2,我们在f1或者f2上建立索引时没有用的。只有使用联合索引才能有用

经常用到排序的列上

因为索引已经排序

经常用在范围内搜索的列上创建索引

因为索引已经排序了，其指定的范围是连续的
总结下来简单记就是：唯一、不为空、经常被查的字段；

索引的分类

主键索引（primay key）
唯一索引（unique）
常规索引（index）
全文索引（fulltext）
1.主键索引
主键：
某一个属性能唯一标识一条记录
如：学生表（学号，姓名，班级，性别…），学号是唯一的标识，可以作为主键。
特点：
最常见的索引
确保数据记录的唯一性
确定特定数据记录在数据库中的位置
eg：
```
CREATE TABLE `表名`（
　　`StudentID` INT(11)  AUTO_INCREMENT PRIMARY KEY,
　　 #或 PRIMARY KEY(`StudentID`)
）
```
2.唯一索引
作用：
避免同一个表中某数据列中的值重复
与主键的区别：
主键索引只能有一个
唯一索引可有多个
eg： ``sql CREATE TABLEGrade`（

　　GradeID INT(11) AUTO_INCREMENT PRIMARY KEY,

　　GradeName VARCHAR(32) NOT NULL UNIQUE

或 UNIQUE KEY `GradeID`(`GradeID`)

<a name="Sihn2"></a>
### 3.常规索引
作用：<br />快速定位特定的数据<br />注意：<br />index与key 关键字都可以设置常规的索引<br />赢加在查询条件后面<br />不易添加太多常规的索引，影响数据的插入，删除和修改<br />eg：
```sql
##创建表时添加
CREATE TABLE `result`{
　　//省略一些代码
　　INDEX / KEY `ind` (`studentNo`,`subjectNo`)
}
##创建后追加
ALTER TABLE `result` ADD  INDEX `ind` (`studentNo`,`subjectNo`);

4.全文索引

作用：
快速定位特定的数据
注意：
只能用于MyIASM类型的数据表
只能用于char，varchar，text数据列类型
使用大型数据集
eg：

CREATE TABLE `student`(
　　#省略一些sql语句
　　　　FULLTEXT(`StudentName`)
)ENDINE=MYISAM;
ALTER TABLE employee ADD FULLTEXT(`first_name`)

补充唯一索引：

它与前面的主键索引类似，不同的是：索引列的值必须是唯一的，但允许有空值。如果是组合索引，则列值的组合必须是唯一的，它有以下几种创建方式：
创建唯一索引：

create unique index indexName ON mytable(username(length))

修改表的结构：

alter table mytable ADD unique [indexName](userName(length))

创建表的时候直接指定

create table mytable(
    id int NOt null,
  username varchar(16) not null,
  unique [indexName](userName(length))
);

基本索引：
基本索引，没有任何限制以下就是几种创建的方式：

create index indexName on mytable(username(length))
//如果是char varchar类型，length可以小于字段的实际长度；如果blob和text类型，必须指定length。

修改表结构（添加索引）

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
);

删除索引的语法

DROP INDEX [indexName] ON mytable;

组合索引
为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable(
     ID INT NOT NULL, 
     username VARCHAR(16) NOT NULL,
     city VARCHAR(50) NOT NULL, 
     age INT NOT NULL 
);

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

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
//建表时，usernname长度为 16，这里用 10。
//这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，
提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。
建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：
usernname,city,age usernname,city usernname
为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引

下面的几个SQL就会用到这个组合索引：

SELECT * 
    FROM mytable 
    WHREE username="admin" AND city="郑州";
SELECT * FROM mytable WHREE username="admin"

而下面几个则不会用到

SELECT * FROM mytable WHREE age=20 AND city="郑州"
SELECT * FROM mytable WHREE city="郑州"

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此
因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。
eg：

SELECT t.Name 
    FROM mytable t LEFT JOIN mytable m 
    ON t.Name=m.username 
    WHERE m.age=20 AND m.city=’株洲‘

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。
刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。
例如下句会使用索引：

SELECT * FROM mytable WHERE username like’admin%’;

而下句就不会使用：

SELECT * FROM mytable WHEREt Name like‘%admin’;

因此，在使用LIKE时应注意以上的区别。

索引的不足

说了那么多索引的好处，但是我们使用在做优化的时候要明白，过多的使用索引不一定能达到我们想要的结果；
因此索引也会有它的缺点：
1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、updata、delete。因为更新表时，MySQl不仅要保存数据，还要保存一下索引文件；
2.建立索引会占用磁盘空间的索引文件。一般这个情况不太严重，但如果你在一张表上创建了多种组合索引，索引文件会膨胀的很快；
3.索引只是提高效率的一个因素，如果你的MySQL有大量的表，就需要花时间研究建立起最优秀的索引，或者优化SQL语句。

在使用中应当注意的细节

【须知！！！】

索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

避免在索引上使用计算 ```sql select id from userinfo where YEAR(adddate)<2007;

//将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select id from userinfo where adddate<‘2007-01-01’;


6. 尽量避免使用in和not   in和<>操作，否则会导致全表扫描因为逻辑判断会导致索引失效。
```sql
select id from t where num in(1,2,3) 
//对于连续的数值，能用 between 就不要用 in 了
select id from t where num between 1 and 3

应尽量避免在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
```

数据库相关得知识总结

MySQL索引

索引的定义

索引的作用

B-Tree与B-Tree异同

什么场景下不适合创建索引

什么的字段适合创建索引

索引的分类

1.主键索引

2.唯一索引

或 UNIQUE KEY `GradeID`(`GradeID`)

4.全文索引

补充唯一索引：

索引的不足

在使用中应当注意的细节

MySQL索引

索引的定义

索引的作用

B-Tree与B-Tree异同

什么场景下不适合创建索引

什么的字段 适合创建索引

索引的分类

1.主键索引

2.唯一索引

或 UNIQUE KEY GradeID(GradeID)

4.全文索引

补充唯一索引：

索引的不足

在使用中应当注意的细节

什么的字段适合创建索引

或 UNIQUE KEY `GradeID`(`GradeID`)