mysql索引探讨索引篇章什么是索引？排好序的快速查找数据结构，可以帮助快速查找数据优缺点： 1 类似大学图书馆建

索引篇章

5个思考题

为了减少IO，索引树会一次性加载么
B+树的存储能力如何？为何说一般的查找行记录，最多只需1~3次磁盘IO
为什么说B+树比B-树更适合实际应用中操作系统文件索引和数据库索引
Hash索引与B+树索引的区别
Hash索引与B+树索引是在建索引的时候手动指定的么

什么是索引？

排好序的快速查找数据结构，可以帮助快速查找数据优缺点：

1 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因
2 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
3 在实现数据的参考完整性方面，可以加速表和表之间的连接
4 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗缺点
1 创建和维护索引需要耗费时间，并且随着数据量的增加而增加
2 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上
3 虽然索引大大加快了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加，删除和修改的时候，索引也要动态的维护，这就降低了数据的维护速速

唯一性索引

单值多值唯一索引可以向声明为unique的字段上添加null值，而且可以多次添加null 因为这里 NULL 的定义，是指未知值。所以多个 NULL ，都是未知的，不能说它们是相等的，也不能说 >是不等，就是未知的。所以多个NULL的存在是不违反唯一约束的。

！！！在mysql里 null 不等于 null！！！

innoDB B+tree

聚簇索引：

索引按照物理实现方式聚簇（聚集）索引既数据叶子节点存储完整的数据记录和非聚簇（非聚集）索引。我们也把非聚集索引称为二级索引或者辅助索引。叶子节点存储主键值

特点：

1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
- 页内的记录是按照主键的大小顺序排成一个单向链表
- 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
- 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表
1. B+树的叶子节点存储的是完整的用户记录。
- 所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列

优点：

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作

缺点：

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键(或者美团的leaf 开源分布式id解决方案)
更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据 俗称回表

限制：

对于mysql数据库只有InnoDB支持聚簇索引 myISAM不支持
由于数据物理存储排序方式只能有一种，所以每个mysql的只能有一个聚簇索引，一般就是主键
如果没有定义主键 innoDB会选择非空的唯一索引代替，如果没有这样的索引，innoDB会隐式的定义一个主键来作为索引
为了充分利用聚簇索引的特性所以innoDB表主键尽量选择有序的顺序id，保证主键顺序增长

问题：为什么我们还需要一次回表操作呢？直接把完整的用户记录放到叶子节点不OK吗？

空间浪费，并且需要影响更新等的效率

非聚簇索引：此图中未体现出主键真实情况，内节点也会存储主键值

聚簇索引与非聚簇索引小结：

联合索引 start：联合索引 end：

InnoDB的B+树索引的注意事项

根页面位置万年不动
内节点中目录项记录的唯一性
一个页面最少存储2条记录

索引的代价

索引是个好东西，可不能乱建，它在空间和时间上都会有消耗：
空间上的代价
- 每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。
时间上的代价
- 每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位,页面分裂,页面回收等操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿

MySQL索引数据结构选择合理性，MySQL索引数据结构为什么使用B+树

从mysql的角度讲，不得不考虑的一个现实问题就是磁盘的IO，如果我们能让索引的数据结构尽量减少磁盘的IO操作，所消耗的时间也就越小，可以说，磁盘的IO操作次数对索引的使用效率非常重要查找都是索引操作，一般来说索引非常大，尤其是关系型数据库，当数据量比较大的时候，索引的大小有可能几个G，为了减少索引在内存的占用，数据库索引都是存储在硬盘上的，我们利用索引查询的时候，不可能把整个索引全部加载到内存，只能按照页加载，那么mysql效率恒量的一个重要参数就是IO次数

全表遍历
hash索引
- show variables like '%adaptive_hash_index%'
- 关于自适应索引的深度理解 juejin.cn/post/694006…
二叉搜索树如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的，极端情况下二叉树近似于退化成链表
AVL树平衡M叉树依然层级过高
B-Tree

B+Tree
R树类似redis的geo 附近的人，空间立体

思考题答案：

B+树的存储能力如何？为何说一般查找行记录，最多只需1~3次IO

innoDB 数据存储结构

页结构

页：磁盘与内存交互基本单位 innoDB 将数据划分为若干个页，innoDB中页的大小默认为16KB 以页作为磁盘和内存之间交互的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。也就是说，在数据库中，不论读一行还是读多行，都是将这些行所在的页进行加载，也就是说，数据库管理存储空间的基本单位是页数据库IO操作最小单位是页。一个页中可以存储多行记录记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取(也就是一次IO操作)只能处理一行数据，效率非常低

索引

使用字符串前缀创建索引 Alibaba 开发手册强制在vachar字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。说明：索引的长度与区分度是一对矛盾体，一般对字符串类型的数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名，索引长度))/count(*)的区分度来确定前缀索引引申出另外一个问题？如果使用了索引列前缀，比方说前边只把address列的前12个字符放到了二级索引中，下边的这个查询可能就有点儿尴尬了 select * from shop group by address limit 10; 因为二级索引中不包含完整的address 列信息，所以无法对前12个字符相同，后边的字符不同的记录进行排序，也就是使用索引前缀的方式无法支持使用索引排序，只能使用文件排序。

