MySQL高级性能优化---索引

以你之姓@ 2021-12-11 07:04 547阅读 0赞

# 1. 什么是索引 #

帮助Mysql高效获取数据的数据结构，索引就是一种数据结构，这种数据结构类似新华字典的索引目录， 可以通过索引目录快速查到你想要的字，排好序的快速查找数据

# 2. 为什么要建立索引 #

提高查询效率

# 3. 优势 #

索引类似大学图书馆建立的书目索引，提高检索效率，降低数据库的IO成本  
通过索引对数据进行排序，降低数据排序成本，降低了CPU的消耗

# 4. 劣势 #

一般来说，索引本身也很大，索引往往以文件的形式存储到磁盘上  
索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是要占磁盘空间的  
虽热索引提高了查询速度，但是会降低更新表的速度  
因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段  
会调整因为更新所带来的键值变化后索引的信息

# 5. 索引分类 #

*  单值索引  
    一个索引只包含某个列，一个表可以有多个单值索引。一般来说一个表建立索引不要超过5个
 *  唯一索引  
    索引列的值必须唯一，但允许有空值
 *  复合索引  
    一个索引包含多个列
 *  全文索引  
    MYSQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度

# 6. 索引为什么能快速查找数据 #

## 6.1 概述 ##

在我们存储数据时，如果建立索引，数据库会维护一个满足特定查找算法的数据结构，这些数据结构以某种方式引用数据。可以在这些数据结构之上，实现高级查找算法，这种结构就是索引。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。为了加快数据的查找，可以维护二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针。这样就可以运用二叉查找在一定的复杂度内获取相应的数据，从而快速的检索出符合条件的记录。除了二叉树还有BTtree索引。**我平时所说的索引如果没有特别指定，都是指B树结构组织的索引，其中聚焦索引，次要索引，复合索引，前缀索引，唯一默认都是B+树索引**。除B+树索引之外，还有哈希索引等。

## 6.2 二叉查找树 ##

特性：  
左子树的键值小于根的键值  
右子树的键值大于根的键值  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70]  
在节点上的键值记录了磁盘里的地址  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 1]

## 6.3 B-Tree(平衡多路查找树) ##

特性:  
m阶B-Tree满足以下条件  
备注m是灰色块，k是红色块，p是绿色块

1.  根节点至少包括两个孩子
2.  树中每个节点最多有m个孩子(m>=2)
3.  除了根节点和叶子节点外，其他每个节点至少有Ceil(m/2)个孩子
4.  所有叶子节点都在同一层
5.  ki(1=1…n)为**关键字(图中的红色块)**，且关键字按顺序升序排列k(i-1)<k 8<9
6.  关键字的个数n满足: ceil(m/2)-1 <= n <= m-1 (非叶子节点关键字个数比指向孩子的指针)
7.  非叶子节点的指针p\[1\],p\[2\]…p\[m\] 其中p1指向关键字小于k\[1\]的子树 **3和5 < 8**(如图)  
    p\[m\]指针关键字大于k\[m-1\]的子树 **13,15>12**(如图)  
    p\[i\]指向关键字属于(k\[i-1\],k\[1\])的子树 **9,10是位于8和12之间**(如图)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 2]

## 6.4 B+Tree ##

B+树是B树的变体，基本与B-Tree相同

不同点

1.  非叶子节点的子树指针与关键字个数相同
2.  非叶子节点的子树指针，指向关键字值(k\[i\],k\[i+1\])的子树
3.  非叶子节点(**第二行的是非叶子节点**)仅用来做索引，数据都保存在叶子节点中\*\*\*\*
4.  所有叶子节点均有一个链指针指向下一个叶子节点  
    链接起来，能够方便我们在直接在叶子节点做范围统计，而不是再回到子节点中，一旦定位到某个子节点，便可以从该叶子节点横向的跨子树去做统计

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 3]

## 6.4.1采用B+Tree做为主流索引数据结构的原因 ##

1.  更适合用来做存储索引
2.  B+树的磁盘读写代价更低  
    内部的结构并没有指向关键字的具体指针  
    不存放数据，只存放索引  
    内部节点相对B树更小
3.  B+树的查询效率更加稳定  
    内部节点并不是最终指向文件内容的节点，只是叶子节点中关键字的索引  
    所以它任何关键字的查找，必须走一条从根节点到叶子节点的路  
    所有关键字查询的长度相同，导致每一个数据查询的效率也几乎相同
4.  B+树更有利于对数据库扫描  
    B树在提高IO性能同时，并没有解决元素遍历效率低下的问题  
    B+树只需要遍历叶子节点，就可以解决对全部关键字信息的扫描  
    对数据库中，频繁使用的范围查询，性能更高

# 7. 索引基本语法 #

## 7.1 创建索引 ##

CREATE INDEX 索引名 on 表名(字段名)

CREATE INDEX cus_idx on employee(cus_id)

## 7.2 查看索引 ##

SHOW INDEX FROM 表名

SHOW INDEX FROM employee

## 7.3 删除索引 ##

DROP INDEX 索引名 on 表名

DROP INDEX cus_idx on employee

## 7.4 更高索引 ##

alter table table_name add primary key(column_list)
    alter table table_name add UNIQUE index_name(column_list)
    alter table table_name add index index_name(column_list)
    alter table table_name add fulltextindex_name(column_list)

# 8. 索引建立选择 #

## 8.1 什么情况下适合建立索引 ##

1.  主键自动建立唯一索引
2.  频繁作为查询条件的字段应创建索引
3.  查询中与其它表关联的字段，外键关系应建立索引
4.  查询中排序的字段，排序的字段若通过索引去访问将大提升排序速度
5.  查询中统计或分组的字段

## 8.2 什么情况下不适合建立索引 ##

1.  频繁更新的字段不适合建立索引
2.  where条件里用不到的字段不建立索引
3.  记录比较少
4.  经常增删改的表
5.  数据重复的表字段

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70]: /images/20211211/69f6f68bd2a348d1a5717a6ecd1497b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 1]: /images/20211211/3bb360104aea436c9f28991e41f91f06.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 2]: /images/20211211/cb1ede4582de4ee4a6059df49bd9a6bc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 3]: /images/20211211/61a2a17bc47b4cf1ac7dfa9b67453b58.png