索引是数据库优化中最常用也是最重要的手段之一，通过索引可以帮助用户解决大多数的 SQL 性能问题。多数情况下，查询速度很慢时，加上索引便能解决问题。但也并非总是如此，因为优化不是件简单的事情。但是如果你不使用索引，在许多情况下，尝试通过其它途径来提高性能都纯粹是在浪费时间。应该首先使用索引来最大程度的改善性能，然后再看看是否还有其它有用的技术。索引提供了高效访问数据的方法，能够快速的定位表中的某条记录，加快数据库查询的速度，从而提高数据库的性能。如果查询时不使用索引，那么查询语句将查询表中的所有字段。这样查询的速度会很慢。使用索引进行查询，查询语句不必读完表中的所有记录，而只查询索引字段。这样可以减少查询的记录数，达到提高查询速度的目的。下面通过对比使用索引和不使用索引来分析索引对查询速度的影响。例 为了便于读者更好的理解，分析之前，我们先查询一下 tb_students_info 数据表中的记录，SQL 语句和运行结果如下：mysql> SELECT * FROM tb_students_info; +----+------+ | id | name | +----+------+ |  1 | 张三 | |  2 | 李四 | |  3 | 王五 | |  4 | 赵六 | |  5 | 周七 | |  6 | 吴八 | |  7 | 朱九 | |  8 | 苏十 | +----+------+ 8 rows in set (0.02 sec)使用 EXPLAIN 分析未使用索引时的查询情况，SQL 语句和运行结果如下：mysql> EXPLAIN SELECT * FROM tb_students_info WHERE name='张三' \G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_students_info    partitions: NULL          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 8      filtered: 12.50         Extra: Using where 1 row in set, 1 warning (0.00 sec)由结果可以看到，rows 列的值是 8，说明查询语句扫描了表中的 8 条记录。没有索引的表就相当于一组无序的行，如果我们想找到某条记录就必须检查表的每一行，看看它是否与那个期望值相匹配。这是一个全表扫描操作，其效率很低，如果表很大，而且仅有少数几条记录与搜索条件相匹配，那么整个扫描过程的效率将会超级低。在 tb_students_info 表的 name 字段添加索引，SQL 语句和运行结果如下：mysql> CREATE INDEX index_name ON tb_students_info(name); Query OK, 8 rows affected (0.14 sec)使用 EXPLAIN 再次执行上面的查询语句，SQL 语句和运行结果如下：mysql> EXPLAIN SELECT * FROM tb_students_info WHERE name='张三' \G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_students_info    partitions: NULL          type: ref possible_keys: index_name           key: index_name       key_len: 63           ref: const          rows: 1      filtered: 100.00         Extra: NULL 1 row in set, 1 warning (0.00 sec)结果显示，rows 列的值为 1，表示这个查询语句只扫描了表中的 1 条记录。创建索引后访问的行由 8 行减少到 1 行，其查询速度自然比扫描 8 条记录快。而且 possible_keys 和 key 的值都是 index_name，这说明查询时使用了 index_name 索引。所以，在查询操作中，使用索引不仅能自动优化查询效率，还会降低服务器的开销。注意：由于 tb_students_info 表中记录较少，所以在这没有分析运行时间。表中记录多时，运行时间的差异也会体现出索引对查询速度的影响。

分析了mysqld进程关闭的过程，以及如何安全、缓和地关闭MySQL实例，对这个过程不甚清楚的同学可以参考下。关闭过程：1、发起shutdown，发出  SIGTERM信号2、有必要的话，新建一个关闭线程(shutdown thread)如果是客户端发起的关闭，则会新建一个专用的关闭线程如果是直接收到 SIGTERM 信号进行关闭的话，专门负责信号处理的线程就会负责关闭工作，或者新建一个独立的线程负责这个事当无法创建独立的关闭线程时（例如内存不足），MySQL Server会发出类似下面的告警信息：Error: Can't create thread to kill server3、MySQL Server不再响应新的连接请求关闭TCP/IP网络监听，关闭Unix Socket等渠道4、逐渐关闭当前的连接、事务空闲连接，将立刻被终止；当前还有事务、SQL活动的连接，会将其标识为 killed，并定期检查其状态，以便下次检查时将其关闭；（参考 KILL 语法）当前有活跃事务的，该事物会被回滚，如果该事务中还修改了非事务表，则已经修改的数据无法回滚，可能只会完成部分变更；如果是Master/Slave复制场景里的Master，则对复制线程的处理过程和普通线程也是一样的；如果是Master/Slave复制场景里的Slave，则会依次关闭IO、SQL线程，如果这2个线程当前是活跃的，则也会加上 killed 标识，然后再关闭；Slave服务器上，SQL线程是允许直接停止当前的SQL操作的（为了避免复制问题），然后再关闭该线程；在MySQl 5.0.80及以前的版本里，如果SQL线程当时正好执行一个事务到中间，该事务会回滚；从5.0.81开始，则会等待所有的操作结束，除非用户发起KILL操作。当Slave的SQL线程对非事务表执行操作时被强制 KILL了，可能会导致Master、Slave数据不一致；5、MySQL Server进程关闭所有线程，关闭所有存储引擎；刷新所有表cache，关闭所有打开的表；每个存储引擎各自负责相关的关闭操作，例如MyISAM会刷新所有等待写入的操作；InnoDB会将buffer pool刷新到磁盘中（从MySQL 5.0.5开始，如果innodb_fast_shutdown不设置为 2 的话），把当前的LSN记录到表空间中，然后关闭所有的内部线程。6、MySQL Server进程退出关于KILL指令从5.0开始，KILL 支持指定  CONNECTION | QUERY两种可选项：KILL CONNECTION和原来的一样，停止回滚事务，关闭该线程连接，释放相关资源；KILL QUERY则只停止线程当前提交执行的操作，其他的保持不变；提交KILL操作后，该线程上会设置一个特殊的 kill标记位。通常需要一段时间后才能真正关闭线程，因为kill标记位只在特定的情况下才检查：1、执行SELECT查询时，在ORDER BY或GROUP BY循环中，每次读完一些行记录块后会检查 kill标记位，如果发现存在，该语句会终止；2、执行ALTER TABLE时，在从原始表中每读取一些行记录块后会检查 kill 标记位，如果发现存在，该语句会终止，删除临时表；3、执行UPDATE和DELETE时，每读取一些行记录块并且更新或删除后会检查 kill 标记位，如果发现存在，该语句会终止，回滚事务，若是在非事务表上的操作，则已发生变更的数据不会回滚；4、GET_LOCK() 函数返回NULL；5、INSERT DELAY线程会迅速内存中的新增记录，然后终止；6、如果当前线程持有表级锁，则会释放，并终止；7、如果线程的写操作调用在等待释放磁盘空间，则会直接抛出“磁盘空间满”错误，然后终止；8、当MyISAM表在执行REPAIR TABLE 或 OPTIMIZE TABLE 时被 KILL的话，会导致该表损坏不可用，指导再次修复完成。安全关闭MySQL几点建议想要安全关闭 mysqld 服务进程，建议按照下面的步骤来进行：0、用具有SUPER、ALL等最高权限的账号连接MySQL，最好是用 unix socket 方式连接；1、在5.0及以上版本，设置innodb_fast_shutdown = 1，允许快速关闭InnoDB（不进行full purge、insert buffer merge），如果是为了升级或者降级MySQL版本，则不要设置；2、设置innodb_max_dirty_pages_pct = 0，让InnoDB把所有脏页都刷新到磁盘中去；3、设置max_connections和max_user_connections为1，也就最后除了自己当前的连接外，不允许再有新的连接创建；4、关闭所有不活跃的线程，也就是状态为Sleep  且 Time 大于 1 的线程ID；5、执行 SHOW PROCESSLIST  确认是否还有活跃的线程，尤其是会产生表锁的线程，例如有大数据集的SELECT，或者大范围的UPDATE，或者执行DDL，都是要特别谨慎的；6、执行 SHOW ENGINE INNODB STATUS 确认History list length的值较低（一般要低于500），也就是未PURGE的事务很少，并且确认Log sequence number、Log flushed up to、Last checkpoint at三个状态的值一样，也就是所有的LSN都已经做过检查点了；7、然后执行FLUSH LOCKAL TABLES 操作，刷新所有 table cache，关闭已打开的表（LOCAL的作用是该操作不记录BINLOG）；8、如果是SLAVE服务器，最好是先关闭 IO_THREAD，等待所有RELAY LOG都应用完后，再关闭 SQL_THREAD，避免 SQL_THREAD 在执行大事务被终止，耐心待其全部应用完毕，如果非要强制关闭的话，最好也等待大事务结束后再关闭SQL_THREAD；9、最后再执行 mysqladmin shutdown。10、紧急情况下，可以设置innodb_fast_shutdown = 1，然后直接执行 mysqladmin shutdown 即可，甚至直接在操作系统层调用 kill 或者 kill -9 杀掉 mysqld 进程（在innodb_flush_log_at_trx_commit = 0 的时候可能会丢失部分事务），不过mysqld进程再次启动时，会进行CRASH RECOVERY工作，需要有所权衡。啰嗦那么多，其实正常情况下执行 mysqladmin shutdown 就够了，如果发生阻塞，再参考上面的内容进行分析和解决吧，哈哈：）