限制索引的数目在实际工作中，我们也需要注意平衡，索引的数目不是越多越好，我们需要限制每张表上的索引数量，建议单张表索引数量不超过6个原因：

每个索引都需要占用磁盘空间
索引会影响 insert delete update等语句的性能，因为表中数据更改的同时，索引也会进行调整和更新，会造成负担
优化器在选择如何优化索引查询时，会根据统一信息，对每一个可以用到的索引进行评估，以生成出一个最好的执行计划，如果同时有多个索引都可以用于查询，会增加mysql优化器生成执行计划时间，降低查询性能。

mysql 性能分析工具

查看系统性能参数在MySQL中，可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的性能参数、执行频率。 SHOW STATUS语句语法如下： SHOW [GLOBAL|SESSION] STATUS LIKE '参数';

Version:0.9 StartHTML:0000000105 EndHTML:0000005560 StartFragment:0000000141 EndFragment:0000005520

一些常用的性能参数如下： • connections：连接MySQL服务器的次数。 • uptime：MySQL服务器的上线时间。 • slow_queries：慢查询的次数。 • innodb_rows_read：Select查询返回的行数 • innodb_rows_inserted：执行INSERT操作插入的行数 • innodb_rows_updated：执行UPDATE操作更新的行数 • innodb_rows_deleted：执行DELETE操作删除的行数 • com_select：查询操作的次数。 • com_insert：插入操作的次数。对于批量插入的 INSERT 操作，只累加一次。 • com_update：更新操作的次数。 • com_delete：删除操作的次数。

eg： SHOW STATUS LIKE 'connections'

mysql 慢查询日志 nysql慢查询日志，用来记录在mysql中响应时间超过阈值的语句，具体指运行时间超过 long_query_time值的语句，则会被记录到慢查询语句中，long_query_time 默认值10s 默认情况下 mysql数据库没有开启慢查询日志，需要我们手动来设置这个参数，如果不是调优需要的话，一般不建议开启该参数，因为开启慢查询日志会带来一定的性能影响。

mysqldumpslow 命令分析慢查询日志

查看 SQL 执行成本：SHOW PROFILE！！默认也是不开启的需要手动开启

show profile的常用查询参数： ① ALL：显示所有的开销信息。 ② BLOCK IO：显示块IO开销。 ③ CONTEXT SWITCHES：上下文切换开销。 ④ CPU：显示CPU开销信息。 ⑤ IPC：显示发送和接收开销信息。 ⑥ MEMORY：显示内存开销信息。 ⑦ PAGE FAULTS：显示页面错误开销信息。 ⑧ SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。 ⑨ SWAPS：显示交换次数开销信息。

explain 如何使用！！！

key_len：实际使用到的索引长度，主要针对于联合索引。

上面的覆盖索引少了一步回表操作

索引下推

！！！结论！！！

explain 进一步说明 explain 四种输出格式：传统模式 json格式 tree格式以及可视化输出用户可根据需要选择适用于自己的格式

分析优化器执行计划：trace

MySQL监控分析视图-sys schema

索引优化与查询优化

都有哪些维度可以进行数据库调优？ 1 索引失效，没有充分利用到索引 --索引建立 2 关联查询太多join(设计缺陷或不得已的需求) 3 服务器调优及各个参数设置(缓冲线程数等)调整my.cnf 4 数据过多分库分表

关联查询优化

左连接内连接连接查询咱们就理解成嵌套for循环即可外层for循环又名驱动表
左连接驱动表写在左边

对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
对于内连接来讲，如果表的连接条件中只有一个字段有索引，则有索引的字段的表被作为被驱动表
对于内连接来讲，在两个表的连接条件都存在索引条件下，会选择小表作为驱动表出现(小表驱动大表)

JOIN 语句原理 join方式连接多个表，本质就是各个表之间数据的循环匹配，mysql5.5版本之前，mysql只支持一种表间关联方式，就是嵌套循环(Nested Loop Join)，如果关联表数据量很大，则join关联的执行时间会非常长，在Mysql5.5以后的版本，mysql通过引入BNLJ算法来优化嵌套循环执行。

join 小结论：

整体效率对比 INLJ > BNLJ > SNLJ
永远用小结果集驱动大的结果集(其本质就是减少外层for循环的数据数量) （小的度量单位指的是表行数*每行大小）
为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)
增大join buffer size的大小一次缓存的数据越多，name内层包的扫表次数就越少
减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多）
保证被驱动表的JOIN字段已经创建了索引
需要JOIN的字段，数据类型保持绝对的一致
left join时，选择小表作为驱动表，大表作为被驱动表。减少外层循环的次数
inner join时，mysql会自动将小结果集的表选为驱动表
能够直接多表关联的直接关联，不用子查询(减少查询的趟数)
不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用JOIN来代替子查询
衍生表减不了索引

mysql 5.8 又出现hashJoin

子查询

