索引的优势

1. 提高查询效率（降低IO使用率）

2. 降低CPU使用率

比如查询order by age desc，因为B+索引树本身就是排好序的，所以再查询如果触发索引，就不用再重新查询了。

索引的弊端

• 索引本身很大，可以存放在内存或硬盘上，通常存储在硬盘上。
• 索引不是所有情况都使用，比如（1）少量数据（2）频繁变化的字段（3）很少使用的字段
• 索引会降低增删改的效率

索引的分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种:

聚集索引选取规则:

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

回表

假设你的某个索引由 a,b 两个字段组成为名为 idx_a_b的索引，然后你的查询语句是

SELECT a,b,c FROM t where a = 2

此时因为你的 idx_a_b 的索引树叶子结点并不包含字段c的数据，所以MySQL会根据idx_a_b这棵树的叶子结点中存储的主键ID，去主键索引树上去查询数据，这个过程就叫做回表。
简单一句话就是，二级索引叶子结点不完全包含要查询的数据，所以需要根据二级索引叶子结点中的主键ID去主键索引树查数据。

索引的结构

B-tree

B-Tree：多路平衡查找树

• 树形结构：由根节(root)、分支(branches)、叶(leaves)三级节点组成，其中分支节点可以有多层。
• 多分支结构：与binary tree不相同的是，B-tree索引中单root/branch可以有多个子节点(超过2个)。
• 双向链表：整个叶子节点部分是一个双向链表(后面会描述这个设计的作用)
• 单个数据块中包括多条索引记录

利用率

B-tree索引对结构的利用率很高，定位高效。当1千万条数据时，B-tree索引也是三层结构(依稀记得亿级数据才是3层与4层的分水岭)。

平衡扩张

除了利用率高、定位高效外，B-tree的另一个特点是能够永远保持平衡，这与它的扩张方式有关。

先描述下B-tree索引的扩张方式：

新建一个索引，索引上只会有一个leaf节点，取名为Node A，不断的向这个leaf节点中插入数据后，直到这个节点满，这个过程如下图（绿色表示新建/空闲状态，红色表示节点没有空余空间）：

当Node A满之后，我们再向表中插入一条记录，此时索引就需要做拆分处理：会新分配两个数据块NodeB & C，如果新插入的值，大于当前最大值，则将Node A中的值全部插入Node B中，将新插入的值放到Node C中；否则按照5-5比例，将已有数据分别插入到NodeB与C中。

无论采用哪种分割方式，之前的leaf节点A，将变成一个root节点，保存两个范围条目，指向B与C，结构如下图（按第一种拆分形式）：

当Node C满之后，此时 Node A仍有空余空间存放条目，所以不需要再拆分，而只是新分配一个数据块Node D，将在Node A中创建指定到Node D的条目：

如果当根节点Node A也满了，则需要进一步拆分：新建Node E&F&G，将Node A中范围条目拆分到E&F两个节点中，并建立E&F到BCD节点的关联，向Node G插入索引值。此时E&F为branch节点，G为leaf节点，A为Root节点

在整个扩张过程中，B-tree自身总能保持平衡，Leaf节点的深度能一直保持一致。

单一方向扩展引起的索引竞争(Index Contention)

若索引列使用sequence或者timestamp这类只增不减的数据类型。这种情况下B-tree索引的增长方向总是不变的，不断的向右边扩展，因为新插入的值永远是最大的。

当一个最大值插入到leaf block中后，leaf block要向上传播，通知上层节点更新所对应的“值范围”条目中的最大值，因此所有靠右边的block(从leaf 到branch甚至root)都需要做更新操作，并且可能因为块写满后执行块拆分。

如果并发插入多个最大值，则最右边索引数据块的的更新与拆分都会存在争抢，影响效率。在AWR报告中可以通过检测enq: TX – index contention事件的时间来评估争抢的影响。解决此类问题可以使用Reverse Index解决，不过会带来新的问题。

Index Browning 索引枯萎

其实oracle针对这个问题有优化机制，但优化的不彻底，所以还是要拿出来的说。

我们知道当表中的数据删除后，索引上对应的索引值是不会删除的，特别是在一性次删除大批量数据后，会造成大量的dead leaf挂到索引树上。

如果表100以上的数据会部被删除了，但这些记录仍在索引中存在，此时若对该列取max()：

通过与之前相同演算，找到了索引树上最大的数据块，按照记录最大的值应该在这里，但发现这数据块里的数据已经被清空了，与是利用B-tree索引的另一个特点：leaves节点是一个双向列表，若数据没有找到就去临近的一个数据块中看看，在这个数据块中发现了最大值99。

在计算最大值的过程中，这次的定位多加载了一个数据块，再极端的情况下，大批量的数据被删除，就会造成大量访问这些dead leaves。

针对这个问题的一般解决办法是重建索引，但记住! 重建索引并不是最优方案，详细原因可以看看这。使用coalesce语句来整理这些dead leaves到freelist中，就可以避免这些问题。理论上oracle中这步操作是可以自动完成的，但在实际中一次性大量删除数据后，oracle在短时间内是反应不过来的。

B+ Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

• 非叶子节点只存储键值信息。
• 所有叶子节点之间都有一个链指针。
• 数据记录都存放在叶子节点中。

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^{3）。也就是说一个深度为3的B+Tree索引可以维护10}3 * 10^3 * 10^3 = 10亿 条记录

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2_{4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1}3次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。

上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。

辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

hash索引

哈希索引原理其实就是hash表，搜索时间效率O(1)，搜索效率好，也意味着磁盘IO花费少，mysql底层使用的是链式哈希表，结构如下，每一个bucket就是一个个哈希桶，也就是哈希链表的头结点。哈希结构天然的需要耗费空间资源，是一种用空间换时间的做法。