MySQL 外键约束的相关资料官方文档：https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html1.外键的作用MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。2.外键的使用条件两个表必须是InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，但至少目前不支持）外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立；外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以3.创建语法[CONSTRAINT [symbol]] FOREIGN KEY     [index_name] (col_name, ...)     REFERENCES tbl_name (col_name,...)     [ON DELETE reference_option]     [ON UPDATE reference_option] reference_option:     RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT 该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。 ON DELETE、ON UPDATE表示事件触发限制，可设参数： RESTRICT（限制外表中的外键改动） CASCADE（跟随外键改动） SET NULL（设空值） SET DEFAULT（设默认值） NO ACTION（无动作，默认的） CASCADE：表示父表在进行更新和删除时，更新和删除子表相对应的记录 RESTRICT和NO ACTION：限制在子表有关联记录的情况下，父表不能单独进行删除和更新操作 SET NULL：表示父表进行更新和删除的时候，子表的对应字段被设为NULL4.案例演示以CASCADE（级联）约束方式1. 创建势力表（父表）country create table country ( id int not null, name varchar(30), primary key(id) );   2. 插入记录 insert into country values(1,'西欧')； insert into country values(2,'玛雅')； insert into country values(3,'西西里');   3. 创建兵种表（子表）并建立约束关系 create table solider( id int not null, name varchar(30), country_id int, primary key(id), foreign key(country_id) references country(id) on delete cascade on update cascade, );   4. 参照完整性测试 insert into solider values(1,'西欧见习步兵',1); #插入成功 insert into solider values(2,'玛雅短矛兵'，2); #插入成功 insert into solider values(3,'西西里诺曼骑士',3) #插入成功 insert into solider values(4，'法兰西剑士',4); #插入失败，因为country表中不存在id为4的势力   5. 约束方式测试   insert into solider values(4，'玛雅猛虎勇士'，2); #成功插入 delete from country where id=2； #会导致solider表中id为2和4的记录同时被删除，因为父表中都不存在这个势力了，那么相对应的兵种自然也就消失了 update country set id=8 where id=1; #导致solider表中country_id为1的所有记录同时也会被修改为8以SET NULL约束方式1. 创建兵种表（子表）并建立约束关系   drop table if exists solider; create table solider( id int not null, name varchar(30), country_id int, primary key(id), foreign key(country_id) references country(id) on delete set null on update set null, );   2. 参照完整性测试   insert into solider values(1,'西欧见习步兵',1); #插入成功 insert into solider values(2,'玛雅短矛兵'，2); #插入成功 insert into solider values(3,'西西里诺曼骑士',3) #插入成功 insert into solider values(4，'法兰西剑士',4); #插入失败，因为country表中不存在id为4的势力   3. 约束方式测试   insert into solider values(4，'西西里弓箭手'，3); #成功插入 delete from country where id=3； #会导致solider表中id为3和4的记录被设为NULL update country set id=8 where id=1; #导致solider表中country_id为1的所有记录被设为NULL以NO ACTION 或 RESTRICT方式 （默认）1. 创建兵种表（子表）并建立约束关系   drop table if exists solider; create table solider( id int not null, name varchar(30), country_id int, primary key(id), foreign key(country_id) references country(id) on delete RESTRICT on update RESTRICT, );   2. 参照完整性测试   insert into solider values(1,'西欧见习步兵',1); #插入成功 insert into solider values(2,'玛雅短矛兵'，2); #插入成功 insert into solider values(3,'西西里诺曼骑士',3) #插入成功 insert into solider values(4，'法兰西剑士',4); #插入失败，因为country表中不存在id为4的势力   3. 约束方式测试   insert into solider values(4，'西欧骑士'，1); #成功插入 delete from country where id=1； #发生错误，子表中有关联记录，因此父表中不可删除相对应记录，即兵种表还有属于西欧的兵种，因此不可单独删除父表中的西欧势力 update country set id=8 where id=1; #错误，子表中有相关记录，因此父表中无法修改

在 MySQL 中用很多类型的自增 ID，每个自增 ID 都设置了初始值。一般情况下初始值都是从 0 开始，然后按照一定的步长增加（一般是自增 1）。一般情况下，我们都是用int(11)来作为数据表的自增 ID，在 MySQL 中只要定义了这个数的字节长度，那么就会有上限。MySQL的自增ID(主键) 用完了，怎么办？如果用 int unsigned (int，4个字节 ), 我们可以算下最大当前声明的自增ID最大是多少，由于这里定义的是 int unsigned，所以最大可以达到2的32幂次方 - 1 = 4294967295。这里有个小技巧，可以在创建表的时候，直接声明AUTO_INCREMENT的初始值为4294967295。create table `test` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4294967295;SQL插入语句insert into `test` values (null);当想再尝试插入一条数据时，得到了下面的异常结果。[SQL] insert into `test` values (null); [Err] 1062 - Duplicate entry '4294967295' for key 'PRIMARY'说明，当再次插入时，使用的自增ID还是 4294967295，报主键冲突的错误，这说明 ID 值达到上限之后，就不会再变化了。4294967295，这个数字已经可以应付大部分的场景了，如果你的服务会经常性的插入和删除数据的话，还是存在用完的风险，建议采用 bigint unsigned ，这个数字就大了。bigint unsigned 的范围是 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）， 存储大小为 8 个字节。不过，还存在另一种情况，如果在创建表没有显示申明主键，会怎么办？如果是这种情况，InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id，而且InnoDB 维护了一个全局的 dictsys.row_id，所以未定义主键的表都共享该row_id，每次插入一条数据，都把全局row_id当成主键id，然后全局row_id加 1。该全局row_id在代码实现上使用的是bigint unsigned类型，但实际上只给row_id留了6字节，这种设计就会存在一个问题：如果全局row_id一直涨，一直涨，直到2的48幂次-1时，这个时候再+1，row_id的低48位都为0，结果在插入新一行数据时，拿到的row_id就为0，存在主键冲突的可能性。所以，为了避免这种隐患，每个表都需要定一个主键。总结数据库表的自增 ID 达到上限之后，再申请时它的值就不会在改变了，继续插入数据时会导致报主键冲突错误。因此在设计数据表时，尽量根据业务需求来选择合适的字段类型。

在使用MySQL建表时，我们通常会创建一个自增字段(AUTO_INCREMENT)，并以此字段作为主键。1.MySQL为什么建议将自增列id设为主键？如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要**到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。综上而言：当我们使用自增列作为主键时，存取效率是最高的。2.自增列id一定是连续的吗？自增id是增长的 不一定连续。我们先来看下MySQL 对自增值的保存策略：nnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力， 也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，具体情况是： 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候， 都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。 举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。 但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10。 也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。造成自增id不连续的情况可能有：1.唯一键冲突2.事务回滚3.insert ... select语句批量申请自增id3.自增id有上限吗？自增id是整型字段，我们常用int类型来定义增长id，而int类型有上限 即增长id也是有上限的。下表列举下 int 与 bigint 字段类型的范围：类型大小范围（有符号）范围(无符号)int4字节(-2147483648,2147483647)(0,4294967295) bigint8字节(-9223372036854775808,9223372036854775807)（0，18446744073709551615）从上表可以看出：当自增字段使用int有符号类型时，最大可达2147483647即21亿多；使用int无符号类型时，最大可达4294967295即42亿多。当然bigint能表示的范围更大。下面我们测试下当自增id达到最大时再次插入数据会怎么样：create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;insert into t values(null); // 成功插入一行 4294967295show create table t;/* CREATE TABLE `t` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4294967295;*/ insert into t values(null);//Duplicate entry '4294967295' for key 'PRIMARY'从实验可以看出，当自增id达到最大时将无法扩展，第一个 insert 语句插入数据成功后，这个表的AUTO_INCREMENT 没有改变（还是 4294967295），就导致了第二个 insert 语句又拿到相同的自增 id 值，再试图执行插入语句，报主键冲突错误4.关于自增列 我们该怎么维护？维护方面主要提供以下2点建议：字段类型选择方面：推荐使用int无符号类型，若可预测该表数据量将非常大 可改用bigint无符号类型。多关注大表的自增值，防止发生主键溢出情况。

