Mysql索引原理

索引

所有的索引都会创建一个B+树

（1）主键索引：非空唯一索引，一个表只有一个主键索引；在 innodb 中，主键索引的 B+ 树包含表数据信息

（2）唯一索引：不可以出现相同的值，可以有NULL值

（3）普通索引：允许出现相同的索引内容

（4）组合索引：对表上的多个列进行索引

（5）全文索引：将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词 FULLTEXT; 在短字符串中用 LIKE % ；在全文索引中用 match 和 against ；

主键选择

innodb 中表是索引组织表，每张表有且仅有一个主键；

1. 如果显示设置 PRIMARY KEY ，则该设置的key为该表的主键；

2. 如果没有显示设置，则从非空唯一索引中选择： 只有一个非空唯一索引，则选择该索引为主键； 有多个非空唯一索引，则选择声明的第一个为主键； 没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键；

约束

为了实现数据的完整性，对于innodb，提供了以下几种约束，primary key，unique key， foreign key， default, not null

外键约束

外键约束具备事务性，不会被其他语句干扰

外键用来关联两个表，来保证参照完整性；MyISAM存储引擎本身并不支持外键，只起到注释作 用；而innodb完整支持外键；

约束与索引的区别：创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束是逻辑上的概念；索引是一个数据结构既包含逻辑的概念也包含物理的存储方式；

索引的实现

B+树是多路平衡搜索树（矮胖），而红黑树是平衡二叉搜索树（瘦高）。从名称就可以看出来，搜索树按照中序遍历会是一个有序的结果，平衡的目的是平衡树的高度，提供一个稳定的时间搜索复杂度，我们希望对树进行增删改的时候都会维持它的高度，而不是变成链表，这样搜索复杂度会变成O(n)。树的高度代表了比较的次数，代表了对磁盘的访问次数。

B+树一个非常平衡的树，红黑树不如B+树平衡，这体现在B+树的叶子节点都在同一层，每条链路的高度都是一样的。红黑树的每一个节点都是一对kv，B+树的每一个节点是多个kv。

B+树

全称：多路平衡搜索树，减少磁盘访问次数；用来组织磁盘数据，以页为单位，物理磁盘页一般为 4K，innodb 默认页大小为 16K；对页的访问是一次磁盘io，缓存中会缓存常访问的页；

特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息；叶子节点之间互相连接，方便范 围查询；

P指下一次查找的物理磁盘的地址

B+树会经常进行范围查询，这些范围查询要求我们快速索引数据，而B+树的所有叶子节点之间会相互连接，因此不需要回溯

B+树层高问题

B+树的一个节点对应一个数据页；B+树的层越高，那么要读取到内存的数据页越多，io次数越多；

Mysiam和InnoDB对比

mysiam的数据文件和索引文件用两个文件存储，索引文件存储索引和行所在数据文件的地址，mysiam进行select查询的时候会进行回表查询，即先找到索引，通过索引找到行数据文件的地址，再回到数据文件中去查询

聚集索引

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

辅助索引

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

mysiam查行数据每次都是回表 查询，InnoDB如果通过主键查询只会走聚集索引，不涉及回表查询

索引存储

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

Buffer Pool

Buffer pool 缓存表和索引数据；采用 LRU 算法（原理如下图）让 Buffer pool 只缓存比较热的数 据  

mysql插入到最中间，不认为新插入的数据是最热 的数据，如果经常访问会往前面移动

Change Buffer

Change buffer 缓存非唯一索引的数据变更（DML增删改操作），Change buffer 中的数据将会异步 merge 到磁盘当中；对唯一索引的数据修改直接写入数据库

Log Buffer

最左匹配原则

对于组合索引，从左到右依次匹配，遇到 > < between like 就停止匹配

覆盖索引

从辅助索引中就能找到数据，不需要通过聚集索引查找（不需要回表查询，叫少磁盘IO），select比较少的数据

索引失效

B+树通过比较key来维持有序性

select ... where A and B 若 A 和 B 中有一个不包含索引，则索引失效；

索引字段参与运算，则索引失效；例如： from_unixtime(idx) = '2021-04-30';

索引字段发生隐式转换，则索引失效；例如： '1' 隐式转换为 1 ；

LIKE 模糊查询，通配符 % 开头，则索引失效；例如： select * from user where name like '%Mark';

在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0 ；

组合索引中，没使用第一列索引，索引失效； in + or 索引失效；单独的in 是不会失效的；not in  肯定失效的；

索引原则

查询频次较高且数据量大的表建立索引；

索引选择使用频次较高，过滤效果好的列或者组合；

使用短索引；节点包含的信息多，较少磁盘io操作；比如：smallint，tinyint； 对于很长的动态字符串，考虑使用前缀索引

如何找到有问题的Sql语句

1.htop top 看CPU磁盘 

2.sql-slow-log 慢查询日志 找到哪条语句有问题 默认10s

3.线上时候 用户访问很慢  

show processlist 查看连接

show full processlist 查看哪条语句 然后用explain分析 如果还分析不出 ，就去看优化器选择过程