mysql pdo 插入没效果_Mysql面试整理

微信公众号：PHP在线

php 远程获取文件
第一种：file_get_contents

$url = '![](file:///C:\Users\ASUS\AppData\Roaming\Tencent\QQ\Temp\%W@GJ$ACOF(TYDYECOKVDYB.png)http://www.xxx.com/';
$contents = file_get_contents($url)

第二种使用 curl

$url = “![](file:///C:\Users\ASUS\AppData\Roaming\Tencent\QQ\Temp\%W@GJ$ACOF(TYDYECOKVDYB.png)http://www.xxx.com/”;
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, $timeout);\
$contents = curl_exec($ch);

第三种
fopen->fread->fclose

$handle = fopen (“![](file:///C:\Users\ASUS\AppData\Roaming\Tencent\QQ\Temp\%W@GJ$ACOF(TYDYECOKVDYB.png)http://www.xxx.com/”, “rb”);
$data = fread($handle, 8192)

一般我们使用  curl 来进行 文章的抓取   他可以设置 超时时间  比较方便

怎么解决请求被劫持的问题

推荐用 https，充分测试无问题以后在服务器端配置 HSTS 头，但即使这样也还不能解决首次访问时的劫持问题，不过已经能解决绝大部分的问题了。如果是个人网站，建议直接用 sha2 的证书，sha1 的证书已经不安全了，双证书费用和维护成本都不低，何况第三方浏览器现在是流量的大头

用户输入 url 到页面显示经历了哪些

通过 DNS 找对应的 IP

找浏览器缓存，浏览器会保存一段时间你之前访问过的一些网址的 DNS 信息
通过 dns 找  域 名对应 ip  本地的  host   通过 dns 找  域名对应 ip
接着会发送一个请求到路由器上，然后路由器在自己的路由器缓存上查找记录，路由器一 般也存有 DNS 信息。
通过 dns 找  域名对应 ip   你的 ISP 的 DNS 服务器会将请求发向根域名服务器进行搜通过 dns 找  域名对应 ip

通过 IP 向对应的 web 服务器发送请求

浏览器终于得到了 IP 以后，浏览器接着给这个 IP 的服务器发送了一个 http 请求，方式为 get，例如访问 nbut.cn

服务器接受到请求后，如果是 nginx 通过 nginx 的 location 匹配 后缀是.php 的文件， 然后如果是，则将这个请求转发到 127.0.0.1:9000 的个服务，而 9000 这个服务是 PHP， 把请求交给 php 来进行处理，php 处理完毕，把处理的数据发送给 nginx ,nginx 把数据再 相应，并发送给浏览器

服务器收到浏览器的请求以后(其实是 WEB 服务器接收到了这个请求，WEB 服务器有 iis、 apache 等 会解析这个请求(读请求头)，然后生成一个响应头和具体响应内容 如果是个静态页面，那么基本上到这一步就没了

页面还有其他资源(css img js ) 继续重复执行

主页(index)页面框架传送过来以后，浏览器还要继续向服务器发送请求，请求的内容是 主页里面包含的一些资源，如图片，视频，css 样式等等

mysql

建表，修改字段，字段类型

链接: 博客：MySQL 细致总结之基础篇

事务

事务(transaction)是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。

事务控制语句

BEGIN 或 START TRANSACTION 显式地开启一个事务；
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier 把事务回滚到标记点；
SET TRANSACTION 用来设置事务的隔离级别。
InnoDB 存储引擎提供事务的隔离级别有 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

事务的处理方法

用 BEGIN, ROLLBACK, COMMIT 来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
例

php 实现事务

mysql_query("COMMIT");//提交事务
mysql_query("ROLLBACK");//至少有一条sql语句执行错误，事务回滚
mysql_query("END");//事务结束

mysqli

$conn = mysqli_connect('127.0.0.1', 'root', 'root') or die(mysqli_error());  //连接数据库 
mysqli_query($conn, 'BEGIN');    //开启事务
mysqli_query($conn, 'COMMIT');    //提交事务
mysqli_query($conn, 'rollBack');    //回滚事务

php 的 PDO 的实现方式 $pdo 为实例化 PDO 对象

//1.开启事务 $pdo->beginTransaction();
//提交事务  $pdo->commit();
//回滚事务  $pdo->rollBack();
//结束事务  $pdo->end();

TP 实现事务
无 model

// 启动事务  Db::startTrans();
// 提交事务  Db::commit();  
// 回滚事务  Db::rollback();

有 model

$model=M('myself_audio');
//事务开启  $model->startTrans();
//事务提交  $model->commit();
// 事务回滚   $model->rollback();

laravel 框架实现事务

想要在一个数据库事务中运行一连串操作，可以使用 DB 门面的 transaction 方法，如果事务闭包中抛出异常，事务将会自动回滚。如果闭包执行成功，事务将会自动提交。

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);
    DB::table('posts')->delete();
});
手动使用事务

