mysql之索引详解

一、索引

1.什么是索引

1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2）让获取的数据更有目的性，从而提高数据库检索数据的性能。

索引类型介绍

1)BTREE:B+树索引（Btree，B+tree，B*tree）
2)HASH：HASH索引 （将数据打散再去查询，Inodb和Myisam都不支持，设置完还是Btree，memery存储引擎支持）
3)FULLTEXT：全文索引 （只可以用在MyISAM引擎）
通过关键字的匹配来进行查询，类似于like的模糊匹配，like + %在文本比较少时是合适的，但是对于大量的文本数据检索会非常的慢，全文索引在大量的数据面前能比like快得多，但是准确度很低，百度在搜索文章的时候使用的就是全文索引，但更有可能是ES，
4)RTREE：R树索引
RTREE在mysql很少使用，仅支持geometry数据类型，geometry数据类型一般填写经纬度那样的数据，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。RTREE范围查找很强，但Btree也不弱.
生活中很多算法，电梯算法，二分法

b+树：
在二叉树、平衡二叉树、b树的基础上做了进一步优化
只有叶子结点放真正的数据，这意味着在等量数据的前提下，B+树的高度是二叉树、平衡二叉树、b树中最低的，而树的高度越低查询速度越快，查询步数越少
b+树的性质：
1、索引字段要尽量的小，
2、索引的最左匹配特性（B+树是按照从筛选条件左至右的顺序来搜索树的，若条件中第一个命令不在索引中，那么B+树就不知道下一个节点该去哪查）

2、B+树的叶子节点都是排好序的，这意味着在范围查询上，B+树比b树更快，快就快在一旦找到了一个树叶节点，就不需要再从树根查起了

3.索引根据算法分类

索引建立在表的列上(字段)的。
在where后面的列建立索引才会加快查询速度。
pages<—索引（属性）<----查数据。

1）主键索引(聚集索引)

#创建主键索引
mysql> alter table student add primary key pri_id(id);
mysql> create table student(id int not null, primary key(id)); 
mysql> create table student(id int not null primary key auto_increment comment '学号');

#提示：
database可以写 schema
index可以写 key

#查看索引
mysql> show index from student;

2）唯一键索引

#创建唯一建索引

mysql> alter table country add unique key uni_name(name);
mysql> create table student(id int unique key comment '学号');
mysql> create unique key index index_name on table_name(id);

扩展

#数据库修改某一列为唯一键的时候，那一列的数据不能有重复数据
#所以可以使用函数计算那一列是否有重复数据

#数据条数 类似于 wc -l
mysql> select count(name) from qiudao3;

#数据去重 类似于 uniq -c
mysql> select distinct(name) from qiudao3;

#去重之后计数，当值与没去重条数一致时可以为该列加唯一键
mysql> select count(distinct(name)) from qiudao3;

#city表name列不能做唯一键或主键
mysql> select count(name) from city;
mysql> select distinct(name) from city;

#country表name列能做唯一键或主键
mysql> select count(Name) from country;
mysql> select distinct(Name) from country;

函数总结

count()			#统计
password()		#加密
distinct()		#去重
now()			#当前时间
database()		#当前数据库

max()			#最大值
mysql> select max(population) from country;

min()			#最小值
mysql> select min(population) from country;

sum()			#求和
mysql> select sum(population) from country;

avg()			#求平均值
mysql> select avg(population) from country;

3）普通索引(辅助索引)

#普通索引的创建
mysql> alter table student add index idx_gender(gender);
CREATE INDEX index_name ON table_name (column_list);

4）全文索引

#针对content做了全文索引：
CREATE TABLE pull (
id int NOT NULL AUTO_INCREMENT,
title char(255) NOT NULL,
content text,
PRIMARY KEY (id),
FULLTEXT (content));

#查找时：
mysql> select * from pull where match(content) against('查找的字符串');

5）查看索引

