携手创造，一同生长！这是我参与「日新计划 8 月更文挑战」的第18天，点击检查活动概况

本文常识点较多，篇幅较长，请耐性学习

MySQL已经成为时下联系型数据库产品的中坚力量，备受互联网大厂的青睐，出门面试想进BAT，想拿高工资，不会点MySQL优化常识，拿offer的成功率会大大下降。

为什么要优化

• 体系的吞吐量瓶颈往往呈现在数据库的拜访速度上
• 跟着运用程序的运行，数据库的中的数据会越来越多，处理时刻会相应变慢
• 数据是寄存在磁盘上的，读写速度无法和内存比较

怎么优化

• 规划数据库时：数据库表、字段的规划，存储引擎
• 运用好MySQL自身供给的功用，如索引等
• 横向扩展：MySQL集群、负载均衡、读写别离
• SQL句子的优化（收效甚微）

字段规划

字段类型的挑选，规划标准，范式，常见规划案例

准则：尽量运用整型表明字符串

存储IP

INET_ATON(str)，address to number

INET_NTOA(number)，number to address

MySQL内部的枚举类型（单选）和调集（多选）类型

可是由于保护本钱较高因而不常运用，运用相关表的办法来代替enum

准则：定长和非定长数据类型的挑选

decimal不会损失精度，存储空间会随数据的增大而增大。double占用固定空间，较大数的存储会损失精度。非定长的还有varchar、text

金额

对数据的精度要求较高，小数的运算和存储存在精度问题（不能将一切小数转换成二进制）

定点数decimal

price decimal(8,2)有2位小数的定点数，定点数支撑很大的数（甚至是超过int,bigint存储规模的数）

小单位大数额防止呈现小数

元->分

字符串存储

定长char，非定长varchar、text（上限65535，其间varchar还会耗费1-3字节记载长度，而text运用额定空间记载长度）

准则：尽或许挑选小的数据类型和指定短的长度

准则：尽或许运用 not null

非null字段的处理要比null字段的处理高效些！且不需求判断是否为null。

null在MySQL中，欠好处理，存储需求额定空间，运算也需求特别的运算符。如select null = null和select null <> null（<>为不等号）有着同样的成果，只能经过is null和is not null来判断字段是否为null。

怎么存储？MySQL中每条记载都需求额定的存储空间，表明每个字段是否为null。因而一般运用特别的数据进行占位，比方int not null default 0、string not null default ‘’

准则：字段注释要完好，见名知意

准则：单表字段不宜过多

二三十个就极限了

准则：能够预留字段

在运用以上准则之前首先要满意业务需求

相关表的规划

外键foreign key只能完成1对1或一对多的映射

一对多

运用外键

多对多

独自新建一张表将多对多拆分成两个一对多

1对1

如产品的基本信息（item）和产品的具体信息（item_intro），一般运用相同的主键或许添加一个外键字段（item_id）

范式 Normal Format

数据表的规划标准，一套越来越严厉的标准体系（假如需求满意N范式，首先要满意N-1范式）。N

榜首范式1NF：字段原子性

字段原子性，字段不行再切割。

联系型数据库，默许满意榜首范式

留意比较简略出错的一点，在一对多的规划中运用逗号分隔多个外键，这种办法尽管存储便利，但不利于保护和索引（比方查找带标签java的文章）

第二范式：消除对主键的部分依托

即在表中加上一个与业务逻辑无关的字段作为主键

主键：能够仅有标识记载的字段或许字段调集。

course_name	course_class	weekday（周几）	course_teacher
MySQL	教育大楼1525	周一	张三
Java	教育大楼1521	周三	李四
MySQL	教育大楼1521	周五	张三

依托：A字段能够确认B字段，则B字段依托A字段。比方知道了下一节课是数学课，就能确认任课老师是谁。于是周几和下一节课和就能构成复合主键，能够确认去哪个教室上课，任课老师是谁等。但咱们常常添加一个id作为主键，而消除对主键的部分依托。

对主键的部分依托：某个字段依托复合主键中的一部分。

处理计划：新增一个独立字段作为主键。

第三范式：消除对主键的传递依托

传递依托：B字段依托于A，C字段又依托于B。比方上例中，任课老师是谁取决于是什么课，是什么课又取决于主键id。因而需求将此表拆分为两张表日程表和课程表（独立数据独立建表）：

日程表

id	weekday	course_class	course_id
1001	周一	教育大楼1521	3546

课程表

course_id	course_name	course_teacher
3546	Java	张三

这样就减少了数据的冗余（即便周一至周日每天都有Java课，也仅仅course_id:3546呈现了7次）

存储引擎挑选

前期问题：怎么挑选MyISAM和Innodb？

现在不存在这个问题了，Innodb不断完善，从各个方面赶超MyISAM，也是MySQL默许运用的。

存储引擎Storage engine：MySQL中的数据、索引以及其他目标是怎么存储的，是一套文件体系的完成。

功用差异

show engines

Engine	Support	Comment
InnoDB	DEFAULT	Supports transactions, row-level locking, and foreign keys
MyISAM	YES	MyISAM storage engine

存储差异

MyISAM	Innodb
文件格局	数据和索引是别离存储的，数据.MYD，索引.MYI	数据和索引是集中存储的，.ibd
文件能否移动	能，一张表就对应.frm、MYD、MYI3个文件	否，由于相关的还有data下的其它文件
记载存储次序	按记载刺进次序保存	按主键巨细有序刺进
空间碎片（删去记载并flush table 表名之后，表文件巨细不变）	发生。守时整理：运用指令optimize table 表名完成	不发生
业务	不支撑	支撑
外键	不支撑	支撑
锁支撑（锁是防止资源争用的一个机制，MySQL锁对用户简直是通明的）	表级确认	行级确认、表级确认，确认力度小并发才能高

锁扩展

表级锁（table-level lock）：lock tables <table_name1>,<table_name2>... read/write，unlock tables <table_name1>,<table_name2>...。其间read是同享锁，一旦确认任何客户端都不行读；write是独占/写锁，只要加锁的客户端可读可写，其他客户端既不行读也不行写。确认的是一张表或几张表。

行级锁（row-level lock）：确认的是一行或几行记载。同享锁：select * from <table_name> where <条件> LOCK IN SHARE MODE;，对查询的记载添加同享锁；select * from <table_name> where <条件> FOR UPDATE;，对查询的记载添加排他锁。这儿值得留意的是：innodb的行锁，其实是一个子规模锁，根据条件确认部分规模，而不是就映射到具体的行上，因而还有一个学名：间隙锁。比方select * from stu where id < 20 LOCK IN SHARE MODE会确认id在20左右以下的规模，你或许无法刺进id为18或22的一条新纪录。

