【WEEK7】 【DAY1】索引【中文版】

2024-04-09 10:44
文章标签 索引 day1 中文版 week7

本文主要是介绍【WEEK7】 【DAY1】索引【中文版】,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

2024.4.8 Monday

目录

  • 7.索引
    • 7.1.内涵
    • 7.2.索引的作用
    • 7.3.索引的分类
      • 7.3.1.主键索引(PRIMARY KEY)
        • 7.3.1.1.主键 : 某一个属性组能唯一标识一条记录
        • 7.3.1.2.特点 :
      • 7.3.2.唯一索引(UNIQUE KEY)
        • 7.3.2.1.作用 : 避免同一个表中某数据列中的值重复
        • 7.3.2.2.与主键索引的区别:
      • 7.3.3.常规索引(KEY/INDEX)
        • 7.3.3.1.作用 : 快速定位特定数据
        • 7.3.3.2.注意 :
      • 7.3.4.全文索引(FULLTEXT)
        • 7.3.4.1.百度搜索:全文索引
        • 7.3.4.2.作用 : 快速定位特定数据
        • 7.3.4.3.注意 :
        • 7.3.4.4.版本声明等
        • 7.3.4.5.索引的使用
        • 7.3.4.6.例
        • 7.3.4.7.使用全文索引
        • 7.3.4.8.测试索引
    • 7.4.索引准则
    • 7.5.索引的数据结构

7.索引

7.1.内涵

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
提取句子主干,就可以得到索引的本质:索引是数据结构。
https://blog.codinglabs.org/articles/theory-of-mysql-index.html

7.2.索引的作用

  1. 提高查询速度
  2. 确保数据的唯一性
  3. 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
  4. 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
  5. 全文检索字段进行搜索优化

7.3.索引的分类

7.3.1.主键索引(PRIMARY KEY)

唯一的标识,主键不可重复,只能有一个列作为主键

7.3.1.1.主键 : 某一个属性组能唯一标识一条记录
7.3.1.2.特点 :
  • 最常见的索引类型
  • 确保数据记录的唯一性
  • 确定特定数据记录在数据库中的位置

7.3.2.唯一索引(UNIQUE KEY)

7.3.2.1.作用 : 避免同一个表中某数据列中的值重复
7.3.2.2.与主键索引的区别:
  • 主键索引只能有一个
  • 唯一索引可能有多个
