【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 中最常使用的一种索引实现：BTree，B+ 树比较适合用作 > 或 < 这样的范围查询。

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 注入。