DB::beginTransaction();  //开启事务
DB::commit();  //事务提交
DB::rollBack();  // 事务回滚 

四大特性

原子性(Autmic)：事务在执行性，要做到 “要么不做，要么全做！”，就是说不允许事务部分得执行。即使因为故障而使事务不能完成，在 rollback 时也要消除对数据库得影响！
一致性(Consistency)：事务得操作应该使使数据库从一个一致状态转变倒另一个一致得状态！就拿网上购物来说吧，你只有即让商品出库，又让商品进入顾客得购物篮才能构成事务！
隔离性(Isolation)：如果多个事务并发执行，应象各个事务独立执行一样！
持久性(Durability)：一个成功执行得事务对数据库得作用是持久得，即使数据库应故障出错，也应该能够恢复！

四种隔离级别

读未提交(read-uncommitted)所有事务都可以看到其他未提交事务的执行结果。很少用于实际应用，因为它的性能也不比其他级别好多少。会产生脏读，不可重复读以及幻读
不可重复读(read-committed)这是大多数数据库系统的默认隔离级别(但不是 MySQL 默认的)。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。会产生不可重复读，幻读
可重复读(repeatable-read)这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，会导致另一个棘手的问题：幻读 (Phantom Read)。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的 “幻影” 行。InnoDB 和 Falcon 存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了该问题。
串行化(serializable)这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

操作

查看当前会话隔离级别

select @@tx_isolation; 

查看系统当前隔离级别

select @@global.tx_isolation; 

设置当前会话隔离级别

set session transaction isolatin level repeatable read; 

设置系统当前隔离级别

set global transaction isolation level repeatable read; 

命令行，开始事务时

set autocommit=off 或者 start transaction

锁机制

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU、RAM、I/O)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

mysql 的三种锁

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

表级锁的锁模式(MyISAM)

MySQL 表级锁有两种模式：表共享锁(Table Read Lock)和表独占写锁(Table Write Lock)。
对 MyISAM 的读操作，不会阻塞其他用户对同一表请求，但会阻塞对同一表的写请求；
对 MyISAM 的写操作，则会阻塞其他用户对同一表的读和写操作；
MyISAM 表的读操作和写操作之间，以及写操作之间是串行的。
当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
对ＭyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；对ＭyISAM 表的写操作，则会阻塞其他用户对同一表的读和写请求；ＭyISAM 表的读和写操作之间，以及写和写操作之间是串行的！(当一线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。)

如何加表锁

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

一些命令

查询表级锁争用情况

show status like 'table%';

如何加表锁

加读锁 / 表共享锁(Table Read Lock)：

lock table tbl_name read;

加写锁 / 表独占写锁(Table Write Lock)：

lock table tbl_name write;

释放锁：

unlock tables;

并发锁

在一定条件下，MyISAM 也支持查询和操作的并发进行。
MyISAM 存储引擎有一个系统变量 concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为 0、1 或 2。
当 concurrent_insert 设置为 0 时，不允许并发插入。
当 concurrent_insert 设置为 1 时，如果 MyISAM 允许在一个读表的同时，另一个进程从表尾插入记录。这也是 MySQL 的默认设置。
当 concurrent_insert 设置为 2 时，无论 MyISAM 表中有没有空洞，都允许在表尾插入记录，都允许在表尾并发插入记录。
可以利用 MyISAM 存储引擎的并发插入特性，来解决应用中对同一表查询和插入锁争用。例如，将 concurrent_insert 系统变量为 2，总是允许并发插入；同时，通过定期在系统空闲时段执行 OPTIONMIZE TABLE 语句来整理空间碎片，收到因删除记录而产生的中间空洞。

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 锁

并发问题

1、脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。
3、幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
4、更新丢失(Lost Update)：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更 新覆盖了由其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文 档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。
不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

查看 InonoD 行锁争用情况

show status like 'innodb_row_lock%';

打开监视器

CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
Show innodb status\G;

停止监视器

DROP TABLE innodb_monitor;

打开监视器以后，默认情况下每 15 秒会向日志中记录监控的内容，如果长时间打开会导致.err 文件变得非常的巨大，所以用户在确认问题原因之后，要记得删除监控表以关闭监视器，或者通过使用 “--console” 选项来启动服务器以关闭写日志文件。

锁机制

共享锁和排它锁

