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

时间:2023-01-09 11:53:32

存储引擎

MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV等等存储引擎。
通过show engines; 命令查看,如下图
【MySQL】MySQL存储引擎,索引,锁以及调优
图中看到:
Support 列的值表示某种引擎是否能使用,
YES表示可以使用,
NO表示不能使用,
DEFAULT表示该引擎为当前默认的存储引擎。

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

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

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

MySQL中的索引

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

MySQL 索引优缺点

优点:

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

缺点:

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

MySQL 索引类型

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

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

MySQL索引的实现

【MySQL】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 注入。