百木园索引的目的在于提高查询效率
一 索引分类
1、普通索引 index
加速查询
2、唯一索引
2.1、主键索引 primary key
加速查询+约束（不为空且唯一）
2.2、唯一索引 unique
加速查询+约束（唯一）
3、联合索引
-- index(id,name) 联合普通索引
-- primary key(id,name) 联合主键索引
-- unique(id,name) 联合唯一索引
4、全文索引 fulltext
用于搜索很长文章的时候效果最好。
5、空间索引 spatial
二 索引类型
# 我们可以在创建索引的时候，为其指定索引类型，分两类
1、hash类型
查询单条快，范围查询慢
2、btree类型 B+树
b+树，层级越多，数据量指数级增长
#不同的存储引擎支持的索引类型也不一样
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 TABLE_NAME(
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 TABLE_NAME(name); # 添加普通索引
create unique age on TABLE_NAME(age); # 添加唯一索引
alter table TABLE_NAME add primary key(id); # 添加主键索引，也就是给id字段增减一个主键约束
create index name on TABLE_NAME(id,name); # 添加普通联合索引
2、删除索引
drop index name on TABLE_NAME; # 删除普通索引
drop index age on TABLE_NAME; # 删除唯一索引，就和普通索引一样，不用在index前加unique就可以删除
alter table TABLE_NAME drop promary key; # 删除主键（因为它添加的时候是按照alter来增加的，那么我们也用alter来删）
四 测试索引
1、准备表 create table TABLE_NAME( 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();
# 准备测试表
create table TEST_TABLE_SUOYIN(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
# 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into TEST_TABLE_SUOYIN values(i,concat(\'egon\',i),\'male\',concat(\'egon\',i,\'@oldboy\'));
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
# 查看存储过程
show create procedure auto_insert1;
# 调用存储过程
call auto_insert1();
# 未添加索引
select * from TEST_TABLE_SUOYIN where id = 1000; # 1.287s
select * from TEST_TABLE_SUOYIN where id BETWEEN 1000 and 100000; # 1.306s
select * from TEST_TABLE_SUOYIN where id>= 1000 and id <= 10000; # 1.337s
select * from TEST_TABLE_SUOYIN where name = \'egon1000\'; # 1.392s
select * from TEST_TABLE_SUOYIN where id = 12000 and name = \'egon12000\'; # 1.292s
select * from TEST_TABLE_SUOYIN where email = \'egon10002@oldboy\'; # 1.424s
select * from TEST_TABLE_SUOYIN where gender = \'male\' and email = \'egon10002@oldboy\'; # 1.628s
# 添加主键索引
alter table TEST_TABLE_SUOYIN add primary key(id);
# 添加普通索引 的两种方式
create index name on TEST_TABLE_SUOYIN(name);
alter table TEST_TABLE_SUOYIN add index(gender);
# 添加组合索引 的两种方式
create index i_id_name on TEST_TABLE_SUOYIN(id, name);
alter table TEST_TABLE_SUOYIN add index(name, gender, email);
# 添加唯一索引
alter table TEST_TABLE_SUOYIN add unique(email);
# 已添加索引
select id, name, gender, email from TEST_TABLE_SUOYIN where id = 1000; # 0.008s
select id, name, gender, email from TEST_TABLE_SUOYIN where id BETWEEN 1000 and 100000; # 0.021s
select id, name, gender, email from TEST_TABLE_SUOYIN where id>= 1000 and id <= 10000; # 0.007s
select id, name, gender, email from TEST_TABLE_SUOYIN where name = \'egon1000\'; # 0.008s
select id, name, gender, email from TEST_TABLE_SUOYIN where id = 12000 and name = \'egon12000\'; # 0.007s
select id, name, gender, email from TEST_TABLE_SUOYIN where email = \'egon10002@oldboy\'; # 0.007s
# index(name, gender, email) 仅添加这个组合索引
select id, name, gender, email from TEST_TABLE_SUOYIN where gender = \'male\' and email = \'egon10002@oldboy\'; # 0.000s 索引失效 未遵守最左前缀匹配原则
select id, name, gender, email from TEST_TABLE_SUOYIN where gender = \'male\' and email = \'egon10002@oldboy\' and name = \'egon10002\'; # 0.001s 索引有效 遵守最左前缀匹配原则
# 索引无法命中的情况 %模糊查询
# 所以得出结论 %加在前面所以无法命中 只能加在后面才能够命中
select * from TEST_TABLE_SUOYIN where email like \'%on10000@oldboy\'; # 1.925s
select * from TEST_TABLE_SUOYIN where email like \'%on10000@%\'; # 1.940s
select * from TEST_TABLE_SUOYIN where email like \'egon10000@%\'; # 0.008s
# 索引无法命中的情况 使用函数
select * from TEST_TABLE_SUOYIN where REVERSE(email) = \'yobdlo@00001noge\'; # 1.840s
# 索引无法命中的情况 or
# 在测试这个情况时 我删除了 gender 的索引；特别在于 or 前后的列有未添加索引的 索引才会失效；如果前后两个列都有索引，则索引生效
select * from TEST_TABLE_SUOYIN where id = 1000000 or gender = \'sss\'; # 索引未命中
select * from TEST_TABLE_SUOYIN where id = 1000000 or email = \'egon1000000@oldboy\'; # 索引命中
# 索引无法命中的情况 类型不一致
# email的类型是字符串，如果查询的值类型不是字符串则不会命中索引
select * from TEST_TABLE_SUOYIN where email = 123; # 2.133s
select * from TEST_TABLE_SUOYIN where email = \'egon1000000@oldboy\'; # 0.008s
# 索引无法命中的情况 !=
# 因为数据量太大 所以无法直观的测出来
select * from TEST_TABLE_SUOYIN where id != 1; # 文章上说：主键的 != 会走索引
select * from TEST_TABLE_SUOYIN where email != \'egon1@oldboy\'; # 反之则不会
# 索引无法命中的情况 > <
# 文章上说：主键或索引是整数类型的可以命中索引，但是实测字符也是可以命中的
select * from TEST_TABLE_SUOYIN where id > 2999999; # 0.002s
select * from TEST_TABLE_SUOYIN where id < 2; # 0.006s
select * from TEST_TABLE_SUOYIN where email > \'xxxx\'; # 0.001s
# 索引无法命中的情况 ORDER BY
# 从第一次查询出来的结果看 查询 gender 的时间明显比 id 要久，所以 order by的条件列有索引 查询结果列 也需要有索引 才会命中索引
# 但是如果对主键排序 则不看 结果列 是否有索引 也会命中索引
select gender from TEST_TABLE_SUOYIN where id BETWEEN 1000 and 10000 order by name DESC; # 0.020s
select id from TEST_TABLE_SUOYIN where id BETWEEN 1000 and 10000 order by name DESC; # 0.005s
# 索引无法命中的情况 最左前缀原则（组合索引）
# 比如 添加 index(a,b,c,d)这个组合索引 以下例子是否命中索引
select * from dual where c = 1 and d = 1 and b = 1 and a = 1; # 命中索引
select * from dual where c = 1 and d = 1 and b = 1; # 未命中索引 因为（a不在查询条件中，且a在组合索引种的第一位，所以之后的索引都不会命中）
SELECT * from dual where a = 1 and c = 1 and d = 1; # 仅只有a命中索引（b不在查询条件中，且b在组合索引种的第二位，所以之后的索引都不会命中）
# 索引无法命中的情况 count(?)
# 文章中说count(1) | count(列名) 代替 count(*) 这样会命中索引 但是实测下来 没有区别；
select count(*) from TEST_TABLE_SUOYIN; # 1.732s
select count(1) from TEST_TABLE_SUOYIN; # 2.060s
select count(id) from TEST_TABLE_SUOYIN; # 1.778s
# 索引无法命中的情况 添加索引时 如果列类型时text类型的，必须制定长度
create index index_name on table_name(title(19)) #text类型，必须制定长度
# 删除主键索引
alter table TEST_TABLE_SUOYIN drop PRIMARY key;
# 删除普通索引
drop index name on TEST_TABLE_SUOYIN;
drop index gender on TEST_TABLE_SUOYIN;
# 删除唯一索引
drop index email on TEST_TABLE_SUOYIN;
# 删除组合索引
drop index i_id_name on TEST_TABLE_SUOYIN;
drop index name_2 on TEST_TABLE_SUOYIN;
来源：https://www.cnblogs.com/ganguixu/p/15798198.html
图文来源于网络，如有侵权请联系删除。