mysql性能优化
2023-02-22 17:01:27 4 举报AI智能生成
详细myql性能优化介绍
作者其他创作
大纲/内容
mysql性能调优
无索引、索引失效导致慢查询
支持事务，每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，最好把多条sql放在begin和commit质检，组成一个事务
支持外键，一个包含外键的InnoDB表转为MYISAM会失败
聚簇索引，数据文件是和索引绑定在一起的，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询 ，先查询到主键，然后再通过主键查询到数据，因此主键不应该过大，因为主键过大， 其他索引也都会很大。
不保存表的具体行数，执行select count(*) from table时需要全表扫描
mysql5.6以后支持全文索引
行锁，提供了具有提交、回滚、和崩溃恢复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些（行锁是基于索引加的锁，如果我们在更新操作时，条件索引失效，那么行锁也会升级为表锁）mysql5.5之后默认
InnoDB
不支持事务
不支持外键
创建的所有索引都是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
支持全文索引，查询效率上要高
MyISAM
全表锁，存储在内容中，速度快，但会占用和数据量成成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表
Memory
是一组MYISAM表的组合
Merge
黑洞，任何写入到此引擎的数据均会被丢弃掉， 不做实际存储；Select语句的内容永远是空
BLACKHOLE
存储引擎
死锁
锁等待
SELECT *
SELECT COUNT(*)
对非索引字段进行排序
不恰当的SQL语句
常见诱因
id
SIMPLE：普通查询
PRIMARY：主查询
UNION：union中后面的查询
SUBQUERY：子查询
select_type：SELECT查询类型
table：当前执行计划查的表，用别名则显示别名
partitions：访问的分区表信息
案例：EXPLAIN SELECT * FROM tableA where id='1';
eq_ref：唯一索引扫描，常见于夺标连接中使用主键和唯一索引作为关联条件
案例：EXPLAIN SELECT * FROM order where order_no='2';
ref：非唯一索引扫描，或唯一索引最左原则匹配扫描
案例：EXPLAIN SELECT * FROM order where id>1;
range：索引范围扫描，比如> < between操作
案例：EXPLAIN SELECT id FROM tableA ;
index：索引全表扫描，遍历整个索引树
案例：EXPLAIN SELECT * FROM tableA where cname='sdf';
ALL：全表扫描
* type：表示从表中查询到行所执行的方式（结果从好到差排序）
possible_keys：可能用到的索引
key：实际使用到的索引
key_len：当前使用的索引长度
ref：关联id等信息
row：查找到记录所扫描的行数
filtered：查找到所需记录占总扫描记录数的比例
表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。
using index
使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低
using temporary
using filesort
Extra：额外信息
1、通过 EXPLAIN + sql分析 SQL 执行计划
查询：select @@have_profiling
开启：set profiling=true;
ALL：显示所有开销信息
BLOCK IO：阻塞的输入输出次数
CONTEXT SWITCHES：上下文切换相关开销信息
CPU：显示 CPU 的相关开销信息
IPC：接收和发送消息的相关开销信息
MEMORY ：显示内存相关的开销，目前无用
PAGE FAULTS ：显示页面错误相关开销信息
SWAPS：显示 swap 交换次数的相关开销信息
SHOW PROFILE [ALL、CPU、MEMORY、BLOCK IO、CONTEXT SWITCHES] FOR QUEYR query_id LIMIT row_count;
2、通过Show Profile分析SQL执行性能（>=5.0.37）
用子查询替代limit
优化分页查询
如果业务场景不需要返回精确的count值，
使用近似值
优化select count(*)
优化select *
3、常用的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;
4、慢sql查询
优化SQL的步骤
A原子性
C一致性
I隔离性
D持久性
事务内的语句要么全部成功，要不全部失败，良好的事务处理系统，必须具体的四个特征：
事务
悲观锁解决：for update
数据丢失
一个事务读到了另一个未提交事务修改过的数据
脏读
一个事务A查一条数据，事务B修改了这条数据并提交，事务A再次查了这条数据，结果变成修改之后的了。
不可重复读
一个事务A根据条件查出一些数据，另一个事务B插入了符合条件的数据，事务A再次查询时，结果能把事务B插入的数据也查出来
幻读
并发事务带来的问题
允许一个事务读数据，不允许修改，其他事务要对这行数据加锁，只能加共享锁。
共享锁S：lock in share model
可以读取和修改，一旦一个事务对该行数据加排他锁，其他事务将不能再对该数据加任务锁。
排它锁X：for update
锁机制（按锁的使用方式，行锁）
通过索引实现，如果不通过索引条件检索数据，行锁将会升级到表锁。
record lock：记录锁，单条记录上锁
gap lock：间隙锁，锁定记录的前后范围，但不包含记录本身
next-key lock：临键锁，锁定记录本身和前后范围
三种算法
行锁
表锁
页锁
全局锁
锁机制（按锁的粒度）
很少用：事务A可以读到事务B未提交的数据。所以会产生脏读、不可重复读，幻读；
未提交读（Read Uncommitted）
事务A只能读到最新提交的数据，会产生不可重复读、幻读
已提交读（Read Committed）
每个读操作加了共享锁，即其他事务也只能读，不能改；事务修改数据时，用了行级排它锁，其他事务不能修改当前数据。同一事务多次查询会看到相同的数据。会产生幻读。
可重复读（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（索引中存储了实际的列值，所以某些查询只使用索引就能完成全部查询）
是存储引擎用于快速找到记录的一种数据结构。
介绍
叶子节点记录了主键值、事务 id、用于事务和 MVVC 的回流指针以及所有的剩余列。使用这种索引检索数据，索引找到对应的叶子节点，直接就可以获取到行数据。
聚簇索引（Clustered Index）
叶子节点记录的是主键值。使用这种索引检索数据，先找到叶子节点的主键值，再通过聚族索引中的 B+ 树检索到对应的叶子节点，然后获取整行数据。这个过程叫做回表
非聚簇索引（（Secondary Index）辅助索引、二级索引）
类型
和索引中的所有列进行匹配
全值匹配
最左匹配
匹配列前缀
精确匹配某一列并范围匹配另一列
只访问索引的查询
使用（复合）索引查询或排序时，有效：
不满足最左匹配原则
比如使用order_no+user_id查询，那么索引只能使用order_no列
跳过了索引中的列
查询中有某个列的范围查询，则其右边所有的列都无法使用索引优化查找
查询条件中使用or，且or的前后条件中有一个列没有索引，那有索引的列也不会被用到索引
函数操作或表达式计算
where条件中类型为字符串的字段没有使用引号引起来；【查询where条件数据类型不匹配也无法使用索引，字符串与数字比较不使用索引，因为正则表达式不使用索引，如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描】
在索引字段上使用“not”，“<>”，“!=”等
如果MySQL评估使用索引比全表扫描更慢，则不使用索引
使用（复合）索引查询或排序时，失效：
B-Tree索引
哈希索引（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;
索引不能是表达式的一部分，也不能是函数的参数，养成始终将索引单独放在比较符号的一侧的习惯
独立的列
含义：使用某个字段中字符串的前几个字符建立索引
1、先计算某字段全列的区分度SELECT COUNT(DISTINCT column_name)/COUNT(*) FROM table_name;
如何用：ALTER TABLE table_name ADD KEY(column_name(prefix_length));//前缀长度的确定方法：
前缀索引优化
将选择项最高的列放在索引的最前列
索引列顺序非常重要
当出现服务器对多个索引做and操作时
当服务器需要对多个索引做or操作时
索引合并：》mysql5.0引入的策略，一定程度上可以使用表上的多个单列索引来定位指定的行
如果explain中看到索引合并时
何时创建
多列索引
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_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_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_locksperformance_schema.data_lock_waits查最近死锁记录：show engine innodb status;查询是否锁表：show open tables where in_use>0;
id：主键，有问题时kill id
user：线程用户
host：发送请求的ip
db：命令执行的数据库
Query: 该线程正在执行一个语句
Sleep: 正在等待客户端向它发送执行语句
Connect: 一个从节点连上了主节点
Binlog Dump: 主节点正在将二进制日志 ，同步到从节点
Command：
time：该线程处于当前状态的时间
State：
Info：线程执行的语句， 只显示100个字符，要查全的用show full processlist
用户正在运行的线程：show processlist;=select * from information_schema.processlist
查看死锁
1、通过应用业务日志定位到问题代码，找到相应的事务对应的sql；
2、确定数据库隔离级别
3、show InnoDB STATUS看看最近死锁的日志
如何定位死锁原因
将列创建唯一索引列
在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；
在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 gap lock 导致的死锁问题；
更新表时，尽量使用主键更新；
避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
如果业务允许，将大事务拆小
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率
为表添加合理的索引
设置锁等待超时参数，默认是50s，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。
off，只回滚当前语句
默认on，回滚整个事务
show variables like '%innodb_deadlock_detect%'
取决于innodb_lock_wait_timeout配置
超过50s会怎样？？？
innodb_lock_wait_timeout
避免死锁的措施
在单库进行，一个表最多只能支持 1024个分区，更适合大表
分区
键值对存储，虽然查询性能非常高，但是不是关系型数据库，不支持事务以及稳定性方面相对 RDBMS 差一些。
NoSQL 存储
分表分库
海量表的处理方式
尽量不用，有大表时有限考虑分区
当数据库表的数据量逐渐累积到一定的数量时（5000W 行或 100G 以上），操作数据库的性能会出现明显下降，即使我们使用索引优化或读写库分离，性能依然存在瓶颈。此时，如果每日数据增长量非常大，我们就应该考虑分表
何时需要
根据业务来分库，不同的业务使用不同的数据库。
垂直分库
根据一张表中的字段，将一张表划分为两张表，其规则就是将一些不经常使用的字段拆分到另一张表中。
垂直分表
垂直切
将表中的某一列作为切分的条件，按照某种规则（Range 或 Hash 取模）来切分为更小的表。
水平分表
水平分表只是在一个库中，如果存在连接数、I/O 读写以及网络吞吐等瓶颈，我们就需要考虑将水平切换的表分布到不同机器的库中
水平分库
水平切
单库单表
单库多表
多库多表
数据库分类
如何分
同一个事务中的更新两个库中的表
两阶事务提交（2PC）
补偿事务提交（TCC）
方案
分布式事务
比如一些基础表，或者一两个字段的查询可以在多个库中都存在
冗余表或冗余字段来优化跨库 JOIN 查询
跨节点join查询
跨节点分页查询
Redis 分布式锁实现一个递增的主键 ID
通过分别截取时间、机器标识、顺序计数的位数组成一个 long 类型的主键 ID
snowflake
全局主键id
扩容
需处理的问题
会导致程序设计复杂化、表宽度大，从而减少磁盘单页存储行数，影响查询性能，且维护成本高；
使用相同字段存储不同信息
导致一张表中字段过多，如果有新的商品类型出现，又需要动态添加字段。
新增字段来维护各自的信息
反例
缺点：可能会导致表非常多，查询商品信息的时候不够灵活，不好实现全文搜索
通过一个公共表字段来存储一些具有共性的字段，创建单独的商品类型表
基于一个公共表来存储商品的公共信息，同时结合搜索引擎，将商品详细信息存储到键值对数据库，例如 ElasticSearch、Solr 中。
正例
不同类别存在差异，如何设计表结构？
近期的数据为热数据，较久的数据为冷数据，热数据放在redis中，缓存中查不到再去数据库查
购物车
促销活动的库存更新到缓存中，通过缓存来查询商品的实时库存，并且通过分布式锁来实现库存扣减、锁定库存。
库存车
使用缓存和分布式锁来查询、更新优惠券和红包的数量，通过缓存获取数量成功以后，再通过异步方式更新数据库中优惠券和红包的数量
促销活动
表性能瓶颈，如何解决
如果确定后期做表升级未分库分表，提前使用snowflake生成主键id
如果要使用水平分表，怎么选字段？
一般可以通过冗余一些不经常修改的配置表来实现，每个分库中冗余该表
字段信息较少的话可以直接在每个分表中冗余这些字段
join查询
冗余信息到大数据中，后台管理系统通过大数据来查询信息，新增或修改数据后以异步的方式通知大数据更新数据
分页查询
分表分库后，如何查询
海量数据表，如何设计表结构？
在字段比较复杂、易变动、不方便统一的情况下，建议使用键值对来代替关系数据库表存储；
在高并发情况下的查询操作，可以使用缓存代替数据库操作，提高并发性能；
数据量叠加比较快的表，需要考虑水平分表或分库，避免单表操作的性能瓶颈；
尽量避免比较复杂的 JOIN 查询操作，例如冗余一些字段，减少 JOIN 查询；创建一些中间表，减少 JOIN 查询。
总结
表设计优化
SWAP分区：交换分区，物理内存不够用的时候，就把物理内存里不常用的数据放到交换分区里，直到再次用到的时候才会被交换swap分区是否被使用也是Linux服务器上看内存是否够用的标准
free -mh
错，内存设置过大会引起服务器SWAP页交换
内存设置的越大越好？
show variables like '%参数%';
查询
set global 参数='值';
修改
参数
主要包括了数据库连接、授权认证、安全管理等，该层引用了线程池，为接入的连接请求提高线程处理效率。
第一层：客户连接器
主要实现 SQL 的一些基础功能，包括 SQL 解析、优化、执行以及缓存等，其中与我们这一讲主要相关的就是缓存
第二层：Server 层
主要负责数据的存取，这一层涉及到的 Buffer 缓存，
第三层：存储引擎
主要负责将数据存储在文件系统中，并完成与存储引擎的交互
第四层：数据存储
mysql体系结构
详见流程图
查询语句
redo log是为了解决crash-safe问题引入，通过redo的两阶段提交
通过binlog实现
1、能够恢复到任何时间点的状态
通过redo log、undo log实现
2、能够保证MySQL在任何时间段突然奔溃，重启后之前提交的记录都不会丢失；（crash-safe）
binlog归档日志
undo log回滚日志
redo log重做日志
核心日志模块
mysql保证数据不会丢失的两个能力
更新会记录到binlog和redo log中
更新语句
SQL语句处理过程
是否支持query cache，已经在MySQL 8.0.3中弃用
have_query_cache
query_cache_limit
Query Cache每个结果集存放的最小内存大小，默认为4k
query_cache_min_res_unit
系统中用于Query Cache的内存大小
query_cache_size
系统是否打开了Query Cache功能，可以设置为ON、OFF、DEMAND(只有在查询语句中使用SQL_CACHE和SQL_NO_CACHE来控制是否需要缓存）
query_cache_type
query cache
服务器内存中可用内容的1/4
key_buffer_size
innodb_buffer_pool_size
buffer
内存调优
数据库参数设置优化
0 条评论
回复 删除
下一页
