MySQL——学习经验

MySQL

各位读者朋友你们好，我是你们的好朋友IT黑铁，最近巩固加深MySQL中将经验记录了下来，其中若有错误请多指教！

学习途径：

《数据库系统原理教程》

B站黑马程序员的MySQL视频

注：由于部分ppt图片过于的好和部分知识基本不需要扩展，我就直接截取了黑马程序员的ppt图片，万分感谢！

概述

数据库发展简述

数据库技术是应数据管理任务的需要而产生的。数据管理是指如何对数据进行分类、组织、编码、储存、检索和维护，它是数据处理的中心问题。随着计算机硬件和软件的发展，数据管理经历了人工管理、文件系统和数据库系统三个阶段。

扼要的说明一下三者区别，人工管理阶段受限于没有操作系统、没有存储设备、数据不具备结构化、与应用程序高度耦合。数据库系统比较于文件系统，更加专注于管理数据（共享性，冗余度，安全性，完整性，并发控制和恢复能力），整体结构化（而文件系统只是记录内有结构）（组织性，数据之间的联系）。

数据库理念简述

为了将数据组织起来，也就是结构化。将一个数据库的建立核心，分作两种模型，用户角度的概念模型，计算机实现的数据模型。

概念模型使用非专业的实体-联系方法E-R图来表示模型抽象。数据模型的种类则多种多样，但依据其特征分为关系型数据库和非关系型数据库。非关系型数据库简略叙述，常见的层次数据模型以树形为其脉络（树形不能表示多对多，只能通过分解多对多的办法，冗余结点和虚拟结点两种办法）、网状数据模型以图为脉络（复杂的数据模型带来的麻烦是建立结构十分困难。并且实体间联系由存储路径指示，所以独立性较差）。关系型数据模型则以数学概念的集合为脉络（规范的概念。隐藏了存储细节，所以性能依赖于数据库管理系统）。

数据库管理系统为了组织好数据的物理存储、逻辑建立和展示，让他们有高的逻辑独立性和物理独立性，使用的是三级模式和二级映像，外模式（用户模式或子模式）、模式（逻辑模式）、内模式（存储模式），外模式/模式映像、模式/内模式映像。一个数据库只有一个模式，也只有一个内模式，所以只有一个模式/内模式映像。为了不同的数据展示，则有多个外模式，多个外模式/模式映像。其中外模式起到了简单（简化操作）、安全（数据库可以授权，但不能授权到特定的行和列）、数据独立（屏蔽真实表带来的影响）的作用。

具体的数据库组织和操作，在关系模型中，实体以及实体间的联系都是用关系来表示。关系的完整性规则是对关系的某种约束条件，具体有实体完整性、参照完整性、用户自定义完整性。关系数据语言，分为三类，关系代数语言、关系演算语言、兼备两种语言特点的语言（SQL）。简略的提一下在关系代数运算中，并、差、笛卡尔积、投影和选择5中运算为基本的运算，其他3种运算，即交、连接和除军科院用5种基本运算表达。

逻辑模式设计理论，参照范式定义，注意的是到BCNF时，在函数依赖范畴内，已经达到了最高的规范化程度，消除了插入异常和删除的异常。对关系模式的规范化过程是通过对关系模式的分解来实现的，具体不再叙述。所以过多的追求范式，会使得关系表过多，对查询数据的复杂度和多表连接导致的性能下降是值得注意的，所以这就叫做反范式。

数据库数学术语

域是一组具有相同数据类型的值的集合

笛卡尔积为多个域的分量组合

码是能够唯一标识一个元组的属性组

候选码是能够唯一标识一个元组的属性组，并且其真子集不需。候选码的诸属性称为主属性，不在任何候选码中的属性称为非码属性。

主码是从多个候选码中选定的一个。

关系的描述称为关系模式àR(U,D,DOM,F)。R为关系名，U为组成该关系的属性名集合，D为属性组U中属性所来自的域，DOM为属性向域的映像集合，F为属性间数据的依赖关系集合。