InnoDB 实现了标准的行级锁，有两种类型：共享锁(S 锁)允许事务持有锁并读取一行记录和排它锁(X 锁)允许事务持有并更新或者删除一行记录。
如果事务 T1 持有行记录 r 的上一个共享 (S) 锁，接着另一个事务 T2 请求行记录 r 上的锁
如果事务 T1 持有 r 上的排它 (S) 锁，事务 T2 请求任意一个锁都不立刻获得。此时，T2 必须等待 T1 释放 r 上的锁。
上共享锁的写法：lock in share mode

例如：

select  math from zje where math>60 lock in share mode；

上排它锁的写法：for update
例如：

select math from zje where math >60 for update；

注意

行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。
两个事务不能锁同一个索引，例如：
事务 A 先执行：

select math from zje where math>60 for update;

事务 B 再执行：

select math from zje where math<60 for update；

这样的话，事务 B 是会阻塞的。如果事务 B 把 math 索引换成其他索引就不会阻塞，
但注意，换成其他索引锁住的行不能和 math 索引锁住的行有重复。
insert ，delete ， update 在事务中都会自动默认加上排它锁。

意向锁

InnoDB 支持多粒度的锁，多粒度的锁允许行锁和表锁共存。为了在实际中实现多粒度的锁，使用了另外一种类型的锁：意向锁。在 InnoDB 中，意向锁是表级锁，它标明了一个事务在将在表中行记录上使用锁的类型 (共享锁和排它锁)。在 InnoDB 中使用了两种意向锁(假设事务 T 请求了表 t 上的锁)
意向共享锁 (IS): 事务 T 打算在表 t 的某些行设置 S 共享锁。
意向排它锁 (IX)：：事务 T 打算在表 t 的某些行设置 T 排他锁。
意向锁的协议如下：
一个事务获得表 t 中某行的共享锁之前，必须先获得 t 表上的 IS 意向共享锁或者更强类型的锁。
一个事务获得表 t 中某行的排他锁之前，必须先获得 t 表上的 IX 意向排它锁。
IX 意向共享锁和 IS 意向排它锁的主要目的是显示有人锁定了一行记录或者准备锁定一行记录。

记录锁

记录锁是索引记录上的锁。SELECT c1 FOR UPDATE FROM t WHERE c1 = 10; 可以防止任何其它事务插入、 更新或删除 t.c1 等于 10 的行。
记录锁锁定的是索引，即使表没有定义索引。这种情况下，InnoDB 会创建一个隐式的聚簇索引并将此索引用作记录锁。

间隙锁

间隙锁会锁定一个范围内的索引，或者是某索引记录之前或者之后的索引。例如，SELECT c1 FOR UPDATE FROM t WHERE c1 BETWEEN 10 and 20; 可以防止其他事务将一个 t.c1 等于 15 的值插入到表中，无论列中是否有该记录，因为该范围内所有可能存在的值都被锁定。
间隙有可能跨越单索引值，多个索引值，甚至空值。
在 InnoDB 中，间隙锁是 “完全被抑制” 的，意思是它只阻止其它事务给往间隙中插入。它们不阻止其他事务在同一个间隙上获得间隙锁。因此，一个间隙 X 锁和一个间隙 S 锁效果一样

Next-Key 锁

Next-Key 锁是索引记录上的记录锁与此索引记录之前间隙上的间隙锁两者的结合。InnoDB 在查找或扫描索引时使用行级锁，给遇到的索引记录上设置共享锁或者排它锁。因此，行级锁就是索引记录锁。一个索引记录上的 next-key 锁也影响在该索引之间的 “间隙”。也就是说，next-key 是一个索引记录锁加上在该索引记录之前间隙上的间隙锁。如果一个会话在记录 R 的索引上持有一个共享锁或者排它锁，另一个会话无法在在 R 的索引之前立刻插入一个新的索引记录。
InnoDB 使用 next-key 锁查找和扫描索引，阻止了幻行。

插入意向锁

插入意向锁时在插入行前设置的一种间隙锁。这个锁示意如果多个事务感兴趣的不是索引区间中的同一个位置，则事务在同一个索引区间插入不需要相互等待。假设有索引记录值为 4 和 7 的行。两个事务分别尝试插入 5 和 6，分别用插入意向锁锁住 4 和 7 之间的间隙，然后再取得插入行的排它锁，但是锁相互不会冲突，因为插入行没有冲突。

自动增长锁

自动增长锁是一个特殊的表级锁，事务持有它用于给带有 auto_increment 列的表插入数据。在最简单的情况下，如果一个事务正在给表中插入数据，其他的事务想要插入数据，必须等待，这样第一个事务才能插入具有连续主键的值。
innodb_autoinc_lock_mode 配置选项控制用于自动增长锁的算法。它允许你选择如何权衡可预测的自动增长值序列和最大的插入操作并发性