为了保证并发时操作数据的正确性，数据库都会有事务隔离级别的概念。1) 脏读脏读是指一个事务正在访问数据，并且对数据进行了修改，但是这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。2) 不可重复读不可重复读是指在一个事务内，多次读取同一个数据。在这个事务还没有结束时，另外一个事务也访问了该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。3) 幻读幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。为了解决以上这些问题，标准 SQL 定义了 4 类事务隔离级别，用来指定事务中的哪些数据改变是可见的，哪些数据改变是不可见的。MySQL 包括的事务隔离级别如下：读未提交（READ UNCOMITTED）读提交（READ COMMITTED）可重复读（REPEATABLE READ）串行化（SERIALIZABLE）MySQL 事务隔离级别可能产生的问题如下表所示：隔离级别脏读不可重复读幻读READ UNCOMITTED√√√READ COMMITTED×√√REPEATABLE READ××√SERIALIZABLE×××MySQL 的事务的隔离级别由低到高分别为 READ UNCOMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE。低级别的隔离级别可以支持更高的并发处理，同时占用的系统资源更少。下面根据实例来一一阐述它们的概念和联系。1. 读未提交（READ UNCOMITTED，RU）顾名思义，读未提交就是可以读到未提交的内容。如果一个事务读取到了另一个未提交事务修改过的数据，那么这种隔离级别就称之为读未提交。在该隔离级别下，所有事务都可以看到其它未提交事务的执行结果。因为它的性能与其他隔离级别相比没有高多少，所以一般情况下，该隔离级别在实际应用中很少使用。例 1 主要演示了在读未提交隔离级别中产生的脏读现象。示例 11) 先在 test 数据库中创建 testnum 数据表，并插入数据。SQL 语句和执行结果如下：mysql> CREATE TABLE testnum(     -> num INT(4)); Query OK, 0 rows affected (0.57 sec) mysql> INSERT INTO test.testnum (num) VALUES(1),(2),(3),(4),(5); Query OK, 5 rows affected (0.09 sec) 2) 下面的语句需要在两个命令行窗口中执行。为了方便理解，我们分别称之为 A 窗口和 B 窗口。在 A 窗口中修改事务隔离级别，因为 A 窗口和 B 窗口的事务隔离级别需要保持一致，所以我们使用 SET GLOBAL TRANSACTION 修改全局变量。SQL 语句如下：mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Query OK, 0 rows affected (0.04 sec) flush privileges; Query OK, 0 rows affected (0.04 sec) 查询事务隔离级别，SQL 语句和运行结果如下：mysql> show variables like '%tx_isolation%'\G *************************** 1. row *************************** Variable_name: tx_isolation         Value: READ-UNCOMMITTED 1 row in set, 1 warning (0.00 sec)结果显示，现在 MySQL 的事务隔离级别为 READ-UNCOMMITTED。 3) 在 A 窗口中开启一个事务，并查询 testnum 数据表，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM testnum; +------+ | num  | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | +------+ 5 rows in set (0.00 sec)4) 打开 B 窗口，查看当前 MySQL 的事务隔离级别，SQL 语句如下：mysql> show variables like '%tx_isolation%'\G *************************** 1. row *************************** Variable_name: tx_isolation         Value: READ-UNCOMMITTED 1 row in set, 1 warning (0.00 sec)确定事务隔离级别是 READ-UNCOMMITTED 后，开启一个事务，并使用 UPDATE 语句更新 testnum 数据表，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> UPDATE test.testnum SET num=num*2 WHERE num=2; Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 05) 现在返回 A 窗口，再次查询 testnum 数据表，SQL 语句和运行结果如下：mysql> SELECT * FROM testnum; +------+ | num  | +------+ |    1 | |    4 | |    3 | |    4 | |    5 | +------+ 5 rows in set (0.02 sec)由结果可以看出，A 窗口中的事务读取到了更新后的数据。 6) 下面在 B 窗口中回滚事务，SQL 语句和运行结果如下：mysql> ROLLBACK; Query OK, 0 rows affected (0.09 sec)7) 在 A 窗口中查询 testnum 数据表，SQL 语句和运行结果如下：mysql> SELECT * FROM testnum; +------+ | num  | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | +------+ 5 rows in set (0.00 sec)当 MySQL 的事务隔离级别为 READ UNCOMITTED 时，首先分别在 A 窗口和 B 窗口中开启事务，在 B 窗口中的事务更新但未提交之前， A 窗口中的事务就已经读取到了更新后的数据。但由于 B 窗口中的事务回滚了，所以 A 事务出现了脏读现象。使用读提交隔离级别可以解决实例中产生的脏读问题。2. 读提交（READ COMMITTED，RC）顾名思义，读提交就是只能读到已经提交了的内容。如果一个事务只能读取到另一个已提交事务修改过的数据，并且其它事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那么这种隔离级别就称之为读提交。该隔离级别满足了隔离的简单定义：一个事务从开始到提交前所做的任何改变都是不可见的，事务只能读取到已经提交的事务所做的改变。这是大多数数据库系统的默认事务隔离级别（例如 Oracle、SQL Server），但不是 MySQL 默认的。例 2 演示了在读提交隔离级别中产生的不可重复读问题。示例 21) 使用 SET 语句将 MySQL 事务隔离级别修改为 READ COMMITTED，并查看。SQL 语句和运行结果如下：mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%tx_isolation%'\G *************************** 1. row *************************** Variable_name: tx_isolation         Value: READ-COMMITTED 1 row in set, 1 warning (0.00 sec)2) 确定当前事务隔离级别为 READ COMMITTED 后，开启一个事务，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) 3) 在 B 窗口中开启事务，并使用 UPDATE 语句更新 testnum 数据表，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql>  UPDATE test.testnum SET num=num*2 WHERE num=2; Query OK, 1 row affected (0.07 sec) Rows matched: 1  Changed: 1  Warnings: 04) 在 A 窗口中查询 testnum 数据表，SQL 语句和运行结果如下：mysql> SELECT * from test.testnum; +------+ | num  | +------+ |    1 | |    2 | |    3 | |    4 | |    5 | +------+ 5 rows in set (0.00 sec)5) 提交 B 窗口中的事务，SQL 语句和运行结果如下：mysql> COMMIT; Query OK, 0 rows affected (0.07 sec)6) 在 A 窗口中查询 testnum 数据表，SQL 语句和运行结果如下：mysql> SELECT * from test.testnum; +------+ | num  | +------+ |    1 | |    4 | |    3 | |    4 | |    5 | +------+ 5 rows in set (0.00 sec)当 MySQL 的事务隔离级别为 READ COMMITTED 时，首先分别在 A 窗口和 B 窗口中开启事务，在 B 窗口中的事务更新并提交后，A 窗口中的事务读取到了更新后的数据。在该过程中，A 窗口中的事务必须要等待 B 窗口中的事务提交后才能读取到更新后的数据，这样就解决了脏读问题。而处于 A 窗口中的事务出现了不同的查询结果，即不可重复读现象。使用可重复读隔离级别可以解决实例中产生的不可重复读问题。3. 可重复读（REPEATABLE READ，RR）顾名思义，可重复读是专门针对不可重复读这种情况而制定的隔离级别，可以有效的避免不可重复读。在一些场景中，一个事务只能读取到另一个已提交事务修改过的数据，但是第一次读过某条记录后，即使其它事务修改了该记录的值并且提交，之后该事务再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据。那么这种隔离级别就称之为可重复读。可重复读是 MySQL 的默认事务隔离级别，它能确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。在该隔离级别下，如果有事务正在读取数据，就不允许有其它事务进行修改操作，这样就解决了可重复读问题。例 3 演示了在可重复读隔离级别中产生的幻读问题。示例 31) 在 test 数据库中创建 testuser 数据表，SQL 语句和执行结果如下：mysql> CREATE TABLE testuser(     -> id INT (4) PRIMARY KEY,     -> name VARCHAR(20)); Query OK, 0 rows affected (0.29 sec)2) 使用 SET 语句修改事务隔离级别，SQL 语句如下：mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 rows affected (0.00 sec)3) 在 A 窗口中开启事务，并查询 testuser 数据表，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM test.testuser where id=1; Empty set (0.04 sec)4) 在 B 窗口中开启一个事务，并向 testuser 表中插入一条数据，SQL 语句和运行结果如下：mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql>  INSERT INTO test.testuser VALUES(1,'zhangsan'); Query OK, 1 row affected (0.04 sec) mysql> COMMIT; Query OK, 0 rows affected (0.06 sec)5) 现在返回 A 窗口，向 testnum 数据表中插入数据，SQL 语句和运行结果如下：mysql> INSERT INTO test.testuser VALUES(1,'lisi'); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql>  SELECT * FROM test.testuser where id=1; Empty set (0.00 sec)使用串行化隔离级别可以解决实例中产生的幻读问题。4. 串行化（SERIALIZABLE）如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。那么这种隔离级别就称之为串行化。SERIALIZABLE 是最高的事务隔离级别，主要通过强制事务排序来解决幻读问题。简单来说，就是在每个读取的数据行上加上共享锁实现，这样就避免了脏读、不可重复读和幻读等问题。但是该事务隔离级别执行效率低下，且性能开销也最大，所以一般情况下不推荐使用。

