总结 MySQL 相关知识点

记录一下mysql相关的知识点，方便以后查阅，持续更新

• 数据库设计
  • 设计范式
• 存储引擎
• 索引
  • MySQL中索引的优点和缺点和使用原则
  • 索引的分类
  • 索引优化
• mysql事务
  • 事务并发带来的问题
  • 事务的四种隔离级别
• sql语句
• mysql优化
• mysql日志
  • 错误日志
  • 一般查询日志
  • 慢查询日志
  • 二进制日志
• mysql备份与恢复
• mysql复制
  • 复制的基本概念和原理
  • 复制分类和特性

数据库设计

1、一般都使用 INNODB 存储引擎，除非读写比率<1%,才考虑使用 MYISAM 存储引擎;其他存储引擎请在 DBA 的建议下使用。
2、Stored procedure (包括存储过程，函数，触发器)对于 MYSQL 来说还不是很成熟，没有完善的出错记录处理，不建议使用。
3、UUID()，USER()这样的MySQL INSIDE函数对于复制来说是很危险的，会导致主备数据不一致，所以请不要使用。如果一定要使用UUID作为主键，让应用程序来产生。
4、请不要使用外键约束，如果数据存在外键关系，请在程序层面实现。
5、选择合适的字符集，无emoji使用utf8，有emoji使用utf8mb4。
6.选择合适的类型。
7.添加逻辑删除，创建，修改时间。
8.添加表，字段注释
9.主键使用bigint(20)，主外键类型一致。
10.添加表，字段注释
11.添加索引

设计范式

第一范式：要求有主键，并且要求每一个字段原子性不可再分
第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖
第三范式：所有非主键字段和主键字段之间不能产生传递依赖
反范式化：指的是通过增加冗余或重复的数据来提高数据库的读性能。

MySQL索引

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

MySQL中索引的优点和缺点和使用原则

优点：

　　　1、所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引

　　　2、大大加快数据的查询速度

缺点：

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

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

　　　3、当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

使用原则：

　　　通过上面说的优点和缺点，我们应该可以知道，并不是每个字段度设置索引就好，也不是索引越多越好，而是需要自己合理的使用。

　　　1、对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引，

　　　2、数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。

　　　3、在一同值少的列上(字段上)不要建立索引，比如在学生表的”性别”字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可以建立索引。

索引的分类

注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引

MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使BTREE，不能够更换。
MEMORY/HEAP存储引擎：支持HASH和BTREE索引。

索引我们分为四类来讲 单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、

1.单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了。

• 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

• 唯一索引：索引列中的值必须是唯一的，但是允许为空值，

• 主键索引：是一种特殊的唯一索引，不允许有空值。

2.组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。这个如果还不明白，等后面举例讲解时在细说

3.全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有”你是个靓仔，靓女 …” 通过靓仔，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。

4.空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

索引优化

1）如果MySQL估计使用索引比全表扫描还慢，则不会使用索引。
2）前导模糊查询不能命中索引。
前导模糊查询不能命中索引：
SELECT * FROM user WHERE name LIKE ‘%s%’;
3）数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来。
4）复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中符合索引。
5）union、in、or都能够命中索引，建议使用in。
6）用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。
7）负向条件查询不能使用索引，可以优化为in查询。负向条件有：!=、<>、not in、not exists、not like等。
8）范围条件查询可以命中索引。范围条件有：<、<=、>、>=、between等。
9）数据库执行计算不会命中索引。
10）利用覆盖索引进行查询，避免回表。
11）建立索引的列，不允许为null。

a. 更新十分频繁的字段上不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的。

b. 区分度不大的字段上不宜建立索引：类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以外的情况下，优化器不会选择使用索引。

c. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响insert速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生。

d. 多表关联时，要保证关联字段上一定有索引。

