MySQL优化笔记整理

A. 优化简介

1. 衡量指标

Qps：Transmission Per Second（每秒传输的事务处理个数），指服务器每秒处理的事务个数，支持事务的存储引擎的一个特有指标。

TPS=（COM_COMMIT+COM_ROLLBACK）/UPTIME（即（事务提交数+事务回滚数）/服务器启动时间）

Tps：Queries Per Second（每秒查询处理量），同时使用与InnoDB和MyISAM引擎

TPS=QUESTIONS/UPTIME（即查询量/服务器启动时间）

2. 常用工具

a) MySqlSlap

mysqlslap -uroot -padmin --concurrency=500 --iterations=3 --number-char-cols=5 --number-int-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --create-schema='alyshentest1'

或者测试单一个存储引擎

mysqlslap -uroot -padmin --concurrency=200 --iterations=3 --number-char-cols=5 --number-int-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=innodb --create-schema='alyshentest1'

打印CPU信息加参数：--debug-info（5.7已禁用）

B. MySQL逻辑架构

链接层、服务层、引擎层、存储层

MySQL查询缓存

#看数据文件目录
show global variables like "%datadir%" 
#查看查询缓存是否开启
show VARIABLES LIKE 'query_cache_type'
#查询缓存大小
show VARIABLES LIKE 'query_cache_size'
show variables like 'query_cache%'
开启查询缓存：在my.ini文件中设置：
query_cache_type=1
query_cache_size=128M

然后重启MySQL

my.ini文件参数说明

port 参数也是表示数据库的端口。
basedir 参数表示MySQL的安装路径。
datadir 参数表示MySQL数据文件的存储位置，也是数据库表的存放位置。
default-character-set 参数表示默认的字符集，这个字符集是服务器端的。
default-storage-engine 参数默认的存储引擎。
sql-mode 参数表示SQL模式的参数，通过这个参数可以设置检验SQL语句的严格程度。
max_connections 参数表示允许同时访问MySQL服务器的最大连接数，其中一个连接是保留的，留给管理员专用的。

query_cache_size 参数表示查询时的缓存大小，缓存中可以存储以前通过select语句查询过的信息，再次查询时就可以直接从缓存中拿出信息。

table_cache 参数表示所有进程打开表的总数。
tmp_table_siz e参数表示内存中临时表的总数。
thread_cache_size 参数表示保留客户端线程的缓存。
myisam_max_sort_file_size 参数表示MySQL重建索引时所允许的最大临时文件的大小。
myisam_sort_buffer_size 参数表示重建索引时的缓存大小。
key_buffer_size 参数表示关键词的缓存大小。
read_buffer_size 参数表示MyISAM表全表扫描的缓存大小。
read_rnd_buffer_size 参数表示将排序好的数据存入该缓存中。
sort_buffer_size 参数表示用于排序的缓存大小

#InnoDB存储引擎使用的参数：
innodb_additional_mem_pool_size 参数表示附加的内存池，用来存储InnoDB表的内容。
innodb_flush_log_at_trx_commit 参数是设置提交日志的时机，若设置为1，InnoDB会在每次提交后将事务日志写到磁盘上。
innodb_log_buffer_size 参数表示用来存储日志数据的缓存区的大小。
innodb_buffer_pool_size 参数表示缓存的大小，InnoDB使用一个缓冲池类保存索引和原始数据。
innodb_log_file_size 参数表示日志文件的大小。
innodb_thread_concurrency 参数表示在InnoDB存储引擎允许的线程最大数。

C. 存储引擎

查看引擎列表：SHOW ENGINES;

查看默认存储引擎：SHOW VARIABLES LIKE ‘%storage_engine%‘

1. MyISAM

MySQL5.5之前默认的存储引擎，由MYD数据文件和MYI索引文件组成，也就是所谓的非聚集索引。在并发性与锁级别方面支持表级锁，支持全文检索，支持数据压缩。
使用压缩如下：

myisampack -b -f product_info.MYI

检查表：CHECK TABLE product_info;
修复表：REPAIR TABLE product_info;
适用场景：非事务型应用（数据库仓库、报表、日志数据）
只读类应用，空间类应用（空间函数，坐标）

2. InnoDB

MySQL5.5及以后默认使用的存储引擎，可以使用系统表空间和独立表空间，建议使用独立表空间，因为独立表空间可以收缩文件大小，可以同时向多个文件刷新数据，系统表空间无法收缩文件大小，而且会产生IO瓶颈
查看InnoDB是否使用独立表空间：

