mysql性能优化思维导图模板_ProcessOn思维导图、流程图

常见诱因

无索引、索引失效导致慢查询

锁等待

存储引擎

InnoDB

行锁，提供了具有提交、回滚、和崩溃恢复能力的事务安全，支持自动增长列，支持外键约束，并发能力强， 占用空间是MYISAM的2.5倍，处理效率相对会差一些（行锁是基于索引加的锁，如果我们在更新操作时，条件索引失效，那么行锁也会升级为表锁） mysql5.5之后默认

支持事务，每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，最好把多条sql放在begin和commit质检，组成一个事务

支持外键，一个包含外键的InnoDB表转为MYISAM会失败

聚簇索引，数据文件是和索引绑定在一起的，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据，因此主键不应该过大，因为主键过大，其他索引也都会很大。

不保存表的具体行数，执行select count(*) from table时需要全表扫描

mysql5.6以后支持全文索引

MyISAM

全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select为主的应用可以使用该引擎,mysql 5.5之前默认

不支持事务

不支持外键

创建的所有索引都是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。

用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快

支持全文索引，查询效率上要高

Memory

全表锁，存储在内容中，速度快，但会占用和数据量成成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表

Merge

是一组MYISAM表的组合

BLACKHOLE

黑洞，任何写入到此引擎的数据均会被丢弃掉， 不做实际存储；Select语句的内容永远是空

死锁

不恰当的SQL语句

SELECT *

SELECT COUNT(*)

在大数据表中使用 <LIMIT M,N> 分页查询

对非索引字段进行排序

优化SQL的步骤

 1、通过 EXPLAIN + sql分析 SQL 执行计划

id

select_type：SELECT查询类型

SIMPLE：普通查询

PRIMARY：主查询

UNION：union中后面的查询

SUBQUERY：子查询

table：当前执行计划查的表，用别名则显示别名

partitions：访问的分区表信息

* type：表示从表中查询到行所执行的方式（结果从好到差排序）

system/const：表中只有一行数据匹配,根据索引查询一次就能找到对应数据

案例：EXPLAIN SELECT * FROM tableA where id='1';

eq_ref：唯一索引扫描，常见于夺标连接中使用主键和唯一索引作为关联条件

案例：EXPLAIN SELECT * FROM tableA a, tableB b WHERE  a.id=b.order_id;

ref：非唯一索引扫描，或唯一索引最左原则匹配扫描

案例：EXPLAIN SELECT * FROM order where order_no='2';

range：索引范围扫描，比如> < between操作

案例：EXPLAIN SELECT * FROM order where id>1;

index：索引全表扫描，遍历整个索引树

案例：EXPLAIN SELECT id FROM tableA ;

ALL：全表扫描

案例：EXPLAIN SELECT * FROM tableA where cname='sdf';

possible_keys：可能用到的索引

key：实际使用到的索引

key_len：当前使用的索引长度

ref：关联id等信息

row：查找到记录所扫描的行数

filtered：查找到所需记录占总扫描记录数的比例

Extra：额外信息

using index

表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。

using temporary

使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低

using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。

2、通过Show Profile分析SQL执行性能（>=5.0.37）

查询：select @@have_profiling

开启：set profiling=true;

SHOW PROFILE [ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES] FOR QUEYR  query_id LIMIT row_count;

ALL：显示所有开销信息

BLOCK IO：阻塞的输入输出次数

CONTEXT SWITCHES：上下文切换相关开销信息

CPU：显示 CPU 的相关开销信息

IPC：接收和发送消息的相关开销信息

MEMORY ：显示内存相关的开销，目前无用

PAGE FAULTS ：显示页面错误相关开销信息

SWAPS：显示 swap 交换次数的相关开销信息

3、常用的sql优化

优化分页查询

用子查询替代limit

-- 分页 select * from aps_bd_bank order by ccode  limit 100000,20; select * from aps_bd_bank where ccode>=(select ccode from aps_bd_bank order by ccode limit 100000,1) order by ccode limit 20;

优化select count(*)

使用近似值

如果业务场景不需要返回精确的count值，

优化select *

4、慢sql查询

查看是否开启慢sql查询：show variables like 'slow_query%';

查看最大执行时间：show variables like 'long_query_time';

开启慢sql查询：set global slow_query_log='ON';

设置记录日志地址：set global slow_query_log_file='/var/lib/mysql/test-slow.log';

设置最大执行时间：set global long_query_time=1;

高并发场景数据库调优

事务

事务内的语句要么全部成功，要不全部失败，良好的事务处理系统，必须具体的四个特征：

