脚本宝典收集整理的这篇文章主要介绍了03索引，脚本宝典觉得挺不错的，现在分享给大家，也给大家做个参考。

索引

索引的作用

• 提高查询速度

• 确保数据的唯一性

• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性

• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间

• 全文检索字段进行搜索优化.

索引的分类

• 主键索引（Primary KEY）

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

  特点 :

  • 最常见的索引类型

  • 确保数据记录的唯一性

  • 确定特定数据记录在数据库中的位置

• 唯一索引（unique KEY）

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

  与主键索引的区别

  • 主键索引只能有一个

  • 唯一索引可能有多个

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

• 常规索引（KEY/INDEX）

  作用 : 快速定位特定数据

  注意 :

  • index 和 key 关键字都可以设置常规索引

  • 应加在查询找条件的字段

  • 不宜添加太多常规索引,影响数据的插入,删除和修改操作

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

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

1. 创建表的时候给字段增加索引

2. 创建完毕后，增加索引

show index from 表名

explain分析sql执行的情况

EXPLAIN SELECT * FROM 表名--非全文索引

拓展：测试索引

建表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'; --执行耗时   : 4.739 sec  SELECT * FROM app_user WHERE `name` = '用户9999'; --执行耗时   : 5.133 sec  EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';  ​

创建索引

  CREATE INDEX 索引名 ON 表（字段）--创建索引结构  CREATE INDEX idx_app_user_name ON app_user(name);

    加索引后查询效率提升  SELECT * FROM app_user WHERE `name` = '用户9999'; --执行耗时   : 0.002 sec

 

索引准则

• 索引不是越多越好

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

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

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

索引的数据结构

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

 

脚本宝典总结

以上是脚本宝典为你收集整理的03索引全部内容，希望文章能够帮你解决03索引所遇到的问题。

如果觉得脚本宝典网站内容还不错，欢迎将脚本宝典推荐好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。