e. 创建索引时避免以下错误观念：索引越多越好，认为一个查询就需要建一个索引；宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度；抵制唯一索引，认为业务的唯一性一律需要在应用层通过“先查后插”方式解决；过早优化，在不了解系统的情况下就开始优化。

MySQL中的存储引擎

在MySQL 5.7版本中，MySQL支持的存储引擎有:

• InnoDB

• MyISAM

• Memory

• CSV

• Archive

• Blackhole

• Merge:

• Federated

• Example

InnoDB：支持事务操作(如 begin， commit，rollback命令)，支持行级锁，行级锁相对于表锁，其粒度更细，允许并发量更大，InnoDB存储引擎也是MySQL 5.7版本中默认的存储引擎。其缺点是：存储空间会占用比较大。

MyISAM：该存储引擎存储占用的空间相对与InnoDB存储引擎来说会少很多，但其支持的为表锁，其并发性能会低很多，而且不支持事务，通常只应用于只读模式的应用。它是MySQL最原始的存储引擎。

Memory：该存储引擎最大的特点是，所有数据均保存在内存中，之前还有个名字叫做 「Heap」。
应用场景: 主要存储一些需要快速访且非关键数据，为什么不是关键数据呢？就因为其所有数据保存在内存中，也可以理解为不安全。

CSV：首先先认识一下CSV，CSV文件其实就是用逗号分隔开的文本文件，常用于数据转换，该类型平时用的比较少，不支持索引。

Archive：存档文件，主要用于存储很少用到的引用文件，

Example：该存储引擎主要用于展示如何自行编写一个存储引擎，一般不会用作生产环境使用。

mysql事务

张三有1000块钱，李四也有1000块钱，张三给李四500,还剩下500，李四此时就有1500。我们想象着会执行下面的mysql语句：

update table user set money=500 where name = “张三”；

update table user set money=1500 where name = “李四”；

但是在计算机中可能会不一样。可能上面语句执行了下面的没有执行，因此为了保证两条语句要么都执行，要么都不执行，这时候就用到了事务。

事务的意思是一条或者是一组语句组成一个单元，这个单元要么全部执行，要么全不执行。

事务具有四个特性，也是面试常考的四个特性ACID：

• A（原子性Atomicity）：原子性指的是事务是一个不可分割的，要么都执行要么都不执行。

• C（一致性Consistency）：事务必须使得数据库从一个一致性状态，到另外一个一致性状态。

• I（隔离性Isolation）：指的是一个事务的执行，不能被其他的事务所干扰。

• D（持久性Durability）：持久性指的是一个事务一旦提交了之后，对数据库的改变就是永久的。

事务并发带来的问题

如果要提升系统的吞吐量，当有多个任务需要处理时，应当让多个事务同时执行，这就是事务的并发。既然事务存在并发执行，那必然产生同一个数据操作时的冲突问题

更新丢失（Lost Update），当两个事务更新同一行数据时，双方都不知道对方的存在，就有可能覆盖对方的修改。比如两个人同时编辑一个文档，最后一个改完的人总会覆盖掉前面那个人的改动。

脏读（Dirty Reads），一个事务在执行时修改了某条数据，另一个事务正好也读取了这条数据，并基于这条数据做了其他操作，因为前一个事务还没提交，如果基于修改后的数据进一步处理，就会产生无法挽回的损失。

不可重复读（Non-Repeatable Reads），同样是两个事务在操作同一数据，如果在事务开始时读了某数据，这时候另一个事务修改了这条数据，等事务再去读这条数据的时候发现已经变了，这就是没办法重复读一条数据。

幻读（Phantom Read），与上方场景相同，事务一开始按某个查询条件没查出任何数据，结果因为另一个事务的影响，再去查时却查到了数据，这种就像产生幻觉了一样，被称作幻读。

事务的四种隔离级别

读未提交 （Read uncommitted），读未提交其实就是事务没提交就可以读，很显然这种隔离级别会导致读到别的还没提交的数据，一旦基于读到的数据做了进一步处理，而另一个事务最终回滚了操作，那么数据就会错乱，而且很难追踪。总的来说说，读未提交级别会导致脏读。

