1.Index 索引原理

1.索引：排好序的数据结构，快速查找到数据

• 1.数据库读写比例 一般 = 10:1，互联网企业这个比例可能更大

• 2.数据库查询 I/O 消耗较大
• 3.户使用一个数据，连带使用周边数据概率非常大

2.索引结构 B+Tree

能把一次查询的 IO 次数控制在常量级,数据库的性能提升非常明显

B+Tree 索引是由一个个磁盘块组成的树形结构，每个磁盘块由数据项和指针组成
数据存放在叶子节点，非叶子节点不存数据 (注意 Mysql 主键索引和非主键索引的区别)，是索引

3.查找过程

• 磁盘块1：指针 P1 表示小于17的磁盘块，P2 表示在 17~35 之间的磁盘块，P3 则表示大于35的磁盘块

• 查找数据项99

  • 1.首先将磁盘块1 load 到内存中，发生 1 次 IO

  • 2.接着通过二分查找发现 99 大于 35，所以找到了 P3 指针。
  • 3.通过P3 指针发生第二次 IO 将磁盘块4加载到内存
    再通过二分查找发现大于87，通过 P3 指针发生了第三次 IO 将磁盘块11，加载到内存。
    最后再通过一次二分查找找到了数据项99

一个几百万的数据查询只需要进行 三次 IO 即可找到数据，那么整个效率将是非常高的
观察树的结构，发现查询需要经历几次 IO 是由树的高度来决定的，而树的高度又由磁盘块，数据项的大小决定的

磁盘块越大，数据项越小 ==> 树的高度就越低 索引字段要尽可能小的原因

• 时间复杂度 = **Math.log( ** 记录数,分叉数 )

2.索引结构

如下图是 windows 上 Mysql 索引文件
创建索引可以选择数据类型：

• btree: 常用，支持范围查找

• hash: 查找快，不支持范围查找

1.MyISAM 索引文件和数据文件是分离的

.frm 是存放表结构数据
MYD 是表数据
MYI 是存放索引，索引树上会存储数据在MYD文件里面的位置

• MyISAM： 主键 与 普通索引 区别在于主键是唯一的，相同点在于存储结构是一样的，都是key及地址

• InnoDB：主键索引是将主键与行数据一起存储在叶子节点中，起到聚簇索引的作用
  其他索引则是将key与主键值存储在一个叶子节点中，然后通过二次查找得当行数据

2.Innodb

.frm 表结构数据
.ibd 数据和索引
表数据文件本身就是按 B+Tree 组织的一个索引结构文件
PK 主键索引 leafs 叶节点 ==> 包含了完整的数据记录

1.数据是放在主键索引上面 “PK-Index”

实际上在每个节点上还会存放所有的数据，在每个对应的索引列的值上存放上对应的数据

2.B树存 放数据

会在每个对应的索引列的值上存放上对应的数据

3.B+树 存放数据

它只会在叶子节点上面挂载数据，非叶子节点不会存放数据，
数据只会存在叶子节点上面，非叶子节点只存放索引列的数据

如下：
一个节点就可以存放很多个索引列数据，一次IO就可以拿到很多数据，mysql默认的一个节点 16K 的大小

show global status like "Innodb_page_size" // 16384

每次IO读取16K大小的数据，以索引列是bigInt类型为例，大小8字节，每一条数据还有一个指向下一层的指针6字节，16384/（8+6）=1170，一个节点就大约可以存1170条数据

层高为3的树:
叶子节点存放数据之后大小1KB
那么这个树可以存放 1170 *1170 *16 =21,902,400,大约 2200万 条数据。
所以在这种千万级的表中通过主键索引查找一条数据，最多3次IO就可以找到一条数据。
而很多时候树的根节点基本都是在内存中，所以多数时候只需要2次IO

叶子节点之间也有双向指针连接，提高区间范围性能，范围查找

3.非叶子节点存储的是主键 PK值

保证一致性，更新数据只需要更新主键索引树，节约存储空间

4.推荐Innodb一定要有主键

数据存放在主键索引树上，没有的话，会自动生成一个 rowid 自增主键索引

5.推荐整型自增主键

• 1）方便查找比较

• 2）新增数据的时候只需要在最后加入，不会大规模调整树结构

UUID 大小不好比较，新增的时候也极有可能在中间插入数据，会导致树结构大规调整，造成插入数据变慢

3.索引建议

1.创建索引原则

• 1．选择唯一性索引

• 2．为经常需要排序、分组和联合操作的字段建立索引 join

• 3．为常作为查询条件的字段建立索引 where

• 4．限制索引的数目

• 5．尽量使用数据量少的索引

• 6．尽量使用前缀来索引

• 7．删除不再使用或者很少使用的索引

优化性能，选择在哪些列上创建索引最为关键

2.使用时机

MySQL只对 <，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引, 在 WHERE、JOIN、ORDER、GROUP BY 中出现的列需要建立索引。

3.考虑要素