实体完整性：若属性A是基本关系R的主属性，则属性A不能取空值。

参照完整性：设F是基本关系R的一个或一组属性，但不是关系R的码，如果F与基本关系S的主码Ks相对应，则称F是基本关系R的外码，并称基本关系R为参照关系，基本关系S为被参照关系或目标关系。R和S一定不同。对于外码只能取空值，或者等于其主表的主码值。

用户自定义完整性：根据不同数据库系统不同。

选择又称为限制，选择在关系表中满足给定条件的元组。

投影是从关系表中选择出若干属性列，组成新的关系。

连接是从两个关系的笛卡尔积中选取属性间满足一定条件的元组。

等值连接是从不同关系中选取属性值相等的元组。

自然连接是特殊的等值连接，要求属性值相等且是相同的字段，最后还要对重复的元组去重。

象集，一个属性组的值（或单个属性的值）的象集是诸元组中与该属性组的值相同的元组的其余属性的组的集合。

除是，被除数关系表在被除数关系表和除数基本表的相同属性组（或属性）外的属性组（或属性）的值的象集，包含除数基本表在相同属性组的投影值，商即为非相同属性组的值。

函数依赖（functionnal dependency，FD）

平凡依赖

非平凡依赖

完全函数依赖

部分函数依赖

传递函数依赖

多值依赖

连接依赖

第一范式（1NF）：非主属性函数依赖于码。

第二范式（2NF）：上一基础上，非主属性完全依赖于码。

第三范式（3NF）：上一基础上，非主属性既不部分依赖于码也不传递依赖于码。

BC范式（BC范式）：上一基础上，所有属性都不部分或传递依赖于码，所有决定属性集都包含码。

第四范式（4NF）：上一基础上，所有非平凡的多值依赖都是函数依赖。

第五范式（5NF）：连接依赖均由候选码所蕴含。

数据访问并发控制

事务的4个属性：原子性、一致性、隔离性、持续性。

原子性：一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。

一致性：事务必须是使数据库从一个一致性状态变到另一个一致性状态。

隔离性：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持续性（永久性）：一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

并发带来的三种数据不一致：丢失修改、不可重复读和读“脏”数据。

丢失修改：当前事务要对数据进行修改（先读取其值后写回修改后的值，不是直接对值进行修改），其他事务也对该数据进行修改。事务若是读取到的相同值先后执行了自己的写回操作，前一个事务的修改被后一个事务覆盖，这就是丢失修改。

不可重复读：包括三种情况。第一种是：一个事务前一次读取了数据后，该数据被其他事务修改，后一次的读取数据跟前一次不一致，叫做不可重复读。第二种和第三种是：一个事务前一次按条件读取数据时，而该表被其他事务增加数据或删除数据，导致发生了不可重复读。这三种情况都是因为数据发生了改变，但有时将另外两种称为幻影（幻行）的现象。

脏读：指前一个事务还没提交，另一个事务读到了其事务中执行的修改过的数据，而前一个事务若发生回滚，则此时数据就不一致，这就是脏读。

DBMS普遍采用封锁方法来保证并发调度的正确性，即保证并行操作的可串行性。除此之外，还有其他方法如时标方法、乐观方法。

一个事务对某个数据对象枷锁后究竟拥有什么样的控制是由其封锁的类型决定的。基本封锁类型有两种：排它锁（exclusive lock，X锁，写锁），共享锁（share lock，S锁，读锁）。一个事务对一个数据对象加了排它锁，其他事务就不能再加任何锁。一个事务对一个数据对象加了共享锁，其他事务还能对其加共享锁，不能加排它锁。

封锁对象的大小称为封锁粒度，封锁粒度越大，系统中能够被封锁的对象就越少，并发度就越小，系统开销也越小。反之，封锁粒度越小，并发度越高，系统开销越大。

