深入浅出MySQL

一、SQL基础

1.1 DDL

DDL 是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。

创建数据库

CREATE DATABASE dbname;

切换数据库

USE dbname;

删除数据库

DROP DATABASE dbname;

创建表

CREATE TABLE tablename(列名 列类型,列名1 列类型);

查看表定义

DESC tablename;

删除表

DROP TABLE tablename

修改表

修改表字段属性

ALTER TABLE tablename MODIFY username varchar(20);

增加表字段

ALTER TABLE tablename ADD password varchar(255);

删除表字段

ALTER TABLE tablename DROP COLUMN password;

表字段改名

ALTER TABLE tablename CHANGE age age1 int(4);

表改名

ALTER TABLE tablename RENAME tablename2;

1.2 DML

插入记录

INSERT INTO tablename (username,age) VALUES("cl",18);

更新记录

UPDATE tablename SET username="clcoding",age=21 WHERE age=18;

删除记录

DELETE FROM tablename WHERE username="clcoding"

查询记录

去重查询

SELECT DISTINCT username FROM tablename;

排序查询

DESC : 降序
ASC : 升序

SELECT * FROM tablename ORDER BY age DESC;

限制查询
```
SELECT * FROM tablename LIMIT 0,3;
```
聚合查询
```
SELECT deptno,count(1) FROM emp GROUP BY deptno; 
```
count(1)：聚合函数，常用的有sum、count、max、min

GROUP BY关键字表示要进行分类聚合的字段

HAVING关键字表示对分类后的结果再进行条件的过滤。

having 和 where 的区别在于 having 是对聚合后的结果进行条件的过滤，而 where 是在聚合前就对记录进行过滤，如果逻辑允许，我们尽可能用 where 先过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用 having 进行再过滤。
连接查询
- 左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
- 右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录
联合查询
```
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept;
```
UNION 和 UNION ALL 的主要区别是 UNION ALL 是把结果集直接合并在一起，而 UNION 是将UNION ALL 后的结果进行一次 DISTINCT，去除重复记录后的结果。

二、数据类型

2.1 整型数据

2.1.1 数据显示宽度

对于整型，MySQL支持类型名称后面小括号内指定显示宽度，不显示指定宽度，默认为int(11),一般配合zerofill使用，就是在数字位数不够的空间用字符“0”填满。
显示宽度不会影响插入值，插入值长度大于显示宽度，前面不会有0填充
如果一个列指定为zerofill，MySQL自动为该列添加UNSIGNED属性

2.1.2 自增长列

一个表中最多有一个自增长列，且定义为NOT NULL，定义为PRIMARY KEY 或 UNIQUE键

2.2 小数数据

2.2.1 浮点数

没有精度和标度，则按实际精度值显示
如果有精度和标度，保存值时自动采用四舍五入，不会报错

2.2.2 定点数

定点数在MySQL中，以字符串形式存放
在不指定精度时，默认的整数位为10，小数位为0，超过精度和标度时会报错

2.3 日期时间数据

主要类型

日期时间类型	字节	最小值	最大值
DATE	4	1000-01-01	9999-12-31
DATETIME	8	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	4	19700101080001	2038年某时刻
TIME	3	-838:59:59	838:59:59
YEAR	1	1901	2155

2.3.1 使用场景

如果只用来表示年月日，通常用DATE表示。
如果只用来表示时分秒，通常用 TIME 来表示。
如果表示年月日时分秒，通常用 DATETIME 表示。
如果需要经常插入或者更新日期为当前系统时间，则通常使用 TIMESTAMP 来表示。TIMESTAMP 值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串，显示宽度固定为 19 个字符。如果想要获得数字值，应在 TIMESTAMP 列添加+0。
如果只是表示年份，可以用 YEAR 来表示，它比 DATE 占用更少的空间。YEAR 有 2 位或4 位格式的年。默认是 4 位格式。在 4 位格式中，允许的值是 1901～2155 和 0000。在 2 位格式中，允许的值是 70～69，表示从 1970～2069 年。MySQL 以 YYYY 格式显示 YEAR值

2.3.2 TIMESTAMP

系统会给TIMESTAMP列自动创建默认值CURRENT_TIMESTAMP（系统日期）
MySQL只给表中的第一个TIMESTAMP字段设置默认值为系统日期，如果有第二个TIMESTAMP类型，则默认值设置为0值
插入日期时，会先转换为本地时区后存放，从数据库取出时，也需要将日期转换为本地时区后显示
不适合存储比较久远的日期，超过范围会以零值存储

