mssql 索引 sqlyog索引

7.1 索引的分类

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

• 主键索引（PRIMARY KEY）

• 唯一的标识，主键不可重复，只能有一个列作为主键

• 唯一索引（UNIQUE KEY）

• 避免重读的列出现，唯一索引可以重复，多个列都可以标识为唯一索引

• 常规索引（KEY / INDEX）

• 默认的，通过index或key关键字来设置

• 全文索引（FULLTEXT）

• 在特定的数据库引擎下采用，之前只有MYISAM有
• 快速定位数据

基础语法

-- 索引的使用
 -- 方式1：在创建表的时候给字段增加索引
 -- 方式2：表创建完毕后，增加索引
 
 -- 显示所有的索引信息
 SHOW INDEX FROM `student`
 
 -- 增加一个全文索引：格式：索引类型 索引名（列名），主键索引只要有索引名就行了
 ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `StudentName`(`StudentName`)

 -- 删除索引
 DROP INDEX 索引名 ON 表名
 
 -- EXPLAIN分析sql执行的情况
 EXPLAIN SELECT * FROM `student`; -- 非全文索引
 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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`)
 ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

插入100万条数据

-- 插入100万数据
 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),
     CONCAT('18',FLOOR(RAND()*((999999999-100000000)[phone]))),
     FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
     SET i=i+1;
   END WHILE;
   RETURN i;
 END;
 SELECT mock_data() -- 执行此函数 生成一百万条数据

运行时间：

输出结果：

注意： 删除mock_data函数

DROP FUNCTION mock_data

通过查看数据所用时间来体现索引的高效：

1.SELECT * FROM `app_user` WHERE `name`='用户9999';

2.EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999';

此时需要查询的rows为993623

3.SELECT * FROM `student`

查询整个表格数据用时很短

4.创建索引后进行查询

-- 创建索引的的方式3
 -- 3、CREATE INDEX 索引名 ON 表（字段），注意索引名一般为：id_表名_字段名
 CREATE INDEX id_app_user_name ON `app_user`(`name`)

4.1 SELECT * FROM `app_user` WHERE `name`='用户9999' ;

发现添加索引后，查询时间变短

4.2 EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999';

添加索引后，查询rows为1，极大的提高了效率

 

总结：索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显。

 

7.3 索引原则

• 索引不是越多越好
• 不要对进程变动数据加索引
• 小数据量的表不需要加索引
• 索引一般加在常用来查询的字段上

索引的数据结构

Hash类型的索引

Btree：InnoDB默认的数据结构！