SHOW GLOBAL VARIABLES LIKE '%innodb_file_per_table%';
#设置InnoDB使用独立表空间
SET GLOBAL innodb_file_per_table=ON;

值为ON，使用的是.idb文件，值为OFF，使用的是ibdataX

收缩表空间语法：

OPTIMIZE TABLE product_info2;

特性：InnoDB是一种事务性存储引擎，完全支持事务的ACID特性以及Redo Log和Undo Log，InnoDB支持行级锁，使得并发程度更高.

实用场景：InnoDB使用与大多数OLTP（联机事务处理）应用

3. CSV

特点：以csv格式进行数据存储，所用的列不能为null，不支持索引（不适合大表，不适合在线处理），可以对数据文件直接编辑，保存文本文件内容。

编辑后需要刷新表：

FLUSH TABLE;
REPAIR TABLE csv_table;

4. Archive

组成：以zlib对表数据进行压缩，磁盘I/O更少，数据存储在以ARZ为后缀的文件中。

特点：只支持insert和select操作，只允许在自增ID列上加索引。

使用场景：日志和数据采集应用。

5. Memory

文件系统存储特点，也称HEAP存储引擎，所有数据保存在内存中，支持HASH索引和Btree索引，所有字段都是固定长度，不支持Blog和Text等大字段，Memory存储引擎使用表级锁，最大大小由max_heap_table_size决定

使用场景：hash索引用于查找或者是映射表（邮编和地区的对应表），用于保存数据分析的中间表，用于缓存周期性聚合数据的结果表。

Memory数据容易丢失，所以要求数据可再生

6. Federated

特点：提供了访问远MySQL服务器上表的方法，本地不存储数据，数据全部放到远程服务器上，本地需要保存表结构和远程服务器的链接信息。

在my.ini文件中开启使用federated

federated=1

实现示例：

-- federated
CREATE DATABASE remote;

USE remote;
CREATE TABLE t_remote(
  `id` int(20) NOT NULL,
  `name` varchar(150) NOT NULL,
  `remark` varchar(255) NOT NULL
)
SELECT * FROM t_remote;

USE mysqldemo;

CREATE TABLE federated_remote(
  `id` int(20) NOT NULL,
  `name` varchar(150) NOT NULL,
  `remark` varchar(255) NOT NULL
)ENGINE=federated CONNECTION='mysql：//root：admin@localhost：3306/remote/t_remote';

show TABLES;
SELECT * FROM federated_remote;
INSERT INTO federated_remote VALUES(2,'6565','9854');

使用场景：偶尔的统计查询及手工分析

D. 锁

1. MyISAM表共享读锁

-- 共享读锁

-- 上锁
LOCK TABLE innodb_table READ;

INSERT INTO innodb_table VALUES(2,'12332','3654'); -- 报错
INSERT INTO innodb_table2 VALUES(2,'12332','3654'); -- 报错，在另一个会话中成功

SELECT * FROM innodb_table -- ok 

-- 解锁
UNLOCK TABLES

-- 查看锁表次数
show status LIKE 'table_locks_waited'

2. MySIAM表读占写锁

-- MyISAM独占写锁
LOCK TABLE myisam_table WRITE;
INSERT INTO myisam_table(name,remark) VALUES('12332','3654'); -- ok
UPDATE myisam_table SET `name`='32133' WHERE id=1;                      -- ok
INSERT INTO innodb_table(name,remark) VALUES('12332','3654'); -- 报错

SELECT * FROM myisam_table; -- ok

-- 在另外一个会话中 INSERT、updata 等待

UNLOCK TABLES

3. MyISAM锁调度

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写 锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为 写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从 而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM的调度行为。

·通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。

·通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。

·通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。

另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出 现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通 过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段 执行，比如一些定期统计可以安排在夜间执行。

4. InnoDB行锁

共享锁：又称读锁，当一个事务对某几行上读锁时，允许其他事务对这几行进读操作，但不允许进行写操作，也不允许其他事务对这几行上排它锁，但允许上读锁。

排它锁：又称写锁，当一个事务对某几行上写锁时，不允许其他事务写，但允许读，更不允许其他事务对这几行上任何锁，包括写锁。

上共享锁：

BEGIN; -- 读锁和写锁都要先开启事务

SELECT * FROM innodb_table WHERE id = 1 LOCK IN SHARE MODE;

-- 在另外一个会话中：
SELECT * FROM innodb_table WHERE id = 1; -- ok
UPDATE innodb_table SET `name`='商品名' WHERE id=1; -- 等待

-- 释放锁
COMMIT;
ROLLBACK;

上排它锁：