为了避免三种数据不一致问题和对申请锁的隐藏，引入了三级封锁协议。1级封锁协议解决了丢失修改问题，一个事务要执行对数据对象的修改则事务开启时加X锁，事务结束后才释放。2级封锁协议解决了读脏数据的问题，一个事务要执行对数据对象的读取时，必须先在事务开启时加S锁，读完后就释放。3级封锁协议及绝了不可重复读的问题，一个事务要执行对数据对象的读取时，必须先在事务开启时加S锁，整个事务结束才释放。除此之外还有严苛的两段锁协议，其保证了可串行化，但并发就差了。

死锁和活锁。简而言之活锁指事务饥饿，死锁指打了个死结循环等待。解决死锁问题主要分预防和诊断系统直接解锁两种。

一、语法

通用语法

1->SQL语句可以单行或多行书写，以分号结尾。

2->MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

3->注释：单行注释(--或#，MySQL特有)，多行注释：/* */

4->大部分命令都可以添加if exsits 、if not exsits、comment 备注、

（一） DDL（Data Definition Language）

关于数据库

SHOW databases ---查询所有数据库

SELECT database() ---查询当前数据库

CREATE database 数据库名 [DEFAULT CHARSET 字符集][COLLATE 排序规则] ---创建数据库

DROP database 数据库名 ---删除数据库

USE 数据库名 ---使用数据库

SELECT @@TRANSCATION_ISOLATION—查看事务隔离级别

SET [SESSION|GLOBAL] TRANSCACTION ISOLATION LEVEL 隔离级别名称

SHOW GLOBAL STATUS LIKE ‘Com_______’;

delimiter 结束符 ---更改结束符

ibd2 表空间文件名---查看ibd表空间文件

关于表

SHOW tables ---查询数据库中所有的表

SHOW engines ---查询支持的存储引擎

DESC 表名 --- 描述展示表的结构

SHOW CREATE table 表名 ---展示建表语句

CREATE table 表名{

字段名字段类型,#最后一个字段不要分号

}engine=存储引擎名; ---创建表结构

ALTER table 表名 ADD 字段名类型(长度) [约束] ---添加字段

ALTER table 表名 MODIF 字段名新数据类型(长度)---修改数据类型

ALTER table 表名 CHANGE 旧字段名新字段名类型(长度) [约束]---修改字段名和字段类型

ALTER TABLE表名 DROP 字段名 --- 删除字段

DROP TABLE 表名 ---删除表

TRUNCATE TABLE 表名 ---删除表并重新创建该表

ALTER table 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为 ---添加外键

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name(index_col_name,...) ---创建索引

SHOW INDEX FROM table_name ---查看索引

DROP INDEX index_name ON table_name ---删除索引

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED|LOCAL]CHECK OPTION]---创建视图或修改视图

SHOW CREATE VIEW 视图名称 ---查看创建视图语句

ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH[CASCADED|LOCAL]CHECK OPTION]---修改视图，WITH CHECK OPTION的CASCADE是默认选项。

DROP VIEW 视图名称[,视图名称]... ---删除名称

关于存储过程和存储函数

CREATE PROCEDURE 存储过程([IN/OUT/INOUT 参数名,...]) BEGIN SQL语句 END;---创建存储过程，注意在命令行中创建存储过程因为SQL结束符与存储过程结束符冲突，所以使用delimiter更改结束符。

CALL 名称([参数]) ---调用

SELECT * FROM INFOREMATION_SCHEAM.ROUTINES WHERE ROUNTING_SCHEAMA = ‘xxx’; ---查询指定数据库的存储过程及状态信息

SHOW CREATE PROCEDURE 存储过程名称;---查询某个存储过程的定义

DROP PROCEDURE [IF EXISTS] 存储过程名称; ---删除

IF 条件1 THEN ... ELSE IF 条件2 THEN ... ELSE ... END IF;

CASE case_value WHEN when_value1 THEN statement_list1 [WHEN when_value2 THEN statement_list2]... [ELSE statement_list] END CASE;

