MySQL数据库总体知识架构_数据库有那些知识结构

一、关系型数据库设计理论

一些重要术语

• 属性（attribute）：列的名字，我们在开发中一般称为字段
• 依赖（relation）：字段之间存在的关系
• 元祖（tuple）：每一个行，如第二行 （1301，小明，13班，篮球，英语，赵英，70） 就是一个元组
• 外键
• 笛卡尔积（交叉链接Cross join）：A表每一行分别与B表每一行组合。
• 连接（theta join）：即加上约束条件的笛卡儿积，先得到笛卡儿积，然后根据约束条件删除不满足的元组。
• 内连接（inner join）：A表每一行与B表每一行进行匹配，如果得到有交叉部分则合并，若无交叉部分则舍弃。
• 外连接（outer join）：执行自然连接后，将舍弃的部分也加入，并且匹配失败处的属性用NULL代替。
• 记录（record）：我们把数据库中的一行称为记录

三大范式

高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

1、第一范式：字段（属性）不可再分

2、第二范式：每个非主键字段严格依赖主键字段

3、第三范式：每个非主键字段与主键字段不产生传递依赖，这里的意思是其他字段要直接与主键字段产生关联，不能更新某个非主键字段时，要牵扯到另外某个非主键字段
可能有些同学对第三范式

关系型数据库的设计流程

了解设计流程可以让我们更加专业的从零到有的设计数据库，而不是没有头绪>

• 需求分析：分析用户的需求，包括数据、功能和性能需求；

• 概念结构设计：主要采用E-R模型进行设计，包括画E-R图；

• 逻辑结构设计：通过将E-R图转换成表，实现从E-R模型到关系模型的转换；

• 数据库物理设计：主要是为所设计的数据库选择合适的存储结构和存取路径；

• 数据库的实施：包括编程、测试和试运行；

• 数据库运行与维护：系统的运行与数据库的日常维护

需求分析阶段（常用自顶向下）

进行数据库设计首先必须准确了解和分析用户需求（包括数据与处理）。需求分析是整个设计过程的基础，也是最困难，最耗时的一步。需求分析是否做得充分和准确，决定了在其上构建数据库大厦的速度与质量。需求分析做的不好，会导致整个数据库设计返工重做。

需求分析的任务，是通过详细调查现实世界要处理的对象，充分了解原系统工作概况，明确用户的各种需求，然后在此基础上确定新的系统功能，新系统还得充分考虑今后可能的扩充与改变，不仅仅能够按当前应用需求来设计。

调查的重点是，数据与处理。达到信息要求，处理要求，安全性和完整性要求。

分析方法常用SA(Structured Analysis) 结构化分析方法，SA方法从最上层的系统组织结构入手，采用自顶向下，逐层分解的方式分析系统。

数据流图表达了数据和处理过程的关系，在SA方法中，处理过程的处理逻辑常常借助判定表或判定树来描述。在处理功能逐步分解的同事，系统中的数据也逐级分解，形成若干层次的数据流图。系统中的数据则借助数据字典（data dictionary，DD）来描述。数据字典是系统中各类数据描述的集合，数据字典通常包括数据项，数据结构，数据流，数据存储，和处理过程5个阶段。

概念结构设计（常采用自底向上）

设计概念结构通常有四类方法：

• 自顶向下。即首先定义全局概念结构的框架，再逐步细化。
• 自底向上。即首先定义各局部应用的概念结构，然后再将他们集成起来，得到全局概念结构。
• 逐步扩张。首先定义最重要的核心概念结构，然后向外扩张，以滚雪球的方式逐步生成其他的概念结构，直至总体概念结构。
• 混合策略。即自顶向下和自底向上相结合。

二、数据库核心知识

事务

概念：事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

事务的四大特性：ACID

1、原子性（Atomicity)：事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

2、一致性（Consistency）：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的

3、隔离性（Isolation)：事务之间互不影响

4、持久性（Durability）：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

隔离级别

• 读未提交（read uncommitted ）：事务A未提交的数据，事务B可以读取到，B读取到的数据叫‘脏数据’/drity read；（最低级别隔离）

• 读已提交（read committed ）：事务A提交的数据，事务B才能读取到（隔离级别高于上），可避免‘脏数据，会导致不可重复读取这是oracle默认的隔离级别

• 可重复度（repeatable read ）：事务A提交的数据，事务B读取不到，事务B可重复读取已缓存的数据，达到可重复读取（例如银行月总账），会导致‘幻想读’ 这是MySQL默认的隔离级别

• 串行化（serializable ）：数据A在操作数据库表中数据时，事务B只能排队等待，这种隔离级别很少用，级别太高，但是吞吐量太低，用户体验不好意思是事务A和事务B是排队执行，不是并发

  离级别 脏读 不可重复读 幻影读
  未提交读 √ √ √
  提交读 × √ √
  可重复读 × × √
  可串行化 × × ×

多版本并发控制

多版本并发控制(Multi-Version Concurrency Control, MVCC)是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现

版本号

• 系统版本号: 是一个递增的数字，每开始一个新的事务，系统版本号就会自动递增。
• 事务版本号: 事务开始时的系统版本号。

实现过程

以下实现过程针对可重复读隔离级别。

当开始新一个事务时，该事务的版本号肯定会大于当前所有数据行快照的创建版本号，理解这一点很关键。

1、select

多个事务必须读取到同一个数据行的快照，并且这个快照是距离现在最近的一个有效快照。但是也有例外，如果有一个事务正在修改该数据行，那么它可以读取事务本身所做的修改，而不用和其它事务的读取结果一致。

把没有对一个数据行做修改的事务称为 T，T 所要读取的数据行快照的创建版本号必须小于 T 的版本号，因为如果大于或者等于 T 的版本号，那么表示该数据行快照是其它事务的最新修改，因此不能去读取它。