CREATE TABLE `Grade`(`GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,`GradeName` VARCHAR(32) NOT NULL UNIQUE-- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

7.3.3.常规索引(KEY/INDEX)

7.3.3.1.作用 : 快速定位特定数据
7.3.3.2.注意 :
  • index 和 key 关键字都可以设置常规索引
  • 应加在查询找条件的字段
  • 不宜添加太多常规索引,影响数据的插入,删除和修改操作
CREATE TABLE `result`(-- 省略一些代码INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

7.3.4.全文索引(FULLTEXT)

7.3.4.1.百度搜索:全文索引
7.3.4.2.作用 : 快速定位特定数据
7.3.4.3.注意 :
  • 只能用于MyISAM类型的数据表
  • 只能用于CHAR , VARCHAR , TEXT数据列类型
  • 适合大型数据集
7.3.4.4.版本声明等
/*
开始之前,先说一下全文索引的版本、存储引擎、数据类型的支持情况MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;
MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时,要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/
7.3.4.5.索引的使用
/*
#方法一:创建表时CREATE TABLE 表名 (字段名1 数据类型 [完整性约束条件…],字段名2 数据类型 [完整性约束条件…],[UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY[索引名] (字段名[(长度)] [ASC |DESC]));#方法二:CREATE在已存在的表上创建索引CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名ON 表名 (字段名[(长度)] [ASC |DESC]) ;#方法三:ALTER TABLE在已存在的表上创建索引ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX索引名 (字段名[(长度)] [ASC |DESC]) ;#删除索引:DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;#显示索引信息: SHOW INDEX FROM student;
*/
7.3.4.6.例
  1. 显示所有的索引信息
-- 索引 --
/* 索引的使用
1. 在创建表的时候给字段增加索引
2. 表创建完毕后,增减索引
*/
-- 显示所有的索引信息
use `school`
SHOW INDEX FROM student
  1. 增加一个全文索引(索引名)列名
-- 增加一个全文索引(索引名)列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`StudentName`)	-- 前面是索引的名字,括号内是字段名
-- 增加后再运行一次SHOW INDEX FROM student,发现索引多了一行

在这里插入图片描述

  1. EXPLAIN 分析sql语句执行性能
-- EXPLAIN 分析sql语句执行性能
EXPLAIN SELECT * FROM student;	-- 非全文索引

在这里插入图片描述

SELECT * FROM student WHERE studentNo = '1000';

在这里插入图片描述

7.3.4.7.使用全文索引
/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行,MATCH() 返回一个相关性值。即,在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
SELECT * FROM student WHERE MATCH(studentName) AGAINST('吴')

在这里插入图片描述

7.3.4.8.测试索引
  1. 建表
-- 被存到数据库`school`里了
CREATE TABLE `app_user` (`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,`name` VARCHAR(50) DEFAULT'' COMMENT'用户昵称',`email` VARCHAR(50) NOT NULL COMMENT'用户邮箱',`phone` VARCHAR(20) DEFAULT'' COMMENT'手机号',`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别(0:男;1:女)',`password` VARCHAR(100) NOT NULL COMMENT '密码',`age` TINYINT(4) DEFAULT'0'  COMMENT '年龄',-- `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,	-- 这行(包含了默认值的)在mysql5.5里不能运行`create_time` DATETIME DEFAULT'1999-01-01 01:01:01' COMMENT '试一下',`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
  1. 插入数据
DROP FUNCTION IF EXISTS mock_data
-- 插入100万数据.
delimiter $$	-- 写函数之前必须要写,标志
-- set global log_bin_trust_function_creators=TRUE;CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
-- declare声明DECLARE num INT DEFAULT 1000000;DECLARE i INT DEFAULT 0;-- 	循环运算WHILE i<num DO-- 	插入语句INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'19224305@qq.com',CONCAT('18',FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
-- 		UUID()是通用唯一识别码(Universally Unique Identifier)的缩写SET i=i+1;END WHILE;RETURN i;END;
SELECT mock_data(); -- 执行此函数 生成一百万条数据。执行该函数需要等待一段时间

在这里插入图片描述

  1. 查询第9999条数据
  • 无索引时
SELECT * FROM app_user WHERE `name` = '用户9999';	-- > 时间: 0.344s
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';		-- > 为991749
  • 有索引时
-- 创建索引
-- 命名新建的索引id_表名_字段名
-- CREATE INDEX 索引名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);-- 创建索引后检索时间减少了
SELECT * FROM app_user WHERE `name` = '用户9999';	-- > 时间: 0s
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';	-- > rows为1

7.4.索引准则

7.4.1.索引不是越多越好
7.4.2.不要对经常变动的数据加索引
7.4.3.小数据量的表建议不要加索引
7.4.4.索引一般应加在查找条件的字段

7.5.索引的数据结构

-- 我们可以在创建上述索引的时候,为其指定索引类型,分两类
hash类型的索引:查询单条快,范围查询慢
btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
Archive 不支持事务,支持表级别锁定,不支持 B-tree、HashFull-text 等索引;

这篇关于【WEEK7】 【DAY1】索引【中文版】的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/887943

相关文章

Mysql中InnoDB与MyISAM索引差异详解(最新整理)

《Mysql中InnoDB与MyISAM索引差异详解(最新整理)》InnoDB和MyISAM在索引实现和特性上有差异,包括聚集索引、非聚集索引、事务支持、并发控制、覆盖索引、主键约束、外键支持和物理存... 目录1. 索引类型与数据存储方式InnoDBMyISAM2. 事务与并发控制InnoDBMyISAM

StarRocks索引详解(最新整理)

《StarRocks索引详解(最新整理)》StarRocks支持多种索引类型,包括主键索引、前缀索引、Bitmap索引和Bloomfilter索引,这些索引类型适用于不同场景,如唯一性约束、减少索引空... 目录1. 主键索引(Primary Key Index)2. 前缀索引(Prefix Index /

MySQL进阶之路索引失效的11种情况详析

《MySQL进阶之路索引失效的11种情况详析》:本文主要介绍MySQL查询优化中的11种常见情况,包括索引的使用和优化策略,通过这些策略,开发者可以显著提升查询性能,需要的朋友可以参考下... 目录前言图示1. 使用不等式操作符(!=, <, >)2. 使用 OR 连接多个条件3. 对索引字段进行计算操作4

Java实现Elasticsearch查询当前索引全部数据的完整代码

《Java实现Elasticsearch查询当前索引全部数据的完整代码》:本文主要介绍如何在Java中实现查询Elasticsearch索引中指定条件下的全部数据,通过设置滚动查询参数(scrol... 目录需求背景通常情况Java 实现查询 Elasticsearch 全部数据写在最后需求背景通常情况下

Pandas中多重索引技巧的实现

《Pandas中多重索引技巧的实现》Pandas中的多重索引功能强大,适用于处理多维数据,本文就来介绍一下多重索引技巧,具有一定的参考价值,感兴趣的可以了解一下... 目录1.多重索引概述2.多重索引的基本操作2.1 选择和切片多重索引2.2 交换层级与重设索引3.多重索引的高级操作3.1 多重索引的分组聚

oracle数据库索引失效的问题及解决

《oracle数据库索引失效的问题及解决》本文总结了在Oracle数据库中索引失效的一些常见场景,包括使用isnull、isnotnull、!=、、、函数处理、like前置%查询以及范围索引和等值索引... 目录oracle数据库索引失效问题场景环境索引失效情况及验证结论一结论二结论三结论四结论五总结ora

Python中列表的高级索引技巧分享

《Python中列表的高级索引技巧分享》列表是Python中最常用的数据结构之一,它允许你存储多个元素,并且可以通过索引来访问这些元素,本文将带你深入了解Python列表的高级索引技巧,希望对... 目录1.基本索引2.切片3.负数索引切片4.步长5.多维列表6.列表解析7.切片赋值8.删除元素9.反转列表

MySQL的索引失效的原因实例及解决方案

《MySQL的索引失效的原因实例及解决方案》这篇文章主要讨论了MySQL索引失效的常见原因及其解决方案,它涵盖了数据类型不匹配、隐式转换、函数或表达式、范围查询、LIKE查询、OR条件、全表扫描、索引... 目录1. 数据类型不匹配2. 隐式转换3. 函数或表达式4. 范围查询之后的列5. like 查询6

PostgreSQL如何查询表结构和索引信息

《PostgreSQL如何查询表结构和索引信息》文章介绍了在PostgreSQL中查询表结构和索引信息的几种方法,包括使用`d`元命令、系统数据字典查询以及使用可视化工具DBeaver... 目录前言使用\d元命令查看表字段信息和索引信息通过系统数据字典查询表结构通过系统数据字典查询索引信息查询所有的表名可

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题,本文将一起从代码层去分析为什么没有建立索引? 开源ERP项目地址:https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置