索引是存储引擎用于快速找到记录的一种数据结构。 B+树叶子节点指针指向被索引的数据
优点：
减少了服务器需要扫描的数据行数帮助服务器避免排序和分组，避免创建临时表。能够将相关的数据保存在一起，减少IO次数 缺点：创建索引需要时间和空间修改数据的时候还要顺带修改索引，减慢速度1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向具体数据的指针，但是B树有，因此B+树节点能容纳更多的键值，能查找更大的范围，树高和查询的次数降低，相对IO读写次数就降低了。
2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
3、由于B+树的数据都存储在叶子结点中，叶子使用指针相连，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。
哈希索引基于哈希表实现。对于每一行数据，存储引擎会对所有的索引列计算一个哈希码，不同的索引列计算出来的哈希码是不同的。哈希索引把哈希码存储在哈希表的索引列中，哈希表中另一列保存着指向每个数据行的指针。
InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表上生成哈希索引。缺点 无法用于排序与分组； 只支持精确查找，无法用于部分查找和范围查找https://www.cnblogs.com/zlslch/p/6440114.htm 在搜索引擎中有一种叫正向索引的结构： “文档1”的ID > 单词1：出现次数，出现位置列表；单词2：出现次数，出现位置列表； 给定一个文档的ID，能得到文档中单词出现的次数和位置。 倒排索引则是反过来，将文件ID对应到关键词的映射转换为关键词到文件ID的映射。
使用多个列作为条件查询的时候，使用联合索引的性能比多个单列索引要好。对表上的多个列进行索引。联合索引也对键值进行了排序。
对于BLOB，text和varchar这样的字符列，应该使用前缀索引，只索引开始的部分字符，这样可以大大节约索引空间。 前缀长度的选取需要根据索引选择性来确定。 缺点： 无法用前缀索引做order by和group by,也无法用前缀索引做覆盖索引。
如果一个索引包含了所有需要查询字段的值，就叫做覆盖索引。 优点：
只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO操作，提高了效率假如在(name,age,pos)这3列上建立了索引，
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos); 必须满足左前缀匹配法则。从索引的左边的列开始，不跳过索引中的列，被跳过的列后面不能使用索引。如果中间的列使用了范围查询，这个列后面的列也不能使用索引。索引列不能参与计算，保持列“干净”，对索引列进行运算导致索引失效,此处对索引列进行运算包括(+，-，*，/，! 等) 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10;使用不等于时，无论是哪一列使用了不等于，整个索引都失效 select * from staffs where name = "july" and age != 25 //name也不能使用索引 使用is null 或者is not null失效。 select * from staffs where name1 = 'july' and age is not null //这时候第一列的索引时可以用的，带有not null的列和这个列后面的索引是失效的 查询条件like以通配符开头会导致全表扫描，不使用索引。 现在重新建了一个表，新建的索引是(name,pos,age) select * from staffs where name1 ="xiaoming" and pos like "%dev" and age = 23 //只能使用name1上的索引，pos和age的索引都是无效的 使用or的时候，整个索引会失效。 select * from staff where name="liang" or age=23 //不走索引。MySQL的慢查询日志是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。long_query_time的默认值是10，意思是运行10s以上的语句。默认情况下，MySQL数据库并不启动该参数，因为开启慢查询日志会带来一定的性能影响。慢查询日志支持将文件记录写入文件，也支持将日志写入数据库表。 https://blog.51cto.com/ljianbing/1616932
mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总，找到需要优化的SQL语句。使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。Explain可以用来查看SQL语句的执行效 果，可以帮助选择更好的索引和优化查询语句
例如：explain select * from tableName; 简单的说就是explain关键字后面跟你要检查的SQL语句
select_type : 查询类型，有简单查询、联合查询、子查询等 key : 使用的索引 rows : 扫描的行数
一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。所以我们需要将大查询切分成小查询，每个查询的功能完全一样，每次返回一小部分的查询结果，将一次性的压力分散到一个很长的时间段中。
这个方法的意思是把单个多表连接查询改成多个单表查询，然后在程序中合并数据 如 ： select a.,b. from A a join B b on a.id = b.id 可以替换为: select a.* from A; select b.* from B; 然后通过程序把数据合并
其中InnoDB的默认级别是可重复读。 读脏数据和不可重复读的例子 读脏数据解决：对脏数据设置为加锁访问，如果访问结束后释放锁的话，会导致不可重复读。 不可重复读解决：对数据设置为加锁访问，事务结束后释放锁。 幻读解决：在两行记录的空隙加上锁，阻止新纪录的插入；这个锁称为间隙锁。
http://blog.itpub.net/15498/viewspace-2135342/ https://zhuanlan.zhihu.com/p/50561846
分库分表有垂直切分和水平切分两种。
就是“大表拆分成小表”，基于列字段进行的。一般是表中的字段太多，将不常用的，数据量较大的拆分到“扩展表”。这样可以避免查询时，数据量较大造成的跨页问题。
数据库有很多表，每一张表都对应着不同的业务，根据业务的不同，把每一张表拆分到相应的数据库上。比如用户表User放到User库中，商品表放到Product库中。而且拆分之后，每一个库需要放到不同的服务器上。 这是因为
没拆分数据库之前，请求都是落在一个库上，使得单个数据库的处理能力成为瓶颈。没把不同的数据库放到不同服务器上时，请求都是落在一个服务器上的，使得单个服务器的处理能力成为瓶颈。对于数据量巨大的单张表，按照某种规则(HASH取模)，切分到多张表里面去，但是这些表还是在同一个库中，单个数据库的处理能力是瓶颈。不建议使用。
将表水平切分后，放到不同的数据库上，不同的数据库放在不同的服务器上，这样能够缓解单库和单机的性能瓶颈。
在执行分库分表之后，事务就变成分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。
表的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。 https://www.jianshu.com/p/e20169951da1 案例：日志数据库有pay_log，里面有一个userid。用户的详细信息放主库main_db中。 粗略的解决办法有： 1. 字段冗余。在pay_log表里面放userid，还要放一个user_name字段。这种方法只能放少量的字段。 2. 表的复制。把join操作中，那一个较小的表格复制到较大的表所在的数据库中，然后就能在同一个库进行join操作了。但是这样比较浪费空间。 3. 链接表。什么是链接表呢？简单来说，就是在log_db里有一个user_info表，但这个表并没有存储数据，而是直接链接到了 main_db里的user_info表。这样的话，我们可以既无需定期同步，又可以像在同一个库里使用JOIN等操作
例如，对于一个记录用户成绩的用户数据表userTable，业务要求查出成绩最好的100位，在进行分表之前，只需一个order by语句就可以搞定，但是在进行分表之后，将需要n个order by语句，分别查出每一个分表的前100名用户数据，然后再对这些数据进行合并计算，才能得出结果。
从图中可以看到，SQL语句并不直接进入到master数据库或者slave数据库，而是进入到 proxy，然后proxy判断这条语句是有关写的语句（包括insert、update、delete）还 是读语句（select），当是写语句的时候，那么proxy将向master所在的服务器发出请 求，同理，如果是读语句的时候，proxy将向slave所在的服务器发出请求。 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销； 增加冗余，提高可用性。 主从服务器之间采用主从复制的方法来同步数据
参考链接 http://blog.itpub.net/30126024/viewspace-2222570/
这个更好https://blog.csdn.net/weter_drop/article/details/93386581 1、client和server建立连接，client发送sql至server（对应连接器这一过程）
2、server如果在查询缓存中发现了该sql，则直接使用查询缓存的结果返回给client，如果查询缓存中没有，则进入下面的步骤（对应查询缓存这一过程，8.0这一过程已经不存在了，8.0完全抛弃了这个功能）
3、server对sql进行语法分析，识别出sql里面的字符串是否符合标准，比如select关键字不能写错（对应分析器这一过程）
4、server对sql进行语义分析，识别出sql里面的字符串的具体意思是什么，比如T1是表名，C1是列名（对应分析器这一过程。3、4步其实解析的过程，这个解析的过程是分析器的工作不是优化器的工作）
5、server确定sql的执行路径，比如走索引还是全表，多表连接时哪张表先走哪张表后走，当你的where条件的可读性和效率有冲突时mysql内部还会自动进行优化，也就是大家理解的重写where子句（对应优化器这一过程）
6、server对执行sql的用户进行权限检查，比如对表是否有权限执行(对应执行器这一过程)
7、server执行该sql语句，发送结果给client(对应执行器这一过程)
连接器–>查询缓存–>分析器–>优化器–>执行器
如果表 T1 中没有字段 C1，而执行select * from T1 where C1=1会报错不存在C1这个列，这个过程对应上面第4个过程，对应分析器这一过程
如果用户对T1表没有权限，而执行select * from T1 where C1=1会报错对表T1没有权限，这个过程对应上面第6个过程，对应执行器这一过程
什么是事务 事务： 是用户定义的一个数据库操作序列， 这些操作要么全做， 要么全不做， 是一个不可分割的工作单位。
事务的四个特性
原子性（atomicity）： 一个事务是一个不可分割的工作单位， 事务中包括的诸操作要么都做， 要么都不做；一致性（consistency）： 事务必须使数据库从一个一致性状态变成另一个一致性状态；比如A有500块，B有400块，两者相互转钱，两者相加的钱还是900.与现实保持一致。隔离性（isolation）： 一个事务的执行不能被其他事务干扰；系统保证，任何一对事务A和B，在A看来，B要么在A执行之前已经执行完成，要么在A执行完后才开始执行。持续性（durability）： 也称永久性， 指一个事务一旦提交， 它对数据库中数据的改变就应该是永久性的。脏读：是读取了另一个事务未提交的修改 不可重复读：是读取了另一个事务提交之后的修改 幻读：两次读取得到的结果集不一样
为什么要使用MVCC 数据库使用加锁的方式来实现事务的隔离性，但是这样的话，读取数据的时候没法修改，修改数据的时候没办法读取，极大地降低了数据库的性能。 MVCC可以在读取数据的时候进行修改，修改数据的同时可以读取。
InnoDB使用MVCC来实现可重复读，避免脏读，但是没有解决幻读的问题。InnoDB的默认隔离级别就是可重复读。
首先表格有隐藏列 DB_TRX_ID和 DB_ROLL_PTR。对于每一行数据，DB_TRX_ID用来记录修改该事务的事务id，DB_ROLL_PTR用来记录这条数据的上一个版本在undo log的位置。 写事务修改这一条数据的时候，先把这条数据复制到undo log里面，然后修改这条数据，并把这条数据的DB_TRX_ID设置为写事务的事务ID，然后将DB_ROLL_PTR指向undo log的位置。 每一个SQL语句在执行的时候都会得到一个read view，read vie有着4个属性。 trx_ids: 当前系统活跃(未提交)事务版本号集合。 low_limit_id: 创建当前read view 时“当前系统最大事务版本号+1”。 up_limit_id: 创建当前read view 时“系统正处于活跃事务最小版本号” creator_trx_id: 创建当前read view的事务版本号；
Read view 匹配条件 （1）数据事务ID <up_limit_id 则显示 如果数据事务ID小于read view中的最小活跃事务ID，则可以肯定该数据是在当前事务启之前就已经存在了的,所以可以显示。
（2）数据事务ID>=low_limit_id 则不显示 如果数据事务ID大于read view 中的当前系统的最大事务ID，则说明该数据是在当前read view 创建之后才产生的，所以数据不予显示。
（3） up_limit_id <数据事务ID<low_limit_id 则与活跃事务集合trx_ids里匹配 如果数据的事务ID大于最小的活跃事务ID,同时又小于等于系统最大的事务ID，这种情况就说明这个数据有可能是在当前事务开始的时候还没有提交的。
所以这时候我们需要把数据的事务ID与当前read view 中的活跃事务集合trx_ids 匹配:
情况1: 如果事务ID不存在于trx_ids 集合（则说明read view产生的时候事务已经commit了），这种情况数据则可以显示。
情况2： 如果事务ID存在trx_ids则说明read view产生的时候数据还没有提交，但是如果数据的事务ID等于creator_trx_id ，那么说明这个数据就是当前事务自己生成的，自己生成的数据自己当然能看见，所以这种情况下此数据也是可以显示的。
情况3： 如果事务ID既存在trx_ids而且又不等于creator_trx_id那就说明read view产生的时候数据还没有提交，又不是自己生成的，所以这种情况下此数据不能显示。
（4）不满足read view条件时候，从undo log里面获取数据 当数据的事务ID不满足read view条件时候，从undo log里面获取数据的历史版本，然后数据历史版本事务号回头再来和read view 条件匹配 ，直到找到一条满足条件的历史数据，或者找不到则返回空结果；
https://www.aneasystone.com/archives/2017/10/solving-dead-locks-one.html
间隙锁和间隙锁之间是互不冲突的。
间隙锁的问题，在可能会锁住更大的范围。比如我执行这条语句
UPDATE accounts SET level = 100 WHERE id = 5;可能会锁住这两个范围(a,5)和(5,b)，假设a=1,b=10，那么这时候插入一条id=3的数据就会阻塞，尽管这时候(用这条语句，插入id=3的数据)并不会导致幻读。
这个锁表示插入的意向，只有insert的时候才会有这个锁。插入意向锁和插入意向锁之间是不冲突的。假如区间(1,5)没有间隙锁，事务1和事务2都能在这个区间加上插入意向锁，然后分别插入2和3。但是当区间(1,5)已经被间隙锁锁住，那么事务1和事务2获得插入意向锁的时候就会阻塞。
https://www.aneasystone.com/archives/2017/12/solving-dead-locks-three.html Next-Key Lock，锁一条记录及其之前的间隙，这是 RR 隔离级别用的最多的锁。默认隔离级别REPEATABLE-READ下，InnoDB中默认隔离级别是RR，所以行锁默认使用算法Next-Key Lock，只有当查询走的索引是唯一索引时，InnoDB会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围。 当查询走的索引为非唯一索引(只有辅助索引才有可能是非唯一的)时，InnoDB则会使用Next-Key Lock进行加锁。还会将该键值后面的间隙加上Gap LOCK。 当查询没有走索引时，只能走聚簇索引，对表中的记录进行扫描。需要给所有的聚簇索引的数据加上行锁，聚簇索引的之间加上间隙锁。
1.4.1 死锁案例一 死锁的根本原因是有两个或多个事务之间加锁顺序的不一致导致的，这个死锁案例其实是最经典的死锁场景。 首先，事务 A 获取 id = 20 的锁（lock_mode X locks rec but not gap），事务 B 获取 id = 30 的锁；然后，事务 A 试图获取 id = 30 的锁，而该锁已经被事务 B 持有，所以事务 A 等待事务 B 释放该锁，然后事务 B 又试图获取 id = 20 的锁，这个锁被事务 A 占有，于是两个事务之间相互等待，导致死锁。 1.4.2 死锁案例二 首先事务 A 和事务 B 执行了两条 UPDATE 语句，但是由于 id = 25 和 id = 26 记录都不存在，事务 A 和 事务 B 并没有更新任何记录，但是由于数据库隔离级别为 RR，所以会在 (20, 30) 之间加上间隙锁（lock_mode X locks gap before rec）(唯一索引如果没有命中，那么就加间隙锁)，间隙锁和间隙锁并不冲突。之后事务 A 和事务 B 分别执行 INSERT 语句要插入记录 id = 25 和 id = 26，需要在 (20, 30) 之间加插入意向锁（lock_mode X locks gap before rec insert intention），插入意向锁和间隙锁冲突，所以两个事务互相等待，最后形成死锁。
对某个对象加锁，首先得看该节点是否加了互斥的锁，然后检查上级节点有没有加互斥锁，然后检查下级节点是否加了互斥锁。这样检查效率低，所以引入了意向锁。
如果对一个节点加意向锁，说明下层节点正在加锁。对任意一个节点加锁的时候，必须对它的上层节点加意向锁。
锁意向共享锁（IS锁）意向排他锁（IX锁）共享意向排他锁（SIX锁）定义如果对一个数据对象加 IS 锁， 表示它的子节点拟加 S 锁如果对一个数据对象加 IX 锁， 表示它的子节点拟加 X 锁。如果对一个数据对象加 SIX 锁， 表示对它加 S 锁， 再加 IX 锁， 即 SIX = S + IX例子事务 T1 要对 R1 中某个元组加 S 锁， 则要首先对关系 R1 和数据库加 IS 锁事务 T1 要对 R1 中某个元组加 X 锁， 则要首先对关系 R1 和数据库加 IX 锁对某个表加 SIX 锁， 则表示该事务要读整个表（所以要对该表加 S 锁），同时会更新个别元组（所以要对该表加 IX 锁）事务故障：事务运行到正常终止点前被终止 解决办法：反向扫描日志文件，查找该事务的更新操作，对这些更新操作做逆操作。直到读到事务的开始标志。
https://www.imooc.com/article/299436 https://www.jianshu.com/p/4bcfffb27ed5 https://segmentfault.com/a/1190000023897572
redo log MySQL的持久化并不是直接持久化到磁盘的，而是使用了redo日志作为中间层，redo日志是InnoDB特有的功能。在一条语句执行的时候，InnoDB会把更新记录写到redo log日志中，然后更新内存里面的数据页。在空闲的时候或者按照更新策略将redo log中的内容刷新到磁盘中。这样当内存中的数据还没来得及持久化到磁盘就发生宕机，我们也可以通过磁盘上的redo log完成数据的恢复，避免数据的丢失。
bin log 数据库的bin log记录了数据库系统的所有更新操作。在主从复制的时候，可以用bin log把主库的更新操作传递到从库中。而且数据库还可以用bin log进行数据的恢复。
两阶段提交保证redo log和bin log的一致性 执行更新语句的时候，redo log记录事务的更新操作，然后将更新后的数据行都设置为prepare，这是prepare阶段。然后将bin log写入内存中，再把bin log持久化到硬盘中，接着提交事务，然后将redo log里面这个事务相关的记录置为commit状态，这就是commit阶段。 以第二阶段的bin log是否写入作为成功提交的标志。如果在bin log记录之前/记录过程中发生宕机，那么回滚操作。如果在bin log记录完成之后宕机，即使redo log还没有commit，也认为是成功提交。在这种情况，机器重启后，会把redo log中那些认为成功提交的事务的记录设置为commit状态。
为什么要引入redo日志？直接持久化到硬盘不行吗？ 如果每一次的更新操作都需要写入磁盘，然后磁盘也要找到对应的记录，然后再更新，整个IO过程的成本和查找成本都很高。所以引入了redo日志作为缓存，先把redo log的内容写到磁盘，等系统空闲的时候再把修改过的内存页刷新到磁盘。redo日志占用的空间很小，并且redo日志是顺序写入磁盘的，所以写redo日志的成本很低。
避免数据冗余和操作异常 假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)，关键字为组合关键字(学号, 课程名称)，因为存在如下决定关系：
(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)
这个数据库表不满足第二范式，因为存在如下决定关系：
(课程名称) → (学分)
(学号) → (姓名, 年龄)
即存在组合关键字中的字段决定非关键字的情况。
由于不符合2NF，这个选课关系表会存在如下问题： (1) 数据冗余： 同一门课程由n个学生选修，"学分"就重复n-1次；同一个学生选修了m门课程，姓名和年龄就重复了m-1次。 (2) 更新异常： 若调整了某门课程的学分，数据表中所有行的"学分"值都要更新，否则会出现同一门课程学分不同的情况。 (3) 插入异常： 假设要开设一门新的课程，暂时还没有人选修。这样，由于还没有"学号"关键字，课程名称和学分也无法记录入数据库。视图是虚拟的表，并不是预先计算并存储，在使用的时候才通过执行查询并计算出来。 视图本身不包含数据，它返回的数据是从其他表检索出来的。
可以利用其他视图来构造一个视图，也就是说可以嵌套。视图不能索引，也不能有触发器和默认值Order by可以在视图中使用创建视图的语句。其中faculty是视图的名字。 create view faculty as select ID, name, dept_name from instructor