挑选根据

假如没有特别的需求，运用默许的Innodb即可。

MyISAM：以读写刺进为主的运用程序，比方博客体系、新闻门户网站。

Innodb：更新（删去）操作频率也高，或许要确保数据的完好性；并发量高，支撑业务和外键确保数据完好性。比方OA主动化工作体系。

索引

关键字与数据的映射联系称为索引（==包括关键字和对应的记载在磁盘中的地址==）。关键字是从数据傍边提取的用于标识、检索数据的特定内容。

索引检索为什么快？

• 关键字相关于数据自身，==数据量小==
• 关键字是==有序==的，二分查找可快速确认位置

图书馆为每本书都加了索引号（类别-楼层-书架）、字典为词语解释按字母次序编写目录等都用到了索引。

MySQL中索引类型

一般索引（key），仅有索引（unique key），主键索引（primary key），全文索引（fulltext key）

三种索引的索引办法是相同的，只不过对索引的关键字有不同的束缚：

• 一般索引：对关键字没有束缚
• 仅有索引：要求记载供给的关键字不能重复
• 主键索引：要求关键字仅有且不为null

索引办理语法

检查索引

show create table 表名：

desc 表名

创立索引

创立表之后树立索引

create TABLE user_index(
    id int auto_increment primary key,
    first_name varchar(16),
    last_name VARCHAR(16),
    id_card VARCHAR(18),
    information text
);
​
-- 更改表结构
alter table user_index
-- 创立一个first_name和last_name的复合索引，并命名为name
add key name (first_name,last_name),
-- 创立一个id_card的仅有索引，默许以字段名作为索引名
add UNIQUE KEY (id_card),
-- 鸡肋，全文索引不支撑中文
add FULLTEXT KEY (information);

show create table user_index：

创立表时指定索引

CREATE TABLE user_index2 (
    id INT auto_increment PRIMARY KEY,
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
);

删去索引

根据索引名删去一般索引、仅有索引、全文索引：alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

删去主键索引：alter table 表名 drop primary key（由于主键只要一个）。这儿值得留意的是，假如主键自添加，那么不能直接履行此操作（自添加依托于主键索引）：

需求撤销自添加再行删去：

alter table user_index
-- 从头界说字段
MODIFY id int,
drop PRIMARY KEY

但一般不会删去主键，由于规划主键必定与业务逻辑无关。

履行计划explain

CREATE TABLE innodb1 (
    id INT auto_increment PRIMARY KEY,
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
);
insert into innodb1 (first_name,last_name,id_card,information) values ('张','三','1001','华山派');

咱们能够经过explain selelct来剖析SQL句子履行前的履行计划：

由上图可看出此SQL句子是依照主键索引来检索的。

履行计划是：当履行SQL句子时，首先会剖析、优化，构成履行计划，在依照履行计划履行。

索引运用场景（要点）

where

上图中，根据id查询记载，由于id字段仅树立了主键索引，因而此SQL履行可选的索引只要主键索引，假如有多个，终究会选一个较优的作为检索的根据。

-- 添加一个没有树立索引的字段
alter table innodb1 add sex char(1);
-- 按sex检索时可选的索引为null
EXPLAIN SELECT * from innodb1 where sex='男';

能够测验在一个字段未树立索引时，根据该字段查询的功率，然后对该字段树立索引（alter table 表名 add index(字段名)），同样的SQL履行的功率，你会发现查询功率会有显着的进步（数据量越大越显着）。

order by

当咱们运用order by将查询成果依照某个字段排序时，假如该字段没有树立索引，那么履行计划会将查询出的一切数据运用外部排序（将数据从硬盘分批读取到内存运用内部排序，最终兼并排序成果），这个操作是很影响功用的，由于需求将查询触及到的一切数据从磁盘中读到内存（假如单条数据过大或许数据量过多都会下降功率），更无论读到内存之后的排序了。

可是假如咱们对该字段树立索引alter table 表名 add index(字段名)，那么由于索引自身是有序的，因而直接依照索引的次序和映射联系逐条取出数据即可。而且假如分页的，那么只用取出索引表某个规模内的索引对应的数据，而不必像上述那取出一切数据进行排序再回来某个规模内的数据。（从磁盘取数据是最影响功用的）

join

对join句子匹配联系（on）触及的字段树立索引能够进步功率

索引掩盖

假如要查询的字段都树立过索引，那么引擎会直接在索引表中查询而不会拜访原始数据（不然只要有一个字段没有树立索引就会做全表扫描），这叫索引掩盖。因而咱们需求尽或许的在select后==只写必要的查询字段==，以添加索引掩盖的几率。

这儿值得留意的是不要想着为每个字段树立索引，由于优先运用索引的优势就在于其体积小。

语法细节（要点）

在满意索引运用的场景下（where/order by/join on或索引掩盖），索引也不必定被运用

字段要独立呈现

比方下面两条SQL句子在语义上相同，可是榜首条会运用主键索引而第二条不会。

select * from user where id = 20-1;
select * from user where id+1 = 20;
​

like查询，不能以通配符最初

比方查找标题包括mysql的文章：

select * from article where title like '%mysql%';

这种SQL的履行计划用不了索引（like句子匹配表达式以通配符最初），因而只能做全表扫描，功率极低，在实践工程中简直不被选用。而一般会运用第三方供给的支撑中文的全文索引来做。

可是 关键字查询 热搜提醒功用还是能够做的，比方键入mysql之后提醒mysql 教程、mysql 下载、mysql 装置进程等。用到的句子是：

select * from article where title like 'mysql%';

这种like是能够运用索引的（当然前提是title字段树立过索引）。

复合索引只对榜首个字段有用

树立复合索引：

alter table person add index(first_name,last_name);

其原理便是将索引先依照从first_name中提取的关键字排序，假如无法确认先后再依照从last_name提取的关键字排序，也便是说该索引表仅仅依照记载的first_name字段值有序。

因而select * from person where first_name = ?是能够运用索引的，而select * from person where last_name = ?无法运用索引。

那么该复合索引的运用场景是什么？==组合查询==

比方关于select * person from first_name = ? and last_name = ?，复合索引就比对first_name和last_name独自树立索引要高效些。很好了解，复合索引首先二分查找与first_name = ?匹配的记载，再在这些记载中二分查找与last_name匹配的记载，只触及到一张索引表。而别离独自树立索引则是在first_name索引表中二分找出与first_name = ?匹配的记载，再在last_name索引表中二分找出与last_name = ?的记载，两者取交集。

