MySQL性能秘籍：揭秘缓冲与索引的魔法！

一般情况下，我们不会用到数据库自带的缓存，所以 MySQL 默认是不开启缓存的。只有以读为主的业务，数据不变化的情况下，可以开启数据库的缓存。

查看缓存是否开启：

show variables like 'query_cache%';

queyr_cache_type:on，表示缓存开启，默认是关闭的，可以通过修改 MySQL 配置文件 my.cnf 进行调整，重启服务后生效。

query_cache_limit:1048576，表示单词查询缓存的结果集大小1M，超过1M则不会缓存。

query_cache_size，表示缓存开辟的空间大小。

查看缓存操作情况：

show status like 'Qcache%';

Qcache_hits:表示缓存命中次数

Qcache_inserts:表示缓存写入次数

缓存生效的条件是在缓存开启的情况下，执行的sql 语句字符串一模一样的时候，可以从缓存直接读取数据，但是当缓存数据相关的表存在数据变化的时候，原有的缓存就会失效，需要重新写入缓存。

MySQL 的缓存开启后，当 sql 查询语句带有 sql_no_cache 关键字或者带有函数操作或者单次查询结果集超过 query_cache_limit 的设置的值或者查询系统表时，不会用到缓存。

我们在开发中，最好不要开启缓存，将 query_cache_type 设置为off，query_cache_size 设置为 0；缓存一般会用 Redis 方案来替代。

正确合理的创建索引是提升数据库查询性能的基础，因此针对数据库来说，索引是必须要掌握的。

1

数据库索引的本质

我们一说到索引，如果大家想到是一个类似于字典的目录，可以提高数据库查询的性能，那么这个理解还是太表面了，也就是说对索引的作用有了解，对它的本质还是不够了解。

数据库索引的本质是一种数据结构，是数据库管理系统（DBMS）中一个排序的数据结构，目的是为了提升对数据库表中的记录行的检索速度，以协助快速查询、更新数据库表中数据，而创建的一种数据结构。

实际上，建立索引会占用磁盘空间的索引文件，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录地址。

使用索引提升查询效率的 3 个特点：

1、索引能极大的减少存储引擎需要扫描的数据量，从而提升查询效率；

2、索引可以把随机 IO 变成顺序 IO，从而提升查询效率；

3、索引在进行分组、排序操作时避免使用临时表，从而提升查询效率；

2

MySQL 中 B+Tree 索引的实现方式

1、Myisam 存储引擎中的实现

.frm 表定义文件：每一个存储引擎都有这个文件

.myd 数据文件：d 代表 data，数据库表中的数据保存在这个文件

.myi 索引文件：i 代表 Index，索引保存在这个文件

Myisam 引擎的数据文件与索引文件是分开的，在.myi 索引文件中无论是主键索引还是辅助索引，其叶子节点保存的是.myd 数据文件中的数据行的地址指针，因此 Myisam 引擎获取数据记录的过程是先从索引文件中拿到数据区的地址，然后再去数据文件中获取对应地址的数据返回给客户端。

2、Innodb 存储引擎中的实现

.frm 表定义文件：每一个存储引擎都有这个文件

.ibd 数据文件：数据库表中的数据保存在这个文件

在 InnoDB 存储引擎里，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在 .ibd 文件里面。

Innodb 以主键为索引来组织数据的存储，如果没有指定主键，那么 InnoDB 会默认增加主键索引，同时，叶子节点存放的是整张表的行记录数据，因为主键对应的行所有的字段都在叶子节点上存在，而且索引键值的逻辑顺序跟数据库表中的数据行内容存储顺序是一致，所以这种主键索引也叫聚集索引。 

Innodb 的辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键聚集索引键（即主键的值）,当通过辅助索引来查询数据时，InnoDB 存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

InnoDB 会默认增加主键索引的规则：

1、如果定义了主键（PRIMARY KEY），那么 InnoDB 会选择主键作为聚集索引；

2、如果没有定义主键，那么 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引；

3、如果唯一索引也没有定义，那么 InnoDB 会选择内置 6 个字节的 ROWID 作为默认的聚集索引，采用主键递增的策略生成。

Innodb 引擎的聚集索引把行记录数据与主键全部存起来的原因是考虑到用主键做查询的情况比较多，所以把所有的行记录聚集起来放到主键的叶子节点上。

Innodb 引擎的辅助索引用主键值而不用地址表示，主要原因是当数据变化的时候，如果用的是主键，不是地址的话，不用再维护辅助索引了。

后面将为大家深入讲解数据库的索引。