#方法一：
1.mysql> desc student;
+-----+
| Key |
+-----+
| PRI |		主键索引
| MUL |		普通索引
| UNI |		唯一键索引	
+-----+

#方法二：
2.mysql> show index from student;

6）删除索引

mysql> alter table country drop index uni_name;

4.索引根据创建方式分类

1.在创建索引的时候，会把该列所有的数据按照btree的方式进行排序.
2.创建索引,会占用磁盘空间，所以不要每列都创建索引
3.在同一列上，尽量避免创建多个索引，可以创建但要有优先级，先走哪个就不会再走另一个索引了；
alter table student add index idx_name(name);
alter table country add unique key uni_name(name);
4.避免对大列建索引，在数据很长的列上创建前缀索引

1）前缀索引

#建表
create table student(
sid int not null primary key auto_increment comment ‘学号’,
sname varchar(10) not null comment ‘姓名’,
sage tinyint unsigned not null comment ‘年龄’,
sgender enum(‘f’,‘m’) not null default ‘m’ comment ‘性别’,
scometime datetime default now() comment ‘入学时间’,
sbirthday datetime comment ‘学生生日’,
class varchar(10) not null comment ‘学生班级’);

#插入数据
mysql> insert into oldtian5(name,age,gender) values(‘alixkailiangle’,18,‘m’),(’’,84,‘f’);

#创建前缀索引
按照该列数据的前n个字母创建索引
mysql> alter table student add index idx_name(name(4));

2）联合索引

假如是相亲网站，肯定有多个条件，性别，身高，长相等等
根据访问次数或者喜好创建联合索引

#例子：
1.创建一个库
create database xiangqin;
use xiangqin

2.创建一个表
create table people (id int, name varchar(20), age tinyint, money int, gender enum(‘m’,‘f’), hight int, weight int, figure varchar(10), looks varchar(10));

3.创建联合索引
alter table people add index idx_all(gender,money,figure,looks);

4.查看索引
show index from people;

5.插入多条数据
insert into people values(1,‘fbb’,18,800000000,‘f’,170,90,‘good’,‘beautiful’);
insert into people values(2,‘邱导’,66,80,‘m’,150,190,‘absurd’,‘ugly’);

#查询的时候有的时候走索引，有的时候不走索引如下：
一定走索引
mysql> select * from people where gender=？ money=？ figure=？ looks=？

一定不走索引
mysql> select * from people where money=？ gender=？ looks=？ figure=？

部分走索引
mysql> select * from people where gender=？ money=？ looks=？ figure=？

二、explain使用

之前总说到查询速度、查询效率，那使用什么去查看查询的快慢呢，就用explain

1.explain用法
我们之前查询过SQL

#1.查询中国和美国的城市 or 和 in

mysql> select * from world.city where countrycode='CHN' or countrycode='USA';
   mysql> select * from world.city where countrycode in ('CHN','USA');

#2.union all (联合查询) 讲索引的时候再说
mysql> select * from world.city where countrycode=‘USA’ union all select * from world.city where countrycode=‘CHN’;

#3.explain用法

 explain select * from city where countrycode='USA' or countrycode='CHN';
   explain select * from city where countrycode in ('USA','CHN');
   explain select * from world.city where countrycode='USA' union all select * from world.city where countrycode='CHN';

2.explain结果注解
mysql> explain select id from student where id=2;

#ID：
id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
#type：
查询使用了哪种类型
#possible_keys：
可能应用在这张表中的索引，一个或多个
#key：
实际使用的索引，如果为NULL，则没有使用索引
#key_len （可以使用前缀索引控制）
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。
#ref
级别是否在ref之上
#rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
#Extra
Using filesort：使用order by时
Using temporary：使用排序order by和分组查询group by时

2.查询数据种类

1）全表扫描

#1.什么是全表扫描
在explain语句结果中type为ALL，MySQL将遍历全表以找到匹配的行

#2.什么时候出现全表扫描?
1.业务确实要获取所有数据
mysql> explain select * from city;

