【基础知识③】MySQL基础

数据库基础

Mysql常用的存储引擎

MyISAM是MySQL存储引擎之一，不支持数据库事务、行级锁、和外键。因此在INSERT或UPDATE数据即写操作时需要锁定整个表，效率会很低

InnoDB为MySQL提供了事务支持、回滚、崩溃修复能力、多版本并发控制、事务安全的操作。

InnoDB有什么特性

InnoDB引擎特点

1.支持事务，支持4个事务隔离级别，支持多版本读。

2.行级锁定（更新时一般是锁定当前行），通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。

3.读写阻塞与事务隔离级别相关。

4.具有非常高效的缓存特性：能缓存索引，也能缓存数据。

5.整个表和主键以Cluster方式存储，组成一个平衡树。

6.所有Secondary Index都会保存主键信息。

7.支持分区，表空间，类似oracle数据库。

8.支持外键约束，5.5之前不支持全文索引，5.5之后支持外键索引。

小结：supports transactions，row-level locking。and foreign keys

9.和Myisam引擎比，Innodb对硬件资源要求比较高。

MySQL引擎之innodb引擎应用场景及调优

Innodb引擎适用的生产场景

1、需要事务支持的业务（具有较好的事务特性）

2、行级锁定对高并发有很好的适应能力，但需要确保查询时通过索引完成。

3、数据读写及更新都较为频繁的场景，如：bbs，sns，微博，微信等。

4、数据一致性要求较高的业务，例如：充值转账，银行卡转账。

5、硬件设备内存较大，可以利用Innodb较好的缓存能力来提高内存利用率，尽可能减少磁盘IO。

redolog 和binlog的区别

• redolog

  redo log又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。

• binlog

  binlog记录了对MySQL数据库执行更改的所有操作，但是不包括SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改。然后，若操作本身例如update操作并没有导致数据库发生变化，那么该操作也会写入二进制日志。

• undolog

  Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用UndoLog来实现多版本并发控制(简称：MVCC)。Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为UndoLog）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用UndoLog中的备份将数据恢复到事务开始之前的状态。除了可以保证事务的原子性，Undo Log也可以用来辅助完成事务的持久化（即在事务执行的过程中断电了，恢复后我们依然可以进行回滚操作）。

  -用Undo Log实现原子性和持久化的事务的简化过程

  假设有A、B两个数据，值分别为1,2。
  A.事务开始.
  B.记录A=1到undolog.
  C.修改A=3.
  D.记录B=2到undolog.
  E.修改B=4.
  F.将undolog写到磁盘。
  G.将数据写到磁盘。
  H.事务提交
  这里有一个隐含的前提条件：‘数据都是先读到内存中，然后修改内存中的数据，最后将数据写回磁盘’。
  之所以能同时保证原子性和持久化，是因为以下特点：
  A.更新数据前记录Undo log。
  B.为了保证持久性，必须将数据在事务提交前写到磁盘。只要事务成功提交，数据必然已经持久化。
  C.Undo log必须先于数据持久化到磁盘。如果在G,H之间系统崩溃，undo log是完整的，可以用来回滚事务。

  D.如果在A-F之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。

  缺陷：每个事务提交前将数据和Undo Log写入磁盘，这样会导致大量的磁盘IO，因此性能很低。
  如果能够将数据缓存一段时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即RedoLog

• Redo log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是RedoLog已经持久化。系统可以根据RedoLog的内容，将所有数据恢复到最新的状态。

  -Undo+Redo事务的简化过程
  假设有A、B两个数据，值分别为1,2.
  A.事务开始.
  B.记录A=1到undolog.
  C.修改A=3.
  D.记录A=3到redolog.
  E.记录B=2到undolog.
  F.修改B=4.
  G.记录B=4到redolog.
  H.将redolog写入磁盘。
  I.事务提交

  -Undo+Redo事务的特点
  A.为了保证持久性，必须在事务提交前将RedoLog持久化。
  B.数据不需要在事务提交前写入磁盘，而是缓存在内存中。
  C.RedoLog保证事务的持久性。
  D.UndoLog保证事务的原子性。
  E.有一个隐含的特点，数据必须要晚于redolog写入持久存

