数据库索引-初级知识get

来源博客1：深入浅出数据库索引原理

来源博客2（超级推荐~~）数据库索引全解

来源博客3：正确合理的建立MYSQL数据库索引

---

创建索引：

CREATE INDEX indexName ON mytable(username(length));如果是CHAR VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

 删除索引：

DROP INDEX [indexName] ON mytable; 

(2)唯一索引

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

创建索引:

CREATE UNIQUE INDEX indexName ON mytable(username(length)); 

(3)主键索引

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

 CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   PRIMARY KEY(ID) ); 

(4)组合索引 

---

很大一部份程序员对索引的了解仅限于到“加索引能使查询变快”这个概念为止。

• 为什么要给表加上主键？

• 为什么加索引后会使查询变快？

• 为什么加索引后会使写入、修改、删除变慢？

• 什么情况下要同时在两个字段上建索引？

---

想要理解索引原理必须清楚一种数据结构「平衡树」(非二叉)，也就是b tree或者 b+ tree，重要的事情说三遍：“平衡树，平衡树，平衡树”。

 主流的RDBMS都是把平衡树当做数据表默认的索引数据结构的。

---

如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引，也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。

---

什么样的字段不适合建索引？

一般来说，列的值唯一性太小（如性别，类型什么的），不适合建索引（怎样叫太小？一半说来，同值的数据超过表的百分之15，那就没必要建索引了） 
太长的列，可以选择只建立部分索引，（如：只取前十位做索引） 
更新非常频繁的数据不适宜建索引（怎样叫非常？意会）

大多数情况下索引能大幅度提高查询效率，但：

• 数据的变更（增删改）都需要维护索引，因此更多的索引意味着更多的维护成本
• 更多的索引意味着也需要更多的空间 （一本100页的书，却有50页目录？）
• 过小的表，建索引可能会更慢哦 ：） （读个2页的宣传手册，你还先去找目录？）

多列查询该如何建索引?

一次查询只能用到一个索引，所以 首先枪毙 a，b各建索引方案

a还是b？ 谁的区分度更高（同值的最少），建谁！

当然，联合索引也是个不错的方案，ab，还是ba，则同上，区分度高者，在前

---

覆盖索引(Covering Indexes)拥有更高效率

索引包含了所需的全部值的话，就只select 他们，换言之，只select 需要用到的字段，如无必要，可尽量避免select *

NULL 的问题

NULL会导致索引形同虚设，所以在设计表结构时应避免NULL 的存在（用其他方式表达你想表达的NULL，比如 -1？）

一次查询能用多个索引吗?

不能

---

什么情况下使用索引
       表的主关键字

自动建立唯一索引

表的字段唯一约束

ORACLE利用索引来保证数据的完整性

直接条件查询的字段

在SQL中用于条件约束的字段

查询中与其它表关联的字段

字段常常建立了外键关系

查询中排序的字段

排序的字段如果通过索引去访问那将大大提高排序速度

查询中统计或分组统计的字段

---

什么情况下应不建或少建索引

表记录太少

 

经常插入、删除、修改的表

 

数据重复且分布平均的表字段

 

经常和主字段一块查询但主字段索引值比较多的表字段