索引基本认识和操作

索引的概念

：索引（Index）是帮助MySQL高效获取数据的数据结构，通俗来讲索引就好比书本的目录，加快数据库的查询速度。

索引的作用

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

索引的分类

• 主键索引

  主键：某一个属性组能唯一标识一条记录

  特点:

  • 最常见的索引
  • 确保数据的唯一性
  • 确定特定数据记录在数据库的唯一性

• 唯一索引

• 常规索引

  作用 : 快速定位特定数据

  注意 :

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

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

• 全文索引

  百度搜索：全文索引

  作用 : 快速定位特定数据

  注意 :

  • 只能用于MyISAM类型的数据表
  • 只能用于CHAR , VARCHAR , TEXT数据列类型
  • 适合大型数据集

• 唯一索引

  作用 : 避免同一个表中某数据列中的值重复

  与主键索引的区别

  • 主键索引只能有一个
  • 唯一索引可能有多个

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

/*
#方法一：创建表时
  　　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 版本、存储引擎和数据类型是否支持全文索引。
*/


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

create table `表名` (
	...
    index/key `索引名`(`列名`)
    
)

--- 创建表后
alter table `表名` add index `索引名`(`列名`) 

插入百万数据

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();

测试sql

explain select * from user where name='lisi';

索引的准则

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

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

索引相关操作

--- 查看表索引相关信息
show index from 表名;

--- 删除索引
ALTER TABLE 表名 DROP INDEX 索引名;

自我总结

​ 索引是为了提高查询效率，小数据可能无法感受，但是大数量很明显。添加索引之后，查询效率能变高，如果是并发这样就更加体现了索引的好处，能提高很多倍呢。未添加索引时，如果你去查询一条数据，那么sql需要一条一条的去匹配，这样很耗时；添加索引之后，数据会以某种格式存起来，这样查询它就会很快定位到，所以很快，不需要一条一条去匹配。但是索引不能随便乱用，对于经常变动的数据列不要添加索引，因为这样每次数据都要去重新更新索引数据。对于大数据量才去考虑使用索引，添加完索引之后查询效率会有明显的提升，等数据量上去之后再添加索引也没有关系，因为小数据量差别不大。

参考资料

遇见狂神说:https://space.bilibili.com/95256449/