读提交 （Read committed），顾名思义就是事务提交后才能读，假设你拿着银行卡去消费，付钱之前你看到卡里有2000元，这个时候你老婆在淘宝购物，赶在你前面完成了支付，这个时候你再支付的时候就提示余额不足，但是分明你看到卡里的钱是够的啊。这就是两个事务在执行时，事务A一开始读取了卡里有2000元，这个时候事务B把卡里的钱花完了，事务A最终再确认余额的时候发现卡里已经没有钱了。很显然，读提交能解决脏读问题，但是解决不了不可重复读。

Sql Server，Oracle的默认隔离级别是Read committed。

可重复读（ Repeatable read），看名字就看出来了，它的出现就是为了解决不可重复读问题，事务A一旦开始执行，无论事务B怎么改数据，事务A永远读到的就是它刚开始读的值。那么问题就来了，假设事务B把id为1的数据改成了2，事务A并不知道id发生了变化，当事务A新增数据的时候却发现为2的id已经存在了，这就是幻读。

MySQL的默认隔离级别就是Repeatable read。

串行化（ serializable），这个就是最无敌的存在了，所有的事务串起来一个个执行，因为没有并发的场景出现了，什么幻读、脏读、不可重复读统统都不存在的。但是同样的，基本并发能力会非常差。最终，到底什么隔离级别完全要根据自己的业务场景选择，没有最好的，只有最适合的。

1、事务隔离级别为读提交时，写数据只会锁住相应的行

2、事务隔离级别为可重复读时，如果检索条件有索引（包括主键索引）的时候，默认加锁方式是next-key 锁；如果检索条件没有索引，更新数据时会锁住整张表。一个间隙被事务加了锁，其他事务是不能在这个间隙插入记录的，这样可以防止幻读。****

3、事务隔离级别为串行化时，读写数据都会锁住整张表

4、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大

sql语句

内容转自SQL语句大全，所有的SQL都在这里

一、基础

1、创建数据库
CREATE DATABASE database-name

2、删除数据库
drop database dbname

3、备份sql server
— 创建 备份数据的 device
USE master
EXEC sp_addumpdevice ‘disk’, ‘testBack’, ‘c:mssql7backupMyNwind_1.dat’
— 开始 备份
BACKUP DATABASE pubs TO testBack

4、创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only

5、删除新表
drop table tabname

6、增加一个列
Alter table tabname add column col type注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。

7、添加主键： Alter table tabname add primary key(col)
删除主键： **Alter table tabname drop primary key(col)

8、创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。

9、创建视图：create view viewname as select statement
删除视图：drop view viewname

10、几个简单的基本的sql语句
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2)
删除：delete from table1 where 范围更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ —like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1

11、几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B：EXCEPT 运算符
EXCEPT运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C：INTERSECT 运算符
INTERSECT运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。

12、使用外连接
A、left （outer） join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right （outer） join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full/cross （outer） join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

12、分组:Group by:
一张表，一旦分组 完成后，查询后只能得到组相关的信息。
组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准)
在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据
在selecte统计函数中的字段，不能和普通的字段放在一起；

13、对数据库进行操作：
分离数据库： sp_detach_db;附加数据库：sp_attach_db 后接表明，附加需要完整的路径名

14.如何修改数据库的名称:
sp_renamedb ‘old_name’, ‘new_name’

mysql优化

内容转自MySQL数据库优化的八种方式(经典必看)

1、选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

2、使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

DELETE FROM customerinfo

WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo)

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）..替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT * FROM customerinfo

WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)

如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

SELECT * FROM customerinfo

LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID

WHERE salesinfo.CustomerID ISNULL

连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

3、使用联合(UNION)来代替手动创建的临时表

MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

SELECT Name,Phone FROM client UNION