A原子性

C一致性

I隔离性

D持久性

并发事务带来的问题

数据丢失

悲观锁解决：for update

乐观锁解决：update 表 set ... , version = version +1 where id= #{id} and version = #{version}

脏读

一个事务读到了另一个未提交事务修改过的数据

不可重复读

一个事务A查一条数据，事务B修改了这条数据并提交，事务A再次查了这条数据，结果变成修改之后的了。

幻读

一个事务A根据条件查出一些数据，另一个事务B插入了符合条件的数据，事务A再次查询时，结果能把事务B插入的数据也查出来

锁机制（按锁的使用方式，行锁）

共享锁S：lock in share model

允许一个事务读数据，不允许修改， 其他事务要对这行数据加锁，只能加共享锁。

排它锁X：for update

可以读取和修改，一旦一个事务对该行数据加排他锁， 其他事务将不能再对该数据加任务锁。

锁机制（按锁的粒度）

行锁

通过索引实现，如果不通过索引条件检索数据，行锁 将会升级到表锁。

三种算法

record lock：记录锁，单条记录上锁

gap lock：间隙锁，锁定记录的前后范围，但不包含记录本身

next-key lock：临键锁，锁定记录本身和前后范围

表锁

页锁

全局锁

隔离级别

未提交读（Read Uncommitted）

很少用：事务A可以读到事务B未提交的数据。所以会产生脏读、不可重复读，幻读；

已提交读（Read Committed）

事务A只能读到最新提交的数据，会产生不可重复读、幻读

可重复读（Repeatable Read）

每个读操作加了共享锁，即其他事务也只能读，不能改；事务修改数据时，用了行级排它锁，其他事务不能修改当前数据。同一事务多次查询会看到相同的数据。会产生幻读。

可序列化（Serializable）

很少用：每个读操作都加了共享锁，即其他事务只能读，不能改；事务修改数据时用表级排它锁，可能会导致大量的超时和锁争用问题，只有在非常需要确保数据一致性而且可以接受没有并发的情况下，才考虑使用

配置

查看：show variables like 'tx_isolation';

修改：set GLOBAL TRANSACTION ISOLATION level read UNCOMMITTED;

优化高并发事务

1、隔离级别越高，并发性能就越低， 结合业务场景，使用低级别事务隔离

案例：修改用户登录时间，只有用户自己登录时才会修改，不存在一个事务提交的信息 被覆盖的可能。所以我们允许该业务使用最低隔离级别。

2、避免行锁升级表锁

案例：行锁是通过索引实现的，如果不通过索引条件检索修改数据，行锁 将会升级到表锁

3. 控制事务的大小，减少锁定的资源量和锁定时间长度

案例：将加锁的操作尽量放到临时事务结束的地方

索引

介绍

是存储引擎用于快速找到记录的一种数据结构。

大大减少了服务器需要扫描的数据量

可以帮助服务器避免排序和临时表（B-Tree索引，按顺序存储数据，所以mysql可以用来做order by和group by）

可以将随机I/O变为顺序I/O（索引中存储了实际的列值，所以某些查询只使用索引就能完成全部查询）

类型

聚簇索引（Clustered Index）

叶子节点记录了主键值、事务 id、用于事务和 MVVC 的回流指针以及所有的剩余列。 使用这种索引检索数据，索引找到对应的叶子节点，直接就可以获取到行数据。

非聚簇索引（（Secondary Index）辅助索引、二级索引）

叶子节点记录的是主键值。 使用这种索引检索数据，先找到叶子节点的主键值，再通过聚族索引中的 B+ 树检索到对应的叶子节点，然后获取整行数据。这个过程叫做回表

调优方法

防止索引失效

B-Tree索引

使用（复合）索引查询或排序时，有效：

全值匹配

和索引中的所有列进行匹配

最左匹配

复合索引idx_user_order_status(order_no, status, user_id)，如果我们使用 order_no、order_no+status、order_no+status+user_id 以及 order_no+user_id 组合查询，则能利用到索引；而如果我们用 status、status+user_id 查询，将无法使用到索引

匹配列前缀

精确匹配某一列并范围匹配另一列

只访问索引的查询

使用（复合）索引查询或排序时，失效：

不满足最左匹配原则

跳过了索引中的列

比如使用order_no+user_id查询，那么索引只能使用order_no列

查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查找

比如like,<>:select * from table_name where column like '%aaa'//失效，column like 'aaa%'有效 ：where order_no ='' and status like 'aa%' and userid='',这个查询只能使用索引的前两列，如果范围查询的列值数量有限，那么可以通过使用多个=来代替范围条件

