索引

• 索引是一种数据结构
• 索引是一种排好序的快速查找的数据结构
• 数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列也是要占用空间的。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。

索引的分类

1. 索引

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建,只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

2. 存储形式 InnoDB

分类	含义	特点
聚集索引	将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引	将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键	可以存在多个（其他索引都是二级，会回表）

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一(UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

创建索引

create index idx_xxxx on tb_anme(column [desc,asc]);

show index tb_name;

常用的索引数据结构

索引	Innodb	MyISAM	Memory
B+Tree	YES	YES	YES
Hash			YES
R-Tree		YES
Full-Text	5.6以后	YES

Tree

二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。

RB-Tree

平衡二叉树，可以解决数据倾斜的问题，但是层级还是太深

B-Tree

多路平衡查找树

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

根节点维护四个数据，5个区间指针，通过区间去检索下一级的节点。每个节点都会维护数据项。

当节点的度达到最大值会使中间值向上分裂

B+Tree

最常见的索引类型，大部分引擎都支持B+树索引

以一颗最大度数（max-degree）为4（4阶）的 b+tree 为例：

所有数据都会存在叶子节点，叶子节点的数据会形成一个单向链表

当节点的度达到最大值会使中间值向上分裂

相对于B-Tree区别:

• 所有的数据都会出现在叶子节点
• 叶子节点形成一个单向链表
• 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
• 相对Hash索引，B+tree支持范围匹配及排序操作

MySQL 里面的 B+Tree 结构，叶子节点是一个循环双向链表：

R-Tree

空间索引是MylSAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少

Full-Text

是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

Hash

底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞)，可以通过链表来解决。

hash 底层是哈希表实现，等值查询，可以快速定位，一般情况效率很高，不稳定，当出现大量键重复哈希冲突，效率下降，不支持范围查询，无法用于排序分组，无法模糊查询，多列索引的最左前缀匹配原则，总要回表操作等。

性能优化

查看执行频次

// 查看 数据库的执行频次
show global status;

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认没有开启，需要在MvSOL的配置文件(/etr/my.rnf)中配詈加下信自．

show variables like "slow_query_%";

[mysqld]
slow_query_log=1

long_query_time=2

profile 详情

查询一条 SQL 的具体执行时间

// 是否支持
select @@have_profiling;
// 开启
set [session|global] profiling=1;

// 查询当前会话所有的 SQL 耗时
show profiles;

// 查看某条 SQL 的执行耗时
show profile [cpu|mem] for query query_id;

explain

也可以用 desc

• Id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下; id不同值越大，越先执行）
• select_type：表示SELECT的类型，常见的取值有SIMPLE(简单表，即不使用表连接或者子查询）、PRIMARY（主查询即外层的查询）、UNION(UNION中的第二个或者后面的查询语句）、SUBQUERY (SELECT/WHERE之后包含了子查询）
• type：表示连接类型，性能由好到差的连接类型为 NLLL、system、const、eq_ref、ref、range、index、all
  • NLLL一般不访问任何表才会出现：select 'A';
  • system 访问系统表
  • const 主键唯一索引
  • ref 非唯一索引查询就会出现
  • index
• possible_key：显示可能应用在这张表上的索引，一个或多个
• key：实际使用的索引，如果为NULL，则没有使用索引
• key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
• rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
• filtered：表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好
• extra：
  • Using index condition：查找使用了索引，但是需要回表查询数据
  • Using where; using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
  • Using filesort
  • Using temporary

SQL 提示

use index：使用

igonre index：忽略

force index：强制

SQL 优化

插入

• 批量插入，单批次1000条以内
• 手动提交事物插入
• 主键顺序插入
• 大批量插入数据
  • 如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

  mysql --local-infile -u root -p
  set global local_infile = 1;
  load data local infile 'path' into table tb_user fileds terminated by ',' lines terminated by '\n';

主键

优化

• 满足业务需求的情况下，尽量降低主键的长度
  • 因为二级索引的叶子节点会挂着主键索引，占用大量的磁盘空间，占IO
• 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号
• 业务操作时，避免对主键的修改
  • 还要修改二级索引

页

• 数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table IOT）。
• 页分裂：页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据多大，会行溢出)，根据主键排列。
  • 开辟一个新的页
  • 从索引页分出一半数据到新的页
  • 然后新的数据插入到新的页的结尾
  • 然后重排索引页指针

• 页合并：
  • 当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。
  • 当页中删除的记录达到MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。
  • MERGE_THRESHOLD：合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

order by

• Using filesort：
  • 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
  • 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)
• Using Index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高
• 尽量使用覆盖索引
• 如果是联合索引，遵循左前缀法则
  • 全部使用 升序 或 降序索引
  • 如果使用 一个升序 一个降序则不会走索引，可以在创建的时候可以指定索引，这样也会走索引

group by

• 分组字段有索引
• 满足左前缀法则

limit

• 大批量数据分页慢，可以采用排序主键，然后 from 后子查询比较
  • 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

count

不为 NULL 则加一

• MyISAM 会单独存储了一个 count
• InnoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
  • 采用 Redis
• count(主键)
  • InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)
• count(字段)
  • 没有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加
  • 有not null约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
• count(1)
  • InnoDB引擎遍历整张表,但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加
• count(*)
  • InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

按照效率排序的话，count(字段)<count(主键id)<count(1) < count()，所以尽量使用count()

