【MySQL】MySQL存储引擎，索引，锁以及调优

存储引擎

MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV等等存储引擎。
通过show engines; 命令查看如下图

图中看到
Support 列的值表示某种引擎是否能使用
YES表示可以使用
NO表示不能使用
DEFAULT表示该引擎为当前默认的存储引擎。

简单了解一下存储引擎的描述

存储引擎	描述
InnoDB	具备外键支持功能的事务处理引擎
MyISAM	主要的非事务处理存储引擎
Archive	用于数据存档的引擎数据被插入后就不能在修改了且不支持索引。
Memory	置于内存的表
Merge	用来管理由多个 MyISAM 表构成的表集合
CSV	在存储数据时会以逗号作为数据项之间的分隔符。

了解一下这些即可目前大部分的业务使用MySQL默认的存储引擎InnoDB。
特点是InnoDB支持 ACID 事务、支持外键、支持行级锁提高了并发效率。
而MyISAM 是 MySQL 官方提供的存储引擎其特点是支持全文索引查询效率比较高缺点是不支持事务、使用表级锁。

MySQL中的索引

索引是一种特殊的数据库结构由数据表中的一列或多列组合而成可以用来快速查询数据表中有某一特定值的记录。
是 MySQL 中十分重要的数据库对象是数据库性能调优技术的基础常用于实现数据的快速检索。提高业务的访问效率。

MySQL 索引优缺点

优点

• 通过创建数据表唯一索引可以保证表中每一行数据的唯一性。
• 可以快速提高数据的查询速度这是使用索引最主要的原因。
• 在实现数据的参考完整性方面可以加速表与表之间的连接。

缺点

• 在数据创建和维护索引组要耗费时间并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占磁盘空间除了数据表占数据空间以外每一个索引还要占一定的物理空间。如果有大量的索引索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候索引也要动态维护这样就降低了数据的维护速度。

MySQL 索引类型

• 唯一索引 表示每一列的值必须是唯一的但允许为空值
• 主键索引表示每一列的值必须唯一且不允许为空值否则会报错。通常一个表ID设置为默认递增为主键索引列。
• 普通索引允许索引列有重复的值出现。
• 联合索引是由多个列共同组成的索引。一个表中含有多个单列的索引并不是联合索引联合索引是对多个列字段按顺序共同组成一个索引。应用联合索引时需要注意最左原则就是 where 查询条件中的字段必须与索引字段从左到右进行匹配。
• 全文索引全文索引只能在 CHAR、VARCHAR、TEXT 类型字段上使用底层使用倒排索引实现。要注意对于大数据量的表生成全文索引会非常消耗时间也非常消耗磁盘空间。

MySQL索引的实现

MySQL 中最常使用的一种索引实现BTreeB+ 树比较适合用作 > 或 < 这样的范围查询。

FullText 是全文索引是一种记录关键字与对应文档关系的倒排索引。

Hash 是使用散列表来对数据进行索引Hash 方式不像 B-Tree 那样需要多次查询才能定位到记录因此 Hash 索引的效率高于 B-Tree但是不支持范围查找和排序等功能。实际使用的也比较少。

RTree 是一种用于处理多维数据的数据结构可以对地理数据进行空间索引。

MySQL中的锁

InnoDB 使用行级锁而MyIASAM使用表级锁。
表锁开销小加锁快不会出现死锁但是锁的粒度大发生锁冲突的概率高并发访问效率比较低。

行级锁开销大加锁慢有可能会出现死锁不过因为锁定粒度最小发生锁冲突的概率低并发访问效率比较高。

共享锁也就是读锁其他事务可以读但不能写。MySQL 可以通过 lock in share mode 语句显示使用共享锁。

排他锁就是写锁其他事务不能读取也不能写。对于 UPDATE、DELETE 和 INSERT 语句InnoDB 会自动给涉及的数据集加排他锁或者使用 select for update 显示使用排他锁。

MySQL8.0 新特性

MySQL8.0 的一些新特性例如

1. 默认字符集格式改为了 UTF-8
2. 增加了隐藏索引的功能隐藏后的索引不会被查询优化器使用可以使用这个特性用于性能调试
3. 支持了通用表表达式使复杂查询中的嵌入表语句更加清晰
4. 新增了窗口函数的概念可以用来实现新的查询方式。

MySQL中调优

问题明明建立了索引的语句但是查询效率还是很慢如何解决呢
通过 Explain 分析发现表中有多个索引MySQL 的优化器选用了错误的索引导致查询效率偏低然后通过在 SQL 语句中使用 use index 来指定索引解决。

所以可通过两个方面进行

第一表结构和索引方面进行优化

• 要为字段选择合适的数据类型在保留扩展能力的前提下优先选用较小的数据结构
• 设计表结构时考虑数据库的水平与垂直扩展能力提前规划好未来1年的数据量、读写量的增长规划好分库分表方案。
• 要擅用索引比如为经常作为查询条件的字段创建索引、创建联合索引时要根据最左原则考虑索引的复用能力不要重复创建索引要为保证数据不能重复的字段创建唯一索引等等。
• 列字段尽量设置为 not null。MySQL 难以对使用 null 的列进行查询优化允许 null 会使索引、索引统计和值更加复杂允许 null 值的列需要更多的存储空间
• 可以将字段多的表分解成多个表必要时增加中间表进行关联。

第二SQL语句进行优化

• 通过查询 MySQL 的慢查询日志来发现需要进行优化的 SQL 语句
• 使用 Explain 来分析语句的执行计划看看是否使用了索引使用了哪个索引扫描了多少记录是否使用文件排序等等
• 避免使用 SELECT *而是应该指定具体需要获取的字段。原因一是可以避免查询出不需要使用的字段二是可以避免查询列字段的元信息。
• 尽量使用索引扫描来进行排序。
• 尽量使用 prepared statements一个是它性能更好另一个是可以防止 SQL 注入。