mysql|MySQL索引原理以及SQL优化数据库|sql

文章目录

前言
一、MySQL中的索引与约束
- - 索引
  - - 索引的概念
    - 索引的分类
  - 约束
  - - 约束的概念
    - 约束的分类
  - 索引与约束的区别
二、索引的存储
- - 索引存储结构的选择
  - B+树的索引存储以及查找
  - - B+树映射磁盘和快速查找
  - 关于自增id
  - 聚集索引
  - 辅助索引
三、最左匹配原则和覆盖索引
- - 最左匹配原则
  - 覆盖索引
四、索引失效
五、索引原则
六、问题的解决与定位

前言为了更好的阐述本节知识的内容，将从以下内容进行阐述。
1.索引的概念，有哪些索引。索引与约束区别是什么
2.为什么要使用B+ 树，有什么特征，什么类型都是？
3.最左匹配原则覆盖索引
4.索引失效
5.索引原则
6.怎么做？
一、MySQL中的索引与约束索引
索引的概念 MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
索引其实是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
对于一本字典来说，查找某个汉字有两种方式：
第一种方式：一页一页挨着找，直到找到为止，这种查找方式属于全字典扫描。效率比较低。
第二种方式：先通过目录（索引）去定位一个大概的位置，然后直接定位到这个位置，做局域性扫描，缩小扫描的范围，快速的查找。这种查找方式属于通过索引检索，效率较高。
明白了什么是索引，接下来我们看看都有哪些索引
索引的分类索引分类：主键索引、唯一索引、普通索引、组合索引、以及全文索引（elasticsearch）；
主键索引：
数据库管理系统对于主键自动生成唯一索引，所以主键是一个特殊的索引
注意：
主键是一定是唯一性索引，但唯一性索引不一定是主键
唯一索引标识索引值唯一，一个表可以有多个唯一索引，但主键只能有一个
主键列不能为空，但唯一索引列可以为空
一张表只能有一个主键，但可以有多个索引
PRIMARY KEY(key)
唯一索引：
唯一索引可以避免数据出现重复。**唯一索引可以有多个，但索引列的值必须唯一，索引列的值允许有空值。**创建唯一索引可以使用关键字UNIQUE随表一同创建。
UNIQUE(key)
普通索引：
最基本的索引，没有任何限制，是我们经常使用到的索引。允许出现相同的索引内容；
组合索引：
两个或更多个列上的索引
全文索引：
将存储在数据库当中的整本书和整篇文章中的任意内容信息查找
约束
约束的概念约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。
约束的分类 innodb中约束分类：主键约束、外键约束、唯一约束、检查约束、非空约束、默认值约束
主键约束：
在innodb 中表是索引组织表，每张表有且仅有一个主键；
1 如果显示设置 PRIMARY KEY ，则该设置的key为该表的主键；
2 如果没有显示设置，则从非空唯一索引中选择；
3 只有一个非空唯一索引，则选择该索引为主键；
4 有多个非空唯一索引，则选择声明的第一个为主键；
5 没有非空唯一索引，则自动生成一个 6 字节的 _rowid 作为主键；
外键约束：
外键用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。MyISAM存储引擎本身并不支持外键，只起到注释作用；而innodb完整支持外键；
在项目中不建议使用外键，分库分表的场景，无法使用外键
索引与约束的区别
创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束时逻辑上的概念；索引是一个数据结构既包含逻辑的概念也包含物理的存储方式；约束是为了保证表数据的完整性，索引是为了提高查询效率。
二、索引的存储索引存储结构的选择
众所周知，MySQL的索引存储数据结构选择的是B+树。但是为什么选择B+树，选择B+树有什么优点。我们来分析下：
一说到索引，我们脑海不仅想到几种数据结构：二叉查找树进行二分查找、B-Tree、B+ -Tree、Hash。接下来我们分析下这几种数据结构进行索引存储的优缺点。
二叉查找树：链化严重。如果存储的数据比较极端，如数据一直是升序的,可以导致树像链表一样,查找速度大大下降。对于普通的二叉查找树的弊端，我们可以用平衡二叉查找树来代替。但是当数据量很大时，一般mysql中一张表达到3-5百万条数据是很普遍的，因此平衡二叉树的深度还是非常大，mysql读取时还是会消耗大量IO。并且存储的数据量太小了，没有很好的利用操作磁盘IO的数据交换特性(我们可以一次读4K数据的,现在只读了1个结点进去,而且这个结点只存了一个数据,非常浪费操作系统资源)，也没有利用好磁盘IO的预读能力（空间局部性原理）(预读；每一次IO时，不仅仅把当前磁盘地址的数据加载到内存，同时也把相邻数据也加载到内存缓冲区中。) 从而带来频繁的IO操作。
B-Tree和B+ -Tree的选择：解决平衡二叉树树深度的问题，解决了平衡二叉树读取消耗大量内存空间的问题。因为B-树每个节点可以存放多个关键字，最大限度的利用了从磁盘读取的内存空间，单节点存放多个关键字同时也大大减少了树的深度。极大的提高了mysql的查询性能。
但是B+ -Tree关键字不存放数据,只存放索引信息,因此内部结点相对B-Tree更小。并且B+ -Tree查询效率更加稳定，由于内部结点并不是最终指向文件内容的结点而只是叶子结点中关键字的索引,所以任何关键字的查找必须走一条从根节点到叶子结点的路,所有查询的长度相同,导致每个数据的查询效率也几乎是相同的。但是对于B-Tree对有范围查找的查询(如age>20)时采用的还是中序排序法，因此也需要多遍历，并且查询性能不稳定，比如查询(select * from table where id = 222 和 select * from table where id = 223)时在查询效率(耗时)上可能会存在一定的差别，因为B-树还是将关键字，这里为id，存放在根节点和叶节点的，如果运气好，可能id=222这个关键字就在第一个节点，消耗一次IO就找到了，而id=223可能在叶节点，需要消耗3次IO才能找到。因此B-树对同一条sql语句的查询性能可能会有很大影响(确实感觉有点扯，但是事实时这样)。
B+树天然有序,更有利于对数据库的扫描（排序能力强）， B-Tree树在提高IO性能时候,并没有解决元素遍历效率底下问题.而B+Tree只需要遍历叶子结点就可以解决对全部关键字信息的扫描,做范围查询相当方便(所有叶子节点均有一个链指针指向下一个叶子结点)
接下来我们看看B-Tree和B+ -Tree的结构图更加清楚：