选择binlog日志作为replication我想主要原因是MySQL的特点就是支持多存储引擎，为了兼容绝大部分引擎来支持复制这个特性，那么自然要采用MySQL Server自己记录的日志而不是仅仅针对InnoDB的redo log

binlog属于逻辑日志，是逻辑操作。innodb redo属于物理日志，是物理变更。

binlog作用

1.恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。
2.在主复制服务器上记录所有将发送给从服务器的语句。

MVCC（多版本并发控制）

MVCC(Mutil-Version Concurrency Control)，就是多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。

在Mysql的InnoDB引擎中就是指在已提交读(READ COMMITTD)和可重复读(REPEATABLE READ)这两种隔离级别下的事务对于SELECT操作会访问版本链中的记录的过程。

这就使得别的事务可以修改这条记录，反正每次修改都会在版本链中记录。SELECT可以去版本链中拿记录，这就实现了读-写，写-读的并发执行，提升了系统的性能。

我们来具体看看是如何实现的。

版本链

我们先来理解一下版本链的概念。在InnoDB引擎表中，它的聚簇索引记录中有两个必要的隐藏列：

trx_id这个id用来存储的每次对某条聚簇索引记录进行修改的时候的事务id。

roll_pointer每次对哪条聚簇索引记录有修改的时候，都会把老版本写入undo日志中。这个roll_pointer就是存了一个指针，它指向这条聚簇索引记录的上一个版本的位置，通过它来获得上一个版本的记录信息。(注意插入操作的undo日志没有这个属性，因为它没有老版本)

比如现在有个事务id是60的执行的这条记录的修改语句

此时在undo日志中就存在版本链

ReadView

说了版本链我们再来看看ReadView。已提交读和可重复读的区别就在于它们生成ReadView的策略不同。

ReadView中主要就是有个列表来存储我们系统中当前活跃着的读写事务，也就是begin了还未提交的事务。通过这个列表来判断记录的某个版本是否对当前事务可见。假设当前列表里的事务id为[80,100]。

如果你要访问的记录版本的事务id为50，比当前列表最小的id80小，那说明这个事务在之前就提交了，所以对当前活动的事务来说是可访问的。如果你要访问的记录版本的事务id为70,发现此事务在列表id最大值和最小值之间，那就再判断一下是否在列表内，如果在那就说明此事务还未提交，所以版本不能被访问。如果不在那说明事务已经提交，所以版本可以被访问。如果你要访问的记录版本的事务id为110，那比事务列表最大id100都大，那说明这个版本是在ReadView生成之后才发生的，所以不能被访问。这些记录都是去版本链里面找的，先找最近记录，如果最近这一条记录事务id不符合条件，不可见的话，再去找上一个版本再比较当前事务的id和这个版本事务id看能不能访问，以此类推直到返回可见的版本或者结束。

举个例子 ，在已提交读隔离级别下：

比如此时有一个事务id为100的事务，修改了name,使得的name等于小明2，但是事务还没提交。则此时的版本链是

那此时另一个事务发起了select 语句要查询id为1的记录，那此时生成的ReadView 列表只有[100]。那就去版本链去找了，首先肯定找最近的一条，发现trx_id是100,也就是name为小明2的那条记录，发现在列表内，所以不能访问。

这时候就通过指针继续找下一条，name为小明1的记录，发现trx_id是60，小于列表中的最小id,所以可以访问，直接访问结果为小明1。

那这时候我们把事务id为100的事务提交了，并且新建了一个事务id为110也修改id为1的记录，并且不提交事务

这时候版本链就是

这时候之前那个select事务又执行了一次查询,要查询id为1的记录。

这个时候关键的地方来了

如果你是已提交读隔离级别，这时候你会重新一个ReadView，那你的活动事务列表中的值就变了，变成了[110]。

按照上的说法，你去版本链通过trx_id对比查找到合适的结果就是小明2。

如果你是**可重复读隔离级别，这时候你的ReadView还是第一次select时候生成的ReadView,**也就是列表的值还是[100]。所以select的结果是小明1。所以第二次select结果和第一次一样，所以叫可重复读！

