mysql结构及mysql调优

数据库设计三范式

1. 保证每列数据的原子性，每列的字段不能再拆分，如在数据库中设计地址字段时，可以将地址拆分为省、市、区三个字段分别存储，而不是只使用地址一个字段。第一范式可以根据自己的应用场景决定是否遵循
2. 保证表中每行都有主键进行区分，不同行的同一列不会出现同样的值，即不会出现数据冗余。第二范式也可以根据自己的应用场景决定是否遵循，当不希望进行多表联查时，可以设计冗余字段提高查询性能
3. 保证表中每列都和主键直接相关而不是间接相关

mysql的查询顺序

mysql缓存查询——查询优化器生成查询计划——执行查询引擎

mysql缓存

mysql中有自带的缓存，客户端在进行查询时会先去缓存中进行查询，若缓存查询到则直接返回，不会再为本次查询生成执行计划，也不会执行，默认缓存是关闭的，可以在mysql中设置这个参数或者在配置文件中设置参数

查询优化器

一条sql在查询时会有多种不同执行方式，最后返回相同的结果，查询优化器是用于找出最优的执行方式，并将其转换成对应的查询计划

Explain执行计划（用于分析索引的使用情况）

使用explain关键字可以模拟sql在查询优化器中生成查询计划的过程，查看当前sql在执行时的具体执行顺序，针对一条查询语句使用explain关键字可以通过type字段分析出当前查询语句在查询中使用索引的情况

1. type字段是进行sql调优时重点关注的字段，type字段表示该sql进行查询时的索引访问类型，字段的查询效率具体如下：
   system > const > eq_ref > ref（最好能到） > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL
   ref：按某个非唯一性索引的值返回查询结果
   range：只检索给定范围内的行，如where条件中的between
2. key：显示本次查询中具体用到了哪些索引

索引的分类

1. 主键索引：一般是在建表的时候同时创建主键索引，也就是说主键约束默认索引
2. 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE index 索引名 on 表名(列名)

3. 普通索引：最基本的索引，它没有任何限制.

CREATE index 索引名 on 表名(列名)

4. 复合索引：指在多个字段上创建的索引

CREATE index 索引名 on 表名(列名,列名...)

5. 全文索引：innoDB中没有全文索引，一般需要用到全文索引的地方可以用es做

索引可能出现的问题

1. 本身可能并没有索引，比如用explain分析type=all
2. 有索引，但索引失效
   1. 针对复合索引没有遵循左前匹配原则，如针对name、age、pos三个字段建立联合索引，则底层mysql实际上会先后生成三个索引，（name）、（name，age）和（name，age，pos）。所以针对建立了联合索引的字段进行查询时，要按照这三个索引中的顺序进行查询，否则联合索引就会失效
   2. 在索引上进行了函数运算，like等
   3. 避免使用select *进行查询，需要哪些字段就查询哪些字段

索引的存储结构

1. BTree索引：

   1. 二叉树：特点左子树值都比根父节点小，右子树都比根节点大，但可能会出现极端情况，二叉树退化成链表
   2. 平衡二叉树：根节点的值动态变化，保证整个树的高度是最低，可以避免普通二叉树的极端情况，但进行范围查询的回旋查询时效率较低，并且要求每个节点左子树和右子树的高度差不大于1，导致每次插入删除时都会进行左旋或右旋进行调整，所以平衡二叉树的插入删除效率很低
   3. 红黑树：一种平衡树，特点：根节点为黑，红色节点的子节点一定是黑色，从一个节点到其所有叶子节点的路径都要包含相同数目的黑色节点，缺点是每个节点只能有一个值，且只能有两个子节点，会导致树的高度很高，查询时会增加很多io次数
   4. B树：遵循二叉树的基本规则，大的在右小的在左，但B树中一个节点可以多个值，一个节点也可以有多个子节点，能够大大降低整个树的高度，减少io的读写次数提高查询效率，但在进行范围查询时仍要进行回旋查询，导致效率不高
      
   5. B+树：在B树基础上进行改进，B+树中非叶子节点只包含key，非叶子节点key只用于查找，只有叶子节点包含key和value，在非叶子节点中的value会放到叶子节点中进行保存，所以B+树的叶子节点会构成一个链表，链表中包含所有节点的key和value，这样在进行范围查找时就不在需要进行回旋，直接找对应节点的左边或右边的节点就能完成查询。优点是查询效率很高，缺点是会出现数据冗余，非叶子节点的key值会重复出现

