mysql45jia

得看你说的范围是什么意思的范围了，如果使用了任何函数那索引失效，如果存在隐式转换 索引也会失效 如果你用or 索引依然失效。如果你用的是 and 并且没有任何函数 这样子索引是可以用上的

AVL在外存的存储结构一般有两种：第一种是顺序存储结构（数组），第二种是链式存储结构（链表），这就导致了在逻辑上很近的节点（父子）在物理上可能很远，无法利用局部性，于是读出的数据块很有可能只有我们想要的一个节点，因此，AVL的I/O渐进复杂度跟树高（h）挂钩，为O（h）,所以树高20，其实I/O的渐进复杂度就为20。

基于联合索引，如果查找的数据字段在where语句的判断字段中也有，那就是覆盖索引。如果没有那就是按照最左前缀原则定位记录然后索引下推。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

我们来具体看一下，在没有使用ICP的情况下，MySQL的查询：

存储引擎读取索引记录；

根据索引中的主键值，定位并读取完整的行记录；

存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程：

存储引擎读取索引记录（不是完整的行记录）；

判断条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；

条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；

存储引擎把记录交给层，层检测该记录是否满足条件的其余部分。

比如这样一个模糊匹配的sql：

select * from tuser where name like '张%' and age=10;

假如你了解索引最左匹配原则，那么就知道这个语句在搜索索引树的时候，只能用 张，找到的第一个满足条件的记录id为1。

那接下来的步骤是什么呢？

先把第一个汉字为张的索引节点的主键id拿出来（1，4），再回表把age读出来，根据age=10进行筛选。

最后把符合条件的id（1） 进行回表读出整行数据。

可以看到只回表了一次。

除此之外我们还可以看一下执行计划，看到Extra一列里 Using index condition，这就是用到了索引下推。

你这个问题不准确哈，Innodb 语句都是在“事务中”的，你没有明确启动，也会把单个SQL语句按单语句事务处理

从普通索引和唯一索引看change buffer 和 redo log

普通索引和唯一索引的区别，唯一索引的优势就是，查到符合条件的索引后，就会立马停止。那普通索引会再次查找别的符合条件的索引。
但是性能差别大吗，结论是微乎其微。因为索引是有序的，顶多再在有序列表中再查找下一个符合条件的，但是由于索引中都是有序的，且都是按整页掉入内存，所以只有很小的概率需要去磁盘调入新的分页，因此这点性能差距可用忽略不计。

但是唯一索引写入时带来的性能消耗是很大的，因为每写一次都是索引是否判重复。

change buffer的概念，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。同时这些操作会写入redo log中。就是在黑板写好一整页再顺序写入到内存的原理。因此redo log是有必要的。比如对一条数据反复操作，没有redo log就要反复操作，或者对相邻的数据反复操作（局部性原理），操作的临时结果也写在buffer里，因此在还没有落盘的情况下可以先查buffer保证数据一致性。

在数据库要关闭时或者一段时间后会将change buffer的数据刷到磁盘中。

因为每次更新都要读全部记录，所以唯一索引会不断地调取磁盘的索引文件来判断，比如内存中有（1，100）索引记录，插入/更新10000，就需要去磁盘里找。涉及到随机io，这是很耗时的操作。
因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer可以通过参数 innodb_change_buffer_max_size 来动态设置，最多只能占用 buffer pool 的 50%。

“Buffer Pool本质是Innodb向操作系统申请的一块连续的内存空间，用来做缓存、连接池、事务等信息，默认情况下,缓冲池大小为128MB,。

总结就是普通索引性能远远大于唯一索引，唯一索引查询的性能好，但是可忽略不计。因此尽量使用普通索引。

目标是page1的数据，
1，如果page1数据在内存中，直接更新内存
2，page1如果不在内存，就在内存的change buffer记录更新操作信息 a->b
3，将上述动作记入redo log中
4，提交事务

刷盘的时间，仅访问了一次磁盘。而且还是顺序写。

而且刷盘的动作是后台异步完成的，如果此时读取会怎么样呢，毕竟还没有刷到磁盘中。

读取的时候会先读内存，先读内存中的数据。而page2呢，此时发现page2还有正在事务中数据，会将buffer和redo log的数据先刷盘，然后生成一个正确的版本返回结果。

读的时候，其他事务正在更新会触发刷盘，并再次读入内存，与redis懒加载的逻辑是相通的。

总结redo log是将离散的随机io转成顺序写。此外buffer节省磁盘随机读取的消耗。

