MySQL浅析

最新推荐文章于 2022-07-09 17:25:21 发布
最新推荐文章于 2022-07-09 17:25:21 发布
阅读量167 收藏
点赞数
分类专栏： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43185598/article/details/88533268
版权

一、索引基础

1.1 什么是索引

索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据结构。
在这里插入图片描述

1.2 索引的类型

可以使用SHOW INDEX FROM table_name;查看索引详情

1.2.1 主键索引 PRIMARY KEY

定义：它是一种特殊的唯一索引，不允许有空值。
创建：一般是在建表的时候同时创建主键索引。
注意：一个表只能有一个主键。

1.2.2 唯一索引 UNIQUE

定义：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
创建：1、通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引
2、可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);

1.2.3 普通索引 INDEX

定义：最基本的索引，它没有任何限制，只是加快查询速度。
创建：ALTER TABLE table_name ADD INDEX index_name (column);

1.2.4 组合索引

定义：即一个索引包含多个列，多用于避免回表查询。
创建：ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3)；

1.2.5 全文索引 FULLTEXT

定义：也称全文检索，是目前搜索引擎使用的一种关键技术
创建：ALTER TABLE table_name ADD FULLTEXT (column);
注意：1、索引一经创建不能修改，如果要修改索引，只能删除重建。可以使用DROP INDEX index_name ON table_name;
2、MyISAM引擎的特性

二、索引的数据结构

索引的数据结构主要有hash索引、二叉树索引、B树索引、B+树索引等

2.1 Hash索引

哈希表（Hash table，也叫散列表）， 是根据关键码值(Key value)而直接进行访问的数据结构。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。这个映射函数叫做散列函数，存放记录的数组叫做散列表。

哈希表hash table(key，value) 的做法其实很简单，就是把Key通过一个固定的算法函数既所谓的哈希函数转换成一个整型数字，然后就将该数字对数组长度进行取余，取余结果就当作数组的下标，将value存储在以该数字为下标的数组空间里。

而当使用哈希表进行查询的时候，就是再次使用hashCode()将key转换为对应的数组下标，再根据equals()定位到指定位置，获取value，如此一来，就可以充分利用到数组的定位性能进行数据定位。

优点：查询数据效率高，基于hash算法可以直接定位到数据位置回去数据
缺点：1、不支持范围查询
2、存在hash碰撞，不同key值会存在相同hash值得情况，最后还是通过实际值比较来获取数据，最后的效率也不一定能高起来

2.2 平衡二叉树（AVL树）

平衡二叉树定义(AVL)：它或者是一颗空树，或者具有以下性质的二叉排序树：它的左子树和右子树的深度之差(平衡因子)的绝对值不超过1，且它的左子树和右子树都是一颗平衡二叉树。

优点：基于二分法定位数据速度快
缺点：1、他是平衡树，数据的修改都需要左旋右悬来重新平衡数据结构，降低效率
2、每个节点的只能有2个节点，数据越多，则节点越深，最后的导致IO越深效率越低

2.3 B树

定义：B-Tree是一种平衡的多路查找(又称排序)树，在文件系统中和数据库系统中有所应用，主要用作文件的索引。其中的B就表示平衡(Balance) 。
在这里插入图片描述
特性：为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

d为大于1的一个正整数，称为B-Tree的度：
在这里插入图片描述
h为一个正整数，称为B-Tree的高度：
在这里插入图片描述

key和指针互相间隔，节点两端是指针：
在这里插入图片描述

一个节点中的key从左到右非递减排列：
在这里插入图片描述

每个指针要么为null，要么指向另外一个节点；
每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d：
在这里插入图片描述

每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null；
所有叶节点具有相同的深度，等于树高h。
在这里插入图片描述

某个指针在节点node最左边且不为null，则其指向节点的所有key小于key1，其中key1为node的第一个key的值：
在这里插入图片描述

某个指针在节点node最右边且不为null，则其指向节点的所有key大于keym，其中keym为node的最后一个key的值：
在这里插入图片描述

某个指针在节点node的左右相邻key分别是keyi和keyi+1且不为null，则其指向节点的所有key小于keyi+1且大于keyi：
在这里插入图片描述