空间索引的预测锁

在 MySQL 5.7.5，InnoDB 支持包含空间数据列的空间索引
为了使得空间索引支持隔离级别，InoDB 使用预测锁。一个空间索引包含最小边界矩形 (MBR) 值，所以 InnoDB 通过在 MBR 上设置预测锁来查询数据，强制一致性读。其它事务不能插入或修改与查询条件匹配的行。

InnoDB 的行锁模式及加锁方法

InnoDB 的行锁有两种：共享锁(S)和排他锁(X)。为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁：意向共享锁和意向排他锁，这两种意向锁都是表锁。一个事务在给数据行加锁之前必须先取得对应表对应的意向锁。

两种类型的行锁。

共享锁(s)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁(Ｘ)：允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。
另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁(Intention Locks)，这两种意向锁都是表锁。

意向共享锁(IS)：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
意向排他锁(IX)：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
如果一个事务请求的锁模式与当前的锁兼容，InnoDB 就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。

意向锁是 InnoDB 自动加的，不需用户干预。对于 UPDATE、DELETE 和 INSERT 语句，InnoDB 会自动给涉及及数据集加排他锁(Ｘ)；对于普通 SELECT 语句，InnoDB 会自动给涉及数据集加排他锁(Ｘ)；对于普通 SELECT 语句，InnoDB 不会任何锁；事务可以通过以下语句显示给记录集加共享锁或排锁。
共享锁(Ｓ)：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁(X)：SELECT * FROM table_name WHERE … FOR UPDATE
释放锁：unlock tables;(会隐含提交事务)
用 SELECT .. IN SHARE MODE 获得共享锁，主要用在需要数据依存关系时确认某行记录是否存在，并确保没有人对这个记录进行 UPDATE 或者 DELETE 操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用 SELECT … FOR UPDATE 方式获取排他锁。

InnoDB 行锁实现方式

InnoDB 行锁是通过给索引上的索引项加锁来实现的，InnoDB 这种行锁实现特点意味着：
只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。
由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。(虽然使用的是不同的索引，但是如果记录已经被其他 session 锁定的话也是需要等待的。)
即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。

什么时候使用表锁

第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。

第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

当然，应用中这两种事务不能太多，否则，就应该考虑使用ＭyISAＭ表。

在 InnoDB 下 ，使用表锁要注意以下两点。

使用 LOCK TALBES 虽然可以给 InnoDB 加表级锁，但必须说明的是，表锁不是由 InnoDB 存储引擎层管理的，而是由其上一层ＭySQL Server 负责的，仅当 autocommit=0、innodb_table_lock=1(默认设置)时，InnoDB 层才能知道 MySQL 加的表锁，ＭySQL Server 才能感知 InnoDB 加的行锁，这种情况下，InnoDB 才能自动识别涉及表级锁的死锁；否则，InnoDB 将无法自动检测并处理这种死锁。
在用 LOCAK TABLES 对 InnoDB 锁时要注意，要将 AUTOCOMMIT 设为 0，否则ＭySQL 不会给表加锁；事务结束前，不要用 UNLOCAK TABLES 释放表锁，因为 UNLOCK TABLES 会隐含地提交事务；COMMIT 或 ROLLBACK 产不能释放用 LOCAK TABLES 加的表级锁，必须用 UNLOCK TABLES 释放表锁，正确的方式见如下语句。

总结

Ｍ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 MVCC

MVCC (Multiversion Concurrency Control)，即多版本并发控制技术，它使得大部分支持行锁的事务引擎，不再单纯的使用行锁来进行数据库的并发控制，取而代之的是把数据库的行锁与行的多个版本结合起来，只需要很小的开销，就可以实现非锁定读，从而大大提高数据库系统的并发性能
innodb MVCC 主要是为 Repeatable-Read (可重复读) 事务隔离级别做的。在此隔离级别下，A、B 客户端所示的数据相互隔离，互相更新不可见
具体的执行拿 select,insert,update 和 delete 来说，
Select Innodb 检查每行数据，确保他们符合两个标准：
1、InnoDB 只查找版本早于当前事务版本的数据行 (也就是数据行的版本必须小于等于事务的版本)，这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行
2、行的删除操作的版本一定是未定义的或者大于当前事务的版本号，确定了当前事务开始之前，行没有被删除
符合了以上两点则返回查询结果。
INSERT InnoDB 为每个新增行记录当前系统版本号作为创建 ID。
DELETE InnoDB 为每个删除行的记录当前系统版本号作为行的删除 ID。
UPDATE InnoDB 复制了一行。这个新行的版本号使用了系统版本号。它也把系统版本号作为了删除行的版本。