文章图片

文章图片

hash：无规则、不能排序。仅仅能满足“=“，“IN”，不能使用范围查询；无法被用来避免数据的排序操作；不能利用部分索引键查询；不能避免表扫描
了解Mysql中索引的数据结构选择。接下来我们再接着看，B+Tree是怎么存储索引信息的以及是怎么查找的。
B+树的索引存储以及查找
B+树的全称是，多路平衡搜索树，提供一个稳定搜索时间复杂度，高度平衡，叶子节点都在同一层，每一条链路的高度都是一致的
全称：多路平衡搜索树，减少磁盘访问次数；用来组织磁盘数据，以页为单位，物理磁盘页一般为4K，innodb 默认页大小为 16K；对页的访问是一次磁盘io，缓存中会缓存常访问的页；
特征：非叶子节点只存储索引信息，叶子节点存储具体数据信息；叶子节点之间互相连接，方便范围查询；每个索引对应着一个B+树；
下图的区间左闭右开，如索引1-18（不包括18）在页2中，18-35（不包括35）在页3中

文章图片

B+树映射磁盘和快速查找从一个节点出发，对应的是一个16k的数据，平常去调用访问那些io函数，去访问磁盘访问数据的时候，通常是4k或者8k，B+树呢就应该是4k的整数倍，一个节点的大小是固定的，通常是16k，B+树一个节点至少要存储两行数据非叶子节点只存储索引信息，也会指向下一个物理磁盘的地址，比如说查找磁盘的时候，会执行范围查询，然后根据范围选择下一个磁盘
所有的叶子节点之间都是相互引用的，叶子节点之间存储前后叶子节点的物理地址，叶子节点存储具体的数据信息，不用去回溯地去查找
如下图所示，叶子节点之间都是互相引用的

文章图片

关于自增id
类型 bigint 范围: -2的63次方到2的63次方减去1; 假设采用 bigint 1秒插入1亿条数据，大概需要5849年才会用完索引。所以不用担心超过最大值报错
聚集索引
按照主键构造的 B+ 树；叶子节点中存放数据页；数据也是索引的一部分；
在Innodb引擎(聚集索引)中创建的表，默认就是以主键为索引。会生成两个文件：.ibd 索引文件 .frm数据结构类型
聚集索引下对下面sql查询语句，执行的操作

# table id name select * from user where id >= 18 and id < 40;