or，两头条件都有索引可用

一但有一边无索引可用就会导致整个SQL句子的全表扫描

状况值，不简略运用到索引

如性别、支付状况等状况值字段往往只要极少的几种取值或许，这种字段即便树立索引，也往往运用不上。这是由于，一个状况值或许匹配很多的记载，这种状况MySQL会认为运用索引比全表扫描的功率低，从而弃用索引。索引是随机拜访磁盘，而全表扫描是次序拜访磁盘，这就比方有一栋20层楼的写字楼，楼底下的索引牌上写着某个公司对应不相邻的几层楼，你去公司找人，与其依照索引牌的提示去其间一层楼没找到再下来看索引牌再上楼，不如从1楼挨个往上找到顶楼。

怎么创立索引

• 树立根底索引：在where、order by、join字段上树立索引。

• 优化，组合索引：根据业务逻辑

  • 假如条件常常性呈现在一同，那么能够考虑将多字段索引晋级为==复合索引==
  • 假如经过添加个别字段的索引，就能够呈现==索引掩盖==，那么能够考虑为该字段树立索引
  • 查询时，不常用到的索引，应该删去掉

前缀索引

语法：index(field(10))，运用字段值的前10个字符树立索引，默许是运用字段的全部内容树立索引。

前提：前缀的标识度高。比方暗码就适合树立前缀索引，由于暗码简直各不相同。

==实操的难度==：在于前缀截取的长度。

咱们能够运用select count(*)/count(distinct left(password,prefixLen));，经过从调整prefixLen的值（从1自增）检查不同前缀长度的一个平均匹配度，挨近1时就能够了（表明一个暗码的前prefixLen个字符简直能确认仅有一条记载）

索引的存储结构

BTree

btree（多路平衡查找树）是一种广泛运用于==磁盘上完成索引功用==的一种数据结构，也是大多数数据库索引表的完成。

以add index(first_name,last_name)为例：

BTree的一个node能够存储多个关键字，node的巨细取决于计算机的文件体系，因而咱们能够经过减小索引字段的长度使结点存储更多的关键字。假如node中的关键字已满，那么能够经过每个关键字之间的子节点指针来拓宽索引表，可是不能破坏结构的有序性，比方依照first_name榜首有序、last_name第二有序的规矩，新添加的韩香就能够插到韩康之后。白起 < 韩飞 < 韩康 < 李世民 < 赵奢 < 李寻欢 < 王语嫣 < 杨不悔。这与二叉查找树的思想是相同的，只不过二叉查找树的查找功率是log(2,N)（以2为底N的对数），而BTree的查找功率是log(x,N)（其间x为node的关键字数量，能够达到1000以上）。

从log(1000+,N)能够看出，少量的磁盘读取即可做到很多数据的遍历，这也是btree的规划意图。

B+Tree聚簇结构

聚簇结构（也是在BTree上晋级改造的）中，关键字和记载是寄存在一同的。

在MySQL中，仅仅只要Innodb的==主键索引为聚簇结构==，其它的索引包括Innodb的非主键索引都是典型的BTree结构。

哈希索引

在索引被载入内存时，运用哈希结构来存储。

查询缓存

缓存select句子的查询成果

在装备文件中敞开缓存

windows上是my.ini，linux上是my.cnf

在[mysqld]段中装备query_cache_type：

• 0：不敞开
• 1：敞开，默许缓存一切，需求在SQL句子中添加select sql-no-cache提示来放弃缓存
• 2：敞开，默许都不缓存，需求在SQL句子中添加select sql-cache来主动缓存（==常用==）

更改装备后需求重启以使装备收效，重启后可经过show variables like ‘query_cache_type’;来检查：

show variables like 'query_cache_type';
query_cache_type    DEMAND
​

在客户端设置缓存巨细

经过装备项query_cache_size来设置：

show variables like 'query_cache_size';
query_cache_size    0
​
set global query_cache_size=64*1024*1024;
show variables like 'query_cache_size';
query_cache_size    67108864
​

将查询成果缓存

select sql_cache * from user;

重置缓存

reset query cache;

缓存失效问题（大问题）

当数据表改动时，根据该数据表的任何缓存都会被删去。（表层面的办理，不是记载层面的办理，因而失功率较高）

留意事项

0. 运用程序，不应该关心query cache的运用状况。能够测验运用，但不能由query cache决议业务逻辑，由于query cache由DBA来办理。
1. 缓存是以SQL句子为key存储的，因而即便SQL句子功用相同，但假如多了一个空格或许巨细写有差异都会导致匹配不到缓存。

分区

一般状况下咱们创立的表对应一组存储文件，运用MyISAM存储引擎时是一个.MYI和.MYD文件，运用Innodb存储引擎时是一个.ibd和.frm（表结构）文件。

当数据量较大时（一般千万条记载级别以上），MySQL的功用就会开始下降，这时咱们就需求将数据涣散到多组存储文件，==确保其单个文件的履行功率==。

最常见的分区计划是按id分区，如下将id的哈希值对10取模将数据均匀涣散到10个.ibd存储文件中：

create table article(
    id int auto_increment PRIMARY KEY,
    title varchar(64),
    content text
)PARTITION by HASH(id) PARTITIONS 10
​

检查data目录：

==服务端的表分区关于客户端是通明的==，客户端还是照常刺进数据，但服务端会依照分区算法涣散存储数据。

MySQL供给的分区算法

==分区根据的字段有必要是主键的一部分==，分区是为了快速定位数据，因而该字段的查找频次较高应作为强检索字段，不然依照该字段分区毫无意义

hash(field)

相同的输入得到相同的输出。输出的成果跟输入是否具有规律无关。==仅适用于整型字段==

key(field)

和hash(field)的性质相同，只不过key是==处理字符串==的，比hash()多了一步从字符串上钩算出一个整型在做取模操作。

create table article_key(
    id int auto_increment,
    title varchar(64),
    content text,
    PRIMARY KEY (id,title)  -- 要求分区根据字段有必要是主键的一部分
)PARTITION by KEY(title) PARTITIONS 10
​

range算法

是一种==条件分区==算法，依照数据巨细规模分区（将数据运用某种条件，涣散到不同的分区中）。

如下，按文章的发布时刻将数据依照2018年8月、9月、10月分区寄存：