2. HASH索引：直接根据key值进行hash运算，根据结果选择存放位置，优点是精确查询效率很高，但不支持范围查询，因为HASH底层存放数据时是散列存放的，无法和相邻元素进行大小比较

3. 聚簇索引和非聚簇索引

   1. 聚簇索引：主键索引就是聚簇索引，聚簇索引的叶子节点的value中保存的就是存放实际字段数据的磁盘位置的指针，通过这个指针找到存放数据的地址，进行io操作
   2. 非聚簇索引：非主键索引就是非聚簇索引，非聚簇索引的叶子节点的value中保存的是对应数据的主键id，所以利用非聚簇索引进行查询时，是先从非聚簇索引中查找出对应数据的id，再根据主键id去聚簇索引中查找对应的字段的地址，再根据地址进行io操作

MyISAM和InnoDB的区别

mysql默认使用的是innodb引擎，区别如下：

1. innodb支持事务，myisam不支持事务
2. innodb锁的粒度是行级别，myisam是表级别
3. innodb支持外键，myisam不支持外键
4. 总体来说，myisam性能更高，innodb的安全性更高

面试题

索引优化的步骤

需要根据explain的执行计划分析，判断哪些字段应使用索引，步骤如下

1. 首先开启慢查询日志，设置慢查询语句的记录时间，当查询语句超过这个时间就会被记录到日志中
2. 用explain分析慢查询日志中执行慢的sql，分析索引使用情况
3. 根据分析结果优化索引类型，重点关注执行计划中type的类型，要针对all全表扫描类型进行优化，最好能达到ref级别，至少也要达到range级别，所以就需要为字段添加索引

索引建立的原则？

同时满足下列条件的字段可以建立索引：

1. 在where或order by中频繁使用
2. 字段的内容不仅仅是几个固定的值（如性别就不应该建立索引）
3. 该字段一旦被设置就不会被频繁的修改（修改后还需要去索引表中修改，降低性能）

InnoDB使用B+树索引，你知道为什么采用B+树吗？和Hash索引比起来有什么优缺点?

索引失效情况？

1. 针对复合索引没有遵循左前匹配原则，如针对name、age、pos三个字段建立联合索引，则底层mysql实际上会先后生成三个索引，（name）、（name，age）和（name，age，pos）。所以针对建立了联合索引的字段进行查询时，要按照这三个索引中的顺序进行查询，否则联合索引就会失效
2. 在索引上进行了函数运算，like等
3. 避免使用select *进行查询，需要哪些字段就查询哪些字段

建立联合索引时怎么考虑字段间顺序？

在创建多列索引的时候，我们会根据业务的需求，where子句中使用最频繁的一列放在最左边，因为mysql索引查询会遵循最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。当我们创建一个联合索引的时候，如(key1,key2,key3)相当于(key1),(key1,key2)和(key1,key2,key3)

你知道B+树的叶子结点都可以存放哪些东西吗?

InnoDB的B+树可能是存储的整行数据，也能是主键的值，索引B+树叶子结点存储整行数据的是主键索引，也被称为聚簇索引。

而索引B+树叶子点单存储了主键的key是非主键索引，也被称为非聚簇索引。

聚簇索引和非聚簇索引，在查询的时候有区别吗?

聚簇索引: 以InnoDB作为存储引擎的表，表中的数据都会有一个主键，即使不创建主键，系统也会自动创建一个隐式的主键

非聚簇索引: 以主键以外的列值作为键值构建的B+树索引，我们称之为非聚簇索引。

mysql去重

distinct或group by