MySQL的索引-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_62520626/article/details/123092320

一 ,索引简介

索引在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();  --括号可可不写
 
 call testProcedure();

创建索引使用 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：

方式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：

方式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);
         
         
         alter table test add index MyIndex(id);

四,管理索引

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

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

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

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


注意：你在建表的时候，如果有主键，这个主键，就带有所有主键索引。