MySql数据库优化

MySql调优准备工作

查看慢 SQL 日志是否启用（on 表示启用）:

show variables like 'slow_query_log';

查看执行慢于多少秒的 SQL 会记录到日志文件中

show variables like 'long_query_time';

修改MySql的配置文件需要重启服务，但是同命令行不用重启服务，但只是临时性的，关掉后再打开还是原来的配置

 

常用参数详解

常用的参数详解：

#--是否开启慢查询日志

slow_query_log=1

# --指定保存路径及文件名，默认为数据文件目录，

slow_query_log_file="bxg_mysql_slow.log"

# --指定多少秒返回查询的结果为慢查询

long_query_time=1

# --记录所有没有使用到索引的查询语句

log_queries_not_using_indexes=1

#--记录那些由于查找了多于 1000 次而引发的慢查询

min_examined_row_limit=1000

# --记录那些慢的 optimize table，analyze table 和 alter table 语句

log_slow_admin_statements=1

#--记录由 Slave 所产生的慢查询

log_slow_slave_statements=1

注意：修改以下参数，需要重新启动数据库服务才会生效。

命令行修改：

set global slow_query_log=1; 1代表off 0代表on

set global slow_query_log_file='bxg_mysql_slow.log';

set long_query_time=1;

set global log_queries_not_using_indexes=1;

set global min_examined_row_limit=1000;

set global log_slow_admin_statements=1;

set global log_slow_slave_statements=1;

EXPLAN执行计划

用法

EXPLAN SELECT ..... 经常使用的方式，查看sql执行计划

EXPLAIN select * from student

 

字段	说明
Id	查询中执行 select 子句或操作表的顺序
Select_type	所使用的 SELECT 查询类型，包括以下常见类型：SIMPLE、 PRIMARY、SUBQUERY、UNION、DERIVED、UNION RESULT、DEPENDENT、
table	所使用的的数据表的名字
type（重要）	表示 MySQL 在表中找到所需行的方式，又称“访问类型”。 取值按优劣排序 为 NULL>system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
Possible_keys	可能使用哪个索引在表中找到记录
key	实际使用的索引
Key_len	索引中使用的字节数
ref	显示索引的哪一列被使用了
rows	估算的找到所需的记录所需要读取的行数
filtered	通过条件过滤出的行数的百分比估计值
extra	包含不适合在其他列中显示但十分重要的额外信息

type中的类型：

ALL代表查询效率太低了，为了查处结果需要去查询全表的语句

index类型：索引类型，全盘扫描索引

range类型：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行

ref类型：一个索引对应多行语句，连接程序无法根据键值只取得一条记录，使用索引的最左前缀或者索引不是 primary key 或 unique 索引的情况。

NULL类型：MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成

extra里面的Using index：改值表示相应的select操作中使用了覆盖索引，MySql可以根据索引返回select列表中的字段。比如说：我去查询一个结果，就查id，我在索引中就取得到了一个信息，通过信息找到了他的位置。

Profiling 的使用

他提供了一些CPU的消耗信息以及IO的操作信息

（1）通过执行“set profiling”命令，可以开启关闭 QueryProfiler 功能 
    mysql> SET global profiling=on; 
在开启 Query Profiler 功能之后，MySQL 就会自动记录所有执行的 Query 的 profile 信息，
（2）获取当前系统中保存的多个 Query 的 profile 的概要信息 
    mysql> show profiles;
（3）针对单个 Query 获取详细的 profile 信息：可以根据概要信息中的 Query_ID 来获取某个 Query 在执行过程中详细的 profile 信息
    查看CPU和IO的详细信息：
    show profile cpu,block io for query 501; // 501是Query_id
    查看所有的信息：
    show profile all for query 501;
    

数据库连接进程列表

show processlist;

查询一些信息，比如死锁！

Mysql存储引擎

定义

数据库引擎是用于存储、处理和保护数据的核心服务 。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。建用于联机事务处理或联机分析处理数据的关系数据库 。

作用

设计并创建数据库以保存系统所需的关系或 XML 文档。

实现系统以访问和更改数据库中存储的数据。包括实现网站或使用数据的应用程 序，还包括生成使用 SQL Server 工具和实用工具以使用数据的过程

为单位或客户部署实现的系统。

提供日常管理支持以优化数据库的性能。

