索引

1.索引概念

在数据库中如果有上万或者上亿条数据，对数据库查询就会非常慢，这个时候就需要用索引。
索引是对数据库中某一列或多个列的值进行预排序的数据结构，通过使用索引，可以使得数据库不用扫描整张表，而是直接找到符合条件的记录位置，大大加快了索引查询的速度，同时数据库索引对于程序和用户来说是透明的。

2.索引的作用

• 大大提高查询速度
• 确保数据唯一性
• 可以加速表与表之间的链接，实现表与表之间的参照完整性
• 使用分组和排序自居进行查询时，可以显著减少分组和排序的时间
• 全文检索字段可以进行搜索优化

3.索引分类

1. 主键索引
   • 主键：可以识别某一条记录的唯一标识：
   • 确定数据的唯一性
   • 确定特定数据在数据库中的位置
2. 唯一索引
   • 避免同一个表中，某数据列中值重复
   • 主键索引只能有一个，唯一索引可以有多个

CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

3. 常规索引
   • 快速定位特定数据
   • index和key关键字都可以设置常规索引
   • 应该加载查询找条件的字段
   • 不应该设置太多的常规索引，会影响数据插入，删除，修改操作

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

4. 全文索引
   • 快速定位特定数据
   • 只能用于MylSAM类型的数据表
   • 只能用于varchar，char，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;
*/

/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);

/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';

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

/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/

4. 索引测试

• 创建用户app表

create table `appuser`(
	`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用户表"

• 批量插入数据：100W

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO appuser(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', 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 appuser where name="用户9999";
--- [SQL]select * from appuser where name="用户9999";
-- 受影响的行: 0
-- 时间: 0.435s

• 创建索引

create index indexname on appuser(`name`);
-- 再次查询
select * from appuser where name="用户9999";
-- [SQL]select * from appuser where name="用户9999";
-- 受影响的行: 0
-- 时间: 0.001s

5.索引准则

• 索引不是越多越好
• 对不经常变动的数据添加索引
• 小数据量的表一般不添加索引
• 索引一般添加在查找条件的字段上

6.索引的数据结构

• 我们可以在创建上述索引的时候，为其指定索引类型，分两类

1. hash类型的索引：查询单条快，范围查询慢
2. btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

• 不同的存储引擎支持的索引类型也不一样

1. InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
2. MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
3. Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
4. NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
5. Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；