2022年5月3日【狂神说Java】MySQL最新教程通俗易懂4索引

7 索引

索引(index) 是帮助mysql高效获取数据的数据结构

7.1 索引的分类

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

• 主键索引 primary key
  • 唯一的标识,主键不可重复, 只能有一个列作为主键
• 唯一索引 unique key
  • 避免重复的列出现, 唯一索引可以有多个, 多个列都可以标志为唯一索引
• 常规索引 key/index
  • 默认的, index /key关键字来设置
• 全文索引 (fullTest)
  • 在特定的数据库引擎下才有, myIsam
  • 快速定位数据

-- 索引的创建
-- 1. 在创建表的时候给字段增加索引CREATE TABLE &＃96;app_user&＃96; (&＃96;id&＃96; BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (&＃96;id&＃96;)
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8 COMMENT &＃61; &＃39;app用户表&＃39;
;-- 2. 创建完毕后, alter table 给表增加索引
Alter table app_useradd INDEX &＃96;name&＃96;(&＃96;name&＃96;)-- 3. create index 索引名 on 表名(列名)
CREATE INDEX id_app_user_name ON app_user(&＃96;name&＃96;);

-- 索引的使用-- 显示所有的索引信息
show INDEX from school.student;-- 添加索引
alter table student add INDEX &＃96;pwd&＃96;(&＃96;pwd&＃96;)-- 增加一个全文索引(索引名) 列名
alter table school.student add FULLTEXT INDEX &＃96;studentname&＃96;(&＃96;name&＃96;);-- EXPLAIN 分析SQL执行的状况
EXPLAIN select * from student;explain select *from student where match(name) against(&＃39;张&＃39;);


7.2 测试索引(代码可用)

CREATE TABLE &＃96;app_user&＃96; (
&＃96;id&＃96; BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
&＃96;name&＃96; VARCHAR(50) DEFAULT&＃39;&＃39; COMMENT&＃39;用户昵称&＃39;,
&＃96;email&＃96; VARCHAR(50) NOT NULL COMMENT&＃39;用户邮箱&＃39;,
&＃96;phone&＃96; VARCHAR(20) DEFAULT&＃39;&＃39; COMMENT&＃39;手机号&＃39;,
&＃96;gender&＃96; TINYINT(4) UNSIGNED DEFAULT &＃39;0&＃39;COMMENT &＃39;性别&＃xff08;0&＃xff1a;男;1:女&＃xff09;&＃39;,
&＃96;password&＃96; VARCHAR(100) NOT NULL COMMENT &＃39;密码&＃39;,
&＃96;age&＃96; TINYINT(4) DEFAULT&＃39;0&＃39; COMMENT &＃39;年龄&＃39;,
&＃96;create_time&＃96; DATETIME DEFAULT CURRENT_TIMESTAMP,
&＃96;update_time&＃96; TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (&＃96;id&＃96;)
) ENGINE&＃61;INNODB DEFAULT CHARSET&＃61;utf8 COMMENT &＃61; &＃39;app用户表&＃39;
;-- 插入100万数据
delimiter $$ -- 写函数前必须写
create function mock_data()
returns int
BEGINDECLARE num int DEFAULT 10;DECLARE i int DEFAULT 0;WHILE i<num DOINSERT INTO app_user(&＃96;name&＃96;,&＃96;email&＃96;,&＃96;phone&＃96;,&＃96;gender&＃96;,&＃96;password&＃96;)VALUES(CONCAT(&＃39;用户&＃39;,i),&＃39;1234&＃64;1234.com&＃39;,&＃39;12345&＃39;,floor(RAND()*2),floor(RAND()*99999));set i &＃61; i&＃43;1;END WHILE;return i;
end;SELECT mock_data();/*SELECT mock_data()
> OK
> 查询时间: 35.61s
*/

-- 没加索引之前,查询需要1sexplain select * from app_user where name &＃61;&＃39;用户9999&＃39;;-- 创建索引方式3CREATE INDEX id_app_user_name ON app_user(&＃96;name&＃96;);
-- 加索引之后, 查询0s


索引 小数据量的时候,用处不大, 但是在大数据量的时候, 区别十分明显.

7.3 索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上.

索引的数据结构

• Hash类型的索引

• Btree: InnoDB默认的结构