mysql总结(四)-查询优化

sql语句做了什么

select * from T where ID=1

先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接成功客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时
数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。
查询缓存, mysql进行查询之前，会发到查询缓存中查看是否有,如果有缓存直接返回结果,如果无缓存的话，执行后面的操作.只要有对一个表的更新，这个表上所有的查询缓存都会被清空,都会将缓存进行删除重建,对于常用应用来说，mysql缓存基本无效。==建议关闭==
分析器分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么
优化器优化器是在表里面有多个索引的时候，决定使用哪个索引,在sql语句的索引顺序的时候，决定使用哪种索引
1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 1，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行
3. 。执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

索引优化

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

索引类型分为主键索引和非主键索引。主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引，非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引

1.基于主键索引和普通索引的查询有什么区别？

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，==得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表==。

基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询

覆盖索引


mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),
(700,7,'gg');

执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

在 k 索引树上找到 k=3 的记录，取得 ID = 300；
再到 ID 索引树查到 ID=300 对应的 R3；
在 k 索引树取下一个值 k=5，取得 ID=500；
再回到 ID 索引树查到 ID=500 对应的 R4；
在 k 索引树取下一个值 k=6，不满足条件，循环结束

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），==回表了两次==（步骤 2 和 4）

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为==覆盖索引==

最左缀索引

索引下推

索引维护

==页分裂== 如果插入的数据,所在的数据页已经满了,就需要进行页分裂。这时候需要申请一个新的数据页，然后挪动部分数据过去，性能受到影响。页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%。
==页合并== 当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并
==自增主键== 自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