create table article_range(
    id int auto_increment,
    title varchar(64),
    content text,
    created_time int,   -- 发布时刻到1970-1-1的毫秒数
    PRIMARY KEY (id,created_time)   -- 要求分区根据字段有必要是主键的一部分
)charset=utf8
PARTITION BY RANGE(created_time)(
    PARTITION p201808 VALUES less than (1535731199),    -- select UNIX_TIMESTAMP('2018-8-31 23:59:59')
    PARTITION p201809 VALUES less than (1538323199),    -- 2018-9-30 23:59:59
    PARTITION p201810 VALUES less than (1541001599) -- 2018-10-31 23:59:59
);
​

留意：条件运算符只能运用==less than==，这以为着较小的规模要放在前面，比方上述p201808,p201819,p201810分区的界说次序依照created_time数值规模从小到大，不能颠倒。

insert into article_range values(null,'MySQL优化','内容示例',1535731180);
flush tables;   -- 使操作立即改写到磁盘文件
​

由于刺进的文章的发布时刻1535731180小于1535731199（2018-8-31 23:59:59），因而被存储到p201808分区中，这种算法的存储到哪个分区取决于数据状况。

list算法

也是一种条件分区，依照列表值分区（in (值列表)）。

create table article_list(
    id int auto_increment,
    title varchar(64),
    content text,
    status TINYINT(1),  -- 文章状况：0-草稿，1-完成但未发布，2-已发布
    PRIMARY KEY (id,status) -- 要求分区根据字段有必要是主键的一部分
)charset=utf8
PARTITION BY list(status)(
    PARTITION writing values in(0,1),   -- 未发布的放在一个分区   
    PARTITION published values in (2)   -- 已发布的放在一个分区
);
​
insert into article_list values(null,'mysql优化','内容示例',0);
flush tables;

分区办理语法

range/list

添加分区

前文中咱们测验运用range对文章依照月份归档，跟着时刻的添加，咱们需求添加一个月份：

alter table article_range add partition(
    partition p201811 values less than (1543593599) -- select UNIX_TIMESTAMP('2018-11-30 23:59:59')
    -- more
);
​

删去分区

alter table article_range drop PARTITION p201808
​

留意：==删去分区后，分区中原有的数据也会随之删去！==

key/hash

新增分区

alter table article_key add partition partitions 4

销毁分区

alter table article_key coalesce partition 6

key/hash分区的办理不会删去数据，可是每一次调整（新增或销毁分区）都会将一切的数据重写分配到新的分区上。==功率极低==，最好在规划阶段就考虑好分区战略。

分区的运用

当数据表中的数据量很大时，分区带来的功率进步才会显现出来。

只要检索字段为分区字段时，分区带来的功率进步才会比较显着。因而，==分区字段的挑选很重要==，而且==业务逻辑要尽或许地根据分区字段做相应调整==（尽量运用分区字段作为查询条件）。

水平切割和垂直切割

水平切割：经过树立结构相同的几张表别离存储数据

垂直切割：将常常一同运用的字段放在一个独自的表中，切割后的表记载之间是一一对应联系。

分表原因

• 为数据库减压
• 分区算法局限
• 数据库支撑不完善（5.1之后mysql才支撑分区操作）

id重复的处理计划

• 借用第三方运用如memcache、redis的id自增器
• 独自建一张只包括id一个字段的表，每次自增该字段作为数据记载的id

集群

横向扩展：从根本上（单机的硬件处理才能有限）进步数据库功用 。由此而生的相关技能：==读写别离、负载均衡==

装置和装备主从仿制

环境

• Red Hat Enterprise Linux Server release 7.0 (Maipo)（虚拟机）
• mysql5.7（下载地址）

装置和装备

解压到对外供给的服务的目录（我自己专门创立了一个/export/server来寄存）

tar xzvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /export/server
cd /export/server
mv mysql-5.7.23-linux-glibc2.12-x86_64 mysql

添加mysql目录的所属组和所属者：

groupadd mysql
useradd -r -g mysql mysql
cd /export/server
chown -R mysql:mysql mysql/
chmod -R 755 mysql/

创立mysql数据寄存目录（其间/export/data是我创立专门用来为各种服务寄存数据的目录）

mkdir /export/data/mysql

初始化mysql服务

cd /export/server/mysql
./bin/mysqld --basedir=/export/server/mysql --datadir=/export/data/mysql --user=mysql --pid-file=/export/data/mysql/mysql.pid --initialize

假如成功会显现mysql的root账户的初始暗码，记下来以备后续登录。假如报错缺少依托，则运用yum instally顺次装置即可

装备my.cnf

vim /etc/my.cnf
​
[mysqld]
basedir=/export/server/mysql
datadir=/export/data/mysql
socket=/tmp/mysql.sock
user=mysql
server-id=10 # 服务id，在集群时有必要仅有，主张设置为IP的第四段
port=3306
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
​
[mysqld_safe]
log-error=/export/data/mysql/error.log
pid-file=/export/data/mysql/mysql.pid
​
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
​

将服务添加到开机主动启动

cp /export/server/mysql/support-files/mysql.server /etc/init.d/mysqld

启动服务

service mysqld start

装备环境变量，在/etc/profile中添加如下内容

# mysql env
MYSQL_HOME=/export/server/mysql
MYSQL_PATH=$MYSQL_HOME/bin
PATH=$PATH:$MYSQL_PATH
export PATH

使装备即可收效

source /etc/profile

运用root登录

mysql -uroot -p
# 这儿填写之前初始化服务时供给的暗码

登录上去之后，更改root账户暗码（我为了便利将暗码改为root），不然操作数据库会报错

set password=password('root');
flush privileges;

设置服务可被一切长途客户端拜访

use mysql;
update user set host='%' where user='root';
flush privileges;

这样就能够在宿主机运用navicat长途衔接虚拟机linux上的mysql了

装备主从节点

装备master

以linux（192.168.10.10）上的mysql为master，宿主机（192.168.10.1）上的mysql为slave装备主从仿制。

修正master的my.cnf如下

[mysqld]
basedir=/export/server/mysql
datadir=/export/data/mysql
socket=/tmp/mysql.sock
user=mysql
server-id=10
port=3306
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
​
log-bin=mysql-bin   # 敞开二进制日志
expire-logs-days=7 # 设置日志过期时刻，防止占满磁盘
binlog-ignore-db=mysql  # 不运用主从仿制的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
binlog-do-db=test   #运用主从仿制的数据库
​
[mysqld_safe]
log-error=/export/data/mysql/error.log
pid-file=/export/data/mysql/mysql.pid
​
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

重启master

service mysqld restart

登录master检查装备是否收效（ON即为敞开，默许为OFF）：

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin    | ON   |
+---------------+-------+

在master的数据库中树立备份账号：backup为用户名，%表明任何长途地址，用户back能够运用暗码1234经过任何长途客户端衔接master

