7、索引

最新推荐文章于 2024-11-07 14:28:14 发布

原创最新推荐文章于 2024-11-07 14:28:14 发布 · 349 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #sql #索引

MySQL 专栏收录该内容

20 篇文章

订阅专栏

7、索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1、索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（UNIQUE KEY）
避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引（KEY/INDEX）
- 默认的，index，key关键字来设置
全文索引（FullText）
- 在特定的数据库引擎下才有，MyISAM
- 快速定位数据

基础语法：

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引
-- 显示所有的索引信息
USE school;
SHOW INDEX FROM student;

-- 增加一个全文索引（索引名） 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);

-- explain分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM  student WHERE MATCH(studentName) AGAINST('刘');

7.2、测试索引

USE 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,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT 'app用户表';
--  插入100万数据
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`)
		VALUES (
		CONCAT('用户1'),
		'1072805311@qq.com',
		CONCAT('18',FLOOR(
		RAND()*
		(
		(999999999-100000000)+100000)
		)
		),
		FLOOR(RAND()*2),
		UUID(),
		FLOOR(RAND()*100)
		);
		SET i = i + 1;
		
	END WHILE;
	RETURN i;
	
END ;
SELECT mock_data();

-- 测试插入一条数据
INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES (
CONCAT('用户1'),
'1072805311@qq.com',
CONCAT('18',FLOOR(
RAND()*
(
(999999999-100000000)+100000)
)
),
FLOOR(RAND()*2),
UUID(),
FLOOR(RAND()*100)
);

SELECT COUNT(*) FROM app_user;
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.167 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.171 sec 
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.166 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 1.163 sec
 
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';

SELECT * FROM student;

-- id_表名_字段名
-- CREATE INDEX 索引名 on 表(字段);
CREATE INDEX id_app_user_name ON app_user(`name`); -- 7.473 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.001 sec
SELECT * FROM app_user WHERE `name` = '用户9999'; -- 0.003 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';