MySQL查询优化之 index 索引的分类和使用
2024-09-01 10:06:56
索引的分类
- 主键索引 (PRIMARY KEY)
- 唯一的标识符, 主键不可重复, 只能有一列作为主键
- 唯一索引 (Unique KEY)
- 避免重复的列出现, 唯一索引可以重复, 多个列都可以标识为唯一索引
- 常规索引 (KEY/INDEX)
- 默认的, index 和 key 关键字可以设置常规索引
- 全文索引 (FullText)
- 快速定位数据, 在 MyISAM 引擎下才有
- 只能用于CHAR , VARCHAR , TEXT数据列类型, 适合大型数据集
基础语法
/*
# 方法一:创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
*/
-- 方法二:CREATE在已存在的表上创建索引
-- CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;
/*常规索引*/
CREATE INDEX id_app_user_name ON app_user(`name`);
-- 方法三:ALTER TABLE在已存在的表上创建索引
-- ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX
-- 索引名 (字段名[(长度)] [ASC |DESC]) ;
/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname`(`StudentName`);
-- 删除索引:DROP INDEX 索引名 ON 表名字;
DROP INDEX `id_app_user_name` ON app_user
-- 删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行,MATCH() 返回一个相关性值。即,在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
/*
开始之前,先说一下全文索引的版本、存储引擎、数据类型的支持情况
MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;
MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时,要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/
测试索引
-- 建表
-- 默认时间 DEFAULT CURRENT_TIMESTAMP
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,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
-- 创建 插入一百万条数据 的函数
DELIMITER $$ -- 写函数之前的默认操作
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
-- 插入数据
INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`,`create_time`)
VALUES(
CONCAT('用户', i),
'896352417@qq.com',
CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),
FLOOR(RAND()*2),
UUID(),
FLOOR(RAND()*100),
NOW()
);
SET i = i+1;
END WHILE;
RETURN i;
END;
-- 调用函数
SELECT mock_data();
SELECT * FROM app_user WHERE `name`='用户9999' -- 耗时 0.686 sec
SELECT * FROM app_user WHERE `name`='用户9999' -- 耗时 0.711 sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- 查询了1000258行数据
-- 一般索引命名: id_表名_字段名
-- 创建索引: CREATE INDEX 索引名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);
-- 测试发现快了许多
SELECT * FROM app_user WHERE `name`='用户9999' -- 耗时 0 sec
EXPLAIN SELECT * FROM app_user WHERE `name`='用户9999' -- 查询了一行数据
索引准则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
索引的数据结构
-- 我们可以在创建上述索引的时候,为其指定索引类型,分两类
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 等索引;
最新文章
- 10月27日PHP加载类、设计模式(单例模式和工厂模式)、面向对象的六大原则
- MySql增删改查命令
- 图层的核心动画(CABaseAnimation)
- iOS 通过二进制判断图片类型
- 20 款超棒免费的 Bootstrap 管理和前端模板
- 为Mac自带的Apache配置PHP和虚拟机
- html to pdf
- Struts2 Handle 404 error page and wrong action
- javaWeb学习总结(11)- 监听器(Listener)学习(2)
- [模拟赛] T3 最优序列
- Echarts line折线图使用(vue)
- python Flask web框架
- Lodop打印控件 如何打印虚线
- 部署WEB项目到服务器(四)部署WEB项目Forum到linux服务器(Ubuntu)详解
- hash的排序(转载)
- ABP之事件总线(2)
- JVM jmap dump 分析dump文件 / 如何使用Eclipse MemoryAnalyzer MAT 排查线上问题
- HP Notebook PCs - Updating the BIOS
- mysql 存储过程用程序调的问题
- Python 去除列表中重复的元素
热门文章
- 应用案例——高并发 WEB 服务器队列的应用
- elasticsearch-head 配置
- 工具-Redis-介绍(99.6.1)
- sqli-labs Less24 登录出现报错Cannot send session cache limiter....
- 30道 有趣的 的 JVM 面试题
- Redisearch实现的全文检索功能服务
- Autofac的基本使用---2、普通类型
- 安装篇六:安装PHP(7.2.29版本)
- 转载--对batch normalization的理解
- [leetcode]203. Remove Linked List Elements链表中删除节点