数据库基础(1)

一 数据库索引

索引是存储引擎用于快速找到记录的一种数据结构。

索引分类(按照创建方式)：

mysql的索引分为单列索引(主键索引,唯一索引,普通索引)和组合索引.

单列索引:一个索引只包含一个列,一个表可以有多个单列索引。

组合索引:一个组合索引包含两个或两个以上的列。

单列索引

普通索引

其sql格式是 CREATE INDEX IndexName ON `TableName`(`字段名`(length)) 或者 ALTER TABLE TableName ADD INDEX IndexName(`字段名`(length))

CREATE INDEX account_Index ON `award`(`account`);

ALTER TABLE award ADD INDEX account_Index(`account`)

唯一索引

与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值,

其sql格式是 CREATE UNIQUE INDEX IndexName ON `TableName`(`字段名`(length)); 或者 ALTER TABLE TableName ADD UNIQUE (column_list)  

CREATE UNIQUE INDEX account_UNIQUE_Index ON `award`(`account`);

主键索引

不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位)

主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的。

组合索引

一个表中含有多个单列索引不代表是组合索引,通俗一点讲 组合索引是:包含多个字段但是只有一个索引名称。

其sql格式是 CREATE INDEX IndexName On `TableName`(`字段名`(length),`字段名`(length),...);

CREATE INDEX nickname_account_createdTime_Index ON `award`(`nickname`, `account`, `created_time`);

如果建立了 组合索引(nickname_account_createdTime_Index) 那么他实际包含的是3个索引 (nickname) (nickname,account)(nickname,account,created_time)

在使用查询的时候遵循mysql组合索引的"最左前缀"

（1）不按索引最左列开始查询（多列索引） 例如index(‘c1’, ‘c2’, ‘c3’) where ‘c2’ = ‘aaa’ 不使用索引,where `c2` = `aaa` and `c3`=`sss` 不能使用索引。

（2）查询中某个列有范围查询，则其右边的所有列都无法使用查询（多列查询）Where c1= ‘xxx’ and c2 like = ‘aa%’ and c3=’sss’ 改查询只会使用索引中的前两列,因为like是范围查询

（3）不能跳过某个字段来进行查询,这样利用不到索引

索引的删除

删除索引的mysql格式 :DORP INDEX IndexName ON `TableName`

索引分类方式（索引底层实现）

B+树索引

是mysql的默认索引类型。

B+树索引可以加快访问数据速度，因为存储引擎不再需要全表扫描来获取需要的数据，而是从索引的根节点开始搜索，根节点中存放了指向子节点的指针，存储引擎根据这些指针向下层寻找，直至找到对应的值，或者记录不存在。

B+树索引列是顺序组织存储的，所以比交适合查找范围数据。也适用于以下查询类型：

（1）全值查询：和索引列中所有列进行匹配

（2）匹配最左前缀

（3）匹配列前缀：只匹配某一列的值的开头部分

（4）匹配范围值

（5）精确匹配某一列并范围匹配另一列

（6）只访问索引的查询：即查询只需要访问索引而无需访问数据行

B+树索引的限制：

（1）如果不按照索引的最左列开始查找，则无法使用索引

（2）不可以跳过索引中的某列进行查询

（3）查询中某个列有范围查询，则其右边的所有列都无法使用查询

哈希索引

只有memory引擎显式支持哈希索引，且把它作为默认索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（一个较小的值），哈希索引把所有的哈希码存储在索引中，并在哈希表中保存指向每个数据行的指针。哈希索引内部以哈希码有序。

哈希索引自身只存储对应的哈希码，因此索引的结构十分紧凑，哈希索引查找的速度很快。

哈希索引的缺点：

（1）哈希索引只包括哈希码和行指针，而不存储字段值，所以不可以使用索引中的值来避免读取行。但一般访问内存中的数据行速度很快。

（2）哈希索引数据不是按照索引值顺序存储，所以无法用于排序

（3）哈希索引不支持部分索引列匹配查找，因为哈希索引始终都是使用索引列的全部内容来计算哈希值

（4）哈希索引只支持等值比较查询，不支持任何范围查询

（5）访问哈希索引的数据非常快，除非有很多哈希冲突，当出现冲突时，存储引擎必须遍历链表中所有行指针，逐行比较。

（6）如果有哈希冲突的话，一些索引维护操作的代价比较大。

自适应哈希索引

当InnoDB存储引擎注意到某些索引值被使用的很频繁时，它会在内存中基于B+树索引之上再自动创建一个哈希索引，提高查找效率

空间数据索引

全文索引

聚集索引与非聚集索引

（都采用B+树作为索引） 聚集索引与非聚集索引的总结 - {-）大傻逼 - 博客园

（1）聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。索引文件即数据文件。叶子节点存储了数据行

（2）非聚集索引：索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。叶子节点还是索引节点，存储了数据行的地址。存在二次查询的问题，即使用非聚簇索引时，查询列中包含了该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。解决方案：使用组合索引覆盖掉查询列。