如果此时宕机，redolog也记录了数据信息，不会造成数据一致性的问题。

change buffer的前身是insert buffer,只能对insert 操作优化（多个insert放一起顺序写，减少随机io）；

后来升级了，增加了update/delete的支持，名字也改叫change buffer.

2.redo log里记录了数据页的修改以及change buffer新写入的信息
如果掉电,持久化的change buffer数据已经purge,不用恢复。主要分析没有持久化的数据
情况又分为以下几种: 先写redolog最终以binlog为准
(1)change buffer写入,redo log虽然做了fsync但未commit,binlog未fsync到磁盘,这部分数据丢失
(2)change buffer写入,redo log写入但没有commit,binlog以及fsync到磁盘,先从binlog恢复redo log,再从redo log恢复change buffer
(3)change buffer写入,redo log和binlog都已经fsync.那么直接从redo log里恢复。

经常用于查询和排序的列应该添加唯一索引，对于高要求唯一性的业务场景，使用唯一索引效果会被应用层判断的效果要好，考虑高并发场景，提交数据存在延迟，在应用层判断唯一写入有可能造成数据的重复从而造成业务错误，而唯一索引不会出现此情况。先查再插的做法在并发条件下 网络又有延迟的情况下是无法保证唯一的。

唯一索引数据为null时，不会写入索引，查找会失效。所以唯一索引一般不让写null值。

写多读少尽量用普通索引，归档数据库（一段时间会删掉过期数据）也用普通数据比较好。
使用索引要注意的点：

1. 大批量数据，先写入数据，再建索引
2. 一个表不要有太多索引，不要超过五个
3. varchar字段不得不建立索引，要指定索引长度
4. 不定长的字段，比如图片url，可以先生成一定长度的md5，用于辅助索引
5. 唯一索引是先增加数据，检测到有重复再删除数据。

mysql为什么选错索引

mysql为啥会选错索引？ 索引统计信息不对 使用中间表和排序也会影响索引选择 是否需要回表

https://blog.csdn.net/xiangweiqiang/article/details/126440419 模拟死锁。
模拟数据插入。

# 27s
CREATE TABLE `t` (
                     `id` int(11) NOT NULL AUTO_INCREMENT,
                     `a` int(11) DEFAULT NULL,
                     `b` int(11) DEFAULT NULL,
                     PRIMARY KEY (`id`),
                     KEY `a` (`a`),
                     KEY `b` (`b`)
) ENGINE=InnoDB;
delimiter ;;
create procedure idata()
begin
    declare i int;
    set i=1;
    while(i<=100000)do
            insert into t values(i, i, i);
            set i=i+1;
        end while;
end;;
delimiter ;
call idata();

explain select * from t where a between 10000 and 20000;
set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000;/*Q2*/

show variables like '%slow_query_log%';
set GLOBAL slow_query_log=on;

删除数据

假设，我们要删掉 R4 这个记录，InnoDB 引擎只会把 R4 这个记录标记为删除。如果之后要再插入一个 ID 在 300 和 600 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。

现在，你已经知道了 InnoDB 的数据是按页存储的，那么如果我们删掉了一个数据页上的所有记录，会怎么样？
如果插入一个 ID 是 400 的行，可以直接复用这个空间。但如果插入的是一个 ID 是 800 的行，就不能复用这个位置了。
因为要为了维护 B+ 树叶子数据的有序性

而当整个页从 B+ 树里面摘掉以后，可以复用到任何位置。以图 1 为例，如果将数据页 page A 上的所有记录删除以后，page A 会被标记为可复用。这时候如果要插入一条 ID=50 的记录需要使用新页的时候，page A 是可以被复用的。

可见如果频繁删除单行数据，碎片复用条件难达成，会造成较大的碎片。频繁做批量行删除，碎片可以很快被复用，不会造成较大的碎片。

实际上，不止是删除数据会造成空洞，插入数据也会。思考m树插入的过程。

也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

如果业务比较敏感，可以通过rename的方式，再删除旧表。

这里，你可以使用 alter table A engine=InnoDB 命令来重建表。

这个临时表 B 不需要你自己创建，MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的。还是要考虑增量的情况。。

加个锁，给我排队等着，或者cas等着。毕竟变树是8，不会太久。对比垃圾回收器。最后也是要STW一会儿的

而在 MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。（全文索引除外）