顾名思义，临时表就是临时用来存储数据的表，是建立在系统临时文件夹中的表，如果使用得当，完全可以像普通表一样进行各种操作。我们常使用临时表来存储中间结果集。如果需要执行一个很耗资源的查询或需要多次操作大表时，可以把中间结果或小的子集放到一个临时表里，再对这些表进行查询，以此来提高查询效率。临时表主要适用于需要临时保存数据的一些场景。一般情况下，临时表通常是在应用程序中动态创建或者由 MySQL 内部根据需要自己创建。临时表可以分为内部临时表和外部临时表。外部临时表外部临时表也可称为会话临时表，这种临时表只对当前用户可见，它的数据和表结构都存储在内存中。当前会话中断或结束后，数据表数据就会丢失，MySQL 会自动删除表并释放其所占空间。1）创建临时表创建临时表很容易，在 CREATE TABLE 语句上添加 TEMPORARY 关键字，如下所示：CREATE TEMPORARY TABLE <表名>...临时表的命名可以和非临时表同名，但是同名后非临时表将对当前会话不可见，直到临时表被删除。2）查询临时表创建了临时表之后，运行 SHOW TABLES 命令不会列出临时表，以及在 INFORMATION_SCHEMA 数据库中也不存在临时表的信息，这不是 Bug，而是设计就是如此。我们可以使用以下命令来查看临时表：SHOW CREATE TABLE <表名>; 3）删除临时表当然，我们也可以在当前会话中手动销毁临时表，SQL 语句如下：DROP TABLE <表名>;例 下面是创建 tmp_table 表，并对它进行操作。mysql> CREATE TEMPORARY TABLE tmp_table (     -> id INT NOT NULL,     -> name VARCHAR(10) NOT NULL); Query OK, 0 rows affected (0.03 sec) mysql> SHOW TABLES; +-------------------+ | Tables_in_test    | +-------------------+ | product           | | product_price     | | score             | | student           | | student_comment   | | tb_student        | | tb_student_course | | tb_students_info  | | tb_students_score | | tb_usertest       | +-------------------+ 10 rows in set (0.01 sec) mysql> SHOW CREATE TABLE tmp_table \G *************************** 1. row ***************************        Table: tmp_table Create Table: CREATE TEMPORARY TABLE `tmp_table` (   `id` int(11) NOT NULL,   `name` varchar(10) COLLATE utf8_unicode_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 1 row in set (0.00 sec) mysql> DROP TABLE tmp_table; Query OK, 0 rows affected (0.01 sec) mysql> SHOW CREATE TABLE tmp_table \G ERROR 1146 (42S02): Table 'test.tmp_table' doesn't exist如果你在执行 DROP TABLE 命令之前退出当前 MySQL 会话，再使用 SHOW CREATE TABLE 命令来读取 tmp_table 表，会发现数据库中没有该表的存在，因为在退出会话时该临时表已经被销毁了。外部临时表也有一些限制，使用时需注意以下几点：所用数据库账号需要有建立和使用临时表的权限在同一条 SQL 语句中，不能关联2次相同的临时表不能用 RENAME 来重命名一个临时表，可以用 ALTER TABLE 来代替。内部临时表内部临时表是一种特殊轻量级的临时表，不同于手工创建的临时表，它是被 MySQL 自动创建的。在 SQL 的执行过程中可能会用到临时表来存储某些操作的中间结果，该过程由 MySQL 自动完成，用户无法手工干预，且这种内部表对用户来说是不可见的。我们可以通过 EXPLAIN 或者 SHOW STATUS 查看 MySQL 是否使用了内部临时表帮助完成某个操作。内部临时表在 SQL 语句的优化过程中扮演着非常重要的角色，MySQL 中的很多操作都要依赖于内部临时表来进行优化。但是使用内部临时表需要创建表以及中间数据的存取代价，所以用户在写 SQL 语句的时候应该尽量的去避免使用临时表。以下是生成内部临时表的几种可能：使用 ORDER BY/GROUP BY 的列并非全来自于表连接的第一个表Distinct 和 ORDER BY 联合使用多表连接需要保存中间结果集内部临时表有两种类型，一种是 HEAP 临时表，这种临时表的所有数据都会存在内存中，对于这种表的操作不需要 IO 操作；另一种是 OnDisk 临时表，顾名思义，这种临时表会将数据存储在磁盘上。OnDisk 临时表用来处理中间结果比较大的操作。以下情况可能阻碍 MySQL 使用 HEAP 临时表，改用 OnDisk 临时表：数据表中含有 BLOB 类型或 TEXT 类型字段列；在 GROUP BY 或 DISTINCT 任何条件中含有超过 512 字节的列；如果使用了 UNION 或 UNION ALL，而且 SELECT 列中含有任何超过 512 字节的列；如果 HEAP 临时表的大小大于 MAX_HEAP_TABLE_SIZE 系统变量的值，HEAP 临时表将会被自动转换成 OnDisk 临时表。MySQL 5.7 版本中，OnDisk 临时表可以通过 INTERNAL_TMP_DISK_STORAGE_ENGINE 系统变量选择使用 MyISAM 引擎或者 InnoDB 引擎。Created_tmp_tables 和 Created_tmp_disk_tables 变量用来记录所用临时表的数目。

优化服务器硬件服务器的硬件直接决定着 MySQL 数据库的性能。例如，增加内存和提高硬盘的读写速度，可以提高 MySQL 数据库的查询、更新的速度。优化服务器硬件的方法主要有以下几种：配置较大的内存配置高速磁盘系统，以减少读盘的等待时间，提高响应速度合理分布磁盘 I/O，把磁盘 I/O 分散在多个设备上，以减少资源竞争，提高并行操作能力配置多处理器，MySQL 是多线程的数据库，多处理器可同时执行多个线程随着硬件技术的成熟，硬件的价格也随之降低。现在普通的个人电脑都已经配置了 8GB 内存，甚至一些个人电脑配置 16GB 内存。因为内存的读写速度比硬盘的读写速度快。可以在内存中为 MySQL 设置更多的缓冲区，这样可以提高 MySQL 的访问的速度。如果将查询频率很高的记录存储在内存中，那么查询速度就会很快。如果条件允许，可以将内存提高到 16GB。并且选择 my-innodb-heavy-4G.ini 作为 MySQL 数据库的配置文件。但是，这个配置文件主要支持 InnoDB 存储引擎的表。如果使用 8GB 内存，可以选择 my-huge.ini 作为配置文件。MySQL 所在的计算机最好是专用数据库服务器，这样数据库就可以完全利用该机器的资源。服务器类型分为 Developer Machine、Server Machine 和 Dedicate MySQL Server Machine。其中 Developer Machine 用来做软件开发的时候使用，数据库占用的资源比较少。后面两者占用的资源比较多，尤其是 Dedicate MySQL Server Machine，其几乎要占用所有的资源。还可以使用多块磁盘来存储数据。这样可以从多个磁盘上并行读取数据，提高数据库读取数据的速度。通过镜像机制可以将不同计算机上的 MySQL 服务器进行同步，这些 MySQL 服务器中的数据都是一样的。通过不同的 MySQL 服务器来提供数据库服务，这样可以降低单个 MySQL 服务器的压力，从而提高 MySQL 的性能。优化MySQL参数和大多数数据库一样，MySQL 提供了很多参数来进行服务器的优化设置。数据库服务器第一次启动时，很多参数都是默认设置的，这在实际应用中并不能完全满足需求，为此数据库管理员要进行必要的设置。1. 查看性能参数的方法MySQL 服务器启动之后，可以使用 SHOW VARIABLES;命令查看系统参数，也可称为静态参数。这些参数是系统默认或者 DBA 调整优化后的参数，可以通过 SET 命令或在配置文件中修改。使用 SHOW STATUS; 命令查询服务器运行的实时状态信息，也就是动态参数。便于 DBA 查看当前 MySQL 运行的状态，做出相应优化，不能手动修改。例 1下面为使用 SHOW VARIABLES 和 SHOW STATUS 命令的实例。mysql> SHOW VARIABLES LIKE 'key_buffer_size'; +-----------------+----------+ | Variable_name   | Value    | +-----------------+----------+ | key_buffer_size | 33554432 | +-----------------+----------+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW STATUS LIKE 'key_read_requests'; +-------------------+-------+ | Variable_name     | Value | +-------------------+-------+ | Key_read_requests | 149   | +-------------------+-------+ 1 row in set (0.01 sec)2. 设置优化性能参数在 MySQL 中，有些参数直接影响到系统的性能。我们可以通过优化 MySQL 的参数提高资源利用率，从而达到提高 MySQL 服务器性能的目的。以下配置参数都在 my.cnf 或者 my.ini 文件的 [mysqld] 组中。下面对几个重要参数进行详细介绍。1）key_buffer_size（针对MyISAM存储引擎）表示索引缓存的大小，这个参数是对 MyISAM 表性能影响最大的一个参数。值越大，索引进行查询的速度越快。通过检查状态值 key_read_requests 和 key_reads，可以知道 key_buffer_size 的值是否合理。正常情况下，key_reads / key_read_requests 的比例值需小于 0.01。2）table_cache（针对MyISAM存储引擎）表示数据库用户同时打开的表的个数。值越大，能够同时打开的表的个数越多。需要注意的是，这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。在设置该参数的时候，可以通过 open_tables 和 opened_tables 变量的值来确定该参数的值。open_tables 参数表示当前打开的表缓存数，opened_tables 参数表示曾经打开的表缓存数。如果 open_tables 的值已经接近 table_cache 的值，且 opened_tables 还在不断变大，则说明 MySQL 正在将缓存的表释放以容纳新的表，此时可能需要加大table_cache 的值。对于大多数情况，比较适合的值如下：open_tables / opened_tables >= 0.85open_tables / table_cache <= 0.95执行 FLUSH TABLE 操作后，系统会关闭一些当前没有使用的表缓存，因此 FLUSH TABLE 后，open_tables 参数的值会变小，opened_tables 参数的值不会变。3）query_cache_size表示查询缓存区的大小。使用查询缓存区可以提高查询的速度。内存中会为 MySQL 保留部分的缓存区，这些缓存区可以提高 MySQL 的处理速度。可以从以下几个方面考虑如何设置该参数的大小：查询缓存对 DDL 和 DML 语句的性能的影响查询缓存的内部维护成本查询缓存的命中率以及内存使用率等因素4）query_cache_type表示查询缓冲区的开启状态，用于控制查询结果是否放到查询缓存中。这种方式只适用于修改操作少且经常执行相同的查询操作的情况，其默认值为 0。值为 0 表示关闭；值为 1 表示开启；值为 2 表示按要求使用查询缓存区，只有 SELECT 语句中使用了 SQL_CACHE 关键字，查询缓存区才会使用。例如，SELECT SQL_CACHE * FROM student。5）max_connections表示数据库的最大连接数，默认值为 100。参数最大值不能超过 16384，即使超过也以 16384 为准。该参数设置过小的最明显特征是出现“Too many connections”错误。当然连接数也不是越大越好，因为这些连接会浪费内存的资源。6）sort_buffer_size表示排序缓存区的大小。值越大，排序的速度越快。7）read_buffer_size表示为每个线程保留的缓冲区的大小。当线程需要从表中连续读取记录时需要用到这个缓冲区。8）read_rnd_buffer_size表示为每个线程保留的缓冲区的大小，与 read_buffer_size 相似。但主要用于存储按特定顺序读取出来的记录。9）innodb_buffer_pool_size表示 InnoDB 类型的表和索引的最大缓存。值越大，查询的速度越快。但是这个值太大了也会影响操作系统的性能。调优参考计算方法：val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%val > 95% 则考虑增大 innodb_buffer_pool_size， 建议使用物理内存的 75%val < 95% 则考虑减小 innodb_buffer_pool_size， 建议设置为：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)10）innodb_log_file_size该参数的作用是设置日志组中每个日志文件的大小。该参数在高写入负载尤其是大数据集的情况下很重要，这个值越大则性能相对较高。最好不要超过 innodb_log_files_in_group * innodb_log_file_size 的 0.75。11）innodb_log_files_in_group该参数用于指定数据库中有几个日志组，默认为2个，因为有可能出现跨日志的大事务，所以一般来讲，建议使用 3~4 个日志组。12）innodb_log_buffer_size该参数的作用是设置日志缓存的大小，一旦提交事务，则将该缓存池中的内容写到磁盘的日志文件上。该参数的设置在中等强度写入负载以及较短事务情况下，一般都可以满足服务器的性能要求。如果服务器负载较大，可以考虑加大该参数的值。一般缓存池中的内存每秒钟写到磁盘一次，所以设置较大会浪费内存空间，一般设置为 8MB~16MB 就足够了。可以参考 Innodb_os_log_written 的值，如果该值增加过快，可以适当的增加该参数的值。13）innodb_flush_log_at_trx_commit表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数有 3 个值，分别为 0、1 和 2。值为 0 时，表示每隔 1 秒将数据写入日志文件并将日志文件写入磁盘；值为 1 时，表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘；值为 2 时，表示每次提交事务时将数据写入日志文件，每隔 1 秒将日志文件写入磁盘。该参数的默认值为 1，是最安全最合理的值。为了保证事务的持久性和一致性，建议将该参数设置为 1。参数设置的值要根据自己的实际情况来设置，并不是值越大越好，可能设置的数值太大体现不出优化效果，反而造成系统空间被占用，导致操作系统变慢。合理的配置参数可以提高 MySQL 服务器的性能。需要注意的是，配置完参数以后，需要重新启动 MySQL 服务配置才会生效。

