sql基础(4)之索引

MySQL 索引

一 ,索引简介

索引在MySQL中也叫做’键 key’,是存处引擎用于快速找到记录的一种数据结构.
索引对于良好的性能非常关键,尤其是当表中的数据越来越多,索引对于性能的影响愈发重要.
索引优化应该是对查询性能优化的最有效手段了,索引能够轻易将查询性能提高好几个数量级.
索引相当于新华字典的音序表,如果要查某个汉字,不使用音序表,则需要从几百页中的数据中去找找个汉字
简单理解就是,索引就像一本书的目录,让你很快能够查询到你想要的数据.

二,索引的分类

• 普通索引
• 唯一索引
• 全文索引
• 单列索引
• 多列索引
• 空间索引

三 ,索引的使用语法

例子:我们用一个存储过程先往一张表中多插入点数据,等会演示
DELIMITER $$

CREATE
    PROCEDURE testProcedure()
    BEGIN
    
    
	DECLARE i INT DEFAULT 1;
	WHILE (i<20000) DO
	    INSERT INTO mytestdb.test VALUES(i,'aaaa');
	    set i=i+1;
	 END WHILE;   
    END$$

DELIMITER ;
 查看所有存储过程 show procedure status\G  -- \G 排版一下显示格式,看起来好看些
 查看创建某个存储过程的语句 show create procedure testProcedure;
 调用存储过程 call autoinsertdata();  --括号可可不写

创建索引 使用 index 或者 key 关键字

• 方式1:创建表的时候创建索引
• 方式2:表已经创建好了,再去创建索引
• 方式3:表已经创建好了,通过修改 alter table 语句再去创建索引

方式1:语法 
	create table 表名(
			字段名 数据类型 [约束],
			字段名2 数据类型2 [约束],
            [unique|fulltext|spatial] index|key
            [索引名](字段名[(长度)] [ASC|DESC])   -- 注意中括号里面的是可选项
	);
例如:创建普通索引
		create table student(
			sid int,
			sname varchar(32),
			index(sname) -- 给name 字段建立普通索引 注意字段不要带引号
		);
查看建表语句可以看到这个sname 字段,有没有加上索引
show create table student;

例如:创建唯一索引
    create table test(
    	tid int,
    	tname varchar(32),
    	unique index(tname)  -- 创建唯一索引,这列的值不能重复
    );
    
 例如:创建全文索引,一般针对这个字段是 text类型,比如存了一篇文章
 create table test2(
 	tid int,
 	tname varchar(32),
 	tlog text,
 	fulltext index myIndex (tlog)  -- 这个myIndex是你起的索引名,一般省略不写
 );
 
 例如:创建多列索引,给多个字段创建索引
  create table test2(
 	tid int,
 	tname varchar(32),
 	tlog varchar(100),
 	index my_Index (tname,tlog)  -- 给 tname 和 tlog 两个列都建立索引
 );

方式2:创建索引的语法 就是表已经存在我们给表中的某个字段,建立索引
	语法:	create [unique|fulltext|spatial] index 索引名 on 表名 (字段名[(长度)] [ASC|DESC]);

创建普通索引例子
	create index myIndex on student (sname);
创建全文索引例子
	create unique index myIndex on student (sname);
创建多列索引例子
	create index myIndex on student (sname,slog);

方式3:表已经存在,通过使用修改表的语句 alter table 给某个字段建立索引
	语法: alter table 表名 add [unique|fulltext|spatial] index 索引名 on 表名 (字段名[(长度)] [ASC|DESC]);
	
	创建普通索引的例子
		alter table student add index MyIndex(sname);
	创建唯一索引的例子
		alter table student add unique index MyIndex(sname);
	创建多列索引
	alter table student add index MyIndex(sname,slog); 

四,管理索引

查看索引
	show create table 表名\G;
删除索引
	drop index 索引名 on 表名;
测试索引是否提高了效率
	explain select * from student where sid=2000000;
	explain 这条测试语句是看查询优化器 怎么通过select语句去查 并没没有真正去查

五,测试索引的效率,从时间上和查询方式上对比

未加索引索引时 我们查一下,最后面的数据 看下耗时
select * from student where sid=2000000;  
再看一下查询优化器的查询方式
explain select * from student where sid=2000000;

然后我们给 sid 加上索引 再执行 上面两行代码对比一下即可看到加了索引效率会大大提高
这里注意 你的where条件后面使用的字段,加了索引 ,才是去使用索引