种类

主要有 MyISAM InnoDB Memory Blackhole CSV等九种

Engine ：InnoDB 和 MyISAM

两者的区别是：InnoDB支持事务与外键和行级锁。而MyISAM不支持。需求趋向于稳定性，MyISAM是首选。

MyISAM

MyISAM支持三种不同的存储格式：静态表，动态表和已压缩表

静态表和动态表是根据正是用的列的类型来自动选择的。而已压缩表只能使用myisampack工具来创建，将整张表都压缩，即每行都会压缩一些数据

静态格式是MyISAM默认的存储格式。当表中不包含变量长度(VARCHAR,BLOB,或TEXT)时，使用这个格式。表崩溃后容易重建，因为记录位于固定的位置

动态格式表和静态格式表正好相反，原本数据存储多大空间就使用多大空间，不容易查找，但是省空间。表在崩溃后要比静态格式表更难重建，因为一个记录可能被分为多个碎片且链接（碎片）可能被丢失。

压缩的表更加适合快速获得里面的信息，唯一缺点是压缩包是只读的，如果再想去修改是修改不了的。

InnoDB

InnoDB 是一个事务型的存储引擎，有行级锁定和外键约束 。

适用于经常更新的表，支持事务，可以恢复数据，有外键约束，支持自动增长列auto_increment

行格式分为REDUNDANT和COMPACT行格式

功能

InnoDB和MyISAM是许多人在使用MySql时常用的两个表类型。这两个表类型各有优势。视具体应用而定。

基本的差别：MyISAM类型不支持事务处理的高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务以及外部键等高级数据库功能。

InnoDB对比MyISAM

在事务上， MyISAM不支持事务处理等高级处理，而InnoDB类型支持，提供事务支持以及外部键等高级数据库功能。InnoDB表的行锁不是绝对的，根据实际情况也可以锁全表。比如不确定的范围

在性能上，以前版本中 MyISAM 类型的表强调的是性能，其执行数度比 InnoDB 类型更快。但现在 InnoDB 在多方面的性能已经赶上活超过了 MyIsam

行数保存：MySql在存储数据时，会把行数存储下来，则在查询行数时，只需要将保存好的行数读出来就好，但是InnoDB需要去扫描表。还有一种可能，当count(*)后有where条件时，两种表的操作是一样的

锁的支持：MyISAM只支持表锁，InnoDB支持表锁，行锁。行锁大幅度提高多用户并发操作。然而，InnoDB的行锁，只是在where的逐渐是有效的，非主键的where还是会锁全表

黑洞引擎 Blackhole

任何写入到此引擎的数据均会被丢弃掉， 不做实际存储

CREATE TABLE `Blackhole` ( 
    `id` bigint(20) unsigned NOT NULL,
    `fname` varchar(100) NOT NULL, 
    `lname` varchar(100) NOT NULL, 
    `age` tinyint(3) unsigned NOT NULL,
    `sex` tinyint(1) unsigned NOT NULL, 
    PRIMARY KEY (`id`)
) ENGINE=Blackhole DEFAULT CHARSET=utf8

验证语法的正确与否，然后在正式库上执行！

SQL语句优化

避免select *

从数据库里读出越多的数据，那么查询就会变得越慢。并且如果你的数据库服务器和WEB 服务器是两台独立的服务器的话，这还会增加网络传输的负载。

避免在where子句中使用不等于号操作符 != <>

如果使用!=或者<>操作符，引擎会放弃索引而进行全表扫描

EXPLAIN select * from student where s_id <> 3

尽量避免全表扫描

在查表时，首先应该考虑where和order by涉及的列上建立索引

用 UNION 来代替 OR

两者的执行效果一样，但是UNION的速度更快

select * from student where s_id = '03' OR s_id = '04'；
​
select * from student where s_id = '03'
UNION
select * from student where s_id = '04'

like 语句避免前置百分号

前置百分号会导致索引失效

避免 where 子句中使用参数

在where子句中使用参数，会造成全表扫描。sql在运行中会解析局部变量，这属于在运行阶段。而我们要做的是是在编译阶段进行优化，此时的变量的值是未知的，是不是索引不确定

select id from t where num=@num
可以改为强制查询使用索引： 
select id from t with(index(索引名)) where num=@num

避免在 where 子句中对字段进行表达式操作

