MySQL索引

一、索引的作用

1.索引通俗解释:索引相当于书的目录,根据条件查询时,没有索引,就会进行全表扫描,对于百万上千万的数据,速度慢得让人窒息,一条SQL可能就需要5秒以上(此表一千万条记录)。

image.png

2.索引的原理:根据索引指向的页以及记录在页中的位置，迅速地读取目标页进而获取目标记录。底层数据结构:B树(大多数)、R-树、Hash。

3.索引的优缺点:

优点

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点

1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

二、MySQL索引类型

5种:主键索引、普通索引、唯一索引、全文索引、聚合索引（多列索引）。

1)单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了。

1-1)普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

1-2)唯一索引：索引列中的值必须是唯一的，但是允许为空值，

1-3)主键索引：是一种特殊的唯一索引，不允许有空值。

2)组合索引:在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

3)全文索引:全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"呵呵呵嗯嘻嘻" 通过嘻嘻，可能！就可以找到该条记录。

常用3种:主键索引、普通索引、聚合索引(多列索引)

1）主键索引：主键索引是加在主键上的索引，设置主键（primary key）的时候，mysql会自动创建主键索引；

2）普通索引：创建在非主键列上的索引；

3）聚合索引：创建在多列上的索引。

三、索引语法:增删查

查看某张表的索引：SHOW INDEX FROM 表名；

创建普通索引：ALTER TABLE 表名 ADD INDEX 索引名 (加索引的列)

创建聚合索引：ALTER TABLE 表名 ADD INDEX 索引名 (加索引的列1,加索引的列2)

删除某张表的索引：DROP INDEX 索引名 ON 表名;

四、建个表、插点数据来直观感受索引的好处吧！

MyISAM

1)建表语句

CREATE TABLE `test_user` (  
    `id` bigint(20)  PRIMARY key not null AUTO_INCREMENT,  
    `username` varchar(50) DEFAULT NULL,  
    `email` varchar(30) DEFAULT NULL,  
    `password` varchar(32) DEFAULT NULL,
    `status`  tinyint(1) NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8; 

2)使用存储过程插入1千万条语句

create procedure myproc()
begin   
    declare num int;   
    set num=1;   
    while num <= 10000000 do   
        insert into test_user(username,email,password) values(CONCAT('username_',num), CONCAT(num ,'@qq.com'), MD5(num));   
        set num=num+1;  
    end while;  
end

3)call myproc();

4)主键索引测试

image.png

5)普通索引测试

5-1)未建普通索引

image.png

5-2)给username列加上普通索引

ALTER TABLE `test_user` ADD INDEX index_name(username) ;

image.png

.frm 表结构文件
.myd 表数据文件
.myi 表索引文件

5-2-1)再跑一遍SQL

image.png

速度提升地非常可观！

5-2-2)再加一个条件

image.png

sql优化器优先用索引命中。

5-2-3)使用OR条件

image.png

对 username 加了索引，但是password列未加索引，索引执行password筛选的时候，还是会全表扫描，因此此时查询速度下降。

6)聚合索引测试(sql有多个列的筛选条件的时候使用聚合索引)

6-1)加聚合索引

ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)

image.png

emmm...建索引可真久

6-2)再把上面这条SQL跑一次和跑多一条用AND语句的SQL

image.png image.png

事实证明一个结论:使用 OR 语句不启用聚合索引，也没使用到索引username,AND 语句才使用到了聚合索引，聚合索引必须使用AND条件。

6-3)最左原则补充+索引生效验证

最左原则--最左前缀：顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

image.png
---转自https://blog.csdn.net/SkySuperWL/article/details/52583579

6-3-1)新建A、B、C聚合索引

ALTER TABLE `test_user` ADD INDEX index_union_name_email_password(username,email,password)

6-3-2)A、B、C

image.png

6-3-3)A、B

image.png

6-3-3)B、C

image.png

总结:
1.要使用覆盖索引必须都是 AND 条件,注意使用慎用 OR 条件。
2.要使用覆盖索引如ABC，需满足条件语句中有 A、AB、ABC才会使用覆盖索引，采用最左原则。

InnoDB

1)重复上面建表语句

CREATE TABLE `test_user_innodb` (  
    `id` bigint(20)  PRIMARY key not null AUTO_INCREMENT,  
    `username` varchar(50) DEFAULT NULL,  
    `email` varchar(30) DEFAULT NULL,  
    `password` varchar(32) DEFAULT NULL,
    `status`  tinyint(1) NULL DEFAULT 0
) ENGINE=INNODB DEFAULT CHARSET=utf8; 

未完待续....