mysql查询除主键外的所有数据_mysql索引是开发人员都必须知道的，这篇mysql索引原理送给大家...

最新推荐文章于 2021-01-27 08:28:10 发布
最新推荐文章于 2021-01-27 08:28:10 发布
阅读量473 收藏 1
点赞数
文章标签： mysql查询除主键外的所有数据
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39827036/article/details/111576084
版权

7a0e7ed37cf8dbb6269782be84102fd6.png

来源:jianshu.com/p/b9d4c13ae33d

前言

网上已经有了很多相关mysql索引原理的文章，但是都存在一些问题，有的是直接复制别人的比较老的文章，有的直接开篇讲B+Tree的原理，过程不是很清楚，即使原理讲清楚了，没有各种数据结构的对比也很难体现出B+Tree的优越性，其实我觉得从一些问题来看，然后根据发现问题解决问题的思路来看，这样可能学习效果会更好，所以写了这篇文章，希望你能喜欢

问题列表

1:数据库中最常见的优化方式是什么？
加索引(相信这个大家都知道)
2:为什么加索引能优化慢查询？
。。。。。
3:哪些数据结构可以优化查询速度？
。。。。。
4:这些数据结构都可以优化查询速度，为何mysql会选择B+Tree
。。。。。
5:数据库能存多少数据？大概数据达到多少会到达峰值？计算依据是什么？。。。。。

数据库查询过程

对于一般字段而言，mysql查询都是采用的全表扫描的方式来进行数据查询

eddf048c1c8bb2e302b7011fa4df0922.png

比如查找

select id from Test where age =1;  

扫描第一次就能找到，如果查找

select id from Test where age =7;  

可能扫描到第八次才能找到，如果数据非常多，多达几千万行，查找数据最极端的情况下可能需要查找到最后一次才能找到业务所需数据，所以是非常耗时的，我们就需要对数据查找过程进行优化，其实mysql索引就是一种数据结构，来优化查询速度的。方便我们查找数据(包括范围查找和指定查找)

索引的几种数据结构

在这里先给大家介绍一个网站https://www.cs.usfca.edu/~galles/visualization/Algorithms.html这个网站可以模拟各种数据结构的插入和查找的动画过程，这样更容易理解

二叉树

二叉树其实就是遵循了一个规律来排列，左小右大

02af6214a4d7e2975d9387188e358598.png

这样的话比比如我们查询9  通过两次比对就可以查询到

但是二叉树这种数据结构存在一些问题，比如数据是递增或者递减的顺序插入，二叉树就很容易形成单路链表

5ca7df80839a3e8cc3f5f38c7ed31072.png

如果遇到这种情况那么查找数据就普通的没有加索引的情况没什么区别了

红黑树

红黑树是一种自平衡二叉查找树，平衡二叉树的目的是为了减少二叉查找树层次，提高查找速度
比如依次插入 1到9，二叉树可能就和上图所展示的单路链表一样，红黑树可以自动旋转，减少层数

d049ab39f687bfcbeb8f7c1a31aa58cd.png

具体过程可以在下面链接中插入看演示https://www.cs.usfca.edu/~galles/visualization/AVLtree.html

散列表(hash)

散列表也为哈希表，又直接寻址改进而来。在哈希的方式下，一个元素k处于h(k)中，即利用哈希函数h，根据关键字k计算出槽的位置。函数h将关键字域映射到哈希表T[0...m-1]的槽位上

54f53ee296827fef7978461001315972.png

散列表的缺点
1)Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
2)Hash 索引无法被用来避免数据的排序操作。
3)Hash 索引不能利用部分索引键查询。
4)Hash 索引在任何时候都不能避免表扫描。
5)Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

B树

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

  • d为大于1的一个正整数，称为B-Tree的度。
  • h为一个正整数，称为B-Tree的高度。
  • 每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。
  • 每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。
  • 所有叶节点具有相同的深度，等于树高h。
  • key和指针互相间隔，节点两端是指针。
  • 一个节点中的key从左到右非递减排列。
  • 所有节点组成树结构。
  • 每个指针要么为null，要么指向另外一个节点。
  • 如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)v(key1)，其中v(key1)v(key1)为node的第一个key的值。
  • 如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)v(keym)，其中v(keym)v(keym)为node的最后一个key的值。
    如果某个指针在节点node的左右相邻key分别是keyikeyi和keyi+1keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)v(keyi+1)且大于v(keyi)v(keyi)。