首先是根节点，第一次io，查询大于18，就能定位到p2所对应的地址，然后找到页3，然后找到页8，一直查到页12 （共七次io查询，不用进行回溯）在所有叶子节点里边的数据都是有序的，所以采用的是二分查找，如果没找到或者找到了想找下一条，不用回溯到上一层，而是直接通过索引去寻找下一个叶子节点。

文章图片

辅助索引
【mysql|MySQL索引原理以及SQL优化】除了主键外构建的索引。叶子节点不包含行记录的全部数据；辅助索引的叶子节点中，除了用来排序的 key 还包含一个bookmark （索引+主键）；
对下面sql查询语句，执行的操作：

-- 某个表包含 id name lockyNum; id是主键，lockyNum存储辅助索引; select * from user where lockyNum = 33;

每一个索引会对应一个B+树，lockynum不是主键，走辅助索引，通过索引信息去找主键的id，通过主键id走聚集索引找一行数据，叶子节点的数据，索引+主键id。再通过主键id找具体信息聚集索引叫回表查询（解决了多个索引情况下，数据重复多份的问题）

文章图片

三、最左匹配原则和覆盖索引最左匹配原则
在Mysql建立多列索引（联合索引）有最左匹配的原则，即最左优先。
如果我们建立了一个2列的联合索引(col1,col2),实际上已经建立了两个联合索引(col1)、(col1,col2）; 如果有一个3列索引(col1,col2,col3)，实际上已经建立了三个联合索引(col1)、(col1,col2)、(col1,col2,col3)。
1、b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道第一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
2、比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。（这种情况无法用到联合索引）
能不能用到索引可以看第一段分析的，建立的有一个联合索引，其实相当于建立了几个索引，便知。
覆盖索引
从辅助索引中就能找到数据，而不需通过聚集索引查找，不需要回表操作。；利用辅助索引树高度一般低于聚集索引树；较少磁盘 io；
同样的有复合索引（a,b,c），如果有如下的sql: select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一
四、索引失效 select … where A and B 若 A 和 B 中有一个不包含索引，则索引失效；
索引字段参与运算，则索引失效；例如： from_unixtime(idx) = ‘2022-04-05，这个太复杂了’;
索引字段发生隐式转换，则索引失效；例如： ‘1’ 隐式转换为 1 ；
LIKE 模糊查询，通配符 % 开头，则索引失效；例如： select * from user where name like ‘%Mark’;
在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0 ；
组合索引中，没使用第一列索引，索引失效；
in + or 索引失效；单独的in 是不会失效的； not in 肯定失效的；
B+树的有序性是通过比较K得出来的
五、索引原则查询频次较高且数据量大的表建立索引；
索引选择使用频次较高，过滤效果好的列或者组合；
使用短索引；节点包含的信息多，较少磁盘io操作；比如：smallint，tinyint；
对于很长的动态字符串，考虑使用前缀索引；
有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的区分度，索引的区分度是指不重复的索引值和数据表记录总数的比值。索引的区分度越高则查询效率越高，因为区分度更高的索引可以让mysql在查找的时候过滤掉更多的行。
对于 BLOB , TEXT , VARCHAR 类型的列，必要时使用前缀索引，因为mysql 不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的区分度
六、问题的解决与定位有问题的sql语句怎么找
1、htop top 看cpu 磁盘
2、sql-slow-log 10s看日志查询
3、线上的时候，有些用户访问特别慢
看完整的语句
slow processlist看哪条连接出问题了
show full processlist就能看到是哪条语句出了问题
再用explain进行分析，可能分析不出什么问题，通过输出那几个字段很难描述问题的复杂性
所以我们还需要把优化器的选择过程弄出来
优化器根据解析树可能会生成多个执行计划，然后选择最优的的执行计划；
SHOW VARIABLES LIKE ‘optimizer_trace’;
– 启用优化器的追踪
SET optimizer_trace=‘enabled=on’;
– 执行一条查询语句
SELECT * FROM information_schema.optimizer_trace;
– 用完关闭
SET optimizer_trace=“enabled=off”; SHOW VARIABLES LIKE ‘optimizer_trace’;
推荐一个零声学院免费公开课程，个人觉得老师讲得不错，
分享给大家：[Linux，Nginx，ZeroMQ，MySQL，Redis，
fastdfs，MongoDB，ZK，流媒体，CDN，P2P，K8S，Docker，
TCP/IP，协程，DPDK等技术内容，点击立即学习:服务器课程