查询条件中使用or，且or的前后条件中有一个列没有索引，那有索引的列也不会被用到索引

函数操作或表达式计算

where条件中类型为字符串的字段没有使用引号引起来；【查询where条件数据类型不匹配也无法使用索引，字符串与数字比较不使用索引，因为正则表达式不使用索引，如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描】

在索引字段上使用“not”，“<>”，“!=”等

如果MySQL评估使用索引比全表扫描更慢，则不使用索引

哈希索引（MySQL中只有Memory引擎显式支持哈希索引）

空间数据索引R-Tree

全文索引

通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。

高性能的索引策略

独立的列

索引不能是表达式的一部分，也不能是函数的参数，养成始终将索引单独放在比较符号的一侧的习惯

反例：select order_id from table_name where orderid+1=5; select * from table_name where to_days(current_data)-to_days(data_col)<=10;

前缀索引优化

含义：使用某个字段中字符串的前几个字符建立索引

何时用：当某个字段的数据量太大，而且查询又非常频繁时,比如对BLOB、TEXT或者非常长的varcha列进行索引

如何用：ALTER TABLE table_name ADD KEY(column_name(prefix_length));//前缀长度的确定方法：

1、先计算某字段全列的区分度SELECT COUNT(DISTINCT column_name)/COUNT(*) FROM table_name;

2、然后计算前缀长度为多少时和全列的区分度最相似SELECT COUNT(DISTINCT LEFT(column_name,prefix_length))/COUNT(*) FROM table_name;

3、不断调整prefix_length,直到和全列计算出区分度最相近的那个值

多列索引

索引列顺序非常重要

将选择项最高的列放在索引的最前列

何时创建

当出现服务器对多个索引做and操作时

当服务器需要对多个索引做or操作时

如果explain中看到索引合并时

索引合并：》mysql5.0引入的策略，一定程度上可以使用表上的多个单列索引来定位指定的行

自增字段作主键优化查询

InnoDB主键索引默认为聚族索引，是按主键顺序依次存放，如果是自增字段为主键，则直接追加即可，如果是随机字段为主键，可能会插入到现有数据页中间的某个位置，需要移动其他数据来满足新数据的插入，甚至从一个页面复制到另一个页面，这种现象叫页分裂，可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率

注意：高并发工作负载，主键顺序插入可能会造成明显争用导致间隙锁竞争

覆盖索引优化查询，避免回表

如果一个索引包含所有需要查询的字段的值，就叫覆盖索引，查询部分字段使用覆盖索引

统计数据，count(*)如果没有辅助索引，就会找聚族索引查行数，如果有辅助索引，就会找聚族索引统计行数，减少io

创建索引的常用规则

适合建立索引的字段

主键和外键字段

在order by 或 group by 或 count/max 后面的字段

占用存储空间少的字段，比如整型比字符型更适合

存储空间固定的字段，比如char比text更适合

经常与其他表进行连接的表的字段

经常出现在where子句中的字段

选择性较高（离散度）的字段，比如唯一性字段select count(distinct 列)/count(*) from table

尽量地扩展索引，不要新建索引

复合索引在建立时应该具体分析，尽量用单字段索引替代

复合索引中的主列（靠近左边的字段），一般是选择性较好的字段

复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

如果复合索引中包含的字段经常单独出现在Where子句，则分解为多个单字段索引；

如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段

如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引。

不适合建立索引的字段

不应该在字段比较长的字段上建立索引

对于频繁更新、插入的字段应该少建立索引，因为在修改和插入之后，数据库会去维护索引，会消耗资源

尽量少在无用字段上建立索引【where条件中用不到的字段】

数据重复且分布平均的表字段不应该创建索引【选择性太低，例如性别、状态、真假值等字段】

参与列计算的列不适合建索引【保持列"干净"，比如from_unixtime(create_time) = '2014-05-29'就不能使用到索引，原因是b+树中存的都是数据表中的字段值，但进行检索时需要把所有元素都应用函数才能比较，显然成本太大，所以语句应该写成create_time = unix_timestamp('2014-05-29')】

适合建立索引的表

数据量超过300的表

案例

已有的

aps_im_templateline的cheadid

aps_data_table_column

aps_page_referline的cheadid，但是代码中没有用到

还没有

aps_page_fieldformat的ctemplateid字段，无索引1.082s,创建后0.111s

aps_page_selectedfields的ctemplateid，centityid，无索引0.167s，创建后0.013s

