索引

优点

1、提高数据检索效率，降低数据库的IO成本

2、通过索引对数据库进行排序，降低数据排序成本，降低CPU消耗

缺点

1、索引需要占用空间

2、索引大大提高了查询效率，但是降低了插入、删除、更新等操作的效率

不同存储引擎索引结构不同

B+Tree索引 大多数【全部支持】

Hash索引【仅支持memory】

R-Tree索引（空间索引）【仅支持MyISAM】

Full-Text索引（全文索引）【仅支持MyISAM、5.6版本后的InnoDB支持】

B+Tree索引

B-Tree 度数大于2的数

B+Tree所有元素都会出现在叶子节点，叶子节点形成了一个单向链表

B+Tree索引在B+Tree结构上进行了优化，叶子节点之间用双向循环链表连接

Hash索引

只能用于等值查询，不支持范围查询

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

查询效率高，通常只需要一次检索就可以了（不出现hash冲突），效率通常高于B+Tree索引

InnoDB中具有自适应hash功能

为什么InnoDB选择B+Tree索引结构？（面试题）

相对于二叉树层级更少，搜索效率高

相对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页

中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，

导致性能降低

相对于Hash索引，B+Tree支持范围匹配和排序操作

索引分类

主键索引：

针对于表中主键创建的索引、默认自动创建，只能有一个、关键字PRIMARY

唯一索引：

避免同一个表中某数据列中的值重复、可以有多个、关键字UNIQUE

常规索引：

快速定位数据、可以有多个

全文索引：

查找的是文本中的关键字，而不是比较索引里的值、可以有多个、关键字FULLTEXT

OR

聚集索引：（默认主键索引、不存在主键会使用第一个唯一索引、不存在唯一索引会自动生成rowid）

将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据、必须有，而且只有一个

二级索引：

将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键、可以有多个

回表查询：先走二级索引得到主键值，再根据主键值去聚集索引中获取数据

索引操作语法

创建索引

create [unique|fulltext] index index_name on table_name(关联字段名，可以有多个);

如果一个索引关联一个字段，则称为单链索引

查看索引

show index from table_name;后加上\G改变格式，列转行

删除索引

drop index index_name on table_name;

索引名称规范：

index_表名_字段名

SQL优化（主要针对查询）

SQL性能分析

SQL执行频率

show [session当前会话|global全局] status可以查看当前数据库insert、update、delete、select的访问频次

show global status like 'Com_操作类型'; 使用一个下划线代表一个字符，

慢查询日志

记录所有执行时间超过指定参数的SQL语句，默认10s

show variables like 'slow_query_log'; 显示慢查询日志开启情况

profile

show profiles能显示每一条语句耗费的时间，且展示耗时详情

select @@have_profiling;查看当前数据库是否支持profile

show profile [cpu] for query query_id;查看指定SQL语句的(cpu)耗时详情

explain查看执行计划*

在任意select语句前加上关键字explain/desc

字段含义：

id:select查询的序列号，表示查询中执行select子句或者是操作表的顺序（从大到小执行，相同则从上到下）

select_type:查询类型

！type:访问类型，性能由好到差：NULL、system、const、eq_ref、ref、range、index、all

尽量把type往前优化

NULL一般达不到、const根据主键或者唯一索引、ref根据非唯一索引查询

！possible_key:可能应用的索引，一个或者多个

！key:实际用到的索引

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

rows:预估执行查询的行数

filtered:表示返回结果的行数占读取行数的百分比，越大越好

extra:额外没有展示的内容

索引使用原则

最左前缀法则

使用联合索引时，查询从索引的最左列（必须存在，但是与其位置无关）开始（否则索引失效），并且不跳过索引中的列

如果跳过了某一列，索引会部分被失效（后面的字段索引）

范围查询 >和=和