MySQL学习：索引

1. 什么是索引：

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

2.索引的分类：

• 主键索引（PRIMARY KEY）
  • 一张表中可以有多个列可以作为主键（不建议使用联合主键），主键是不可以重复的，自带唯一和非空约束，主键索引只能是唯一的。
• 全文索引（FULLTEXT）
  • 与引擎有关，以前出现在MyISAM引擎条件下。
• 常规索引（UNIQUE KEY）
  • 默认索引。
• 唯一索引（INDEX/KEY）
  • 多个列都可以表示为唯一索引，这个唯一可以理解为属性都唯一，不允许具有索引值相同的行，避免列中重复，从而禁止重复的索引或键值 。

3.与索引有关的语句：

-- 显示表中拥有的所有索引信息。
SHOW INDEX FROM `表名`;
SHOW INDEX FROM `scholl`.`student`;

-- 增加索引。
ALTER TABLE `表名` ADD 索引类型 INDEX 新增索引名(索引所用列名);
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `index_name`(student_name);

-- 模拟SQL优化器执行SQL语句,关键字:EXPLAIN分析该关键字之后的SQL执行状况。
-- 非全文索引/常规索引。
EXPLAIN SELECT * FROM `school`.`student`;

-- 全文索引。
SELECT * FROM `表名` WHERE MATCH(`全文索引名称`) AGAINST(索引匹配值);

EXPLAIN SELECT * FROM `school`.`student` WHERE MATCH(`student_name`) AGAINST('老八');

4.测试索引：

建立测试索引所需表：

CREATE DATABASE `app` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `app`;

CREATE TABLE `app`.`user`(
`no` INT(4) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`name` VARCHAR(20) DEFAULT 'NULL' COMMENT '用户名称',
`tel` VARCHAR(11) DEFAULT '00000000000' COMMENT '联系电话',
`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
`create_date` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`no`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT 'app用户表';

建立插入100万个数据的函数：

DELIMITER $$ -- 定界符，必写。
CREATE FUNCTION insertData()
RETURNS INT
DETERMINISTIC
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app`.`user`(`name`, `tel`, `age`)
		VALUES(CONCAT('用户:',i), '13898298932', FLOOR(RAND()*100));	
		SET i = i+1;	
	END WHILE;
	RETURN i;
END;

开始测试：

SELECT insertData(); -- 执行函数插入1000k行数据。

/*
	在一百万数据中尝试正常查询，没有索引。
	体会下来，速度很慢。
*/
SELECT * FROM `app`.`user` WHERE `app`.`user`.`name` = '用户:123421';


/*
	紧接着我们创建一个常规索引给user表中的name字段之后，
	再进行上面语句的查询，可以发现查询速度极大减少。
	我们可以运用 EXPLAIN 关键字查看到明显查找行数都不一样。
*/
CREATE INDEX `id_user_name` ON `app`.`user`(`name`);

SELECT * FROM `app`.`user` WHERE `app`.`user`.`name` = '用户:123421';

EXPLAIN SELECT * FROM `app`.`user` WHERE `app`.`user`.`name` = '用户:123421';

5.索引的一些小贴士：

• 一般建表不加，看情况读表才加。
• 索引数量也会影响性能，一般不是越多越好，拿空间换性能。
• 数据量小的表不需要加索引就可以很快的实现查找。
• 索引加在用来查询频率高的字段上。
• 不要对进程变动的数据添加索引。

6.索引用到的数据结构：

Hash类型

B树类型

• BTree数据结构是InnoDB的默认数据结构。

2021.1.26
本文章是本人学习笔记，不进行任何商用所以不支持转载请理解，也请别拿去商用！
如果觉得对你有帮助那么欢迎你随时来回顾！
只为记录本人学习历程。
毕