2.3.3 日期插入格式

YYYY-MM-DD HH:MM:SS 或 YY-MM-DD HH:MM:SS 格式的字符串。允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符。
YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。无意义则使用零值。
YYYYMMDDHHMMSS 或 YYMMDDHHMMSS 格式的数字，假定数字对于日期类型是有意义的。
函数返回的结果，其值适合 DATETIME、DATE 或者TIMESTAMP 上下文，例如 NOW()或 CURRENT_DATE。

2.4 字符串数据

2.4.1 VARCHAR和CHAR

都用来保存MySQL中较短的字符串
CHAR列的长度固定为创建表时声明的长度，0~255的任何值。
VARCHAR列中的值为可变长字符串，长度可以指定为0~65535之间的值。
检索时，CHAR列删除了尾部的空格，VARCHAR则保留了这些空格

三、存储引擎

MYSQL-5.1版本以前，默认引擎为MyISAM,之后为InnoDB

3.1 MyISAM

MyISAM在磁盘上存储分为3个文件，数据文件和索引文件可以放到不同目录，平均分布IO
- .frm：存储表定义
- .MYD：存储数据
- .MYI：存储索引
存储格式
1. 静态表
  - 静态表是默认的存储格式
  - 静态表中的字段都是固定长度的
  - 优点：存储非常迅速，容易缓存，出现故障容易恢复
  - 缺点：占用的空间多，存储的时候会按照列宽不足空格，访问时自动取出尾部空格
2. 动态表
  - 记录不是固定长度的
  - 优点：占用空间少
  - 缺点：频繁的更新删除记录会产生碎片
3. 压缩表
  - 由myisampack工具创建，占据非常小的磁盘空间
  - 每个记录单独压缩，具有非常小的访问开支

3.2 InnoDB

3.2.1 自增长列

自增长列可以手动插入，但是如果插入的值为空或0，则实际插入的是自动增长后的值
自增长列必须是索引。如果是组合索引，必须是组合索引的第一列
MyISAM中自增长列可以是组合索引的其他列，插入记录时，自增长列按照组合索引的前面几列进行排序后递增

3.2.2 外键

在创建外键的时候，必须要求父表有对应的索引，子表在创建外键时也会自动创建对应的索引
当某个表被其他表创建了外键参照，那么该表的对应索引或者主键禁止被删除

3.2.3 存储方式

使用共享表空间存储：创建的表的表结构保存在.frm文件中，数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。
使用多表空间存储：创建的表的表结构保存在.frm文件中，表数据和索引单独保存在.idb文件中。
即便在多表空间的存储方式下，共享表空间仍然是必须的，InnoDB把内部数据词典和未作日志放在这个文件中

四、字符集

MySQL可以同时支持多种字符集，同一台服务器，同一个数据库，同一个表的不同字段都可以指定使用不同的字符集。

4.1 字符集设置

MySQL的字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级、字段级

4.1.1 服务器级

服务器字符集和校对，在MySQL服务启动的时候确定

#可以在my.cnf中设置：
#[mysqld]
default-character-set=gbk
#或者在启动选项中指定：
mysqld --default-character-set=gbk
#或者在编译的时候指定
./configure --with-charset=gbk

如果没有特别的指定服务器字符集，默认使用latin1作为服务器字符集

4.1.2 数据库级

数据库的字符集和校对规则在创建数据库的时候指定，也可以在创建完数据库后通过“alter database”命令进行修改
如果数据库里已经存在数据，不能通过修改数据库的字符集直接修改数据的内容
数据库字符集规则
- 如果指定了字符集和校对规则，则使用指定的字符集和校对规则；
- 如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则；
- 如果没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符集和校对规则。

4.1.3 表级

表的字符集合校对规则在创建表时指定，也可以通过alter table 命令修改
如果表里已经存在数据，不能通过修改表的字符集直接修改数据的内容
表的字符集规则
- 如果指定了字符集和校对规则，使用指定的字符集和校对规则；
- 如果指定了字符集没有指定校对规则，使用指定字符集的默认校对规则；
- 如果没有指定字符集和校对规则，使用数据库字符集和校对规则作为表的字符集和校对规则。