1409f18d0b022370ebe557440a8b5f81.png

另外，由于插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质，而且B-Tree也是不支持范围查找的，所以一般不采用

B+树

B+Tree其实和BTree类似，但是也有很多区别，我们先看图

551a98a0469c7e84a1c324b94734788a.png

B+Tree的特征

1: 每一个父节点的元素都出现在了子节点中，是节点的最大或者最小元素

比如上面事例图中的元素8和15

34d89f7c6b3c6bb16b7891d3fe0a31d5.png

需要注意的是，根结点的最大元素(这里是15)，也是整个B+Tree的最大元素

2: 由于父节点的元素都出现在了叶子节点，因此所有的叶子节点包含了全部元素信息，并且每一个叶子节点都带有指向下一个叶子节点的指针，形成了一个有序链表

a06987acc6357d119a4ac35e0ba0b3a7.png

3: 在BTree中，每一个节点都包含了数据，但是在B+Tree中，只有叶子节点包含了数据，中间节点仅仅是索引，没有任何数据关联

6e4abd8c4bb852b086ba86f9ed363982.png

注意：聚簇索引(Innodb)的叶子节点包含了整行数据和索引

注意:对于聚簇索引来说，仅仅是主键的索引最终节点包含了数据本身，而非主键的叶子节点保存的是主键的值，然后再根据主键索引的值走B+树来查找数据本身，这么做的目的是为了一致性和节省存储空间。

innodb的索引如图所示

d9e030e0857db2f73e51b7895a66d6ba.png

而非聚簇索引(myisam)的叶子节点只包含了索引，而不包含数据行本身
其实从数据库保存文件也能看出来，对于非聚簇索引(myisam)的表来说，数据库保存了三个文件
user_m.frm   表结构定义数据
user_m.MYD   表数据
user_m.MYI   索引文件
myisam索引如图所示

88587836945b2fff31c1118d493a825f.png

而非聚簇索引(Innodb)，数据库只保存了一个文件user_i.frm  所以说他的索引和数据是在一起的。
有一个非常形象的例子大家可以理解一下
非聚簇索引(myisam)相当于是一本书前面的目录，咱们可以根据目录去找到对应的内容
而聚簇索引(Innodb)相当于是书每一页下面的页码，页码和书本的内容是在一起的B+Tree的优势

  1. 在单元素查询的时候，B+Tree会从根节点，逐层向下查找，最终找到要匹配的叶子节点，只需要三次磁盘IO就能找到，这个流程看起来和BTree差不多，其实并不是这样
  • 首先B+树的非叶子节点不包含数据，所以同样的磁盘页可以容纳更多的节点元素，所以相同的数据情况下，B+Tree会显得更加“矮胖”，所以查询磁盘IO的次数会少
  • 另外B+Tree的查询必须最后查找到叶子节点，而B-Tree只要找到了元素即可，不一定必须是得找到叶子节点，所以B-Tree的查询性能很不稳定，B+Tree的查询性能相对稳定很多。
  1. 对于范围查询来说B-Tree非常麻烦，如下图
060c7440ca3d18e2936feb27421beb80.png

比如我想查找大于3的数据
中序遍历到元素6：
中序遍历到元素8：
中序遍历到元素9：
中序遍历到元素11，遍历结束：

5e07e573bde1097f2b2e719e5c1bccfa.png

这种回环运算非常麻烦

像B+Tree就简单了很多

直接叶子节点链表指过去就行

744f638b67b16738ec23d0c445392fca.png

最后总结一下B+Tree的特征和优势
B+树的特征：

  • 有k个子树的中间节点包含有k个元素(B树中是k-1个元素)，每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

  • 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

  • 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大(或最小)元素。

B+树的优势：

  • 单一节点存储更多的元素，使得查询的IO次数更少。

  • 所有查询都要查找到叶子节点，查询性能稳定。

  • 所有叶子节点形成有序链表，便于范围查询。

索引下推

符合索引(age,name)
在5.6之前
先根据name把所有数据查询出来，然后在server层进行age所有字段筛选
在5.6 之后
从存储引擎拉取数据的时候，会根据age 和name两个字段做筛选，将符合条件的数据拉回