SELECT Name,BirthDate FROM author UNION

SELECT Name,Supplier FROM product

4、事务

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

BEGIN; INSERT INTO salesinfo SET CustomerID=14; UPDATE inventory SET Quantity=11 WHERE item='book'; COMMIT;

事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5、锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';

...

UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES

这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。

6、使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。

CREATE    TABLE    customerinfo( CustomerIDINT    NOT    NULL,PRIMARYKEY(CustomerID))TYPE=INNODB;

CREATE    TABLE    salesinfo( SalesIDNT    NOT    NULL,CustomerIDINT    NOT    NULL,

PRIMARYKEY(CustomerID,SalesID),

FOREIGNKEY(CustomerID)    REFERENCES    customerinfo(CustomerID)    ON    DELETE    CASCADE)TYPE=INNODB;

注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB。如例中所示。

7、使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

那该对哪些字段建立索引呢？

一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况

例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

下面是应该注意的几个方面。

• 首先，最好是在相同类型的字段间进行比较的操作。

  在MySQL3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。

• 其次，在建有索引的字段上尽量不要使用函数进行操作。

例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。

• 第三，在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。

例如下面的查询将会比较表中的每一条记录。

SELECT    *    FROM    books

WHERE    name    like"MySQL%"

但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：

SELECT    *    FROM    books

WHERE    name＞="MySQL"    andname    ＜"MySQM"

最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

mysql日志

内容转自详细分析MySQL的日志

在MariaDB/MySQL中，主要有5种日志文件：
1.错误日志(error log)：记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。
2.查询日志(general log)：记录建立的客户端连接和执行的语句。
3.二进制日志(bin log)：记录所有更改数据的语句，可用于数据复制。
4.慢查询日志(slow log)：记录所有执行时间超过long_query_time的所有查询或不使用索引的查询。
5.中继日志(relay log)：主从复制时使用的日志。

日志刷新操作

以下操作会刷新日志文件，刷新日志文件时会关闭旧的日志文件并重新打开日志文件。对于有些日志类型，如二进制日志，刷新日志会滚动日志文件，而不仅仅是关闭并重新打开。

mysql> FLUSH LOGS;
shell> mysqladmin flush-logs
shell> mysqladmin refresh

错误日志

错误日志是最重要的日志之一，它记录了MariaDB/MySQL服务启动和停止正确和错误的信息，还记录了mysqld实例运行过程中发生的错误事件信息。

可以使用” –log-error=[file_name] “来指定mysqld记录的错误日志文件，如果没有指定file_name，则默认的错误日志文件为datadir目录下的 hostname.err ，hostname表示当前的主机名。

也可以在MariaDB/MySQL配置文件中的mysqld配置部分，使用log-error指定错误日志的路径。

如果不知道错误日志的位置，可以查看变量log_error来查看。

show variables like ‘log_error’;

在MySQL 5.5.7之前，刷新日志操作(如flush logs)会备份旧的错误日志(以_old结尾)，并创建一个新的错误日志文件并打开，在MySQL 5.5.7之后，执行刷新日志的操作时，错误日志会关闭并重新打开，如果错误日志不存在，则会先创建。

在MariaDB/MySQL正在运行状态下删除错误日志后，不会自动创建错误日志，只有在刷新日志的时候才会创建一个新的错误日志文件。

一般查询日志

查询日志分为一般查询日志和慢查询日志，它们是通过查询是否超出变量 long_query_time 指定时间的值来判定的。在超时时间内完成的查询是一般查询，可以将其记录到一般查询日志中，但是建议关闭这种日志（默认是关闭的），超出时间的查询是慢查询，可以将其记录到慢查询日志中。

使用” –general_log={0|1} “来决定是否启用一般查询日志，使用” –general_log_file=file_name “来指定查询日志的路径。不给定路径时默认的文件名以 hostname.log 命名。

和查询日志有关的变量有：

