mysql

mysql的调优

explain

① id列

id列的编号是select的序列号，有几个select就有几个id，并且id是按照select出现的顺序增长的，id列的值越大优先级越高，id相同则是按照执行计划列从上往下执行，id为空则是最后执行。

② select_type列

1. simple：不包含子查询和union的简单查询
2. primary：复杂查询中最外层的select
3. subquery：包含在select中的子查询（不在from的子句中）
4. derived：包含在from子句中的子查询。mysql会将查询结果放入一个临时表中，此临时表也叫衍生表。
5. union：在union中的第二个和随后的select，UNION RESULT为合并的结果

③ table列

表示当前行访问的是哪张表。当from中有子查询时，table列的格式为，表示当前查询依赖id=N行的查询，所以先执行id=N行的查询，如上面select_type列图4所示。当有union查询时，UNION RESULT的table列的值为<union1,2>，1和2表示参与union的行id。

④ partitions列

查询将匹配记录的分区。 对于非分区表，该值为 NULL。

type列

此列表示关联类型或访问类型。也就是MySQL决定如何查找表中的行。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all。

如果为all就是全表扫描，必须优化sql 至少是range范围，想办法调整sql语句

NULL：MySQL能在优化阶段分解查询语句，在执行阶段不用再去访问表或者索引。

system、const：MySQL对查询的某部分进行优化并把其转化成一个常量（可以通过show warnings命令查看结果）。system是const的一个特例，表示表里只有一条元组匹配时为system。

eq_ref：主键或唯一键索引被连接使用，最多只会返回一条符合条件的记录。简单的select查询不会出现这种type。

ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引和某个值比较，会找到多个符合条件的行。

range：通常出现在范围查询中，比如in、between、大于、小于等。使用索引来检索给定范围的行。

index：扫描全索引拿到结果，一般是扫描某个二级索引，二级索引一般比较少，所以通常比ALL快一点。

ALL：全表扫描，扫描聚簇索引的所有叶子节点。

possible_keys列

此列显示在查询中可能用到的索引。如果该列为NULL，则表示没有相关索引，可以通过检查where子句看是否可以添加一个适当的索引来提高性能。

key列

此列显示MySQL在查询时实际用到的索引。在执行计划中可能出现possible_keys列有值，而key列为null，这种情况可能是表中数据不多，MySQL认为索引对当前查询帮助不大而选择了全表查询。如果想强制MySQL使用或忽视possible_keys列中的索引，在查询时可使用force index、ignore index。

⑧ key_len列

此列显示MySQL在索引里使用的字节数，通过此列可以算出具体使用了索引中的那些列。索引最大长度为768字节，当长度过大时，MySQL会做一个类似最左前缀处理，将前半部分字符提取出做索引。当字段可以为null时，还需要1个字节去记录。

⑨ ref列

此列显示key列记录的索引中，表查找值时使用到的列或常量。常见的有const、字段名

⑩ rows列

此列是MySQL在查询中估计要读取的行数。注意这里不是结果集的行数。

Extra列

1）Using index：使用覆盖索引（如果select后面查询的字段都可以从这个索引的树中获取，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值，这种情况一般可以说是用到了覆盖索引）。

2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖。

3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个查询的范围。

4）Using temporary：MySQL需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的。

5）Using filesort：将使用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。

6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时。

读取28的值就走三次IO，使用的是分块读取，一次读取16kb，也就读取48kb的值，这就是b树

b树与b+树的区别

1、b+树只有在叶子节点中存放data，非叶子节点不存放数据 2、一般是几层的b+树？

一般情况下，3，4层b+树足以支撑千万级别的数据

3、mysql索引选择的时候选择int还是varchar

key要尽量小的占用存储空间

4、如果id的int类型，要不要自增

满足业务场景的情况下，尽可能的自增

5、数据迁移的时候，将索引关掉

b树主要的特点：

1.关键字分布在整颗树

2.任何一个关键字出现且只出现在一个结点中

3.搜索有可能在非叶子结点结束；

4.其搜索性能等价于在关键字全集内做一次二分查找；

b+树的特点：

1.有n棵子树的非叶子结点不保存数据，只用来索引，所有数据都保存在叶子节点（b树是每个关键字都保存数据）。

2.所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小到大顺序链接。

3.所有的非叶子结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。

4.通常在b+树上有两个头指针，一个指向根结点，一个指向关键字最小的叶子结点。