2、insert

将当前系统版本号作为数据行快照的创建版本号

3、delete

将当前系统版本号作为数据行快照的删除版本号。

4、update

将当前系统版本号作为更新前的数据行快照的删除版本号，并将当前系统版本号作为更新后的数据行快照的创建版本号。可以理解为先执行 DELETE 后执行 INSERT。

总结：反正就是版本号控制，有点像乐观锁，不加锁，实现并发操作，增删改查通过版本号和快照来操作。这个版本号其实就是系统版本号

快照读和当前读

1、快照读

使用 MVCC （多版本并发控制）读取的是快照中的数据，这样可以减少加锁所带来的开销。

2、当前读

读取的是最新的数据，需要加锁

比如：insert、update、delete等语句，需要对所在行加行锁，这时，读取到的是当前值

三、SQL语法

SQL 语句不区分大小写

标准的SQL语句的字符串都是单引号括起来，没有双引号

SQL语句以分号结尾

SQL语言的分类：

1）数据查询语言（DQL Data Query Language）代表关键字：selec

2)数据操纵语言（DML Data Manipulate Language）代表关键字：insert,delete,uptat

3)数据定义语言（DDL Data Definition Language）代表关键字：create（创建）,drop（删除）,alter（改动表结构

4)事务控制语言（TCL Transactional contral Language）代表关键字：commit,rollback

5)数据控制语言（DCL Data Contral Language）代表关键字：grant,revoke

操作表

CREATE TABLE tablenam (  
    columnname dataType(length),
    columnname dataType(length),
    columnname dataType(length),
    .........
     );
     
drop table if exists t_student;

ALTER TABLE mytableADD col CHAR(20);

ALTER TABLE t_student MODIFY tel VARCHAR(20);
1
2
3
4
5
6
7
8
9
10
11
12

其他操作

desc tableName   //查看表结构
show datebases  //展示当前数据库
show create table tableName  //查看表是怎么生成的
1
2
3

增删改查记录

insert into tableName (colume1,colume2...) values(value1,value2...)
delete from tableName where colume = vlaue
update tableName set sex="m" where id = 1
select age as 年龄 from tableName where id>1 group by salary having sex='f' order by id limit 3
1
2
3
4

四、MySQL知识点

1、常用数据类型

-VARCHAR （可变长度字符串）

-CHAR （定长字符串）

-INT （整型）

-BIGINT （长整型）

-FLOAT （浮点型单精度）

-DOUBLE （浮点型双精度） DOUBLE（7,2）表示有7位有效数字，保留两位小数；

-DATE （日期类型）在实际开发中，为了通用，通常用字符串来表示日期；

-BLOG （Binary Large Object 二进制大对象） 存图片，声音和视频；数据库中存放图片是很常见的，

但是一般不存储大容量的视频，一般是存储它的链接地址；

-CLOG （Character Large Object 字符大对象）存储比较的字符串，如4G+的字符串；

注意：CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

VARCHAR和CHAR的区别

例如 name Varchar(10) 输入jack数据，varchar会动态的分配内存，前提是不超过它指定的长度大小；

但是CHAR就是底层数据分配十个空间内存；VARCHAR能够节省硬盘的内存，但是效率低，而CHAR相反；

在实际中，有伸缩性的数据用VARCHAR

2、日期

关于MySQL中的日期处理：-在每一个数据库处理日期的时候，采用的机制是不一样的，日期处理都有自己的一套机制，所以在实际开发中，表中的字段定义为varchar类型

MySQL日期格式

%Y年 %m月 %d日

%H时 %i分 %s 秒

MySQL数据库管理系统中对日期的处理提供两种函数

1）str_to_date 将字符串（varchar）转化成日期类型（date）

2)date_format 将日期转为字符串

例如：str_to_date(‘10-11-1998’,‘%m-%d-%Y’)

在开发中常常需要进行日期范围内的统计，用到between and函数，这个函数是前闭后开！

3、约束

什么是约束？为什么使用约束？

-约束对应的英语单词为：constrain

-约束实际上就是表中数据的限制条件

-表在设计的时候加入约束的目的是为了保证表中的记录完整和有效

约束包括哪些 ？

• 非空约束 not null
• 唯一性约束 unique
• 主键约束 primary key
• 外键约束 foreign key
• 检查约束 （目前MySQL不支持，Qracle数据库支持）

非空约束

作用：not null约束的字段不能为NULL值，必须给定具体的数据

创建表，给字段添加非空约束

drop table if exists t_user;
     create table t_user(
         id int(10),
         name varchar(32) not null,
         email varchar(128)
     );
     insert into t_user(id,name,email) values(1,'jack','jack@baidu.com');
     mysql> select * from t_user;
     
+------+------+----------------+
 | id   | name | email          |
 +------+------+----------------+
 |    1 | jack | jack@baidu.com |
 +------+------+----------------+
mysql>  insert into t_user(id,email) values(2,'zhangsan@baidu.com');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

结果：ERROR 1364 (HY000): Field ‘name’ doesn’t have a default value

如果不给默认值，则为NULL

唯一性约束

作用：unique约束的字段具有唯一性

列级约束，演示创建对象，保证邮箱地址唯一

drop table if exists t_user;
     create table t_user(
         id int(10),
         name varchar(32) not null,
         email varchar(128) unique
     );
   insert into t_user(id,name,email) values(1,'jack','jack@baidu.com');
   insert into t_user(id,name,email) values(2,'lucy','jack@baidu.com');//邮箱一致
1
2
3
4
5
6
7
8

结果：ERROR 1062 (23000): Duplicate entry ‘jack@baidu.com’ for key ‘email’

