Mysql总结之索引

道友请留步12138
520 阅读6分钟

前言

数据库索引，是数据库管理系统（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的实现更好。
路数与关键字数量对等。
根节点与也节点不存储数据（即记录数据的完整地址），只有叶子节点才会存储数据。
每个叶子节点增加一个指向相邻叶子节点的指针，最后一个数据会指向下个叶子节点的第一个数据，形成了一个有序的链表结构。

优势：

  1. 是B Tree的变种，B Tree解决的问题它都能解决（节点存储更多的关键字，路数变多）。
  2. 扫库，扫表能力更强：如果需要全表扫描只需要遍历叶子节点，不需要遍历整个B+ tree。
  3. B+Tree的磁盘能力更强：非叶子节点都是不保存数据区，所以可以保存更多的关键字，一次磁盘加载的关键字更多。
  4. 排序能力更强：叶子节点的最后一个数据指向下个叶子节点的第一个数据。
  5. 效率更加稳定:永远是在叶子节点上拿到的，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对应的数据地址，最后拿到数据。也就是我们经常说的回表。

问：如果一个表没有主键怎么办？

  1. 如果我们定义了Primary Key，那InnoDB会选择其作为聚集索引。
  2. 如果没有显式的定义Primary Key，InnoDB会选择一个不包含null值的唯一索引作为主键索引。
  3. 如果上述的索引也没有呢？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是走不了索引的。 所以查询过程是：

  1. 根据联合索引查询出所有lastName='wang'的二级索引
  2. 然后回表查询对应的数据行返回给Server层。
  3. Server层根据firstName like '%zi' 进行过滤。 这里是存在问题，如果wang的数据量很大，而以zi结尾的确只有1个，那么那些就是多余的查询。
    Using where : 代表从数据引擎取回的数据不满足全部条件，需要在Server层过滤。

下推的优化下是：

  1. 根据联合索引查询出所有lastName='wang'的二级索引
  2. 然后二级索引过滤出zi结尾的索引，只剩1条
  3. 然后再回表查询这一条对应的数据，返还给Server层。

索引创建

  1. 在用于where 判断order排序和join的（on）、group by的字段上创建索引。
  2. 索引的个数不要太多：浪费空间，更新变慢。
  3. 过长字段，建立前缀索引。
  4. 区分度低的字段不要建索引：离散度低，导致扫描的行变多。
  5. 频繁更新的字段，不要建：页分裂
  6. 随机无序的字段：页分裂
  7. 组合索引把散列性（区分度）高的放前面
  8. 创建复合索引，而不是单列索引

什么时候用不到索引

  1. 索引列上使用函数，表达式，计算符号。
  2. 字符串不加引号，出现隐式转换。
  3. like '%zi'
  4. 负向查询：not in /not like

Explain

www.cnblogs.com/gomysql/p/3…

SQL执行顺序

select distinct 
        <select_list>
from
    <left_table><join_type>
join <right_table> on <join_condition>
where
    <where_condition>
group by
    <group_by_list>
having
    <having_condition>
order by
    <order_by_condition>
limit <limit number>

SQL执行顺序

1、from <left_table><join_type>
2、on <join_condition>
3、<join_type> join <right_table>
4、where <where_condition>
5、group by <group_by_list>
6、having <having_condition>
7、select
8、distinct <select_list>
9、order by <order_by_condition>
10、limit <limit_number>
avatar
文章
阅读
粉丝