1.字段本身角度，类型，长度，复杂度
2.查询条件角度
3.函数角度 等

4.实用建议

1.负向查询不能使用索引

select name from user where id not in (1,3,4); // 不可以用索引
select name from user where id in (2,5,6);     // 可以

2.前导模糊查询不能使用索引，可以考虑Lucene等全文索引工具代替频繁模糊查询

select name from user where name like '%zhangsan' // 不可以用索引
select name from user where name like 'zhangsan%' // 可以

3.数据区分不明显的不建议索引，如性别不建议，身份证ID建议

4.字段默认值不要为null，会带来和预期不一致的查询结果

5.在字段上计算不能命中索引

select name from user where FROM_UNIXTIME(create_time) < CURDATE(); 
// 修改为
select name from user where create_time < FROM_UNIXTIME(CURDATE());

6.最左前缀问题 复合索引命中问题

如果给 user 表中的 username pwd 字段创建了复合索引那么使用以下SQL 都是可以命中索引:
select username from user where username='zhangsan' and pwd ='axsedf1sd'
select username from user where pwd ='axsedf1sd' and username='zhangsan'
select username from user where username='zhangsan'

// 以下不能命中索引select username from user where pwd ='axsedf1sd'

7.确定知道只有1条数据，可以limit停止游标移动，提高效率

select name from user where username='zhangsan' **limit 1**

8.不要让数据库帮我们做强制类型转换

select name from user where telno=18722222222 // 可查到结果，会引起全表扫描
// 修改为
select name from user where telno='18722222222'

9.如果join，两表字段类型要相同

4.索引实现方案

本身也是一张表，保存了主键与索引字段，并指向实体表的记录
建立索引会占用磁盘空间的索引文件

• 优点：提高 DQL 速度

• 缺点：降低 DML 速度

FULLTEXT：全文索引 MyISAM

可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用
不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引
为了解决WHERE name LIKE “%word%”这类针对文本的模糊查询效率较低的问题

HASH：

可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率
“=”和“in”条件下高效，不支持范围查询、排序及组合索引

BTREE：

将索引值按一定的算法，存入一个树形的数据结构中（二叉树），
每次查询都是从树的入口root开始，依次遍历node，获取leaf，这是MySQL里默认和最常用的索引类型

RTREE：

仅支持 geometry 数据类型，支持该类型的存储引擎只有
MyISAM、BDb、InnoDb、NDb、Archive几种
相对于BTREE，RTREE的优势在于范围查找

5.索引种类

• NORMAL 普通：仅加速查询

• UNIQUE 唯一：加速查询 + 列值唯一（可以有null）

• PRIMARY 主键：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

• UNION 组合：多列值组成一个索引，专门用于组合搜索，效率大于索引合并

• FULLTEXT 全文索引：对文本的内容进行分词，进行搜索

6.联合索引 和 聚簇索引

1.联合index

• 1.多个独立索引很难形成三星索引

• 2.and相交，通常意味着需要创建联合索引，取代多个独立的单列索引

• 3.or联合，通常需要消耗大量CPU或内存资源在 算法的缓存、排序、合并操作上，特别当有些index列选择性不高，会返回大量数据进行合并操作

• 4.优化器不会把 3中提到的 排序、合并等计算到成本中，它只关心随机页读取成本，可能导致该执行计划不如全表扫描，查询成本被低估

• 5.遵循 左前缀 排序，当不需要考虑排序和分组时，采用将选择性最高的列放在最左边通常是最好的选择

2.聚簇Index

每一个叶子节点都包含了：主键值、事务ID、用于事务和MVCC回滚的指针以及剩余的其他列
Innodb 主索引文件上，直接存放该行数据，称为聚簇索引，次索引指向对主键的引用

myIsam 主索引和次索引和次索引，指向物理行

• 1.一种数据存储方式，将索引与数据存储在同一个叶子节点中
• 2.由engines实现
• 优点：
  • 1）减少了IO，提高了IO密集型应用性能，mysql IO按照页page读取的，读一个数据很大概率会连带读取了周边数据。符合用户使用一个数据，连带使用周边数据概率非常大，减少了IO
  • 2）访问更快 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值

• 缺点：

  • 1）当数据都在内存，聚簇减少IO的优势就没了
  • 2）插入速度严重依赖插入顺序
  • 3）代价高，强制Innodb将每个新更新的行移动到新位置上
  • 4）聚簇索引插入新列或者更新聚簇索引的时候可能导致页分裂
  • 5）导致全表扫描变慢，尤其是行比较稀疏或者由于页分裂导致数据存储不连续
  • 6）二级索引需要的存储空间更大，因为二级索引中包含了主键列，同时二级索引需要两次查询才能查询到行数据

7.优化从何而来

系统特别是微服务系统，请实用sql执行监控工具或方案进行监控，然后订立标准分析慢SQL并处理，否则会出现系统越来越大，越来越复杂，想优化无从下手的情况。

参考：