MySQL

[TOC]

https://blog.csdn.net/ThinkWon/article/details/104778621

一、数据库管理

1）、SQL语言分类：

DDL：（Data Definition Language，数据定义语言）：用来建立数据库、数据库对象和定义字段，如CERATE、ALTER、DROP；

DML：（Data Manipulation Language，数据操纵语言）：用来插入、删除和修改数据库中的数据，如INSERT、UPDATE、DELETE。

DQL：（Data Query Language，数据查询语言）：用来查询数据库中的数据，如SELECT。

DCL：（Data Control Language，数据控制语言）：用来控制数据库组件的存取许可、存取权限等，如COMMIT、ROLLBACK、GRANT、REVOKE。

2）、创建数据库和表（DDL）：

1、create database a1；

2、use a1；

3、create table a2（）；

4、create table 表名（字段01名称 字段01类型 字段01约束，字段02名称 字段02类型 字段02约束,…）存储引擎，字符集

字段01类型：

int（4） 整型 代表0000-9999

double 浮点型

decimal（5,2）有效数字是5位，小数点后面保留2位 100.00；099.50

float 单精度浮点 4字节

char 字符

**char （10） ** 固定长度字符串，字符串要用单引号引起来

**varchar（50） ** 可变长度字符串

字段01约束：

非空约束： not null

主键约束： primary key（主键）

默认约束：假如没有填数据，默认预先设定的值填写 default ‘未知’

自增特性： auto_increment（自动增长）

存储引擎：myisam innodb

字符集：UTF-8

3）、管理数据表中的数据：

1、insert

insert into 表名 (字段1，字段2) values (字段1的值，字段2的值)

或insert into 表名 values (所有字段的值)

查看添加的信息：select * from 表名；

2、update

update 表名 set 字段名1=值1,字段名2=值2 where 条件表达式

3、delete（对数据操作用delete，对库和表用drop）

格式：delete from 表名 where 条件表达式（不带where代表删除表中所有记录）

4）、数据库高级操作

1、清空表

delete from info；

truncate table info；

truncate清空表，表还在；drop是删除表中所有记录。

truncate和delete是两者的新值初始id不同。

2、临时表

临时建立的表，用于保存一些临时数据，不会长期存在

create temporary table temp_info(…)engine=innodb default charset=utf8;

innodb 支持事务；写在括号外面的是对整张表的设定。

show tables；看不到临时表

3、克隆表

like方法：从info表完整复制结构生成test表

create table test like info;

导入数据：insert into test select * from info;

5）、数据库用户授权

1、设置登录密码为abc123的lisi用户，可以从任意终端登录，对所有库和所有表有全部权限：

create user ‘lisi‘@’%’ identified by ‘abc123’;

grant all on . to ‘lisi‘@’%’ identified by ‘abc123’;

2、设置登录密码为abc123的tom用户，可以从本地终端登录，对mysql库中的user表有select权限

create user ‘tom‘@’localhost’ identified by ‘abc123’;

grant select on mysql.user to ‘tom‘@’abc123’ identified by ‘abc123’;

3、查看当前用户的权限：show grants；

​ 查看当前系统中的用户：select user from mysql.user;

​ 查看从本地登录的tom用户的权限：

​ show grants for ‘tom‘@’localhost’;

4、撤销用户的权限：

​ revoke select on mysql.user from ‘tom‘@’localhost’;

二、索引

1、索引的概念

1、是一个排序的列表，存储着索引值和这个值所对应的物理地址；

2、无需对整个表进行扫描，通过物理地址就可以找到所需数据；

3、是表中一列或者若干列值排序的方法；

4、需要额外的磁盘空间；

补充：

索引需要的额外的磁盘空间伴随着表直接存在

阈值：300行以上的才建立索引，不然浪费磁盘空间

2、索引的作用

1、数据库利用各种快速定位技术，能够大大加快查询速率；

2、当表很大或查询涉及到多个表时，可以成千上万倍地提高查询速度；

3、可以降低数据库的IO成本，并且还可以降低数据库的排序成本；

4、通过创建唯一性索引保证数据表数据的唯一性

IO：输入（写入、更改数据），输出（读取数据）；

5、可以加快表与表之间的连接；

6、在使用分组和排序时，可大大减少分组和排序时间

3、索引的分类

1）、普通索引

最基本的索引类型，没有唯一性之类的限制

1、创建表时创建

create table test(……index id_index(id));

2、直接创建

create index name_index on test(name);

3、修改表结构方式创建

alter table test add index id_index(id);

2）、唯一性索引

与“普通索引”基本相同

与普通索引的区别是索引列的所有值只能出现一次，即必须唯一

1、创建表时创建

create table test(……unique index id_index(id));

2、直接创建

create unique index name_index on test(name);

3、修改表结构方式创建

alter table test add unique index id_index(id);

3）、主键索引

是一种特殊的唯一索引，指定为“primary key”

一个表只能有一个主键，不允许有空值（非空且唯一）

1、创建表时创建

create table test(……primary key(id));

2、修改表时创建

alter table test add primary key(id);

4）、组合索引（单列索引与多列索引）

可以是单列上创建的索引，也可以是在多列上创建的索引

最左原则，从左往右依次执行

创建组合索引：create table test(……index ff(id,name,score));

5）、全文索引

1、创建表时创建全文索引

create table test(……fulltext (name));

2、在已存在的表上创建全文索引

create fulltext index name_index on test(name);

3、通过SQL语句alter table创建全文索引

alter table test add fulltext index name_index(score);

4、创建索引的原则

1、表的主键、外键必须有索引；

2、数据量超过300行的表应该有索引；

3、经常与其他表进行连接的表，在连接字段上应该建立索引；

4、唯一性太差的字段不适合建立索引；

5、更新太频繁地字段不适合创建索引；

6、经常出现在 Where子句中的字段，特别是大表的字段，应该建立索引；

7、索引应该建在选择性高的字段上；

8、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

5、查看索引的方法

show index from table_name\G；（\G：竖着显示）

6、删除索引的方法

1、drop index index_name on table_name;

2、alter table table_name drop index index_name;

三、事务

1、概念

1、事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行；

2、事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元；

3、适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等；

4、通过事务的整体性以保证数据的一致性；

5、如果事务成功了一部分，一部分未成功，则执行回滚，回到事务的起点，重新开始操作

2、特点

1）、原子性（Atomicity）