grant replication slave on *.* to 'backup'@'%' identified by '1234'

检查user表能够看到咱们刚创立的用户：

mysql> use mysql
mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user      | authentication_string           | host    |
+---------------+-------------------------------------------+-----------+
| root      | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | %     |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys   | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| backup     | *A4B6157319038724E3560894F7F932C8886EBFCF | %     |
+---------------+-------------------------------------------+-----------+

新建test数据库，创立一个article表以备后续测验

CREATE TABLE `article` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(64) DEFAULT NULL,
 `content` text,
 PRIMARY KEY (`id`)
) CHARSET=utf8;

重启服务并改写数据库状况到存储文件中（with read lock表明在此进程中，客户端只能读数据，以便获得一个一致性的快照）

[root@deepinsea ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@deepinsea mysql]# mysql -uroot -proot
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

检查master上当时的二进制日志和偏移量（记一下其间的File和Position）

mysql> show master status \G
*************************** 1. row ***************************
       File: mysql-bin.000002
     Position: 154
   Binlog_Do_DB: test
 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
Executed_Gtid_Set: 
1 row in set (0.00 sec)

File表明完成仿制功用的日志，即上图中的Binary log；Position则表明Binary log日志文件的偏移量之后的都会同步到slave中，那么在偏移量之前的则需求咱们手动导入。

主服务器上面的任何修正都会保存在二进制日志Binary log里边，从服务器上面启动一个I/O thread（实践上便是一个主服务器的客户端进程），衔接到主服务器上面恳求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log里边。从服务器上面敞开一个SQL thread守时检查Realy log，假如发现有更改立即把更改的内容在本机上面履行一遍。

假如一主多从的话，这时主库既要担任写又要担任为几个从库供给二进制日志。此刻能够稍做调整，将二进制日志只给某一从，这一从再敞开二进制日志并将自己的二进制日志再发给其它从。或许是干脆这个从不记载只担任将二进制日志转发给其它从，这样架构起来功用或许要好得多，而且数据之间的延时应该也稍微要好一些

手动导入，从master中导出数据

mysqldump -uroot -proot -hlocalhost test > /export/data/test.sql

将test.sql中的内容在slave上履行一遍。

装备slave

修正slave的my.ini文件中的[mysqld]部分

log-bin=mysql
server-id=1 #192.168.10.1

保存修正后重启slave，WIN+R->services.msc->MySQL5.7->从头启动

登录slave检查log_bin是否以被敞开：

show VARIABLES like 'log_bin';

装备与master的同步仿制：

stop slave; 
change master to
   master_host='192.168.10.10',    -- master的IP
   master_user='backup',         -- 之前在master上创立的用户
   master_password='1234',
   master_log_file='mysql-bin.000002', -- master上 show master status \G 供给的信息
   master_log_pos=154;

启用slave节点并检查状况

mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
          Master_Host: 192.168.10.10
          Master_User: backup
          Master_Port: 3306
         Connect_Retry: 60
        Master_Log_File: mysql-bin.000002
      Read_Master_Log_Pos: 154
        Relay_Log_File: DESKTOP-KUBSPE0-relay-bin.000002
         Relay_Log_Pos: 320
     Relay_Master_Log_File: mysql-bin.000002
       Slave_IO_Running: Yes
       Slave_SQL_Running: Yes
        Replicate_Do_DB:
      Replicate_Ignore_DB:
      Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
          Last_Errno: 0
          Last_Error:
         Skip_Counter: 0
      Exec_Master_Log_Pos: 154
        Relay_Log_Space: 537
        Until_Condition: None
        Until_Log_File:
         Until_Log_Pos: 0
      Master_SSL_Allowed: No
      Master_SSL_CA_File:
      Master_SSL_CA_Path:
        Master_SSL_Cert:
       Master_SSL_Cipher:
        Master_SSL_Key:
     Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
         Last_IO_Errno: 0
         Last_IO_Error:
        Last_SQL_Errno: 0
        Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
       Master_Server_Id: 10
          Master_UUID: f68774b7-0b28-11e9-a925-000c290abe05
       Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\master.info
           SQL_Delay: 0
      SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
      Master_Retry_Count: 86400
          Master_Bind:
    Last_IO_Error_Timestamp:
   Last_SQL_Error_Timestamp:
        Master_SSL_Crl:
      Master_SSL_Crlpath:
      Retrieved_Gtid_Set:
       Executed_Gtid_Set:
         Auto_Position: 0
     Replicate_Rewrite_DB:
         Channel_Name:
      Master_TLS_Version:
1 row in set (0.00 sec)

留意检查第4、14、15三行，若与我一致，表明slave装备成功

测验

关闭master的读取确认

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

向master中刺进一条数据

mysql> use test
mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');
Query OK, 1 row affected (0.00 sec)

检查slave是否主动同步了数据

mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');
Query OK, 1 row affected (0.00 sec)

至此，主从仿制的装备成功！：)

运用mysqlreplicate指令快速建立 Mysql 主从仿制

读写别离✂

读写别离是依托于主从仿制，而主从仿制又是为读写别离服务的。由于主从仿制要求slave不能写只能读（假如对slave履行写操作，那么show slave status将会呈现Slave_SQL_Running=NO，此刻你需求依照前面提到的手动同步一下slave）。

计划一、界说两种衔接

就像咱们在学JDBC时界说的DataBase相同，咱们能够抽取出ReadDataBase,WriteDataBase implements DataBase，可是这种办法无法运用优秀的线程池技能如DruidDataSource帮咱们办理衔接，也无法运用Spring AOP让衔接对DAO层通明。

计划二、运用Spring AOP

假如能够运用Spring AOP处理数据源切换的问题，那么就能够和Mybatis、Druid整合到一同了。

咱们在整合Spring1和Mybatis时，咱们只需写DAO接口和对应的SQL句子，那么DAO实例是由谁创立的呢？实践上便是Spring帮咱们创立的，它经过咱们注入的数据源，帮咱们完成从中获取数据库衔接、运用衔接履行 SQL 句子的进程以及最终偿还衔接给数据源的进程。

假如咱们能在调用DAO接口时根据接口办法命名标准（增addXXX/createXXX、删deleteXX/removeXXX、改updateXXXX、查selectXX/findXXX/getXX/queryXXX）动态地挑选数据源（读数据源对应衔接master而写数据源对应衔接slave），那么就能够做到读写别离了。

项目结构

引入依托

其间，为了便利拜访数据库引入了mybatis和druid，完成数据源动态切换首要依托spring-aop和spring-aspects