表级约束

drop table if exists t_user;
     create table t_user(
         id int(10),
         name varchar(32) not null,
         email varchar(128)，
         unique(name,email)
     );
    insert into t_user(id,name,email) values(1,'jack','jack@baidu.com');
    insert into t_user(id,name,email) values(2,'lucy','lucy@baidu.com');
    insert into t_user(id,name,email) values(2,'lucy','jbj@baidu.com');
1
2
3
4
5
6
7
8
9
10

主键约束

作用：唯一性的标识记录

1、表中的某个字段添加主键约束之后，该字段被称为主键字段，主键字段中出现的每一个数据都被称为主键值

2、给某个字段添加主键约束之后，该字段不能重复，而且不能为空；效果和“not null unique”约束相同，但是本质不同，主键约束除了可以做到“not null nuique”

3、给一个字段添加主键约束，被称为单一主键，给多个字段添加主键约束，被称为复合主键；无论是单一主键还是复合主键，一张表主键约束只能有一个

外键约束

作用：术语和关系与主键约束雷同，不做讲述，区别在于一张表中可以有多个外键字段 ，用于关联表

外键值可以为NULL，外键字段去引用一张表的字段时，被引用的字段必须有unique约束

延展：级联更新和级联删除

添加级联删除和级联添加要在外键约束后面添加

在删除父表中数据时，级联删除子表中的数据 on delete cascade

在更新父表中数据时，级联更新子表中的数据 on update cascade

谨慎使用，因为级联操作会将数据改变或者删除（数据无价啊！）

4、常用函数和运算符

单行处理函数

1)select lower(字段名) as 新名字 from tablename; 将查询的结果转为小

2)select upper(字段名) as 新名字 from tablename; 将查询的结果转为大写

3）select round(rank(),2);将查询到的随机数四舍五入，取两位小数

4）select substr(字段名,1,2) from tablename;

5)select columnName from tablename where trim(’ lucy ');去空格键（好像没用，执行不了）

6)select ename from tablename where ename like’%v%';模糊查找（引申，不是函数）

7)select length(columnName) from tablename; 查询表中字段值的长度

8）ifnull（空值处理函数，很重要） select ifnull(name,0) from t_user; 意思为查询name字段的值，碰到有NULL，转为0

多行数据处理函数 （分组函数 ）

-取得记录数：count -求和：sum -取平均值：avg -取最大：max -取最小：min

-注意：这些函数自动忽略空值/不能直接使用在where关键字后面（还没分组呢！）

条件查询运算符

等于： = 不等于：<>或！= 小于、大于、小于等于、大于等于

两值之间：betwee…and… 注意是前闭后开

为空：is null 并且：and 或者：or 包含：in

取非:not 模糊查询：like（支持%和_）

怎么将查询结果去重？

用distinct关键字 例如：select distinct ColumnName from tablename

distinct关键字只能用在最前边修饰 select distinct ColumnName1,ColumnName2 from tablename;表示字段1和字段2联合起来没有相同的

分组查询

分组查询涉及到的两个子句： -group by -having

1)-order by:表示通过字段进行排序

         -group by:表示通过字段进行分组 

         -案例：找出每个工作岗位的最高薪水（先按照工作岗位分组，使用max函数求每组的最高薪水）

     代码：select max(sal) from tablename group by job;

         -注意：若一条DQL语句有group by子句，那么select关键字后面只能跟参与分组的字段和分组函数；       

         -案例：计算不同部门中不同工作岗位的最高薪水

     代码：select deptno,job,max(sal) from tablename group by deptno,job;意思为这两个字段完全一样时才看成一组；

        2）

         -having和where功能都是对数据过滤

         -where和having后面都是添加条件

         -having在group by之后完成过滤，where在group之前完成过滤

         -案例：找出每个工作岗位的平均薪水，要求显示平均薪水大于5677；

      代码：select job,avg(sal) from tablename group by job having avg(sal)>5677;

         -原则：尽量在where中完成过滤，无法过滤的数据，通常是需要先分组再过滤，这时候使用having。效率问题
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

一个完整的DQL语句的总结

select … from … where … group by … having … order by …

-以上关键字顺序不能变，严格遵守

 -执行顺序：from      从某张表中检索数据

            where     经过某条件进行过滤

            group by  然后分组

            having    分组之后不满意在过滤

            select    查询结果

            order by  排序输出          
1
2
3
4
5
6
7
8
9
10
11

子查询

select语句的嵌套

-案例：找出薪水比公司平均薪水高的员工，要求显示员工名和薪水

-解题第一步：找出公司的平均薪水  select avg(sal) from emp;

-解题第二步：找出薪水大于平均薪水的员工信息  select ename,sal from emp where sal>上面计算的值；

-合并：select ename,sal from emp where sal>(select avg(sal) from emp)；（实现了select的嵌套，子查询）
1
2
3
4
5

union:合并集合

例如： select ename,job from emp where job=‘engineer’;

       union

       select ename,enpno from emp where job='teacher';

   -要求字段的数量相同，在oracle中语法更加严格，要求数据类型也要相同
1
2
3
4
5

limit:-用来获取一张表中某部分数据

-只有在MySQKL数据库中存在，是MySQKL数据库的特色

      -尽量别用，不通用

      案例：找出员工表中的前五条记录：select ename from emp limit 5;

      -此代码中‘limit 5’中5表示从表中记录下标0开始，取5条，等价于：select ename from emp limit 0，5;

      -limit语法：limit 起始下标，长度 若没指定起始下标，默认从0 开始；
1
2
3
4
5
6
7

5、锁

MySQL大致可归纳为以下3种锁：

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

  行锁 表锁 页锁
  MyISAM √
  BDB √ √
  InnoDB √ √