mysql4.1 开始支持子查询，子查询是mysql的一项重要的功能，可以帮组我们通过一个SQL语句实现比较复杂的查询，但是子查询执行效率不高因为

执行子查询时，mysql需要为内层查询语句的查询结果简历一个临时表然后外层查询语句从临时表中查询记录。查询执行完毕后，在撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询
子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响
对于返回的结果集比较大的子查询，其对查询性能的影响也就越大

建议：在 mysql中，可以使用连接join查询来代替子查询，连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用到索引的话，性能会更好。

尽量不要使用NOT IN 或者 NOT EXISTS 用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

排序优化

问题：在WHERE条件字段上加索引，但是为什么在ORDER BY字段上还要加索引呢？回答：在mysql中，支持两种排序方式，分别是FileSort 和 Index排序

index 排序中，索引可以保证数据的有序性，不需要在进行排序，效率更高
FileSort 排序则一般在内存中进行排序，占用CPU较多，如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低

优化建议：

SQL中，可以在where子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序，当然，某些情况下全表扫描，或者FileSort排序不一定比索引慢，但总的来说，我们还是要避免，以提高查询效率
尽量使用index完成order by排序，如果where 和 order by 后面是相同的列就使用单列索引列，如果不同就使用联合索引
无法使用index时候，需要对FileSort方式进行调优
order by 时规则不一致，索引失效(顺序错，不索引方向反，不索引)
无过滤不索引
无过滤不索引！！结论！！

上面有了 limit 用了索引

filesort算法：双路排序和单路排序排序的字段若不在索引列上，则filesort会有两种算法：双路排序和单路排序
- 双路排序：慢 mysql在4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取数据输出从磁盘取排序字段，在buffer进行排序，在从磁盘取其他字段取一批数据，要对磁盘进行两次扫描，重所周知，io是很耗时的，所以在mysql4.1后出现了单路排序
- 单路排序：块从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成了顺序IO，但是他会使用更多的空间，因为它把每一行都保存在内存中了单路排序
  单路排序

实战！实战！

group by

group by使用索引的原则几乎跟order by一致，group by即使没有过滤条件用到索引，也可以直接使用索引
group by先排序后分组，遵照最佳左前缀法则·
当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置
where效率高于having，能写在where限定条件就不要写在having里了
减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序段去做。order by。group by。distinct。这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的
包含了 order by。group by。distinct。这些查询的语句，where条件过滤出来的结果集请保在1000行以内，否则sql会很慢

优化分页查询

覆盖索引

理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行，毕竟索引叶子节点存储了他们索引的数据，当能通过读取索引就可以得到想要的数据，那就不需要读取行了，一个索引包好了满足查询结果的数据叫做覆盖索引理解方式一：非聚簇索引的一种形式，它包含在查询里的select join和where子句用到的所有列(既建索引的字段正好是覆盖查询条件所涉及的字段)

简单来说索引+主键包含select 到 from之间查询的列

索引条件下推 ICP

Index Condition Pushdown(ICP) 是msql5.6中的新特性，是一种在存储引擎使用索引过滤数据的优化方式如果没有ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给mysql服务器，由mysql服务器评估where后面的条件是否保留行。启用ICP后，如果部分where条件可以仅使用索引中的列进行筛选，则mysql服务器会把这部分where条件放到存储引擎筛选，然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行好处:ICP可以减少存储引擎必须访问基表的次数和mysql服务器必须访问存储引擎的次数。但是，ICP的加速效果取决于在存储引擎通过ICP筛选掉的数据比例

其他查询优化

主键分布式设计

目前主流解决方案

号段模式
雪花算法

数据库设计三范式与反三范式

第一范式确保每列保持原子性的但是这个原子性也是需要考虑时间业务的，比如配置表，大json
第一范式
第二范式确保每列都和主键完全依赖 ！！！尤其在复合主键的情况下，非主键部分不应该依赖于部分主键！！！
第二范式
第三范式（字段冗余）确保每列都和主键列直接相关，而不是间接相关
第三范式
BCNF 巴斯范式人们在第三范式基础上进行了改进，提出了巴斯范式。巴斯范式被认为没有新的设计规范加入，只是对第三范式规范要求更强，使得数据库冗余度更小。若一个关系达到了第三范式，并且它只有一个候选键，或者它的每个候选键都是单属性，则该关系达到了巴斯范式一般来说一个数据库设计达到了第三范式或者巴斯范式就可以了。

BCNF 巴斯范式
小结
小结
反范式化有的时候不能简单按照范式要求设计数据表，因为有的数据看似冗余，其实对业务来说十分重要，这个时候，我们就要遵循业务优先原则，首先满足业务需求，在尽量减少冗余。如果数据库中的数据量比较大，系统的UV PV访问频次比较高，则完全按照mysql的三大范式设计数据库，读取数据会产生大量的关联查询，在一定程度上会影响数据库的读性能，如果我们想对查询效率进行优化，反范式优化也是一种优化思路，此时，可以通过在数据表中增加冗余字段来提高数据库的读性能
反范式化
实战案例
实战案例