long_query_time = 10 ``# 指定慢查询超时时长，超出此时长的属于慢查询，会记录到慢查询日志中
log_output={TABLE|FILE|NONE} ``# 定义一般查询日志和慢查询日志的输出格式，不指定时默认为file

TABLE表示记录日志到表中，FILE表示记录日志到文件中，NONE表示不记录日志。只要这里指定为NONE，即使开启了一般查询日志和慢查询日志，也都不会有任何记录。

和一般查询日志相关的变量有：
general_log=off ``# 是否启用一般查询日志，为全局变量，必须在global上修改。sql_log_off=off # 在session级别控制是否启用一般查询日志，默认为off，即启用 `general_log_file=/mydata/data/hostname.log# 默认是库文件路径下主机名加上.log`

在MySQL 5.6以前的版本还有一个”log”变量也是决定是否开启一般查询日志的。在5.6版本开始已经废弃了该选项。

默认没有开启一般查询日志，也不建议开启一般查询日志。此处打开该类型的日志，看看是如何记录一般查询日志的

慢查询日志

查询超出变量 long_query_time 指定时间值的为慢查询。但是查询获取锁(包括锁等待)的时间不计入查询时间内。

mysql记录慢查询日志是在查询执行完毕且已经完全释放锁之后才记录的，因此慢查询日志记录的顺序和执行的SQL查询语句顺序可能会不一致(例如语句1先执行，查询速度慢，语句2后执行，但查询速度快，则语句2先记录)。

注意，MySQL 5.1之后就支持微秒级的慢查询超时时长，对于DBA来说，一个查询运行0.5秒和运行0.05秒是非常不同的，前者可能索引使用错误或者走了表扫描，后者可能索引使用正确。

另外，指定的慢查询超时时长表示的是超出这个时间的才算是慢查询，等于这个时间的不会记录

和慢查询有关的变量：

long_query_time=10 ``# 指定慢查询超时时长(默认10秒)，超出此时长的属于慢查询
log_output={TABLE|FILE|NONE} ``# 定义一般查询日志和慢查询日志的输出格式，默认为file
log_slow_queries={``yes``|no} ``# 是否启用慢查询日志，默认不启用
slow_query_log={1|ON|0|OFF} ``# 也是是否启用慢查询日志，此变量和log_slow_queries修改一个另一个同时变化``slow_query_log_file=``/mydata/data/hostname-slow``.log ``#默认路径为库文件目录下主机名加上-slow.log
log_queries_not_using_indexes=OFF ``# 查询没有使用索引的时候是否也记入慢查询日志

慢查询在SQL语句调优的时候非常有用，应该将它启用起来，且应该让慢查询阈值尽量小，例如1秒甚至低于1秒。就像一天执行上千次的1秒语句，和一天执行几次的20秒语句，显然更值得去优化这个1秒的语句。

二进制日志

二进制日志包含了引起或可能引起数据库改变(如delete语句但没有匹配行)的事件信息，但绝不会包括select和show这样的查询语句。语句以”事件”的形式保存，所以包含了时间、事件开始和结束位置等信息。

二进制日志是以事件形式记录的，不是事务日志(但可能是基于事务来记录二进制日志)，不代表它只记录innodb日志，myisam表也一样有二进制日志。

对于事务表的操作，二进制日志只在事务提交的时候一次性写入(基于事务的innodb二进制日志)，提交前的每个二进制日志记录都先cache，提交时写入**。对于非事务表的操作，每次执行完语句就直接写入。

MariaDB/MySQL默认没有启动二进制日志，要启用二进制日志使用 –log-bin=[on|off|file_name] 选项指定，如果没有给定file_name，则默认为datadir下的主机名加”-bin”，并在后面跟上一串数字表示日志序列号，如果给定的日志文件中包含了后缀(logname.suffix)将忽略后缀部分。