4.1.4 字段级

列字符集合校对规则的定义可以在创建表时指定，或者在修改表时调整

4.2 连接字符集

对实际应用访问，还存在客户端和服务器之间交互的字符集合校对规则的设置
MySQL的3个参数：通常情况下，三个字符集应该是相同的
- character_set_client：客户端字符集
- character_set_connection：连接字符集
- character_set_results：返回结果字符集
修改
1. 命令：需要每次连接数据库都执行这个命令
```
SET NAMES ***;
```
2. 配置文件：服务器启动后，所有连接默认是GBK字符集进行链接的
```
[mysql]
default-character-set=gbk
```

五、索引

5.1 索引概述

MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引
MySQL支持前缀索引，MyISAM存储引擎的表，索引的前缀长度可以达到1000字节长，InnoDB索引的前缀长度最长是767字节
MyISAM支持全文索引

索引创建

#创建十个字节的前缀索引
create index username on user(user(10))

索引删除
```
drop index username on user
```

5.2 设计原则

搜索的索引列，不一定是要选择的列
- 最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的列
使用唯一索引
- 索引列的基数越大，索引效果越好，即要选择区分度高的列
使用短索引
- 如果对字符串列进行索引，应该指定一个前缀长度
- 较短的键值，索引高速缓存中的块能容纳更多的键值
利用最左前缀
- 组合索引实际是创建了MySQL可利用的n个索引
不要过度索引
- 每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能

5.3 HASH索引

只用于适用=或<=>操作符的等式比较
优化器不能使用HASH索引来加速ORDER BY操作
只能使用整个关键字来搜索一行

六、事务

6.1 表锁

LOCK TABLES可以锁定用于当前线程的表，如果表被其他线程锁定，则当前线程会等待，直到可以获取所有锁定为止
UNLOCK TABLES可以释放当前线程获得的任何锁定，当前线程执行另一个LOCK TABLES或者与服务器的连接被关闭时，所有由当前线程锁定的表被隐含地解锁

简单加锁解锁示例

lock table user_table read;
unlock tables;

6.2 事务控制

START TARNSACTION 或 BEGIN语句可以开始一项新的事务
COMMIT 和 ROLLBACK 用来提交或者回滚事务
CHAIN 和 RELEASE分别定义事务提交或者回滚之后的操作。CHAIN会立即开启一个新事务，并且和刚才的事务具有相同的隔离级别。RELEASE则会断开和客户端的连接。
在锁表期间，用start transaction命令开始一个新事物，会造成一个隐含的unlock tables被执行

6.3 分布式事务

6.3.1 原理

资源管理器（RM）：用于提供通向事务资源的途径
事务管理器（TM）：用于协调作为一个分布式事务一部分的事务，即分支事务

6.3.2 两阶段提交

第一阶段：所有的分支被预备好。即他们被TM告知要准备提交。通常，这意味着用于管理分支的每个RM会记录对于被稳定保存的分支的行动。分支指示是否它们可以这么做。这些结果被用于第二阶段。
第二阶段：TM告知RM是否要提交或回滚。如果在预备分支时，所有的分支指示它们将能够提交，则所有的分支被告知要提交。如果在预备时，有任何分支指示它将不能提交，则所有分支被告知回滚。

七、锁问题

7.1 MyISAM表锁

表锁有两种模式：表共享读锁和表独占写锁

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

7.1.1 加锁

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁。
在执行更新（UPDATE、DELETE、INSERT）前，会自动给设计的所有表加写锁。

加锁代码

lock tables orders read local,orders_detail read local

local table orders write
#local作用是满足MyISAM表在并发插入条件的情况下，允许其他用户在表尾并发插入记录

#显示加锁后，只能访问显示加锁的表，不能访问未加锁的表

#自动加锁时，MyISAM总是一次获得SQL语句所需要的全部锁，这也是MyISAM表不会出现死锁的原因

7.1.2 并发插入

MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制并发插入的行为，其值可以为0,1,2
- 当concurrent_insert设置为0，不允许并发插入
- 当concurrent_insert设置为1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这是MySQL的默认设置
- 当concurrent_insert设置为2，无论MyISAM是否有空洞，都允许在表尾并发插入记录
设置了local后，当前session不能进行插入操作，也不能查询新插入的值，其他session可以插入值