也就是说已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView，之后的读都复用之前的ReadView。

这就是Mysql的MVCC,通过版本链，实现多版本，可并发读-写，写-读。通过ReadView生成策略的不同实现不同的隔离级别。

删除重复的邮件地址

mysql删除重复数据保留id最小（最大）的数据:

DELETE p1
FROM
    Person p1,
    Person p2
WHERE
    p1.Email = p2.Email
    AND p1.Id > p2.Id

事务四大特性

 原子性，要么执行，要么不执行

隔离性，所有操作全部执行完以前其它会话不能看到过程

一致性，事务前后，数据总额一致

持久性，一旦事务提交，对数据的改变就是永久的

事务的隔离级别

读未提交 读已提交 可重复读 序列化

读已提交解决脏读问题

可重复读解决不可重复读问题

序列化解决幻读问题。

MySQL的默认隔离级别是可重复读 Oracle的默认隔离级别是读已提交 SQL Server的默认隔离级别是读已提交

多个事务读可能会道理以下问题
脏读：事务B读取事务A还没有提交的数据
不可重复读：，一行被检索两次，并且该行中的值在不同的读取之间不同时
幻读:当在事务处理过程中执行两个相同的查询，并且第二个查询返回的行集合与第一个查询不同时
这两个区别在于，不可重复读重点在一行，幻读的重点 ，返回 的集合不一样

聚集索引和非聚集索引

• 聚集索引：数据按索引顺序存储，子节点存储真实的物理数据
• 非聚簇索引：存储指向真正数据行的指针

索引的优缺点，什么时候使用索引？

索引最大的好处是提高查询速度，

缺点是更新数据时效率低， 因为要同时更新索引

对数据进行频繁查询宜建立索引

如果要频繁更改数据不建议使用索引。

Sql语句的优化

• 子查询变成left join
• limit 分布优化，先利用ID定位，再分页
• or条件优化，多个or条件可以用union all对结果进行合并（union all结果可能重复）
• 不必要的排序
• where代替having,having 检索完所有记录，才进行过滤
• 避免嵌套查询 对多个字段进行等值查询时，联合索引

索引最左前缀问题

最左匹配原则是针对索引的
举例来说：两个字段（name,age）建立联合索引，如果where age=12这样的话，是没有利用到索引的，这里我们可以简单的理解为先是对name字段的值排序，然后对age的数据排序，如果直接查age的话，这时就没有利用到索引了，
查询条件where name=‘xxx’ and age=xx 这时的话，就利用到索引了，再来思考下where age=xx and name=’xxx‘ 这个sql会利用索引吗，
按照正常的原则来讲是不会利用到的，但是优化器会进行优化，把位置交换下。这个sql也能利用到索引了

如果对三个字段建立联合索引，如果第二个字段没有使用索引，第三个字段也使用不到索引了

数据库索引的实现原理

树	区别
红黑树	增加，删除，红黑树会进行频繁的调整，来保证红黑树的性质，浪费时间
B树也就是B-树	B树，查询性能不稳定，查询结果高度不致，每个结点保存指向真实数据的指针，相比B+树每一层每屋存储的元素更多，显得更高一点。
B+树	B+树相比较于另外两种树,显得更矮更宽，查询层次更浅

B+树的实现：

一个m阶的B+树具有如下几个特征：
1.有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
2.所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
3.所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素

为什么使用B+ Tree

索引查找过程中就要产生磁盘I/O消耗,主要看IO次数，和磁盘存取原理有关。 根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理， 将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入 局部性原理与磁盘预读

创建索引有什么技巧吗

• 经常检索排序大表中30% 或非排序表7%的行，建议建索引；

• 为了改善多表关联，索引列用于联结；

• 列中的值比较惟一；

• 列中有许多空值，不适合建立索引；

• 经常一起使用多个字段检索记录，组合索引比单索引更有效；

• 不要索引大型字段；

• 不要索引常用的小型表，尤其假如它们有频繁的插入和删除操作；