事务是一个完整的操作，事务的各元素是不可分的

事务中的所有元素必须作为一个整体提交或回滚

如果事务中的任何元素失败，则整个事务将失败

2）、一致性（Consistency）

当事务完成时，数据必须处于一致状态

在事务开始前，数据库中存储的数据处于一致状态

在正在进行的事务中，数据可能处于不一致的状态

当事务成功完成时，数据必须再回到已知的一致状态

3）、隔离性（Isolation）

对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务

修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

4）、持久性

指不管系统是否发生故障，事务处理的结果都是永久的

一旦事务被提交，事务的效果会被永久地保留在数据库中

3、控制语句

1、MySQL事务默认是自动提交的，当SQL语句提交时事务便自动提交；

2、事务控制语句

begin 事务的开始

commit 提交

rollback 回滚

savepoint 存档点名称 存档点

release savepoint 存档点名称 删除存档点

rollback to 存档点名称 回滚到某个存档点

set transaction 设置事务

set autocommit=0 禁止自动提交

set autocommit=1 开启自动提交（默认）

4、事务的操作

创建的数据表存储引擎必须是innodb，才支持事务（5.7版本默认就是innodb）

具体操作：

1、begin;

​ insert into test values(1,’zhangsan’);

​ commit;（结束事务）

2、begin;

​ insert into test values(1,’zhangsan’);

​ savepoint a;

​ savepoint b;

​ rollback to b;

​ rollback to a

​ 注意：只能向前回滚，无法向后回滚

3、三种情况事务开始：

begin；

set autocommit=0；

start transaction

三种情况结束事务：

commit；

set autocommit=1；

rollback

四、操作引擎

1、概念

1、MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在 MySQL中称为存储引擎

2、存储引擎就是 MySQL将数据存储在文件系统中的存储方式或者存储格式

3、目前 MySQL常用的两种存储引擎

MyISAM

InnoDB

（innodb支持事务，myisam不支持事务）

4、MySQL存储引擎是 MySQL数据库服务器中的组件，负责为数据库执行实际的数据I/O操作

使用特殊存储引擎的主要优点之一在于：

仅需提供特殊应用所需的特性

数据库中的系统开销较小

具有更有效和更高的数据库性能

5、MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储

2、Myisam

1）、特点：

1、myisam不支持事务，也不支持外键

2、访问速度快

3、对事物完整性没有要求

4、myisam在磁盘上存储成三个文件

.frm文件存储表定义

数据文件的扩展名为.MYD(MYData)

索引文件的扩展名是.MYI(MYIndex)

5、表级锁定形式，数据在更新时锁定整个表（不允许两个人同时操作）

6、数据库在读写过程中相互阻塞

会在数据写入的过程阻塞用户数据的读取，也会在数据读取的过程中阻塞用户的数据写入

7、数据单独写入或读取，速度过程较快且占用资源相对少

8、myisam

静态表

动态表 （直接写入的，数据会随时变的）

压缩表

2）、适用生产场景

1、公司业务不需要事务的支持

2、单方面读取或写入数据比较多的业务

3、myisam存储引擎数据读写都比较频繁场景不适合

4、使用读写并发访问相对较低的业务

5、数据修改相对较少的业务

6、对数据业务一致性要求不是非常高的业务

7、服务器硬件资源相对比较差

3、Innodb

1）、特点：

1、支持事务：支持4个事务隔离级别

2、行级（读写分离）锁定，但是全表扫描仍然会是表级锁定

3、读写阻塞与事务隔离级别相关

4、具有非常高效的缓存特性：能缓存索引，也能缓存数据

5、表与主键以簇的方式存储

6、支持外键约束，5.5以前不支持全文索引，5.5版本以后支持全文索引

7、对硬件资源要求还是比较高的场合

2）、适用生产场景

1、业务需要事务的支持

2、行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成

3、业务数据更新较为频繁的场景，如：论坛、微博等

4、业务数据一致性要求较高，如：银行业务

5、硬件设备内存较大（因为事务都先放内存），利用innodb较好的缓存能力来提高内存利用率，减少磁盘IO的压力

4、修改存储引擎

1、alter table 修改

alter table table_name engine=引擎；

2、修改my.cnf，指定默认存储引擎并重启服务

在[mysqld]下面添加default-storage-engine=InnoDB

3、create table创建表时指定存储引擎

create table 表名（字段）engine=引擎

4、Mysql_convert_table_format转化存储引擎

Mysql_convert_table_format –user=root –password=密码

–sock=/tmp/mysql.sock-engine=引擎 库名 表名

五、备份与恢复

1、备份的分类

1）、从物理和逻辑的角度

1、物理备份：

对数据库操作系统的物理文件（如数据文件、日志文件等）的备份

冷备份（脱机备份）：是在关闭数据库的时候进行的

热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件

温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

2、逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

2）、从数据库的备份策略角度

1、完全备份：每次对数据库进行完整的备份

2、差异备份：备份自从上次完全备份之后被修改过的文件

3、增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

2、常见的备份方法

1）、物理冷备

备份时数据库处于关闭状态，直接打包数据库文件

备份速度快，恢复时也是最简单的

2）、专业备份工具mysqldump或mysqlhotcopy

mysqldump常用的逻辑备份工具

mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

3）、启用二进制日志进行增量备份

进行增量备份，需要刷新二进制日志

4）、第三方工具备份

免费的MySQL热备份软件Percona XtraBackup

3、完全备份

1）、简介

1、是对整个数据库、数据库结构和文件结构的备份

2、保存的是备份完成时刻的数据库

3、是差异备份与增量备份的基础

4、每次对数据进行完整的备份，完全备份是增量备份的基础，完全备份保存的是备份完成时刻的数据库

2）、优点

备份与恢复操作简单方便

3）、缺点

数据存在大量的重复

占用大量的备份空间

备份与恢复时间长

4）、分类

1、物理冷备份与恢复

关闭MySQL数据库

使用tar命令直接打包数据库文件夹（/usr/local/mysql/data）

直接替换现有MySQL目录即可

2)、mysqldump备份与恢复

MySQL自带的备份工具，可方便实现对MySQL的备份

可以将指定的库、表导出为SQL脚本（.sql结尾）

使用命令MySQL导入备份的数据

操作步骤：

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###备份所有数据库

mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库

mysqldump -u root -p auth > auth-$(data +%F).sql ###备份auth数据库

mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表

mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构

方法一：