CASE WHEN search_condition1 THEN statement_list1 [WHEN search_condition2 THEN statement_list2]...[ELSE statement_list] END CASE;

WHILE 条件 DO SQL逻辑... END WHILE;

REPEAT SQL逻辑... UNTIL 条件 END REPEAT;

[begin_label:] LOOP SQL逻辑... END LOOP [end_label];---注意，特性是由LEAVE label(相当于break，要退出循环，只能用它)，ITERATElabel（相当于continue）

游标（CURSOR）：用来存储查询结果集的数据类型。DECLARE 游标名称 CURSOR FOR 查询语句;---定义游标。OPEN 游标名称;---打开游标。FETCH 游标名称 INTO 变量[,变量];---获取游标记录，变量对应查询结果字段。CLOSE 游标名称---关闭游标。

条件处理程序（handler）：可以用来定义在流程控制结构执行过程中遇到问题相应的处理步骤。DECLARE handler_action HANDLER FOR condition,[,condition_value]... statement;---定义条件处理程序。handler_actionàCONTINUE，EXIT。condition_valueàSQLSTATE sqlstate_value，状态码，如02000；SQLWARING，所有以01开头的SQLSTATE代码简写；NOT FOUND，所有以02开头的SQLSTATE代码简写；SQLEXCEPTION，所有没有被SQLWARING或NOT FOUND捕获的SQLSTATE代码简写。

存储函数：是有返回值的存储过程，存储函数的参数只能是IN类型。CRETE FUNCTION 存储函数名称([参数列表]) RETURNS type [characteristic...] BEGIN SQL语句 RETURN...; END;。其中characteristic.有DETERMINISTICà相同的输入参数总是产生相同的结果，NO SQLà不包含SQL语句，READS SQL DATAà包含读取数据的语句，但不包含写入数据的语句。

关于触发器

介绍：在insert/update/delete之前或之后，触发并执行触发器调用的SQL语句逻辑。现在触发器只支持行级触发，不支持语句级触发。

CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tbl_name FOR EACH ROW BEGIN trigger_stmt; END;

SHOW TRIGGERS --- 查看触发器

DROP TRIGGER[schema_name.]trigger_name;---如果没有指定schema_name，默认为当前数据库

关于变量

系统变量：全局变量（GLOBAL）、会话变量（SESSION）。

用户自定义变量：不用提前声明，直接用@变量名使用，作用域是当前连接。

局部变量：需要用DECLARE关键字声明，作用域是局部的。

SHOW [SESSION|GLOBAL] VARIABLES; ---查看所有系统变量

SHOW [SESSION|GLOBAL] VARIABLES LIKE ‘......’;---通过模糊匹配查找变量

SELECT @@[SESSION|GLOBAL].系统变量名 ---查看指定变量的值

SET [SESSION|GLOBAL] 系统变量名=值;---设置系统变量

SET @@[SESSION|GLOBAL] 系统变量名=值; ---设置系统变量

SET @var_name=expr[,@var_name=expr]...; ---为自定义变量赋值

SET @var_name:=expr[,@var_name:=expr]...;---为自定义变量赋值

SELECT @var_name:=expr[,@var_name:=expr]...; ---为自定义变量赋值

SELECT 字段名 INTO @var_name FROM 表名; ---将查询结果赋值给自定义变量

SELECT @var_name; ---使用自定义变量

DECLARE 变量名变量数据类型[DEFAULT ...];---声明变量

SET 变量名=值 ---为局部变量赋值

SET 变量名:=值 ---为局部变量赋值

SELECT 字段名 INTO 变量名 FROM 表名 ...; ---将查询结果赋值给局部变量

SELECT 变量名; ---使用局部变量

注意：

如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。

mysql服务重新启动后，所设置的全局参数会失效，要想不失效，在其配置文件中配置。

关于数据类型