• 不能真正使用到索引的情形：在索引列上使用函数查询，使用模式匹配LIKE，使用IN子查询；

索引的类型，怎么判断是否走索引

索引类型：

• 普通索引：仅加速查询
• 唯一索引：加速查询+列值唯一（可以为null）
• 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
• 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
• 全文索引：对文本的内容进行分词，进行搜索
• 索引合并，使用多个单列索引组合搜索
• 覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖*

查看sql是否用了索引：

可以在查询的sql前面增加explain命令，以此可以查看到sql的运行状态

当extra出现Using filesort和Using temproary这两个时，表示无法使用索引，必须尽快做优化。

当type出现index和all时，表示走的是全表扫描没有走索引，效率低下，这时需要对sql进行调优。

当type出现ref或者index时，表示走的是索引，index是标准不重复的索引，ref表示虽然使用了索引，但是索引列中有重复的值，但是就算有重复值，也只是在重复值的范围内小范围扫描，不造成重大的性能影响。

索引失效的7种情况

• 1.有or必没有索引

  select * from `order` where id=780    //没有or的情况下，走的是主键索引
  select * from `order` where id=780 or user_id=12  //条件中有or，则索引无效
  

  注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

• 2.复合索引未引用左列字段

• 3.like以%开头

  select * from `order` where user_name like 'w%'   //以%结尾：索引可以使用
  select * from `order` where user_name like '%w'   //以%开头 ：索引失效
  

• 4.需要类型转换

  存在索引列的数据类型隐形转换，则用不上索引，比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

  select * from 'order' where user_name='123'  // 查询字段加有引号：索引可用
  select * from 'order' where user_name=123   //查询字段没有引号，会将数字隐式转换为字符串，索引失效
  

• 5.where中索引列有运算

  select * from RULE_INFO where id=1
  select * from RULE_INFO where id=id+1  //索引失效
  

• 6.where中索引列使用了函数

  select * from RULE_INFO where id=1
  select * from RULE_INFO where ABS(id)=1
  

• 7.如果mysql觉得全表扫描更快时（数据少的情况下）

什么时候没必要用索引

1.唯一性差

比如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描。

2.频繁更新的字段不用（更新索引消耗性能）

比如logincount登录次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率。

3.where中不用的字段

只有在where语句出现，mysql才会去使用索引

4.索引使用<> 不等号 时，索引效果一般，不建议使用

数据库的主从复制

复制方式	操作
异步复制	默认异步复制，容易造成主库数据和从库不一致,一个数据库为Master,一个数据库为slave,通过Binlog日志,slave两个线程，一个线程去读master binlog日志，写到自己的中继日志一个线程解析日志，执行sql,master启动一个线程,给slave传递binlog日志
半同步复制	只有把master发送的binlog日志写到slave的中继日志，这时主库,才返回操作完成的反馈，性能有一定降低
并行操作	slave 多个线程去请求binlog日志

数据库中join的left join 、inner join 、cross join

1.以A，B两张表为例
A left join B
选出A的所有记录，B表中没有的以null 代替
right join 同理

2.inner join
A,B有交集的记录

3.cross join (笛卡尔积)
A中的每一条记录和B中的每一条记录生成一条记录
例如A中有4条，B中有4条，cross join 就有16条记录

关系型数据库和非关系型数据库的区别

优点
1、容易理解：二维表结构是非常贴近逻辑世界一个概念，关系模型相对网状、层次等其他模型来说更容易理解；
2、使用方便：通用的SQL语言使得操作关系型数据库非常方便；
3、易于维护：丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率；
4、支持SQL，可用于复杂的查询。
5.支持事务

缺点
1、为了维护一致性所付出的巨大代价就是其读写性能比较差；
2、固定的表结构；
3、不支持高并发读写需求；
4、不支持海量数据的高效率读写

数据库的三大范式

1.第一范式：确保每一列保持原子性

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。作用：提高数据库性能

2.第二范式（确保表中的每列都和主键相关）

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。 作用：减小数据库的冗余

