索引下推
参考我的另一篇文章：
图解索引下推！
open in new window
常见的存储引擎有哪些？
MySQL中常用的四种存储引擎分别是：
MyISAM
、
InnoDB
、
MEMORY
、
ARCHIVE
。MySQL 5.5版本后默认的存储引擎为
InnoDB
。
InnoDB存储引擎
InnoDB是MySQL
默认的事务型存储引擎
，使用最广泛，基于聚簇索引建立的。InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。
优点
：支持事务和崩溃修复能力；引入了行级锁和外键约束。
缺点
：占用的数据空间相对较大。
适用场景
：需要事务支持，并且有较高的并发读写频率。
MyISAM存储引擎
数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用MyISAM引擎。MyISAM会将表存储在两个文件中，数据文件
.MYD
和索引文件
.MYI
。
优点
：访问速度快。
缺点
：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。
适用场景
：对事务完整性没有要求；表的数据都会只读的。
MEMORY存储引擎
MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。
MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。
优点
：访问速度较快。
缺点
：
-
哈希索引数据不是按照索引值顺序存储，无法用于排序。
-
不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
-
只支持等值比较，不支持范围查询。
-
当出现哈希冲突时，存储引擎需要遍历链表中所有的行指针，逐行进行比较，直到找到符合条件的行。
ARCHIVE存储引擎
ARCHIVE存储引擎非常适合存储大量独立的、作为历史记录的数据。ARCHIVE提供了压缩功能，拥有高效的插入速度，但是这种引擎不支持索引，所以查询性能较差。
MyISAM和InnoDB的区别？
-
存储结构的区别
。每个MyISAM在磁盘上存储成三个文件。文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
-
存储空间的区别
。MyISAM支持支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表，极大的减少磁盘的空间占用。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
-
可移植性、备份及恢复
。MyISAM数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。对于InnoDB，可行的方案是拷贝数据文件、备份 binlog，或者用mysqldump，在数据量达到几十G的时候就相对麻烦了。
-
是否支持行级锁
。MyISAM 只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。而InnoDB 支持行级锁和表级锁，默认为行级锁。行锁大幅度提高了多用户并发操作的性能。
-
是否支持事务和崩溃后的安全恢复
。 MyISAM 不提供事务支持。而InnoDB 提供事务支持，具有事务、回滚和崩溃修复能力。
-
是否支持外键
。MyISAM不支持，而InnoDB支持。
-
是否支持MVCC
。MyISAM不支持，InnoDB支持。应对高并发事务，MVCC比单纯的加锁更高效。
-
是否支持聚集索引
。MyISAM不支持聚集索引，InnoDB支持聚集索引。
-
全文索引
。MyISAM支持 FULLTEXT类型的全文索引。InnoDB不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
-
表主键
。MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)。
-
表的行数
。MyISAM保存有表的总行数，如果
select count(*) from table
;会直接取出该值。InnoDB没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了where条件后，MyISAM和InnoDB处理的方式都一样。
MySQL有哪些锁？
按锁粒度分类
，有行级锁、表级锁和页级锁。
-
行级锁是mysql中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。行级锁的类型主要有三类：
-
Record Lock，记录锁，也就是仅仅把一条记录锁上；
-
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
-
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
-
表级锁是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分mysql引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。
-
页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折衷的页级锁，一次锁定相邻的一组记录。
按锁级别分类
，有共享锁、排他锁和意向锁。
-
共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
-
排他锁又称写锁、独占锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。
-
意向锁是表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁：
意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是 InnoDB 自动加的，不需要用户干预。
对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。
共享锁：
SELECT … LOCK IN SHARE MODE;
排他锁：
SELECT … FOR UPDATE;
MVCC 实现原理？
MVCC(
Multiversion concurrency control
) 就是同一份数据保留多版本的一种方式，进而实现并发控制。在查询的时候，通过
read view
和版本链找到对应版本的数据。
作用：提升并发性能。对于高并发场景，MVCC比行级锁开销更小。
MVCC 实现原理如下：
MVCC 的实现依赖于版本链，版本链是通过表的三个隐藏字段实现。
-
DB_TRX_ID
：当前事务id，通过事务id的大小判断事务的时间顺序。
-
DB_ROLL_PTR
：回滚指针，指向当前行记录的上一个版本，通过这个指针将数据的多个版本连接在一起构成
undo log
版本链。
-
DB_ROW_ID
：主键，如果数据表没有主键，InnoDB会自动生成主键。
每条表记录大概是这样的：
使用事务更新行记录的时候，就会生成版本链，执行过程如下：
-
用排他锁锁住该行；
-
将该行原本的值拷贝到
undo log
，作为旧版本用于回滚；
-
修改当前行的值，生成一个新版本，更新事务id，使回滚指针指向旧版本的记录，这样就形成一条版本链。
下面举个例子方便大家理解。
1、初始数据如下，其中
DB_ROW_ID
和
DB_ROLL_PTR
为空。
2、事务A对该行数据做了修改，将
age
修改为12，效果如下：
3、之后事务B也对该行记录做了修改，将
age
修改为8，效果如下：
4、此时undo log有两行记录，并且通过回滚指针连在一起。
接下来了解下read view的概念。
read view
可以理解成将数据在每个时刻的状态拍成“照片”记录下来。在获取某时刻t的数据时，到t时间点拍的“照片”上取数据。
在
read view
内部维护一个活跃事务链表，表示生成
read view
的时候还在活跃的事务。这个链表包含在创建
read view
之前还未提交的事务，不包含创建
read view
之后提交的事务。
不同隔离级别创建read view的时机不同。
read view的记录筛选方式
前提
：
DATA_TRX_ID
表示每个数据行的最新的事务ID；
up_limit_id
表示当前快照中的最先开始的事务；
low_limit_id
表示当前快照中的最慢开始的事务，即最后一个事务。
-
如果
DATA_TRX_ID
<
up_limit_id
：说明在创建
read view
时，修改该数据行的事务已提交，该版本的记录可被当前事务读取到。
-
如果
DATA_TRX_ID
>=
low_limit_id
：说明当前版本的记录的事务是在创建
read view
之后生成的，该版本的数据行不可以被当前事务访问。此时需要通过版本链找到上一个版本，然后重新判断该版本的记录对当前事务的可见性。
-
如果
up_limit_id
<=
DATA_TRX_ID
<
low_limit_i
：
-
需要在活跃事务链表中查找是否存在ID为
DATA_TRX_ID
的值的事务。
-
如果存在，因为在活跃事务链表中的事务是未提交的，所以该记录是不可见的。此时需要通过版本链找到上一个版本，然后重新判断该版本的可见性。
-
如果不存在，说明事务trx_id 已经提交了，这行记录是可见的。
总结
：InnoDB 的
MVCC
是通过
read view
和版本链实现的，版本链保存有历史版本记录，通过
read view
判断当前版本的数据是否可见，如果不可见，再从版本链中找到上一个版本，继续进行判断，直到找到一个可见的版本。
快照读和当前读
表记录有两种读取方式。
快照读情况下，InnoDB通过
mvcc
机制避免了幻读现象。而
mvcc
机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。
下面举个例子说明下：
1、首先，user表只有两条记录，具体如下：
2、事务a和事务b同时开启事务
start transaction
；
3、事务a插入数据然后提交；
insert into user(user_name, user_password, user_mail, user_state) values('tyson', 'a', 'a', 0);
4、事务b执行全表的update；
update user set user_name = 'a';
5、事务b然后执行查询，查到了事务a中插入的数据。（下图左边是事务b，右边是事务a。事务开始之前只有两条记录，事务a插入一条数据之后，事务b查询出来是三条数据）
以上就是当前读出现的幻读现象。
那么MySQL是如何避免幻读？
-
在快照读情况下，MySQL通过
mvcc
来避免幻读。
-
在当前读情况下，MySQL通过
next-key
来避免幻读（加行锁和间隙锁来实现的）。
next-key包括两部分：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。
Serializable
隔离级别也可以避免幻读，会锁住整张表，并发性极低，一般不会使用。
共享锁和排他锁
SELECT 的读取锁定主要分为两种方式：共享锁和排他锁。
select * from table where id<6 lock in share mode;--共享锁
select * from table where id<6 for update;--排他锁
这两种方式主要的不同在于
LOCK IN SHARE MODE
多个事务同时更新同一个表单时很容易造成死锁。
申请排他锁的前提是，没有线程对该结果集的任何行数据使用排它锁或者共享锁，否则申请会受到阻塞。在进行事务操作时，MySQL会对查询结果集的每行数据添加排它锁，其他线程对这些数据的更改或删除操作会被阻塞（只能读操作），直到该语句的事务被
commit
语句或
rollback
语句结束为止。
SELECT... FOR UPDATE
使用注意事项：
-
for update
仅适用于innodb，且必须在事务范围内才能生效。
-
根据主键进行查询，查询条件为
like
或者不等于，主键字段产生
表锁
。
-
根据非索引字段进行查询，会产生
表锁
。
bin log/redo log/undo log
MySQL日志主要包括查询日志、慢查询日志、事务日志、错误日志、二进制日志等。其中比较重要的是
bin log
（二进制日志）和
redo log
（重做日志）和
undo log
（回滚日志）。
bin log
bin log
是MySQL数据库级别的文件，记录对MySQL数据库执行修改的所有操作，不会记录select和show语句，主要用于恢复数据库和同步数据库。
redo log
redo log
是innodb引擎级别，用来记录innodb存储引擎的事务日志，不管事务是否提交都会记录下来，用于数据恢复。当数据库发生故障，innoDB存储引擎会使用
redo log
恢复到发生故障前的时刻，以此来保证数据的完整性。将参数
innodb_flush_log_at_tx_commit
设置为1，那么在执行commit时会将
redo log
同步写到磁盘。
undo log
除了记录
redo log
外，当进行数据修改时还会记录
undo log
，
undo log
用于数据的撤回操作，它保留了记录修改前的内容。通过
undo log
可以实现事务回滚，并且可以根据
undo log
回溯到某个特定的版本的数据，
实现MVCC
。
bin log和redo log有什么区别？
-
bin log
会记录所有日志记录，包括InnoDB、MyISAM等存储引擎的日志；
redo log
只记录innoDB自身的事务日志。
-
bin log
只在事务提交前写入到磁盘，一个事务只写一次；而在事务进行过程，会有
redo log
不断写入磁盘。
-
bin log
是逻辑日志，记录的是SQL语句的原始逻辑；
redo log
是物理日志，记录的是在某个数据页上做了什么修改。
讲一下MySQL架构？
MySQL主要分为 Server 层和存储引擎层：
-
Server 层
：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
-
存储引擎
： 主要负责数据的存储和读取。server 层通过api与存储引擎进行通信。
Server 层基本组件
-
连接器：
当客户端连接 MySQL 时，server层会对其进行身份认证和权限校验。
-
查询缓存:
执行查询语句的时候，会先查询缓存，先校验这个 sql 是否执行过，如果有缓存这个 sql，就会直接返回给客户端，如果没有命中，就会执行后续的操作。
-
分析器:
没有命中缓存的话，SQL 语句就会经过分析器，主要分为两步，词法分析和语法分析，先看 SQL 语句要做什么，再检查 SQL 语句语法是否正确。
-
优化器：
优化器对查询进行优化，包括重写查询、决定表的读写顺序以及选择合适的索引等，生成执行计划。
-
执行器：
首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会根据执行计划去调用引擎的接口，返回结果。
分库分表
当单表的数据量达到1000W或100G以后，优化索引、添加从库等可能对数据库性能提升效果不明显，此时就要考虑对其进行切分了。切分的目的就在于减少数据库的负担，缩短查询的时间。
数据切分可以分为两种方式：垂直划分和水平划分。
垂直划分
垂直划分数据库是根据业务进行划分，例如购物场景，可以将库中涉及商品、订单、用户的表分别划分出成一个库，通过降低单库的大小来提高性能。同样的，分表的情况就是将一个大表根据业务功能拆分成一个个子表，例如商品基本信息和商品描述，商品基本信息一般会展示在商品列表，商品描述在商品详情页，可以将商品基本信息和商品描述拆分成两张表。
优点
：行记录变小，数据页可以存放更多记录，在查询时减少I/O次数。
缺点
：
-
主键出现冗余，需要管理冗余列；
-
会引起表连接JOIN操作，可以通过在业务服务器上进行join来减少数据库压力；
-
依然存在单表数据量过大的问题。
水平划分
水平划分是根据一定规则，例如时间或id序列值等进行数据的拆分。比如根据年份来拆分不同的数据库。每个数据库结构一致，但是数据得以拆分，从而提升性能。
优点
：单库（表）的数据量得以减少，提高性能；切分出的表结构相同，程序改动较少。
缺点
：
-
分片事务一致性难以解决
-
跨节点
join
性能差，逻辑复杂
-
数据分片在扩容时需要迁移
什么是分区表？
分区是把一张表的数据分成N多个区块。分区表是一个独立的逻辑表，但是底层由多个物理子表组成。
当查询条件的数据分布在某一个分区的时候，查询引擎只会去某一个分区查询，而不是遍历整个表。在管理层面，如果需要删除某一个分区的数据，只需要删除对应的分区即可。
分区一般都是放在单机里的，用的比较多的是时间范围分区，方便归档。只不过分库分表需要代码实现，分区则是mysql内部实现。分库分表和分区并不冲突，可以结合使用。
分区表类型
range分区
，按照范围分区。比如按照时间范围分区
CREATE TABLE test_range_partition(
id INT auto_increment,
createdate DATETIME,
primary key (id,createdate)
PARTITION BY RANGE (TO_DAYS(createdate) ) (
PARTITION p201801 VALUES LESS THAN ( TO_DAYS('20180201') ),
PARTITION p201802 VALUES LESS THAN ( TO_DAYS('20180301') ),
PARTITION p201803 VALUES LESS THAN ( TO_DAYS('20180401') ),
PARTITION p201804 VALUES LESS THAN ( TO_DAYS('20180501') ),
PARTITION p201805 VALUES LESS THAN ( TO_DAYS('20180601') ),
PARTITION p201806 VALUES LESS THAN ( TO_DAYS('20180701') ),
PARTITION p201807 VALUES LESS THAN ( TO_DAYS('20180801') ),
PARTITION p201808 VALUES LESS THAN ( TO_DAYS('20180901') ),
PARTITION p201809 VALUES LESS THAN ( TO_DAYS('20181001') ),
PARTITION p201810 VALUES LESS THAN ( TO_DAYS('20181101') ),
PARTITION p201811 VALUES LESS THAN ( TO_DAYS('20181201') ),
PARTITION p201812 VALUES LESS THAN ( TO_DAYS('20190101') )
在
/var/lib/mysql/data/
可以找到对应的数据文件，每个分区表都有一个使用#分隔命名的表文件：
-rw-r----- 1 MySQL MySQL 65 Mar 14 21:47 db.opt
-rw-r----- 1 MySQL MySQL 8598 Mar 14 21:50 test_range_partition.frm
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201801.ibd
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201802.ibd
-rw-r----- 1 MySQL MySQL 98304 Mar 14 21:50 test_range_partition#P#p201803.ibd
...
list分区
list分区和range分区相似，主要区别在于list是枚举值列表的集合，range是连续的区间值的集合。对于list分区，分区字段必须是已知的，如果插入的字段不在分区时的枚举值中，将无法插入。
create table test_list_partiotion
id int auto_increment,
data_type tinyint,
primary key(id,data_type)
)partition by list(data_type)
partition p0 values in (0,1,2,3,4,5,6),
partition p1 values in (7,8,9,10,11,12),
partition p2 values in (13,14,15,16,17)
hash分区
可以将数据均匀地分布到预先定义的分区中。
create table test_hash_partiotion
id int auto_increment,
create_date datetime,
primary key(id,create_date)
)partition by hash(year(create_date)) partitions 10;
分区的问题？
-
打开和锁住所有底层表的成本可能很高。当查询访问分区表时，MySQL 需要打开并锁住所有的底层表，这个操作在分区过滤之前发生，所以无法通过分区过滤来降低此开销，会影响到查询速度。可以通过批量操作来降低此类开销，比如批量插入、
LOAD DATA INFILE
和一次删除多行数据。
-
维护分区的成本可能很高。例如重组分区，会先创建一个临时分区，然后将数据复制到其中，最后再删除原分区。
-
所有分区必须使用相同的存储引擎。
查询语句执行流程？
查询语句的执行流程如下：权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。
举个例子，查询语句如下：
select * from user where id > 1 and name = '大彬';
-
首先检查权限，没有权限则返回错误；
-
MySQL8.0以前会查询缓存，缓存命中则直接返回，没有则执行下一步；
-
词法分析和语法分析。提取表名、查询条件，检查语法是否有错误；
-
两种执行方案，先查
id > 1
还是
name = '大彬'
，优化器根据自己的优化算法选择执行效率最好的方案；
-
校验权限，有权限就调用数据库引擎接口，返回引擎的执行结果。
更新语句执行过程？
更新语句执行流程如下：分析器、权限校验、执行器、引擎、
redo log
（
prepare
状态）、
binlog
、
redo log
（
commit
状态）
举个例子，更新语句如下：
update user set name = '大彬' where id = 1;
-
先查询到 id 为1的记录，有缓存会使用缓存。
-
拿到查询结果，将 name 更新为大彬，然后调用引擎接口，写入更新数据，innodb 引擎将数据保存在内存中，同时记录
redo log
，此时
redo log
进入
prepare
状态。
-
执行器收到通知后记录
binlog
，然后调用引擎接口，提交
redo log
为
commit
状态。
-
更新完成。
为什么记录完
redo log
，不直接提交，而是先进入
prepare
状态？
假设先写
redo log
直接提交，然后写
binlog
，写完
redo log
后，机器挂了，
binlog
日志没有被写入，那么机器重启后，这台机器会通过
redo log
恢复数据，但是这个时候
binlog
并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
exist和in的区别？
exists
用于对外表记录做筛选。
exists
会遍历外表，将外查询表的每一行，代入内查询进行判断。当
exists
里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果
exists
里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。
select a.* from A awhere exists(select 1 from B b where a.id=b.id)
in
是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。
select * from Awhere id in(select id from B)
子查询的表比较大的时候
，使用
exists
可以有效减少总的循环次数来提升速度；
当外查询的表比较大的时候
，使用
in
可以有效减少对外查询表循环遍历来提升速度。
MySQL中int(10)和char(10)的区别？
int(10)中的10表示的是显示数据的长度，而char(10)表示的是存储数据的长度。
truncate、delete与drop区别？
相同点：
-
truncate
和不带
where
子句的
delete
、以及
drop
都会删除表内的数据。
-
drop
、
truncate
都是
DDL
语句（数据定义语言），执行后会自动提交。
不同点：
-
truncate 和 delete 只删除数据不删除表的结构；drop 语句将删除表的结构被依赖的约束、触发器、索引；
-
一般来说，执行速度: drop > truncate > delete。
having和where区别？
-
二者作用的对象不同，
where
子句作用于表和视图，
having
作用于组。
-
where
在数据分组前进行过滤，
having
在数据分组后进行过滤。
什么是MySQL主从同步？
主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（
master
），其余的服务器充当从服务器（
slave
）。
因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。
为什么要做主从同步？
-
读写分离，使数据库能支撑更大的并发。
-
在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。
-
数据备份，保证数据的安全。
乐观锁和悲观锁是什么？
数据库中的并发控制是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观锁和悲观锁是并发控制主要采用的技术手段。
-
悲观锁：假定会发生并发冲突，会对操作的数据进行加锁，直到提交事务，才会释放锁，其他事务才能进行修改。实现方式：使用数据库中的锁机制。
-
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否数据是否被修改过。给表增加
version
字段，在修改提交之前检查
version
与原来取到的
version
值是否相等，若相等，表示数据没有被修改，可以更新，否则，数据为脏数据，不能更新。实现方式：乐观锁一般使用版本号机制或
CAS
算法实现。
用过processlist吗？
show processlist
或
show full processlist
可以查看当前 MySQL 是否有压力，正在运行的
SQL
，有没有慢
SQL
正在执行。返回参数如下：
-
id
：线程ID，可以用
kill id
杀死某个线程
-
db
：数据库名称
-
user
：数据库用户
-
host
：数据库实例的IP
-
command
：当前执行的命令，比如
Sleep
，
Query
，
Connect
等
-
time
：消耗时间，单位秒
-
state
：执行状态，主要有以下状态：
-
Sleep，线程正在等待客户端发送新的请求
-
Locked，线程正在等待锁
-
Sending data，正在处理
SELECT
查询的记录，同时把结果发送给客户端
-
Kill，正在执行
kill
语句，杀死指定线程
-
Connect，一个从节点连上了主节点
-
Quit，线程正在退出
-
Sorting for group，正在为
GROUP BY
做排序
-
Sorting for order，正在为
ORDER BY
做排序
-
info
：正在执行的
SQL
语句
MySQL查询 limit 1000,10 和limit 10 速度一样快吗？
两种查询方式。对应
limit offset, size
和
limit size
两种方式。
而其实
limit size
，相当于
limit 0, size
。也就是从0开始取size条数据。
也就是说，两种方式的
区别在于offset是否为0。
先来看下limit sql的内部执行逻辑。
MySQL内部分为
server层
和
存储引擎层
。一般情况下存储引擎都用innodb。
server层有很多模块，其中需要关注的是
执行器
是用于跟存储引擎打交道的组件。
执行器可以通过调用存储引擎提供的接口，将一行行数据取出，当这些数据完全符合要求（比如满足其他where条件），则会放到
结果集
中，最后返回给调用mysql的
客户端
。
以主键索引的limit执行过程为例：
执行
select * from xxx order by id limit 0, 10;
，select后面带的是
星号
，也就是要求获得行数据的
所有字段信息。
server层会调用innodb的接口，在innodb里的主键索引中获取到第0到10条
完整行数据
，依次返回给server层，并放到server层的结果集中，返回给客户端。
把offset搞大点，比如执行的是：
select * from xxx order by id limit 500000, 10;
server层会调用innodb的接口，由于这次的offset=500000，会在innodb里的主键索引中获取到第0到（500000 + 10）条
完整行数据
，
返回给server层之后根据offset的值挨个抛弃，最后只留下最后面的size条
，也就是10条数据，放到server层的结果集中，返回给客户端。
可以看出，当offset非0时，server层会从引擎层获取到
很多无用的数据
，而获取的这些无用数据都是要耗时的。
因此，mysql查询中 limit 1000,10 会比 limit 10 更慢。原因是 limit 1000,10 会取出1000+10条数据，并抛弃前1000条，这部分耗时更大。
深分页怎么优化？
还是以上面的SQL为空：
select * from xxx order by id limit 500000, 10;
方法一
：
从上面的分析可以看出，当offset非常大时，server层会从引擎层获取到很多无用的数据，而当select后面是*号时，就需要拷贝完整的行信息，
拷贝完整数据
相比
只拷贝行数据里的其中一两个列字段
更耗费时间。
因为前面的offset条数据最后都是不要的，没有必要拷贝完整字段，所以可以将sql语句修改成：
select * from xxx where id >=(select id from xxx order by id limit 500000, 1) order by id limit 10;
先执行子查询
select id from xxx by id limit 500000, 1
, 这个操作，其实也是将在innodb中的主键索引中获取到
500000+1
条数据，然后server层会抛弃前500000条，只保留最后一条数据的id。
但不同的地方在于，在返回server层的过程中，只会拷贝数据行内的id这一列，而不会拷贝数据行的所有列，当数据量较大时，这部分的耗时还是比较明显的。
在拿到了上面的id之后，假设这个id正好等于500000，那sql就变成了
select * from xxx where id >=500000 order by id limit 10;
这样innodb再走一次
主键索引
，通过B+树快速定位到id=500000的行数据，时间复杂度是lg(n)，然后向后取10条数据。
方法二：
将所有的数据
根据id主键进行排序
，然后分批次取，将当前批次的最大id作为下次筛选的条件进行查询。
select * from xxx where id > start_id order by id limit 10;
通过主键索引，每次定位到start_id的位置，然后往后遍历10个数据，这样不管数据多大，查询性能都较为稳定。
高度为3的B+树，可以存放多少数据？
InnoDB存储引擎有自己的最小储存单元——页（Page）。
查询InnoDB页大小的命令如下：
mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