MyISAM表锁

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。锁模式的兼容性如下表所示。 MySQL中的表锁兼容性

请求锁模式 是否兼容当前锁模式 None 读锁 写锁
读锁 是 是 否
写锁 是 否 否

可见，对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对 MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；MyISAM表的读操作与写操作之间，以及写操作之间是串行的！根据如下表所示的例子可以知道，当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。 MyISAM存储引擎的写阻塞读例子

session_1 session_2
获得表film_text的WRITE锁定 ：mysql> lock table film_text write;
当前session对锁定表的查询、更新、插入操作都可以执行： mysql> select film_id,title from film_text where film_id = 1001; 其他session对锁定表的查询被阻塞，需要等待锁被释放： mysql> select film_id,title from film_text where film_id = 1001;
等待 mysql> insert into film_text (film_id,title) values(1003,‘Test’);
mysql> update film_text set title = ‘Test’ where film_id = 1001;
释放锁： mysql> unlock tables; 等待
Session2获得锁 : mysql> select film_id,title from film_text where film_id = 1001;

如何加表锁 MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。在本书的示例中，显式加锁基本上都是为了方便而已，并非必须如此。 给MyISAM表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。例如，有一个订单表orders，其中记录有各订单的总金额total，同时还有一个订单明细表order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条SQL：

Select sum(total) from orders;
Select sum(subtotal) from order_detail;
1
2

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中，order_detail表可能已经发生了改变。因此，正确的方法应该是：

Lock tables orders read local, order_detail read local;

Select sum(total) from orders;

Select sum(subtotal) from order_detail;

Unlock tables;
1
2
3
4
5
6
7

要特别说明以下两点内容。 上面的例子在LOCK TABLES时加了“local”选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录，有关MyISAM表的并发插入问题，在后面的章节中还会进一步介绍。 在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因。 在如下表所示的例子中，一个session使用LOCK TABLE命令给表film_text加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误；同时，另外一个session可以查询表中的记录，但更新就会出现锁等待。注意，当使用LOCK TABLES时，不仅需要一次锁定用到的所有表，而且，同一个表在SQL语句中出现多少次，就要通过与SQL语句中相同的别名锁定多少次，否则也会出错！举例说明如下。 （1）对actor表获得读锁： mysql> lock table actor read; Query OK, 0 rows affected (0.00 sec) （2）但是通过别名访问会提示错误： mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = ‘Lisa’ and a.last_name = ‘Tom’ and a.last_name <> b.last_name; ERROR 1100 (HY000): Table ‘a’ was not locked with LOCK TABLES （3）需要对别名分别锁定： mysql> lock table actor as a read,actor as b read; Query OK, 0 rows affected (0.00 sec) （4）按照别名的查询可以正确执行： mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = ‘Lisa’ and a.last_name = ‘Tom’ and a.last_name <> b.last_name; ±-----------±----------±-----------±----------+ | first_name | last_name | first_name | last_name | ±-----------±----------±-----------±----------+ | Lisa | Tom | LISA | MONROE | ±-----------±----------±-----------±----------+ 1 row in set (0.00 sec)

并发插入（Concurrent Inserts）

上文提到过MyISAM表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。 MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。 当concurrent_insert设置为0时，不允许并发插入。 当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。 当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

MyISAM的锁调度 前面讲过，MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个 MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM 的调度行为。 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。 虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中，读锁等待严重的问题。 另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。 上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点：一些需要长时间运行的查询操作，也会使写进程“饿死”！因此，应用中应尽量避免出现长时间运行的查询操作，不要总想用一条SELECT语句来解决问题，因为这种看似巧妙的SQL语句，往往比较复杂，执行时间较长，在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”，使每一步查询都能在较短时间完成，从而减少锁冲突。如果复杂查询不可避免，应尽量安排在数据库空闲时段执行，比如一些定期统计可以安排在夜间执行。

InnoDB锁问题

InnoDB的行锁模式及加锁方法 InnoDB实现了以下两种类型的行锁。 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 share mode 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。 for update 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

请求锁模式 是否兼容当前锁模式 X IX S IS
X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 兼容
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。 意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。 共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE。 用SELECT … IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。

1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。 在如下所示的例子中，开始tab_no_index表没有索引： mysql> create table tab_no_index(id int,name varchar(10)) engine=innodb; Query OK, 0 rows affected (0.15 sec) mysql> insert into tab_no_index values(1,‘1’),(2,‘2’),(3,‘3’),(4,‘4’); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0

间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL： Select * from emp where empid > 100 for update; 是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。 InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。 很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。 还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！ 在如下表所示的例子中，假如emp表中只有101条记录，其empid的值分别是1,2,…,100,101。

恢复和复制的需要，对InnoDB锁机制的影响 MySQL通过BINLOG录执行成功的INSERT、UPDATE、DELETE等更新数据的SQL语句，并由此实现MySQL数据库的恢复和主从复制（可以参见本书“管理篇”的介绍）。MySQL的恢复机制（复制其实就是在Slave Mysql不断做基于BINLOG的恢复）有以下特点。 l 一是MySQL的恢复是SQL语句级的，也就是重新执行BINLOG中的SQL语句。这与Oracle数据库不同，Oracle是基于数据库文件块的。 l 二是MySQL的Binlog是按照事务提交的先后顺序记录的，恢复也是按这个顺序进行的。这点也与Oralce不同，Oracle是按照系统更新号（System Change Number，SCN）来恢复数据的，每个事务开始时，Oracle都会分配一个全局唯一的SCN，SCN的顺序与事务开始的时间顺序是一致的。 从上面两点可知，MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读，这已经超过了ISO/ANSI SQL92“可重复读”隔离级别的要求，实际上是要求事务要串行化。这也是许多情况下，InnoDB要用到间隙锁的原因，比如在用范围条件更新记录时，无论在Read Commited或是Repeatable Read隔离级别下，InnoDB都要使用间隙锁，但这并不是隔离级别要求的，有关InnoDB在不同隔离级别下加锁的差异在下一小节还会介绍。