或者在配置文件中的[mysqld]部分设置log-bin也可以。注意：对于mysql 5.7，直接启动binlog可能会导致mysql服务启动失败，这时需要在配置文件中的mysqld为mysql实例分配server_id。

[mysqld]
# server_id=1234
log-bin=[on|filename]

mysqld还创建一个二进制日志索引文件，当二进制日志文件滚动的时候会向该文件中写入对应的信息。所以该文件包含所有使用的二进制日志文件的文件名。默认情况下该文件与二进制日志文件的文件名相同，扩展名为’.index’。要指定该文件的文件名使用 –log-bin-index[=file_name] 选项。当mysqld在运行时不应手动编辑该文件，免得mysqld变得混乱。

当重启mysql服务或刷新日志或者达到日志最大值时，将滚动二进制日志文件，滚动日志时只修改日志文件名的数字序列部分。

二进制日志文件的最大值通过变量 max_binlog_size 设置(默认值为1G)。但由于二进制日志可能是基于事务来记录的(如innodb表类型)，而事务是绝对不可能也不应该跨文件记录的，如果正好二进制日志文件达到了最大值但事务还没有提交则不会滚动日志，而是继续增大日志，所以 max_binlog_size 指定的值和实际的二进制日志大小不一定相等。

因为二进制日志文件增长迅速，但官方说明因此而损耗的性能小于1%，且二进制目的是为了恢复定点数据库和主从复制，所以出于安全和功能考虑，极不建议将二进制日志和datadir放在同一磁盘上。

mysql备份与恢复

内容转自MariaDB/MySQL备份和恢复

mysql复制

内容转自深入MySQL复制

复制的基本概念和原理

mysql复制是指从一个mysql服务器(MASTER)将数据通过日志的方式经过网络传送到另一台或多台mysql服务器(SLAVE)，然后在slave上重放(replay或redo)传送过来的日志，以达到和master数据同步的目的。

它的工作原理很简单。首先确保master数据库上开启了二进制日志，这是复制的前提。

• 在slave准备开始复制时，首先要执行change master to语句设置连接到master服务器的连接参数，在执行该语句的时候要提供一些信息，包括如何连接和要从哪复制binlog，这些信息在连接的时候会记录到slave的datadir下的master.info文件中，以后再连接master的时候将不用再提供这新信息而是直接读取该文件进行连接。

在slave上有两种线程，分别是IO线程和SQL线程。

• IO线程用于连接master，监控和接受master的binlog。当启动IO线程成功连接master时，master会同时启动一个dump线程，该线程将slave请求要复制的binlog给dump出来，之后IO线程负责监控并接收master上dump出来的二进制日志，当master上binlog有变化的时候，IO线程就将其复制过来并写入到自己的中继日志(relay log)文件中。
• slave上的另一个线程SQL线程用于监控、读取并重放relay log中的日志，将数据写入到自己的数据库中。如下图所示。

站在slave的角度上看，过程如下：

站在master的角度上看，过程如下(默认的异步复制模式，前提是设置了sync_binlog=1，否则binlog刷盘时间由操作系统决定)：

所以，可以认为复制大致有三个步骤：

1. 数据修改写入master数据库的binlog中。
2. slave的IO线程复制这些变动的binlog到自己的relay log中。
3. slave的SQL线程读取并重新应用relay log到自己的数据库上，让其和master数据库保持一致。

从复制的机制上可以知道，在复制进行前，slave上必须具有master上部分完整内容作为复制基准数据。例如，master上有数据库A，二进制日志已经写到了pos1位置，那么在复制进行前，slave上必须要有数据库A，且如果要从pos1位置开始复制的话，还必须有和master上pos1之前完全一致的数据。如果不满足这样的一致性条件，那么在replay中继日志的时候将不知道如何进行应用而导致数据混乱。也就是说，复制是基于binlog的position进行的，复制之前必须保证position一致。(注：这是传统的复制方式所要求的)

