mysql索引（本质是数据结构，常用b树）

1.what这个技术是什么

官方文档定义

https://dev.mysql.com/doc/refman/5.7/en/glossary.html 搜索index

index

A data structure that provides a fast lookup capability for rows of a table, typically by forming a tree structure (B-tree)representing all the values of a particular column or set of columns.

总结：索引是数据结构。

对比同类技术的优缺点，适用场景

https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes.

总结：索引常用b树，当然不同的场景，应该选择不同的索引类型。

ps: 索引博大精深，我搜到的有：B树索引、位图索引、散列索引、反转索引、倒排索引。。。

传统数据库多使用B树作为索引结构。而在大数据场景下，hbase、kudu等存储引擎选择LSM树

（这个太高深了，很多种，后续有空说）

此技术的架构组成

每一种索引都可以各自写一篇文章了。后续补充这个位置。

 

2.why为什么有这个技术

此技术解决什么问题

1.查找稳定和快

2.如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

如果是我，怎么解决这个问题

这个我也不知道怎么答。有人知道怎么答吗？

 

3.how怎么学这个技术

quickstart

善用帮助文档
help create
help create index
==================
1.创建索引
    -在创建表时就创建（需要注意的几点）
    create table s1(
    id int ,#可以在这加primary key
    #id int index #不可以这样加索引，因为index只是索引，没有约束一说，
    #不能像主键，还有唯一约束一样，在定义字段的时候加索引
    name char(20),
    age int,
    email varchar(30)
    #primary key(id) #也可以在这加
    index(id) #可以这样加
    );
    -在创建表后在创建
    create index name on s1(name); #添加普通索引
    create unique age on s1(age);添加唯一索引
    alter table s1 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束
    create index name on s1(id,name); #添加普通联合索引
2.删除索引
    drop index id on s1;
    drop index name on s1; #删除普通索引
    drop index age on s1; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
    alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

demo

1、准备

#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,concat('egon',i),'male',concat('egon',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1();

 

2 、在没有索引的前提下测试查询速度

#无索引：从头到尾扫描一遍，所以查询速度很慢
mysql> select * from s1 where id=333;
+------+---------+--------+----------------+
| id   | name    | gender | email          |
+------+---------+--------+----------------+
|  333 | egon333 | male   | 333@oldboy.com |
|  333 | egon333 | f      | alex333@oldboy |
|  333 | egon333 | f      | alex333@oldboy |
+------+---------+--------+----------------+
rows in set (0.32 sec)

mysql> select * from s1 where email='egon333@oldboy';
....
... rows in set (0.36 sec)

3、 加上索引

 

#1. 一定是为搜索条件的字段创建索引，比如select * from t1 where age > 5;就需要为age加上索引

#2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，插入删除更新都很慢，只有查询快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了

#3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI