mysql知识：数据库设计,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。

选择合适的类型。
添加逻辑删除，创建，修改时间。
添加表，字段注释
主键使用 bigint (20)，主外键类型一致。
添加表，字段注释
添加索引

更多存储引擎介绍可见《存储引擎详解》。

设计范式

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

MySQL 索引

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

select min(field1) as minvalue from table1

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

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

mysql数据库设计,mysql存储引擎,mysql索引优化,mysql错误日志,mysql 复制

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 后接表明，附加需要完整的路径名

如何修改数据库的名称:
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 种日志文件：

错误日志 (error log)：记录 mysql 服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。
查询日志 (general log)：记录建立的客户端连接和执行的语句。
二进制日志 (bin log)：记录所有更改数据的语句，可用于数据复制。
慢查询日志 (slow log)：记录所有执行时间超过 long_query_time 的所有查询或不使用索引的查询。
中继日志 (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 刷盘时间由操作系统决定)：

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

数据修改写入 master 数据库的 binlog 中。
slave 的 IO 线程复制这些变动的 binlog 到自己的 relay log 中。
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 复制) 前，需要完成以下几个关键点，这几个关键点指导后续复制的所有步骤。

为 master 和 slave 设定不同的 server-id，这是主从复制结构中非常关键的标识号。到了 MySQL 5.7，似乎不设置 server id 就无法开启 binlog。设置 server id 需要重启 MySQL 实例。
开启 master 的 binlog。刚安装并初始化的 MySQL 默认未开启 binlog，建议手动设置 binlog 且为其设定文件名，否则默认以主机名为基名时修改主机名后会找不到日志文件。
最好设置 master 上的变量 sync_binlog=1(MySQL 5.7.7 之后默认为 1，之前的版本默认为 0)，这样每写一次二进制日志都将其刷新到磁盘，让 slave 服务器可以尽快地复制。防止万一 master 的二进制日志还在缓存中就宕机时，slave 无法复制这部分丢失的数据。
最好设置 master 上的 redo log 的刷盘变量 innodb_flush_log_at_trx_commit=1(默认值为 1)，这样每次提交事务都会立即将事务刷盘保证持久性和一致性。
在 slave 上开启中继日志 relay log。这个是默认开启的，同样建议手动设置其文件名。
建议在 master 上专门创建一个用于复制的用户，它只需要有复制权限 replication slave 用来读取 binlog。
确保 slave 上的数据和 master 上的数据在” 复制的起始 position 之前” 是完全一致的。如果 master 和 slave 上数据不一致，复制会失败。
记下 master 开始复制前 binlog 的 position，因为在 slave 连接 master 时需要指定从 master 的哪个 position 开始复制。
考虑是否将 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。

MySQL的学习资源:github,官方资料,书籍,大神博客,优秀公众号,学习网站,专栏,文章数据库事务总结

评论区（0）