7.MySQL中的索引

最新推荐文章于 2025-11-30 14:10:27 发布

原创最新推荐文章于 2025-11-30 14:10:27 发布 · 154 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #database

本文详细介绍了MySQL中的四种索引类型：主键索引、唯一索引、常规索引和全文索引，以及如何创建和使用它们。通过实例展示了索引对查询性能的影响，强调了索引原则，并探讨了索引数据结构。同时，通过创建和测试表，对比了无索引与有索引查询的效率提升。

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。

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

7.1索引的分类

主键索引(PRIMARY KEY):唯一的标识，主键不可重复，只能有一个列作为主键

PRINMARY KEY (studentno)

唯一索引(UNIQUE KEY)：避免重复的列出现，唯一的索引可以重复，多个列可以标识位唯一索引

UNIQUE KEY studentno（studentno）

常规索引(LEY/INDEX)：默认的，index，key关键字设置

KEY studentno（studentno）

全文索引(FullText)：在特点的数据库引擎下才有，MylSAM ，快速定位数据

FULLTEXT INDEX studentno（studentno）

基础语法：

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

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

-- EXPLAN 分析sql执行的现状
EXPLAIN SELECT *FROM student  -- 非全文索引
SELECT *FROM student WHERE MATCH(studentname) AGAINST('张')

7.2测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8   -- 创建一个表

-- 插入100万数据.
DELIMITER $$  -- 创建函数时必须要有
CREATE FUNCTION mock_data()
RETURNS INT DETERMINISTIC
-- 注意returns，否则报错。
BEGIN
DECLARE num INT DEFAULT 1000000;
-- num 作为截止数字，定义为百万，
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('13', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
   SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();-- 执行此函数 生成一百万条数

SELECT*FROM app_user WHERE `name`='用户9999';  -- 总耗时   : 1.297 sec
SELECT*FROM app_user WHERE `name`='用户9999'; -- 总耗时      : 1.311 sec

EXPLAIN SELECT * FROM app_user WHERE `name` ='用户9999';

SELECT *FROM app_user

-- 创建一个索引
-- id_表名_字段名  :索引名
-- CREATE INDEX 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);  -- 每行数据都创建一个索引
SELECT*FROM app_user WHERE `name`='用户9999'; -- 执行耗时   : 0.009 sec
SELECT*FROM app_user WHERE `name`='用户999'; -- 执行耗时   : 0.009 sec
EXPLAIN SELECT * FROM app_user WHERE `name` ='用户9999';  --  执行耗时   : 0.002 sec