数据查询：B-Tree的度可以非常大，高度会变的非常小，只需要二分几次就可以找到数据。例如一个度为d的B-Tree，设其索引N个key，则其树高h的上限为logd((N+1)/2))，检索一个key，其查找节点个数的渐进复杂度为O(logdN)。

  1. 先从根节点进行二分查找，如果找到则返回对应节点的data
  2. 否则对相应区间的指针指向的节点递归进行查找，如果找到则返回对应节点的data
  3. 如果找不到，则重复上述“对相应区间的指针指向的节点递归进行查找”，直到找到节点或找到null指针，前者查找成功，后者查找失败

优点：1、每个节点的子节点不限于2个，降低IO的性能消耗
缺点：1、范围查询需要回路查询，效率低
2、每个节点【key， value】存数据，有内存的消耗

2.4 B+Tree

定义：B+Tree是B-Tree的一种变种。一般情况下，B+Tree比B-Tree更适合实现外存储索引结构。

Innodb引擎中的B+树结构图：
在这里插入图片描述

MYISAM引擎中的B+树结构图：
在这里插入图片描述

特性：

  1. 每个节点的指针上限为2d
  2. 内节点不存储data，只存储key；

查询：
若此时，你在其他列创建索引例如name，它就会另外创建一个以name为索引的索引树。
执行select * from user where name = ‘吴磊’，他的执行过程如下：

  1. 找到name索引树
  2. 根据name的值找到该树下叶子的name的索引和主键值
  3. 用主键值去主键索引树去叶子节点到该条数据信息

优点：

  1. 单节点能存储更多数据，使得磁盘IO次数更少。
  2. 叶子节点形成有序链表，便于执行范围操作。

三、索引的设计

3.1 索引相关概念
3.1.1 基数

单个列唯一键（distict_keys）的数量叫做基数。
user表的总行数是5，gender列的基数是2，说明gender列里面有大量重复值，name列的基数等于总行数，说明name列没有重复值，相当于主键。

注意：返回表中30%内的数据会走索引，返回超过30%数据就使用全表扫描。30%是一个大概的范围。

3.1.2 回表

当对一个列创建索引之后，索引会包含该列的键值及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描，应该直接走全表扫描。

EXPLAIN命令：

  1. Using Index意味着不会回表，通过索引就可以获得主要的数据。
  2. Using Where则意味着需要回表取数据。
3.2 组合索引的设计
3.2.1 概念

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

3.2.2 示例应用

对列col1、列col2和列col3建一个联合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 test_col1_col2_col3实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

情景1：

SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

情景2：

SELECT * FROM test WHERE col1=“1” AND clo2=“2”

SELECT * FROM test WHERE col2=“2” AND clo1=“1”

两个查询语句都会用到索引(col1,col2)，因为两个查询语句都包涵索引(col1,col2)中的col1、col2两个字段，只是顺序不一样，查询条件一样，最后所查询的结果肯定是一样的

情景3：

SELECT * FROM test WHERE col2=2;

可以触发索引。
explain分别执行如下：

EXPLAIN SELECT * FROM test WHERE col2=2;

EXPLAIN SELECT * FROM test WHERE col1=1;

观察上述两个explain结果中的type字段。查询中分别是：

  • type: index
  • type: ref

index：这种类型表示mysql会对整个该索引进行扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。所以也也触发索引

ref：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

3.2.3 为什么要用联合索引

mysql创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段col1的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段col2进行排序。其实就相当于实现了类似 order by col1 col2这样一种排序规则。

  • 减少开销：建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销
  • 覆盖索引：对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
  • 效率高：索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知

四、索引的优化

4.1 查看索引使用情况

使用SHOW STATUS LIKE ‘Handler_read%’;查看索引的使用情况：在这里插入图片描述

  1. Handler_read_key：如果索引正在工作，Handler_read_key的值将很高。
  2. Handler_read_rnd_next：数据文件中读取下一行的请求数，如果正在进行大量的表扫描，值将较高，则说明索引利用不理想。
4.2 索引优化规则
4.2.1 如果MySQL估计使用索引比全表扫描还慢，则不会使用索引。
  1. 返回数据比列在在30%以上
4.2.2 前导模糊查询不能命中索引。
4.2.3 数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。
4.2.4 复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中符合索引。

注意：最左原则并不是说是查询条件的顺序，而是查询条件中是否包含索引最左列字段：

4.2.5 union、in、or都能够命中索引，建议使用in

查询的CPU消耗：or>in>union

4.2.6 用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。

因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加IO访问。

4.2.7 负向条件查询不能使用索引，可以优化为in查询