MySQL中SQL Mode的查看与设置MySQL可以运行在不同的模式下，而且可以在不同的场景下运行不同的模式，这主要取决于系统变量 sql_mode 的值。本文主要介绍一下这个值的查看与设置，主要在Mac系统下。对于每个模式的意义和作用，网上很容易找到，本文不做介绍。按作用区域和时间可分为3个级别，分别是会话级别，全局级别，配置（永久生效）级别。会话级别：查看-select @@session.sql_mode;修改-set @@session.sql_mode='xx_mode' set session sql_mode='xx_mode'session均可省略，默认session，仅对当前会话有效全局级别：查看-select @@global.sql_mode;修改-set global sql_mode='xx_mode'; set @@global.sql_mode='xx_mode';需高级权限，仅对下次连接生效，不影响当前会话（亲测过），且MySQL重启后失效，因为MySQL重启时会重新读取配置文件里对应值，如果需永久生效需要修改配置文件里的值。配置修改（永久生效）：打开 vi /etc/my.cnf在下面添加[mysqld] sql-mode = "xx_mode"注意：[mysqld]必须加，且sql-mode中间是“-”，而不是下划线。保存退出，重启服务器，即可永久生效。因为Mac下安装MySQL没有配置文件，所以需要自己手动添加。ps最后额外加一点东西，就是Mac下MySQL的启动、停止、重启等操作。主要有两种方式，一是点击”系统偏好设置“对应的MySQL面板，可实现管理。二是命令行方式。MySQL相关的执行脚本，常用的主要是下面两个：/usr/local/mysql/support-files/mysql.server /usr/local/mysql/bin/mysqlmysql.server是控制服务器的启停等操作。mysql.server start|stop|restart|statusmysql主要用于连接服务器。mysql -uroot -p **** -h **** -D **有些需要sudo权限，且可将相关路径添加到环境变量，可简化书写，至于如何添加是不做介绍了。知识点扩展：Strict Mode阐述根据 mysql5.0以上版本 strict mode (STRICT_TRANS_TABLES) 的限制：1).不支持对not null字段插入null值2).不支持对自增长字段插入''值，可插入null值3).不支持 text 字段有默认值看下面代码：(第一个字段为自增字段)$query="insert into demo values('','$firstname','$lastname','$sex')";上边代码只在非strict模式有效。Code代码$query="insert into demo values(NULL,'$firstname','$lastname','$sex')";上边代码只在strict模式有效。把空值''换成了NULL.