[root@server1 ~]# mysqldump -u root -p test > test-$(date +%F).sql

mysql> drop database test;

mysql> create database test2; ###建立空库

[root@server1 ~]# mysql -u root -p test2 < test-2020-10-23.sql

方法二：

[root@server1 ~]# mysqldump -u root -p test > test-$(date +%F).sql

mysql> drop database test;

mysql> create database test2;

mysql> use test2;

mysql> source /root/test-2020-10-24.sql;

4、增量备份

1、使用mysqldump进行完全备份存在的问题

备份数据中有重复数据

备份时间与恢复时间过长

2、MySQL增量备份是自上一次备份后增加/变化的文件或者内容

3、特点

没有重复数据，备份量不大，时间短

恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

4、MySQL没有提供直接的增量备份方法

可通过MySQL提供的二进制日志间接实现增量备份

5、MySQL二进制日志对备份的意义

二进制日志保存了所有更新或者可能更新数据库的操作

二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件

只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

5、增量恢复

1）、一般恢复

将所有备份的二进制日志内容全部恢复

2）、断点恢复

1、基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作

可以基于精准的位置跳过错误的操作

[root@server1 ~]# mkdir -p /opt/bak_sql

[root@server1 ~]# mysqldump -uroot -p test2 > /opt/bak_sql/test2-$(date +%F).sql; ###完整备份



[root@server1 ~]# vi /etc/my.cnf

[mysqld]

log_bin=/usr/local/mysql/data/mysql_bin ###开启增量备份



[root@server1 ~]# systemctl restart mysqld



[root@server1 ~]# mysqladmin -uroot -p flush-logs ###将二进制日志更新，产生新的日志文件

[root@server1 ~]# cd /usr/local/mysql/data/

[root@server1 data]# ll ###查询增量备份结果