3.第三范式（确保每列都和主键直接相关，而不是间接相关）

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。作用：减小数据冗余
要求一个数据表中不包含已在其他表中已包含的非主键信息。简单来说就是，一张数据表只能包含其他数据表的主键字段。

MySQL有几种锁

行级锁

(1) 描述

行级锁是mysql中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁和排他锁

(2) 特点

开销大，加锁慢，会出现死锁。发生锁冲突的概率最低，并发度也最高。

其实行级锁和页级锁之间还有其他锁粒度的锁，就是***间隙锁***和***临键锁***。

InnoDB有三种行锁的算法：

1，Record Lock（记录锁）：单个行记录上的锁。这个也是我们日常认为的行锁。

2，Gap Lock（间隙锁）：间隙锁，锁定一个范围，但不包括记录本身（只不过它的锁粒度比记录锁的锁整行更大一些，他是锁住了某个范围内的多个行，包括根本不存在的数据）。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。该锁只会在隔离级别是RR或者以上的级别内存在。间隙锁的目的是为了让其他事务无法在间隙中新增数据。

3，Next-Key Lock（临键锁）：它是记录锁和间隙锁的结合，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。next-key锁是InnoDB默认的锁

上面这三种锁都是排它锁（X锁）

next-key lock的效果相当于一个记录锁加一个间隙锁。当next-key lock加在某索引上，则该记录和它前面的区间都被锁定。

假设有记录1, 3, 5, 7，现在记录5上加next-key lock，则会锁定区间(3, 5]，任何试图插入到这个区间的记录都会阻塞。

注意，由于其效果相当于(3, 5)上的gap lock加5上的record lock，而且gap lock是****可重入的****，相互不阻塞的(上文讲过)，当其它事务试图获取(3, 5)的gap lock时，不会被阻塞；但如果要获取5上的record lock，就会阻塞；如果要获取5上的next-key lock，同样会阻塞。

record lock、gap lock、next-key lock，都是加在索引上的。假设有记录1,3,5,7，则5上的记录锁会锁住5，5上的gap lock会锁住(3,5)，5上的next-key lock会锁住(3,5]。

表级锁

(1) 描述

表级锁是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分mysql引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

(2) 特点

开销小，加锁快，不会出现死锁。发生锁冲突的概率最高，并发度也最低。

• LOCK TABLE my_table_name READ; 用读锁锁表，会阻塞其他事务修改表数据。
• LOCK TABLE my_table_name WRITE; 用写锁锁表，会阻塞其他事务读和写。

****MyISAM****在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

但是在InnoDB中如果需要表锁就需要显式地声明了。

页级锁

(1) 描述

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录。BDB 支持页级锁。

(2) 特点

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

自增锁

自增锁是一种特殊的表级锁，主要用于事务中插入自增字段，也就是我们最常用的自增主键id。通过innodb_autoinc_lock_mode参数可以设置自增主键的生成策略。防止并发插入数据的时候自增id出现异常。

当一张表的某个字段是自增列时，innodb会在该索引的末位加一个排它锁。为了访问这个自增的数值，需要加一个表级锁，不过这个表级锁的持续时间只有当前sql，而不是整个事务，即当前sql执行完，该表级锁就释放了。其他session无法在这个表级锁持有时插入任何记录。

mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

• user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
• db权限表：记录各个帐号在各个数据库上的操作权限。
• table_priv权限表：记录数据表级的操作权限。
• columns_priv权限表：记录数据列级的操作权限。
• host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

Mysql执行计划

当我们的系统上线后数据库的记录不断增加，之前写的一些SQL语句或者一些ORM操作效率变得非常低。我们不得不考虑SQL优化，SQL优化大概是这样一个流程：1.定位执行效率低的SQL语句（定位）,2.分析为什么这段SQL执行的效率比较低（分析），3.最后根据第二步分析的结构采取优化措施（解决）。而EXPLAIN命令的作用就是帮助我们分析SQL的执行情况，属于第二步。说的规范一点就是:EXPLAIN命令是查看查询优化器如何决定执行查询的主要的方法。学会解释EXPLAIN将帮助我们了解SQL优化器是如何工作的。执行计划可以告诉我们SQL如何使用索引，连接查询的执行顺序，查询的数据行数。