数值型：TINYINT(1 byte)、SMALLINT(2 bytes)、MEDIUMINT(3 bytes)、INT或INTEGER(4 bytes)、BIGINT(8 bytes)、FLOAT(4 bytes)、DOUBLE(8 bytes)、DECIMAL。

字符型：CHAR(0-255 bytes)、VARCHAR(0-65535 bytes)、TINYBLOB(0-255 bytes)、TINYTEXT(0-255 bytes)、BLOB(0-65535 bytes)、TEXT(0-65535 bytes)、MEDIUMBLOB(0-16777215 bytes)、MEDIUTEXT(0-16777215bytes)、LONGBLOB(0-4294967295 bytes)、LONGTEXT(0-4294967295 bytes)。

日期型：DATE(YYYY-MM-DD)、TIME(HH:MM:SS)、YEAR(YYYY)、DATETIME(YYYY-MM-DD HH:MM:SS)、TIMESTAMP(YYYY-MM-DD HH:MM:SS)。

（二） DML（Data Manipulation Language）

INSERT INTO 表名(字段名列表) VALUES(对应值列表)---添加数据，省略字段名列表就是给全部字段添加数据，对应值列表可以有多个称为批量添加数据。

INSERT INTO 视图名（字段名列表）VALUES（对应值列表）---向视图的基表添加数据，若视图加了检查选项会先检查视图检查选项再插入基表，其中CASCADE检查选项表示所有依赖视图或表都要进行检查，LOCAL则是递归检查添加了检查选项的依赖视图或表。除此之外，视图的更新操作必须视图中的行与基本表的行之间存在一对一的关系，即若包含以下任何一项，视图不可更新，聚合函数或窗口函数、DISTINCT、GROUPBY、HAVING、UNION。

UPDATE 表名 SET 字段名1=值1，字段名2=值2 [where 条件] ---修改数据。

DELETE FROM 表名 [where 条件] ---若没有条件，则是删除表的全部数据，不能删除某一字段的值，可以使用UPDATE。

（三） DQL（Data Query Language）

常用关键字

distinct 去重

as 取别名（没有as也行）

asc desc（排序关键字，默认asc）

语法结构

SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDEY BY 排序字段列表 LIMIT 分页参数

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件---显示内连接，默认INNER，所以INNER可以省略。

SELECT 字段列表 FROM 表1 LEFT/OUTER [OUTER] JOIN 表2 ON 条件---左/右外连接，默认OUTER，所以OUTER可以省略

SELECT 字段列表 FROM 表A ... UNION [ALL] SELECT 字段列表 FROM 表B ... ---联合查询，不加ALL会对合并后的数据去重，加了ALL不去重直接合并。

注意事项：

1. 执行次序：FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY->LIMIT

2. where不能对聚合函数进行判断， having可以。

3.LIMIT若索引从0开始，可以省略起始索引。

常见聚合函数

count 统计数量

max 最大值

min 最小值

avg 平均值

sum 求和

多表查询

关于连接查询：分为内连接和外连接。

关于内连接：隐式内连接(from关键字后表名用逗号连接)，显式内连接（from关键字后表名用 INNER JOIN 连接，并且where被on代替）

关于外连接：左外连接和右外连接。

关于自连接：使用别名实现将自己连接起来。

关于子连接：标量子查询（子查询结果为单个值，按照单值处理处理）、列子查询（子查询结果为一列，使用操作符处理子查询结果）、行子查询（子查询结果为一行，使用操作符处理，注意左边字段名对上右边子查询的值）、表子查询（子查询结果为多行多列，使用操作符处理，注意左边字段名对上右边子查询的值）

联合查询

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

（四） DCL（Data Control Language）

在系统数据库mysql里查询其user表---查询用户

CREATE USER ‘用户名’@’主机名’ IDENTIFIED BY ‘密码’---创建

用户

ALTER USER ‘用户名’@‘主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码’

DROP USER ‘用户名’@’主机名’

SHOW GRANTS FOR ‘用户名’@’主机名’---查询权限

GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@’主机名’---授予权限

REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@’主机名’---撤销权限

注意事项：

当主机名为%，表示供外界所有机器访问。
权限列表可以使用通配符*。

（五）内置函数

关于字符串

concat(s1,s2,...)

lower(str)

upper(str)

lpad(str,n,pad) ---左填充

rpad(str,n,pad) ---右填充

trim(str)

substring(str,start,len)

关于数值

ceil(x)

floor(x)

mod(x,y)

rand()

round(x,y)

关于日期

curdate()

curtime()

now()

year(date)

month(date)

date_add(date,expr)

datediff(date1,date2)

关于流程

if(value,t,f)

ifnull(value1,value2)

case when [value1] then [res1] ... else [default] end---若Value1为true，返回res1

case [expr] when [value1] then [res1] ... else [default] end ---若expr等于value1返回res1

二、体系架构

三、索引

介绍

索引（index）是帮助MySQL高效获取数据的数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种就是索引。

索引结构

B+Tree索引图：

Hash索引：

索引分类

注意：

除上述分类外，联合索引（复合索引）并非聚集索引，而是多个字段组成的索引结构。

覆盖索引，二级索引中的字段已经包含了需要查询返回的所有字段，大多情况下是联合索引，毕竟返回的是多列数据。

前缀索引，当索引字段是字符串时，有时候索引很长的字符串使索引变得很大，所以只取其前缀建立索引（在字段名后添个括号，括号里写前缀长度）。前缀长度要确定依赖于其重复性即在不同前缀下去重后的数量占原本数量的百分比，即select count(distinct substring(email,1,8)) /count(*) from 表名。

因为innoDB存储引擎的存储形式是将行数据与聚集索引放在一起存放，所以必须存在聚集索引，并且只能有一个。

回表查询：用户查询二级索引得到主键值后，再去聚集索引结构里查询数据。

SQL优化

性能分析

SQL执行频率：查看服务器状态命令，可以查看当前数据库的CRUD操作访问频次。

慢查询日志：记录了所有执行时机超过指定参数的所有SQL语句日志，默认没有开启（系统变量为slow_query_log），在MySQL配置文件中配置信息。

profile详情：show profiles能够在做SQL优化时帮助我们了解时间耗费在哪个详细细节，通过having_profiling参数查看是否支持，并且默认是关闭的。

explain执行计划：解释查询语句的执行细节，只需要在SELECT语句前加EXPLAIN或DESC，EXPLAIN/DESC SELECT语句。其返回各字段意义：id是子查询或操作表的顺序，id越大越先执行，id相同执行顺序从上到下；select_type是SELECT的类型，如SIMPLE（简单表，不使用表连接或子查询）、PRIMARY（最外层的查询）、UNION（UNION中第二个或后面的查询语句）、SUBQUERY（子查询）；type表示连接类型，性能由好到差的连接类型为NULL（不查询任何表，如select ‘A’）、system（访问系统表）、const（使用唯一索引）、eq_ref、ref（使用非唯一索引）、range、index（用了索引，对索引进行扫描）、all（全表扫描）；possible_key指可能应用在这张表上的索引；key表示实际用到的索引；key_len值索引使用的最大字节数；rows指执行查询的行数，估计值；filtered指返回结果的行数占读取的行数百分比；extra指额外内容，using index condition查找使用了索引，但是需要回表查询，using where;using index查找使用了索引，但是需要的数据都在索引列中能找到，索引不需要回表查询，using index，using filesort，backward index scan，using temporary（用到临时表）。

INSERT优化

使用insert into 表名values(),(),(),()...；

手动开启事务再插入数据；

主键顺序插入；

大批量插入数据

主键优化

在innoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式称为索引组织表。

页分裂和页合并：由于B+树的数据结构，当新增数据和删除数据不满足数据结构的定义时或是到了页合并阈值（优化空间）。

主键设计原则：

