【mysql】数据库索引基础知识总结~

索引基础知识

索引(基于mysql5.5之后的默认数据库引擎是依靠innoDB实现的

1. 定义:索引是⼀种特殊的⽂件，包含着对数据表⾥所有记录的引⽤指针。可以对表中的⼀列或多列创建
   索引，并指定索引的类型，各类索引有各⾃的数据结构实现。通俗理解 他就书的目录 通过索引 我们可以快速找到书的内容

2. 数据库中一张表可以创建多个索引 一张表可以有多个书籍目录

3. mysql常见引擎(mysql5.5之后的默认数据库引擎innoDB 之前myISAM)

   1. 稳定性: innoDB支持事务 保证数据稳定性(比myISAM好)
   2. 性能:myISAM性能比innoDB好

4. 查询当前数据库引擎

   1. show variables like 'default_storage_engine';

5. 索引：

   1. 优点
      1. 索引提升查询效率
         1. 可避免顺序查询 直接将查询的访问定位出来
         2. 使用索引可以将数据库的关键索引信息存储到内存里 而内存的操作速度比磁盘快
      2. 缺点：
         1. 索引增加了维护成本，因为索引使用的是b+树 在数据添加和删除时会需要整理树结构 带来了新的开销
         2. 增加存储成本(磁盘 内存 空间成本的提升
         3. 索引过多会对mysql的优化器造成一定负担

6. 适合场景

   1. 数据量是否足够大 查询速度比较慢
   2. 是否是经常使用的列

7. 不适合场景

   1. 读取低频 添加删除高频的业务 不适合 (日志一年半载查不了一次)
   2. mysql服务器安装的电脑磁盘空间内存空间不足 不适合

8. 注意事项：

   1. 不常使用的列，或经常插入，修改操作的列，不适合加索引
   2. 索引会占用额外的磁盘，磁盘空间不足时，不考虑
   3. 不要再线上环境创建索引，创建索引会锁表，会导致其他接口用不了，可能会导致大事故

9. 索引分类

   1. 按是否为主键

      1. 主键索引(聚簇索引/聚集索引)：⼀种特殊的唯⼀索引，不允许有空值，⼀般是在建表的时候同时创建主键索引（通过 primary key）
      2. 非主键索引(非聚簇索引/非聚集索引/二级索引)：除主键索引之外的其他索引。

   2. 按特征分类

      1. 普通索引：可以重复也可以为 NULL 的索引
      2. 唯一索引(创建索引的字段要保证唯一性)：不能重复的索引。
      3. 联合索引(一个表中的多个字段组成的索引)：使⽤多个字段联合组成的索引

10. 索引的创建

    在创建表的时候 如果设置了主键约束或唯一约束 外键约束 会自动创建主键索引和唯一索引以及外键约束对应的索引（普通索引）。

11. 语法

    1. 索引查看：show index from t3
    2. 创建普通索引： create index idx_id on t3(id)
    3. 手动创建唯一索引： create uniqe index idx_unqie_name on t3
    4. 手动创建主键索引 ：alter table t4 add primary key(column)
    5. 删除索引：drop index idx_name on t3（索引名在表中是唯一的）

12. 索引vs约束

    1. 创建索引的时候会自动创建约束，创建约束的时候也会自动创建索引索引和约束是不同的业务定义，约束是用来规范数据的正确性，而索引用来提升数据库的程序性能

13. 创建索引的注意事项：在创建索引的时候会创建对应的约束，要确保原先的数据保持唯一约束，否则会创建失败

    索引基础知识1

14. InnoDB mysql实现原理

    1. 二叉树 缺点：层级比较高 查询维护不方便
    2. b-树 缺点：层级可以接受 将所有数据都存储在叶子节点和非叶子节点(行信息) 当数据量特别大的时候 光加载索引就需要很长的时间（所有节点存储信息）
    3. b+数 优化1 ：非叶子节点不再存储表数据了 2：叶子节点存储的不是数据 而是数据地址 3：一层上的节点采用链表链接了

15. 聚簇索引和非聚簇索引（二级索引）/聚集索引和非聚集索引的区别：

    1. 聚簇索引：只要查询到相应的主键id 就能得到id这一行的信息
    2. 二级索引：非叶子节点存储二级索引的值（字段k的值） 叶子结点存储主键id
    3. 聚簇索引对比的是主键，如果主键能够对应的上，那么就能够查询到主键对应的行数据。
    4. 但是二级索引的叶子节点存储的是主键，因此当二级索引匹配上之后，只能拿到主键的信息，然后根据主键的信息，去聚簇索引找到叶子节点对应的行数据，这样才能完成二级索引的数据查询。（回表查询）
    5. 答：聚簇索引的查询效率更快，二级索引需进行回表查询，因此他的效率更低

16. 索引校验：explain select * from where id=1;

17. 当key为null表示没有索引或者索引无效，若key有值表示索引生效。

18. 建表的时候创建索引

    1. 普通索引：key idx address(address)
    2. 联合索引：key idx address name age(address,name,age)

19. 联合索引一定非要注意创建的先后顺序
    （顺序：a,b,c

• 索引失效场景一：不满足最左匹配原则
  • 索引不失效：
    • a+b+c
    • a+b
    • a+c
  • 失效：
    • b+c
• 索引失效场景二：使用错误的模糊查询
  • 不失效：
    • 张%
  • 失效：
    • %张
    • %张%
• 索引失效场景三：索引查询列使用运算操作（±*/)
• 索引失效场景四：查询的列使用了函数
• 索引失效场景五：查询的列使用了隐式的类型转换
• 索引失效场景六：使用了is not null(is null可)