什么时候使用表锁

对于InnoDB表，在绝大部分情况下都应该使用行级锁，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个别特殊事务中，也可以考虑使用表级锁。 第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。 第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。 当然，应用中这两种事务不能太多，否则，就应该考虑使用MyISAM表了。 在InnoDB下，使用表锁要注意以下两点。 （1）使用LOCK TABLES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。有关死锁，下一小节还会继续讨论。 （2）在用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁；事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句： 例如，如果需要写表t1并从表t读，可以按如下做： SET AUTOCOMMIT=0; LOCK TABLES t1 WRITE, t2 READ, …; [do something with tables t1 and t2 here]; COMMIT; UNLOCK TABLES;

关于死锁

上文讲过，MyISAM表锁是deadlock free的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁是可能的。如下所示的就是一个发生死锁的例子。

发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout来解决。需要说明的是，这个参数并不是只用来解决死锁问题，在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。

总结：

对于ＭyISAM的表锁，主要有以下几点

（１）共享读锁（S）之间是兼容的，但共享读锁（S）和排他写锁（X）之间，以及排他写锁之间（X）是互斥的，也就是说读和写是串行的。

（２）在一定条件下，ＭyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表和插入的锁争用问题。

（３）ＭyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIPORITY_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY选项来调节读写锁的争用。

（４）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，ＭyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

对于InnoDB表，主要有以下几点

（１）InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。

（２）InnoDB间隙锁机制，以及InnoDB使用间隙锁的原因。

（３）在不同的隔离级别下，InnoDB的锁机制和一致性读策略不同。

（４）ＭySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。

（５）锁冲突甚至死锁很难完全避免。

在了解InnoDB的锁特性后，用户可以通过设计和SQL调整等措施减少锁冲突和死锁

，包括：

• 尽量使用较低的隔离级别
• 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会。
• 选择合理的事务大小，小事务发生锁冲突的几率也更小。
• 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。
• 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁。
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

具体原文链接：1、MySQL中的锁（表锁、行锁） - 雪山飞猪 - 博客园 (cnblogs.com)

2、(10条消息) MySQL学习之——锁(行锁、表锁、页锁、乐观锁、悲观锁等)_一个手艺人-CSDN博客

6、索引

介绍

1、MySQL的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上；插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离；

2、它的架构大概分为四层：

连接层：该层引入了线程池的概念

服务层

引擎层（各种引擎，主流是MyLSAM和innoDB）

存储层（和硬件打交道）

配置文件

1、在my.cnf配置文件中datadir=/var/lib/mysql 记载的是MySQL的目录位置，四种数据库以及自定义的数据库等信息

2、my.cnf配置文件在/etc目录下

3、配置文件的备份

使用 systemctl status mysqld.service 可以查看MySQL的运行状态

进入mysql数据库，输入show global variables like ‘port’; 查看端口号

systemctl status firewalld  //查看防火墙状态
1

开放防火墙放行3306端口：firewall-cmd --zone=public --add-port=3306/tcp --permanent

重新加载防火墙：firewall-cmd --reload

4、在my.cnf中修改字符编码

若没有client,添加[client]

default-character-set=utf8

[mysqld]

character-set-server=utf8

default-storage-engine=INNODB

5、主要配置文件有：

二进制日志log-bin 主从复制

错误日志 log-error

查询日志 log默认关闭，记录查询的SQL语句，如果开启会降低MySQL的整体性能

数据文件 ，默认路径是 /var/lib/mysql，在他目录下的frm 文件是存放表结构，myd文件是存放表数据，

myi文件是存放表索引 （frame框架 data数据 index索引）

存储引擎

对比项 MyLSAM InnoDB
主外键 不支持 支持
事务 不支持 支持
行表锁 表锁，即使操作一条记录也会锁住整张表，不适合高并发 行锁，操作时只锁某一行，适合高并发
缓存 只缓存索引，不缓存真实数据 缓存索引和真实数据，对内存要求高
表空间 小 大
关注点 性能 事务
默认安装 是 是

索引优化分析

1、性能下降SQL慢，执行时间长，等待时间长

1）查询语句写的烂

2）索引失效

a、单值

b、复合

3）关联查询太多join（设计缺陷或不得己的需求）

4）服务器调优及各个参数设置（缓冲、线程数的等）

常见的通用join查询

1、SQL的执行顺序之手写和机读

select *

<select_list>

FROM

<left_table> <join_type>

JOIN <right_table> ON <join_condition>

WHERE

<where_condition>

GROUP BY

<group_by_list>

HAVING

<having_condition>

ORDER BY

<order_by_condition>

LIMIT

<limit_number>

机读
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>　
机读

7、join

左外连接 left join（两者公有，左边全部，右表不匹配的用null补全） eg: select <select_list> from tableA a left join tableB b on a.key=b.key

右外连接 right join eg: select <select_list> from tableA a right join tableB b on a.key=b.key

内连接 inner join (两者公有部分) eg: select <select_list> from tableA a inner join tableB b on a.key=b.key

全连接 full outer join eg: select <select_list> from tableA a full outer join tableB b on a.key=b.key

注意：MySQL不支持直接使用全连接full outrage join ，我们可以使用union来合并查询结果，union的作用是合并去重，但是要注意字段的数量相同

distinct也是去重

索引（Index）