MYSQL 死锁

产生的原因

#竞争资源

产生死锁中的竞争资源之一指的是竞争不可剥夺资源 [当系统把这类资源分配给某进程后，再不能强行收回，只能在进程用完后自行释放，如磁带机、打印机等](例如：系统中只有一台打印机，可供进程 P1 使用，假定 P1 已占用了打印机，若 P2 继续要求打印机打印将阻塞)
产生死锁中的竞争资源另外一种资源指的是竞争临时资源 [指某进程在获得这类资源后，该资源可以再被其他进程或系统剥夺，CPU 和主存均属于可剥夺性资源] (临时资源包括硬件中断、信号、消息、缓冲区内的消息等)，通常消息通信顺序进行不当，则会产生死锁

进程间推进顺序非法

若 P1 保持了资源 R1,P2 保持了资源 R2，系统处于不安全状态，因为这两个进程再向前推进，便可能发生死锁

产生的必要条件

互斥条件：进程要求对所分配的资源进行排它性控制，即在一段时间内某资源仅为一进程所占用。
请求和保持条件：当进程因请求资源而阻塞时，对已获得的资源保持不放。
不剥夺条件：进程已获得的资源在未使用完之前，不能剥夺，只能在使用完时由自己释放。
环路等待条件：在发生死锁时，必然存在一个进程 -- 资源的环形链。

预防死锁

资源一次性分配：一次性分配所有资源，这样就不会再有请求了：(破坏请求条件)
只要有一个资源得不到分配，也不给这个进程分配其他的资源：(破坏请保持条件)
可剥夺资源：即当某进程获得了部分资源，但得不到其它资源，则释放已占有的资源(破坏不可剥夺条件)
资源有序分配法：系统给每类资源赋予一个编号，每一个进程按编号递增的顺序请求资源，释放则相反(破坏环路等待条件)

避免死锁

预防死锁的几种策略，会严重地损害系统性能。因此在避免死锁时，要施加较弱的限制，从而获得 较满意的系统性能。由于在避免死锁的策略中，允许进程动态地申请资源。因而，系统在进行资源分配之前预先计算资源分配的安全性。若此次分配不会导致系统进入不安全的状态，则将资源分配给进程；否则，进程等待。其中最具有代表性的避免死锁算法是银行家算法。
银行家算法：首先需要定义状态和安全状态的概念。系统的状态是当前给进程分配的资源情况。因此，状态包含两个向量 Resource(系统中每种资源的总量)和 Available(未分配给进程的每种资源的总量)及两个矩阵 Claim(表示进程对资源的需求)和 Allocation(表示当前分配给进程的资源)。安全状态是指至少有一个资源分配序列不会导致死锁。当进程请求一组资源时，假设同意该请求，从而改变了系统的状态，然后确定其结果是否还处于安全状态。如果是，同意这个请求；如果不是，阻塞该进程知道同意该请求后系统状态仍然是安全的。

检测死锁

首先为每个进程和每个资源指定一个唯一的号码；
然后建立资源分配表和进程等待表。

解除死锁

剥夺资源：从其它进程剥夺足够数量的资源给死锁进程，以解除死锁状态；
撤消进程：可以直接撤消死锁进程或撤消代价最小的进程，直至有足够的资源可用，死锁状态。消除为止；所谓代价是指优先级、运行代价、进程的重要性和价值等。

索引

是什么

官方对索引的定义

索引 (Index) 是帮助 MySQL 高效获取数据的数据结构。我们可以简单理解为：快速查找排好序的一种数据结构。Mysql 索引主要有两种结构：B+Tree 索引和 Hash 索引。我们平常所说的索引，如果没有特别指明，一般都是指 B 树结构组织的索引 (B+Tree 索引)。

存储引擎

MyISAM 存储引擎

.frm 后缀的文件存储的是表结构。
.myd 后缀的文件存储的是表数据。
.myi 后缀的文件存储的就是索引文件。
InnoDB 存储引擎

.frm 后缀的文件存储的是表结构。
.ibd 后缀的文件存放索引文件和数据 (需要开启 innodb_file_per_table 参数)

总结

索引是按照特定的数据结构把数据表中的数据放在索引文件中，以便于快速查找；
索引存在于磁盘中，会占据物理空间。

索引的类型

B-Tree 索引

以 B-Tree 为结构的索引是最常见的索引类型，比如 InnoDB 和 MyISAM 都是以 B-Tree 为索引结构的索引，事实上是以 B+ Tree 为索引结构，B-Tree 和 B+Tree 区别在于，B+ Tree 在叶子节点上增加了顺序访问指针，方便叶子节点的范围遍历.

