Mysql知识点总结2

1.回表

#先根据非主键索引（二级索引、辅助索引）找到主键，再根据主键找到这条数据。
select * from table where name='xxx'; --在name字段上建索引

2.索引覆盖

#先根据非主键索引（二级索引、辅助索引）找到主键，直接返回主键（只需要主键）。
select id from table where name='xxx';

3.ICP索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。

在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

4.最左匹配

#组合索引:即对多列建索引，查询的时候看查询条件是否能够走索引查询
优化器：
1.CBO:基于成本的优化
2.RBO:基于规则的优化

5.MRR(Multi-Range Read Optimization)

将随机IO转化为顺序IO以降低查询过程中IO开销的一种手段

MRR的使用过程：
先通过二级索引取出满足条件的二级索引和主键放到缓冲区（大小由参数read_rnd_buffer_size控制）中，当该缓冲区满了之后，再通过主键进行排序，最后按照排序后的结果集取表中的数据。（由于此时表是顺序的，就将随机IO变成了顺序IO，多页数据记录可一次性读入或根据此次的主键范围分次读入，以减少IO操作，提高查询效率）


MRR的优点：
1.MRR使数据行能够按照索引元组顺序而不是随机被访问
2.MRR允许批量处理需要通过索引元组访问数据行的操作请求（如：范围索引扫描和连接属性索引等连接）

6.FIC(fast index creation)

	MySQL5.5之后，对innodb表创建或删除辅助索引的效率提升了很多，即增加了新的功能fast index creation。因为MySQL5.5之后，创建和删除辅助索引不再需要拷贝整个表的数据。

在5.5之前，在一个已经存在数据的表上增加或者删除索引是很耗时的。create index或drop index按照以下的方式进行工作：
-创建一个新的、空的临时表，表结构为使用alter table定义的新结构
-逐一拷贝数据到新表，插入数据行同时更新索引
-删除原表
-将新表的名字改为原表的名字

快速索引创建只是对辅助索引有效，对主键索引无效。innodb的表存储时是基于主键的聚集索引来组织的，在oracle中这种方式叫做“索引组织表”。因为表结构仅仅依赖于主键，所以重定义主键仍然要按照5.5之前的方式进行数据拷贝。

fast index creation机制也可以加快对索引组织表的load操作的效率。先创建只有聚集索引的表，数据load结束后，再创建辅助索引。

7.注意事项

1. 当使用索引列进行查询时，尽量不要对其使用表达式
2. 尽量使用主键索引，因为主键查询不会触发回表
3. 当索引列数据很长时，可以使用索引前缀
4. 当进行排序时，尽量使用索引列来排序，注意要带有能根据索引列查询的条件
5. union all、in、or（单列索引，组合索引包含全部列）都能够使用索引，但是推荐使用in
6. 范围列（<、>、<=、>=、between）可以使用索引，但是后面的列无法用到索引
7. 强制类型转换会导致全表扫面
8. 更新十分频繁，数据区分度不高的字段不宜建立索引（区分度：count(distinct(列名))/count(*)）
9. 创建索引的列，不允许为null,可能会得到不符合预期的结果
10. 当进行连表查询时，最好不要有太多的表（限制3张）
11. 尽量使用limit返回数据
12. 单表索引尽量控制在5个以下
13. 组合索引的字段尽量控制在5个以下

8.mysql 的server架构

1. 连接器：管理连接、验证权限
2. 分析器：词法分析、语法分析
3. 优化器：优化sql语句 CBO、RBO
4. 执行器：用来直接跟存储引擎直接交互
5. 查询缓存：8.0版本去掉

9.存储引擎

​ 不同的数据文件在磁盘的不同组织形式

1. innodb:支持事务、支持外键、支持行锁和表锁、5.6支持全文索引、叶子节点存放数据、B+索引

2. myisam：不支持事务、支持表锁、叶子节点存放地址、B+索引

   .frm -------存储表定义

   .MYD --------MYData，存储数据

   .MYI --------MYIndex，存储索引

3. memory: 使用在内存中的数据来创建表，每个memory表对应一个磁盘文件。格式是.frm

   特点：该文件只存储表的结构，而其数据文件，都是存储在内存中，由于他的数据是存放在内存中的，并且默认使用HASH索引，所以它的访问速度特别快，同时也造成了他的缺点，就是数据库服务一旦关闭，数据就会丢失，另外对表的大小有限制

   每个memary表中可存储数据量的大小，受到max_heap_table_size变量的约束，他的初始值是16MB，可以在定义Memary表的时候通过max_rows指定表的最大行数

   适用场景：内容变化不频繁的代码表，作为统计操作的中间结果表，便于利用它速率快的优势高效的对中间结果进分析。