可以选择对哪些数据库甚至数据库中的哪些表进行复制。默认情况下，MySQL的复制是异步的。slave可以不用一直连着master，即使中间断开了也能从断开的position处继续进行复制。

MySQL 5.6对比MySQL 5.5在复制上进行了很大的改进，主要包括支持GTID(Global Transaction ID,全局事务ID)复制和多SQL线程并行重放。GTID的复制方式和传统的复制方式不一样，通过全局事务ID，它不要求复制前slave有基准数据，也不要求binlog的position一致。

MySQL 5.7.17则提出了组复制(MySQL Group Replication,MGR)的概念。像数据库这样的产品，必须要尽可能完美地设计一致性问题，特别是在集群、分布式环境下。Galera就是一个MySQL集群产品，它支持多主模型(多个master)，但是当MySQL 5.7.17引入了MGR功能后，Galera的优势不再明显，甚至MGR可以取而代之。MGR为MySQL集群中多主复制的很多问题提供了很好的方案，可谓是一项革命性的功能。

复制的好处

围绕下面的拓扑图来分析：

主要有以下几点好处：

1.提供了读写分离的能力。

replication让所有的slave都和master保持数据一致，因此外界客户端可以从各个slave中读取数据，而写数据则从master上操作。也就是实现了读写分离。

需要注意的是，为了保证数据一致性，写操作必须在master上进行。

通常说到读写分离这个词，立刻就能意识到它会分散压力、提高性能。

2.为MySQL服务器提供了良好的伸缩(scale-out)能力。

由于各个slave服务器上只提供数据检索而没有写操作，因此”随意地”增加slave服务器数量来提升整个MySQL群的性能，而不会对当前业务产生任何影响。

之所以”随意地”要加上双引号，是因为每个slave都要和master建立连接，传输数据。如果slave数量巨多，master的压力就会增大，网络带宽的压力也会增大。

3.数据库备份时，对业务影响降到最低。

由于MySQL服务器群中所有数据都是一致的(至少几乎是一致的)，所以在需要备份数据库的时候可以任意停止某一台slave的复制功能(甚至停止整个mysql服务)，然后从这台主机上进行备份，这样几乎不会影响整个业务(除非只有一台slave，但既然只有一台slave，说明业务压力并不大，短期内将这个压力分配给master也不会有什么影响)。

4.能提升数据的安全性。

这是显然的，任意一台mysql服务器断开，都不会丢失数据。即使是master宕机，也只是丢失了那部分还没有传送的数据(异步复制时才会丢失这部分数据)。

5.数据分析不再影响业务。

需要进行数据分析的时候，直接划分一台或多台slave出来专门用于数据分析。这样OLTP和OLAP可以共存，且几乎不会影响业务处理性能。

复制分类和特性

MySQL支持两种不同的复制方法：传统的复制方式和GTID复制。MySQL 5.7.17之后还支持组复制(MGR)。

• (1).传统的复制方法要求复制之前，slave上必须有基准数据，且binlog的position一致。
• (2).GTID复制方法不要求基准数据和binlog的position一致性。GTID复制时，master上只要一提交，就会立即应用到slave上。这极大地简化了复制的复杂性，且更好地保证master上和各slave上的数据一致性。

从数据同步方式的角度考虑，MySQL支持4种不同的同步方式：同步(synchronous)、半同步(semisynchronous)、异步(asynchronous)、延迟(delayed)。所以对于复制来说，就分为同步复制、半同步复制、异步复制和延迟复制。

同步复制

客户端发送DDL/DML语句给master，master执行完毕后还需要等待所有的slave都写完了relay log才认为此次DDL/DML成功，然后才会返回成功信息给客户端。同步复制的问题是master必须等待，所以延迟较大，在MySQL中不使用这种复制方式。

例如上图中描述的，只有3个slave全都写完relay log并返回ACK给master后，master才会判断此次DDL/DML成功。

半同步复制