7.1.3 锁调度

如果一个进程请求读锁，一个进程请求写锁，写进程先获得锁
即使读进程先进入等待队列，写进程后进队列，也会插入到读锁请求之前
调节调度行为
- 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利
- 通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低
- 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级
- 给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会

7.2 InnoDB锁

7.2.1 事务隔离级别

实现事务隔离的方式：
1. 在读取数据前，对其加锁，防止其他事务对数据进行修改
2. 不用任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照，并用这个快照来提供一定级别的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MVCC）

7.2.2 InnoDB行锁模式

InnoDB实现了两种类型的行锁
1. 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
2. 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁
InnoDB内部使用的意向锁，两种意向锁都是表锁
1. 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁
2. 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁
兼容性
意向锁是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 方式获得排他锁。

7.2.3 行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的
只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB使用表锁
MySQL行锁是针对索引加的锁，索引访问不同行的记录，如果是使用相同的索引键，会出现锁冲突。
即使在条件中使用了索引，执行计划选择了全表扫描，InnoDB将使用表锁而不是行锁

7.2.4 InnoDB使用表锁

使用表锁的情况
1. 事务需要更新大部分或全部数据，表又比较大，如果使用行锁，事务执行效率低，并且会造成其他事务长时间等待锁和锁冲突
2. 事务涉及多个表，比较复杂，可能引起死锁，造成大量事务回滚
使用 LOCK TABLES 虽然可以给 InnoDB 加表级锁，但必须说明的是，表锁不是由 InnoDB存储引擎层管理的，而是由其上一层──MySQL Server 负责的，仅当 autocommit=0、innodb_table_locks=1（默认设置）时，InnoDB 层才能知道MySQL 加的表锁，MySQL Server也才能感知 InnoDB 加的行锁，这种情况下，InnoDB 才能自动识别涉及表级锁的死锁；否则，InnoDB 将无法自动检测并处理这种死锁。
在用 LOCK TABLES 对 InnoDB 表加锁时要注意，要将 AUTOCOMMIT 设为 0，否则MySQL 不会给表加锁；事务结束前，不要用 UNLOCK TABLES 释放表锁，因为 UNLOCK TABLES会隐含地提交事务；COMMIT 或 ROLLBACK 并不能释放用 LOCK TABLES 加的表级锁，必须用UNLOCK TABLES 释放表锁。

7.3 间隙锁

在使用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对键值在条件范围内但并不存在的记录，叫做间隙，InnoDB会对这个间隙加锁，这种锁机制叫做间隙锁

使用间隙锁的目的：
1. 防止幻读
2. 为了满足其恢复和复制的需要
除了范围查询会使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁。

7.4 恢复和复制

MySQL通过BINLOG记录执行的INSERT UPDATE DELETE等更新数据的SQL语句，并由此实现数据库的恢复和主从复制

MySQL的恢复机制特点：
1. MySQL的恢复是SQL语句级的，也就是重新执行BINLOG中的SQL语句
2. MySQL的BINLOG是按照事务提交的先后顺序记录的，恢复也按这个顺序进行
MySQL恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读。

7.5 死锁

MyISAM 表锁是 deadlock free 的，这是因为 MyISAM 总是一次获得所需的全部锁，要么全部满足，要么等待，因此不会出现死锁。

但在 InnoDB 中，除单个 SQL 组成的事务外，锁是逐步获得的，这就决定了在 InnoDB 中发生死锁是可能的。

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。
但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。
死锁避免
- 尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会（于两个 session 访问两个表的顺序不同，发生死锁的机会就非常高）
- 在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
- 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁

八、日志

8.1 错误日志

错误日志是 MySQL 中最重要的日志之一，它记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，可以首先查看此日志。

可以用--log-error[=file_name]选项来指定 mysqld（MySQL 服务器）保存错误日志文件的位置。如果没有给定 file_name 值，mysqld 使用错误日志名 host_name.err（host_name 为主机名），并默认在参数 DATADIR（数据目录）指定的目录中写入日志文件。

8.2 二进制日志

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。

日志的位置和格式
- 当用--log-bin[=file_name]选项启动时，mysqld将包含所有更新数据的SQL 命令写入日志文件。如果没有给出 file_name 值，默认名为主机名后面跟“-bin”。如果给出了文件名，但没有包含路径，则文件默认被写入参数 DATADIR（数据目录）指定的目录。

