【MySQL】索引 【下】{聚簇索引VS非聚簇索引/创建主键索引/全文索引的创建/索引创建原则}

文章目录

  • 1.聚簇索引 VS 非聚簇索引
    • 经典问题
  • 2.索引操作
    • 创建主键索引
    • 唯一索引的创建
    • 普通索引的创建
    • 全文索引的创建
    • 查询索引
    • 删除索引
    • 索引创建原则

1.聚簇索引 VS 非聚簇索引

之前介绍的将所有的数据都放在叶子节点的这种存储引擎对应的就是 InnoDB 默认存储表数据的存储结构。

下面我们简单介绍一下 MyISAM 存储引擎-主键索引。MyISAM 引擎同样使用B+树作为索引结果,叶节点的data域存放的是数据记录的地址。下图为MyISAM表的主索引,Col1 为主键。
在这里插入图片描述
其中,MyISAM 最大的特点是,将索引Page和数据Page分离,也就是叶子节点没有数据,只有对应数据的地址。相较于InnoDB 索引,InnoDB 是将索引和数据放在一起的。

--终端A
mysql> create database myisam_test;  --创建数据库
Query OK, 1 row affected (0.00 sec)
mysql> use myisam_test;Database changed
mysql> create table mtest(
-> id int primary key,
-> name varchar(11) not null
-> )engine=MyISAM;              --使用engine=MyISAM
Query OK, 0 rows affected (0.01 sec)--终端B
[root@-centos mysql]# ls myisam_test/ -al  --mysql数据目录下
total 28
drwxr-x---  2 mysql mysql 4096 Jun 13 13:33 .
drwxr-x--x 13 mysql mysql 4096 Jun 13 13:32 ..
-rw-r-----  1 mysql mysql   61 Jun 13 13:32 db.opt
-rw-r-----  1 mysql mysql 8586 Jun 13 13:33 mtest.frm   --表结构数据
-rw-r-----  1 mysql mysql    0 Jun 13 13:33 mtest.MYD   --该表对应的数据,当前没有数据,所以是0
-rw-r-----  1 mysql mysql 1024 Jun 13 13:33 mtest.MYI   --该表对应的主键索引数据

MyISAM 这种用户数据与索引数据分离的索引方案,叫做非聚簇索引

--终端A
mysql> create database innodb_test;       
Query OK, 1 row affected (0.00 sec)
mysql> use innodb_test;Database changed
mysql> create table itest(
-> id int primary key,
-> name varchar(11) not null
-> )engine=InnoDB;    --使用engine=InnoDB                
Query OK, 0 rows affected (0.02 sec)--创建数据库--终端B
[root@centos mysql]# ls innodb_test/ -altotal 120
drwxr-x---  2 mysql mysql  4096 Jun 13 13:39 .
drwxr-x--x 14 mysql mysql  4096 Jun 13 13:38 ..
-rw-r-----  1 mysql mysql    61 Jun 13 13:38 db.opt
-rw-r-----  1 mysql mysql  8586 Jun 13 13:39 itest.frm     --表结构数据
-rw-r-----  1 mysql mysql 98304 Jun 13 13:39 itest.ibd     --该表对应的主键索引和用户数据,虽然现在一行数据没有,但是该表并不为0,因为有主键索引数据

InnoDB 这种用户数据与索引数据在一起索引方案,叫做聚索引。当然,MySQL除了默认会建立主键索引外,我们用户也有可能建立按照其他列信息建立的索引,一般这种索引可以叫做辅助(普通)索引。
对于 MyISAM,建立辅助(普通)索引和主键索引没有差别,无非就是主键不能重复,而非主键可重复。下图就是基于 MyISAM 的 col2建立的索引,和主键索引没有差别。
在这里插入图片描述
InnoDB 除了主键索引,用户也会建立辅助(普通)索引,我们以上表中的Col3 建立对应的辅助索引如下图:
在这里插入图片描述

InnoDB 的非主键索引中叶子节点并没有数据,而只有对应记录的 key 值。

通过辅助(普通)索引,找到目标记录,需要两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。这种过程,就叫做回表查询!这种多个索引的就是一个表可以对应多个 B+树!
为何InnoDB 针对这种辅助(普通)索引的场景,不给叶子节点也附上数据呢?原因就是太浪费空间了。【没必要再搞一份重复的数据!】索引的本质:B+树

经典问题

mysql默认创建一列带有主键的列,那为什么插入无主键表时是无序的显示表仍然无序,不是有默认主键吗?

mysql默认的主键肯定不会按着你的想法来,他有自己的设置,你插入顺序怎样,显示就怎样。

那为什么查询还那么慢?

