mysql索引

1. 索引 物理概念 约束 逻辑概念

2. B+树 为什么使用？ 特征？ 类型

3. 最左匹配原则 覆盖索引

4.索引失效

5.索引原则

6.怎么做？ 如何定位问题以及怎么解决问题？

 

索引包括：主键、唯一、普通、组合以及全文索引（elasticsearch）

唯一索引允许有一个为NULL

主键唯一且不为空 ，必须存在 

外键具备事务性

 

红黑树全称平衡二叉搜索树，中序遍历是一个有序的结构

B+ 树全称多路平衡搜索树，平衡的是树的高度，提供一个稳定的搜索时间复杂度。高度平衡，叶子节点都在同一层，每条链路的高度是一致的。

为什么使用？

B+树是一个矮胖型搜索树，树的高度代表着比较的次数，红黑树在内存中比较，而B+树是在磁盘中比较，访问效率不同，一次磁盘IO大约是10ms ，内存寻址100ns，百万数量级的差异，避免多次磁盘io，选择矮胖的数据结构。

B+树一个节点的大小是4k或者8k的整数倍，16k，一个节点至少要存储两行数据。一行是索引信息用来比较的，第二行是物理磁盘的地址

B+树的非叶子节点存储的是索引信息，叶子节点存储着数据信息。针对不同B+树，数据信息不同。

B+树的范围查询，查询语句limit 1，5， 所有叶子节点相邻之间都有连接，每个叶子节点存储着下一个节点的物理地址。

B+树一般2-4层，如果超过500万行，就要考虑分表分库。

 

mysql文件里中，后缀名为frm表示表信息文件，

MYD表示数据文件，中MY表示myisam，D表示data， 堆表进行组织的

MYI表示索引文件，B+树进行组织的，叶子节点存储的是索引和行所在数据文件中的地址

回表查询：查询到B+树后，去数据表中查询。mysam每次都是回表查询，除非查询的是主键id。

 

innodb有两个文件

frm 表示表信息文件

ibd，用的B+树，是聚集索引，主键创建的索引，叶子节点存储的是数据

在B+树中查找某个索引时，所用二分查找的方式，因为B+树是顺序的

 

辅助索引，非主键索引创建的索引，叶子节点存储的是非主键索引信息和主键id，存主键id的原因是防止存储的数据重复。通过主键id再去查询数据是回表查询

 查询条件是辅助索引就走辅助索引b+树。

 

innodb由段、区、页组成，一般4-5个区，区由64个连续的页组成，页的默认值为16k；

innodb体系结构，buffer pool，访问磁盘时，会将数据缓存到buffer pool中，

change buffer，缓存非唯一索引DML操作，进行DML后，先在change buffer中（因为是非唯一的，要求不是很严格），然后异步刷盘

如果是唯一索引，先写日志，然后异步刷盘，涉及到随机io和顺序io的差异，B+树刷盘是随机io比较慢，因此要先写日志，再刷盘。

change buffer占buffer pool的四分之一，buffer pool大概有128M，采用LRU，最近最少使用。mysql采用不同的 LRU，把刚刚插入的数据插到中间，如果后面使用，会把这个数据移到前面。 

 

最左匹配索引

多个key进行比较，比较规则，先比较k1，k1相同才会比较k2，B+树中k2不一定有序，k1有序。

看走不走索引，有以下几种情况：

select 。。。 where col

group by col having col

order by col

join on col

对于组合索引，从左到右一次匹配，遇到>< between like 就停止匹配，打断；最左边的索引一定要有

explain作用在优化器阶段

如果要查询的字段都在辅助索引，就会走辅助索引（即使使用的联合的索引不是最左的那个索引）

覆盖索引

从辅助索引中给就能找到数据，而不需通过聚集索引查找；利用辅助索引一般低于聚集索引树；较少磁盘io；覆盖索引有可能不使用最左匹配原则。

 

索引原则：

1.查询频次较高且数据量大的表建立索引；索引选择使用频次较高，过滤效果好的列或者好的列或者组合；

2.使用短索引，节点包含的信息多，较少磁盘io操作；

3.对于很长的动态字符串，考虑使用前缀索引：

 

 找一个最大值，越大区分度越高，作为前缀位数。

4.尽量扩招索引，在现有索引的基础上，添加复合索引，不要超过6个

5.不要select *；尽量只列出需要的列字段，方便使用广覆盖索引；

6.索引列，列尽量设置为非空；

7.开启自适应hash索引或者调整change buffer；

 

优化器成本分析

mysql优化器主要针对IO和CPU会计算语句的成本，可能不会按照分析的原理来执行语句

成本分析步骤：

1.找出所有可能需要使用到的索引；

2.计算全表扫描的代价 

3.计算不同索引执行查询的代价；

4.对比找出代价最小的执行方案

 

 简单的运算优化器可能做。

模糊查询通配符如果在字符串后面则可以比较，走索引

 

 

 

找到有问题的sql语句？

1.通过explain进行分析索引

2.htop top看cpu磁盘

3.sql-slow-log 10s  可以看到完整的语句

4.线上的时候，show processlist    或者show full processlist

 

搜索

复制