负向条件有：!=、<>、not in、not exists、not like等

4.2.8 范围条件查询可以命中索引
  1. 范围条件有：<、<=、>、>=、between等。
  2. 范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引;
  3. 范围查询和等值查询同时存在，优先匹配等值查询列的索引
4.2.9 数据库执行计算不会命中索引

计算逻辑应该尽量放到业务层处理，节省数据库的CPU的同时最大限度的命中索引。

4.2.10 利用覆盖索引进行查询，避免回表。

被查询的列，数据能从索引中取得，而不用通过行定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

4.2.11 建立索引的列，不允许为null

单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集，所以，请使用not null约束以及默认值。
IS NULL可以命中索引

4.2.12 其他情况：
  1. 更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的。
  2. 区分度不大的字段上不宜建立索引：类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以外的情况下，优化器不会选择使用索引。
  3. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引
  4. 多表关联时，要保证关联字段上一定有索引。虽然唯一索引会影响insert速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生。
  5. 创建索引时避免以下错误观念：索引越多越好，认为一个查询就需要建一个索引；宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度；抵制唯一索引，认为业务的唯一性一律需要在应用层通过“先查后插”方式解决；过早优化，在不了解系统的情况下就开始优化。

五、数据库逻辑

5.1 sql执行顺序

在这里插入图片描述

5.2 逻辑结构图

在这里插入图片描述

