mysql unique normal_Mysql总结之索引

前言

数据库索引，是数据库管理系统(DBMS)中一个排序的数据结构，以协助快速查询、更新数据库表中的数据。

正文

类型

普通索引(Normal)也叫非唯一索引，是最普通的索引，没有任何限制。

唯一索引(Unique)唯一索引要求不能重复。主键是一种唯一索引，但是它要求不能为空。

全文索引(FullText)针对比较大的数据，比如存放的是消息内容或者一篇文章，有几KB的数据这种情况，如果需要用like进行查询，可以使用全文索引提升效率。

只有text,char,varchar等文本字段才能创建全文索引。

MylSAM和InnoDB支持全文索引。

查询语法：

select * from fulltext where match(content) against('今天天气' IN NATURAL LANGUAGE MODE )

结构

二叉树左子树的所有节点都小于父节点，右子树的所有节点都大于父节点。

缺点：左右子树深度差无法控制，容易出现“斜树”，即一个链表。

平衡二叉树(AVL Tree)解决了二叉树的左右子树深度差无法控制的缺点，其左右子树的深度差绝对值不能超过1。

节点存放键值、数据地址、节点引用。

缺点：只有两路(二叉)数据多的时候，深度会变大，需要查询较后的数据需要进行多次IO，效率低。

多路平衡二叉树(B-Tree)节点存放内容与AVL Tree一致。

路数永远比关键字多1。路数变多，IO次数显著降低。

加强版多路平衡二叉树(B+Tree)B+Tree是B-Tree的一个变种，不是说B-Tree有啥问题，而是B+Tree的实现更好。

路数与关键字数量对等。

根节点与也节点不存储数据(即记录数据的完整地址)，只有叶子节点才会存储数据。

每个叶子节点增加一个指向相邻叶子节点的指针，最后一个数据会指向下个叶子节点的第一个数据，形成了一个有序的链表结构。

优势：是B Tree的变种，B Tree解决的问题它都能解决(节点存储更多的关键字，路数变多)。

扫库，扫表能力更强：如果需要全表扫描只需要遍历叶子节点，不需要遍历整个B+ tree。

B+Tree的磁盘能力更强：非叶子节点都是不保存数据区，所以可以保存更多的关键字，一次磁盘加载的关键字更多。

排序能力更强：叶子节点的最后一个数据指向下个叶子节点的第一个数据。

效率更加稳定:永远是在叶子节点上拿到的，IO次数稳定。

数据存储文件

可以看到每个InnoDB有两个文件(.frm和.ibd)MylSAM有三个文件(.frm，.MYD,.MYI)

.frm中存储了表结构，这是任意存储引擎都有的。

MyISAM.MYD是数据文件，即存放数据记录。

.MYI是索引文件，存放索引。

索引结构依旧是B+Tree，但其非聚集索引(下面介绍)，所有索引叶子节点都保存了指向数据存储的地址。如图所示

InnoDB索引与数据存放于一个文件。

在聚集索引的叶子节点上直接存储了数据。

索引即数据，数据即索引。

聚集索引(聚簇索引)索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的

如果一个表创建了主键索引，那么这个主键索引就是聚集索引，决定了数据行的物理存储顺序。

二级索引非聚集索引的其他索引就是二级索引。

其叶子节点存储的是对应的聚集索引的值。为啥不存地址？因为地址会变化。

流程：

如上图，InnoDB中name字段为索引，当用户查询name=Edison的数据时，它会在二级索引中找到name=Edison的聚集索引即id=7;

然后通过这个id去聚集索引树中去查找这个id对应的数据地址，最后拿到数据。也就是我们经常说的回表。

问：如果一个表没有主键怎么办？如果我们定义了Primary Key，那InnoDB会选择其作为聚集索引。

如果没有显式的定义Primary Key，InnoDB会选择一个不包含null值的唯一索引作为主键索引。

如果上述的索引也没有呢？InnoDB会选择内置6字节的ROWID作为隐藏的聚集索引，它会随着行记录的写入而主键递增。

索引使用原则

列离散度

最左匹配原则

举例说明

创建index(a,b,c)

相当于创建三个索引：

index(a)

index(a,b)

index(a,b,c)

用where b= ? 和where b = ? and c = ?是不能使用到索引的

覆盖索引在二级索引中，如果select的数据列只需要从索引中就能取得，不必从数据区中读取，这个时候使用的索引就是覆盖索引。即不需要回表。

索引条件下推(ICP)默认开启。

5.6以后完成的功能，只适用于二级索引。

ICP的目标是减少访问表的完整行的读数据从而减少I/O操作。

下推的意思就是把过滤的动作在存储引擎端完成，而不用到Server层。

举例说明

创建index(lastName,firstName)

sql:select * from t where lastName = 'wang' and firstName like '%zi';

正常情况下，只有lastName走索引，firstName是走不了索引的。

所以查询过程是：根据联合索引查询出所有lastName='wang'的二级索引

然后回表查询对应的数据行返回给Server层。

Server层根据firstName like '%zi' 进行过滤。

这里是存在问题，如果wang的数据量很大，而以zi结尾的确只有1个，那么那些就是多余的查询。

Using where : 代表从数据引擎取回的数据不满足全部条件，需要在Server层过滤。

下推的优化下是：根据联合索引查询出所有lastName='wang'的二级索引

然后二级索引过滤出zi结尾的索引，只剩1条

然后再回表查询这一条对应的数据，返还给Server层。

索引创建在用于where 判断order排序和join的(on)、group by的字段上创建索引。

索引的个数不要太多：浪费空间，更新变慢。

过长字段，建立前缀索引。

区分度低的字段不要建索引：离散度低，导致扫描的行变多。

频繁更新的字段，不要建：页分裂

随机无序的字段：页分裂

组合索引把散列性(区分度)高的放前面

创建复合索引，而不是单列索引

什么时候用不到索引索引列上使用函数，表达式，计算符号。

字符串不加引号，出现隐式转换。

like '%zi'

负向查询：not in /not like

Explain

SQL执行顺序select distinct

from

join  on 

where

group by

having

order by

limit 复制代码

SQL执行顺序1、from 

2、on 

3、 join 

4、where 

5、group by 

6、having 

7、select

8、distinct 

9、order by 

10、limit 复制代码