MySQL索引

菜鸡自用

什么是索引

 索引的通俗解释

　　索引就像是图书的目录，根据目录中的页码快速找到所需内容

简单理解来说，索引就是一种能够帮助MySQL高效获取数据的一种有序数据结构，这种数据结构以某种引用的方式指向数据，可以通过查找算法查找。

索引的优缺点

优点：索引能够提高数据检索的效率，降低数据库的IO成本

           通过索引列对数据进行排序，降低数据排序的成本，降低数据库排序的cpu成本消耗

缺点：索引需要占用空间

           索引大大提高了查询的速度，但对增删改的语句会有影响

索引的结构

比如我们需要进行一次查找select * from user where age = 10

如果不使用数据结构，那么就会全表扫描，压力过大。 

那我们考虑使用二叉树排序树BST，类似这样

 但是最坏情况下就会出现到这种情况

顺序插入时，会形成一个链表，查询性能大大降低，大数据量下，层级较多，速度很慢

 问题仍然没有得到解决。

那我们考虑使用平衡二叉树AVL，防止节点过深

但是插入的次数过多还是不行 

那我们考虑使用红黑树

 这种情况下，层级较深，检索速度慢

 那我们可以考虑使用BTREE（多路平衡查找树）去控制层级_

以一颗最大度数为5阶的b-tree为例每个节点最多存储4个key，5个指针

1

演示地址：

B-Tree Visualization (usfca.edu)

B-TREE

B+TREE 

Hash索引

Hash索引只能用于对等比较，不支持范围查询

无法利用索引完成排序操作

查询效率高，通常止血药一次检索就休息，前提是不出现hash盆装，效率通常高于B+TREEs索引

存储引擎支持：

在MySQL中，支持hash索引的是Memory引擎，而INNODB中hash索引是B+TREE在指定条件下自动构建。

总结

 索引分类

 聚集索引选取规则

        存在主键：主键索引就是聚集索引

        不存在主键：第一个唯一（UNIQUE）索引作为聚集索引

        如果没有主键，或者没有合适的唯一索引，则innodb会自动生成一个rowid作为隐藏的聚集索引

索引过程

  索引语法

#查看索引
show index from 表名

show index from t_user;

#创建索引

普通索引
create index idx_表名——字段名 on 表名(字段名);

唯一索引
create unique index idx_表名——字段名 on 表名(字段名);


#联合索引
create index idx_表名_字段名1_字段名2 on 表名（字段1， 字段2);


#删除索引
drop index idx_表名——字段名 on 表名

SQL性能分析

1.查看执行频次

show GLOBAL STATUS like "com_______"

 2.慢查询日志（linux）

慢查询日志记录了所有执行时间超过指定参数(log_query_time)，单位秒，默认10秒，的所有SQL语句的日志，MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件下（/etc/my.cnf）中配置如下

vi /etc/my.cnf设置

#开始MySQL慢查询日志开关

slow_query_log = 1

#设置慢查询日志时间为2秒，sql执行时间超过两秒，就会是慢查询

long_query_time = 2

重启mysql systemctl restart mysqld

慢查询日志文件：/var/lib/mysql/localhost-slow.log

 其他就不细说了...

3.profile

#查看profile
select @@profiling;

#设置
set profiling = 1;

执行sql

查看执行时间

#查看每一条SQL耗时的基本情况
show profiles;

#查看执行query_id的SQL语句信息
show profile for query query_id;

#查看执行query_id的CPU使用情况
show profile cpu for query query_id;

4.explain

#演示

普通
select * from user


desc/explain select * from user

结果展示

参数分析

id:select查询的序列号，标识查询中select子句或者操作表的顺序(id顺序从上到下，id不同，值越大，越先执行)

select_type:标识select的类型，常见的取值有simple(简单表，不使用表连接或者子查询)，primary（主查询，外层的查询）union(union中大大第二个或者后面的查询语句)，subquery（select，where之后包含的子查询）

type表示连接类型，性能从好到差依次为NULL, system,const,eq_ref,ref,range,index,all

system系统查询，const主键或者唯一索引查询，ref非唯一索引，all全表扫描。

possible_key:可能用到的索引

Key:时间用到的索引，null就是没用到

Key_len

表示索引中使用的字节数，该值为索引字段的最大可能长度，并非实际长度，不损失精度的前提下，越短越好。

rows

MySQL认为必须去执行查询的行数，在innodb引擎的表中，是一个估计值，不准确

filtered

表示返回行数占所需读取行数的百分比，filtered的值越大越好。

extra

额外信息

使用规则

可以自己测试，先建立一个大数据表，查询这个数据表某个未建立索引字段所需要的时间，然后在这个未建立索引的字段建立普通索引，再次查询，能够发现加了索引后，所需的时间不是一个量级。大大减少。

最左前缀法则

如果索引了多列(联合查询)，要遵守最左前缀法则，最左前缀法则指的是插叙从索引的最左列开始，并且不跳过索引中的列，如果跳过某一列，索引将部分失效(后面的字段）

与位置，无关，必须存在！

#创建联合索引
create index procession, age, status on t_user(procession, age, status);

//完整索引查询
explain select * from tb_user where  procession = "软件工程" and age = 31 and status = "0";

explain select * from tb_user where  procession = "软件工程" and age = 31;

explain select * from tb_user where  procession = "软件工程";


//部分索引查询，第一个，中间跳过
explain select * from tb_user where  procession = "软件工程" and status = "0"; 


//
explain select * from tb_user where and age = 31 and status = "0";

范围查询

联合查询中，出现范围查询（< >）,范围查询右侧的链表索引失效

#age右侧索引失效， 49
explain select * from tb_user where  procession = "软件工程" and age > 31 and status = "0";


#全都可以 54
explain select * from tb_user where  procession = "软件工程" and age >= 31 and status = "0";

索引失效情况

1.索引列进行运算操作，索引将失效

explain select * from t_user where substring(phone, 10, 2);

2.字符串不加引号，索引将失效

explain select * from t_user where profession = “软件工程” and age = 23 and status = 0; //status失效

explain select * from t_user where phone = 12324232;

3.模糊查询，尾部模糊查询不会失效，首部和中间就会失效

explain select * from t_user where profession = "%邵"; //不会失效

explain select * from t_user where profession = "邵%"; //会失效

explain select * from t_user where profession = "%邵%";//会失效

4.用or分割开的条件，如果or的前条件有索引，后条件没有索引，那么条件不会被用到

explain select * from t_user where id = 10 or age = 23;

由于age没有索引，即使id，phone有索引，索引也会失效，唯一的方法是给age加上索引。

5.数据分布影响

如果MySQL评估使用索引比全表更慢，则不会使用索引。

sql提示

覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经能够全部找到)减少select *

前缀索引

当字段类型为字符串（varchar, text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量磁盘IO,影响查询效率，此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，提交索引效率

create index idx_xxx table_name(column(n));

前缀的长度，可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是好的。

select count(distinct email) / count(*) from tb_user;


select count(distinct substring(email,1, 5))/count(*) from tb_user;

单列索引和联合索引 

单列索引:一个索引只包含了单个列

联合索引:一个索引包含了多个列

在业务场景中，如果存在了多个查询，可考虑对于查询字段建立索引时，建议联合索引，而非单列索引

explain select id, phone, name from t_user where phone ="1234232" and name =“韩信”

多条件联合查询，MySQL优化器会评估哪个字段的索引效率更高，选个该索引完成搜索

索引设计 

 数据大于100万条建议使用索引