需要补充说明的是，上述的这些重建方法都会扫描原表数据和构建临时文件。对于很大的表来说，这个操作是很消耗 IO 和 CPU 资源的。因此，如果是线上服务，你要很小心地控制操作时间。

这个是在线ddl工具，比如加列，加索引，推荐你使用 GitHub 开源的 gh-ost 来做。

图中alter table A engine=InnoDB理论上M（meta）DL应该加写锁，但是为了不阻塞DDL增删改操作，退化成了读锁。

但是还是要加锁，防止其他地方也执行MDL操作修改表结构。

而对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 无感知的。

如果锁1TB的表，需要预留至少2倍的空间来存放临时文件。

如果主键ID很紧凑，就没必要执行这一步操作了。（雪花ID是趋势递增，保证在一个分页里面）。

varchar(10)存储hello和varchar(100)存储hello 小于256都差不多

orderBy 之类的地命令,varchar会变成固定长度,浪费更多内存

数据页A满了，随机插入一条数据，不得不申请一个新的数据页。申请新页B，把一部分数据从A挪到B，A就空出一些。

A 有 1 2 5 已满 插入 4 就要 4 5 到B。

InnoDB引擎的表数据，整个都是以B+树的组织形式存放的。

主键索引也是这样的，12345 插入id6，那么为了维持树高，会分裂成123，456，再通过双向链表连接起来。

再插入 7，8，9.如果是批量插入，则省去了很多调整b+树的损耗。

如果写入操作频繁导致数据库服务器性能下降，考虑redo log是否设置的太小。

count（*）很慢的问题

实现方式

innodb

一行一行读出来，再统计行数。因为MVCC的存在，全表扫描才知道行数。而且不同事务的提交情况不同，扫描出来的行数也是有可能不一致的。

MyISAM

总行数存在了磁盘上，因此count（*）可以直接读取。如果加了where就不一样了，

Mysql对count(*)进行了优化，会查询数据量最小的索引树，那就是ID索引？不行，ID索引是聚簇索引，存的是整行数据，因此扫描二级索引扫描的数据页才比较少，节省IO开销。

show table status 得到的 rows 是预估值，这个预估值是根据随机采样法计算出来的，MySQL会随机取N页数据，计算出每页中不同的记录数，合起来求每页的平均值，最后乘以总页数得到的就是预估值，不是扫描所有的行计算的。 这个预估值是否接近真实值取决于索引字段的区分度和索引数据页是否紧凑，如果存在也分裂、索引空洞都会影响预估值的准确性。

MyISAM 表虽然 count() 很快，但是不支持事务；show table status 命令虽然返回很快，但是不准确；InnoDB 表直接 count() 会遍历全表，虽然结果准确，但会导致性能问题。

如果有频繁的count(*)操作，可以用

1. redis缓存，不够准确
2. 添加字段或者其他的表来记录。有事务和redolog的存在，可以保证数据一致。

按照效率排序的话，count(字段)<count(主键id)<count(1) 约等于 count()。因为mysql对count()有优化，认为是取行数，不需要把字段取出来。尽量使用count（*）

所以，count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

mysql的rr并没有解决幻读的问题。

RR级别下ReadView的生成时机是在事务中的第一次查询，事务结束前该ReadView复用。但是如果事务中进行了当前读的操作，比如例3事务一中的update，**后续再查询就会重新生成ReadView。也就是说update操作产生了当前读，**那当前读肯定可以读到事务2已经提了的数据，然后全部更新后再去读就又会产生一个readview，很明显之前的update操作对于这个readview是可见的，所以数据的条数就跟之前的不一样了。

幻读专指的是未插入的行。

binlog 买定离手。根据XID去扫描redolog，去binlog找对应的事务。

redolog解决顺序写取代随机io问题，binlog解决备份、（异构）主从一致性问题。

二阶段提交是基于此设计衍生的一种强一致性的设计。

如果说历史原因的话，那就是 InnoDB 并不是 MySQL 的原生存储引擎。MySQL 的原生引擎是 MyISAM，设计之初就有没有支持崩溃恢复。binlog不能舍弃，有些公司依靠binlog来同步数据，mysql先建立了生态。

redolog设置为4/1000比较合适，比如一个T 就四个1G的redolog。

并发喜欢的问题，想办法归结于同一行，（1，2） 2关注1时也操作（1，2）行，这样会有自然锁。

并不是所有order by都需要排序。using filesort使用了额外空间排序。也可以直接根据索引有序的特性，找到满足条件的若干条记录。