BEGIN; 

SELECT * FROM innodb_table WHERE id = 1 FOR UPDATE;
-- 在另外一个会话中：
SELECT * FROM innodb_table WHERE id = 1; -- ok
UPDATE innodb_table SET `name`='商品名' WHERE id=1; -- 等待

-- 释放锁
COMMIT;
ROLLBACK;

5. InnoDB表锁

InnoDB的表锁跟MyISAM的表锁相似，另外执行开启事务的begin语句也也会释放InnoDB的表锁

6. pt-online-schema-change工具的使用

安装perl环境

yum -y install perl
mkdir perl

安装DBI

mv DBI-1.637.tar.gz perl/
cd perl/
tar zxvf DBI-1.637.tar.gz
cd DBI-1.637
perl Makefile.PL
make
make install
yum search gcc
yum -y install gcc-c++
make install

安装perl-DBD-MySQL

yum install perl-DBD-MySQL

安装percona-toolkit

mv percona-toolkit_2.2.12.tar.gz perl/
cd perl/
tar zxvf percona-toolkit_2.2.12.tar.gz
cd percona-toolkit-2.2.12/
perl Makefile.PL
make
make test
make install

使用percona-toolkit

cd bin/
perl pt-online-schema-change h=192.168.86.126,p=admin,u=root,D=mysqldemo,t=innodb_table --alter "modify name varchar(200) not null default '' " –execute

E. 事务

1. 事务特性

原子性、一致性、隔离性、持久性

2. 隔离级别

读未提交、读已提交、可重复读、串行化

-- 设置事务隔离级别，只对下一个事务有效。
set transaction isolation level {事务隔离级别};

-- 设置事务隔离级别，对当前会话的事务有效。
set session transaction isolation level {事务隔离级别};

-- 设置事务隔离级别，对后面建立MySQL连接的事务有效。
set global transaction isolation level {事务隔离级别};

-- 事务的隔离级别
read uncommitted(读取未提交):
-- 该级别引发的问题是脏读，会读取到其他事务未提交的数据。

read committed(读取已提交):
-- 该级别引发的问题是不可重复读，即设置为该级别的事务只能读取到其他事务已经提交的数据，未提交的数据不能读取，会造成多次查询的结果不一致。

repeatable read(可重复读):
-- 该级别引发的问题是幻读，即当用户修改某一范围内的数据行时，另一个事务又在该范围内插入了新的行，当用户再次读取该范围内的数据时，会发现有新的数据行没有被修改。
-- 该级别是MySQL数据库默认的事务隔离级别。注意：该级别不会对事务查询到的行加行锁，也就是该事务查询到的行，其他事务依然能进行修改，但是能保证数据的一致性。

serializable(可串行化):
-- 该级别是MySQL中事务隔离级别最高的，该级别会锁住事务中操作的整张表，因此不会出现以上三个级别的问题。但是这种隔离级别并发性极地，开发中很少会用到。

3. 事务操作

-- 关闭自动提交事务
set autocommit=0;
-- 开启自动提交事务，默认为开启。
set autocommit=1;

-- 开启事务
start TRANSACTION; -- 推荐使用
BEGIN;
BEGIN WORK;

-- commit用于提交事务，只有当自动提交事务被关闭时需要使用。
commit
-- rollback用于回滚事务，撤销对于数据库所做的未提交的操作。
ROLLBACK;

4. 还原点

-- 还原点

show variables like '%autocommit%'; -- 自动提交事务是开启的
set autocommit=1;

insert into testdemo values(5,5,5);
-- 创建保存点
savepoint s1; 

insert into testdemo values(6,6,6);
savepoint s2;

insert into testdemo values(7,7,7);
savepoint s3;

-- 用于删除一个保存点，如果指定的保存点不存在，将会抛出一个异常。
release savepoint s3;

select * from testdemo;

-- 把事务回滚到指定的保存点。
rollback to savepoint s2;

rollback;

F. 慢查询

1. 慢查询配置

慢查询基本配置

-- 开启慢查询日志
SHOW VARIABLES LIKE '%slow_query_log%';
SET GLOBAL slow_query_log=1;

-- 慢查询时间设定
SHOW VARIABLES LIKE '%long_query_time%';
SET GLOBAL long_query_time=3; -- 3秒

SELECT SLEEP(4); -- 4秒的查询
-- 日志文件输出，默认file，建议使用默认的file
SHOW VARIABLES LIKE '%log_output%';
-- 慢查询日志文件输出路径
SHOW VARIABLES LIKE '%datadir%';

2. 慢查询分析

慢查询解读

使用MySQLdumpslow

/usr/bin/mysqldumpslow -s r -t 10 localhost-slow.log

使用Pt_query_digest

perl /usr/local/bin/pt-query-digest localhost-slow.log

localhost-slow.log为慢查询日志

G. 索引

MySQL 的索引分为普通索引、唯一索引、复合索引、聚簇索引和非聚簇索引5大类

1. 索引操作

-- 查看指定数据表的索引。
show index from test.student;

-- 删除指定的索引。
drop index index_name on test.student;

-- 修改表结构的方式删除索引。
alter table test.student drop index index_name;

-- 创建普通索引。
create index index_name on test.student(StuNo);

-- 修改表结构的方式添加索引，这种方式可以不指定索引名称，不指定系统会自动默认一个索引名称。
alter table test.student add index index_name(StuNo);

-- 创建唯一索引，指定创建唯一索引的列的值必须是唯一的，不能重复，但是可以为null。
create unique index index_name on test.student(StuNo);

-- 修改表结构的方式添加唯一索引。
alter table test.student add unique index index_name(StuNo);

-- 修改表结构的方式添加主键，必须保证添加主键的列的值不能为null，并且是唯一的，不可重复。
alter table test.student add primary key PrimaryKey_Name(ID);

-- 删除指定数据表的主键，删除主键时只需指定 primary key，删除索引时必须指定索引名。
-- 注意：当主键列同时是自增长列时，不能直接删除主键，需要先删除自增长约束。
alter table test.student drop primary key;

-- 添加全文索引。
alter table test.student add fulltext index_name(StuNo);

-- 加上关键字ignore创建的唯一索引和主键，在插入重复数据时，会直接过滤掉重复数据，并且不会报错，否则就会抛出错误。
alter ignore table test.student add primary key(ID);
alter ignore table test.student add unique index index_name(StuNo);

2. 什么情况下适合建索引？

1） 某一列相对来说唯一
2） 经常用来查询显示的列（覆盖索引）
3） 经常用来关联的列，where条件中用到的列，以及join on用到的列

H. 执行计划

Explain的10个参数

1. id：

查询的一个排序，值越高执行的优先级越高，值相同，依次执行

2. select_type

查询类型，用于区别普通查询，复合查询、子查询等复杂查询
Simple：简单查询；
Primary：如果有复杂的子查询，最外层标记为primary；
Subquery：子查询
Derived：衍生查询
Union：若第二个select出现在union之后，标记为union，若union包含在from字句的子查询中，外层的select将被标记为derived
union result：从union表获取结果的select

3. table：

查询的表

4. type：

访问类型，是一个重要的指标，常见的指标排序：
system > const > eq_ref > ref >range > all
system：表只有一条数据
const：通过索引只查一次就能找到
eq_ref：唯一性索引扫描，表中只有一条记录匹配
ref：非唯一性索引扫描，表中有多条记录匹配
range：检索给定范围
all：全表扫描

5. possible_keys：

可能用到的键

6. key：

实际用到的键

7. key_length：

索引键总长度

8. ref：

对前面的tpye的一个详细显示，很有可能是一个常量，比如用到的索引是什么type，用到主键具体是那个数据库哪张表的主键

9. rows：

扫描了多少行

10. extra：

包含不适合在其他列显示但十分重要的额外信息

I. SQL优化

1. Extra提示优化

a) using filesort

使用的文件排序，并非使用索引的排序，解决方案是在order by后面使用覆盖索引

b) using temporary

通常情况下在使用了order by或者group by时会出现，解决方案是在group by后面使用覆盖索引

c) using index

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

d) using where

表明使用了where过滤

e) using join buffer

表明使用了链接缓存

f) impossible where

where字句的值总是false，不能用来获取任何元素，比如where 1=2;

2. 优化10策略

策略一：尽量值匹配

策略二：最佳左前缀法则

策略三：不在索引列上做任何操作

策略四：范围条件放最后

策略五：覆盖索引尽量用

策略六：不等于要慎用

策略七：null和not null有影响

策略八：like查询要当心

策略九：字符类型加引号

策略十：or改union效率高

J. 数据批量操作

导出

select * into OUTFILE 'D：\\product.txt' from product_info

导入

load data INFILE 'D：\\product.txt' into table product_info2
#指定客户端文件
load data local INFILE 'D：\\product.txt' into table product_info

报错：The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

查看show variables like ‘%secure%‘，在my.ini中修改secure-file-priv=""

MySQL优化笔记整理

原文：https://www.cnblogs.com/yhongyin/p/12040493.html