Mysql 之索引，及如何生成百万数据

索引 ：通俗点讲就是，如果没索引去查数据，就要从头开始，如果有索引，就相当于数组的下标一样，直接定位到那，查询速度就能够快很多很多倍。

约束（constraint）：一种规范和限制

拿常见的举例。

 primary key :

     唯一标识数据库表中的每条记录；

     主键必须包含唯一的值；

     主键列不能包含 NULL 值；

     每个表都应该有一个主键，并且每个表只能有一个主键。

unique key ：

 唯一性(比如名字不能重复),但可以有空值； 

 主键索引只能有一个、唯一索引可以有多个

index/key:

单纯的索引，并无什么约束

全文索引（FULLText）

快速定位特定数据（百度搜索就是全文索引）

• 在特定的数据库引擎下才有：MyISAM
• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 适合大型数据集

索引的创建：

分为两种，一种是创建表的时候创建。 第二种是创建表后来创建。

第一种: 创建表的时候创建

 注意：key/index 

1.其中 在mysql中 key 和index 是一个意思，都是创建普通索引。

1.主键索引 primary key  2.唯一索引 unique  key  3.普通索引  key/index

create table  tab1(

   id int auto_increment comment '这是主键' primary key ,
   name varchar(20) comment '这是唯一键' unique  ,
   type varchar(5) comment '类型' ,
   number varchar(30) comment '学号',

    key indexName(number)

) engine=InnoDB default charset=utf8;

或者
 

create table  tab1(

   id int auto_increment comment '这是主键'  ,
   name varchar(20) comment '这是唯一键'  ,
   type varchar(5) comment '类型' ,
   number varchar(30) comment '学号',

    primary key (id),
    unique (name),
   index  indexName(number)

) engine=InnoDB default charset=utf8;

或者

create table tab1
(
    id     int auto_increment comment '这是主键'
        primary key,
    name   varchar(20) null comment '这是唯一键',
    type   varchar(5)  null comment '类型',
    number varchar(30) null comment '学号',

    constraint 唯一键名  unique (name, type)  
  // 它跟 unique  唯一键名（name，type）是一样的。 

) engine=InnoDB default charset=utf8;

 第二种：创建表后来创建

create table tab1
(
    id     int auto_increment comment '这是主键'
        primary key,
    name   varchar(20) null comment '这是唯一键',
    type   varchar(5)  null comment '类型',
    number varchar(30) null comment '学号'
) engine=InnoDB default charset=utf8;


create index index_name_type
    on tab1 (name, type);

或者

create table  tab1(

   id int auto_increment comment '这是主键'  primary key ,
   name varchar(20) comment '这是唯一键'  ,
   type varchar(5) comment '类型' ,
   number varchar(30) comment '学号'

);
alter table tab1 add index index_name_type(name,type);

索引删除

当不再需要索引时，可以使用 DROP INDEX 语句或 ALTER TABLE 语句来对索引进行删除。

DROP INDEX <索引名> ON <表名>

alter table 《表名》 drop  index《索引名》;

• DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
• DROP INDEX index_name：表示删除名称为 index_name 的索引。
• DROP FOREIGN KEY fk_symbol：表示删除外键。

alter table  tab1 drop  index index_name_type;


drop  index index_name_type on tab1;

2、如果带有主键的列还有AUTO_INCREMENT属性，需要间接方式去掉。

得先删除auto_increment

modify的作用就是 可以修改一个字段的类型 ，等等
 

alter table tab1 modify id int;

alter table  tab1  drop primary key  ;


其实就是把 id 这个字段，该为 int。然后什么都不加。
你也可以 alter table  tab1 modify  id int comment '这是主键'  not null ;
只要把auto_increment 去掉就好了

联合键

2. constraint 唯一键名(可不写，默认生成第一个，也就是name)  unique (name, type)     我们可以把两个字段约束在一个唯一键，称为联合唯一约束。

3. constraint 主键名（可不写，默认生成第一个，也就是id）  primary key (id,name) 我们可以把两个字段约束在一个主键上 ,称为联合主键。

注意联合起来唯一和单个唯一，联合唯一其中一个字段重复，另一个字段不重复时可以的，但是单个唯一就都不能重复。

如果只是 constraint unique name ;  那么下面这张图第二行数据的name是不可能也为a的。

对于联合索引

对于单个索引查询.

create table  tab1(

   id int auto_increment comment '这是主键'  primary key ,
   name varchar(20) comment '这是唯一键'  ,
   type varchar(5) comment '类型' ,
   number varchar(30) comment '学号',

   index indexName(number);

);
insert into tab1(name, type, number) values ('a','1','123456'),('b','2','55555'),('c','3','66666');


 explain select  * from tab1 where  number='55555';

explain  关键字 ，可以解析 该语句的查询级别，即查的快不快，有没有用到索引之类的。

当我执行了  explain select  * from tab1 where  number='55555'; 输出如图。看ref中  const 说明查询速度是非常快的类型了。

对于联合索引 ,我们需要了解（最左前缀原则）

create table  tab1(

   id int auto_increment comment '这是主键'  primary key ,
   name varchar(20) comment '这是唯一键'  ,
   type varchar(5) comment '类型' ,
   number varchar(30) comment '学号',

   index indexName(name,type);

);
insert into tab1(name, type, number) values ('a','1','123456'),('b','2','55555'),('c','3','66666');

先创建表和插入一些数据。并且创建 name和type 的索引 。 注意 顺序，后面会讲 。index indexName(name,type);
 

当执行

explaint select * from tab1 where name='a' ;或者

explaint select * from tab1 where name='a' and type='2' ; 时，速度ref那 都是const的

但是如果执行， 速度就会变成null了，也就是没有用到索引。

explain select  * from tab1 where  type='2';

create index index_name_type on tab1(name,type);



explaint select * from tab1 where type='2' ;

以下性能从好到坏依次是：system  > const > eq_ref > ref  > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

1、system

system：当表仅有一行记录时(系统表)，数据量很少，往往不需要进行磁盘IO，速度非常快。

2、const

const：表示查询时命中 primary key 主键或者 unique 唯一索引，或者被连接的部分是一个常量(const)值。这类扫描效率极高，返回数据量少，速度非常快。

mysql百万条数据的创建

可以先去了解我写的这篇文章。

mysql 之 百万条数据的生成_猫咪老师吃太少的博客-CSDN博客

只有几条数据，确实看不出什么差别，那么现在我们通过mysql的函数，来创建上百万条数据（在这里我只创建一万条，改个数字而已）。

建议先去看上面那篇文章，记得一键三连哦~  。

现在我们在用索引和没有索引来查询，看看区别；

先给name和type构建一个联合索引（你要建单个索引也可以，我在这只是为了验证那个最左前缀）

create index index_name_type  on  tab1(name,type);

分别执行下面这四个语句。 

explain select * from tab1 where name='数据88';

explain select * from tab1 where number='0992a085-be60-11ec-8853-089798a288fb';

explain select * from tab1 where type='1571' ;

explain select * from tab1 where type='1571' and name='数据0';

可以看出，explain select * from tab1 where name='数据88'; 和

explain select * from tab1 where type='1571' and name='数据0';   查询只查询了一行，速度已经常量级别的了。说明运用到了索引， 而 没运用到索引的，直接查了九千多行。

那么索引底层到底运用了什么数据结构和算法呢？ 答案是B+ tree  。

想要去了解底层结构的，推荐看这篇文章。

MySQL中B+Tree，从二叉树、平衡二叉树、B-Tree讲起