索引（简单总结）

7、索引（重点）

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

本质：索引是数据结构。

7.1、索引的分类

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

• 主键索引（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]) ;

方法三：使用ALTER TABLE在已存在的表上创建索引
       ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            索引名 (字段名[(长度)] [ASC |DESC]) ;
                                                    
删除索引：DROP INDEX 索引名 ON 表名字;
删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;

-- 显示所有的索引信息
SHOW INDEX FROM student
*/

-- 一、添加主键索引
-- 在前面 3、MySQL数据管理 有说到

--二、添加唯一索引（创建表时添加）
CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   UNIQUE KEY `GradeID` (`GradeID`)
    --           索引名     列名
)

-- 三、添加一个常规索引
CREATE TABLE `result`(
   -- 省略一些代码
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

-- 四、增加一个全文索引
ALTER TABLE test.student ADD FULLTEXT INDEX `studentName`(`studentName`)
--                                              索引名         列名  

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student； -- 非全文索引

/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');

 

7.2、测试索引

拓展：测试索引

建表app_user：

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用户表'

索引效率测试

无索引

SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时 0.993 sec
SELECT * FROM app_user WHERE name = '用户9999'; -- 1.003 sec
SELECT * FROM app_user WHERE name = '用户9999'; -- 0.988 sec

-- 看一下它是怎么查询的
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999';
需要查询991749行才查到name = '用户9999'的这行

创建索引

CREATE INDEX idx_app_user_name ON app_user(name);

测试常规索引

SELECT * FROM app_user WHERE name = '用户9999'; -- 0.01 sec

SELECT * FROM app_user WHERE name = '用户9999'; -- 0.01 sec

SELECT * FROM app_user WHERE name = '用户9999'; -- 0.01 sec

-- 看一下它是怎么查询的
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'
根据索引只查询1行就查到了name = '用户9999'的这行

 

7.3、索引原则

• 索引不是越多越好

• 不要对经常变动的数据加索引

• 小数据量的表建议不要加索引

• 索引一般应加在查找条件的字段

索引的数据结构

我们可以在创建上述索引的时候，为其指定索引类型，分两类: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 等索引；