SQL 语句优化

查询优化

1. Explain 分析

• 先开启慢查询日志

1
2

set global slow_query_log = on # 开启慢查询日志，默认是关闭的
set global long_qurey_time=0.5 # 设置慢查询时间阈值，单位：秒

数据类型

整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

定义

索引是对数据库表中一列或者多列的值进行排序的结构。

目的

数据库索引好比一本书的目录，提高查询效率。但是为表设置索引要付出相应的代价：

• 增加了数据库的存储空间
• 在插入和修改时需花费更多的时间（因为索引也要随之变动）

分类

1. 聚集索引

索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储其真实的数据行，不再有另外单独的数据页。

MySQL 基本架构

简单来说 MySQL 主要分为 Server 层和存储引擎层：

• Server 层

  主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

• 存储引擎层

  主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

多版本并发控制-MVCC

多版本并发控制（Multi-Version Concurrency Control, MVCC）以乐观锁为理论基础，和基于锁的并发控制最大的区别和优点是：读不加锁，读写不冲突。

事务版本号

每次事务开启前都会从数据库获得一个自增长的事务 id，可以从事务 id 判断事务的执行先后顺序。

数据库锁的分类

按照锁的粒度划分：

• 表级锁
• 行级锁
• 页级锁

按照锁的级别划分：

• 共享锁
• 排它锁

按照使用方式划分：

• 乐观锁
• 悲观锁

行级锁、表级锁、页级锁

1. 行级锁

行级锁是 MySQL 中锁定粒度最细的一种锁，表示只针对当前行进行加锁。行级锁分为共享锁和排他锁。