aps_bd_bank的ccode，无索引0.312s，创建后0.012s

aps_msg_messagelist的cmsgid，无索引0.134s，创建后0.094s

aps_page_documentcopy的ctemplateid，无索引0.243s,创建后0.18s

aps_page_buttonformat的ctemplateid，创建前后效果不明显

aps_page_bscontra 的ctemplateid，创建前0.025s，创建后0.013s

aps_page_template_widgets的ctemplateid，无索引0.022s，创建后0.014s

aps_page_referline的ctemplateid，无索引0.018s，创建后0.012s

aps_page_pagetemplate的cptemplateid，无索引0.051s，创建后0.013s；cpageid创建前后效果不明显

方法

1、查数据量select * from information_schema.tables where table_schema='apsdb' order by table_rows desc  2、查代码里相关表的sql中常用的查询字段和排序字段 3、看是否需要创建单字段索引还是复合索引 4、创建前后查询效果比对

小结

只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，所以才是有效的。 对于非常小的表，大部分情况，全表扫描更有效。 中大型的表，索引很有效。 特大型的表，索引的代价随之增长，此时可以使用分区技术

死锁

两个或多个事务在同一资源上相互占用， 并请求锁定对方占用的资源，导致恶性循环的现象

互斥

占有且等待

不可强占用

循环等待

查看死锁

查看等待锁的事务：select * from information_schema.INNODB_lock_waits; 正常正在锁的事务：select * from information_schema.INNODB_locks; 8.0是：performance_schema.data_locks performance_schema.data_lock_waits 查最近死锁记录：show engine innodb status; 查询是否锁表：show open tables where in_use>0;

用户正在运行的线程：show processlist; =select * from information_schema.processlist

id：主键，有问题时kill id

user：线程用户

host：发送请求的ip

db：命令执行的数据库

Command：

Query: 该线程正在执行一个语句

Sleep: 正在等待客户端向它发送执行语句

Connect: 一个从节点连上了主节点

Binlog Dump: 主节点正在将二进制日志 ，同步到从节点

time：该线程处于当前状态的时间

State：

Info：线程执行的语句，只显示100个字符，要查全的用show full processlist

如何定位死锁原因

1、通过应用业务日志定位到问题代码，找到相应的事务对应的sql；

2、确定数据库隔离级别

3、show InnoDB STATUS看看最近死锁的日志

避免死锁的措施

将列创建唯一索引列

在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；

在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 gap lock 导致的死锁问题；

更新表时，尽量使用主键更新；

避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；

如果业务允许，将大事务拆小

在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率

为表添加合理的索引

innodb_lock_wait_timeout

设置锁等待超时参数，默认是50s，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等 待，占用系统资源，造成严重的性能开销。

超过50s会怎样？？？

取决于innodb_lock_wait_timeout配置

off，只回滚当前语句

默认on，回滚整个事务

show variables like '%innodb_deadlock_detect%'

分表分库

海量表的处理方式

分区

在单库进行，一个表最多只能支持 1024个分区，更适合大表

NoSQL 存储

键值对存储，虽然查询性能非常高，但是不是关系型数据库，不支持事务以及稳定性方面相对 RDBMS 差一些。

分表分库

何时需要

尽量不用，有大表时有限考虑分区

当数据库表的数据量逐渐累积到一定的数量时（5000W 行或 100G 以上）， 操作数据库的性能会出现明显下降，即使我们使用索引优化或读写库分离， 性能依然存在瓶颈。此时，如果每日数据增长量非常大，我们就应该考虑分表

如何分

垂直切

垂直分库

根据业务来分库，不同的业务使用不同的数据库。

垂直分表

根据一张表中的字段，将一张表划分为两张表，其规则就是将一些不经常 使用的字段拆分到另一张表中。

水平切

水平分表

将表中的某一列作为切分的条件，按照某种规则（Range 或 Hash 取模）来切分为更小的表。

水平分库

水平分表只是在一个库中，如果存在连接数、I/O 读写以及网络吞吐等瓶颈，我们就需要考虑将水平切换的表分布到不同机器的库中

数据库分类

单库单表

单库多表

多库多表

需处理的问题

分布式事务

同一个事务中的更新两个库中的表

方案

两阶事务提交（2PC）

补偿事务提交（TCC）

跨节点join查询

冗余表或冗余字段来优化跨库 JOIN 查询

比如一些基础表，或者一两个字段的查询可以在多个库中都存在

跨节点分页查询

全局主键id