日志的读取

#由于日志以二进制方式存储，不能直接读取，需要用 mysqlbinlog 工具来查看，
mysqlbinlog log-file;

日志的删除
- 执行“RESET MASTER;”命令，该命令将删除所有 BINLOG 日志，新日志编号从“000001”开始。
- 执行“PURGE MASTER LOGS TO 'mysql-bin.'”命令**，该命令将删除“******”编号之前的所有日志。
```
purge master logs to 'localhost-bin.000006';
```
- 执行“PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh24:mi:ss'”命令，该命令将删除日期为“yyyy-mm-dd hh24:mi:ss”之前产生的所有日志。
```
purge master logs before '2007-08-10 04:07:00';
```
- 设置参数--expire_logs_days=#，此参数的含义是设置日志的过期天数，过了指定的天数后日志将会被自动删除，这样将将有利于减少 DBA 管理日志的工作量。

8.3 查询日志

查询日志记录了客户端的所有语句

日志的位置和格式
- 当用--log[=file_name]或-l [file_name]选项启动 mysqld（MySQL 服务器）时，查询日志开始被记录。和其他日志一样，如果没有给定 file_name 的值，日志将写入参数 DATADIR（数据目录）指定的路径下，默认文件名是 host_name.log。
日志查看
```
 more localhost.log
```
log 日志中记录了所有数据库的操作，对于访问频繁的系统，此日志对系统性能的影响较大，建议一般情况下关

8.4 慢查询日志

慢查询日志记录了包含所有执行时间超过参数 long_query_time（单位：秒）所设置值的 SQL语句的日志。获得表锁定的时间不算作执行时间

日志查看
```
 more localhost-slow.log
```

九、SQL安全

9.1 应对措施

PrepareStatement+Bind-variable

String sql = "select * from users u where u.id = ? and u.password = ?";
preparedstatement ps = connection.preparestatement(sql);
ps.setint(1,id);
ps.setstring(2,pwd);
resultset rs = ps.executequery();

使用应用程序提供的转换函数
- 很多应用程序接口都提供了对特殊字符进行转换的函数，恰当地使用这些函数，可以防止应用程序用户输入使应用程序生成不期望的语句。
- MySQL++：使用 escape 和 quote 修饰符。
自定义函数进行校验
- 对用户提交或者可能改变的数据进行简单分类，分别应用正则表达式来对用户提供的输入数据进行严格的检验和验证

十、SQL优化

10.1 EXPLAIN

可以通过 EXPLAIN 或者 DESC 命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序

select_type：表示SELECT的类型，常见的取值有
1. SIMPLE(简单表，即不使用表连接或者子查询)
2. PRIMARY（主查询，即外层的查询)
3. UNION (UNION中的第二个或者后面的查询语句)
4. SUBQUERY(子查询中的第一个SELECT）
table：输出结果集的表。
type：表示表的连接类型，性能由好到差的连接类型为
- system(表中仅有一行，即常量表)
- const(单表中最多有一个匹配行，例如 primary key或者unique index)
- eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key或者unique index）
- ref(与eq_ref类似,区别在于不是使用primarykey或者unique index，而是使用普通的索引）
- ref_or_null(与ref类似，区别在于条件中包含对NULL的查询)、index_merge(索引合并优化)
- unique_subquery ( in的后面是一个查询主键字段的子查询)
- index_subquery(与 unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询)
- range(单表中的范围查询)
- index(对于前面的每一行，都通过查询索引来得到数据)
- all(对于前面的每一行，都通过全表扫描来得到数据)。
possible_keys：表示查询时，可能使用的索引。
key：表示实际使用的索引。
key_len：索引字段的长度。
rows：扫描行的数量。
Extra：执行情况的说明和描述。

10.2 索引优化

10.2.1 未使用索引

查询优化器估计索引比全表扫描更慢，则不用索引
用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到
不符合最左匹配原则
like以%开始
存在隐式数据类型转换

10.3 常见优化策略

10.3.1 大量插入数据

InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率
在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率

10.3.2 优化INSERT

如果同时从同一客户插入很多行，尽量使用多个值表的 INSERT 语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个 INSERT 语句快(在一些情况中几倍)
```
insert into user values("cl",1),("clcoding",2)...
```
如果从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。DELAYED 的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快的多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入；
当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍。