7、索引 下

7.4、常规索引

作用 : 快速定位特定数据

注意 :

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

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

-- 创建后添加 
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

7.5、全文索引

百度搜索：全文索引

作用 : 快速定位特定数据

注意 :

• 只能用于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; 
*/

/*增加全文索引*/
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 版本、存储引擎和数据类型是否支持全文索引。 
*/

关于 EXPLAIN ： https://blog.csdn.net/jiadajing267/article/details/81269067

百度搜索：explain 中文意思：解释，说明

扩展：测试索引

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

批量插入数据：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 app_user(`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 app_user WHERE name = '用户9999'; -- 查看耗时 
  SELECT * FROM app_user WHERE name = '用户9999'; 
  SELECT * FROM app_user WHERE name = '用户9999'; 
  
  mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G 
  
  *************************** 1. row *************************** 
  			id: 1 
  select_type: SIMPLE 
  		table: app_user 
  	partitions: NULL 
  		type: ALL 
  possible_keys: NULL 
  		key: NULL 
  	key_len: NULL 
  		ref: NULL 
  		rows: 992759 
  	filtered: 10.00 
  		Extra: Using where 
  1 row in set, 1 warning (0.00 sec)
  

• 创建索引

  CREATE INDEX idx_app_user_name ON app_user(name);
  

• 测试普通索引

  mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G 
  *************************** 1. row *************************** 
  			id: 1 
  	select_type: SIMPLE 
  		table: app_user 
  	partitions: NULL 
  		type: ref 
  possible_keys: idx_app_user_name 
  		key: idx_app_user_name 
  	key_len: 203 
  		ref: const 
  		rows: 1 
  	filtered: 100.00 
  		Extra: NULL 
  1 row in set, 1 warning (0.00 sec) 
  
  mysql> SELECT * FROM app_user WHERE name = '用户9999'; 
  1 row in set (0.00 sec) 
  
  mysql> SELECT * FROM app_user WHERE name = '用户9999'; 
  1 row in set (0.00 sec) 
  
  mysql> SELECT * FROM app_user WHERE name = '用户9999'; 
  1 row in set (0.00 sec)
  

7.6、索引准则

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

7.7、索引的数据结构

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

关于索引的本质：http://blog.codinglabs.org/articles/theory-of-mysql-index.html