5.同一个数字会在不同节点中重复出现，根节点的最大元素就是b+树的最大元素。

---

b+树相比于b树的查询优势：

1.b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”；

2.b+树查询必须查找到叶子节点，b树只要匹配到即可不用管元素位置，因此b+树查找更稳定（并不慢）；

3.对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历

索引

聚簇索引和非聚簇索引

索引的选择性

1、经常出现在where的字段 2、经常要与其他表做连接的字段 3、分组字段或者排序字段 4、选择性高的字段 5、表的某个字段的离散形高，该字段就适合建索引。 6、占用存储空间少更适合做索引。 7、存储空间固定的字段更适合选作索引的关键字。与 text 类型的字段相比， char 类型的字段较为适合选作索引关键字。 8、更新频繁的字段不适合建立索引。

SHOW INDEX FROM sys_category; 关注Cardinality

测试

添加索引前 7s

--- 查询索引的选择性
SELECT COUNT(*) AS cnt ,LEFT(username,4) AS pref FROM `sys_user2` GROUP BY pref;

添加前缀索引

ALTER TABLE sys_user2 ADD KEY (realname(2));

快了20倍

查看索引是否命中

--- 查询现在存在的索引，因为前缀索引在
SHOW INDEX FROM sys_user2;

==注意Cardinality的字段，除以count大于80%就很适合建索引==

主从复制

1. master会将操作语句记录到binlog日志中，然后授予slave远程连接的权限（master一定要开启binlog二进制日志功能；通常为了考虑数据安全考虑，slave也开启binlog功能。
2. slave开启两个线程：IO线程和SQL线程。其中：IO线程负责读取master的binlog内容到日志relay log里；SQL线程负责从relay log 日志中读取binlog内容，并更新到slave的数据库里，这样就能保证slave数据和master数据保持一致了。
3. Mysql复制至少需要两个Mysql服务，当然Mysql服务可以分布在不同的服务器中，也可以在一台服务器上启动多个服务。
4. Mysql的复制最好确保mysql和slave服务器上的MYSQL版本相同（如果不能满足版本一致，那么要保证master主节点低于slave从节点的版本）。
5. master和slave两节点的时间需同步。

数据更新的流程

redolog的两阶段的提交

==先写redo log 后写binlog==

造成的结果就是，加入binlog还没写完，redo log已经写完了，mysql的进程异常重启，之后会根据redo log的进行数据恢复。但主从复制的时候，人家复制的是binlog的数据，此时binlog并没有数据，就复制不过去，会造成数据不一致的问题。

==先写 binlog 后写 redo log==

如果binlog写完后，redo log还没写，异常重启之后这个事务无效，所以这一行的c的值为0。但是binlog里面已经记录了把“c改成了1”这个数据。所以，在之后用binlog回复的时候就多了一个事务出来，回复出来这一行的c值就是1，与原库的值不同。

ACID

隔离性 MVCC ，解决并发读写的问题

实现原理

事务1插入数据

事务2更新数据，会发现DB_ROLL_PRT会指向undolog的一段地址

事务3再更新一段数据，之后DB_ROLL_PTR再继续改变

是否出現幻讀的現象，现在的是可重复读的隔离级别

在主机A中和主机B中分别开启事务

之后在主机B中更新并提交，看主机A中尚未完成的事务查询之后是怎么样

主机A中，查询

==可以发现，主机A中可以找到主机B中提交的东西，我们再试一下，这次主机A中多一个select==

先分别执行begin，之后主机A中进行select * from user

之后在主机B中，将密码改为12345678，且提交

我们再看主机A中的查询结果是怎么样的，还是密码为123456的现象，这就很有趣，为什么多一个select语句就会查询的结果都改变了，如果为“读已提交”的话就不会出现这种情况

==读已提交和可重复读都可以看到==

==读已提交可以看到，可重复读不能看到==

事务的可见性算法

能否看到修改的数据，取决与可见性算法，可见性算法比较的时候，去觉得Read View的结果值

因为在不同的隔离基本中，生成的Read View的时机不一样

读已提交：每次执行快照读都会生成新的Read View 可重复读：只有在当前事务第一次快照读的时候生成Read View，之后的快照都会读取这个Read View

如果当前的所有操作都是当前读，那么是不会产生幻读的问题，只有当前读和快照读一起使用的时候才会产生幻读的问题。