<dependencies>
  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.3.2</version>
  </dependency>
  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.6</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-core</artifactId>
    <version>5.0.8.RELEASE</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-aop</artifactId>
    <version>5.0.8.RELEASE</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>5.0.8.RELEASE</version>
  </dependency>
  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.6</version>
  </dependency>
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.2</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.0.8.RELEASE</version>
  </dependency>
​
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-aspects</artifactId>
    <version>5.0.8.RELEASE</version>
  </dependency>
​
  <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.22</version>
  </dependency>
  <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>5.0.8.RELEASE</version>
  </dependency>
  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
  </dependency>
​
</dependencies>

数据类

package top.deepinsea.mysqloptimize.entity;
​
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
​
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Article {
​
  private int id;
  private String title;
  private String content;
}

spring装备文件

其间RoutingDataSourceImpl是完成动态切换功用的中心类，稍后介绍。

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
​
  <context:property-placeholder location="db.properties"></context:property-placeholder>
​
  <context:component-scan base-package="top.deepinsea.mysqloptimize"/>
​
  <bean id="slaveDataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <property name="driverClassName" value="${db.driverClass}"/>
    <property name="url" value="${master.db.url}"></property>
    <property name="username" value="${master.db.username}"></property>
    <property name="password" value="${master.db.password}"></property>
  </bean>
​
  <bean id="masterDataSource" class="com.alibaba.druid.pool.DruidDataSource">
    <property name="driverClassName" value="${db.driverClass}"/>
    <property name="url" value="${slave.db.url}"></property>
    <property name="username" value="${slave.db.username}"></property>
    <property name="password" value="${slave.db.password}"></property>
  </bean>
​
  <bean id="dataSourceRouting" class="top.deepinsea.mysqloptimize.dataSource.RoutingDataSourceImpl">
    <property name="defaultTargetDataSource" ref="masterDataSource"></property>
    <property name="targetDataSources">
      <map key-type="java.lang.String" value-type="javax.sql.DataSource">
        <entry key="read" value-ref="slaveDataSource"/>
        <entry key="write" value-ref="masterDataSource"/>
      </map>
    </property>
    <property name="methodType">
      <map key-type="java.lang.String" value-type="java.lang.String">
        <entry key="read" value="query,find,select,get,load,"></entry>
        <entry key="write" value="update,add,create,delete,remove,modify"/>
      </map>
    </property>
  </bean>
​
  <!-- Mybatis文件 -->
  <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="configLocation" value="classpath:mybatis-config.xml" />
    <property name="dataSource" ref="dataSourceRouting" />
    <property name="mapperLocations" value="mapper/*.xml"/>
  </bean>
​
  <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="top.deepinsea.mysqloptimize.mapper" />
    <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
  </bean>
</beans>
​
dp.properties
master.db.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
master.db.username=root
master.db.password=root
​
slave.db.url=jdbc:mysql://192.168.10.10:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
slave.db.username=root
slave.db.password=root
​
db.driverClass=com.mysql.jdbc.Driver
​
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <typeAliases>
    <typeAlias type="top.deepinsea.mysqloptimize.entity.Article" alias="Article"/>
  </typeAliases>
</configuration>
​

mapper接口和装备文件

ArticleMapper.java
package top.deepinsea.mysqloptimize.mapper;
​
import org.springframework.stereotype.Repository;
import top.deepinsea.mysqloptimize.entity.Article;
​
import java.util.List;
​
@Repository
public interface ArticleMapper {
​
   List<Article> findAll();
​
   void add(Article article);
​
   void delete(int id);
​
}
​
ArticleMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="top.deepinsea.mysqloptimize.mapper.ArticleMapper">
  <select id="findAll" resultType="Article">
     select * from article
  </select>