视图：       一个临时表被反复使用的时候，对这个临时表起一个别名，方便以后使用，就可以创建一个视图，别名就是视图的名称。视图只是一个虚拟的表，其中的数据是动态的从物理表中读出来的，所以物理表的变更回改变视图。         创建：                    create view v1 as SQL例如：create view v1 as select * from student where sid<10创建后如果使用mysql终端可以看到一个叫v1的表，如果用navicate可以在视图中看到生成了一个v1的视图再次使用时，可以直接使用查询表的方式。例如：select * from v1　            修改：只能修改视图中的sql语句                          alter view 视图名称 as sql               删除：                          drop view 视图名称               触发器：                       当对某张表做增删改查的时候(之前后者之后)，就可以使用触发器自定义关联行为。                      修改sql语句中的终止符号 delimiter               before after 之前之后-- delimiter //     -- before或者after定义操作（insert或其他）之前或之后的操作 -- on 代表那张表发生操作后引发触发器操作    -- CREATE TRIGGER t1 BEFORE INSERT on teacher for EACH row -- BEGIN -- INSERT into course(cname) VALUES('奥特曼'); -- END // -- delimiter ;       -- insert into teacher(tname) VALUES('triggertest111') -- -- delimiter //  -- CREATE TRIGGER t1 BEFORE INSERT on student for EACH row -- BEGIN -- INSERT into teacher(tname) VALUES('奥特曼'); -- END // -- delimiter ;       -- insert into student(gender,sname,class_id) VALUES('男','1小刚111',3); -- 删除触发器 -- drop trigger t1;       -- NEW 和 OLD 代指新老数据 使其数据一致 -- delimiter // -- create TRIGGER t1 BEFORE insert on student for each row -- BEGIN    --这里的new 指定的是新插入的数据，old通常用在delete上    -- insert into teacher(tname) VALUES(NEW.sname); -- end // -- delimiter ; insert into student(gender,sname,class_id) VALUES('男','蓝色的大螃蟹',3);存储过程：本质上就是一堆sql的集合，然后给这个集合起个别名。和view的区别就是，视图是一个sql查询语句当成一个表。             方式：                                           1 msyql----存储过程，供程序调用　　　　　　     2 msyql---不做存储过程，程序写sql　　　　　　     3 mysql--不做存储过程，程序写类和对象（转化成sql语句）　             创建方法：-- 1 创建无参数的存储过程 -- delimiter // -- create PROCEDURE p1() -- BEGIN -- select * from student; -- insert into teacher(tname) VALUES('cccc'); -- end // -- delimiter ;-- 调用存储过程call p2(5,2)<br data-filtered="filtered"><br data-filtered="filtered"><em id="__mceDel"> pymysql中 cursor.callproc('p1',(5,2))</em>--  2 带参数 in 参数    -- delimiter // -- create PROCEDURE p2( --  in n1 int, -- in n2 int -- ) -- BEGIN --  select * from student where sid<n1; --  -- end //<br data-filtered="filtered"><br data-filtered="filtered"> call p2(5,2)<br data-filtered="filtered"><br data-filtered="filtered"><em id="__mceDel"> pymysql中 cursor.callproc('p1',(5,2))</em>-- 3  out参数 在存储过程入参时 使用out则 该变量可以在外部进行调用 --    存储过程中没有return 如果想要在外部调用变量则需要使用out -- delimiter // -- create PROCEDURE p3( --  in n1 int, -- out n2 int -- ) -- BEGIN --  set n2=444444; --  select * from student where sid<n1; --  -- end // -- -- delimiter ; -- -- set @v1=999  相当于 在session级别 创建一个变量 -- set @v1=999;  -- call p3(5,@v1); -- select @v1; #通过传一个变量进去，然后监测这个变量就可以监测到存储过程是否执行成功    -- pymsyql中 --   -- cursor.callproc('p3',(5,2)) -- r2=cursor.fetchall() -- print(r2) -- -- 存储过程含有out关键字 如果想要拿到返回值  cursor.execute('select @_p3_0,@_p3_1') -- # 其中 'select @_p3_0,@_p3_1'为固定写法 select @_存储过程名称_入参索引位置 -- cursor.execute('select @_p3_0,@_p3_1') -- r3=cursor.fetchall() -- print(r3) --为什么有了结果集，又要有out伪造返回的值？                                    因为存储过程中含有多个sql语句，无法判断所有的sql都能执行成功，利用out的特性来标识sql是否执行成功。                                    例如，如果成功标识为1 部分成功标识2 失败为3存储过程中的事务：事务：       被成为原子性操作。DML（insert，update，delete）语句共同完成，事物只和DML语句相关，或者锁只有DML才有事物。事务的特点：                 原子性 A ：事务是最小单位，不可分割                 一致性 C ：事务要求所有dml语句操作的时候必须保证全部成功或者失败                 隔离性 I ： 事务A和事务B之间有隔离性                 持久性 D ： 是事务的保证，事务终结的标志（内存中的数据完全保存到硬盘中）事务关键字：                    开启事务：start transaction　　　　　事务结束 ：end transaction　　　　　提交事务 ：commit transaction　　　　　回滚事务 ：rollback transaction事务的基本操作delimiter //  create procedure p5(  in n1 int,  out n2 int  )  begin  1 声明如果出现异常执行（    set n2=1;    rollback;   ）   2 开始事务      购买方账号-100         卖放账号+100         commit   3 结束     set n2=2    end //         delimiter ;       这样 既可以通过n2 检测后到错误 也可以回滚       以下是详细代码 delimiter //   create procedure p6(   out code TINYINT   )   begin    声明如果碰到sqlexception 异常就执行下边的操作    DECLARE exit HANDLER for SQLEXCEPTION    begin     --error       set code=1;       rollback;    end;    START TRANSACTION；        delete from tb1;        insert into tb2(name)values('slkdjf')    commit;    ---success    code=2    end // delimiter ;游标在存储过程中的使用：delimiter // create procedure p7()  begin     declare row_id int;      declare row_num int;      declare done int DEFAULT FALSE;      声明游标      declare my_cursor cursor for select id,num from A;      声明如果没有数据 则将done置为True      declare continue handler for not found set done=True;                    open my_cursor;  打开游标          xxoo;LOOP   开启循环叫xxoo             fetch my_cursor into row_id,row_num;               if done then 如果done为True 离开循环                 leave xxoo;               end if;               set temp=row_id+row_num;        insert into B(number)VALUES(temp);             end loop xxoo; 关闭循环       close my_cursor;     end //        delimiter ;       以上代码 转化成python  for row_id,row_num in my_cursor:   检测循环中是否还有数据，如果没有则跳出 break     break     insert into B(num) values(row_id+row_num)动态的执行sql，数据库层面放置sql注入：delimiter \\ create procedure p6(  in nid int)  begin   1 预编译（预检测）某个东西 sql语句合法性    2 sql=格式化tpl+arg     3 执行sql      set @nid=nid     prepare prod from 'select * from student where sid>?'     EXECUTE prod using @ nid;     deallocate prepare prod   end \\   delimiter ;

数据库管理系统中并发控制的任务是确保在多个事务同时存取数据库中同一数据不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁式并发控制主要采用的技术手段。悲观锁在关系数据库管理系统中，悲观并发控制（悲观锁，PCC）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作的每行数据应用了锁，那只有当这个事务锁释放，其他事务才能够执行与该锁冲突的操作。悲观并发控制主要应用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本环境。数据库中，悲观锁的流程如下:在对任何记录进行修改之前，先尝试为该记录加上排他锁如果加锁失败，说明该记录正在被修改，那么当前查询可能要等待或抛出异常如果成功加锁，则就可以对记录做修改，事务完成后就会解锁其间如果有其他对该记录做修改或加排他锁的操作，都会等待我们解锁或直接抛出异常MySQL InnoDB中使用悲观锁要使用悲观锁，必须关闭mysql数据库的自动提交属性，因为MySQL默认使用autocommit模式，也就是当你执行一个更新操作后，MySQL会立即将结果进行提交//开始事务   begin;/begin work;/start transaction;（三者选一个）   select status from t_goods where id=1 for update;   //根据商品信息生成订单   insert into t_orders (id,goods_id) values (null,1);   //修改商品status为2   update t_goods set status=2;   // 提交事务   commit;/commit work;以上查询语句中，使用了select...for update方式，通过开启排他锁的方式实现了悲观锁。则相应的记录被锁定，其他事务必须等本次事务提交之后才能够执行。我们使用select ... for update会把数据给锁定，不过我们需要注意一些锁的级别，MySQL InnoDB默认行级锁。行级锁都是基于索引的，如果一条SQL用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住。特点1.为数据处理的安全提供了保证2.效率上，由于处理加锁的机制会让数据库产生额外开销，增加产生死锁机会3.在只读型事务中由于不会产生冲突，也没必要使用锁，这样会增加系统负载，降低并行性乐观锁1.乐观并发控制也是一种并发控制的方法。2.假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据，在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没其他事务修改该数据，如果有则回滚正在提交的事务3.乐观锁相对悲观锁而言，是假设数据不会发生冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误信息，让用户决定如何做。4.乐观锁实现一般使用记录版本号，为数据增加一个版本标识，当更新数据的时候对版本标识进行更新实现使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新版本号。1.查询出商品信息 select (status,status,version) from t_goods where id=#{id} 2.根据商品信息生成订单 3.修改商品status为2 update t_goods set status=2,version=version+1 where id=#{id} and version=#{version};特点乐观并发控制相信事务之间的数据竞争概率是较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。

