mysql中的索引_存储过程大量数据索引-CSDN博客

本文链接：https://blog.csdn.net/nanruitao10/article/details/84104176

为什么需要索

当数据库表中数据越来越大时，检索数据是非常耗时的。这是就可以使用索引来提高查询数据的速度，索引就类似于字典的目录项，可以将查询的速度提高好几个数量级别。

索引的实现原理

索引的目的就是提高查询效率，通过不断低缩小范围最终得到想要的结果。mysql的索引的实现基础是B+Tree,关于B+Tree的特点如下：

是多叉树了,同时每个节点可以包含更多的节点，这样做有两个原因,一个是降低树的高度。另外一个是将数据范围变为多个区间，区间越多，数据检索越快。
每个节点不再只是存储一个key了，可以存储多个key；
非叶子节点存储key，叶子节点存储key和数据。
叶子节点两两相连，为顺序查询提供了帮助。

下面一个例子理解：

如上图，每个节点可以存储多个key，当需要查找28时，在第一层查找时，发现28在20~40之间，根据指针到第二层查询，发现在25~30之间，再根据指针到下层查找，即找到了28所对应的值。在mysql中根据操作系统的局部预读的特性，再一次io中就可以查询相邻的数据，这样能提高查询的效率，如上图在读取到26~28的内容的同时根据块指针也读取32~34的内容。mysql中的索引远比这复杂。通过以上的小列子，你可能感觉不到索引的用处，假设有1百万条数据，如果没有索引，那么查询数据就有可能参数1百万次的IO操作，但是有索引则数据所在块进行IO读取，有可能就只有3次IO操作，这样效率就会大大提高。

索引的类型

那么来看看索引有哪些类型？

普通索引 index 普通的列
主键索引 primary key 值不能为空
唯一索引 unique 值可以为空
组合索引：普通联合索引 index(id,name)、主键联合索引 primary key(id,name)、唯一联合索引unique（id,name）
全文索引 fulltext,适用于搜索长字符串时

索引的两大类型

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 等索引；

索引的创建与使用

创建索引可以在创建表时添加，也可在已有表上创建

1.创建表时添加索引

　CREATE TABLE 表名 (
                  字段名1  数据类型 [完整性约束条件…],
                  字段名2  数据类型 [完整性约束条件…],
                  [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                  [索引名]  (字段名[(长度)]  [ASC |DESC]) 
                 );

2.存在表中添加索引

CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 ON 表名 (字段名[(长度)]  [ASC |DESC]) ;

 ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)]  [ASC |DESC]) ;

3.删除索引

DROP INDEX 索引名 ON 表名称;

ALTER 表名称 DROP INDEX 索引名；

假设你不知道怎么建索引，也不要着急，你可以在mysql服务端使用 help create index命令，就能列出示例。

实测索引

1.准备测试表

create table test(
    id int,
    name varchar(20),
    gender char(4)
);

2.创建存储过程添加数据

delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into test values(i,concat('test',i),'male');
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#调用存储过程
call auto_insert();

3.查询数据看耗时

不加索引：

加索引：

create index id_index on test(id);

对比时间可以发现，加索引比无索引的耗时整整提高了263倍，极大的提高查询效率。

索引使用的原则

为数据表添加索引虽然能够提高查询速度，但是也不是随便添加，需要遵循以下原则：

常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
索引列不能参与计算，这是因为b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大；
最左前缀匹配，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整；
主键自动建立唯一索引；
作为排序的列要建立索引；
高并发条件下倾向组合索引；
经常增删改的列不要建立索引；
有大量重复的列不建立索引；
表记录太少不要建立索引；
索引的长度越小越好，如int

索引失效的情况：

在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的；
在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了；
LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引；
在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。
在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
在查询条件中使用<>会导致索引失效。
在查询条件中使用IS NULL会导致索引失效。
在查询条件中使用OR连接多个条件会导致索引失效，这时应该改为两次查询，然后用UNION ALL连接起来。
尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；