2.不走索引导致的全表扫描
2.1 没索引
mysql> explain select * from city where District=‘shanghai’;
#创建索引后速度会提升，变成索引扫描了
mysql> alter table city add index idx_District(District);
mysql> explain select * from city where District=‘shanghai’;
2.2 索引创建有问题
2.3 语句有问题

2）索引扫描

#常见的索引扫描类型:
1）index 全索引扫描，index与ALL区别为index类型只遍历索引树（性能也不高）
mysql> explain select population from city;

2）range 范围查询，只检索给定范围的行，使用一个索引来选择行，一般来说，SQL语句只要达到range级别，就可以了
mysql> explain select * from city where countrycode in (‘USA’,‘CHN’);

3）ref 精确查找，匹配条件有普通索引
#先给population列一个索引
mysql> alter table city add index idx_population(population);
#然后查询
mysql> explain select * from city where population=300000000;

4）eq_ref 类似ref，区别就在使用的索引是唯一索引
explain select city.name,city.countrycode,country.name from city join country on city.countrycode=country.code where city.population<100;

5）const 使用的索引是主键索引
mysql> explain select * from country where code=‘CHN’;

6）system system是const类型的特例，当查询的表只有一行的情况下
mysql> delete from country where code != ‘CHN’;
#有外键删除不了

7）null 执行过程不用访问表或索引，直接选取索引列的最小值
mysql> explain select min(population) from city;

扩展：主键与外键

一个表里面的数据是由多个表中的主键组成的字段，那么在这个表中的字段称为其他表的外键；

一旦将所设计的数据库用于了生产环境，就很难对这些外键进行修改，所以在开发阶段就需要设计好主键和外键，外键一旦使用就不能更改

1.如果一个表里有两个外键字段指向两张表，那么每次往表里插入数据，就必须往两个外键对应的表里查询是否有对应数据。如果交由程序控制，这种查询过程就可以控制在我们手里，可以省略一些不必要的查询过程。但是如果由数据库控制，则是必须要去这两张表里判断。
2.若是在高并发大流量事务场景，使用外键更容易造成死锁。
3.迁移数据库或者分库分表的时候，外键很可能出现无法生效的问题。
4.如果没有专业DBA是很难操作外键的

主键 外键 索引
定义： 唯一标识一条记录，不能有重复的，不允许为空 表的外键是另一表的主键, 外键可以有重复的, 可以是空值 该字段没有重复值，但可以有一个空值
作用： 用来保证数据完整性 用来和其他表建立联系用的，保证表间数据的关系“始终完整” 是提高查询排序的速度
个数： 主键只能有一个 一个表可以有多个外键 一个表可以有多个惟一索引

三、索引建立

1.建立索引的原则

主键只能使用一个
但是使用第三方工具可以设置多个主键，可以创建测试，查看
但是第三方设置的主键还是可以重复插入，他相当于对一个主键建立了联合索引，没有任何意义，很坑

1.如果可以创建唯一索引,就创建唯一索引(该列的数据不重复)，查询速度快
#数据去重
mysql> select distinct(name) from qiudao3;
#去重之后计数，当值与没去重条数一致时可以为该列加唯一键
mysql> select count(distinct(name)) from qiudao3;*

2.为经常要排序,分组,联合操作的列,创建联合索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作

3.为常作为查询条件的字段建立索引

4.尽量使用前缀来索引
创建索引的时候,要给该列所有数据进行排序

5.限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

6．删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

2.什么时候不走索引？
1）没有查询条件，或者查询条件没有建立索引

#没有条件的情况
mysql> explain select * from world.city;

mysql> explain select * from world.city where 1=1;

#没建立索引的列做条件
mysql> explain select * from world.city where name=‘shanghai’;

2）查询结果集是原表中的大部分数据，应该是25％以上
#如果生产中,必须有这种全表扫描的需求不走索引
mysql> explain select * from world.city where population > 50;

#如果业务允许，可以使用limit控制，可以走索引
mysql> explain select * from world.city where population>50 limit 10;