Redis 分布式锁实现一个递增的主键 ID

snowflake

通过分别截取时间、机器标识、顺序计数的位数组成一个 long 类型的主键 ID

扩容

表设计优化

不同类别存在差异，如何设计表结构？

反例

使用相同字段存储不同信息

会导致程序设计复杂化、表宽度大，从而减少磁盘单页存储行数，影响查询性能，且维护成本高；

新增字段来维护各自的信息

导致一张表中字段过多，如果有新的商 品类型出现，又需要动态添加字段。

正例

通过一个公共表字段来存储一些具有共性的字段，创建单独的商品类型表

缺点：可能会导致表非常 多，查询商品信息的时候不够灵活，不好实现全文搜索

基于一个公共表来存储商品的公共信息，同时结合搜索引擎，将商品详细 信息存储到键值对数据库，例如 ElasticSearch、Solr 中。

表性能瓶颈，如何解决

购物车

近期的数据为热数据，较久的数据为冷数据，热数据放在redis中，缓存中查不到再去数据库查

库存车

促销活动的库存更新到缓存中，通过缓存来查询商品的实时库存， 并且通过分布式锁来实现库存扣减、锁定库存。

促销活动

使用缓存和分布式锁来查询、更新优惠券和红包的数量，通过缓存获取数量成功以后， 再通过异步方式更新数据库中优惠券和红包的数量

海量数据表，如何设计表结构？

如果确定后期做表升级未分库分表，提前使用snowflake生成主键id

如果要使用水平分表，怎么选字段？

分表分库后，如何查询

join查询

一般可以通过冗余一些不经常修改的配置表来实现，每个分库中冗余该表

字段信息较少的话可以直接在每个分表中冗余这些字段

分页查询

冗余信息到大数据中，后台管理系统通过大数据来查询信息，新增或修改数据后以异步的方式通知大数据更新数据

总结

在字段比较复杂、易变动、不方便统一的情况下，建议使用键值对来代替关系数据库表存储；

在高并发情况下的查询操作，可以使用缓存代替数据库操作，提高并发性能；

数据量叠加比较快的表，需要考虑水平分表或分库，避免单表操作的性能瓶颈；

尽量避免比较复杂的 JOIN 查询操作，例如冗余一些字段，减少 JOIN 查询；创建一些中间表，减少 JOIN 查询。

数据库参数设置优化

内存设置的越大越好？

错，内存设置过大会引起服务器SWAP页交换

SWAP分区：交换分区，物理内存不够用的时候，就把物理内存里不常用的数据放到交换分区里，直到再次用到的时候才会被交换 swap分区是否被使用也是Linux服务器上看内存是否够用的标准

free -mh

参数

查询

show variables like '%参数%';

修改

set global 参数='值';

mysql体系结构

第一层：客户连接器

主要包括了数据库连接、授权认证、安全管理等，该层引用了线程池， 为接入的连接请求提高线程处理效率。

第二层：Server 层

主要实现 SQL 的一些基础功能，包括 SQL 解析、优化、执行以及缓存等，其中与我们这一讲主要相关的就是缓存

第三层：存储引擎

主要负责数据的存取，这一层涉及到的 Buffer 缓存，

第四层：数据存储

主要负责将数据存储在文件系统中，并完成与存储引擎的交互

SQL语句处理过程

查询语句

详见流程图

更新语句

更新会记录到binlog和redo log中

<ul><li>redo log是为了解决crash-safe问题引入， 通过redo的两阶段提交</li></ul>

详见流程图

mysql保证数据不会丢失的两个能力

1、能够恢复到任何时间点的状态

通过binlog实现

2、能够保证MySQL在任何时间段突然奔溃，重启后之前提交的记录都不会丢失；（crash-safe）

通过redo log、undo log实现

核心日志模块

binlog归档日志

undo log回滚日志

redo log重做日志

内存调优

query cache

have_query_cache

是否支持query cache，已经在MySQL 8.0.3中弃用

query_cache_limit

query cache存放的单条query最大结果集，默认值为1M,结果集大小超过该值的query不会被cache

query_cache_min_res_unit

Query Cache每个结果集存放的最小内存大小，默认为4k

query_cache_size

系统中用于Query Cache的内存大小

query_cache_type

系统是否打开了Query Cache功能，可以设置为ON、OFF、DEMAND(只有在查询语句中使用SQL_CACHE和SQL_NO_CACHE来控制是否需要缓存）

buffer

MyISAM

key_buffer_size

服务器内存中可用内容的1/4

InnoDB

innodb_buffer_pool_size