下面是一个简单EXPLAIN的结果

ID列

是一位数字，表示执行SELECT语句的顺序

id值相同执行顺序从上到下

id值不同时id值大的先执行

SELECT_TYPE

TABLE

输入数据行所在的表名称

PARTITIONS

对于分区表，显示查询的分区ID，对于非分区表，显示为NULL

TYPE

EXTRA

POSSIBLE_KEYS

指出MySQL能使用哪些索引来优化查询，查询所涉及的列上的索引都会被列出，但不一定会被使用

KEY

查询优化器优化查询实际所使用的索引，如果没有可用的索引，则显示为NULL，如查询使用了覆盖索引，则该索引仅出现在Key列中

KEY_LEN

表示索引字段的最大可能长度，KEY_LEN的长度由字段定义计算而来，并非数据的实际长度

REF

表示哪些列或常量被用于查找索引列上的值

ROWS

表示MySQL通过哪些列或常量被用于查找索引列上的值，ROWS值的大小是个统计抽样结果，并不十分准确

Filtered

表示返回结果的行数占需读取行数的百分比，Filter列的值越大越好

MySQL覆盖索引与回表

mysql覆盖索引与回表

SQL语句执行的很慢的原因有哪些

大多数情况是正常的，只是偶尔出现很慢的情况

• 数据库在刷新脏页

  当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。

  不过，redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。

• 拿不到锁

  这个就比较容易想到了，我们要执行的这条语句，刚好这条语句涉及到的表，别人在用，并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁，但要使用到的某个一行被加锁了，这个时候，我也没办法啊。如果要判断是否真的在等待锁，我们可以用 show processlist这个命令来查看当前的状态哦，这里我要提醒一下，有些命令最好记录一下，反正，我被问了好几个命令，都不知道怎么写，呵呵。

针对一直都这么慢的情况

• 查询的字段没有索引

  mysql> CREATE TABLE `t` (
    `id` int(11) NOT NULL,
    `c` int(11) DEFAULT NULL,
    `d` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  
  select * from t where 100 <c and c < 100000;
  

• 字段有索引，但却没有用索引

  假如给C字段建立了索引，但sql语句是这样的也解决不了问题

  select * from t where c - 1 = 1000;
  

  我想问大家一个问题，这样子在查询的时候会用索引查询吗？

  答是不会，如果我们在字段的左边做了运算，那么很抱歉，在查询的时候，就不会用上索引了，所以呢，大家要注意这种字段上有索引，但由于自己的疏忽，导致系统没有使用索引的情况了。

  正确的查询应该如下

  select * from t where c = 1000 + 1;
  

总结

一个 SQL 执行的很慢，我们要分两种情况讨论：

1、大多数情况下很正常，偶尔很慢，则有如下原因

(1)、数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。

(2)、执行的时候，遇到锁，如表锁、行锁。

2、这条 SQL 语句一直执行的很慢，则有如下原因。

(1)、没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。

(2)、数据库选错了索引。

MySQL中使用LIMIT进行分页的方法

一、分页需求：

客户端通过传递start(页码)，pageSize(每页显示的条数)两个参数去分页查询数据库表中的数据，那我们知道MySql数据库提供了分页的函数limit m,n，但是该函数的用法和我们的需求不一样，所以就需要我们根据实际情况去改写适合我们自己的分页语句，具体的分析如下：

比如：

查询第1条到第10条的数据的sql是：select * from table limit 0,10; ->对应我们的需求就是查询第一页的数据：select * from table limit (1-1)*10,10;

查询第11条到第20条的数据的sql是：select * from table limit 10,10; ->对应我们的需求就是查询第二页的数据：select * from table limit (2-1)*10,10;

查询第21条到第30条的数据的sql是：select * from table limit 20,10; ->对应我们的需求就是查询第三页的数据：select * from table limit (3-1)*10,10;

二、总结：

通过上面的分析，可以得出符合我们需求的分页sql格式是：select * from table limit (start-1)*pageSize,pageSize; 其中start是页码，pageSize是每页显示的条数。