#如果业务不允许，可以使用缓存
前面加上缓存,memcached,redis

3）索引本身失效，统计数据不真实
反复修改，插入数据，索引被修改坏了，每次都会进行排序

4）查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
mysql> explain select * from world.city where id=9;

#查询ID等于9的数据，会导致不走索引
mysql> explain select * from world.city where id-1=8;

#查询ID等于9的数据，可以在后面作加减
mysql> explain select * from world.city where id=8+1;

5）隐式转换,会导致索引失效
#创建一个表
create table test(id int, name varchar(20), phonenum varchar(10));

#插入几条数据
insert into test values(1,‘jc’,‘110’),(2,‘xf’,119),(3,‘jh’,120);

#建立索引，没有相同值可以给唯一索引
alter table test unique key idx_num(phonenum);

#查看索引
show index from test;

#查询语句级别
不走索引
explain select * from test where phonenum=110;

走索引
explain select * from test where phonenum=‘110’; #引号可能导致大事故
因为这一列是varchar类型，必须以字符来查询

6）<> 和 not in , or 也不走索引

   mysql> explain select * from test where phonenum <> '120';
   
   mysql> explain select * from test where phonenum not in (120);
   
   mysql> explain select * from test where telnum='110' or telnum='119';

#使用union all可以走索引
mysql> explain select * from test where telnum=‘110’ union all select * from test where telnum=‘119’;

7）like模糊查询%在最前面,不走索引
#%在前面不走索引
mysql> explain select * from city where countrycode like ‘%HN’;

#%在后面，走索引
mysql> explain select * from city where countrycode like ‘CH%’;

#放在后面也不是一定了，因为涉及到第二点结果的占总数据的比例

#%在最前面的搜索需求，建议使用elasticsearch ES ELK(E) 搜索引擎式的 数据库

8）单独引用联合索引里非第一位置的索引列

聚集索引、聚簇索引、主键索引
1、一张表中必须有且只有一个
2、聚集索引叶子节点key所对应的value值是整行完整内容

辅助索引、二级索引、非聚簇索引
1、一张表中可以有多个
2、辅助索引叶子节点key所对应的value值是该所对应的主键字段值

覆盖了索引
在命中了索引的前提下，在本索引树的叶子节点就找到了自己想要的全部数据
id----->主键索引
name—>辅助索引
select * from user where id =3;
select name,id from user where name=“egon”;

回表操作
在命中了辅助索引的前提下，在叶子节点没有找到想要的完整数据，需要拿着对应的主键字段值去聚集索引里再查询一次

如何正确使用索引：
1、以什么字段的值为基础构建索引
最好是不为空、唯一、占用空间小的字段

2、针对全表查询语句如何优化？
应用场景：用户想要浏览所有的商品信息
select count(id) from s1;
如何优化：
开发层面分页查找，分页展示，用户每次看现从数据中拿

3、针对等值查询
以重复度低字段为基础创建索引加速效果明显
create index xxx on s1(id);
select count(id) from s1 where id = 33;

以重复度高字段为基础创建索引加速效果不明显
create index yyy on s1(name);
select count(id) from s1 where name=“egon”; – 速度极慢
select count(id) from s1 where name!=“egon”; – 速度快
select count(id) from s1 where name=“mm”; – 速度快

以占用空间大字段为基础创建索引加速效果不明显

总结：给重复低、且占用空间小的字段值为基础构建索引

4、关于范围查询
select count(id) from s1 where id=33;
select count(id) from s1 where id>33;
select count(id) from s1 where id>33 and id < 36;
select count(id) from s1 where id>33 and id < 1000000
总结：innodb存储能加速范围查询，但是查询范围不能特别大

5、关于条件字段参与运算
select count(id) from s1 where id*12 = 10000;
select count(id) from s1 where id = 10000/12;
select count(id) from s1 where func(id) = 10000/12;

总结：不要让条件字段参与运算，或者说传递给函数