假设有abc三个字段,你没有设置主键,假设mysql默认使得d为隐藏主键,你用a去查就无法使用d的索引结构,只能线性遍历,所以很慢。【经过了解,一棵树可能有多个索引,一个索引本质就是一个数据结构实例,主键索引/普通索引】为了讲解清楚,通常把整颗b+树呈现给你,但实际上,查询数据时是按需加载!即用哪个加载哪个结点

叶子节点全部用链表级联起来 a.首先,这是b+的特点 b.我们比较希望进行范围查找

是mysql用了B+结构,B+结构是非叶子节点不相连,叶子节点相连;
一段数据/一张page中数据连续。

理解curd

对mysql内部的数据等做操作(CURD)的时候,本质其实就是操作文件内容。

文件必须先被打开,对文件内容做任何操作,都不是直接在磁盘设备上做操作的!任何磁盘数据,在进程中要进行操作,本质都必须在内存中进行!如果数据不在内存中呢?换入,换出【磁盘到内存,内存到磁盘】mysql内部一定要有自己的内存管理。mysql自己启动的时候,要预先申请一批内存空间。

索引覆盖

在这里插入图片描述

复合索引,通常用于{a,b}:用a找b,即不再通过ab找到主键key(普通索引存的不是数据而是主键),继而通过key找b:覆盖了主键索引。不能拿b找a,因为匹配时都是1从左向右:索引最左匹配原则

2.索引操作

创建主键索引

第一种方式-- 在创建表的时候,直接在字段名后指定 primary key

 create table  user1(id int primary key, name varchar(30));

第二种方式:-- 在创建表的最后,指定某列或某几列为主键索引

create table  user2(id int, name varchar(30), primary key(id)); 

第三种方式:-- 创建表以后再添加主键

create table  user3(id int, name varchar(30));
alter table user3 add primary key(id);

主键索引的特点:

一个表中,最多有一个主键索引,当然可以有复合主键(复合主键也是一个主键,只不过主键由两个字段构成)。
主键索引的效率高(主键不可重复)。
创建主键索引的列,它的值不能为null,且不能重复。
主键索引的列基本上是int。

唯一索引的创建

第一种方式-- 在表定义时,在某列后直接指定unique唯一属性。

create table user4(id int primary key, name varchar(30) unique);

第二种方式-- 创建表时,在表的后面指定某列或某几列为unique

 create table user5(id int primary key, name varchar(30), unique(name));

第三种方式

create table user6(id int primary key, name varchar(30));
alter table user6 add unique(name);

唯一索引的特点:

一个表中,可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引,必须保证这列不能有重复数据
如果一个唯一索引上指定not null,等价于主键索引

普通索引的创建

第一种方式

create table user8(id int primary key,name varchar(20),email varchar(30),index(name) --在表的定义最后,指定某列为索引
);

第二种方式

create table user9(
id int primary key,  
name varchar(20),  
email varchar(30)
);
alter table user9 add index(name); --创建完表以后指定某列为普通索引

第三种方式

create table user10(id int primary key,  name varchar(20),  email varchar(30)
);  
create index idx_name on user10(name);-- 创建一个索引名为 idx_name 的索引  

普通索引的特点:

一个表中可以有多个普通索引,普通索引在实际开发中用的比较多
如果某列需要创建索引,但是该列有重复的值,那么我们就应该使用普通索引

全文索引的创建

当对文章字段或有大量文字的字段进行检索时,会使用到全文索引。MySQL提供全文索引机制,但是有要求,要求表的存储引擎必须是MyISAM,而且默认的全文索引支持英文,不支持中文。如果对中文进行全文检索,可以使用sphinx的中文版(coreseek)。

CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT (title,body))engine=MyISAM;INSERT INTO articles (title,body) VALUES('MySQL Tutorial','DBMS stands for DataBase ...'),('How To Use MySQL Well','After you went through a ...'),('Optimizing MySQL','In this tutorial we will show ...'),('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),('MySQL vs. YourSQL','In the following database comparison ...'),('MySQL Security','When configured properly, MySQL ...');

查询有没有database数据

如果使用如下查询方式,虽然查询出数据,但是没有使用到全文索引

mysql> select * from articles where body like '%database%';+----+-------------------+------------------------------------------+| id | title             | body                                     |+----+-------------------+------------------------------------------+|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             ||  5 | MySQL vs. YourSQL | In the following database comparison ... |+----+-------------------+------------------------------------------+

可以用explain工具看一下,是否使用到索引

mysql> explain select * from articles where body like '%database%'\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: articlestype: ALLpossible_keys: NULLkey: NULL  <== keynull表示没有用到索引
key_len: NULLref: NULLrows: 6Extra: Using where1 row in set (0.00 sec)

如何使用全文索引呢?

mysql> SELECT * FROM articles-> WHERE MATCH (title,body) AGAINST ('database');+----+-------------------+------------------------------------------+| id | title             | body                                     |+----+-------------------+------------------------------------------+|  5 | MySQL vs. YourSQL | In the following database comparison ... ||  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |+----+-------------------+------------------------------------------+mysql> explain -> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database')\G*************************** 1. row ***************************id: 1select_type: SIMPLEtable: articlestype: fulltextpossible_keys: titlekey: title <= key用到了titlekey_len: 0ref: 
rows: 1Extra: Using where

查询索引

show keys from 表名
show index from 表名;
desc 表名;

删除索引

第一种方法-删除主键索引:

alter table 表名 drop primary key;

第二种方法-其他索引的删除:

alter table 表名 drop index 索引名; --索引名就是show keys from 表名中的 Key _name 字段mysql> alter table user10 drop index idx name;

第三种方法方法:

drop index 索引名 on 表名mysql> drop index name on user8;

索引创建原则

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在where子句中的字段不该创建索引

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://xiahunao.cn/news/3281056.html

如若内容造成侵权/违法违规/事实不符,请联系瞎胡闹网进行投诉反馈,一经查实,立即删除!

相关文章

MySQL从jsonarray获取某个字段的所有数据

表结构。表里的order_goods_info_vo_list是jsonarray字段 CREATE TABLE pdd_charge_back_bantuo (id int(11) NOT NULL AUTO_INCREMENT,shopname varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 店铺名,charge_back_sn varchar(64) COLLATE utf8mb4_bin DEFAULT NULL …

2000-2023年上市公司财务困境数据Oscore模型(含原始数据+计算结果)

2000-2023年上市公司财务困境数据Oscore模型&#xff08;含原始数据计算结果&#xff09; 1、2000-2023年 2、指标&#xff1a;证券代码、证券简称、统计截止日期、是否发生ST或*ST或PT、是否发生暂停上市、行业代码、行业名称、上市日期、总资产&#xff08;元&#xff09;、…

Docker Desktop安装(通俗易懂)

1、官网 https://www.docker.com/products/docker-desktop/ 2、阿里云镜像 docker-toolbox-windows-docker-for-windows安装包下载_开源镜像站-阿里云 1. 双击安装文件勾选选项 意思就是&#xff1a; Use WSL 2 instead of Hyper-V (recommended) : 启用虚拟化&#xff0c;…

IDEA对线上项目远程debug

1、在启动脚本上添加以下配置内容 -agentlib:jdwptransportdt_socket,servery,suspendn,address*:5005 nohup java -agentlib:jdwptransportdt_socket,servery,suspendn,address5005 -jar test.jar > misc.out & 2、在IDEA中进行配置 &#xff08;1&#xff09;选择远程…

红外激光模组如何升级为现代科技的璀璨明珠

在日新月异的科技领域中&#xff0c;红外激光模组以其独特的应用价值和卓越的性能&#xff0c;成为了众多行业的宠儿。从通信、测距、监控到医疗&#xff0c;红外激光模组以其广泛的应用场景&#xff0c;不断推动着现代科技的进步与发展。接下来我们就跟着鑫优威一起来了解一下…

详解Qt 之QByteArray

文章目录 详解Qt之QByteArray前言QByteArray概念作用为什么需要 QByteArrayQByteArray 的主要函数和成员函数列表 示例代码示例 1&#xff1a;字节数组的基本操作示例 2&#xff1a;数据编码和解码示例 3&#xff1a;字节数组的字符串操作 更多用法... 总结 详解Qt之QByteArray…

vue3+fetch请求+接收到流式的markdown数据+一边gpt打字机式输出内容,一边解析markdown语法+highlight.js实现代码高亮

这个问题终于解决了&#xff01;好开心。 先看最终效果&#xff1a; video_20240724_141543_edit 项目背景&#xff1a;vue3 场景&#xff1a;像gpt一样可以对话&#xff0c;当用户发送问题之后&#xff0c;ai回复&#xff0c;ai是一部分一部分回复&#xff0c;像打印机式输出…

微服务-服务拆分-服务远程调用

查询订单demo 通过Bean的方式将RestTemplate注册为Spring的一个对象&#xff0c;即注入Spring容器&#xff08;要写在配置类中&#xff0c;启动类本身就是配置类&#xff09;。然后在任何地方都可以注入该对象使用。 Eureka注册中心 Eureka服务搭建 Eureka客户端注册 配置服务…

Java与模式及其应用场景知识点分享(电子版)

前言 Java 编程语言自1995年问世以来&#xff0c;其成功好像任何编程语言都无法媲美。生逢其时(互联网的兴起)固然是一方面的原因&#xff0c;而Java吸收总结了前人的经验教训&#xff0c;反映了最新技术(the state ofthe art)&#xff0c;对其受到欢迎和采用&#xff0c;恐怕…

如何在基于滤波框架的绝对定位系统中融合相对观测

文章目录 1 LIO、VIO propagation来代替IMU propagation2 TRO paper: Stochastic Cloning Kalman filter【有待填坑】 以无人驾驶定位系统为例&#xff0c;融合gnss&#xff0c;imu&#xff0c;轮速&#xff0c;camera LaneMatch(frame to map)&#xff0c;lidar scan match(fr…

大数据-55 Kafka sh脚本使用 与 JavaAPI使用 topics.sh producer.sh consumer.sh kafka-clients

点一下关注吧&#xff01;&#xff01;&#xff01;非常感谢&#xff01;&#xff01;持续更新&#xff01;&#xff01;&#xff01; 目前已经更新到了&#xff1a; Hadoop&#xff08;已更完&#xff09;HDFS&#xff08;已更完&#xff09;MapReduce&#xff08;已更完&am…

如何有效管理众多账号密码:选择适合你的密码管理工具

在如今的数字化时代&#xff0c;我们的生活几乎离不开各种互联网应用和服务。从社交媒体到在线银行&#xff0c;从购物网站到工作平台&#xff0c;每个应用都要求我们注册账号并设置密码。 随着账号数量的不断增加&#xff0c;管理这些密码成为了一个令人头疼的问题。幸运的是…

【运维指南】常见的防火墙端口操作

每当一个应用程序想通过网络访问自己时&#xff0c;它就会申请一个 TCP/IP 端口&#xff0c;这意味着该端口不能被其他任何程序使用。那么&#xff0c;如何检查开放的端口&#xff0c;看看哪个应用程序已经在使用它呢&#xff1f; Windows 查看端口使用情况和进程名称 netst…

免费电子书网站

1.鸠摩搜书:https://www.jiumodiary.com/ 首页 白天模式 夜间模式(个人更喜欢白天的) 评分:☆☆☆☆☆ 1.网站简洁,刚开始只有一个搜索框。 2.内容多,而且有azw3、PDF、mobi、TXT、doc等6种格式提供下载 3.Last but not the least !!! 完全免费,不限制下载次数。…

QT+OpenGL绘制一个更加清晰的三维坐标系和图例

绘制图例 图例绘制有两种&#xff1a; 1. 设置多个颜色绘制 2.随机100个值&#xff08;自己可设置&#xff09;绘制 class CPointLegend : public CLegend { public:static CPointLegend& getInstance() {/*c11支持&#xff0c;线程安全的单例模式*/static CPointLegend …

Linux虚拟化技术KVM

文章目录 虚拟化基础什么是虚拟化虚拟化优势虚拟机虚拟机的主要特征Hypervisor类型类型1&#xff1a;裸金属型类型2&#xff1a;宿主型 KVM概述KVM体系结构KVM模块载入后的系统运行模式KVM集中管理和控制宿主机环境准备 安装KVM工具包libvirt包功能libvirt结构图安装KVM相关包C…

Linux AMBA 驱动:DMA 控制器 PL330 驱动简析

文章目录 1. 前言2. 背景3. PL330 简介4. PL330 驱动加载流程4.1 PL330 设备注册流程4.2 PL330 驱动加载流程 5. 小结6. 参考资料 1. 前言 限于作者能力水平&#xff0c;本文可能存在谬误&#xff0c;因此而给读者带来的损失&#xff0c;作者不做任何承诺。 2. 背景 本文基于…

鸿蒙对接极光推送时候报错1000900010,厂商token获取失败

在AppGallery Connect上配置项目的调试证书&#xff0c;然后手动导入&#xff0c;不要用IDE的自动构建证书&#xff1a; https://developer.huawei.com/consumer/cn/service/josp/agc/index.html#/

探索Python的加速神器:CyToolz,让数据处理快如闪电!

文章目录 探索Python的加速神器&#xff1a;CyToolz&#xff0c;让数据处理快如闪电&#xff01;背景&#xff1a;为何选择CyToolz&#xff1f;CyToolz是什么&#xff1f;如何安装CyToolz&#xff1f;五个简单函数的使用方法1. cytoolz.curry2. cytoolz.map3. cytoolz.reduce4.…

(十)联合概率数据互联原理及应用(JPDA)

目录 前言 一、JPDA原理及算法步骤 &#xff08;一&#xff09;算法步骤 1.确认矩阵计算 2.确认矩阵拆分 3.互联概率计算 4.状态及协方差更新 二、仿真验证 &#xff08;一&#xff09;模型构建 &#xff08;二&#xff09;仿真结果 总结 引用文献 前言 本文主要针…