应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

select id from t where num/2=100
应改为: 
select id from t where num=100*2

避免在 where 子句中对字段进行函数操作

应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

select id from t where substring(name,1,3)=’abc’ –name 以 abc 开头的 id
应改为: 
select id from t where name like ‘abc%’

避免无意义查询

不要写一些没有意义的查询，这类代码不会返回任何结果集，但是会消耗系统资源的

用 exists 代替 in

select num from a where num in(select num from b) 
用下面的语句替换： 
select num from a where exists(select 1 from b where num=a.num)

尽量使用数字型字段

尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销，比如IP可以设为整型的

使用 varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小， 可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些

大临时表使用 select into 代替 create table

新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先 create table，然后 insert

临时表先 truncate table，然后 drop table

避免系统表的较长时间锁定

存储过程使用 SET NOCOUNT ON

避免向客户端返回大数据量

尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理

避免在 where 子句中对字段进行 null 值判断

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

select id from t where num is null
可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询
select id from t where num=0

总结sql优化方法：

1.避免select *

2.避免在 where 子句中使用!=或<>操作符

3.尽量避免全表扫描

4.用 UNION 来代替 OR

5.like 语句避免前置百分号

6.避免 where 子句中使用参数

7.避免在 where 子句中对字段进行表达式操作

8.避免在 where 子句中对字段进行函数操作

9.避免无意义查询

10.用 exists 代替 in

11.尽量使用数字型字段

12.使用 varchar/nvarchar 代替 char/nchar

13.大临时表使用 select into 代替 create table l>

14.临时表先 truncate table，然后 drop table

15.存储过程使用 SET NOCOUNT ON

16.避免向客户端返回大数据量

17.避免在 where 子句中对字段进行 null 值判断

大数据量的分页优化

从业务逻辑优化：加入数据量很大，不允许翻到100页，只翻到前几页即可

技术优化方法一：

select * from student where id > 1000000 limit 100

技术优化方法二：用连接的方式先查出id，然后根据id关联，ID做索引，只查id实现索引覆盖

select p.* from student p inner join (select id from student limit 1000000 ,100) as tmp on p.id=tmp.id;

索引

什么是索引

索引用于快速找出在某个列中有一特定值的行，不使用索引 MySQL 必须从第一条记录开始读完整个表，直到找出相关的行，表越大查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL 能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间

是存储引擎用于快速找到记录的一种数据结构。对查询的速度有着至关重要的影响，理解索引时进行数据库性能能优化的起点。她能够轻易地将查询性能提高几个数量级。索引可以包含一个或者多个列的值。如果包含多个列，那么列的顺序也十分重要，因为MySql之恩那个高效的使用索引的最左前缀列。

Create table user5(
    id int,
    name varchar(10),
    INDEX MYNAME (NAME(5))
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

上面就建立了索引，取得是name字段的前5位做索引。索引名为myname

查询索引：show index from 表名

选择索引的数据类型

选择数据类型的原则：

越小的数据类型通常越好；越小的数据类型在磁盘，内存和CPU的缓存中都需要更少的空间，处理起来更快。

简单的数据类型。整型数据比起字符，处理开销更小。因为字符串比较复杂。在mysql中，应该用内置的日期和时间数据类型，而不用字符串存储时间，用整型来存储IP地址。

尽量避免null。指定列应为not null 在MySQL中，用0或者一个特殊的值或一个空串代替null

选择标识符：

一旦选择数据类型，因保证所有相关的表都使用相同的数据类型

整型：通常是作为标识符的最好选择，因为可以更快的处理，而且可以设置为AUTO_INCREMENT。

字符串：尽量避免使用字符串作为标识符，它们消耗更大的空间，处理起来也较慢。而且，通常来说，字符串都是随机的，所以它们在索引中的位置也是随机。会导致页面分裂，随机访问磁盘，聚簇索引分裂。

索引的类型：

B-Tree类型 Hash索引 空间(R-Tree)索引 全文(Full-text)索引

B-Tree类型

B-Tree 索引，它是目前关系型数据库中查找数据最为常用和有效的索引，大多数存储引擎都支持这种索引。实际上不同的存储引擎可能使用不同的数据结构 。InnoDB 就是使用的 B+Tree

 

索引去维护数据时，有一定的排序规则。增删改数据都会改变索引位置！

索引的优点和缺点

以常见的B-Tree索引来说，按照顺序存储数据，所以MYSql可以用来做order by和group by操作。因为数据是具有顺序的，所以B-Tree也就会将相关的列值存储在一块。最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。

总结下来有以下三个优点：

1.大大减小了服务器需要扫描的数据量

2.索引可以帮助服务器避免排序和临时表

3.索引可以将随机IO变为顺序IO

索引的缺点：

1、创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

2.索引也需要占空间，我们知道数据表中的数据也会有最大上限设置，如果我们有大量索引，索引文件可能会比数据更快到达上限值

3.当对表中的数据进行增删改时，索引是动态维护的，降低了数据的维护速度

使用原则：

1.并不是所有索引对查询都有效 。当索引中有大量重复数据时，sql查询可能就不会去用索引

2.索引并不是越多越好 ，一个表的索引最多不要超过6个。索引会增加select的效率，但是同样降低insert和update的效率

3.避免更新聚簇索引数据列 。在mysql中，默认的clustered索引为主键，记录的是物理存储顺序。一旦它发生改变，整个表记录的顺序也会调整，会耗费较大的资源。

4.经常更新的表就避免对其进行过多的索引 。对经常查询的表应建立索引

5.数据量小的表最好不要使用索引 。数据量小的表再建索引，可能遍历索引的时间比查表数据的时间还要长，那就没有必要了

6.避免在不同值少的列上加索引。比如，性别表上只有男女就不要建索引了，在一个字段上，不同的值比较多建议使用索引。

7.根据业务需求建立索引。经常作为查询条件的列才有建立索引的必要性

索引的分类

索引分为：普通索引 唯一索引 主键索引 组合索引 全文索引 空间索引

普通索引

MySQL 中基本索引类型，没有什么限制 ，允许在定义索引的列中插入重复值和空值 index() 或者 key()

唯一索引

索引列中的值必须是唯一的，但是允许为空值 unique index Uniqid()

主键索引

是一种特殊的唯一索引，不允许有空值 PRIMARY KEY(id)

组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合

INDEX MultiIdx(id,name,age) 当查询条件是（id,name,age） 或者（id,name）或者（id）才会用索引。遵循最左前缀。age 和 (name，age)就不会

全文索引

只有在MyISAM引擎上才能使用，只能在CHAR VARCHAR TEXT类型的字段上使用全文索引。

空间索引

只有在 MyISAM 引擎上才能使用，空间索引是对空间数据类型的字段建立的索引。

MySql中空间数据类型有4种：GEOMETRY、POINT、LINESTRING、POLYGON。 在创建空间索引时，必须用SPATIAL关键字，创建空间索引的列，必须是not null的。 SPATIAL INDEX spatialx(xxx)

索引优化口诀

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

Like 百分写最右，覆盖索引不写星；

不等空值还有 or，索引失效要少用；

VAR 引号不可丢，SQL 高级也不难！

主从复制

目的

数据同步备份，当我的主机Master发生故障，可以马上切换到从库Salve，降低服务风险。

读写分离。把写的操作放在master库，读的操作放在Salve，减轻单一数据库操作压力。

高可用HA。业务量增大，单机部署数据库，就会导致 I/O 访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上 ，降低单机磁盘 I/O 访问的频率，提高单个机器的 I/O 性能

基本原理

master 记录下自己的操作日志，授权从服务器可以读取操作日志，slave 会开启两个 线程IO线程和sql进程。

io线程负责连接master成功后，睡眠等待master产生新的事件，有了新的就保存到自己的中继日志，中继日志位于操作系统的缓存中，开销很小。

sql进程负责执行中继日志中的sql操作，这样slave的内容就和master一样了。

1.主库 db 的更新事件(update、insert、delete)被写到 binlog

2.从库发起连接，连接到主库

3.主库创建一个 binlog dump thread 线程，把 binlog 的内容发送到从库

4.从库启动之后，创建一个 I/O 线程，读取主库传过来的 binlog 内容并写入到 relaylog。

5.还会创建一个 SQL 线程，从 relay log 里面读取内容，从 Exec_Master_Log_Pos 位置开始执行读取到的更新事件，将更新内容写入到 slave 的 db。

 

备注：学习于博学谷视频后的笔记整理，还未学习完成！