哈希要点：

• 解决哈希冲突的几种方式
• 再哈希的参数碰撞因子或者说负载因子
  

拉链法去解决的哈希冲突，也正是这个结构造成了哈希索引的一些特性

• 哈希表没有顺序可言，只能进行等值查询，不支持范围搜索
• 哈希表不稳定，效率最差可能变为O(n)，因为哈希冲突链表可能会变长
• 天然不能减少磁盘IO，要基于内存
• 会锁住桶，有可能造成阻塞

创建哈希索引sql语句如下

create index nameidx on student(name) using hash;

即使创建的是hash索引，也不一定就是哈希结构，Mysql会自动优化，具体用的是什么索引还是需要看看的,依然有可能使用的还是BTREE索引

show indexes from student;

自适应哈希索引

自适应哈希索引其实是MySql自己创建的索引，辅助索引在数据没有覆盖的情况下需要二次回表，也就是使用先使用辅助索引再使用主键索引

辅助索引不带有实际的数据，那么可不可以对辅助索引做一个优化呢，自适应哈希索引就是这么诞生的

Innodb存储引擎检测到某个二级索引不断被使用，那么他就会根据这个二级索引，在内存上根据二级索引B+树的结构创建一个哈希索引，用于加速搜索

自适应哈希索引创建后，就会在等值查询的时候直接查询哈希索引，哈希索引上有数据的地址，这样就省下了辅助索引跟主键索引的搜索时间

其实创建哈希索引也是比较消耗资源的，这里有些衡量参数，要根据衡量参数决定是否打开或者关闭自适应哈希索引，

查看自适应哈希索引是否开启的命令

show variables like 'innodb_adaptive_hash_index';

查看自适应哈希分区（或者说桶）

show variables like 'innodb_adaptive_hash_index_parts';

两个比较重要的信息要注意，查看命令如下

show engine innodb status\G

1. 能看到RW-latch等待的线程数量，自适应哈希索引默认分配了8个分区，同一个分区等待的线程数量过多
2. 走自适应哈希索引搜索的频率和二级索引树搜索的频率

索引使用原则

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

范围查询
联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。尽量使用大于等于或者小于等于

索引列运算
不要在索引列上进行运算操作，索引将失效。

字符串不加引号
字符串类型字段使用时，不加引号，索引将失效。

模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

or连接的条件
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。

创建索引

主键索引：一张表只能有一个主键索引，是一种特殊的唯一索引，一个表只能有一个主键、不允许有空值、不允许重复、不允许为 NULL，一般是在建表的时候同时创建主键索引。

建表方式：例如
CREATE TABLE `table_name` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

普通索引：是最基本的索引，没有什么特殊的限制。

创建方式：CREATE INDEX index_name ON table(column(length));
修改方式：ALTER TABLE table_name ADD INDEX index_name ON (column(length));
建表方式：CREATE TABLE `table_name ` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
);

唯一索引：与前面的普通索引类似，不同的就是，索引列的值必须唯一，但允许有空值。如果是联合（多列）索引，则列值的组合必须唯一。它有以下几种创建方式：

创建方式：CREATE UNIQUE INDEX indexName ON table(column(length))
修改方式：ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
建表方式：CREATE TABLE `table_name ` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    UNIQUE indexName (title(length))
);

联合索引：两个或两个以上字段联合组成一个索引。使用时需要注意满足最左匹配原则！

create index 索引名称 on 表名(字段名称)
ALTER TABLE `table_name` ADD INDEX idx_name_city_age (name,city,age); 

删除索引的方法:

DROP INDEX index_name ON table_name

性能优化

SQL执行频率

MySQL客户端连接成功后，通过 show [sessionIglobal) status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:

如：

show GLOBAL STATUS LIKE 'com____'
多少个下划线代表多少个字符

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位:秒，默认10秒）的所有5QL语句的日志。

MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息:

# 开启MySQL慢日志查询开关
slow_query_log=1

#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试

systenctl restart mysql

查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。

cd /var/lib/mysql

查看是否开启慢查询：

show variables like 'slow_query_log'

profile详情

show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过have_profiling参数，能够看到当前MySQL是否支持

profile操作：

SELECT @@have_profiling

默认profiling是关闭的，查看是否打开

select @@profiling

可以通过set语句在session/global级别开启profiling:

set profiling = 1

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时:

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

# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

# 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

explain执行计划

EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

# 直接在select语句之前加上关键字 explain / desc

EXPIAIN SELECT 字段列表 FROM 表名 WHERE 条件;

explain 执行计划各字段含义：

• 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 。

• possible_key

  • 显示可能应用在这张表上的索引，一个或多个。

• Key

  • 实际使用的索引，如果为NULL，则没有使用索引。

• Key_len

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

• rows

  • MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

• filtered

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

sql提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。use index:用哪个索引

explain select * from tb_user uge index(idx_user_pro) where profession= '软件工程";

ignore index:不用哪个索引

explain select * from tb_user ignore index(idx_user_pro) where profession= "软件工程";

force index:必须用哪个索引

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程";

覆盖索引

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

• using index condition :查找使用了索引，但是需要回表查询数据
• using where; using index :查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

一张表,有四个字段(id, username, password,status),由于数据量大,需要对以下SQL语句进行优化,该如何进行才是最优方案:

select id,username,password from tb_user where username = ' itcast';

1. 以username建立单列索引，但返回结果包含id和username，会触发回标查询，固不可取
2. username和password建立联合索引。他们就作为二级索引，二级索引的叶子节点就是id。此时不需要回表，直接覆盖索引

前缀索引

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

create index idx _xxxx on table_name(column(n)) ;

单列索引和联合索引的选择

• 单列索引:即一个索引只包含单个列。
• 联合索引:即一个索引包含了多个列。
  在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。