MySQL数据库中的索引(含SQL语句)

这篇具有很好参考价值的文章主要介绍了MySQL数据库中的索引(含SQL语句)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

为什么要用索引

假设有一张表,表中有100万条数据,这100万条数据在硬盘上是存储在数据页上的,一页数据大小为16k。存储100万条数据那么就需要数据页,假设其中有一条数据是“id为7900”的,那么如果要查询这条数据,其中SQL是SELECT * FROM 表名 WHERE id = 7900。在执行这条SQL语句的时候,MySQL需要扫描全表来查询id = 7900的记录。
全表扫描就是从“数据页1”开始,向后逐页查询。对于少量数据来说,查询的速度会很快,但是,当随着数据量的增加,性能会急剧下降。100万条数据进行逐页查询的时间是无法被用户所接受的。

索引是什么

索引是帮助MySQL高效获取数据的数据结构
是排好序的快速查找的数据结构。

索引就是类似于书的目录。

数据库在存储数据本身以外,还有维护着一个满足特定查找算法的数据结构,这些数据结构就以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引

索引的原理

索引的目的在于提高查询效率,与我们查询图书所用的目录是一个道理:先定位到章,然后定位到该章下的一个小节,然后找到页数。相似的例子还有:查字典、查火车车次,飞机航班等。

本质上都是:通过不断地缩小想要获取数据的范围来筛选出最最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们就可以总是用同一种查找方式来锁定数据。

索引类似于书的目录,在一本书前面加上目录,查找内容时就不必逐页翻阅就能够快速地找到所要查找的内容。借助索引,执行查询时就不必扫描整个表就能够快速地找到所需要的数据。

优点

索引提高了数据检索的效率,降低了数据库IO成本。

通过索引列对数据进行排序,降低了数据排序的成本,降低了CPU的消耗。

缺点

实际上,索引本质上也是一张表,这个表保存了主键与索引之间的字段,并指向实体表的记录,所以索引列也是要占用磁盘的空间。

虽然索引大大提高了插叙的速度,但同时也会降低更新表数据的速度。例如:INSERT、UPDATE和DELETE,因为更新表时,MySQL不仅要保存数据,还要保存一次索引文件,每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。

创建索引的原则

索引虽好,但也不能乱用。

什么情况下需要索引

  1. 主键自动建立唯一索引。
  2. 频繁作为查询条件的字段应该创建索引(WHERE 后面的语句)。
  3. 查询中与其它表关联的字段,外键关系建立索引。
  4. 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度分组中的字段。

什么情况下不需要索引

  1. 表记录过少
  2. 经常性的增删改的表。虽然有提高了查询的速度,但同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE,因为更新表时,MySQL不仅要保存数据,还要保存一下索引的文件。(所以在遇到这种情况下,就会分表,分表就是将读、写的数据进行分离)
  3. WHERE条件里用不到的字段不会创建索引
  4. 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列表建立索引,某个数据列包含许多重复的内容,建立索引没有太大的实际效果。

索引的分类

主键索引

就是设定主键后数据库会自动建立索引,一个表只能有一个主键。

ALTER TABLE 表名 ADD PRIMARY KEY 表名(列名);

删除主键索引:

ALTER TABLE 表名 DROP PRIMARY KEY;

单值索引

就是一个索引只包含单个列,一个表可以有多个单列索引。
加这个索引肯定是比不加索引要快的。

创建单值索引:

CREATE INDEX 索引名 ON 表名(列名);

删除索引:

DROP INDEX 索引名;

唯一索引

索引列的值必须唯一,允许为null;

CREATE UNIQUE INDEX 索引名 ON 表名 (列名);

删除索引:

DROP INDEX 索引名 ON 表名;

组合索引(复合索引)

即一个索引包含多个列,在数据库操作期间,复合索引比单值索引所需要的开销是更小的(对于相同的多个列键索引)

当表的行数远大于索引列的数目时可以使用复合索引。

创建复合索引

CREATE INDEX 索引名 ON 表名(列1,列2,…);

删除索引

DROP INDEX 索引名 ON 表名;

组合索引最左前缀原则
例如表中有a,b,c 3列,为a,b两列创建组合索引,那么在使用时需要满足最左侧索引原则。即在使用组合索引的列为条件的时候,是必须要出现最左侧列作为条件,否则索引是不生效的。

例如:

SELECT * FROM 表名 WHERE a = ' ' AND b = ' ' ; #索引生效
SELECT * FROM 表名 WHERE b = ' ' AND b = ' ' ; #索引生效
SELECT * FROM 表名 WHERE a = ' ' AND b = ' ' ; # 索引生效
SELECT * FORM 表名 WHERE b = ' ' AND c = ' ' ; # 索引不生效

全文索引(仅在MySQL8之后有)

是需要模糊查询的,一般索引是无效的,这个时候就可以使用全文索引。

比如: WHERE name LIKE %J%,这样查询的时候,即使name列添加了索引,但也会索引失效的。所以是不建议使用的,在MySQL8当中就建议使用全文索引。

创建全文索引列:

CREATE FULLTEXT INDEX 索引名 ON 表名(列名) WITH TARSER ngram;

使用全文索引:

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST(‘输入的参数’);

全文索引的插叙效率是比使用LIKE来查询要快的,所以在MySQL8之后是建议使用全文索引的。

查找索引:

SHOW INDEX FROM 表名;

索引的数据结构

在MySQL数据库中的InnoDB引擎是使用B+树来进行数据存储的。

B+树由二叉查找树、平衡二叉树(AVLTree)和平衡多落查找树(B-Tree)逐步优化而来的。使其更适合实现外存索引结构。

B+数的特点:

  1. 它是排好序的,一个节点可以存储多个数据。
  2. 非叶子节点是不存储数据的,只存储索引,是可以放更多的索引。
  3. 数据都是存放在在叶子节点中的。
  4. 所有的叶子节点之间都是有一个链指针的。

就是使用B+树使得树是横向扩展的,让树的高度降低了。在叶子节点也可以存储多个数据。
MySQL数据库中的索引(含SQL语句)

MySQL所以使用B+树,因为索引是用来加快查询的,而B+数通过对数据进行排序所以是可以提高查询速度,然后通过一个节点可以存储多个元素,从而可以使得B+树的高度不会太高。并且叶子节点之间存在指针,可以很好的支持全表扫描,范围查找等SQL语句。

聚簇索引和非聚簇索引

聚簇索引
就是找到了索引就找到了需要的数据,那么这个索引就是聚簇索引,所以主键就是聚簇索引。

非聚簇索引
索引的存储和数据的存储时分离的,也就是意味着找到了索引但没有找到数据,需要根据索引上的值(主键)再次回表查询,非聚簇索引也叫做辅助索引。

举例:
先创建一个表,用来说明说明情况下是聚簇索引和非聚簇索引:

CREATE TABLE student (
  `id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` VARCHAR (50) COMMENT '学生姓名',
  `age` INT NOT NULL DEFAULT 0 COMMENT '学生年龄',
  KEY `idx_name` (`name`)
)

创建的数据库表中,主键id是一个索引,给age添加了索引。

① 直接根据主键查询获取所有字段数据,此时主键就是聚簇索引。因为主键对应的索引叶子节点存储的就是id的所有字段值。

SELECT * FROM student WHERE id = 1;

MySQL数据库中的索引(含SQL语句)

② 根据name查询所有信息,name本身就是一个唯一索引,但查询的数据包括了所有的数据,所以当命中name索引的时候,该索引的节点的数据存储的就是主键的ID,然后需要ID再查询一次。
这就是非聚簇索引。

SELECT * FROM student WHERE name = “张三”;

索引就是再单独的创建一张表,在这个表中查找数据,然后拿到ID在返回所有数据的表中查询所有的数据。

③ 根据name查询的时候,我们只查询name的值,不查询其他的信息。这种查询就是命中name索引,直接返回name的值,因为所需要的数据就是索引,此时不需要会表查询。这种场景就是非聚簇索引。

SELECT name FROM student WHERE name = “张三”;


拓展
在MySQL中InnoDB引擎的索引和文件是存放在一起的,找到索引就可以找到数据,是聚簇式设计。(默认是聚簇设计的,当你加上其他的索引,根据变化就成为了非聚簇式)

而MyISAM引擎采用的是非聚簇式设计,索引文件和数据文件不在同一个文件中。(不管怎么创建,它本质上就是非聚簇的设计)

因为在MyISAM当中,它是在MYD中存储文件的数据;在MYI中存储文件的索引;在sdi中存储表的结构。
MySQL数据库中的索引(含SQL语句)文章来源地址https://www.toymoban.com/news/detail-431134.html