1、MySQL中的索引的存储类型有两种：BTREE、HASH。 也就是用树或者Hash值来存储该字段，要知道其中详细是如何查找的，就需要会算法的知识了。我们现在只需要知道索引的作用，功能是什么就行。

2、MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换

3、介绍：目的是提高查询效率，拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

4、在删除时最好逻辑删除，不要真删了，一是其他部门可能要用到，二是BTREE结构可能被破坏，也就是索引被破坏，导致查询慢

5、优势：排序和查找变快，保证行的唯一性

6、缺点：

a、创建和维护索引需要成本

b、降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

7、什么时候使用索引？

a、 数据量大的，经常进行查询操作的表要建立索引。

b、用于排序的字段可以添加索引，用于分组的字段应当视情况看是否需要添加索引。

c、表与表连接用于多表联合查询的约束条件的字段（外键）应当建立索引。

8、索引分类

a、单值索引：一个索引只能包含一个列，一张表可以有多个单列索引

b、复合索引：在多个字段上建立索引，能够加速查询到速度 （只能按从左到右的顺序依次使用，最左前缀原则 ）

c、唯一索引：索引列的值必须唯一，但允许有空值

9、MySQL Optimizer是一个专门负责优化SELECT 语句的优化器模块，它主要的功能就是通过计算分析系统中收集的各种统计信息，为客户端请求的Query 给出他认为最优的执行计划，也就是他认为最优的数据检索方式。

EXPLAIN

1、介绍：使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。explain主要用于分析查询语句或表结构的性能瓶颈

2、使用：explain+SQL语句

3、作用：

①表的读取顺序。（对应id）

②select子句的类型。（对应select_type）

③哪些索引可以使用。（对应possible_keys）

④哪些索引被实际使用。（对应key）

⑤表直接的引用。（对应ref）

⑥每张表有多少行被优化器查询。（对应rows）

7、各种数据来自于哪种表。（对应table）

8、对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型” （对应type）

9、表示索引的长度，越短越好（key_len）

10.表示十分重要的额外信息(extra)

4、执行计划包含的信息：（我们只需要关注type、key和rows，其他了解即可）

±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+ | 1 | SIMPLE | sig | NULL | ALL | NULL | NULL | NULL |NULL | 7 | 100.00 | NULL | ±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±------+

a、id : select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，该字段通常与table字段搭配来分析。

三种情况：

1、ID相同：id相同，执行顺序从上到下，搭配table列进行观察可知，执行顺序为t1->t3->t2。

2、id不同，如果是子查询，id的序号会递增，id值越大执行优先级越高。 执行顺序为t3->t1->t2。

3、ID相同也有不同，id如果相同，可认为是同一组，执行顺序从上到下。在所有组中，id值越大执行优先级越高。 所以执行顺序为t3->derived2(衍生表，也可以说临时表)->t2。

b、select _type: 表示查询中每个select子句的类型

常见的五种情况：

(1) SIMPLE：简单的select查询，查询中不包含子查询或嵌套查询（union）

