mysql索引

mysql索引

文章目录

• 一、概念
• 二、索引实现
• • 1.存储
  • 2.页
  • 3.B+树
  • 4.聚集索引
  • 5.辅助索引
  • 6.最左匹配原则
  • 7.覆盖索引
• 三、索引构建原则
• • 1. 索引失效
  • 2. 索引原则
  • 3. EXPLAIN指令

---

一、概念 概念表达式主键索引非空唯一索引，一个只有一个主键索引；innodb中，主键索引的B+树包含表数据信息PRIMARY KEY(key)唯一索引不可以出现相同的值，可以有NULL值UNIQUE(key)普通索引允许出现相同的索引内容INDEX(key) OR KEY(key[,…])组合索引对表上的多个列进行索引INDEX idx(key1,key2[,…])全文索引将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术FULLTEXT (key1, key2) 二、索引实现 1.存储

Innodb由段、区、页组成分为数据段、索引段、回滚段等；
区大小为1MB（一个区由64个连续页构成）；
页的默认值为16k（逻辑页）
为了保证页的连续，存储引擎一般一次从磁盘中申请4~5个页

2.页

innodb磁盘管理的最小单位；默认16k，可通过innodb_page_size参数来修改；
B+树的一个节点的大小就是该页的值

3.B+树

在B树的基础上优化为B+树，目的为了方便范围查找，所以将叶子节点相连

4.聚集索引

按照主键构建的B+树；叶子节点中存放数据页；数据也是索引的一部分

5.辅助索引

叶子节点不包含行记录的全部数据；辅助索引的叶子节点中，除了用来排序的key还包含一个bookmark；该书签存储了聚集索引的key

6.最左匹配原则

对于组合索引，从左到右一次匹配，遇到> 7.覆盖索引

从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引树；减少磁盘io；

三、索引构建原则 1. 索引失效

• select … where A and B 若A和B中有一个不包含索引，则索引失效；
• 索引字段参与运算，则索引失效；
• 索引字段发生隐式转换，则索引失效；'1’隐式转换为1
• LIKE模糊查询，通配符%开头，则索引失效；
• 在索引字段上使用NOT <> != 索引失效；如果判断id<>0则修改为idx>0 or idx<0;
• 组合索引中，没有第一列索引，索引失效

2. 索引原则

• 查询频次较高且数据量大的表建立索引；索引选择使用频次较高，过滤效果好的列或者组合；
• 使用短索引；节点包含的信息多，较少磁盘io *** 作
• 对于很长的动态字符串，考虑使用前缀索引；
• 对于组合索引，考虑最左侧匹配原则和覆盖索引；
• 尽量选择区分度高的列作为索引；该列的值相同的越少越好；
• 尽量扩展索引，在现有索引的基础上，添加符合索引；
• 不要select *；尽量只列出需要的列字段；
• 索引列，列尽量设置为非空；

3. EXPLAIN指令

概念：用来查看SQL语句的具体执行过程。
原理：模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。