update

• where 没加索引，会行锁会升级成表锁
• InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁。

常用索引类型

• 普通索引：可以重复
• 主键索引：
  • 唯一，不为空，叶子结点存出了行记录数据，主键索引也称聚簇索引，对应非主键索引的叶子结点存的主键的值（二级索引），用二级索引查需要回表操作（根据二级索引查到主键，再根据主键去主键索引查）
  • 一般推荐用自增主键，保证空间利用率，减少页分裂
• 唯一索引：唯一，可为空，表中只有一个主键索引，可多个唯一索引
• 全文索引：

索引使用原则

• **覆盖索引：**索引字段覆盖了查询语句涉及的字段，直接通过索引文件就可以返回查询所需的数据，不必通过回表操作。
  • 回表：通过索引找到主键，再根据主键 id 去找主键索引查询。
• **索引下堆：**在根据索引查询过程中就根据查询条件过滤掉一些记录，减少最后的回表操作
• 联合索引：
  • 将查询需求频繁或者字段选择性高的列放在前面
  • 索引的复用，可以少维护一些索引 (a)->(a,b) 如果既有联合查询，又有基于 a、b 各自的查询呢？：考虑的原则就是空间，将小的单独建索引
  • 最左匹配原则：如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)。顺序无关，SQL 会自动优化
  • 如果使用范围查询（<>），右边列的索引会失效。如果一定要使用：>=
  • 联合索引的数据结构：

• 前缀索引：
  • 当字段类型为字符串(varchar，text等),时，有时候需要索引很长的字符串，这会让索引变得很大查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
  • 尽量创建短索引，对长子字符串创索引可使用前缀索引，使用字段值前几个字符作为索引
  • 可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

// 计算选择性
select count(distinct email) /count(*) from tb_user;

// 计算选择几个字符
select count(distinct substring(email, 1, 5)) / count(*) from tb_user;

// 创建索引
create index idx_xxx on table(column(filed(10)));

- **前缀索引数据结构：**

使用前缀查询时，会先截取索引长度字符到索引里面查询，找到节点以后会回表比较是否全部一致，然后在去匹配下一个节点是否一致。

索引失效场景

• 以**“%”开头的like**语句，索引无效，后缀“%”不影响
• or 语句前后没有同时使用索引
• 列类型是字符串，一定要在条件中将数据用引号引用，否则失效（隐式转换）
• 组合索引要遵守最左前缀原则——不使用第一列索引 失效
• 在索引字段上使用** not，<>，!= **（对它处理是全表扫描）
• 对索引字段进行计算操作，字段使用函数也会失效
• 如果 mysql 估计使用全表扫描比索引快，则不用索引（键值少，重复数据多）

  • = 的情况

  • 如果表中数据大多都是 null，使用 is not null 走索引，is null 走全表
  • 如果表中数据大多都是有值，使用 is not null 走全表，is null 走索引会更快

索引设计原则

• 基数小的表没必要

• 使用短索引，如果索引长字符串列，应该指定前缀长度

• 定义有外键的数据列一定索引

• 更新频繁的不适合

• 针对于数据量较大（100万），且查询比较频繁的表建立索引。

• 针对于常作为查询条件（where)、排序(order by)、分组(group by)操作的字段建立索引。

• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。区分度不高的不适合，如性别

• 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。尽量扩展索引，别新建索引，如(a)->(a,b)

• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。不要过度索引

• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

• 字符串字段建立索引方法

  • 直接创建完整索引，这样可能比较占用空间；
  • 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
  • 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
  • 额外用一个字段进行索引，额外计算开销

总结：索引设计原则要求查询快，占用空间少；一般建在where条件，匹配度高的；要求基数大，区分度高，不要过大索引，尽量扩展，用联合索引，更新频繁不适合、使用短索引。

最左匹配原则

从左往右匹配，直到遇到范围查询建立联合索引（a,b,c）索引是先根据a排序，a相同时b有序，a不同无序，以此类推。总之遇到范围查询就停。

数据删除

删除数据的速度和创建的索引数量是成正比的。先删索引，再删无用数据，再创建索引

普通索引和唯一索引怎么选

• 查询比较
  • 查询会以页为单位将数据页加载进内存，不需要一条记录一条记录读取磁盘。然后唯一索引根据条件查询到记录时就返回结果，普通索引查到第一条记录往后遍历直到不满足条件，由于都在内存中，不需要磁盘读取那么大开销，带来的额外查询开销忽略不计，所以查询性能几乎一致
• 更新比较
  • 唯一索引由于更新时要检查唯一性，所以需要将数据页先加载进内存才能判断，此时直接操作内存，不需要操作change buffer
  • 补充：普通索引若数据再内存中直接内存中更新，否则会将更新操作先记录到channge buffer中，等下一次查询将数据读到内存中再进行change buffer里相关更新操作后将数据返回，这样一来，再写多读少的情况下就减少了磁盘IO，若写完就马上查询，就大可不必用change buffer，不但没提高多少效率还造成维护change buffer额外消耗
  • 将change buffer的操作对应到原始数据页的操作称为merge（可以查询来时读到内存再修改数据，后台线程也会merge，数据库正常关闭也会merge）
• 适合场景
  • 写多读少，选用普通索引更好，可以利用change buffer进行性能优化减少磁盘IO，将更新操作记录到change bufer，等查询来了将数据读到内存再进行修改.