(2) PRIMARY (子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

(3) UNION (UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果，从union表获取结果的select)

(6) SUBQUERY (子查询中的第一个SELECT，结果不依赖于外部查询)

(7) DEPENDENT SUBQUERY (子查询中的第一个SELECT，依赖于外部查询)

(8) DERIVED (派生表(临时表)的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

c、type：对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。 （非常重要！要掌握）

访问类型排序：

从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL

ALL：全表扫描 eg：select * from employee where no=2001; 这里no字段没有索引

index：全索引扫描，index与ALL区别为index类型只遍历索引数，索引文件比数据文件小，且索引是加载到内存中，所以比ALL快 eg：select * from employee order by rec_id ; 这里rec_id是主键

range：有范围的索引扫描 ，相对于index的全索引扫描，它有范围限制，因此要优于index。 关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>‘,’<'外，in和or也是索引范围扫描。 eg：select * from employee where rec_id between 1 and 8; 这里rec_id是主键

ref：查询条件使用了不唯一的索引，也就是该索引列的值不唯一（不是主键或unique）；。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。 eg：select * from employee where name=‘张三’;

eq_ref：唯一性索引扫描 ，对于每一个索引键，表中只有一条记录与之匹配 eq：select * from employee e join score s where e.rec_id=s.rec_id; 这里rec_id是主键

const：将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量 ，也就是给主键赋了一个写死的常量 eg：select * from employee where rec_id=1;

system：最理想情况，了解即可

我们在工作中，得保证查询至少达到range级别，最好达到ref级别

查看表索引：show index from tablename;

删除索引：alter table table_name drop index index_name ;

增加普通和联合索引：alter table table_name add index index_name (column_list) ;

增加unique索引：alter table table_name add unique index_name(column_name)

增加主键索引：alter table table_name add primary index_name(colomn_name)

extra

1、Using filesort（需优化）：

1>在MySQL中的order by有两种排序实现方式：

第一种：利用有序索引获取有序数据（using index），表示待排序的内容由所使用的索引直接完成排序

第二种：文件排序（using filesort），表示待排序的内容不能由所使用的索引直接完成排序

eg： select * from employee where no=‘2003’ order by no; 这里no是索引

2、using temporary（需优化）：使用了临时表保存中间的结果，MySQL在对查询结果排序时使用临时表，常见的排序order by和分组查询group by，group by的字段尽量和复合字段的数量和顺序保持一致（可以理解为复合索引是我们创建好的梯子，我们要找到我们的东西，只需要搬动梯子即可，但是，当我们拆散我们的梯子，属下就得自己创建梯子，相当于自个创建文件内排序和中间表）

3、using index（好事） ：使用了覆盖索引（Covering index）：就是select的数据列只用从索引中就能够获取，不必读取数据行（我们创建了复合索引，然后我们查的时候只查复合索引指定的字段）

注意：使用覆盖索引，一定要注意select列表中只取出需要的列，不可用select *

五、索引优化

join语句的优化

1、尽可能的减少join语句中的Nestedloop的循环次数：“永远用小结果集驱动大结果集”；就是说比如两张表联合查询，一张书表（多），一张书类表（少），这时我们要用书类表作为驱动表（驱动表的意思就是全查All）

2、优先优化NestedLoop的内层循环（从鸡蛋黄到鸡蛋白到鸡蛋壳）

3、保证join语句中被驱动表上join条件字段已经被索引（全局匹配我最爱，最左前缀要遵守）

4、当无法保证被驱动表的join条件字段被索引且内存资源充足的条件下，不要太吝啬joinBuffer的设置

5、尽量使用覆盖索引（using index） ，少使用select

哪些场景会造成索引失效

1、应尽量避免在 where 子句中使用 != 或 <> 操作符

理解（我们建立索引，创建好了梯子，MySQL根据我们的梯子往上爬，一楼，二楼…但是如果有范围，到了指定楼层，MySQL只能文件内排序，自己再找，导致索引失效）

2、尽量避免在 where 子句中使用 or 来连接条件

3、对于多列索引，不是使用的第一部分，则不会使用索引；（带头大哥不能死，中间兄弟不能乱）

4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不会使用索引；（字符串里有引号）

5、like的模糊查询以 % 开头，索引失效；(百分like加右边)

特例：select * from test01 where c1=‘c1’ and c2 like ‘kk%’ and c3=‘c3’; 这里三个都用到了

select * from test01 where c1=‘c1’ and c2=‘c2’ and c3 like ‘%kk’ and c4=‘c4’ ;这里用到了两个

6、不要在索引列上做任何的操作（计算，函数等）（索引列上不计算）

7、如果MySQL估计使用全表扫描要比使用索引快，则不使用索引；

8、不适合键值较少的列（重复数据较多的列）

9、存储引擎不能使用索引中范围条件右边的列（范围之后全失效）

10、不要在索引列上使用 is null / is not null

优化口诀：

全局匹配我最爱，最左前缀要遵守。

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

索引列上不计算，范围之后全失效。

like百分写最右，覆盖索引不写星。

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

var引号不可丢，SQL高级也不难。

使用order by 和group by

order by中的排序字段一般情况下如果和复合字段的顺序不一致，会产生文件内排序，但是，特例是排序字段中有常量，eg： select * from test01 where c1=‘c1’ and c2=‘c2’ order by c3,c2; c2是一个常量了，实际上只有c3用于排序！ 排序字段要同升上或同降，eg：select * from tbla order by age asc,birth asc;

group by 是分组，记住：分组之前必排序！

in 和 exists的比较（原理是小结果集驱动大结果集）

一、in查询分析

SELECT * FROM A WHERE id IN (SELECT id FROM B);

执行过程：1、SELECT id FROM B ----->先执行in中的查询 2、SELECT * FROM A WHERE A.id = B.id

执行原理：SELECT id FROM B相当于驱动表，它只执行一次，结果放入内存，每次查询A表的ID与内存中的B表ID比较，如果存在则将A的查询数据加入到结果集中，直到遍历完A表中所有的结果集为止。 可以理解为B表示全表扫描，数据尽可能的少最好，然后A表使用了ID，是索引扫描，会很快。

结论：小结果集驱动大结果集

二、exists查询分析

SELECT * FROM a A WHERE EXISTS(SELECT 1 FROM b B WHERE B.id = A.id);

执行过程：1、SELECT * FROM A; 2、SELECT I FROM B WHERE B.id = A.id;

执行原理：SELECT * FROM A相当于驱动表，执行A.length次 ，EXISTS()查询返回一个布尔值true或flase ，根据验证结果是true或false来决定主查询数据结果是否得以保存。它会遍历A表， 它不用遍历B表，只需要查询B表即可，所以，B表数据比A表多是最好的

结论：小结果集驱动大结果集

慢查询日志

1、MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中相应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中，默认是10秒

2、默认MySQL数据库没有开启慢查询日志，需要手动设置参数，当然，如果不是调优需要，一般不开启该参数，会影响性能

3、查看开启命令： show variables like ‘%slow_query_log%’;

开启命令：set global slow_query_log=1;(只对当前数据库有效)

4、查看慢查询SQL：show global status like ‘%slow_queries%’;

my.cnf中的【mysqld】如下配置：

slow_query_log=1;

slow_query_log_file=/var/lib/mysql/slow.log

long_query_time=3;

log_output=FILE

5、在生产环境中，如果要手动分析日志，查找，分析SQL很难，MySQL提供了日志分析工具：mysqldumpslow

批量数据脚本（使用函数和存储过程）

1、设置参数log_bin_trust_function_creators ： set global log_bin_trust_function_creators=1;

2、创建函数，保证每条数据都不同

3、创建存储过程

4、执行函数

具体见；https://www.cnblogs.com/xujunkai/p/12496611.html

show profile

1、是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量

2、默认情况下，参数处于关闭，并保存最近15次的运行结果

3、查看打开：show variables like ‘profiling’; 打开命令：set profiling =on

4、查看结果：show profiles;

5、查看CPU、io等具体的消耗时间；show profile cpu,block io for query 1;

6、日常开发需要注意的结论：

converting HEAP to MyISAM：查询结果太大，内存不够用，往磁盘上搬

Creating tmp table：创建临时表

Coping to tmp tabel on disk：把内存中临时表复制到磁盘 ，危险！

locked

全局查询日志

1、注意：永远不要在生产环境中使用,开启会耗性能

2、开启命令：set global general_log=1; set golbal log_output=‘table’;

3、开启后你所编写的SQL语句，将会记录到MySQL库里的general_log 表

4、查看general_log表：select * from mysql.general_log;

MySQL锁机制

表锁（偏读）：偏向MyISAM存储引擎，开销小，加锁快，无死锁，发生锁冲突的概率高，并发度最低

1、查看所有表是否开启锁命令：show open tables; 2、开启锁命令：lock table TABLENAME read/write;

3、解锁命令：unlock tables;

主机被加锁表演示更新，删除，添加操作

mysql> update mylock set name='world' where id=1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
1
2

主机上被加锁表演示查操作

mysql> select * from mylock;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
+----+------+
1
2
3
4
5
6
7

主机上查看其它未加锁表操作：可以理解为烂摊子还没收拾好，不能查看其它的表

mysql> select * from tb_emp;
ERROR 1100 (HY000): Table 'tb_emp' was not locked with LOCK TABLES
1
2

从机在加锁表上添加操作：一执行就阻塞，只有当主机被锁表解锁，才能执行

mysql> insert into mylock(name) values('李四');
1

行锁（偏写）：

主表添加写锁，主表能够增删改查自己的被锁表；主表不能够查看其它未加锁表；

从机不能增删改查被锁表；从机能够增删改查其它未加锁表；

总结：读锁会阻塞写，但是不会堵塞读，而写锁会把读和写都堵塞

查看表锁统计情况命令：show status like ‘table%’;

myisam的读写锁调度是写优先，这也是myisam不适合做写为主的引擎。因为写锁后，其他线程不能做任何操作，大量更新会使查询很难得到锁，造成阻塞

个人对锁的总结：innodb引擎是行锁，myisam引擎是表锁，见名知意。

当我们使用innodb时，在开启事务的前提下，会开启行锁，你使用哪行，就会锁住哪行，其他的事务就不能使用这行来增删改查，如果使用，则事务进入阻塞，需要前者commit或者rollback后才能执行；

MySQL数据库默认的事务隔离级别是可重复读，事务A提交的数据，事务B读取不到，事务B可重复读取已缓存的数据，达到可重复读取（例如银行月总账），会导致‘幻想读’

知乎这么说：可重复读是指：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。

如何锁定一行？

1、begin

2、select * from tb_emp where id=1 for update;

3、commit

4、检查行锁状态： show status like ‘innodb_row_lock%’;

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |  当前正在等待锁定的数量
| Innodb_row_lock_time          | 0     |  从系统启动到现在锁定总时间长度
| Innodb_row_lock_time_avg      | 0     |  每次等待所花平均时间
| Innodb_row_lock_time_max      | 0     |  从系统启动到现在等待最长的时间
| Innodb_row_lock_waits         | 0     |  系统启动到现在总共等待的次数
+-------------------------------+-------+
1
2
3
4
5
6
7
8
9

主从复制

MySQL复制过程分为三步：

1、master将改变记录到二进制日志（binary log）。这些记录叫做二进制日志事件，binary log event

2、slave将master的binary log event拷贝到他的中继日志（relay log）

3.slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步且串行化的

来自知乎：

MySQL之间数据复制的基础是二进制日志文件（binary log file）。一台MySQL数据库一旦启用二进制日志后，其作为master，它的数据库中所有操作都会以“事件”的方式记录在二进制日志中，其他数据库作为slave通过一个I/O线程与主服务器保持通信，并监控master的二进制日志文件的变化，如果发现master二进制日志文件发生变化，则会把变化复制到自己的中继日志中，然后slave的一个SQL线程会把相关的“事件”执行到自己的数据库中，以此实现从数据库和主数据库的一致性，也就实现了主从复制。

复制的基本原则：

1、每个slave只有一个master

2、每个slave只能有一个唯一的服务器ID

3、每个master可以有多个slave

主从复制需要进行的配置如下：

主服务器：

开启二进制日志，window是my.ini，Linux是在my.cnf

配置唯一的server-id

[mysqld]
log-bin = D:/mysql57_data/Data/mysqlbin #开启二进制日志，注意是左斜杠
server-id=1 #设置server-id  
1
2
3

从服务器：

配置唯一的server-id

server-id=2  注意服务ID必须唯一
1

准备工作：

主服务器IP地址：192.168.1.12

从服务器IP地址：192.168.1.8

配置完后重启mysql(注意，这里的重启是指重启服务! )

主机创建用于同步的用户账号

mysql> CREATE USER '用户名'@'从机的IP地址' IDENTIFIED BY '密码';#创建用户
mysql> GRANT REPLICATION SLAVE ON *.* TO '用户名'@'密码';#分配权限
mysql>flush privileges;   #刷新权限
1
2
3

查看master状态，记录二进制文件名(mysql-bin.000001)和位置(154)

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysqlbin.000001 |      154 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1
2
3
4
5
6

从机执行同步SQL语句

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='主机IP地址',
    ->     MASTER_USER='用户名',
    ->     MASTER_PASSWORD='密码',
    ->     MASTER_LOG_FILE='mysqlbin.000001',  
    ->     MASTER_LOG_POS=154;
1
2
3
4
5
6

启动slave同步进程

mysql>start slave;
1

查看slave状态

mysql> show slave status\G;
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.12
                  Master_User: cheng
                  Master_Port: 3306
        ...
        Relay_Master_Log_File: mysqlbin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
        ...
1
2
3
4
5
6
7
8
9
10
11
12

这里主要观察Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes，都为yes则成功！

停止从机的复制功能：stop slave;

最后注意：每次连接，主机的日志文件都不一样，因为每次启动MySQL，都会生成一个新的日志文件