索引
一、全表扫描
没有使用索引的时候，数据的查询需要进行多次IO读写，这样的性能较差——全表扫描的过程。
二、索引
为数据库的某个字段创建索引，相当是为这个字段的内容创建了一个目录。通过这个目录可以快速的实现数据的定位，也就是通过索引能够快速的找到某条数据所在磁盘的位置。
三、索引存放位置
- InnoDB存储引擎的表：将索引和数据存放在同一个文件里。（为什么？有什么优势？）*ibd
- MyISAM存储引擎的表：索引和数据分开两个文件来存储。 索引：*.MYI； 数据：MYD
四、索引分类
1、主键索引：主键自带索效果，也就意味着通过主键来查询表中的记录，性能是非常好的。
2、普通索引：为普通列创建的索引。
索引名一般格式: **idx_字段名**
create index 索引名 on 表明(字段);
例子：
create index idx_participant_ on cal_resource_permission(participant_);3、唯一索引：就像是唯一列，列中的数据是唯一的。比普通索引的性能要好。
唯一索引名称：idx_unique_列明
create unique index 唯一索引名称 on 表明(列名);
例子：
create unique index idx_unique_resource_id_ on cal_resource_permission(resource_id_);4、联合索引（组合索引）：一次性为表中的多个字段一起创建索引,最左前缀法则（如何命中联合索引中的索引列）。注意：一个联合索引建议不要超过5个列
格式：联合索引名一般格式: idx_字段1_字段2_字段3
create index 联合索引名 on 表明(字段1,字段2,字段3);
例子：
create index idx_resource_scope_type on cal_resource_permission(resource_id_,scope_,type_id_);5、全文索引：进行查询的时候，数据源可能来自于不同的字段或者不同的表。比如去百度中查询数据，yanxizhu，来自于网页的标题或者网页的内容。
MyISAM存储引擎支持全文索引。
在实际生产环境中，并不会使用MySQL提供的MyISAM存储引擎的全文索引功能来是实现全文查找。而是会使用第三方的搜索引擎中间件比如ElasticSearch（比较多）、Solr。
索引使用的数据结构
使用索引查找数据性能很快，避免了全表扫描的多次磁盘IO读写。但是我们发现，使用索引实际上也需要在索引中查找数据，而且数据量是一样的，那么凭什么索引就能快呢？这就跟索引使用了哪种数据结构支持快速查找。
什么叫数据结构：存放数据的结构。比如：数组、链表、栈、堆、队列等等这些概念。
一、线性表
线性的维护数据的顺序。对于线性表来说，有两种数据结构来支撑：
- 线性顺序表：相邻两个数据的逻辑关系和物理位置是相同的。
线性链式表：相邻两个数据的逻辑关系和物理存放位置没有关系。数据是有先后的逻辑关系，但是数据的物理存储位置并不连续。
线性链式表又分为单项链表和双向链表：
- 单向链表：能够通过当前结点找到下一个节点的位置，以此来维护链表的逻辑关系
结点结构：数据内容+下一个数据的指针
- 双向链表：能够通过当前结点找到上一个或下一个节点的位置，双向都可找。
结点结构：上一个数据的指针+数据内容+下一个数据的指针。
时间复杂度（比较次数）/空间复杂度（算法需要使用多少个变量空间）
顺序表和链式表的区别：
顺序表--数组：进行数据的查询性能（可以通过数组的索引/下标），数组的查询性能非常好，时间复杂度是0（1）
数组的增删性能是非常差。链式表--链表：查询的性能是非常差的：时间复杂度是O（n），增删性能是非常好。
二、栈、队列、串、广义表
栈分为：顺序栈、链式栈
顺序栈
链式栈
栈特点:先进后出，FIFO。
队列分为：顺序队列、链式队列
顺序队列
链式队列
队列特点：先进先出。
串：String定长串、StringBuffer/Stringbuilder动态串
广义表：更加灵活的多维数组，可以在不同的元素中创建不同的维度的数组。
树
查找树的查找性能是明显比线性表的性能要好，有这么几种树：
多叉树、二叉树、二又查找树、平衡二叉树、红黑树、B树、B+树
一、多叉树：非二叉树
二、二叉树：一个结点最多只能有2个子结点，可以是0、1、2子结点。
三、二叉查找树：
二又查找树的查找性能是ok的。
查询性能跟树的高度有关，树的高度又根你插入数据的顺序有关系。
特点：二又树的根结点的数值是比所有左子树的结点的数值大，比右子树的几点的数值小。这样的规律同样满足于他的所有子树。
四、平衡二又树，又称为AVL树
实际上就是遵循以下两个特点的二叉树：
每棵子树中的左子树和右子树的深度差不能超过1；
二叉树中每棵子树都要求是平衡二叉树；
其实就是在二又树的基础上，若树中每棵子树都满足其左子树和右子树的深度差都不超过1，则这棵二又树就是平衡二又树。
二又排序树转化为平衡二叉树
为了排除动态查找表中不同的数据排列方式对算法性能的影响，需要考虑在不会破坏二又排序树本身结构的前提下，将二又排序树转化为平衡二叉树，左旋、右旋、双向（先左后右、先右后左）。
五、红黑树
红黑树是一种特化的AVL树（平衡二叉树）（平衡二叉树的一种体现），都是在进行插入和删除操作时通过特定操作保持二又查找树的平衡，从而获得较高的查找性能。
在二又查找树强制一般要求以外，对于任何有效的红黑树我们增加了如下的额外要求：
性质1.结点是红色或黑色。
性质2.根结点是黑色。
性质3.不可能有连在一起的红色节点。
性质4.每个红色结点的两个子结点都是黑色。叶子结点都是黑色（nil-黑色的空节点）
这些约束强制了红黑树的关键性质：从根到叶子的最长的可能路径不多于最短的可能路径的两倍长。结果是这个树大致上是平衡的。因为操作比如插入、删除和查找某个值的最坏情况时间都要求与树的高度成比例，这个在高度上的理论上限允许红黑树在最坏情况下都是高效的，而不同于普通的二叉查找树。
平衡二又树为了维护树的平衡，在一旦不满足平衡的情况就要进行自旋，但是自旋会造成一定的系统开销。因此红黑树在自旋造成的系统开销和减少查询次数之间做了权衡。因此红黑树有时候并不是一颗平衡二叉树。
红黑树已经是在查询性能上得到了优化，但索引依然没有使用红黑树作为数据结构来存储数据，因为红黑树在每一层上存放的数据内容是有限的，导致数据量一大，树的深度就变得非常大，于是查询性能非常差。因此索引没有使用红黑树。
六、B树
B树允许一个结点存放多个数据。这样可以使更小的树的深度来存放更多的数据。但是，B树的一个结点中到底能存放多少个数据，决定了树的深度。
七、B+树
特点：
- 非叶子结点冗余了叶子结点中的键。
- 叶子结点是从小到大、从左到右排列的。
- 叶子结点之间提供了指针，提高了区间访问的性能。
- 只有叶子结点存放数据。非叶子结点是不存放数据的，只存放键。
八、哈希表（散列表）
使用哈希表来存取数据的性能是最快的，O（1），但是不支持范围查找（区间访问）。
InnoDB和MyISAM的区别
InnoDB和MyISAM都是数据库表的存储引擎。那么在互联网公司，或者追求查询性能的场景下，都会使用lnnoDB作为表的存储引擎。
为什么？
1、InnoDB引擎
把索引和数据存放在一个文件中，通过找到索引后就能直接在索引树上得叶子节点中获取完整得数据。------聚集索引
可以实现行锁/表锁。
2、MyISAM存储引擎
把索引和数据存放在两个文件中，查找到索引后还要去另一个文件中找数据，性能会慢一些——非聚集索引。
除此之外，MyISAM天然支持表锁，而且支持全文索引。|
关键点：InnoDB聚集索引与MyISAM-非聚集索引
面试题
1.问题一：为什么非主键索引的叶子节点存放的数据是主键值
如果普通索引中不存放主键，而存放完整数据，那么就会造成：
数据冗余：虽然提升了查询性能，但是需要更多的空间来存放冗余的数据
维护麻烦：一个地方修改数据，需要在多棵索引树上修改。
2.问题二：为什么InnoDB表必须创建主键
创建InnoDB表不使用主键能创建成功吗？如果能创建功能，能不能为这张表的普通列创建索引？
如果没有主键，MySQL优化器会给一个虚拟的主键，于是普通索引会使用这个虚拟主键——也会造成性能开销。为了性能考虑，和设计初衷，那么创建表的时候就应该创建主键。
3.问题三：为什么使用主键时推荐使用整型的自增主键
1）为什么要使用整型：
主键-主键索引树-树里的叶子结点和非叶子结点的键存放的是主键的值，而且这颗树是一个二又查找树。数据的存放是有大小顺序的。
·整型：大小顺序是很好比较的
·字符串：字符串的自然顺序的比较是要进行一次编码成为数值后再进行比较的。（字符串的自然顺序，AZ）
uuid随机字符串
2）为什么要自增：
如果不用自增：（1016。200。18。29）使用不规律的整数来作为主键，那么主键索引树会使用更多的自旋次数来保证树索引树的叶子节点中的数据是从小到大-从左到右排列，因此性能必然比使用了自增主键的性能要差！
联合索引和最左前缀法则
1.联合索引的特点
在使用一个索引来实现多个表中字段的索引效果。
2.联合索引是如何存储的
3.最左前缀法则
最左前缀法则是表示一条sql语句在联合索引中有没有走索引（命中索引/不会全表扫描）
#创建联合索引
create index idx_name_repaymentAmount_userName on pay(name,repaymentAmount,userName);
#sql是否走联合索引
select * from pay where name="早餐"; //走索引
select * from pay where name="早餐" and repaymentAmount=5.50; //走索引
select * from pay where name="早餐" and repaymentAmount=5.50 and userName="邓凌"; //走索引
select * from pay where repaymentAmount=5.50; //不走索引
select * from pay where name="早餐" and userName="邓凌"; //走一个索引
select * from pay where userName="邓凌"; //不走索引
select * from pay where name="早餐" and userName="邓凌" and repaymentAmount=5.50 ; //走索引，因为mysql内部有优化器SQL优化
SQL优化的目的是为了SQL语句能够具备优秀的查询性能，实现这样的目的有很多的途径：
- 工程优化如何实现：数据库标准、表的结构标准、字段的标准、创建索引
- SQL语句的优化：当前SQL语句有没有命中索引。
一、工程优化
基础规范
- 表存储引擎必须使用InnoDB
表字符集默认使用utf8，必要时候使用utf8mb4
- 通用，无乱码风险，汉字3字节，英文1字节
- utff8mb4是utf8的超集，有存储4字节例如表情符号时，使用它
禁止使用存储过程，视图，触发器，Event
- 对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层
- 调试，排错，迁移都比较困难，扩展性较差
- 禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径
- 禁止在线上环境做数据库压力测试
- 测试，开发，线上数据库环境必须隔离
命名规范
库名，表名，列名必须用小写，采用下划线分隔 tb_book t_book
- abc，Abc，ABC都是给自己埋坑
库名，表名，列名必须见名知义，长度不要超过32字符
- tmp，wushan谁TM知道这些库是干嘛的
- 库备份必须以bak为前缀，以日期为后缀
- 从库必须以-s为后缀
- 备库必须以-ss为后缀
表设计规范
- 单实例表个数必须控制在2000个以内·单表分表个数必须控制在1024个以内
表必须有主键，推荐使用UNSIGNED整数为主键
- 删除无主键的表，如果是row模式的主从架构，从库会挂住
禁止使用物理外键，如果要保证完整性，应由应用程式实现
- 外键使得表之间相互耦合，影响 update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈
- 建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据（具体参考：《如何实施数据库垂直拆分》）
列设计规范
- 根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节
根据业务区分使用char/varchar
- 字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高
- 字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间
根据业务区分使用datetime/timestamp
- 前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime
必须把字段定义为NoT NULL并设默认值
- NULL的列使用索引，索引统计，值都更加复杂，MySQL 更难优化
- NULL需要更多的存储空间
- NULL只能采用Is NUL或者IS NoT NULL，而在=/！=/in/not in 时有大坑
- 使用INT UNSIGNED存储IPv4，不要用char（15）
使用varchar（20）存储手机号，不要使用整数
- 牵扯到国家代号，可能出现+/-/（）等字符，例如+86
- 手机号不会用来做数学运算
- varchar 可以模糊查询，例如like'138%'
使用TINYINT来代替ENUM。
- ENUM增加新值要进行DDL操作
索引规范
- 唯一索引使用uniq_[字段名]来命名
- 非唯一索引使用idx_[字段名]来命名
单张表索引数量建议控制在5个以内
- 互联网高并发业务，太多索引会影响写性能
- 生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL 选择不到最优索引
- 异常复杂的查询需求，可以选择Es等更为适合的方式存储
组合索引字段数不建议超过5个
- 如果5个字段还不能极大缩小row范围，八成是设计有问题
- 不建议在频繁更新的字段上建立索引
非必要不要进行JoIN查询，如果要进行JoIN查询，被JorN的字段必须类型相同，并建立索引
- 踩过因为JoIN字段类型不一致，而导致全表扫描的坑么？
- 理解组合索引最左前缀原则，避免重复建设索引，如果建立了（a，b，c），相当于建立了（a），（a，b），（a，b，c）
二、Explain执行计划--SQL优化神奇
1、Explain介绍
在完成工程结构的优化之后，数据库、表、表中的字段及表的索引，都能够支持海量数据的快速查找。但是查找依然需要通过SQL语句来实现，因此性能优秀的SQL语句是能够走索引，快速查找到数据的。性能不Ok的SQL语句，就不会走索引，导致全表扫描。
阿里云rds提供了慢sql的查询功能。找到慢SQL后，如何做优化，以及如何查询一条SQL语句是否是慢SQL，那就可以通过Explain工具。
通过在SQL语句前面加上Explain关键来执行，于是就能看出当前SQL语句的执行性能。
得知道当前系统里有哪些SQL是慢SQL，查询性能超过1s的sql，然后再通过Explain工具可以对当前SQL语句的性能进行判断——为什么慢，怎么解决。
要想知道哪些SQL是慢SQL，有两种方式，一种是开启本地MySQL的慢查询日志；另一种是阿里云提供的RDS（第三方部署的MySQL服务器），提供了查询慢SQL的功能。
explain select * from pay where name like "早%";
2、MySQL内部优化器
在SQL查询开始之前，MySQL内部优化器会进行一次自我优化，让这一次的查询性能尽可能的好。
查看内部优化器优化后结果：
explain select * from pay where name like "早%";
show WARNINGS;内部优化器优化后结果：
/* select#1 */ select `family`.`pay`.`id` AS `id`,`family`.`pay`.`name` AS `name`,`family`.`pay`.`repaymentAmount` AS `repaymentAmount`,`family`.`pay`.`repaymentTime` AS `repaymentTime`,`family`.`pay`.`repaymentUser` AS `repaymentUser`,`family`.`pay`.`userName` AS `userName`,`family`.`pay`.`updateTime` AS `updateTime`,`family`.`pay`.`description` AS `description` from `family`.`pay` where (`family`.`pay`.`name` like '早%')3、Explain细节
关闭MySQL对衍生表的优化：
set session optimizer_switch='derived_merge=off'一、select_type
该列描述了查询的类型：
simple：简单查询
primary：外部的主查询
derived：在from后面进行的子查询，会产生衍生表
subquery：在from的前面进行的子查询
union：进行的联合查询
explain select (select 1 from type where id=1) from (select * from pay where id=1) des;
show WARNINGS;结果：
derived
##第一条执行sql
select * from pay where id=1第一条执行的sql是from后面的子查询，该子查询只要在from后面，就会生成一张衍生表，因此他的查询类型：derived
subquery
select 1 from type where id=1在select之后from之前的子查询
primary
最外部的查询
simple
不包含子查询的简单的查询
union
explain
select * from pay where id=1
union
select * from pay where id=2结果：
使用union进行的联合查询
二、table列
这一列表示该sql正在访问哪一张表。也可以看出正在访问的衍生表
三、tyge列
通过Type列，可以直接的看出SQL语句的查询性能，性能从大到小的排列：
null>system>const>eq_ref>ref>range>index>ALL一般情况下我们要保证我们的sql类型的性能是range级别。不同的级别的情况：
null
性能最好的，一般在查询时使用了聚合函数，于是直接从索引树里获取数据，而不用查询表中的记录。
explain select min(id) from pay;
const
在进行查询时，查询的条件，使用了主键列或者唯一索引列的值与常量进行比较，这种性能是非常快的，所以是const
system
是const的特殊情况，一般在衍生表里，直接匹配一条记录，就是system
eq_ref
如果顺序一直，按按顺序执行。在进行多表连接查询时。如果查询条件是使用了主键进行比较，那么当前查询类型是eq_ref
EXPLAIN select * from pay p left join type t on t.id=p.id;
ref
简单查询：
使用普通索引列作为查询条件
EXPLAIN select * from pay where name="早餐";
复杂查询里：
在进行连接查询时，连接查询的条件中使用了本表的联合索引列，因此这种类型的sql就是ref
range
在索引列上使用了范围查找，性能是ok的。
EXPLAIN select * from pay where id<5;index
在查询表中的所有的记录，但是所有的记录可以直接从索引树上获取，因此这种sql的查询类型就是index（pay表所有列都有索引）
EXPLAIN select name from payall
没有走索引，进行全表扫描。
全表扫描。就是要从头到尾对表中的数据扫描一遍。这种查询性能是一定要做优化的。
4、id列
在多个select中，id越大越先执行，如果id相同。上面的先执行。
5、possible_keys
这一次查询可能使用到的索引（名称）。为什么要设计possiblekey这一列。因为在实际MySQL内部优化器做优化选择时，虽然我们写的sql语句是使用了索引列，但是MySQL内部优化器发现，使用索引列查找的性能并没有比全表扫描的性能要好（可以通过trace工具来查看），于是MySQL内部优化器就选择使用全表扫描。
explain select * from pay where name like "早%"结果：
可能使用idx_name_repaymentAmount_userName联合索引，实际上没用到。一共有142条记录，使用索引可能也需要扫描142条数据，使用索引效率可能还没有使用全表扫描快。
6、key
实际sql使用的索引
7、rows列
该sql语句可能要查询的数据条数
8、key_len列
键的长度，通过这一列可以让我们知道当前命中了联合索引中的哪几列。
name长度是74，也就是当看到key-len是74，表示使用了联合索引中的name列。
ken_len计算规则：
-字符串
1.char（n）：n字节长度
2.varchar（n）：2字节存储字符串长度，如果是utf-8，则长度3n+2
-数值类型
1.tinyint：1字节
2.smallint：2字节
3.int：4字节
4.bigint：8字节
--时间类型
1、date：3字节
2、timestamp：4字节
3、datetime：8字节
如果字段允许位null，需要1字节记录是否位null。
索引最大长度768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，讲前半部分的字符提取出来做索引。
9、extra
extra列提供了额外的信息，是能够帮助我们判断当前sql的是否使用了覆盖索引、文件排序、使用了索引进行查询条件等等的信息。
using index
覆盖索引：所谓的覆盖索引，指的是当前查询的所有数据字段都是索引列，这就意味着可以直接从索引列中获取数据，而不需要进行查表。
使用了覆盖索引
explain select repaymentAmount from pay where name ="早餐";
using where
where的条件没有使用索引列。这种性能不ok的，我们如果条件允许可以给列设置索引，也同样尽可能的使用覆盖索引。
explain select * from pay where name like "早%"
using index condition
查询的列没有完全被索引覆盖，并且where条件中使用普通索引。
using temporary
会创建临时表来执行，比如在没有索引的列上执行去重操作，就需要临时表来实现。
explain select distinct(description) from pay;
这种情况可以通过给列加索引进行优化。
using filesort
MySQL对数据进行排序，都会使用磁盘来完成，可能会借助内存，设计到两个概念：单路排序、双路排序
explain select * from pay ORDER BY description;
select tables optimized away
当直接在索引列上使用聚合函数，意味着不需要操作表。
explain select min(id) from pay;
编写SQL注意点
等值匹配：
下面3个都命中：
最左前缀法：
1、2没命中，4name，age命中。
不能在索引列上做计算、函数、类型转换：
日志查找如何处理：
尽量使用覆盖索引
使用不等于(!=或者<>)会导致全表扫描
使用is null、is not null会导致全表扫描
使用like以通配符开头('%abc..')会导致全表扫描
字符串不加单引号会到会导致全表扫描
少用or或in，MySQL内部优化器可能不走索引
in进行几千条数据查询时，解决方案：在后端通过多线程countDownlatch将几千条数据拆分，通过多个线程每个查询1000条进行结果汇总。
范围查询优化
Trace工具
在执行计划中我们发现有的sql会走索引，有的sql即使明确使用了索引也不会走索引。这是因为mysql的内部优化器任务走索引的性能比不走索引全表扫描的性能要差，因此mysql内部优化器选择了使用全表扫描。依据来自于trace工具的结论。
1、开启Trace
set session optimizer_trace="enabled=on",end_markers_in_json=on; --开启Traceexplain select * from pay where name>1; --查询
SELECT * FROM information_schema.OPTIMIZER_ ; --获得TRACE信息Trace信息：
{
"steps": [
{
"join_preparation": { --- 阶段1：准备阶段
"select#": 1,
"steps": [
{ ----sql变成成下面这种
"expanded_query": "/* select#1 */ select `pay`.`id` AS `id`,`pay`.`name` AS `name`,`pay`.`repaymentAmount` AS `repaymentAmount`,`pay`.`repaymentTime` AS `repaymentTime`,`pay`.`repaymentUser` AS `repaymentUser`,`pay`.`userName` AS `userName`,`pay`.`updateTime` AS `updateTime`,`pay`.`description` AS `description` from `pay` where (`pay`.`name` > 1)"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { ----阶段2：进入优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { ---条件处理
"condition": "WHERE",
"original_condition": "(`pay`.`name` > 1)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`pay`.`name` > 1)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`pay`.`name` > 1)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`pay`.`name` > 1)"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ -----表依赖详情
{
"table": "`pay`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`pay`",
"range_analysis": {
"table_scan": {--表的扫描
"rows": 142, --扫描行数
"cost": 31.5 --花费时间
} /* table_scan */,
"potential_range_indexes": [ ----（关键）可能使用的索引
{
"index": "PRIMARY", ---主键索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_repaymentAmount_userName", ---联合索引
"usable": true, --是否可用
"key_parts": [
"name",
"repaymentAmount",
"userName",
"id"
] /* key_parts */
},
{
"index": "idx_id",
"usable": false,
"cause": "not_applicable"
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [---分析执行计划
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`pay`",
"best_access_path": { ---最优访问路径
"considered_access_paths": [ --最后选择访问的路径
{
"rows_to_scan": 142, --全表扫描行数
"access_type": "scan", --全表扫描
"resulting_rows": 47.329, --结果的行数
"cost": 29.4, ---花费时间
"chosen": true --选择这种方式
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 47.329,
"cost_for_plan": 29.4,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`pay`.`name` > 1)",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`pay`",
"attached": "(`pay`.`name` > 1)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`pay`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_explain": {
"select#": 1,
"steps": [
] /* steps */
} /* join_explain */
}
] /* steps */
}常见优化方案、
1、Order by
在Order by中，如果排序会造成文件排序（在磁盘中完成排序，这样的性能会比较差），那么就说明sql没有命中索引，怎么解决？可以使用最左前缀法则，让排序遵循最左前缀法则，避免文件排序。
优化手段：
如果排序的字段创建了联合索引，那么尽量在业务不冲突的情况下，遵循最左前缀法则来写排序语句。
如果文件排序没办法避免，那么尽量想办法使用覆盖索引。all-->index
2.Group by优化
group by 本质上是先排序后分组，所以排序优化参考Order by优化。
3.文件排序的执行原理
MySQL在进行文件排序时，会设置一个缓冲区，比较要排序的所有列的数据大小之和，与max_length_for_sort_data（默认是1024个字节）进行比较。如果比系统变量小，那么执行单路排序，反正执行双路排序。
单路排序：把所有的数据扔到sort_buffer内存缓冲区中，进行排序，然后结束
双路排序：取数据的排序字段和主键字段，在内存缓冲区中排序完成后，将主键字段做一次回表查询，获取完整数据。
4、分页查询优化
优化方式一：比较少见，因为有curd
注意：主键是连续的，如果删除了，获取到的数据条数不对。
优化方式二：先覆盖查询，在到小范围里面全表扫描
5、join连表查询优化
方式一：
在join查询中，如果关联字段建立了索引，mysql就会使用nlj算法，去找小表（数据量比较小的表）作为驱动表，先从驱动表中读一行数据，然后拿这一行数据去被驱动表（数据量比较大的表）中做查询。这样的大表和小表是由mysql内部优化器来决定的，跟sql语句中表的书写顺序无关。—NLJ算法（nested loop join：嵌套循环join）
方式二：
如果没有索引，会创建一个join buffer内存缓冲区，把小表数据存进来（为什么不存大表，因为缓冲区大小限制，及存数据消耗性能的考虑），用内存缓冲区中100行记录去和大表中的1万行记录进行比较，比较的过程依然是在内存中进行的。索引join buffer起到了提高join效率的效果。-BNLJ算法（block nested loop join：块嵌套循环join）
结论：如果使用join查询，那么join的两个表的关联字段一定要创建索引，而且字段的长度类型一定是要一致的（在建表时就要做好），否则索引会失效，会使用BNLJ算法，全表扫描的效果。
6、in和exits优化
在sql中如果A表是大表，B表是小表，那么使用in会更加合适。反之应该使用exists。
in：前提B数据量<A数据量
select * from A where id in (select id from B);exists: B的数据量>A的数据量
select * from A where exists (select id from B where B.id=A.id);select id from B where B.id=A.id ---会返回true/false,true才查询B7、count优化
对于count的优化应该是架构层面的优化，因为count的统计是在一个产品会经常出现，而且每个用户访问，所以对于访问频率过高的数据建议维护在缓存中。
锁的定义和分类
1、锁的定义
锁是用来解决多个任务（线程、进程）在并发访问同一共享资源时带来的数据安全问题。虽然使用锁解决了数据安全问题，但是会带来性能的影响，频繁使用锁的程序的性能是必然很差的。
对于数据管理软件MySQL来说，必然会到任务的并发访问。那么MySQL是怎么样在数据安全和性能上做权衡的呢？——MVCC设计思
2、锁的分类
1、）从性能上划分：乐观锁和悲观锁
悲观锁：悲观的认为当前的并发是非常严重的，所以在任何时候操作都是互斥。保证了线程的安全，但牺牲了并发性。
乐观锁：乐观的认为当前的并发并不严重，因此对于读的情况，大家都可以进行，但是对于写的情况，再进行上锁。以CAS自旋锁在某种性能是ok的，但是频繁自旋会消耗很大的资源。
2、）从数据的操作细粒度上划分：表锁和行锁
表锁：对整张表上锁
行锁：对表中的某一行上锁。
3、）从数据库的操作类型上划分：读锁和写锁，这2中锁都是悲观锁。
读锁（共享锁）：对于同一行数据进行”读“来说，是可以同时进行但是写不行。
写锁（拍他锁）：在上了写锁之后，及释放写锁之前，在整个过程中是不能进行任何的其他并发操作（其他任务的读和写是都不能进行的）。
表锁：
对整张表进行上锁。MyISAM存储引擎是天然支持表锁的，也就是说在MyISAM的存储引擎的表中如果出现并发的情况，将会出现表锁的效果。MyISAM不支持事务。
读锁：其他任务可以进行读，但是不能进行写
写锁：其他任务不能进行读和写。
行锁：
MyISAM只支持表锁，但不支持行锁，InnoDB可以支持行锁。
在并发事务里，每个事务的增删改的操作相当于是上了行锁。
MVCC设计思想
MySQL为了权衡数据安全和性能，使用了MVCC多版本并发控制的设计。MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。
1、事务的特性
原子性：一个事务是一个最小的操作单位（原子），多条sql语句在一个事务中要么同时成功，要么同时失败。
一致性：事务提交之前和回滚之后的数据是一致的。
持久性：事务一旦提交，对数据的影响是持久的。
隔离性：多个事务在并发访问下，提供了一套隔离机制，不同的隔离级别会有不同的并发效果。
2、事务的隔离级别
幻读：开启2个事务（隔离级别位可重复读），2个事务进行查询，第一个事务插入了一条记录，然后提交。第二个事务，再次插入相同id记录时，提示id重复，查询却看不到第一个事务插入的记录。这种现象就是幻读(虚度).解决办法，将隔离级别设置成Serializable串行化，但是却西能了性能。另一种解决方案：通过上行锁来解决虚读问题。
MVCC思想解读：
MySQL在读和写的操作中，对读的性能做了并发性的保障，让所有的读都是快照读，对于写的时候，进行版本控制，如果真实数据的版本比快照版本要新，那么写之前就要进行版本（快照）更新，这样就可以既能够提高读的并发性，又能够保证写的数据安全。
死锁和间歇锁
1、死锁
所谓的死锁，就是开启的锁没有办法关闭，导致资源的访问因为无法获得锁而处于阻塞状态。
演示：事务A和事物B相互持有对方需要的锁而不释放，造成死锁的情况。
2、间隙锁
行锁只能对某一行上锁，如果相对某一个范围上锁，就可以使用间隙锁。间隙锁给的条件where id>13 and id<19，会对13和19所处的间隙进行上锁。
![[ERR] 1067 - Invalid default value for 'created_date_' [ERR]](https://www.yanxizhu.com/usr/themes/Joe/assets/thumb/24.jpg)
评论 (0)