到了这里,关于MySQL数据库中的索引(含SQL语句)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【数据库】MySQL 高级(进阶) SQL 语句

    location表格创建 store_info表格创建 显示表格中一个或数个字段的所有数据记录 不显示重复的数据记录 按照条件进行查询 在已知的字段数据取值范围内取值 另外还有not in命令,用法一致,表示显示不在指定范围内的字段的值。 在两个字段数据值之间取值,包含两边字段的数据

    2024年02月09日
    浏览(135)
  • 【数据库】sql server 中的sql语句--增删改查

    上面是要建立的数据库 下面是sql语句:  

    2024年02月05日
    浏览(53)
  • 【MySQL】数据库SQL语句之DML

    目录 前言: 一.DML添加数据 1.1给指定字段添加数据 1.2给全部字段添加数据 1.3批量添加数据 二.DML修改数据 三.DML删除数据 四.结尾   时隔一周,啊苏今天来更新啦,简单说说这周在做些什么吧,上课、看书、放松等,哈哈哈,所以博客就这样被搁了。   今天感觉不错,给大

    2024年02月08日
    浏览(68)
  • MySQL相关的SQL语句、数据库、数据表、字段、类型

    1、 SQL 语句不区分大小写。 SQL语句 用途 描述 mysql -u root -p 连接 MySQL 在命令行窗口中输入 mysql -u root -p 命令,回车,然后输入 MySQL 密码(不要忘记了密码,找回麻烦),再回车就连接上 MySQL 了。最初都是使用 root 用户登录,工作中不能一直使用 root 用户登录。因为 root 权限太

    2024年02月13日
    浏览(95)
  • mysql,用sql语句,建立学生-课程数据库基本表

    学生表 学号 姓名 年龄 性别 院系 Student Sno Sname Sage Ssex Sdept 课程表 课程号 课程名 先行课 学分 Course Cno Cname Cpno Ccredit 选课表 学号 课程号 成绩 SC Sno Cno Grade 注意创建表的时候要注意它的字段名,类型,长度,约束条件,取值范围要合适,大小写符号要正确,中文逗号,分号

    2024年02月08日
    浏览(58)
  • MySQL数据库概念、管理以及SQL语句的基本命令操作

    数据(data) 描述事物的符号记录 包括数字,文字、图形、图像、声音、档案记录等 以“记录”形式按统一格式进行存储(记录可以看成一条记录) 表 将不同的记录组织在一起 用来存储具体数据 记录:行 字段(属性):列 以行+列的形式就组成了表(数据存储在表中) 数

    2024年02月08日
    浏览(70)
  • MySQL 数据库实用指南:测试数据准备、SQL语句规范与基本操作

    欢迎来到小K的MySQL专栏,本节将为大家准备MySQL测试数据、以及带来SQL语句规范、数据库的基本操作的详细讲解 要学习SQL查询语句,首先必须解决一个问题,数据问题。为了方便大家学习阅读我的文章,在这里提供了一个test.sql文件 ✨ 登录MySQL,输入 source xxx/test.sql 导入sql文

    2024年02月08日
    浏览(82)
  • MySQL数据库第十一课---------SQl语句的拔高-------水平提升

                                                       个人主页 ::小小页面                   gitee页面 :秦大大                 一个爱分享的小博主  欢迎小可爱们前来借鉴 ______________________________________________________    SQL提高   日期函数     

    2024年02月16日
    浏览(63)
  • 通过sql语句查询出某个字段在数据库中的某个表

    新入职的小伙伴,肯定还没熟悉数据库中表于表的关系以及表中的字段的含义是什么,就需要进行需求开发,导致会出现对某个字段在哪张表里面都分辨不清,不过不要怕,可以利用已下sql语句快速找到。(要注意的是,此方法可能不适用于所有类型的数据库系统,但对于大

    2024年02月16日
    浏览(74)
  • MySQL数据库增删改查及聚合查询SQL语句学习汇总

    目录 数据库增删改查SQL语句 MySQL数据库指令 1.查询数据库 2.创建数据库 3.删除数据库 4.选择数据库 创建表table   查看所有表 创建表 查看指定表的结构 删除表 数据库命令进行注释 增删改查(CRUD)详细说明 增加 SQL库提供了关于时间的函数:now()  查询 查询表作列与列之间进

    2024年02月09日
    浏览(86)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包