在关系型数据库中，悲观锁与乐观锁是解决资源并发场景的解决方案，接下来将详细讲解一下这两个并发解决方案的实际使用及优缺点。首先定义一下数据库，做一个最简单的库存表，如下设计：CREATE TABLE `order_stock` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',  `oid` int(50) NOT NULL COMMENT '商品ID',  `quantity` int(20) NOT NULL COMMENT '库存',  PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;quantity代表着不同商品oid的库存，接下来OCC及PCC使用此数据库进行演示。乐观锁 OCC它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。即“乐观锁”认为拿锁的用户多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。这样就可以避免使用数据库自身定义的行锁，可以避免死锁现象的产生。UPDATE order_stock SET quantity = quantity - 1 WHERE oid = 1 AND quantity - 1 > 0;乐观并发控制多数用于数据争用不大、冲突较少的环境中，这种环境中，偶尔回滚事务的成本会低于读取数据时锁定数据的成本，因此可以获得比其他并发控制方法更高的吞吐量。悲观锁 PCC它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作读某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。这种设计采用了“一锁二查三更新”模式，就是采用数据库中自带 select ... for update 关键字进行对当前事务添加行级锁，先将要操作的数据进行锁上，之后执行对应查询数据并执行更新操作。BEGIN SELECT quantity FROM order_stock WHERE oid = 1 FOR UPDATE; UPDATE order_stock SET quantity = 2 WHERE oid = 1;  COMMIT;MySQL还有个问题是select ... for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此如果在MySQL中用悲观锁务必要确定走了索引，而不是全表扫描。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。OCC 和 PCC 优缺点OCC 优点及缺点【优点】乐观锁相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁；可以快速响应事务，随着并发量增加，但会出现大量回滚出现；效率高，但是要控制好锁的力度【缺点】如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题；随着并发量增加，但会出现大量回滚出现。PCC 优点及缺点【优点】“先取锁再访问”的保守策略，为数据处理的安全提供了保证；【缺点】依赖数据库锁，效率低；处理加锁的机制会让数据库产生额外的开销 ，还有增加产生死锁的机会；降低了并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据。

前言：在 MySQL 系统中，有着诸多不同类型的日志。各种日志都有着自己的用途，通过分析日志，我们可以优化数据库性能，排除故障，甚至能够还原数据。这些不同类型的日志有助于我们更清晰的了解数据库，在日常学习及运维过程中也会和这些日志打交道。本节内容将带你了解 MySQL 数据库中几种常用日志的作用及管理方法1.错误日志（errorlog）错误日志记录着 mysqld 启动和停止，以及服务器在运行过程中发生的错误及警告相关信息。当数据库意外宕机或发生其他错误时，我们应该去排查错误日志。log_error 参数控制错误日志是否写入文件及文件名称，默认情况下，错误日志被写入终端标准输出stderr。当然，推荐指定 log_error 参数，自定义错误日志文件位置及名称。# 指定错误日志位置及名称 vim /etc/my.cnf  [mysqld]  log_error = /data/mysql/logs/error.log   相关配置变量说明： log_error={1 | 0 | /PATH/TO/ERROR_LOG_FILENAME} 定义错误日志文件。作用范围为全局或会话级别，属非动态变量。2.慢查询日志（slow query log）慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。与慢查询相关的几个参数如下：slow_query_log ：是否启用慢查询日志，默认为0，可设置为0，1。slow_query_log_file ：指定慢查询日志位置及名称，默认值为host_name-slow.log，可指定绝对路径。long_query_time ：慢查询执行时间阈值，超过此时间会记录，默认为10，单位为s。log_output ：慢查询日志输出目标，默认为file，即输出到文件。默认情况下，慢查询日志是不开启的，一般情况下建议开启，方便进行慢SQL优化。在配置文件中可以增加以下参数：# 慢查询日志相关配置，可根据实际情况修改 vim /etc/my.cnf  [mysqld]  slow_query_log = 1 slow_query_log_file = /data/mysql/logs/slow.log long_query_time = 3 log_output = FILE3.一般查询日志（general log）一般查询日志又称通用查询日志，是 MySQL 中记录最详细的日志，该日志会记录 mysqld 所有相关操作，当 clients 连接或断开连接时，服务器将信息写入此日志，并记录从 clients 收到的每个 SQL 语句。当你怀疑 client 中的错误并想要确切知道 client 发送给mysqld的内容时，通用查询日志非常有用。默认情况下，general log 是关闭的，开启通用查询日志会增加很多磁盘 I/O， 所以如非出于调试排错目的，不建议开启通用查询日志。相关参数配置介绍如下：# general log相关配置 vim /etc/my.cnf  [mysqld] general_log = 0 //默认值是0，即不开启，可设置为1 general_log_file = /data/mysql/logs/general.log //指定日志位置及名称4.二进制日志（binlog）关于二进制日志，前面有篇文章做过介绍。它记录了数据库所有执行的DDL和DML语句（除了数据查询语句select、show等），以事件形式记录并保存在二进制文件中。常用于数据恢复和主从复制。与 binlog 相关的几个参数如下：log_bin ：指定binlog是否开启及文件名称。server_id ：指定服务器唯一ID，开启binlog 必须设置此参数。binlog_format ：指定binlog模式，建议设置为ROW。max_binlog_size ：控制单个二进制日志大小，当前日志文件大小超过此变量时，执行切换动作。expire_logs_days ：控制二进制日志文件保留天数，默认值为0，表示不自动删除，可设置为0~99。binlog默认情况下是不开启的，不过一般情况下，建议开启，特别是要做主从同步时。# binlog 相关配置 vim /etc/my.cnf  [mysqld] server-id = 1003306 log-bin = /data/mysql/logs/binlog binlog_format = row expire_logs_days = 155.中继日志（relay log）中继日志用于主从复制架构中的从服务器上，从服务器的 slave 进程从主服务器处获取二进制日志的内容并写入中继日志，然后由 IO 进程读取并执行中继日志中的语句。relay log 相关参数一般在从库设置，几个相关参数介绍如下：relay_log ：定义 relay log 的位置和名称。relay_log_purge ：是否自动清空不再需要中继日志，默认值为1(启用)。relay_log_recovery ：当 slave 从库宕机后，假如 relay log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay log ，并且重新从 master 上获取日志，这样就保证了 relay log 的完整性。默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为1可开启此功能。relay log 默认位置在数据文件的目录，文件名为 host_name-relay-bin，可以自定义文件位置及名称。# relay log 相关配置,从库端设置 vim /etc/my.cnf  [mysqld] relay_log = /data/mysql/logs/relay-bin relay_log_purge = 1 relay_log_recovery = 1总结：本篇文章主要讲述了 MySQL 中的几类日志的用途及设置方法，需要注意的是，上述几类日志，若不指定绝对路径，则默认保存在数据目录下，我们也可以新建一个日志目录专用于保存这些日志。还有 redo log 和 undo log 没有讲解，留在下篇文章吧。

