MySQL面试复习2

1.存储引擎

存储引擎：数据采用不同的技术存储在文件（或内存）中，不同的技术拥有不同的存储机制、索引技巧、通过选取不同的技术获取获取不同的功能，从而改善应用的整体功能

1-1.INNODB存储引擎

介绍：

1.InnoDB支持事务，具有的特点：行级锁设计、支持外键、非锁定读等等
2.InnoDB通过MVCC来获得高并发性，并且实现了SQL标准的4个隔离级别
3.InnoDB将数据存放在逻辑表空间中，表被单被存放在独立的ibd文件中
4.InnoDB采用聚集的方式，把每张表的存储按照主键的顺序存放，如果没有显式指定主键，会为每一行生成一个6字节的ROWID作为主键

• 应用场景：高并发数据一致性、更新和删除操作比较多，事务回滚和提交

1-2.MyISAM存储引擎

介绍

1.不支持事务和外键、支持表锁和全文索引
2.存储引擎表由MYD（存储数据）和MYI（存储索引）组成

• 应用场景：读操作较多

1-3.Memory存储引擎

介绍

1.将数据存放在内存中，适用于临时存储，默认使用哈希索引
2.速度非常快，但只支持表锁，不支持TEXT和BLOB数据类型

• 应用场景：快速定位数据

区别

功能特性	InnoDB	MySIAM	Memory
事务	支持
锁	Row	Table	Table
MVCC	支持
BT_index	支持	支持	支持
Clustered_Index	支持
Hash_Index	支持		支持
Full_Index	支持	支持
Index_Cache	支持	支持	支持
Data_Cache	支持		支持
F_Key	支持

2.索引

索引的优点：简单来说就是加快"查询"
索引的缺点：

1.降低表的更新效率（因为索引表也需要更新）
2.会额外占用磁盘空间
如果数据量非常大的情况下创建索引会导致阻塞等问题

2.1索引方法

• B-Tree：大部分存储引擎都会使用B-Tree进行存储数据而加快数据的速度（因为不需要进行全表扫描）
• Hash：基于hash表去存储表数据的，将字段进行hash后生成的哈希码存放在hash表中作为Key.Value值是指向该记录的指针

2.2索引类型

• 聚簇索引：表数据按照索引的顺序来存储的，也就是说索引顺序和记录的物理顺序一致，一张表通常只会有一个聚簇索引，因为物理顺序只能一种（相对于InnoDB来说：叶节点存储真实数据行）
• 非聚簇索引：表数据存储顺序和索引顺序无关（相对于MyISAM来说：叶节点存储的是指向数据页的逻辑指针）

• 主键索引：数据列不允许重复，不允许为NULL.一个表只能有一个主键

  • InnoDB：主键索引其实根据B+Tree数据结构组织成的是聚簇索引
  • MyISAM：主键索引也是根据B+Tree数据结构组织成的但是非聚簇索引
• 唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引
• 普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值
• 联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并（多个单列索引）
• 哈希索引：底层使用hash表数据结构组织的存储表数据（InnoDB中自适应哈希加快字典类型的单记录查询）
• 全文索引：用于查找文本中的关键字，更类似于搜索引擎（MyISAM表中还是使用B-Tree组织的）

非聚簇索引：也可以称之为辅助索引，通常是通过辅助索引找到聚簇索引然后找到记录数据，所以在B树结构中，叶子节点的数据域存放着是聚餐索引

灵魂三问

1.为什么不使用二叉树："运气"不好的时候就会导致查询效率非常低
2.为什么不使用AVL树：维护成本较高，高度过高会导致查询效率降低
3.为什么不使用B-Tree树：非叶子节点存储数据会导致磁盘IO开销增大并且内存中不能放着更多的索引

2-3.索引技巧

1.索引不会包含有NULL的列
2.使用短索引
3.索引列在查询时只会使用一次
4.like语句操作注意%的使用
5.不要在列上进行运算
6.不要使用NOT和!=等操作
7.索引建立在重复率低的字段上
8.注意Join操作是主键和外键数据类型要一致
......

2-4.索引分析

Explain命令：可以帮助我们查看SQL语句执行效果
重点字段1：key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
重点字段2：type：性能由差到好

all: 全表扫描，扫描所有的数据行
index：扫描所有的索引节点
range：查询时能够根据索引做范围的扫描
index_subquery：在子查询中，基于除唯一索引之外的索引进行扫描;
unique_subquery：在子查询中，基于唯一索引进行扫描
index_merge：多重范围扫描。两表连接的每个表的连接字段上均有索引存在且索引有序，结果合并在一起。适用于作集合的并、交操作。
ref_or_null：类似REF，只是搜索条件包括：连接字段的值可以为NULL的情况
fulltext：全文索引
ref：这也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
eq_ref：通过索引列,直接引用某1行数据（精确到一行数据中）常见于连接查询中
const, system, null：当mysql能对查询的部分就行优化，并且转换成一个常量的时候，它就会使用这种访问类型了

3.锁

因为前一篇文章有涉及到，所以这里就不做过多介绍，这里想去总结下关于死锁的情况
死锁：指多个事务对同一个资源上的互相占用；串行化条件下以及表锁是不会发生死锁现象的
关于非事务中会加锁吗？

1.手动加锁：for update
2.自动加锁：create/insert/update操作

4.优化（部分）

4-1.根据服务层面：配置性能优化参数

back_log：短时间内有多少连接请求可以放入堆栈中
wait_timeout：连接闲置超时时间
max_connection：并发连接量
max_user_connection：单个用户的并发连接量
thread_concurrency：充分利用多核（通常为核数的2倍）
skip-name-resolve：关闭域名解析
key_buffer_size：索引块缓冲区
innodb_buffer_pool_size：索引和数据缓冲区

4-2.从系统层面：增强Mysql的性能

1.优化表结构：选取合适的数据类型/字符编码等
2.索引优化：选取合适的索引字段和索引类型
3.分表分库：分散存储压力
4.读写分离：分散读写压力

4-3.从数据库层面：优化SQL语句

1.避免全表扫描：没有使用到索引
2.合理使用索引字段：注意查询语句条件

4-4.其他

1.增加缓存层：高并发情况下减少查询压力
2.构建集群架构：高可用性和负载能力
3.代码安全：防SQL注入

5.其他

5-1.范式

• 第一范式就是无重复的列
• 第二范式就是非主属性非部分依赖于主关键字
• 第三范式就是属性不依赖于其它非主属性

参考

• 推荐书籍1：《Mysql技术内幕：InnoDB存储引擎》
• 推荐书籍2：《高性能MySQL》