一. 索引类型
索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
应用层次划分：普通索引、唯一索引、主键索引、复合索引
索引键值类型划分：主键索引、辅助索引（二级索引）
数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）
1.1 普通索引
基于普通字段建立的索引， 没有任何限制
创建索引
create index <索引名字> on 表名(字段名)
alter table 表名 add index [索引名字](字段名)删除索引
DROP INDEX [indexName] ON tableName; 1.2 唯一索引
与不同字段类似， 索引字段必须唯一， 允许空值。 在创建表或者修改表时添加唯一约束，则自动创建
create unique index <索引名字> on 表名(字段名)
alter table 表名 add unique index [索引名字](字段名)
#案例
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
); 1.3 主键索引
它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。
create table 表明 ([...], peimary key(字段名)) ;
alter table 表名 add primary index [索引名字](字段名) ;
1.4 复合索引
复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。
分为窄索引和宽索引：超过2列是宽索引
create index <索引的名字> ON tablename (字段名1，字段名2...);
alter table tablename ADD index [索引的名字] (字段名1，字段名2...);
create table tablename ( [...], index [索引的名字] (字段名1，字段名2...) );复合索引使用注意事项：
1. 更加where条件添加复合索引
2.
如果表已经建立了
(col1
，
col2)
，就没有必要再单独建立（
col1）；
1.5 全文索引
查询操作在数据量比较少时，可以使用
like
模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like
快很多倍。
create fulltext index <索引的名字> ON tablename (字段名1，字段名2...);
alter table tablename ADD fulltext index [索引的名字] (字段名1，字段名2...);
create table tablename ( [...], FULLTEXT key [索引的名字] (字段名1，字段名2...) );
全文索引使用注意事项：
1.
全文索引必须在字符串、文本字段上建立。
2.
全文索引字段值必须在最小字符和最大字符之间的才会有效。（
innodb
：
3-84
；
myisam
：
4-
84
）
3. 全文索引字段值要进行切词处理，按
syntax
字符进行切割，例如
b+aaa
，切分成
b
和
aaa
4.
全文索引匹配查询，默认使用的是等值匹配，例如
a
匹配
a
，不会匹配
ab,ac
。如果想匹配可以在布
尔模式下搜索
a*
select * from user where
match(name) against('x*' in boolean mode);二. 索引原理
MySQL
官方对索引定义：是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。
索引是物理数据页存储，在数据文件中（
InnoDB
，
ibd
文件），利用数据页
(page)
存储。
索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。
索引涉及的理论知识：二分查找法、Hash和B+Tree。
2.1 二分法查找
2.2 Hash结构
Hash索引主要针对于键值对， 在hash表中， key为hash值， value为对应的一个等值信息。 当InnoDB存储引擎会监控表上的各个索引页的查询， 根据热点数据，会在BP内存中，基于B+Tree索引在创建一个哈希索引， 即能够快速定值访问频繁访问的索引页。 这就是哈希自适应索引
show engine innodb status \G;
show variables like '%innodb_adaptive%';2.3 B+Tree结构
首先B-Tree， 是非聚簇索引， 数据和索引分布了整个Tree中， 每个节点可以存储多个索引值以及data数据， 并且每个节点是有序的， 使得我们可以使用二分法查找， 快速定位索引。
优化改造后的B+Tree:
非叶子节点不存储
data
数据，只存储索引值，这样便于存储更多的索引值
叶子节点包含了所有的索引值和
data
数据
叶子节点用指针连接，提高区间的访问性能
相比
B
树，
B+
树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进
行遍历即可。而
B
树需要遍历范围内所有的节点和数据，显然
B+Tree
效率高
B树的性质：m阶B树(m>=2)==>指每个节点最多有m个子节点
1. 每个节点的子树高度都是一致的
2. 根节点： 1 <= x <= m - 1
3. 非根节点 ceiling(m/2) - 1 <= x <= m - 1 ceiling:向上取整
4. 如果有子节点： 子节点 y = x + 1
根节点：2 <= y <= m
非根节点： ceiling(m/2) <= y <= m
比如三阶B树： 根节点的子节点必须是(2, 3) 非根节点（2， 3） 2-3树
四阶B树： 根节点的子节点必须是(2, 4) 非根节点（2， 4）2-3-4树
5. 数据库一般为： 200~300阶
6. B树在逻辑上和二叉搜索树等价的
7. N代合并的超级节点， 最多有2**n 个 子节点
8. m阶B树， 最多需要log2m 代合并
2.4 聚簇索引和辅助索引
聚簇索引和非聚簇索引：
B+Tree
的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。
主键索引和辅助索引：
B+Tree
的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。
在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。
InnoDB
的表要求必须要有聚簇索引：
如果表定义了主键，则主键索引就是聚簇索引
如果表没有定义主键，则第一个非空
unique
列作为聚簇索引
否则
InnoDB
会从建一个隐藏的
row-id
作为聚簇索引
说明： 首先在B+Tree的所有叶子节点中都存储了整个树的所有索引节点以及数据， 紧挨在一起， 找到索引节点， 则找到数据。 主键索引可以直接找到数据， 主键索引只能有一个， 后面还排列了二级索引， 可以是多个， 首先根据二级索引找到节点数据后， 会看它属于那个主键索引， 再去查找，最终获得数据。
三. 索引的分析与优化
3.1 EXPLAIN
MySQL
提供了一个
EXPLAIN
命令，它可以对
SELECT
语句进行分析，并输出
SELECT
执行的详细信息，供开发人员有针对性的优化。
我们可以根据select_type： 知道查询语句是什么样的！，比如不包含子查询或
union的SIMPLE,
表明是外查询：primary, 还要UNION查询， 子查询等！
type: 我们可以根据这个字段， 初步分析SQL的效率：
ALL：表示全表扫描，性能最差。
index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
range：表示使用索引范围查询。使用
>
、
>=
、
<
、
<=
、
in
等等。
ref：表示使用非唯一索引进行单值查询。
eq_ref：一般情况下出现在多表
join
查询，表示前面表的每一个记录，都只能匹配后面表的一
行结果。
const：表示使用主键或唯一索引做等值查询，常量查询。
NULL：表示不用访问表，速度最快。
key
表示查询时真正使用到的索引，显示的是索引名称。
rows： 可以让我们直观的看到， SQL检索了多少行， 有些时候使用了索引不一定就得到了优化
key_len
表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
Extra
Extra
表示很多额外的信息，各种操作会在
Extra
提示相关信息，常见几种如下：
Using where 一般考虑可以通过 给查询的字段， 根据where条件建立组合索引
表示查询需要通过索引回表查询数据。
Using index 优化后的成果
表示查询需要通过索引，索引就可以满足所需数据。
Using filesort 一般可以给排序的字段添加索引
表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有
Using filesort
建议优化。
Using temprorary 给需要去重、分组的字段， 进行添加索引
查询使用到了临时表，一般出现于去重、分组等操作。
3.2 回表查询
在根据辅助索引查询时， 会根据辅助索引， 定位主键值， 在根据主键值，
通过聚簇索引定位行记
录，这就叫做
回表查询
，它的性能比扫一遍索引树低。
总结：通过索引查询主键值，然后再去聚簇索引查询记录信息
3.3 覆盖索引
只需要在一棵索引树上就能获取
SQL
所需的所
有列数据，无需回表，速度更快，这就叫做索引覆盖
。
实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。
3.4 最左原则
建立了组合索引和 比如 index(a, b, c)
1. 只能先找a ==> where a = 'x' and b = 'x'
2. 可以按照循序， 用a; a,b; a,b,c;
3.5 LIKE查询
like查询会使索引失效吗？
如果是 %xxx% || %xxx 这样使用会导致失效， xx%不会
3.6 NULL查询
如果某一列有null值， 会使索引失效吗？
1. 是可以使用的， 但是不推荐字段为NULL
2. 使用NULL， count() 不会统计， 不能使用> = 等运算符运算， 并且需要额外的空间记录
3.7 排序与索引
MySQL
查询支持
fifilesort
和
index
两种方式的排序，
fifilesort
是先把结果查出，然后在缓存或磁盘进行排序操作，效率较低。使用index
是指利用索引自动实现排序，不需另做排序操作，效率会比较高。
大致就是根据添加 组合索引等手段， 使
Extra类型为 Using index
四. 查询优化
4.1 慢查询定位
查看
慢查询日志和慢查询日志文件的存储位置
SHOW VARIABLES LIKE 'slow_query_log%'配置慢查询
SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;可以根据日志文件找到慢查询语句， 进行优化
4.2 慢查询优化
一般像范围查询， 使用了> < 这样的运算符， 很有可能， 使用了索引， 依然不能使速度变快，type为 index, 进行了全局索性查询。
我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果
扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过
滤性好，执行速度才会快。
提高索引过滤性：
案列：
表：student 字段：id,name,sex,age
造数据：insert into student (name,sex,age) select name,sex,age from student;
SQL案例：select * from student where age=18 and name like '张%';（全表扫 描）优化一：
建立索引：
create index name_1 student(name) ;优化二：
建立组合索引
alter table student add index(age,name); //追加age,name索引优化三：
我们可以通过虚拟列来实现。
//为user表添加first_name虚拟列，以及联合索引(first_name,age)
alter table student add first_name varchar(2) generated always as (left(name, 1)),
add index(first_name, age);
explain select * from student where first_name='张' and age=18;
慢查询原因总结：
全表扫描：explain
分析
type
属性
all
全索引扫描：explain
分析
type
属性
index
索引过滤性不好：靠索引字段选型、数据量和状态、表设计
频繁的回表查询开销：尽量少用select *
，使用覆盖索引
4.3 分页查询的优化：
SELECT * FROM 表名 LIMIT [offset,] rows
案例：
select * from user limit 10000,100;像这样的查询： 我们可以通过两步进行优化：
1. 首先利用利用id 覆盖索引， 这样我们快速的找到1000以后第一条数据
select id from user limit 10000, 1;2. 接着我们只需要让外查询的 where id >= (这个结果的id即可) 在限制 100 。
select * from user where id>= (select id from user limit 10000,1) limit 100;
原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