开源数据库架构设计原则01. 技术选型选择成熟的平台和技术，同时是最熟悉的，能做到极致的，用好不用坏，用熟不用生。目前业界的MySQL主流分支版本有Oracle官方版本的MySQL、Percona Server、MariaDB。02. 高可用选择高可用解决方案探讨的本质上是低宕机时间解决方案，可以理解成高可用的反面是不可用，绝大部分情况下数据库宕机才会导致数据库不可用。随着技术发展，开源数据库方面很多高可用组件（主从复制、半同步、MGR、MHA、Galera Cluster）,对应场景，只有适合的，没有万能的，需要理解每个高可用优缺点。03. 表设计表设计方面目前一致坚持和提倡的原则：单表数据量所有表都需要添加注释，单表数据量建议控制在 3000 万以内不保存大字段数据不在数据库中存储图片、文件等大数据表使用规范拆分大字段和访问频率低的字段，分离冷热数据单表字段数控制在 20 个以内索引规范1.单张表中索引数量不超过 5 个2.单个索引中的字段数不超过 5 个3.INNODB 主键推荐使用自增列，主键不应该被修改，字符串不应该做主键，如果不指定主键，INNODB 会使用唯一且非空值索引代替4.如果是复合索引，区分最大的字段放在索引前面5. 避免冗余或重复索引：合理创建联合索引（避免冗余）6. 不在低基数列上建立索引，例如‘性别'7. 不在索引列进行数学运算和函数运算字符集utf8mb4(偏生字，表情符)04. 优化原则                    05. 复制方式MySQL复制方式提供异步方式、半同步方式、全局事务强一致性、binglog同步。需要不同业务系统间 或 两个数据库间进行同步。异步方式可以防止故障和效率问题的蔓延，扩大化；但强一致性会更复杂，并发、事务大小都有求限制06. 分离原则区分核心的业务，重要业务，渠道，内部业务的业务系统，对不同的系统设置不同的架构。为核心业务设置 最佳为分库，多活 专用高速公路，其他业务可以做读写分离，缓存。07. 扩展性对于系统来说扩展性很重要，尽量做到水平扩展。避免过度依赖纵向扩展，同时具备纵向，横向扩展的能力，例如无状态应用应该多套负载均衡多活部署，数据库分库架构。08. 读写分离读多写少场景（10%写 90%读）复制存在延迟，业务对延迟不敏感的实现方式：                    1. 通过应用代码配置读写分离，                    2. 通过中间代理方式路由只读库                     3. 业务和数据库为一个单位09. 分库分表当表中数据记录的数量超过3000万条，再好的索引也已经不能提高数据查询的速度，这时需要将表拆分成更多的小表，增加性能，增加弹性，避免发生垮库进行操作。引入中间价要考虑性能代价,聚合需求。分库原则尽量在app 上层进行分库，就是流量。分多少合适：可用性和性能满足TPS。路由：写入配置文件 或则 插表 或则 zookeeper。10. 归档原则历史数据定期进行归档 或则 移到其他大数据平台。能让轻量级数据库更多缓存有用的数据。在MySQL分区表里 注意要避免分区锁，只能写读的场景。11. 连接池的要求长链接，自动重链，延时和异常记录， 弹性链接，检测满，异常告警，进阶要求是记录所有访问情况，可以扩展出很多能力。应用和数据库连接池设置，数据库允许的连接数设置，常见问题。A ）应用的数据库连接池设置偏小，一旦数据库相应慢（新上线应用，缺少索引 等）则应。用排队严重，甚至雪崩，而遗憾的是数据库能力还远为用尽。B ）不具备失效及时发现和重新链接数据库能力。C ）隔离级别设置：RR 和 RC下不同的表现。12. 应用解耦通过应用访问数据库而不是直接访问，重要业务不能依赖低保障级别的系统，应用层重要业务和普通业务解耦，关键业务要独立。13. 组件失效免疫能力单一应用，单一硬件，甚至单一基础设施，单一站点容灾，业务影响，故障恢复能力，要季度级别进行演练。14. 关键词组件减负特别是数据库访问,数据库成本最高，扩展性最难，可用性保障最难，恢复难度和时间最大。减负：能不用就不用，使用最简单，成本最低的语句，避免大事务，慎用两阶段事务。15. 灰度数据库减少发布时变更数据库对全局的影响,只有应用程序灰度是不够的，还要有专门的灰度数据库。在分库、读写分离架构下，一套含数据库的完整应用架构，变的很自然。所为灰度环境就是生产环境，生产数据，所影响的也是生产环境，只是范围比测试环境更广，更真实。其实就是小范围的生产环境。类似于游戏内测。16. 高仿真架构体系建立高仿真架构体系数据库，操作系统升级：应用是否适应，性能会变好， 还是变坏应用上线发布，系统变更（列如换平台），提前判断业务影响和性能瓶颈应对突发交易量，例如双十一，性能极限在哪里，瓶颈在哪里。17. 容灾保障高可用是运维核心要求，容灾是最后屏障例如 双活比单活好，MGR比复制架构好，重要系统要做好高可用，容灾建设。18. 多中心建设冗余是基础，多中心建设是为了提升容灾能力和扩展能力，并保障业务。19. 应用和数据库是一个整体应用和运维人员一起，解决应用解耦，数据库解耦，追账补数，业务监控，应用路由，故障切换等。可用性，效率，故障恢复等方面都要一起参与。20. 性能提升开源数据库使用应该合理且有效的结合周边的其他类型数据库，做到性能最大化。比如：Redis、MongoDB、ES、ClickHouse等。总结1. 最适合的架构是结合软件特性和业务场景，又能取得成本收益平衡；2. 大数据情况下可以是利用读写分离、分库分表，但要选择合适的；3. 不适合分库的应该考虑竭尽所能把核心库做小，然后通过垂直扩展来扩容；4. 用尽各种技术， 高可用 和 容灾手段保证其可用。

MySQL 可以基于多表查询更新数据。对于多表的 UPDATE 操作需要慎重，建议在更新前，先使用 SELECT 语句查询验证更新的数据与自己期望的是否一致。下面我们建两张表，一张表为 product 表，用来存放产品信息，其中有产品价格字段 price；另外一张表是 product_price 表。现要将 product_price 表中的价格字段 price 更新为 product 表中价格字段 price 的 80%。操作前先分别查看两张表的数据，SQL 语句和运行结果如下：mysql> SELECT * FROM product; +----+-----------+-----------------------+-------+----------+ | id | productid | productname           | price | isdelete | +----+-----------+-----------------------+-------+----------+ |  1 |      1001 | C语言中文网Java教程   |   100 |        0 | |  2 |      1002 | C语言中文网MySQL教程  |   110 |        0 | |  3 |      1003 | C语言中文网Python教程 |   120 |        0 | |  4 |      1004 | C语言中文网C语言教程  |   150 |        0 | |  5 |      1005 | C语言中文网GoLang教程 |   160 |        0 | +----+-----------+-----------------------+-------+----------+ 5 rows in set (0.02 sec) mysql> SELECT * FROM product_price; +----+-----------+-------+ | id | productid | price | +----+-----------+-------+ |  1 |      1001 |  NULL | |  2 |      1002 |  NULL | |  3 |      1003 |  NULL | |  4 |      1004 |  NULL | |  5 |      1005 |  NULL | +----+-----------+-------+ 5 rows in set (0.01 sec)下面是 MySQL 多表更新在实践中的几种不同写法。执行不同的 SQL 语句，仔细观察 SQL 语句执行后表中数据的变化，很容易就能理解多表联合更新的用法。1. 使用UPDATE在 MySQL 中，可以使用“UPDATE table1 t1,table2,...,table n”的方式来多表更新，SQL 语句和运行结果如下：mysql> UPDATE product p, product_price pp SET pp.price = p.price * 0.8 WHERE p.productid= pp.productId; Query OK, 5 rows affected (0.02 sec) Rows matched: 5  Changed: 5  Warnings: 0 mysql> SELECT * FROM product_price; +----+-----------+-------+ | id | productid | price | +----+-----------+-------+ |  1 |      1001 |    80 | |  2 |      1002 |    88 | |  3 |      1003 |    96 | |  4 |      1004 |   120 | |  5 |      1005 |   128 | +----+-----------+-------+ 5 rows in set (0.00 sec)2. 通过INNER JOIN另外一种方法是使用 INNER JOIN 来多表更新。SQL 语句如下：mysql> UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8; Query OK, 5 rows affected (0.09 sec) Rows matched: 5  Changed: 5  Warnings: 0 mysql> SELECT * FROM product_price; +----+-----------+-------+ | id | productid | price | +----+-----------+-------+ |  1 |      1001 |    80 | |  2 |      1002 |    88 | |  3 |      1003 |    96 | |  4 |      1004 |   120 | |  5 |      1005 |   128 | +----+-----------+-------+ 5 rows in set (0.00 sec)3. 通过LEFT JOIN也可以使用 LEFT JOIN 来做多表更新，如果 product_price 表中没有产品价格记录的话，将 product 表的 isdelete 字段设置为 1。在 product 表添加 1006 商品，且不在 product_price 表中添加对应信息，SQL 语句如下。mysql> UPDATE product p LEFT JOIN product_price pp ON p.productid= pp.productid SET p.isdelete = 1 WHERE pp.productid IS NULL; Query OK, 1 row affected (0.04 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT * FROM product; +----+-----------+-----------------------+-------+----------+ | id | productid | productname           | price | isdelete | +----+-----------+-----------------------+-------+----------+ |  1 |      1001 | C语言中文网Java教程   |   100 |        0 | |  2 |      1002 | C语言中文网MySQL教程  |   110 |        0 | |  3 |      1003 | C语言中文网Python教程 |   120 |        0 | |  4 |      1004 | C语言中文网C语言教程  |   150 |        0 | |  5 |      1005 | C语言中文网GoLang教程 |   160 |        0 | |  6 |      1006 | C语言中文网Spring教程 |  NULL |        1 | +----+-----------+-----------------------+-------+----------+ 6 rows in set (0.00 sec)4. 通过子查询也可以通过子查询进行多表更新，SQL 语句和执行过程如下：mysql> UPDATE product_price pp SET price=(SELECT price*0.8 FROM product WHERE productid = pp.productid); Query OK, 5 rows affected (0.00 sec) Rows matched: 5  Changed: 5  Warnings: 0 mysql> SELECT * FROM product_price; +----+-----------+-------+ | id | productid | price | +----+-----------+-------+ |  1 |      1001 |    80 | |  2 |      1002 |    88 | |  3 |      1003 |    96 | |  4 |      1004 |   120 | |  5 |      1005 |   128 | +----+-----------+-------+ 5 rows in set (0.00 sec)另外，上面的几个例子都是在两张表之间做关联，只更新一张表中的记录。MySQL 也可以同时更新两张表，如下语句就同时修改了两个表。UPDATE product p INNER JOIN product_price pp ON p.productid= pp.productid SET pp.price = p.price * 0.8, p.dateUpdate = CURDATE()两张表做关联，同时更新了 product_price 表的 price 字段和 product 表的 dateUpdate 两个字段。值得一提的是，日常开发中，一般都是用单表 UPDATE 语句，很少写多表关联的 UPDATE。