客户端发送DDL/DML语句给master，master执行完毕后还要等待一个slave写完relay log并返回确认信息给master，master才认为此次DDL/DML语句是成功的，然后才会发送成功信息给客户端。半同步复制只需等待一个slave的回应，且等待的超时时间可以设置，超时后会自动降级为异步复制，所以在局域网内(网络延迟很小)使用半同步复制是可行的。

例如上图中，只有第一个slave返回成功，master就判断此次DDL/DML成功，其他的slave无论复制进行到哪一个阶段都无关紧要。

异步复制

客户端发送DDL/DML语句给master，master执行完毕立即返回成功信息给客户端，而不管slave是否已经开始复制。这样的复制方式导致的问题是，当master写完了binlog，而slave还没有开始复制或者复制还没完成时，slave上和master上的数据暂时不一致，且此时master突然宕机，slave将会丢失一部分数据。如果此时把slave提升为新的master，那么整个数据库就永久丢失这部分数据。

延迟复制

顾名思义，延迟复制就是故意让slave延迟一段时间再从master上进行复制。

配置一主一从

mysql支持一主一从和一主多从。但是每个slave必须只能是一个master的从，否则从多个master接受二进制日志后重放将会导致数据混乱的问题。

以下是一主一从的结构图：

在开始传统的复制(非GTID复制)前，需要完成以下几个关键点，这几个关键点指导后续复制的所有步骤。

1. 为master和slave设定不同的server-id，这是主从复制结构中非常关键的标识号。到了MySQL 5.7，似乎不设置server id就无法开启binlog。设置server id需要重启MySQL实例。
2. 开启master的binlog。刚安装并初始化的MySQL默认未开启binlog，建议手动设置binlog且为其设定文件名，否则默认以主机名为基名时修改主机名后会找不到日志文件。
3. 最好设置master上的变量sync_binlog=1(MySQL 5.7.7之后默认为1，之前的版本默认为0)，这样每写一次二进制日志都将其刷新到磁盘，让slave服务器可以尽快地复制。防止万一master的二进制日志还在缓存中就宕机时，slave无法复制这部分丢失的数据。
4. 最好设置master上的redo log的刷盘变量innodb_flush_log_at_trx_commit=1(默认值为1)，这样每次提交事务都会立即将事务刷盘保证持久性和一致性。
5. 在slave上开启中继日志relay log。这个是默认开启的，同样建议手动设置其文件名。
6. 建议在master上专门创建一个用于复制的用户，它只需要有复制权限replication slave用来读取binlog。
7. 确保slave上的数据和master上的数据在”复制的起始position之前”是完全一致的。如果master和slave上数据不一致，复制会失败。
8. 记下master开始复制前binlog的position，因为在slave连接master时需要指定从master的哪个position开始复制。
9. 考虑是否将slave设置为只读，也就是开启read_only选项。这种情况下，除了具有super权限(mysql 5.7.16还提供了super_read_only禁止super的写操作)和SQL线程能写数据库，其他用户都不能进行写操作。这种禁写对于slave来说，绝大多数场景都非常适合。

一主多从

一主多从有两种情况，结构图如下。

以下是一主多从的结构图(和一主一从的配置方法完全一致)：

以下是一主多从，但某slave是另一群MySQL实例的master：

配置一主多从时，需要考虑一件事：slave上是否要开启binlog? 如果不开启slave的binlog，性能肯定要稍微好一点。但是开启了binlog后，可以通过slave来备份数据，也可以在master宕机时直接将slave切换为新的master。此外，如果是上面第二种主从结构，这台slave必须开启binlog。可以将某台或某几台slave开启binlog，并在mysql动静分离的路由算法上稍微减少一点到这些slave上的访问权重。

上面第一种一主多从的结构没什么可解释的，它和一主一从的配置方式完全一样，但是可以考虑另一种情况：向现有主从结构中添加新的slave。

本作品采用《CC 协议》，转载必须注明作者和本文链接