快乐小石头
关注
专栏目录
浅谈mysql知识点(一)
shen13peng的博客
08-18 181
1、mysql的引擎 ----------》参考文档《---------- 说到mysql，我们不得不谈一谈mysql的引擎，这个是绕不开的话题。 我的mysql数据库版本是5.7版本的，目前大多数企业使用的并不是最新版的8.X，而是之前很流行的5.7版本，因为数据库的数据迁移是一件很麻烦的事情，动辄伤筋动骨，所以现在虽然mysql出了8版本的，但是很多企业还采用5.7版本，我们就按照这个版本学习。 首先，先来看一下mysql的引擎： show engines; 是不是惊了一下，怎么这么多？哈哈，没关系
浅谈MySQL
qq_44762389的博客
10-03 201
MySQL介绍 MySQL是目前非常流行的开源关系型数据库，不仅是免费的，可靠性高，速度也比较快，而且拥有灵活的插件式存储引擎。 MySQL是一个C/S模型，启动MySQL后，本地会启动mysqld服务进程，客户端通过mysql连接服务器，进行数据存储查询。 存储引擎 存储引擎是数据库系统管理数据，存储数据的核心方法，MySQL核心就是插件式存储引擎，支持多种存储引擎，本文主要介绍两种存储引擎：MyISAM、InnoDB。MySQL在5.58版本之前默认使用MyISAM，从5.58开始默认使用InnoDB，
MySQL系列之架构浅析
魔幻世界
10-12 940
数据库架构 MySQL目录结构 InnoDB目录结构 核心类库 客户端与服务端交互 > 真正的高手从来不是临场发挥，随机应变是外人看来的错觉。 MySQL是一个开放源代码的关系数据库管理系统。原开发者为瑞典的MySQL AB公司，最早是在2001年MySQL3.23进入到管理员的视野并在之后获得广泛的应用。 2008年MySQL公司被Sun公司收购并发布了首个收购之后的版本MySQL5
MySQL浅谈
下里巴人
03-01 811
MySql存储引擎 “存储”的意思的存储数据。“引擎”源于发动机，它是发动机中的核心，故可以理解成 它是数据库的核心（比较尬......）。 常见的Mysql存储引擎（按推荐度排名）： Innodb引擎（5.5版本之后Mysql默认引擎）：Innodb引擎提供了对数据库ACID事务的支持，并且还提供了行级锁和外键的约束，缺点（占用空间大） 四大特性： 插入缓冲（insert buffer) 二次写(double write) 自适应哈希索引(ah...
MySQLWhere浅析
02-26
一条SQL，在数据库中是如何执行的呢？相信很多人都会对这个问题比较感兴趣。当然，要完整描述一条SQL在数据库中的生命周期，这是一个非常巨大的问题，涵盖了SQL...更好地理解一些术语，例如：MySQL5.6中一个重要的优化
浅析mysql索引
09-09
数据库索引是一种数据结构，目的是提高表的操作速度，下面通过本文给大家分享mysql索引的相关知识，感兴趣的朋友一起看看吧
Mysql binlog浅析
03-30
这是一份腾讯内部的技术文档，详细讲述了 MySQL 数据库启用二进制日志 binlog 的细节，非常有参考价值。前言：日志是把数据库的每一个变化都记载到一个专用的文件里，这种文件就叫做日志文件。Mysql默认只打开出错...
MySQL异常处理浅析
09-10
主要介绍了MySQL的异常处理,需要的朋友可以参考下
mysql 浅谈
牛牛的博客
10-30 248
1. 主键     主键不能重复，是唯一索引的一种，且必须定义为"PRIMARY KEY",一个表中只能有一个主键     示例：建表时------   PRIMARY KEY (`id`)                 更新主键------ ALTER table "表名" ADD  PRIMARY KEY('....') 2.  外键      定义：如果一个实体的某个字段
浅谈Mysql
m0_58600477的博客
09-25 351
MySQL简单的SQL语句limit限定字段order bywherebetween and空值检查and/or/notin模糊查询 通配符%/_正则表达式 简单的SQL语句 limit 取出表order的前三行数据 SELECT * FROM orders LIMIT 3 取order表中的第二行到第三行 SELECT * FROM orders LIMIT 2,3 从order表中从第二行开始往下取三行 SELECT * FROM orders LIMIT 2 OFFSET
浅谈mysql
weixin_46949165的博客
08-12 161
认识mysql： [学习mysql]:https://www.runoob.com/mysql/mysql-tutorial.html 一、 操作mysql的方式: 命令行 Navicat 数据库可视化工具，可以直接在命令行对数据库的数据进行增删改查等操作 二、使用Navicat操作数据库 1.创建连接 2.创建一个数据库 3.在tables下边创建表 4.在表内进行增删改查 三、命令行操作数据库的常用命令 查 查全表 select * from 表名 带条件查询全表 select *
(三)mysql_浅谈MySQL（三）
weixin_39884323的博客
01-20 65
//文章浅显，大神勿怪，欢迎讨论//未完待续之前我们说到数据库像一个异世界的图书馆，我们是其中的管理员(对，就是那位man)，管理着图书馆里的所有资源，如书架(Databases)，里面装着书(Database)，书里面是一张张布满数据的纸(Table)。在日常生活中，我们用MySQL就是来存放与操作数据的，问题有两个，一是数据从何而来(数据的定义与类型请看前两话)，二是数据存放在哪。关于第二个问...
浅谈Mysql优化
qirjwnouh的博客
02-06 398
Mysql的优化: ----------------------------------------------------------------------------------------------------------------- 优化概述: 存储层:存储引擎,列类型,范式规范 设计层(单台服务器):索引,缓存,分区(分表) 架构层:读写分离(主从复制) SQL语句层:
mysql中的回表操作
qq_53310540的博客
07-09 4743
mysql索引
mysql查询id在一定区间_数据库查询区间数据
weixin_32520601的博客
01-31 5135
这里我说的区间数据就是说，比如我们只查询第20-30条数据。以下以查询20-30条数据为例：MySQLmysql> SELECT * FROM table LIMIT 20,10; // 检索记录行 21-30//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录...
浅谈MySQL的整体架构
weixin_34192732的博客
06-17 737
前言又是新的一周，各位周一好。由于换工作，找房子这一系列事情都推在了一起，所以最近停更了一个多月。现在所有的事情都已尘埃落定，我也可以安安静静的码字啦。好了，也不多说，开始新的征程啦。最近一直在看《MySQL技术内幕——InnoDB存储引擎》这本书，也正好记录下吧。整体架构图我们先看一下MySQL的架构图，对其先有一个整体的了解。MySQL主要分为四层架构，分别是网络连接层，服务层，存储引擎层，物...
rockyou.txt
06-06
rockyou
ASP+ACCESS网上人才信息管理系统(源代码+论文)【ASP】.zip
最新发布
06-06
ASP+ACCESS网上人才信息管理系统(源代码+论文)【ASP】
浅析docker-compose部署mysql无法访问的问题
09-06
当使用 docker-compose 部署 MySQL 时，可能会遇到无法访问 MySQL 的问题。出现这种情况一般有以下几个可能的原因： 1. 网络配置问题：docker-compose 在默认情况下会创建一个默认的网络，并将所有定义的服务连接到...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值