计算数据库能存储多少数据的方法(大概计算)

首先每个索引页可以存储的大小是16kb，这个值可以查到

show global status like 'innodb_page_size'

计算方法如图

535ff63c82d559465ea295c2a05a855e.png

辅助索引

上面我们说的其实都是基于主键索引，如果非主键索引(辅助索引)查询数据是什么样的呢？
① 在辅助索引上检索name(这个name是例子，意思是代表非主键索引)，到达其叶子节点获取对应的主键；
② 使用主键在主索引上再进行对应的检索操作
这个过程也被称为回表

7597d09e62099ef902ad9ab8f7515ee9.png

之所以这么做的原因是 保证数据一致性和节省存储空间，可以这么理解：商城系统订单表会存储一个用户ID作为关联外键，而不推荐存储完整的用户信息，因为当我们用户表中的信息(真实名称、手机号、地址)修改后，不需要再次维护订单表的用户数据，同时也节省了存储空间

select * from table where name = ‘123’；  
select id from table where name = ‘123’；

两个表查询的过程是不一样的  select * 需要回表
覆盖索引：在查询普通索引的时候，如果叶子节点保存的刚好是要查询的字段，此时叫覆盖索引

8388ac36d86b010241513cbd464ec2f1.png

weixin_39827036
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL索引之主键索引
12-15
在MySQL里，主键索引和辅助索引分别是什么意思，有什么区别？ 上次的分享我们介绍了聚集索引和非聚集索引的区别，本次我们继续介绍主键索引和辅助索引的区别。 1、主键索引 主键索引，简称主键，原文是PRIMARY KEY，由一个或多个列组成，用于唯一性标识数据表中的某一条记录。一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL。 在MySQL中，InnoDB数据表的主键设计我们通常遵循几个原则： 1、采用一个没有业务用途的自增属性列作为主键； 2、主键字段值总是不更新，只有新增或者删除两种操作； 3、不选择会动态更新的类型，比如当前时间戳等。 这么做的好处有几点： 1、新增数据
MySQL查看所有列名，以及出去某一个列外所有列名
胡榕昆的博客
04-27 1637
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't_report_apply' AND TABLE_SCHEMA = 'reporttest' TABLE_NAME：表名 TABLE_SCHEMA：数据库名 查看除去F_ID以外的所有列名。 SELECT REPLACE ( GROUP_CONCAT( COLUMN_NAME ), 'F_ID', '' ) AS col_list FR.
根据表名得到除主键外的所有字段及字段类型
ls_zai的专栏
12-17 558
Create FUNCTION [dbo].[fn_FieldsTypeExceptPK] (     @TableName Varchar(50) ) RETURNS Varchar(MAX) AS BEGIN     DECLARE @ReturnValue As Varchar(MAX)     Set @ReturnValue=''     Declare @PKNa
mysql查询除某列之外数据
aini6223017的博客
02-03 3704
//取所有列名$columnnamesql="select COLUMN_NAME from information_schema.COLUMNS where table_name = 表名"; ... $strcolumn='';foreach($columnname as &$v){ $strcolumn.=$v['COLUMN_NAME'].',';}un...
mysql非主键索引的区别_主键索引和非主键索引的区别
weixin_42501956的博客
01-19 679
1. 什么是最左前缀原则？以下回答全部是基于MySQL的InnoDB引擎例如对于下面这一张表如果我们按照 name 字段来建立索引的话，采用B+树的结构，大概的索引结构如下如果我们要进行模糊查找，查找name 以“张"开头的所有人的ID，即 sql 语句为selectIDfromtablewherenamelike'张%'由于在B+树结构的索引中，索引项是按照索引定义里面出现的字段顺...
MySQL索引背后的数据结构及算法原理详解
12-16
为了避免混乱，本文将只关注于BTree索引，因为这是平常使用MySQL时主要打交道的索引，至于哈希索引和全文索引本文暂不讨论。 文章主要内容分为三个部分。 第一部分主要从数据结构及算法理论层面讨论MySQL数据库索引...
MySQL 主键与索引的联系与区别分析
01-19
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。下面是主键和索引的一些区别与联系。 1. 主键一定是唯一性索引，唯一性索引并不一定就是主键。 所谓...
（mysql面试题）MySQL中的索引、主键和外键的概念及其作用及代码展示.txt
最新发布
08-18
创建索引会占用额外的存储空间，并且在插入、更新和删除数据时，需要维护索引的结构，这会带来一定的性能开销。 2. 主键（Primary Key）： 主键是数据库表中用于唯一标识每一行数据的列或一组列。主键的值不能重复...
MySql数据库索引详解,索引看这一篇就够了
12-14
目录 1 为什么要使用索引？ 2 索引的分类 3 索引的创建和删除 ...7.3 所有的查询语句都能用到索引吗？   推荐书籍《mysql索引漫画》,书籍链接如下： 链接：https://pan.baidu.com/s/1Vh4N_S0pPd9_sYN04cIUsw 提取
mysql实现范围查找原理_mysql 非主键做范围查找实现原理的一点困惑
weixin_36096137的博客
01-27 1155
以 innodb 为例。CREATE TABLE `test` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,`age` int(11) unsigned NOT NULL,`name` varchar(30) NOT NULL DEFAULT '',PRIMARY KEY (`id`),KEY `idx_name` (`name`)) ENGINE...
查询数据库某个表的字段、属性、注释说明、是否主键、是否非空sql（mysql）
毛卡人的博客
10-11 1万+
SELECT     UPPER(column_name) AS '字段名称',     column_comment AS '字段描述',     UPPER(column_type) AS '字段类型',     IF(column_key='PRI','TRUE','FALSE') AS '主键唯一',     IF(IS_NULLABLE='NO','TRUE','FALSE') AS '...
MySQL-删除除id外其他内容都相同的数据
走在命运的左岸
04-23 2914
删除除id外其他内容都相同的数据 难点：自己不能删除自己，要进行多次转换 1.查询表中数据 [code="sql"] select * from test ; [/code] [table] | id | name | value | | 1 | p | p | | 2 | p | p | | 3 | pp | pp | |...
如何查询一个表中除某几个字段外其他所有的字段
热门推荐
qq_36350477的博客
03-07 1万+
话说楼主刚开始也不知道怎么查，然后就开始百度了 大部分网友都是建议一个一个写 当然程序员是最懒的啦，一个一个写多痛苦啊 接着找 还别说csdn里还有不少大牛的 于是出现了这个脚本 declare@snvarchar(1000) select@s=isnull(@s+',','')+quotename(Name)fromsyscolumnswhereID=object_...
mysql 查找除id外其他重复的字段数据
weixin_30414155的博客
06-01 540
如表 test1 有多个重复的字段 其中有些数据完全重复是错误的数据,我们要把他找出来,然后删除掉 select * from test1 a where (a.phone,a.name) in ( select phone,name from test1 group by phone,name having count(*)>1 ) and id not in ( ...
mysql复制表数据不复制自增id_mysql表数据装满了怎么办？自增id引发的bug。
weixin_42307478的博客
01-20 800
国庆节的一个大清早，我就被微信群吵醒了，说是spark实时程序有问题，数据没出来，让我看看。我看了下邮箱，确实有报警信息。我特么，没办法，起床。1 主键自增id设置不合理引发的bug首先，我检查了一下应用管理页面，发现资源没有用完，GC也正常，程序正在嗖嗖的跑呢，那怎么会没有今天的数据呢？看了下日志，error.log里面并没有今天的日志，warn.log里面有sql语句，都很正常，这到底是怎么回...
mysql中有多条除ID外相同数据只取其中一条（用户登陆）
iteye_16005的博客
11-09 1164
        建用户表并测试过程中，发现表中可能有多条用户名和密码相同的数据，登陆时需要返回一个user对象，但是简单的select语句返回一个列表，可用以下sql语句代替 SELECT `username`,`password` FROM t_users WHERE `id` = (SELECT MIN(`id`) FROM `t_users` WHERE `username`='adm...
mysql聚集索引_MySQL中怎样创建聚集索引和非聚集索引，求创建这两种索引的SQL语句。谢谢...
06-02
在MySQL中，聚集索引和非聚集索引可以通过以下SQL语句进行创建： 创建聚集索引： ``` CREATE TABLE table_name ( column1 datatype, column2 datatype, ... PRIMARY KEY (column1) ); ``` 上述语句中，将 `...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值