InnoDB

InnoDB 支持聚簇索引，聚簇索引和非聚簇索引严格来说不是一种索引，而是一种数据存储方式，这个名字跟它本身的存储方式有关系，“聚簇 “表示数据行和相邻的键值存储在一起，简单的说，就是叶子节点中存储的实际是真实的数据。InnoDB 通过主键聚集数据，所以一个表只能有一个聚簇索引，且必须有主键，如果没有定义主键，且不存在非空索引可以代替，InnoDB 会隐式定义一个主键作为聚簇索引。
聚簇索引的二级索引存储的不是指向行的物理位置的指针，而是行的主键值，所以如果通过二级索引查找行，需要找到二级索引的叶子结点获得对应的主键值，然后再去查找对应的行。对于 InnoDB，自适应哈希索引可以减少这样的重复工作。

锁

InnoDB 使用的是行锁，所以支持事务，而 MyISAM 使用的是表锁，不支持事务。
适用范围
B-Tree 索引适用于区间查询，因为 B-Tree 存储后的叶子节点本身就是有序的，并且 B+ Tree 结构还增加了叶子节点的连续顺序指针，对于区间查询来说就更加方便了。

哈希索引

哈希索引是基于哈希表实现的，只有精确匹配索引所有列的查询才有效。方法是，对所有的索引列计算一个 hash code，hash code 作为索引，在哈希表中保存指向每个数据行的指针。

优点

索引本身只存储 hash code，所以结构很紧凑，并且查找速度很快

限制

索引中的 hash code 是顺序存储的，但是 hash code 对应的数据并不是顺序的，所以无法用于排序
不支持部分索引列匹配查找，因为哈希索引是使用索引列的全部内容来计算 hash code
只支持等值比较，不支持范围查询
如果哈希冲突严重时，必须遍历链表中所有行指针
哈希冲突严重的话，索引维护操作的代价也很高
InnoDB 的自适应哈希索引

自适应哈希索引对于用户来说是无感知的，这是一个完全自动、内部的行为，用户无法控制或者配置，但是可以关闭。
当 InnoDB 注意到某个索引值被使用的非常频繁时，它会在内存中基于 B-Tree 索引之上再创建一个哈希索引，这样 B-Tree 也可以具有哈希索引的一些优点，比如快速的哈希查找。
当然如果存储引擎不支持哈希索引，用户也可以自定义哈希索引，这样性能会比较高，缺陷是需要自己维护哈希值，如果采用这种方法，不要使用 SHA1 () 和 MD5 () 作为哈希函数，因为这两个是强加密函数，设计目标是最大限度消除冲突，生成的 hash code 是一个非常长的字符串，浪费大量的空间，哈希索引中对于索引的冲突要求没有那么高。

索引的存储结构

MySQL 支持在所有关系数据库表中创建主键、唯一键、不唯一的非主码索引等多种类型的索引。此外 MySQL 还支持纯文本和空间索引类型。
MySQL 内置的存储引擎对各种索引技术有不同的实现方式，包括：B - 树，B + 树，R - 树以及散列类型。

B - 树

B - 树中有两种节点类型：索引节点和叶子节点。叶子节点是用来存储数据的，而索引节点则用来告诉用户存储在叶子节点中的数据顺序，并帮助用户找到相应的数据。
B - 树的搜索，从根节点开始，对节点内的关键字有序进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子节点，重复。直到所对应的儿子指针为空，或已经是叶子节点。
B - 树是一种多路搜索树：
定义任意非叶子节点最多有 M 个儿子，且 M>2;
根节点的儿子数为 [2,M];
除根节点以外的非叶子节点的儿子数为 [M/2,M];
每个节点存放至少 M/2-1 (取上整) 和至多 M-1 个关键字；
非叶子节点的关键字个数 = 指向儿子节点的指针的个数 - 1;
非叶子节点的关键字：k [i]非叶子节点的指针：p [1]，p [2]，・・・・・，p [M]；其中 p [1] 指向的关键字小于 k [1] 的子树，p [M] 指向的关键字大于 K [m-1] 的子树；
所有的叶子节点位于同一层；

B + 树

B + 树数据结构是 B - 树实现的增强版本。尽管 B + 树支持 B - 树索引的所有特性，它们之间最显著的不同点在于 B + 树中底层数据是根据被提及的索引列进行排序的。B + 树还通过叶子节点之间的附加引用来优化扫描性能。
B + 搜索和 B - 搜索不同，区别是 B + 树只有达到叶子节点才命中(B - 树可以在非叶子节点命中)，其性能等价于关键字全集做一次二分搜索。
B + 树的特性：
所有关键字都出现在叶子节点的链表中，叶子节点相当于存储数据的数据层。
不可能在非叶子节点上命中。
非叶子节点相当于是叶子节点的索引，叶子节点相当于数据层。

