MYSQL优化知识集锦思维导图模板_ProcessOn思维导图、流程图

10.慢查询日志

定位查询效率较低的SQL，针对性地做优化

配置项

开启slow_query_log

临界时间long_query_time

慢查询日志会自己记录超过临界时间的SQL，并保存在datadir下的xxx-show.log中

9.典型SQL

线上DDL

为了避免长时间表级锁定

copy策略，逐行复制，记录复制期间旧表SQL日志重新执行

mysql5.6 online ddl,大大缩短锁定时间

批量导入

先禁用索引和约束，导入之后统一建立

避免逐条事务

innodb为保证一致性，默认为每条SQL加事务（也是要耗费时间的），批量导入前应手动建立事务，导入完毕后手动提交事务

limit offset,rows

避免较大的offset（较大页码数）

offset用来跳过数据，完全可以用过滤筛选数据，而不是查出来之后再通过offset跳过

select *

尽量查询所需字段，减少网络传输延时（影响不大）

order by rand()

会为每条数据生成一个随机数最后根据随机数排序，可以使用应用程序生成随机主键代替

limit 1

如果确定了仅仅检索一条数据，建议都加上Limit 1

8.集群

主从复制

首先手动将slave和master同步一下

stop slave

master导出数据到slave执行一遍

show master status with read lock 记录File和Position

到slave上chage master to

start slave 查看Slave_IO_Running和Slave_SQL_Running，必须都是YES

master 可读可写，但slave只能读，否则主从复制会失效需要重新手动同步

mysql replicate快速配置主从复制

读写分离（基于主从复制）

使用原生javax.sql.Connection

WriteDatabase提供写连接

ReadDatabase提供读连接

借助Spring AOP和Aspect实现数据源动态切换

RoutingDataSourceImpl extends AbstractRoutingDataSource，重写determineDatasource，注入到SqlSessionFactory，配置defaultTargetDatasource和targetDatasource（根据determineDatasource的返回值选择具体数据源value-ref）

DatasourceAspect切面组件，配置切入点@Pointcut aspect()（所有DAO类的所有方法），配置前置增强@Before("aspect()")before（Joinpoint point），通过point.getSignature.getName获取方法名，与METHOD_TYPE_MAP的前缀集合比对，将write/read设置到当前线程上（也是接下来要执行DAO方法的线程，前置增强将其拦截下来了）

DatasourceHandler，使用ThreadLocal在前置通知中将方法要使用的数据源绑定到执行该方法的线程上，执行方法要获取数据源时再根据当前线程获取

负载均衡

算法

轮训

加权轮训

依据负载情况

高可用

为单机服务提供一个冗余机

心跳检测

虚IP

主从复制

7.水平分割和垂直分割

水平

多张结构相同的表存储同一类型数据

单独一张表保证id唯一性

垂直

分割字段到多张表，这些表记录是一一对应关系

6.分区

默认情况下一张表对应一组存储文件，但当数据量较大时（通常千万条级别）需要将数据分到多组存储文件，保证单个文件的处理效率

partition by 分区函数（分区字段）（分区逻辑）

hash-分区字段为整型

key-分区字段为字符串

range-基于比较，只支持less than

list-基于状态值

分区管理

创建时分区

create table article() partition by key(title) partitions 10

修改表结构

alter table article add partition（分区逻辑）

分区字段应选择常用的检索字段，否则分区意义不大

5.查询缓存

将select查询结果缓存起来，key为SQL语句，value为查询结果

如果SQL功能一样，但只是多个空格或略微改动都会导致key的不匹配

客户端开启

query_cache_type

0-不开启

1-开启，默认缓存每条select，针对某个sql不缓存：select sql-no-cache

2-开启，默认都不缓存，通过select sql-cache制定缓存哪一条

客户端设置缓存大小

query_cache_size

重置缓存

reset query cache

缓存失效

对数据表的改动会导致基于该数据表的所有缓存失效（表层面的管理）

1.概述

为什么要优化？

一个应用吞吐量瓶颈往往出现在数据库的处理速度上

随着应用程序的使用，数据库数据逐渐增多，数据库处理压力逐渐增大

关系型数据库的数据是存放在磁盘上的，读写速度较慢（与内存中的数据相比）

如何优化？

表、字段的设计阶段，考量更优的存储和计算

数据库自身提供的优化功能，如索引

横向扩展，主从复制，读写分离，负载均衡和高可用

典型SQL语句优化（收效甚微）

2.字段设计

典型方案

对精度有要求

decimal

小数转整数

尽量使用整数表示字符串

IP

inet_aton("ip")

inet_aton("num")

尽可能使用not null

null数值的计算逻辑比较复杂

定长和非定长的选择

较长的数字数据可以使用decimal