​
  <insert id="add" parameterType="Article">
     insert into article (title,content) values (#{title},#{content})
  </insert>
​
  <delete id="delete" parameterType="int">
     delete from article where id=#{id}
  </delete>
</mapper>

中心类

RoutingDataSourceImpl

package top.deepinsea.mysqloptimize.dataSource;
​
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
​
import java.util.*;
​
/**
 * RoutingDataSourceImpl class
 * 数据源路由
 *
 * @author deepinsea, blog:deepinsea.top
 * @date 2018/12/29
 */
public class RoutingDataSourceImpl extends AbstractRoutingDataSource {
​
  /**
   * key为read或write
   * value为DAO办法的前缀
   * 什么前缀最初的办法运用读数据员，什么最初的办法运用写数据源
   */
  public static final Map<String, List<String>> METHOD_TYPE_MAP = new HashMap<String, List<String>>();
​
  /**
   * 由咱们指定数据源的id，由Spring切换数据源
   *
   * @return
   */
  @Override
  protected Object determineCurrentLookupKey() {
    System.out.println("数据源为："+DataSourceHandler.getDataSource());
    return DataSourceHandler.getDataSource();
   }
​
  public void setMethodType(Map<String, String> map) {
    for (String type : map.keySet()) {
      String methodPrefixList = map.get(type);
      if (methodPrefixList != null) {
        METHOD_TYPE_MAP.put(type, Arrays.asList(methodPrefixList.split(",")));
       }
     }
   }
}

它的首要功用是，原本咱们只装备一个数据源，因而Spring动态署理DAO接口时直接运用该数据源，现在咱们有了读、写两个数据源，咱们需求参加一些自己的逻辑来告诉调用哪个接口运用哪个数据源（读数据的接口运用slave，写数据的接口运用master。这个告诉Spring该运用哪个数据源的类便是AbstractRoutingDataSource，有必要重写的办法determineCurrentLookupKey回来数据源的标识，结合spring装备文件（下段代码的5，6两行）

<bean id="dataSourceRouting" class="top.deepinsea.mysqloptimize.dataSource.RoutingDataSourceImpl">
  <property name="defaultTargetDataSource" ref="masterDataSource"></property>
  <property name="targetDataSources">
    <map key-type="java.lang.String" value-type="javax.sql.DataSource">
      <entry key="read" value-ref="slaveDataSource"/>
      <entry key="write" value-ref="masterDataSource"/>
    </map>
  </property>
  <property name="methodType">
    <map key-type="java.lang.String" value-type="java.lang.String">
      <entry key="read" value="query,find,select,get,load,"></entry>
      <entry key="write" value="update,add,create,delete,remove,modify"/>
    </map>
  </property>
</bean>

假如determineCurrentLookupKey回来read那么运用slaveDataSource，假如回来write就运用masterDataSource。

DataSourceHandler

package top.deepinsea.mysqloptimize.dataSource;
​
/**
 * DataSourceHandler class
 * <p>
 * 将数据源与线程绑定，需求时根据线程获取
 *
 * @author deepinsea, blog:deepinsea.top
 * @date 2018/12/29
 */
public class DataSourceHandler {
​
  /**
   * 绑定的是read或write，表明运用读或写数据源
   */
  private static final ThreadLocal<String> holder = new ThreadLocal<String>();
​
  public static void setDataSource(String dataSource) {
    System.out.println(Thread.currentThread().getName()+"设置了数据源类型");
    holder.set(dataSource);
   }
​
  public static String getDataSource() {
    System.out.println(Thread.currentThread().getName()+"获取了数据源类型");
    return holder.get();
   }
}

DataSourceAspect

package top.deepinsea.mysqloptimize.dataSource;
​
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.stereotype.Component;
​
import java.util.List;
import java.util.Set;
​
import static top.deepinsea.mysqloptimize.dataSource.RoutingDataSourceImpl.METHOD_TYPE_MAP;
​
/**
 * DataSourceAspect class
 *
 * 装备切面，根据办法前缀设置读、写数据源
 * 项目启动时会加载该bean，并依照装备的切面（哪些切入点、怎么增强）确认动态署理逻辑
 * @author deepinsea,blog:deepinsea.top
 * @date 2018/12/29
 */
@Component
//声明这是一个切面，这样Spring才会做相应的装备，不然只会当做简略的bean注入
@Aspect
@EnableAspectJAutoProxy
public class DataSourceAspect {
​
  /**
   * 装备切入点：DAO包下的一切类的一切办法
   */
  @Pointcut("execution(* top.deepinsea.mysqloptimize.mapper.*.*(..))")
  public void aspect() {
​
   }
​
  /**
   * 装备前置增强，目标是aspect()办法上装备的切入点
   */
  @Before("aspect()")
  public void before(JoinPoint point) {
    String className = point.getTarget().getClass().getName();
    String invokedMethod = point.getSignature().getName();
    System.out.println("对 "+className+"$"+invokedMethod+" 做了前置增强，确认了要运用的数据源类型");
​
    Set<String> dataSourceType = METHOD_TYPE_MAP.keySet();
    for (String type : dataSourceType) {
      List<String> prefixList = METHOD_TYPE_MAP.get(type);
      for (String prefix : prefixList) {
        if (invokedMethod.startsWith(prefix)) {
          DataSourceHandler.setDataSource(type);
          System.out.println("数据源为："+type);
          return;
         }
       }
     }
   }
}

测验读写别离

怎么测验读是从slave中读的呢？能够将写后仿制到slave中的数据更改，再读该数据就知道是从slave中读了。==留意==，一但对slave做了写操作就要从头手动将slave与master同步一下，不然主从仿制就会失效。

package top.deepinsea.mysqloptimize.dataSource;
​
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import top.deepinsea.mysqloptimize.entity.Article;
import top.deepinsea.mysqloptimize.mapper.ArticleMapper;
​
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:spring-mybatis.xml")
public class RoutingDataSourceTest {
​
  @Autowired
  ArticleMapper articleMapper;
​
  @Test
  public void testRead() {
    System.out.println(articleMapper.findAll());
   }
​
  @Test
  public void testAdd() {
    Article article = new Article(0, "我是新刺进的文章", "测验是否能够写到master而且仿制到slave中");
    articleMapper.add(article);
   }
​
  @Test
  public void testDelete() {
    articleMapper.delete(2);
   }
}

负载均衡

负载均衡算法

• 轮询
• 加权轮询：依照处理才能来加权
• 负载分配：根据当时的空闲状况（可是测验每个节点的内存运用率、CPU运用率等，再做比较选出最闲的那个，功率太低）

高可用

在服务器架构时，为了确保服务器7×24不宕机在线状况，需求为每台单点服务器（由一台服务器供给服务的服务器，如写服务器、数据库中间件）供给冗余机。

关于写服务器来说，需求供给一台同样的写-冗余服务器，当写服务器健康时（写-冗余经过心跳检测），写-冗余作为一个从机的人物仿制写服务器的内容与其做一个同步；当写服务器宕机时，写-冗余服务器便顶上来作为写服务器继续供给服务。对外界来说这个处理进程是通明的，即外界仅经过一个IP拜访服务。

典型SQL

线上DDL

DDL(Database Definition Language)是指数据库表结构的界说（create table）和保护（alter table）的语言。在线上履行DDL，在低于MySQL5.6版本时会导致全表被独占确认，此刻表处于保护、不行操作状况，这会导致该期间对该表的一切拜访无法响应。可是在MySQL5.6之后，支撑Online DDL，大大缩短了锁守时刻。

优化技巧是选用的保护表结构的DDL（比方添加一列，或许添加一个索引），是==copy==战略。思路：创立一个满意新结构的新表，将旧表数据==逐条==导入（仿制）到新表中，以确保==一次性确认的内容少==（确认的是正在导入的数据），同时旧表上能够履行其他任务。导入的进程中，将对旧表的一切操作以日志的方式记载下来，导入结束后，将更新日志在新表上再履行一遍（确保一致性）。最终，新表替换旧表（在运用程序中完成，或许是数据库的rename，视图完成）。

但跟着MySQL的晋级，这个问题简直淡化了。

数据库导入句子

在康复数据时，或许会导入很多的数据。此刻为了快速导入，需求把握一些技巧：

0. 导入时==先禁用索引和束缚==：

alter table table-name disable keys

待数据导入完成之后，再敞开索引和束缚，一次性创立索引

alter table table-name enable keys

0. 数据库假如运用的引擎是Innodb，那么它==默许会给每条写指令加上业务==（这也会耗费必定的时刻），因而主张先手动敞开业务，再履行必定量的批量导入，最终手动提交业务。
1. 假如批量导入的SQL指令格局相同仅仅数据不同，那么你应该先prepare==预编译==一下，这样也能节省很多重复编译的时刻。

limit offset,rows

尽量确保不要呈现大的offset，比方limit 10000,10相当于对已查询出来的行数弃掉前10000行后再取10行，完全能够加一些条件过滤一下（完成挑选），而不应该运用limit跳过已查询到的数据。这是一个==offset做无用功==的问题。对应实践工程中，要防止呈现大页码的状况，尽量引导用户做条件过滤。

select * 要少用

即尽量挑选自己需求的字段select，但这个影响不是很大，由于网络传输多了几十上百字节也没多少延时，而且现在流行的ORM结构都是用的select *，仅仅咱们在规划表的时分留意将大数据量的字段别离，比方产品概况能够独自抽离出一张产品概况表，这样在检查产品简略页面时的加载速度就不会有影响了。

order by rand()不要用

它的逻辑便是随机排序（为每条数据生成一个随机数，然后根据随机数巨细进行排序）。如select * from student order by rand() limit 5的履行功率就很低，由于它为表中的每条数据都生成随机数并进行排序，而咱们只要前5条。

处理思路：在运用程序中，将随机的主键生成好，去数据库中运用主键检索。

单表和多表查询

多表查询：join、子查询都是触及到多表的查询。假如你运用explain剖析履行计划你会发现多表查询也是一个表一个表的处理，最终兼并成果。因而能够说单表查询将计算压力放在了运用程序上，而多表查询将计算压力放在了数据库上。

现在有ORM结构帮咱们处理了单表查询带来的目标映射问题（查询单表时，假如发现有外键主动再去查询相关表，是一个表一个表查的）。

count(*)

在MyISAM存储引擎中，会主动记载表的行数，因而运用count(*)能够快速回来。而Innodb内部没有这样一个计数器，需求咱们手动计算记载数量，处理思路便是独自运用一张表：

id	table	count
1	student	100

limit 1

假如能够确认仅仅检索一条，主张加上limit 1，其实ORM结构帮咱们做到了这一点（查询单条的操作都会主动加上limit 1）。

慢查询日志

用于记载履行时刻超过某个临界值的SQL日志，用于快速定位慢查询，为咱们的优化做参阅。

敞开慢查询日志

装备项：slow_query_log

能够运用show variables like ‘slov_query_log’检查是否敞开，假如状况值为OFF，能够运用set GLOBAL slow_query_log = on来敞开，它会在datadir下发生一个xxx-slow.log的文件。

设置临界时刻

装备项：long_query_time

检查：show VARIABLES like 'long_query_time'，单位秒

设置：set long_query_time=0.5

实操时应该从长时刻设置到短的时刻，行将最慢的SQL优化掉

检查日志

一旦SQL超过了咱们设置的临界时刻就会被记载到xxx-slow.log中

profile信息

装备项：profiling

敞开profile

set profiling=on

敞开后，一切的SQL履行的具体信息都会被主动记载下来

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling   | OFF  |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
​
mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

检查profile信息

show profiles
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling   | ON   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
​
mysql> insert into article values (null,'test profile',':)');
Query OK, 1 row affected (0.15 sec)
​
mysql> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration  | Query                         |
+----------+------------+-------------------------------------------------------+
|    1 | 0.00086150 | show variables like 'profiling'            |
|    2 | 0.15027550 | insert into article values (null,'test profile',':)') |
+----------+------------+-------------------------------------------------------+

经过Query_ID检查某条SQL一切具体进程的时刻

show profile for query Query_ID

上面show profiles的成果中，每个SQL有一个Query_ID，能够经过它检查履行该SQL经过了哪些进程，各耗费了多少时刻。

典型的服务器装备

以下的装备全都取决于实践的运行环境

• max_connections，最大客户端衔接数

  mysql> show variables like 'max_connections';
  +-----------------+-------+
  | Variable_name  | Value |
  +-----------------+-------+
  | max_connections | 151  |
  +-----------------+-------+
  

• table_open_cache，表文件句柄缓存（表数据是存储在磁盘上的，缓存磁盘文件的句柄便利打开文件读取数据）

  mysql> show variables like 'table_open_cache';
  +------------------+-------+
  | Variable_name   | Value |
  +------------------+-------+
  | table_open_cache | 2000  |
  +------------------+-------+
  ​
  

• key_buffer_size，索引缓存巨细（将从磁盘上读取的索引缓存到内存，能够设置大一些，有利于快速检索）

  mysql> show variables like 'key_buffer_size';
  +-----------------+---------+
  | Variable_name  | Value  |
  +-----------------+---------+
  | key_buffer_size | 8388608 |
  +-----------------+---------+
  ​
  

• innodb_buffer_pool_size，Innodb存储引擎缓存池巨细（关于Innodb来说最重要的一个装备，假如一切的表用的都是Innodb，那么甚至主张将该值设置到物理内存的80%，Innodb的很多功用进步如索引都是依托这个）

  mysql> show variables like 'innodb_buffer_pool_size';
  +-------------------------+---------+
  | Variable_name      | Value  |
  +-------------------------+---------+
  | innodb_buffer_pool_size | 8388608 |
  +-------------------------+---------+
  ​
  

• innodb_file_per_table（innodb中，表数据寄存在.ibd文件中，假如将该装备项设置为ON，那么一个表对应一个ibd文件，不然一切innodb同享表空间）

压测东西mysqlslap

装置MySQL时附带了一个压力测验东西mysqlslap（位于bin目录下）

主动生成sql测验

C:\Users\zaw>mysqlslap --auto-generate-sql -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
     Average number of seconds to run all queries: 1.219 seconds
     Minimum number of seconds to run all queries: 1.219 seconds
     Maximum number of seconds to run all queries: 1.219 seconds
     Number of clients running queries: 1
     Average number of queries per client: 0
​

并发测验

C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=100 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
     Average number of seconds to run all queries: 3.578 seconds
     Minimum number of seconds to run all queries: 3.578 seconds
     Maximum number of seconds to run all queries: 3.578 seconds
     Number of clients running queries: 100
     Average number of queries per client: 0
    
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
     Average number of seconds to run all queries: 5.718 seconds
     Minimum number of seconds to run all queries: 5.718 seconds
     Maximum number of seconds to run all queries: 5.718 seconds
     Number of clients running queries: 150
     Average number of queries per client: 0
​

多轮测验

C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=10 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
     Average number of seconds to run all queries: 5.398 seconds
     Minimum number of seconds to run all queries: 4.313 seconds
     Maximum number of seconds to run all queries: 6.265 seconds
     Number of clients running queries: 150
     Average number of queries per client: 0
​

存储引擎测验

C:\Users\deepinsea> mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=innodb -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
     Running for engine innodb
     Average number of seconds to run all queries: 5.911 seconds
     Minimum number of seconds to run all queries: 5.485 seconds
     Maximum number of seconds to run all queries: 6.703 seconds
     Number of clients running queries: 150
     Average number of queries per client: 0
    
C:\Users\deepinsea>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=myisam -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
     Running for engine myisam
     Average number of seconds to run all queries: 53.104 seconds
     Minimum number of seconds to run all queries: 46.843 seconds
     Maximum number of seconds to run all queries: 60.781 seconds
     Number of clients running queries: 150
     Average number of queries per client: 0

结语

欢迎点赞重视评论，感谢观看ヾ(◍∇◍)ﾉﾞ