Order By优化

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲去sort buffer中进行排序操作，所有不是通过索引直接排序结果的排序都叫FileSort排序。不可避免的大量使用该种情况时可以适当增大sort buffer通过更改变量sort_buffer_size。

Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

若用了排序索引，失效情况多为以下介绍的复合索引失效情况using filesort，或者是没有按索引的顺序进行，即单独一个反序只会引发backward index scan，多个索引字段顺序不协调引发using filesort。

Group by 优化

通过索引来提高效率

Limit 优化

一般情况下，通过建立覆盖索引优化。

Count 优化

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候直接返回，效率很高。

innoDB引擎就麻烦了，它执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

几种用法：

Update优化

所以针对搜索条件字段建立索引。

索引的使用

复合（联合）索引的失效

最左前缀法则：查询从索引的最左列开始，并且不跳过索引中的列。如果查询的是联合索引，则应遵循最左前缀法则，跳过某一列，索引将部分失效（后面的字段索引失效）。

如果某个字段使用了范围查询条件(>,<)后面的字段索引失效，但是>=和<=不失效。

其他索引的失效情况

不要在索引列上进行运算操作，索引将失效。

字符串类型字段不加单引号，索引将失效。

模糊查询仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引将失效。

or连接的字段，一个有索引一个无索引，索引将会失效。

当存储引擎评估走索引比走全表扫描还慢（基于B+树，取决于数据分布情况），索引将失效。

SQL显示提示

当查询字段有多个索引时。显示的建议MySQL使用哪个索引，语法为SELECT 字段列表 from 表名 use index(索引名)，还可以忽略索引，即将use换成ignore。强制使用索引，将use 换成force。

索引的设计

四、事务

（1）四种隔离级别---即是封锁协议

a) Read uncommitted 脏读、不可重复读、幻读都未解决

b) Read committed 解决了脏读

c) Repeatable Read（默认）解决了脏读、不可重复读

d) Serializable 解决了脏读、不可重复读、幻读

（2）三种锁

全局锁：锁定数据库中的所有表，其他客户端不能进行更新但能读。

表级锁：每次操作锁住整张表，分为写锁和读锁。除此之外，在MySQL5.5后为了避免DML和DDL的冲突引入了元数据锁（meta data lock，MDL，当对表数据进行增删改查时加MDL读锁，对表结构进行变更时加MDL写锁，元数据锁是系统自动加的）。除此之外，为了避免DML在执行时加的行锁与表锁的冲突，即避免表锁挨个检查每行数据是否加了行锁，而引入了意向锁，意向锁又分意向共享锁（IS）、意向共享锁（IX），较为特殊的是意向锁之间不互斥，其他与锁的概念基本一致。

行级锁：每次操作锁住对应的行数据。结合InnoDB的数据组织表B+Tree，三类，分为行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete，在RC、RR隔离级别下都支持；分为间隙锁（Gap Lock），锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读，在RR隔离级别下都支持，间隙锁也是较为特殊的，间隙锁之间可以共存；分为临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap，在RR隔离级别下支持。

注意：默认情况下，InnoDB在REPEATABLE READ事务隔离级别下运行，InnoDB使用临键锁进行搜索和索引扫描，以防止幻读。（其中又分写锁和读锁，在UPDATE/INSERT/DELETE时系统自动加写锁，SELECT默认不加锁，按需手动加锁）而存在以下锁的变化情况：

第一种：针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

第二种：索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁。

第三种：索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁。

第四种：索引上的范围查询（唯一索引）会访问到不满足条件的第一个值为止，即会给不满足的第一个值加临键锁。

第五种：InnoDB的行锁是针对索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中所有记录加锁，此时就会升级为表锁。

flush tables with read lock; ---加全局锁

unlock tables/客户端断开连接; ---释放全局锁、表锁

lock tables 表名... read/write ---加读锁或写锁

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;---查看意向锁和行锁的加锁情况

select 语句 lock in share mode; ---加行共享锁

select 语句 FOR UPDATE ---加行排它锁