【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.索引的作用

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

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. 在创建表的时候给字段增加索引
2. 表创建完毕后，增减索引
*/
-- 显示所有的索引信息
use `school`
SHOW INDEX FROM student

增加一个全文索引（索引名）列名

-- 增加一个全文索引（索引名）列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`StudentName`)	-- 前面是索引的名字，括号内是字段名
-- 增加后再运行一次SHOW INDEX FROM student，发现索引多了一行

在这里插入图片描述

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.测试索引

建表

-- 被存到数据库`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用户表'

插入数据

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(); -- 执行此函数 生成一百万条数据。执行该函数需要等待一段时间

在这里插入图片描述

查询第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、Hash、Full-text 等索引；