[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 ###查询该二进制日志内容是否正确



[root@server1 ~]# mysqladmin -u root -p flush-logs; ###将二进制日志更新，产生新的日志文件

[root@server1 ~]# cd /usr/local/mysql/data/

[root@server1 data]# ll ###查询增量备份结果

[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ###查询该二进制日志内容是否正确



mysql> use test2;

mysql> drop table aa; ###先删掉坏的那张表

[root@server1 ~]# mysql -u root -p test2 < /opt/bak_sql/test2-2020-10-24.sql ###还原完全备份的数据库



[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002 ###查询该二进制日志内容



[root@server1 ~]# mysqlbinlog --no-defaults --stop-datetime='2020-10-24  0:55:25' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p ###停止错误的时间



[root@server1 ~]# mysqlbinlog --no-defaults --start-datetime='2020-10-24  0:55:48' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p ###开始正确的时间

2、基于时间点恢复

跳过某个发生错误的时间点实现数据恢复

前面步骤同基于时间恢复

[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ###查询该二进制日志内容



[root@server1 ~]# mysqlbinlog --no-defaults --stop-position='2168' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p ###上一次操作正确的位置点停止

[root@server1 ~]# mysqlbinlog --no-defaults --start-position='2537' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p ###下一次操作正确的位置点开始

六、主从复制

1、原理

1、Master将用户对数据库更新的操作以二进制格式保存到Binary Log日志文件中；

2、Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；

3、Master接收来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；

4、Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清除的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”；

5、Slave的Sql进程检测到relay-log中新增了内容后，会马上解析relay-log的内容称为在Master端真实执行时候的那些可执行的内容，并在自身执行。

故障及解决方法

MySQL复制方式：

半同步复制 –》同步速度慢一点能保证slave同步二进制日志的完整性

异步复制–》同步速度快，但是不能确保二进制日志确实到达slave上

在半同步复制的架构下，当master在将自己binlog发给slave上的时候，要确保slave已经接受到了这个二进制日志以后，才会返回数据给客户端。

对比两种架构：异步复制对于用户来说，可以确保得到快速的响应结构，但是不能确保二进制日志确实到达了slave上；半同步复制对于客户的请求响应稍微慢点，但是他可以保证二进制日志的完整性。

============================================================================

故障一：

开发设置的crontab计划任务周期不合理：

感觉前端的页面（LAMP）打开有点延迟，平时正常都很快就加载出来了，查找原因：

发现数据库占满了，因为数据要从数据库拿，影响了读取速度；

问题：周期性计划开发设计不合理，本来1个小时的，结果三分钟一次写业务数据到数据库，导致那个数据库很大占满了，

解决方法：通知开发去修改

故障二：

MySQL中主从延迟高，怎么解决？

主从同步的延迟的原因：

我们知道， 一个服务器开放Ｎ个链接给客户端来连接的，这样有会有大并发的更新操作, 从服务器通过I/O的线程去主服务器同步二进制日志，当某个SQL在从服务器上执行的时间稍长 或者由于某个SQL要进行锁表就会导致主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致，也就是主从延迟。

主从同步延迟的解决办法：

软件方面：

因为所有的SQL必须都要在从服务器里面执行一遍，但是主服务器如果不断的有更新操作源源不断的写入，那么一旦有延迟产生，那么延迟加重的可能性就会原来越大。 当然我们可以做一些缓解的措施。

a. 我们知道因为主服务器要负责更新操作， 他对安全性的要求比从服务器高， 所有有些设置可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置，而slave则不需要这么高的数据安全，完全可以将sync_binlog设置为0或者关闭binlog，innodb_flushlog，innodb_flush_log_at_trx_commit 也可以设置为0来提高sql的执行效率 这个能很大程度上提高效率。另外就是使用比主库更好的硬件设备作为slave。

b. 就是把，一台从服务器当作为备份使用，而不提供查询，那边他的负载下来了，执行relay log 里面的SQL效率自然就高了。

硬件方面：

1.主或者从服务器负载过高，给服务器扩容；

2.增加从服务器，这个目的还是分散读的压力， 从而降低服务器负载。

3.判断主从延迟的方法

可以通过 show slave status 进行查看，比如可以看看Seconds_Behind_Master参数的值来判断，是否有发生主从延时。

其值有这么几种：

NULL - 表示io_thread或是sql_thread有任何一个发生故障，也就是该线程的Running状态是No,而非Yes.

0 - 该值为零，是我们极为渴望看到的情况，表示主从复制状态正常

2、主服务器（Master）

1）、日志文件

修改配置文件

server-id=1 ###mysql服务器id，每个服务器不能相同

log_bin=master-bin ###主服务器日志文件

log_slave_updates=true ###允许中继日志读取主服务器的二进制日志

2）、授权

mysql> grant replication slave on *.* to 'myslave'@'192.168.73.%' identified by 'abc123'; ###为所有从服务器授权所有数据库

mysql> flush privileges;

3）、记录日志文件及位置点

mysql> show master status; ###记下二进制日志文件及position的值

3、从服务器

1）、日志文件

server-id = 12 ###MySQL服务器的id，需要配置不同数字

relay_log=relay-log-bin ###从主服务器上同步日志文件记录到本地中继日志

relay_log_index=slave-relay-bin.index ###定义中继日志的索引

2）、与Master同步

mysql> change master to master_host='192.168.73.10',master_user='myslave',master_password='abc123',master_log_file='master-bin.000001',master_log_pos=863;

mysql> start slave; ###启动从服务器

mysql> show slave status\G ###查看从服务器状态

...省略

 Slave_IO_Running: Yes 

 Slave_SQL_Running: Yes ###这两项需要为YES

3）、主从复制失败原因

1、I/O线程显示为NO: 主库与从库网络不通、主库未授权给从库、

2、SQL线程显示为NO：从库日志和位置点与主不同步

3、若从库查看连接主库I/0线程状态为conneting，一直是这个状态，考虑双方的防火墙是否开启。

七、读写分离

1、原理

1、只在主服务器上写，只在从服务器上读

2、主数据库处理事务性查询，从数据库处理select查询

3、数据库复制用于将事务性查询的变更同步到集群中的从数据库

读写分离原理示意图

2、部署amoeba

基于读写分离

1）、安装jdk环境

2）、部署amoeba代理

[root@amoeba opt]# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local 

[root@amoeba ~]# mv /usr/local/amoeba-mysql-3.0.5-RC/ /usr/local/amoeba 

[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/ 

[root@amoeba ~]# vim /usr/local/amoeba/jvm.properties 32 #JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPe    rmSize=96m" 33 JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k" 

3）、在MySQL上给amoeba授权

mysql> grant all on *.* to 'test'@'192.168.73.%' identified by 'abc123'; 

4）、修改配置文件

1、amoeba.xml

1、修改连接amoeba使用的用户名和密码

2、给master开放默认池和写池，给slave开放读池

2、dbServer.xml

1、指定为amoeba创建的允许读取数据库的用户名和密码（5.7版本没有默认的test数据库）

2、配置三个服务器的主机名和地址

3、指定名为slaves的poolName中pools的主机名

八、MHA高可用及故障切换

1、概述

1、传统的MySQL主从架构存在的问题

MySQL主服务器出故障后就无法写入数据了

2、MHA简介
一套优秀的MySQL高可用环境下故障切换和主从复制的软件
MySQL故障过程中，MHA能做到0-30秒内自动完成故障切换

3、MHA组成
MHA Manager（管理节点）和 MHA Node（数据节点）
MHA Manager 可以单独部署在一台独立的机器上，管理多个 master-slave 集群（Manger是单独一台监控master服务器健康状态的服务器。）；也可以部署在一台 slave 节点上。MHA Node 运行在每台 MySQL 服务器上，MHA Manager 会定时探测集群中的 master 节点。当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master，然后将所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完 全透明。

4、MHA特点（优势）
在 MHA 自动故障切换过程中，MHA 试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过 SSH 访问，MHA 没法保存二进制日志，就会出现只进行故障转移但丢失了最新的数据的情况。
使用 MySQL 5.5 的半同步复制，可以大大降低数据丢失的风险。MHA 可以与半同步复 制结合起来。如果只有一个 slave 已经收到了最新的二进制日志，MHA 可以将最新的二进 制日志应用于其他所有的 slave 服务器上，因此可以保证所有节点的数据一致性。

5、MHA架构
目前MHA支持一主多从架构，最少三台服务，即一主两从

2、具体配置

1）、配置一主两从

MySQL主配置文件修改：

[root@Master ~]# vi /etc/my.cnf
[mysqld]
server-id = 1
log_bin = master-bin
log-slave-updates = true

MySQL从服务器配置：

1、主备服务器：

[root@Slave1 ~]# vi /etc/my.cnf
[mysqld]
server-id = 2
log_bin = master-bin
relay_log = relay-log-bin
relay_log_index = slave-relay-bin.index

2、从服务器

[root@Slave2 ~]# vi /etc/my.cnf
[mysqld]
server-id = 3
relay_log = relay-log-bin
relay_log_index = slave-relay-bin.index

2）、MHA软件安装

Centos7.6必须安装0.57版本，所有服务器上必须先安装node组件，最后在MHA-Manager节点上安装manager组件，因为manager依赖node组件

3）、配置ssh无密码认证

4）、配置虚拟IP

[root@Manager ~]# vi /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';

use Getopt::Long;

my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
#############################添加内容部分#########################################
my $vip = '192.168.73.200';
my $brdc = '192.168.73.255';
my $ifdev = 'ens33';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
my $exit_code = 0;
#my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
#my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
##################################################################################
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);

exit &main();

sub main {

print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

if ( $command eq "stop" || $command eq "stopssh" ) {

my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {

my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}

sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

5）、配置文件解析

[server default]
manager_log=/var/log/masterha/app1/manager.log #manager日志
manager_workdir=/var/log/masterha/app1 #manager工作目录
master_binlog_dir=/usr/local/mysql/data #master保存binlog的位置
master_ip_failover_script=/usr/local/bin/master_ip_failover #设置自动failover时候的切换脚本
master_ip_online_change_script=/usr/local/bin/master_ip_online_change #设置手动切换时的切换脚本
password=manager #设置mysql中root用户的密码，这个密码是前面创建监控用户的密码
ping_interval=1 #设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行failover
remote_workdir=/tmp #设置远端mysql在发生切换时binlog的保存位置
repl_password=abc123 #设置复制用户的密码
repl_user=myslave #设置复制用户的账户
report_script=/usr/local/send_report #设置发生切换后发送的报警的脚本
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 14.0.0.30 -s 14.0.0.40
shutdown_script="" #设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机防止发生脑裂）
ssh_user=root #设置ssh的登录用户名
user=mha #设置监控用户

6）、ssh无密码认证测试

7）、MHA健康检查

8）、在MHA的master节点开启虚拟IP

九、SQL高级语句

1、按关键字排序（order by）

1、使用order by语句来实现排序
2、排序可针对一个或多个字段
3、ASC：升序，默认排序方式
4、DESC：降序
5、order by的语法结构

select 字段1，字段2 from 表名 order by 字段1 desc|asc，字段2 desc|asc；

2、对结果进行分组

1、使用group by语句来实现分组
2、通常结合聚合函数一起使用
3、可以按一个或多个字段对结果进行分组
4、group by的语法结构

select count(name),score from a where score > 80 group by score;

3、限制结果条目

1、只返回select查询结果的第一行或前几行
2、使用limit语句限制条目
3、limit语法结构

offset：位置偏移量，从0开始
number：返回记录行的最大数目

select 字段1，字段2 from 表名 limit [offset,] number；

offset：位置偏移量，从0开始
number：返回记录行的最大数目

例子：

select * from a limit 2,3;  ##从索引2开始，显示3行

4、设置别名

1、使用as语句设置别名，关键字as可省略
2、设置别名时，保证不能与库中其他表或字段名称冲突
3、别名的语法结构
字段别名：

select 字段 as 别名 from 表名；

表的别名：

select 字段 from 表名 as 别名；

5、通配符

1、用于替换字符串中的部分字符
2、通常配合like一起使用，并协同where完成查询
3、常用通配符

%  表示零个、一个或多个即任意字符
_  表示单个字符 

6、子查询

1、也称作内查询或者嵌套查询
2、先于主查询被执行，其结果将作为外层主查询的条件
3、在增删改查中都可以使用子查询
4、支持多层嵌套
5、in语句用来判断某个值是否在给定的结果集中

例：
select id,name from a where id in(1,2);

select * from a where id in(select id from a where score>80);

7、null值

null：真空（什么都没有）
‘’：空气（还有空气）
1、表示缺失的值
2、与数字0或者空白（spaces）是不同的
3、使用is null或is not null进行判断
4、null值和空值（’’）的区别
空值长度为0，不占空间；null值的长度为null，占用空间
is null无法判断空值
空值使用“=”或者“<>”来处理
count()计算时，null会忽略，空值会加入计算

8、正则表达式

^	匹配文本的开始字符
$	匹配文本的结束字符
.	匹配任何单个字符
*	匹配前面的字符零次或多次
+	匹配前面的字符一次或多次
字符串	匹配包含指定的字符串
p1lp2	匹配p1或p2
[…]	匹配字符集合中任一字符
[^…]	匹配不在括号中的任一字符
{n}	匹配前面的字符串n次
{n，m}	匹配前面的字符串至少n次，之多m次

9、运算符

比较运算符

逻辑运算符

（1）逻辑非

逻辑非将跟在它后面的逻辑测试取反，把真变为假，把假变为真。
如果 NOT 后面的操作数为 0 时，所得值为 1；如果操作数为非 0 时，所得值为 0；如果操作数为 NULL 时，所得值为 NULL。
注意：非0值都是1

（2）逻辑与

如果所有值都是真返回 1，否则返回 0。

（3）逻辑或（最好用or）

逻辑或表示包含的操作数，任意一个为非零值并且不是 NULL 值时，返回 1，否则返回0。

（4）逻辑异或

两个非 NULL 值的操作数，如果两者都是 0 或者都是非 0，则返回 0；如果一个为 0， 另一个为非 0，则返回结果为 1；
当任意一个值为 NULL 时，返回值为 NULL。

运算总结：
and运算，只要碰到0就是0，（非0和null是null）
or运算，只要碰到非0值就是1，（0和null是null）
异或运算，只要碰到null都是null

位运算符

位运算符实际上是对二进制数进行计算的运算符。


位运算方法：
按位与运算
10–》1010
15–》1111
1010 –》10
按位与运算（&），是对应的二进制位都是 1 的，它们的运算结果为 1，否则为 0

按位或运算
10–》1010
15–》1111
1111–》15
按位或运算（|），是对应的二进制位只要是 1 的，它们的运算结果就为 1，否则为 0

按位异或运算
10–》1010
15–》1111
0101–》5
按位异或运算（^），是对应的二进制位不相同时，运算结果 1，否则为 0

按位取反运算
1–》0001
1–》1110
5–》0101
0100–》4
按位取反（），是对应的二进制数逐位反转，即 1 取反后变为 0, 0 取反后变为 1

按位左移运算
1<<2
1–》0001
按位左移2位，空缺处补0
0100–》4

10<<3
10–》1010
按位左移3位，空缺处补0
1010000–》80
按位右移运算
10>>2
10–》1010
按位右移2位，多余的位数直接删除
0010–》2

15>>2
15–》1111
按位右移2位，多余的位数直接删除
0011–》3
常用的运算符优先级

10、连接查询

通常都是将来自两个或多个表的行结合起来，基于这些表之间的共同字段，进行数据的拼接。
要先确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。

使用较多的连接查询包括：内连接、左连接和右连接

1、内连接

在from子句中使用关键字 inner join 来连接多张表，并使用 on子句设置连接条件。
mysql> select info.name,hob.hobbyname from info inner join hob on info.hobby=hob.id;
内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用关键字 JOIN。同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表。

2、外连接

左连接，主表在左边，主表内容会全部显示出来，在从表中没匹配到的以NULL显示出来

右连接，主表在右边，主表内容会全部显示出来，在从表中没匹配到的以NULL显示出来

十、MySQL函数

数据库函数

常用的函数分类
1、数学函数
2、聚合函数
3、字符串函数
4、日期时间函数

1、常用的数学函数

abs(x)：返回x的绝对值
rand()：返回0到1的随机数（0-0.9999…，1是取不到的）
mod(x,y)：返回x除以y以后的余数
power(x,y)：返回x的y次方
round(x)：返回离x最近的整数（四舍五入，只看小数点后第一位）
round(x,y)：保留x的y位小数四舍五入后的值
sqrt(x)：返回x的平方根
truncate(x,y)：返回数字x截断为y位小数的值
ceil(x)：返回大于或等于x的最小整数（向上取整数）
floor(x)：返回小于或等于x的最大整数（向下取整数）
greatest(x1,x2…)：返回集合中最大的值
least(x1,x2…)：返回集合中最小的值

abs(x)：返回x的绝对值

rand()：返回0到1的随机数（0-0.9999…，1是取不到的）

mod(x,y)：返回x除以y以后的余数

power(x,y)：返回x的y次方

round(x)：返回离x最近的整数（四舍五入，只看小数点后第一位）

round(x,y)：保留x的y位小数四舍五入后的值

sqrt(x)：返回x的平方根

truncate(x,y)：返回数字x截断为y位小数的值

ceil(x)：返回大于或等于x的最小整数（向上取整数）

floor(x)：返回小于或等于x的最大整数（向下取整数）

greatest(x1,x2…)：返回集合中最大的值

least(x1,x2…)：返回集合中最小的值

2、聚合函数

●对表中数据记录进行集中概括而设计的一类函数
●常用的聚合函数（只会产生一个值）

avg(字段名) 返回指定字段的平均值
count(字段名) 返回指定字段中非NULL值的个数 
min(字段名) 返回指定字段的最小值
max(字段名) 返回指定字段的最大值
sum(字段名) 返回指定字段的所有值之和

avg(字段名) 返回指定字段的平均值

count(字段名) 返回指定字段中非NULL值的个数

min(字段名) 返回指定字段的最小值

max(字段名) 返回指定字段的最大值

sum(字段名) 返回指定字段的所有值之和

3、字符串函数

●常用的字符串函数

length(x)：返回字符串x的长度（空格也算）
trim()：返回去除指定格式的值（只能去除前后的空格）
concat(x,y)：将提供的参数x和y拼接成一个字符串
upper(x)：将字符串x的所有字母变成大写字母
lower(x)：将字符串x的所有字母变成小写字母
left(x,y)：返回字符串x的前y个字符
right(x,y)：返回字符串x的后y个字符
repeat(x,y)：将字符串x重复y次
space(x)：返回x个空格（结合concat使用）
replace(x,y,z)：将字符串z替代字符串x中的字符串y
strcmp(x,y)：比较x和y，返回的值可以为-1 <，0 =，1 >
substring(x,y,z)：获取从字符串x中的第y个位置开始长度为z的字符串
reverse(x)：将字符串x反转

length(x)：返回字符串x的长度（空格也算）

trim()：返回去除指定格式的值（只能去除前后的空格）

concat(x,y)：将提供的参数x和y拼接成一个字符串

upper(x)：将字符串x的所有字母变成大写字母

lower(x)：将字符串x的所有字母变成小写字母

left(x,y)：返回字符串x的前y个字符

right(x,y)：返回字符串x的后y个字符

repeat(x,y)：将字符串x重复y次

space(x)：返回x个空格（结合concat使用）

replace(x,y,z)：将字符串z替代字符串x中的字符串y

strcmp(x,y)：比较x和y，返回的值可以为-1 <，0 =，1 >

substring(x,y,z)：获取从字符串x中的第y个位置开始长度为z的字符串

格式：substring(完整字符串,起始位置,长度)； ##起始位置从1开始

reverse(x)：将字符串x反转

4、日期时间函数

●常用的日期时间函数

curdate()：返回当前时间的年月日
curtime()：返回当前时间的时分秒
now()：返回当前时间的日期和时间
month(x)：返回日期x中的月份值
hour(x)：返回x中的小时值
minute(x)：返回x中的分钟值
second(x)：返回x中的秒钟值
dayofweek(x)：返回x是星期几，1星期日，2星期一，3星期二…
dayofmonth(x)：计算日期x是本月的第几天
dayofyear(x)：计算日期x是本年的第几天

curdate()：返回当前时间的年月日

curtime()：返回当前时间的时分秒

now()：返回当前时间的日期和时间

month(x)：返回日期x中的月份值

hour(x)：返回x中的小时值

minute(x)：返回x中的分钟值

second(x)：返回x中的秒钟值

dayofweek(x)：返回x是星期几，1星期日，2星期一，3星期二…

dayofmonth(x)：计算日期x是本月的第几天

dayofyear(x)：计算日期x是本年的第几天

十一、MySQL存储过程

1、存储过程简介

1、从 5.0 版本才开始支持
2、是一组为了完成特定功能的SQL语句集合（封装）
3、比传统SQL速度更快、执行效率更高
4、存储过程的优点
执行一次后，会将生成的二进制代码驻留缓冲区（便于下次执行），提高执行效率
SQL语句加上控制语句的集合，灵活性高
在服务器端存储，客户端调用时，降低网络负载
可多次重复被调用，可随时修改，不影响客户端调用
可完成所有的数据库操作，也可控制数据库的信息访问权限
5、为什么要用存储过程？
1.减轻网络负载；2.增加安全性

传统SQL访问MySQL服务端过程：

使用存储过程访问MySQL服务端：

2、创建存储过程

1、使用create procedure语句创建存储过程
2、参数分为
输入参数：in
输出参数：out
输入/输出参数：inout
3、存储过程的主体部分，被称为过程体；以begin开始，以end$$结束
4、具体格式

delimiter $$
create procedure 存储过程名（in 参数名 参数类型）
begin
#定义变量
declare 变量名 变量类型
#变量赋值
set 变量名 = 值
  sql 语句1；
  sql 语句2；
  ...
end$$
delimiter ；（有空格）
123456789101112

示例：

3、调用存储过程

call 存储过程名(实际参数);
1

4、查询存储过程

show procedure status where db='数据库'；
1

5、修改存储过程

存储过程的修改分为特征的修改和业务内容的修改。
特征的修改语法结构如下：

alter procedure 存储过程名 [ <特征> … ]
1

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。

6、删除存储过程

删除存储过程的语法：

drop {procedure|function|if exits} <过程名>
1

7、传递参数过程示例：

1、

2、

总结：in和inout参数会将全局变量的值传入存储过程中，而out参数不会将全局变量的值传入存储过程中。在存储过程使用中，参数值in，out，inout都会发生改变。

3、

总结：调用完存储过程后，发现in参数不会对全局变量的值引起变化，而out和inout参数调用完存储过程后，会对全局变量的值产生变化，会将存储过程引用后的值赋值给全局变量。

in参数赋值类型可以是变量还有定值，而out和inout参数赋值类型必须为变量。

十二、死锁＋慢查询

MySQL引擎默认的锁级别：

MyISAM和MEMORY采用表级锁(table-level locking)。

BDB采用页面锁(page-level locking)或表级锁，默认为页面锁。

InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁。

Innodb引擎中的行锁与表锁

在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，Oracle者是通过在数据块中对相应数据行加锁来实现的。

InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

行级锁的缺点是：如果并发请求大量的锁资源，所以速度慢，内存消耗大。

1、行级锁与死锁

　　MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。

　　在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。 在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。

　　当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

　　发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个获取锁完成事务。

如何怎么避免死锁？

1）以固定的顺序访问表和行。

分为两种情景：

对于不同事务访问不同的表，尽量做到访问表的顺序一致；

对于不同事务访问相同的表，尽量对记录的id做好排序，执行顺序一致；

2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

怎么解决死锁？

第一步，查出已锁的进程

查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;  

查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  

INNODB_TRX表主要是包含了正在InnoDB引擎中执行的所有事务的信息，包括waiting for a lock和running的事务

select * from information_schema.innodb_trx

第二步，kill进程

2、慢查询：

https://blog.csdn.net/weixin_42030357/article/details/104105932

进行SQL优化的手段也主要是修改SQL写法，或者新增索引。

（1）数据库中设置SQL慢查询

一、第一步.开启mysql慢查询

方式一：

​ 修改配置文件在 my.cnf增加几行: 主要是慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）

方法二：通过MySQL数据库开启慢查询:

（2）分析慢查询日志

​ 直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句

 例如：执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000得到如下结果： 显示结果分析：

table |  type | possible_keys | key |key_len  | ref | rows | Extra  EXPLAIN列的解释：           

table                 显示这一行的数据是关于哪张表的           

type                  这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL 

rows                显示需要扫描行数

key                   使用的索引

（3）常见的慢查询优化

①、索引没起作用的情况

a、使用LIKE关键字的查询语句
在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。

2、使用多列索引的查询语句
MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。

②、优化数据库结构

    合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

a、将字段很多的表分解成多个表
对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

b、增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

③、分解关联查询

  将一个大的查询分解为多个小查询是很有必要的。

 很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效，例如：    

SELECT * FROM tag
	JOIN tag_post ON tag_id = tag.id
	JOIN post ON tag_post.post_id = post.id
	WHERE tag.tag = 'mysql';      
 
分解为：      
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567); 

④、优化LIMIT分页

在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。

一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。

​ 优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

对于下面的查询：

​ select id,title from collect limit 90000,10;

该语句存在的最大问题在于limit M,N中偏移量M太大（我们暂不考虑筛选字段上要不要添加索引的影响），导致每次查询都要先从整个表中找到满足条件 的前M条记录，之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。 试想，如我们下一次的查询能从前一次查询结束后标记的位置开始查找，找到满足条件的100条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置 开始，这样就不必每次查询都先从整个表中先找到满足条件的前M条记录，舍弃，在从M+1开始再找到100条满足条件的记录了。

方法一：虑筛选字段（title）上加索引

​ title字段加索引 （此效率如何未加验证）

方法二：先查询出主键id值

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

原理：先查询出90000条数据对应的主键id的值，然后直接通过该id的值直接查询该id后面的数据。

方法三：关延迟联”

如果这个表非常大，那么这个查询可以改写成如下的方式：

Select news.id, news.description from news inner join (select id from news order by title limit 50000,5) as myNew using(id);

​ 这里的“关延迟联”将大大提升查询的效率，它让MySQL扫描尽可能少的页面，获取需要的记录后再根据关联列回原表查询需要的所有列。这个技术也可以用在优化关联查询中的limit。

十三、优化范围

MySQL 服务器硬件和操作系统调节

1. 拥有足够的物理内存来把整个InnoDB文件加载到内存中——在内存中访问文件时的速度要比在硬盘中访问时快的多.
2. 不惜一切代价避免使用Swap交换分区 – 交换时是从硬盘读取的，它的速度很慢.
3. 使用电池供电的RAM（注：RAM即随机存储器）.
4. 使用高级的RAID（注：Redundant Arrays of Inexpensive Disks，即磁盘阵列） – 最好是RAID10或更高.
5. 避免RAID5（注：一种存储性能、数据安全和存储成本兼顾的存储解决方案） – 确保数据库完整性的校验是要付出代价的.
6. 将操作系统和数据分区分开，不仅仅是逻辑上，还包括物理上 – 操作系统的读写操作会影响数据库的性能.
7. 把MySQL临时空间和复制日志与数据放到不同的分区 – 当数据库后台从磁盘进行读写操作时会影响数据库的性能.
8. 更多的磁盘空间等于更快的速度.
9. 更好更快的磁盘.
10. 使用SAS（注： Serial Attached SCSI，即串行连接SCSI）代替SATA（注：SATA，即串口硬盘）.
11. 较小的硬盘 比 较大的硬盘快，尤其是在RAID配置的情况下.
12. 使用电池支持的高速缓存RAID控制器.
13. 避免使用软件磁盘阵列.
14. 考虑为数据分区使用固态IO卡 (不是磁盘驱动器) – 这些卡能够为几乎任何数量的数据支持2GB/s的写入速度.
15. 在[Linux](http://www.ttlsa.com/linux/)中设置swappiness的值为0 – 在数据库服务器中没有理由缓存文件，这是一个服务器或台式机的优势.
16. 如果可以的话，使用 noatime 和 nodirtime 挂载文件系统 – 没有理由更新访问数据库文件的修改时间.
17. 使用 XFS 文件系统 – 一种比ext3更快、更小的文件系统，并且有许多日志选项， 而且ext3 已被证实与MySQL有双缓冲问题.
18. 调整 XFS 文件系统日志和缓冲变量 – 为了最高性能标准.
19. 在 Linux 系统中, 使用 NOOP 或者 DEADLINE IO 定时调度程序 – 同 NOOP 和 DEADLINE定时调度程序相比，这个 CFQ 和 ANTICIPATORY 定时调度程序 显得非常慢.
20. 使用64位的操作系统 – 对于MySQL，会有更大的内存支持和使用.
21. 删除服务器上未使用的安装包和守护进程 – 更少的资源占用.
22. 把使用MySQL的host和你的MySQL host放到一个hosts文件中 – 没有DNS查找.
23. 切勿强制杀死一个MySQL进程 – 你会损坏数据库和正在运行备份的程序.
24. 把服务器贡献给MySQL – 后台进程和其他服务能够缩短数据库占用CPU的时间.

MySQL 配置

25. 当写入时，使用 innodb_flush_method=O_DIRECT 来避免双缓冲.
26. 避免使用 O_DIRECT 和 EXT3 文件系统 – 你将序列化所有要写入的.
27. 分配足够的 innodb_buffer_pool_size 来加载整个 InnoDB 文件到内存中– 少从磁盘中读取.
28. 不要将 innodb_log_file_size 参数设置太大， 这样可以更快同时有更多的磁盘空间 – 丢掉多的日志通常是好的，在数据库崩溃后可以降低恢复数据库的时间.
29. 不要混用 innodb_thread_concurrency 和 thread_concurrency 参数– 这2个值是不兼容的.
30. 分配一个极小的数量给 max_connections 参数 – 太多的连接会用尽RAM并锁定MySQL服务.
31. 保持 thread_cache 在一个相对较高的数字，大约 16 – 防止打开连接时缓慢.
32. 使用skip-name-resolve参数 – 去掉 DNS 查找.
33. 如果你的查询都是重复的，并且数据不常常发生变化，那么可以使用查询缓存. 但是如果你的数据经常发生变化，那么使用查询缓存会让你感到失望.
34. 增大temp_table_size值，以防止写入磁盘
35. 增大max_heap_table_size值，以防止写入磁盘
36. 不要把sort_buffer_size值设置的太高，否则的话你的内存将会很快耗尽
37. 根据key_read_requests和key_reads值来决定key_buffer的大小，一般情况下key_read_requests应该比key_reads值高，否则你不能高效的使用key_buffer
38. 将innodb_flush_log_at_trx_commit设置为0将会提高性能，但是如果你要保持默认值（1）的话，那么你就要确保数据的完整性，同时你也要确保复制不会滞后.
39. 你要有一个测试环境，来测试你的配置，并且在不影响正常生产的情况下，可以常常进行重启.

MySQL模式优化

40. 保持你的数据库整理性.
41. 旧数据归档 - 删除多余的行返回或搜索查询.
42. 将您的数据加上索引.
43. 不要过度使用索引，比较与查询.
44. 压缩文字和BLOB数据类型 - 以节省空间和减少磁盘读取次数.
45. UTF 8和UTF16都低于latin1执行效率.
46. 有节制地使用触发器.
47. 冗余数据保持到最低限度 - 不重复不必要的数据.
48. 使用链接表，而不是扩展行.
49. 注意数据类型，在您的真实数据中，尽可能使用最小的一个.
50. 如果其他数据经常被用于查询时，而BLOB / TEXT数据不是，就把BLOB / TEXT数据从其他数据分离出来.
51. 检查和经常优化表.
52. 经常重写InnoDB表优化.
53. 有时，当添加列时删除索引，然后在添加回来索引，这样就会更快.
54. 针对不同的需求，使用不同的存储引擎.
55. 使用归档存储引擎日志表或审计表-这是更有效地写道.
56. 会话数据存储在缓存（[memcache](http://www.ttlsa.com/nosql/memcache/)）的而不是MySQL中 - 缓存允许自动自动填值的，并阻止您创建难以读取和写入到MySQL的时空数据.
57. 存储可变长度的字符串时使用VARCHAR而不是CHAR - 节省空间，因为固定长度的CHAR，而VARCHAR长度不固定（UTF8不受此影响）.
58. 逐步进行模式的变化 - 一个小的变化，可以有巨大的影响.
59. 在开发环境中测试所有模式，反映生产变化.
60. 不要随意更改你的配置文件中的值，它可以产生灾难性的影响.
61. 有时候，在MySQL的configs少即是多.
62. 有疑问时使用一个通用的MySQL配置文件.

查询优化

63. 使用慢查询日志去发现慢查询.
64. 使用执行计划去判断查询是否正常运行.
65. 总是去测试你的查询看看是否他们运行在最佳状态下 –久而久之性能总会变化.
66. 避免在整个表上使用count(*),它可能锁住整张表.
67. 使查询保持一致以便后续相似的查询可以使用查询缓存.
68. 在适当的情形下使用GROUP BY而不是DISTINCT.
69. 在WHERE, GROUP BY和ORDER BY子句中使用有索引的列.
70. 保持索引简单,不在多个索引中包含同一个列.
71. 有时候MySQL会使用错误的索引,对于这种情况使用USE INDEX.
72. 检查使用SQL_MODE=STRICT的问题.
73. 对于记录数小于5的索引字段，在UNION的时候使用LIMIT不是是用OR.
74. 为了 避免在更新前SELECT，使用INSERT ON DUPLICATE KEY或者INSERT IGNORE ,不要用UPDATE去实现.
75. 不要使用 MAX,使用索引字段和ORDER BY子句.
76. 避免使用ORDER BY RAND().
77. LIMIT M，N实际上可以减缓查询在某些情况下，有节制地使用.
78. 在WHERE子句中使用UNION代替子查询.
79. 对于UPDATES（更新），使用 SHARE MODE（共享模式），以防止独占锁.
80. 在重新启动的MySQL，记得来温暖你的数据库，以确保您的数据在内存和查询速度快.
81. 使用DROP TABLE，CREATE TABLE DELETE FROM从表中删除所有数据.
82. 最小化的数据在查询你需要的数据，使用*消耗大量的时间.
83. 考虑持久连接，而不是多个连接，以减少开销.
84. 基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询.
85. 当负载增加您的服务器上，使用SHOW PROCESSLIST查看慢的和有问题的查询.
86. 在开发环境中产生的镜像数据中 测试的所有可疑的查询.

MySQL 备份过程

87. 从二级复制服务器上进行备份.
88. 在进行备份期间停止复制，以避免在数据依赖和外键约束上出现不一致.
89. 彻底停止MySQL，从数据库文件进行备份.
90. 如果使用 MySQL dump进行备份，请同时备份二进制日志文件 – 确保复制没有中断.
91. 不要信任LVM 快照 – 这很可能产生数据不一致，将来会给你带来麻烦.
92. 为了更容易进行单表恢复，以表为单位导出数据 – 如果数据是与其他表隔离的.
93. 当使用mysqldump时请使用 –opt.
94. 在备份之前检查和优化表.
95. 为了更快的进行导入，在导入时临时禁用外键约束.
96. 为了更快的进行导入，在导入时临时禁用唯一性检测.
97. 在每一次备份后计算数据库，表以及索引的尺寸，以便更够监控数据尺寸的增长.
98. 通过自动调度脚本监控复制实例的错误和延迟.
99. 定期执行备份.
100. 定期测试你的备份.
101: 执行MySQL 监控: Monitis Unveils The World’s First Free On-demand MySQL Monitoring.