MySQL 性能优化和性能分析

转载请注明出处 

目录

MySQL 性能优化和性能分析

一、MySQL 的架构介绍

二、索引优化分析

---

一、MySQL 的架构介绍

和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。总体架构分为四层：

• 连接层：最上层是一些客户端和连接服务，主要是连接处理、授权认证、及相关的安全方案。

• 服务层：核心服务功能，如SQL接口，缓存的SQL查询、SQL的分析和优化及部分内置函数。

• 引擎层：存储引擎。

• 存储层：将数据存储在运行与裸体设备的文件系统。

 

存储引擎

查看命令：

 

MyISAM 与 InnoDB：

对比项	MyISAM	InnoDb
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还缓存真是数据，对内存要求高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

 

二、索引优化分析

1、常见的 join 查询

SQL 执行顺序：

 

7种 JOIN 图：

 

 

2、索引简介

是什么？

可以理解为“排好序的快速查找数据结构”，数据本身之外,数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。

优势

• 提高数据检索效率，减低数据库 IO 成本

• 降低数据排序成本，降低了 CPU 的消耗

劣势

• 索引实际上也是一张表，该表保存了索引和主键的字段，并指向实体表的记录，占用磁盘空间

• 虽然大大提高了查询速度，但降低了更新数据的速度

分类

• 单值索引：一索引一列，一表可多索引，建议一表不超5索引，优先用组合索引

• 唯一索引：索引列的值必须唯一，但允许有空值

• 复合索引：一索引多列

语法

-- 创建
CREATE [UNIQUE] INDEX indexName ON mytable(columnName(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnName(length));

-- 删除
DROP INDEX [indexName] ON mytable;

-- 查看
SHOW INDEX FROM table_name\G

哪些情况需要创建索引

1. 主键自动建立唯一索引

2. 频繁作为查询的条件的字段应该创建索引

3. 查询中与其他表关联的字段，外键关系建立索引

4. 单值/组合索引的选择问题， who？（在高并发下倾向创建组合索引）

5. 查询中排序的字段

6. 查询中统计或者分组字段

哪些情况不要创建索引

1. 表记录太少

2. 经常增删改的表

3. where 条件里用不到的字段

4. 数据重复且分布平均的表字段

因此应该只为经常查询和经常拍寻的数据列建立索引

 

3、性能分析

MySQL 常见的瓶颈

 

Explain + SQL 语句

id：select 查询的序列号，表示 select 查询或操作表的顺序，id最大先执行，相同的按顺序自上而下执行

select_type：

顾名思义，SIMPLE 不包含子查询或UNION，PRIMARY包含子查询中最外层的select 语句，DEPRIVED 衍生表

type：访问类型排列，显示查询使用了何种类型

 

从最好到最差：

system>const>eq_ref>ref>range>index>ALL

system：查询系统表

const：通过索引一次就找到，只有一条记录，通常用于主键索引或唯一索引

eq_ref：唯一性索引，扫描后只有一条记录与之匹配，一般应用在一对一的关联查询

ref：非唯一性索引，返回匹配某个单独值的所有行

range：只检索给定范围的行，使用一个索引来选择行，一般在where语句中出现了between、<、>、in

index：全索引扫描

All：全表扫描

一般来说，得保证查询达到range级别，最好能达到 ref 。

possible_keys：查询可使用索引，理论上可能会使用的索引

key：实际上使用的索引

key_lens：表示索引中使用的字节数，长度越短越好

ref：显示索引哪一列被使用了

rows：大致估算出找到所需的记录所需要读取的全部行数

Extra：十分重要的额外信息

• Using filesort（九死一生）：不是按照表内的索引顺序进行读取，无法利用索引完成排序操作成为“文件排序”

• Using temporary（十死无生）：使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见 order by 和 group by

• Using index（起死回生）：相应的select操作中使用了覆盖索引，避免了访问表的数据行，效率不错

  覆盖索引（Covering Index）：select 后查询的列要被索引覆盖

 

详细索引优化可以看我下一篇博客 MySQL 索引失效汇总