using index 覆盖索引的意思。

using index condition rowID排序，排序完之后根据id回表取数据。

需要注意的是255这个边界。小于255都需要一个字节记录长度，超过255就需要两个字节

1个字节8位，最大值是 255，用来记录 varchar 的长度。如果大于255，1个字节无法存储，因此需要2个字节（16位）
偏移量就没法用一个字节来表示了，因为2^8=256

1)无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。
因为优化器认为走二级索引再去回表成本比全表扫描排序更高。

2)无条件查询但是是order by create_time limit m.如果m值较小,是可以走索引的.
因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。

全字段排序，先取数据，再排序。不适应于单行数据太多的表。 —》 内存足够大适合

rowId，先按索引字段排序，再回表取数据。适用于内存较小的表。

MySQL设计思想，如果内存够，就要多利用内存，尽量减少磁盘访问。

bigint和int加数字都不影响能存储的值。
bigint(1)和bigint(19)都能存储2^64-1 范围内的值,int是2^32-1。

建议不加varchar()就必须带,因为varchar()括号里的数字代表能存多少字符。假设varchar(2),就只能存两个字符,不管是中文还是英文。目前来看varchar()这个值可以设得稍稍大点,因为内存是按照实际的大小来分配内存空间的,不是按照值来预分配的。

如果不能覆盖索引，全表回表和全表排序上后者应该消耗更低。索引建的字段不是很有意义。除非有limit。即便没有二级索引,mysql针对order by limit也做了优化,采用堆排序。

排序相关的内存在排序后就free掉还给系统了
tmp_table_size
Using temporary 使用临时表。临时表分为内存表和磁盘表。内存临时表默认内存是16MB；超过了tmp_table_size就会转成磁盘临时表。/。

间隙锁模拟

所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。

在没索引的字段上扫描，右边全表被锁住。

有索引的字段上扫描，只有age=2的行被锁住。

结论：InnoDB 中行级锁是基于索引实现的，

临键锁（Next-Key Locks）只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁

该表中age列潜在的临键锁有： 索引找下一个值。
(-∞, 10],
(10, 24],
(24, 32],
(32, 45],
(45, +∞],

14也被锁

而43写入成功，100也成功，说明8.0next-keys临键锁都在左区间。主要看树的结果，数据表中的索引数据情况。

慢查询性能问题 在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：

1. 索引没有设计好；
2. SQL 语句没写好；
3. MySQL 选错了索引。 应急方案，force index

短连接问题

MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。

在数据库压力比较小的时候，这些额外的成本并不明显。

但是，短连接模型存在一个风险，就是一旦数据库处理得慢一些，连接数就会暴涨。

max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。

设置 wait_timeout 参数表示的是，一个线程空闲 wait_timeout 这么多秒之后，就会被 MySQL 直接断开连接。

高峰期被打挂了，最高效的方法就是执行alter table

1. 准备一主一从，主库A，备库B
2. 在备库B上执行set sql_log_bin=off，也就是不写binlog，然后执行alter table加上索引。因为alter之后会产生大量日志，将变更同步到主库。
3. 执行主备切换，即A库为从库，B库为主库。
4. 然后A也执行set_loh_bin=off
   上线前，在测试环境将慢查询日志打开，并且把long_query_time设置成0，确保每个语句都记录入慢查询日志。在测试表插入模拟线上的数据，做一遍回归测试。

观察慢查询日志里每类语句的输出

---

t1有索引，则t2为驱动表。

t1和t2都没有索引，t120行，t1为小表，用索引扫描了19次。

select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100;
select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100;

t1只需要查一个字段，计算出他占join_buffer的空间更小。因此a为小表。

得出结论，如果大表join，是否有必要特地建一个索引。

---

rr级别下，mvcc+间隙锁能解决绝大部分的幻读问题。但也有特殊情况下解决不了。
mvcc解决了rr级别的快照读的幻读问题，但没有解决当前读（读最新的）的幻读问题。

• mvcc解决快照读的幻读
  所谓快照读就是事务开始时，第一次select读出来的数据。只有在本事务修改的情况下，才能读取最新的快照。事务之间是隔离的，其他事务做的更改读取不到。
  mvcc通过undolog查询版本链中的数据，事务之内保持数据一致性
  mvcc是一种无锁机制，区别当前读。每个事务生成一个read view，通过查看对比历史版本，

• 间隙锁解决当前读的快照
  当前读指的是select * from table for update， 例如where id > 10 and id < 100;