数据库(DataBase)

您所在的位置：网站首页 › 医院数据库管理系统设计索引 › 数据库(DataBase)

数据库(DataBase)

2024-03-23 15:05| 来源: 网络整理| 查看: 265

7.索引

索引的定义：

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质:索引是数据结构。

通过索引，可以使我们更快地获得我们所需的数据。

Eg：原先搜索耗时：0.5s；使用索引后：0.000001s；

索引的作用：

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

·主键索引：Primary Key

唯一的标识，主键不可重复，且只能有一个列作为主键。特点 :

最常见的索引类型确保数据记录的唯一性确定特定数据记录在数据库中的位置

·唯一索引：Unique Key

避免重复的列出现，唯一索引可以重复，每个列都可以标识为唯一索引。与主键索引的区别：

主键索引只能有一个唯一索引可能有多个

·常规索引：Key/Index

默认的，使用Index或者Key关键字来设置。

注意 :

index 和 key 关键字都可以设置常规索引应加在查询找条件的字段不宜添加太多常规索引,影响数据的插入,删除和修改操作

·全文索引：FullTest

在特定的数据库引擎下才有，MyIsam。

作用 : 快速定位特定数据

注意 :

·只能用于MyISAM类型的数据表

·只能用于CHAR , VARCHAR , TEXT数据列类型

·适合大型数据集

2.百万条数据测试

索引在查询数据中的用途，在小数据中一般难以体现，我们创建一个百万数据的用户表来测试索引的效果。

(1)创建测试表格： -- 一百万条数据检验索引的效果。 use school;-- 数据库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` timestamp NULL DEFAULT NULL COMMENT '创建时间', `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', -- Mysql 5.5版本，需要注意：`create_time`和`update_time` 插入数据时，针对创建时间字段：在sql里now() 或者在代码里new date()更改后的sql，把默认值给个空，否则报错。 PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表';

MySQL 5.5版本，需要注意：create_time和update_time 插入数据时，针对创建时间字段：在sql里now() 或者在代码里new date()更改后的sql，把默认值给个空，否则报错。

(2)插入百万条数据： DROP FUNCTION IF EXISTS mock_data; -- 写函数之前必须要写，标志:$$ DELIMITER $$ CREATE FUNCTION mock_data() RETURNS INT -- 注意returns，否则报错。 BEGIN DECLARE num INT DEFAULT 1000000; -- num 作为截止数字，定义为百万， DECLARE i INT DEFAULT 0; WHILE i CONCAT('用户', i): CONCAT(str1,str2,……) CONCAT函数，将“str1,str2,……”等字段连接起来，构成一个字符串。连接一个“用户”+i的字符串，当做用户名。 `email` ——> CONCAT('100',i,'@qq.com')：连接“100”+i+“@qq.com”，构成整个邮箱。 `phone` ——> CONCAT('13', FLOOR(RAND()*(999999999-100000000)+100000000))： RAND()生成一个0-1的随机数，然后乘以一个足够大的数(999999999-100000000)之后，加上100000000，然后，使用FLOOR()强转成舍弃小数的整数。 */

前两步执行结果：

在这里插入图片描述

(3)数据查询检验：

·查询数据总量：耗时14.032sec。

SELECT mock_data();

在这里插入图片描述

输出：

在这里插入图片描述

·查询特定数据的耗时：

-- 对不同用户的查询，耗时一般不同，即使是同一个用户，多次查询的耗时也可能不同。 SELECT * FROM app_user WHERE name = '用户7777'; -- 1.188sec SELECT * FROM app_user WHERE name = '用户7777'; -- 0.766sec SELECT * FROM app_user WHERE name = '用户77777'; -- 0.641sec SELECT * FROM app_user WHERE name = '用户777777'; -- 0.594sec

在这里插入图片描述

·查询查找过程经历的数据量：

explain SELECT * FROM app_user WHERE name = '用户7777'; -- 寻找了2009736条数据后才找到用户7777 explain SELECT * FROM app_user WHERE name = '用户77777'; -- 寻找了2995119条数据后才找到用户77777

寻找了2009736条数据后，才找到“用户7777”

在这里插入图片描述

·修改表格，增添索引：

创建索引的格式：create index 索引名 on 表名(加持字段)（注：索引的名字惯用格式：id _ 表名 _ 字段名）

-- 索引的名字惯用格式：id_表名_字段名 -- 创建索引的格式：create index 索引名 on 表名(加持字段) create index id_app_user_name on app_user(`name`);

在这里插入图片描述

-- 添加索引后再次测试 explain SELECT * FROM app_user WHERE name = '用户7777'; -- 寻找了3条数据后才找到用户7777 explain SELECT * FROM app_user WHERE name = '用户77777'; -- 寻找了3条数据后才找到用户77777

在这里插入图片描述

索引在小数据中的用处不大，但是在大数据中效果很明显。

3.索引原则

·索引不是越多越好

·不要对经常变动的数据加索引

·小数据量的表建议不要加索引

·索引一般应加在查找条件的字段

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类 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 等索引；参考文献

《【狂神说Java】MySQL最新教程通俗易懂》视频连接：https://www.bilibili.com/video/BV1NJ411J79W

《狂神说MySQL06：事务和索引》原文连接

《mysql5.5 timestamp类型create_time和update_time更新时间》

原文连接 2020.05.18

【本文地址】

公司简介

联系我们