散列

散列表数据结构是一种很简单的概念，它将一种算法应用到给定值中以在底层数据存储系统中返回一个唯一的指针或位置。散列表的优点是始终以线性时间复杂度找到需要读取的行的位置，而不像 B - 树那样需要横跨多层节点来确定位置。

通信 R - 树

R - 树数据结构支持基于数据类型对几何数据进行管理。目前只有 MyISAM 使用 R - 树实现支持空间索引，使用空间索引也有很多限制，比如只支持唯一的 NOT NULL 列等。

全文本

全文本结构也是一种 MySQL 采用的基本数据结构。这种数据结构目前只有当前版本 MySQL 中的 MyISAM 存储引擎支持。5.6 版本将要在 InnoDB 存储引擎中加入全文本功能。全文本索引在大型系统中并没有什么实用的价值，因为大规模系统有很多专门的文件检索产品

为什么快

不加索引，会比较整个数据库，因为他不知道数据是不是规律的。添加了索引，相当于加了一个目录，给索引字段排序，比较的时候只用几次就可以查找到你需要的数据。数据越多，索引约有用。也可以说拿空间换时间。
索引对性能的影响：
大大减少服务器需要扫描的数据量。
帮助服务器避免排序和临时表。将随机 I/O 变顺序 I/O。
大大提高查询速度，降低写的速度、占用磁盘。

主键与唯一索引

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
主键一定是唯一性索引，唯一性索引并不一定就是主键。主键列在创建时，已经默认为空值 + 唯一索引了。
一个表中可以有多个唯一性索引，但只能有一个主键。
主键可以被其他表引用为外键，而唯一索引不能。
主键列不允许空值，而唯一性索引列允许空值。
索引可以提高查询的速度，而主键不能。
主键和索引都是键，不过主键是逻辑键，索引是物理键，意思就是主键不实际存在，而索引实际存在在数据库中，会占用磁盘。
主键一般都要建，主要是用来避免一张表中有相同的记录，索引一般可以不建，但如果需要对该表进行查询操作，则最好建，这样可以加快检索的速度。

使用

创建索引
添加 PRIMARY KEY(主键索引)

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

添加 UNIQUE (唯一索引)

mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column`  ) 

添加 INDEX (普通索引)

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 

添加 FULLTEXT (全文索引)

mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

添加多列索引

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

修改索引

ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 约束条件
alter table table_name change id user_id int not null  //把字段 id 换成 user_id

删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

查看索引

mysql> show index from tblname;
mysql> show keys from tblname;

优点

使用索引可以减少服务器需要扫描的数据量
使用索引可以帮助服务器避免排序和临时表
使用索引可以将随机 I/O 变为顺序 I/O
但是不是所有情况下，索引都是最好的解决方案，对于非常小的表来说，大部分情况下简单的全表扫描更高效，对于中到大型表，索引就比较有效，对于特大型的表来说，分区会更加有效。

索引的优化

联合索引最左前缀原则

复合索引遵守「最左前缀」原则，查询条件中，使用了复合索引前面的字段，索引才会被使用，如果不是按照索引的最左列开始查找，则无法使用索引。
比如在 (a,b,c) 三个字段上建立联合索引，那么它能够加快 a|(a,b)|(a,b,c) 三组查询的速度，而不能加快 b|(b,a) 这种查询顺序。
另外，建联合索引的时候，区分度最高的字段在最左边。

不要在列上使用函数和进行运算

不要在列上使用函数，这将导致索引失效而进行全表扫描。

例如下面的 SQL 语句：

select * from artile where YEAR(create_time) <= '2018';

即使 date 上建立了索引，也会全表扫描，可以把计算放到业务层，这样做不仅可以节省数据库的 CPU，还可以起到查询缓存优化效果。

负向条件查询不能使用索引

负向条件有：!=、<>、not in、not exists、not like 等。

select * from artile where status != 1 and status != 2; 

可以使用 in 进行优化：

select * from artile where status in (0,3) 

使用覆盖索引

所谓覆盖索引，是指被查询的列，数据能从索引中取得，而不用通过行定位符再到数据表上获取，能够极大的提高性能。
可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。

避免强制类型转换

当查询条件左右两侧类型不匹配的时候会发生强制转换，强制转换可能导致索引失效而进行全表扫描。
如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引：

select * from user where phone=12345678901; 

可以优化为：

select * from user where phone='12345678901'; 

范围列可以用到索引

范围条件有：、>=、between 等。
范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。
更新频繁、数据区分度不高的字段上不宜建立索引

更新会变更 B + 树，更新频繁的字段建立索引会大大降低数据库性能。
「性别」这种区分度不大的属性，建立索引没有意义，不能有效过滤数据，性能与全表扫描类似。
区分度可以使用 count (distinct (列名))/count (*) 来计算，在 80% 以上的时候就可以建立索引。
索引列不允许为 null

单列索引不存 null 值，复合索引不存全为 null 的值，如果列允许为 null，可能会得到不符合预期的结果集。

避免使用 or 来连接条件

应该尽量避免在 where 子句中使用 or 来连接条件，因为这会导致索引失效而进行全表扫描，虽然新版的 MySQL 能够命中索引，但查询优化耗费的 CPU 比 in 多。

模糊查询

前导模糊查询不能使用索引，非前导查询可以。

MySQL 实现

MyISAM 的 B - 树

MyISAM 存储引擎使用 B - 树数据结构来实现主码索引、唯一索引以及非主码索引。在 MyISAM 实现数据目录和数据库模式子目录中，用户可以找到和每个 MySQL 表对应的.MYD 和.MYI 文件。数据库表上定义的索引信息就存储在 MYI 文件中，该文件的块大小是 1024 字节。这个大小是可以通过 myisam-block-size 系统变量分配。
在 MyISAM 中，非主码索引的 B - 树结构存储索引值和一个指向主码数据的指针，这是 MyISAM 和 InnoDB 的一个显著区别。这一点导致了两个存储引擎的索引的不同工作方式。
MyISAM 索引是在内存的一个公共缓存中管理的，这个缓存的大小可以通过 key_buffer_size 或者其他命名键缓存来定义。这是根据统计和规划的表索引的大小来设定缓存大小时主要的考虑因素。

InnoDB 的 B + 树聚簇主码

InnoDB 存储引擎在它的主码索引(也被称为聚簇主码)中使用了 B + 树，这种结构把所有数据都和对应的主码组织在一起，并且在叶子节点这一层上添加额外的向前和向后的指针，这样就可以更方便地进行范围扫描。
在文件系统层面，所有 InnoDB 数据和索引信息都默认在公共 InnoDB 表空间中管理，否则管理员就通过 innodb_data_file_path 这个变量指定文件路径。这是一个叫 ibdatal 文件。
由于 InnoDB 用聚簇主码存储数据，底层信息占用的磁盘空间的大小很大程度上取决于页面的填充因子。对于按序排列的主码，InnoDB 会用 16K 页面的 15/16 作为填充因子。对于不是按序排列的主码，默认情况下 InnoDB 会插入初始数据的时候为每一个页面分配 50% 作为填充因子。
在改索引的实现方式中 B + 树的叶子节点上是 data 就是数据本身，key 为主键，如果是一般索引的话，data 便会指向对应的主索引。在 B + 树的每一个叶子节点上面增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的 B + 树。其目的是提高区间访问的性能。

InnoDB 的 B - 树非主码

InnoDB 中的非主码索引使用了 B - 树数据结构，但 InnoDB 中的 B - 树结构实现和 MyISAM 中并不一样。在 InnoDB 中，非主码索引存储的是主码的实际值。而 MyISAM 中，非主码索引存储的包含主码值的数据指针。这一点很重要。首先，当定义很大的主码的时候，InnoDB 的非主码索引可能回更大，随着非主码索引数量的增加，索引之间大小差别可能会变得很大。另一个不同点在于非主码索引当前可以包含主键的值，并且可以不是索引必须有的部分。

内存散列索引

在默认 MySQL 的引擎索引中，只有 MEMORY 引擎支持散列数据结构，散列结构的强度可以表示为直接键查找的简单性，散列索引的相似度模式匹配查询比直接查询慢。也可以为 MEMORY 引擎指定一个 B - 树索引实现。

内存 B - 树索引

对于大型 MEMORY 表来说，使用散列索引进行索引范围搜索的效率很低，B - 树索引在执行直接键查询时确实比使用默认的散列索引快。根据 B - 树的不同深度，B - 树索引在个别操作中的确可能比散列算法快。

InnoDB 内部散列索引

InnoDB 存储引擎在聚簇 B + 树索引中存储主码：但在 InnoDB 内部还是使用内存中的散列表来更高效地进行主码查询。这个机制有 InnoDB 存储引擎来管理，用户只能通过 innodb_adaptive_hash_index 配置项来选择是否启用这个唯一的配置选项。