1.经常变化的字段用varchar&#13; 2.知道固定长度的用char&#13; 3.尽量用varchar&#13; 4.超过255字符的只能用varchar或者text&#13; 5.能用varchar的地方不用text&#13; 6.存储可变长度的非Unicode数据，最大长度为2^31-1个字符。text列不能有默认值

字段数不要过多、字段注释是必要的、字段命名见名思意、可以预留字段以备扩展

范式

第一范式

字段原子性（关系型数据库有列的概念，默认就符合了）

第二范式

消除对主键的部分依赖（因为主键可能不止一个）

使用一个与业务无关的字段作为主键

第三范式

消除对主键的传递依赖

高内聚、如商品表可分为商品简略信息表和商品详情表两张表

3.存储引擎的选择（MyISAM和Innodb）

功能差异

Innodb支持事务、行级锁定、外键

存储差异

存储方式

MyISAM的数据和索引是分开存储的（.MYI，.MYD），而Innodb是存在一起的（.frm）

表可移动性

可以通过移动表对应的MYI和MYD能够实现表的移动，而Innodb还有额外的关联文件

碎片空间

MyISAM删除数据时会产生碎片空间（占用表文件空间），需要定期通过optimize table table-name 手动优化，而Innodb不会

有序存储

Innodb插入数据时按照主键有序来插入，因此表中数据默认按主键有序（耗费写入时间，因为需要在b+tree中查找插入点，但查找效率高）

选择依据

读多写少用MyISAM

新闻、博客网站

读多写多用Innodb

支持事务/外键，保证数据一致性、完整性

并发能力强（行锁）

4.索引

什么是索引

从数据中提取的具有标识性的关键字，并且有到对应数据的映射关系

类型

主键索引 primary key

要求关键字唯一且不为null

普通索引 key

符合索引仅按照第一字段有序

唯一索引 unique key

要求关键字唯一

全文索引 fulltext key（不支持中文）

索引管理语法

查看索引

(1)show index from tblname;&#13; (2)show keys from tblname;

建立索引

创建时指定，如 first_name varchar(16),last_name(16), key name(first_name,last_name)

修改索引

alter table student add key/unique key/primary key/fulltext key key_name(first_name,last_name)

删除索引

alter table student drop key key_name

如果删除的是主键索引，并且主键自增长，则需要alter modify先取消自增长再删除

执行计划explain

分析sql执行是否用到了索引，用到了什么索引

索引的使用场景

where

如果查到字段都建立了索引，则会索引覆盖

order by

如果排序字段建立了索引，而索引又是有序排列的，直接根据索引拿对应数据即可，与读取查询出来的所有数据再排序相对效率很高

join

如果join on的条件字段建立了索引，查找会变得高效

索引覆盖

直接对索引做查找，而不去读取数据

语法细节

即使建立了索引，有些场景也不一定适用

where id+1=?建议写成where id = ?-1，即保证索引字段的独立出现

like语句就不要在关键字前模糊匹配，即'%keyword%'不会适用索引，而'keyword%'会使用索引

or关键两边条件字段都建立索引时才会使用索引，只要有一边不是就会做全表扫描

状态值，像性别这样的状态值，一个关键字对应很多条数据，会认为使用索引比全表扫描效率还低

索引的存储结构

B Tree

搜索多叉树：节点内关键字有序排列，关键字之间有一个指针，查找效率log(nodeSize,N)，其中nodeSize指一个节点内关键字数量（这取决于关键字长度和节点大小）

B+ Tree

由B tree升级而来，数据和关键字存在一块空间，省去了由关键字到数据的映射找数据存放地的时间

13.压测工具mysqlslap

自动生成sql并执行来测试性能

mysqlslap --auto-generate-sql -uroot -proot

并发测试

mysqlslap -auto-generate-sql --concurrency-100 -uroot -proot,模拟100个客户端执行sql

多轮测试，反应平均情况

mysqlslap --auto-generate-sql --concurrency=100 --inerations=3 -uroot -proot，模拟100个客户端执行sql，执行三轮

存储引擎测试

--engine=innodb

mysqlslap --auto-generate-sql --concurrency=100 --inerations=3 --engine-innodb -uroot -proot，模拟100个客户端执行sql，执行3轮，innodb的处理性能

--engine=myisam

mysqlslap --auto-generate-sql --concurrency=100 --inerations=3 --engine-myisam -uroot -proot，模拟100个客户端执行sql，执行3轮，innodb的处理性能

12.典型的服务器配置

max_connections，最大客户端连接数

table_open_cahce，表文件缓存句柄数，加快表文件的读写

key_buffer_size，索引缓存大小

innodb_buffer_pool_size，innodb的缓冲池大小，实现innodb各种功能的前提

innodb_file_per_table，每个表一个idb文件，否则innodb共享表空间

11.profile

自动记录每条SQL的执行时间和具体某个SQL的详细步骤花费的时间

配置项

开启profiling

查看日志信息show profiles

查看具体SQL的详细步骤花费的时间

show profiles for query Query_ID