mysql
mysql
存数据
取数据
快（索引，各种缓存空间 change buffer），
安全-->日志（redo log和binlog发生异常的时候，数据不会丢失），
可拓展-->日志，事务（集群），
事务（对多个数据行进行操作的时候.是一组操作集合，多条sql语句的集合。），
锁（避免数据共享竞争）
select语句的执行
连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接 mysql -h$ip -P$port -u$user -p
建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接
查询缓存：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。不建议使用。
分析器：进行词法分析和语法分析
词法分析，这条sql语句是什么，哪些是关键字，哪些字代表着表。
语法分析，这条sql语句满不满足sql语法。
优化器：决定使用哪个索引，多表关联是决定使用表的连接顺序。
执行器：要先判断一下你对这个表 T 有没有执行查询的权限。操作存储引擎，返回结果。
存储引擎：
存储数据，提供读写接口。
存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。
InnoDB 存储表和索引有以下两种方式 ：
①. 使用共享表空间存储， 这种方式创建的表的表结构保存在.frm文件中， 数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中，可以是多个文件。
②. 使用多表空间存储， 这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在 .ibd 中。（ 好像现在的版本都是倾向于这种）
数据库事务隔离级别的实现
隔离级别的出现是为了满足相关的业务场景的使用。比如银行核对账户月初的账户总额和月底的的账户总额和消费数据。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的
数据库会创建一个视图。访问的时候（事务内的查询结果）以视图的逻辑结果为准。
可重复读，事务启动的时候创建。
读提交，每条sql语句开始执行时创建。
读未提交，直接返回记录上的最新值。
串行化，用加锁的方式，避免并行访问。
可重复级别的实现，
每条记录在更新的时候都会记录一条回滚操作。记录上最新的值，通过回滚操作，可以得到前一个状态的值。
拓展：要获取旧版本的数据行时，可以通过最新版本的数据和最新版本到目的版本之间的 Undo Logs 计算出来，因为 Undo Logs 记录了每个对应版本对应行数据的值。 Undo Logs 中分为两种类型: 1. INSERT_UNDO（INSERT操作），记录插入的唯一键值； 2. UPDATE_UNDO（包含UPDATE及DELETE操作），记录修改的唯一键值以及old column记录。
更具体的实现：
每个事务都有唯一的事务id(transaction id)
每行数据有多个数据版本row trx_id，每次事务更新行数据的时候，都会把事务id赋值给数据版本row trx_id.
每个事务启动时，会构造一个数组，记录所有已经启动但是未提交的事务id（事务id最小值是低水位，最大值加1为高水位）.找到这行的数据版本的row trx_id比这个数组的最小值，就是当前事务可见的数据。通过这行当前的数据，和undolog，就可以找到这个事务当前可见的数据。（事务的一致性视图。）（基于整库拍照。秒级创建快照能力也是基于这个原因）
拓展：这个数组有意思。 如果事务 A 是以 begin/start transaction 的方式启动的，那么此时，read-view 还没有创建，事务 A 中第一条读操作执行的时候，read-view 才会创建；此时，离 begin/start transaction 的执行已经过去一段时间，在这段时间中，会有新的事务被创建，这些新事务的 trx_id 都会比事务 A 的 trx_id 大；那么在这个数组中，事务 A 的 trx_id 就未必是数据中最后一个元素；这意味着，高水位线和事务 A 的 trx_id 之间有别的 trx_id。 如果事务 A 是以 start transaction with consistent snapshot 的方式开启事务，那么 read-view 在这一瞬间创建，事务 A 的 trx_id 就是数组中最后一个元素，高水位线就比事务 A 的 trx_id 大 1。（也就是说，高水位和当前trx_id之间可能还会有其他trx_id）
补充：
更新数据都是先读后写，只能使用当前读。当这行数据被另一个事务更新时，会加上写锁，在事务提交时，才会释放锁。两阶段锁协议（需要时加上，事务提交时才释放）。
锁，
乐观锁， 读写锁不互斥。
在读数据的时候，默认不会有其他线程修改数据。
相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制，由开发者实现。一般的实现乐观锁的方式就是记录数据版本。
悲观锁， 读写锁互斥
共享锁（读锁）
表锁
排他锁（写锁）
行锁
表锁
行锁
select不加锁，但可通过如下语句加锁
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE
UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁
两阶段锁协议：更新时加锁，事务提交时才释放锁。
行锁升级为表锁：
如果不通过索引检索数据（update后面的where语句），那么innodb将会对表中所有的行加锁，跟表锁一样。
死锁和死锁检测：
多个线程在互相等待对方的锁释放。
解决办法：
通过参数设置直接进入等待，直到超时。
主动死锁检测。发现死锁的时候，主动kill调其中一个线程。
很耗性能。当要更新的行上，有锁时，就要检测死锁。（一致性读不会加锁，不需要做死锁检测。 B等A, D等C. 来了个E等D,那么E，只检测D.
更新热点行，就会发现请求没几个，cpu利用率很高。
那如何解决这种问题？
业务确保不会发生死锁，关闭死锁检测。
逻辑上的一行，拆分多行。
间隙锁：
使用范围条件而非等值查询的时候(update后面的where语句)，并请求共享或者排他锁时。innodb会对已经存在的数据加锁，并且对于条件范围内不存在的数据记录(间隙)加间隙锁。
表锁
lock table ... read/write
读锁阻塞写，不会阻塞读。写锁既会阻塞读，又会阻塞写。
select 读锁
UPDATE、DELETE、INSERT 写锁
元数据MDL锁
作用是防止DDl和DML的并发冲突。读写数据与表格结构的冲突。
增删改查，加MDL读锁
表结构变更，MDL写锁。
读锁之间不阻塞，读写锁之间阻塞，
当有写锁阻塞，后面请求的读取也会阻塞。（因此要避免长事务）
online DDL
拿MDl写锁。
具体内容有这些：
创建新的临时frm文件
持有EXCLUSIVE-MDL锁，禁止读写
根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)
更新数据字典的内存对象
分配row_log对象记录增量
生成新的临时ibd文件
降级成MDL读锁。
真正做DDL。
升级成MDL写锁。
释放MDL锁。
全局锁
FTWRL:Flush tables with read lock,数据库处于只读状态，数据更新语句，数据定义语句，事务提交语句都会被阻塞。用于全库逻辑备份。
备份的另一种做法（需要支持事务的引擎）：使用mysqldump命令，mysql会启动一个事务，来确保拿到一致性视图。在备份的过程中可以正常的更新。
备库的时候要全库只读，为什么不使用这种方式set global readonly=true
一是，在有些系统中，readonly 的值会被用来做其他逻辑
二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高
binlog and redlog
binlog
记录的是这个语句的原始逻辑
redo log更新语句
先将记录写入redo log，再更新内存。
记录的内容是：在某个数据页做了什么修改。
redo log两阶段提交(这里的两阶段提交是指事务执行commint语句的时候分为两阶段提交，而不是指一个大事务中，每条sql语句都执行两阶段提交。那大事务中，每条sql语句，是怎么写入redo log的呢？ 其实每执行一条sql语句，都会写入redo log buffer中，同时后台会有线程写入到磁盘（写入磁盘分为两步，参考23节。）)
引擎写redo log，处于prepare状态
执行器写binlog
通知执行引擎将redo log将状态改为commit（提交事务）。
（redo log 分为prepare 和 commit阶段提交。）
如果不两阶段提交,会造成主备不一致。
redo log没有提交成功，binlog提交成功，主库数据事务回滚，备库数据没有回滚
redo log提交成功，binlog没有提交成功，主库数据正常，备库数据缺少。
binlog和redo log的区别
redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
在不同的阶段，mysql发生异常重启是怎么保证数据的完成性的。
prepare之后，binlog之前，恢复时，事务回滚，binlog没有完成，不会传到备库。
binlog写完，redolog没有commit.redolog的prepare完成，如果binlog的事务完成，那么提交，否则回滚。
mysql是怎么知道binlog是否完成？
mysql可以通过checksum来判断binlog是否完成。statement格式的binlog，结尾会有commit,row格式的binlog最后会有XID event.
redo log 和binlog是怎么关联起来的？
有共同的数据字段，xid
change buffer更新语句
节省随机读磁盘的io消耗
数据页在内存，直接更新，（注意这里是数据页，不是这条数据）
不在内存，将更新操作缓存在change bugger中。
查询时，将数据页读取到内存，会meger“数据页和changge buffer”
在内存有拷贝，也会写入到磁盘
唯一索引不适用change buffer ,在索引校验唯一性的时候，会将数据页读取到内存，直接更新内存更快。
在实际中，redo log和change buffer是不同概念。
redo log记录的是改变change buffer这个动作。
redo log主要节省的是随机写磁盘的io消耗，而change buffer主要节省的则是随机读磁盘的io消耗。
buffer pool
内存数据页存储在缓冲池中
刷脏页
脏页和干净页
内存数据页跟磁盘数据不一致，这个内存页就叫脏页
内存数据页跟磁盘数据页一致，这个内存页叫干净页。
什么时候刷？
redo log 满了.
【mysql在更新数据的时候，写内存（数据页在内存上，直接更新内存，不在内存写change buffer），写redo log（记录的就是内存数据页的变更或者changebuffer的变更）】
当redo log 满了，不再接受更新的请求，所有的更新请求会被堵住。
系统内存不足，需要淘汰旧的数据页，
系统空闲时
mysql正常关闭
什么情况下的刷脏页会影响性能？
1.一次查询要淘汰的脏页太多。查询时，要把数据读入到内存中的。当内存不足时，就要刷新脏页。
2.日志写满，更新全部堵住。写的性能为0.
那有什么应对情况？
控制刷盘速度。
脏页比例，上线75%
redo写盘的速度。
怎么刷脏页？
数据页在内存中，直接将内存数据更新到磁盘中，
数据页不在磁盘中，将数据读取到内存中，然后redo log更新内存的内容。更新完成之后写盘同上一个步骤。
mysql在两阶段提交的的不同阶段提交发生异常重启，是如何保证数据完整性的？
prepare之后，binlog之前，事务回滚
binlog之后，commit之前，事务提交。（恢复数据时，mysql会判断redolog是否已经commit,如果没有的话，就会判断binlog是否完整，如果完整，那么就提交事务。 redo log 和binlog有个字段对应起来XID）
为什么binlog没有crash_safe能力？
1.mysql在设计之初，myisam就没有支持崩溃恢复的能力。而innodb既可以支持事务，又支持崩溃恢复。所以就干脆使用innodb的崩溃恢复。
2.binlog没有能力恢复数据页。比如事务已经提交，binlog已经生成，但是更新的数据只在数据页，如果数据库崩溃了，那么数据页的数据就会丢失，但是binlog并不会重新应用这个事务的日志。
redo log为什么不能代替binlog.
binlog可以归档，redo log循环写，写完了会删除。
mysql依赖binlog.高可用的基础就是binlog复制。异构架构系统靠消费binlog日志来更新自己的数据。
redo log和binlog是怎么保证数据的完整性的？23节
两阶段提交是指事务执行commint语句的时候分为两阶段提交（prepare commit），而不是指一个大事务中，每条sql语句都执行两阶段提交。那大事务中，每条sql语句，是怎么写入redo log的呢？ 其实每执行一条sql语句，都会写入redo log buffer中，同时后台会有线程写入到磁盘（写入磁盘分为两步）。。【【这里的prepare，应该是指写完了所有的redo log buffer.commit是指将这些redo log buffer持久化到磁盘。prepare和commit中间的写binlog过程是指一次性把binglog cache写入到磁盘文件的binlog文件中，这部分属于各人猜测。】】
只要 redo log 和 binlog 保证持久化到磁盘，就能确保 MySQL 异常重启后，数据可以恢复
binlog写入机制
事务执行过程中，先写入到binlog cache,事务提交时，binlog cache写入到binlog中。
一个事务的binlog是不能被拆开的，不管事务多大，也要保证一次写入。
系统分配binglog cache给每一个线程个，参数 binlog_cache_size 控制 binlog cache 内存的大小。超过了这个参数规定的大小，就要暂存到磁盘。
事务提交的时候，执行器会把binlog cache 写入到binlog中，分为两步（也就是说这个写入过程分成两部分，而不是这两步，有一步是写binlog cache)
write 写入到文件系统的page cache
fsync 真正写盘 此时才会占用IOPS（衡量计算机存储设备的性能方式，每秒的读写次数）
参数 sync_binlog 取不同的值时，write和fsync是有不同的表现的。
sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。 生产上常见设置100~1000，风险是会丢失N个事务的binlog日志。
redo log写入机制
事务的执行过程中，先写到redo log buffer 后写入到redo log
redo log的三种状态
存在redo log buffer中。
写到磁盘write,但是没有持久化fsync.物理上是在文件系统的page cache里面（写入很快）
持久化到磁盘，也就是fsync.（写入很慢）
innodb_flush_log_at_trx_commit 参数决定了redo log的行为
设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。
WAL 机制是减少磁盘写主要得益于两个方面：
redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
组提交机制，可以大幅度降低磁盘的 IOPS 消耗。
MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？
设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志。
将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。
索引及其数据结构
为什么没有索引查询就慢？
找数据：寻磁道（速度慢，费时），找扇区（速度快），进行磁盘IO，速度很慢。
数据在磁盘分布是无序的，分布在各个地方（逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。
存在不同的扇区，分布在不同的磁道。
每查找一次进行一次磁盘io.二叉树也是，每遍历一次数据节点，进行一次IO。
内存和磁盘是怎么交互的？
内存和硬盘交互的基本单位是页的整数倍，页数有上限，一页一般是4K，一次IO只能读取几页数据，因此mysql一个度一般就是一页，一次查询4K
索引是什么？
索引是帮助mysql获取排好序的数据的数据结构。
索引存储在文件里。
既然索引是一种数据结构,为什么不使用红黑树，二叉树等等数据结构？？
二叉树？为什么不用二叉树？当数据量大的时候，节点多，搜索次数多.
红黑树?红黑树，是一种二叉树的变形（会对数据重新排序，避免深度过深），也会存在节点多，搜索次数多（搜索次数多，磁盘IO多）。
HASH？查询效率高，但是不适合使用范围查询
BTREE
另一种形式的问答
索引常见模型（这只是常见的索引模型，并不代表说mysql有这种类型的模型）：
数据库的数据模型决定了使用场景。
哈希表，有序数组，搜索树
哈希表：
数据结构，以key-vulue的形式存储.key就是你要的查询条件，value就是你要的查询数据。
存储时，用hash函数把key转换成一个确定的位置，value就放在数组的这个位置，当有重复位置时，会拉出一个链表。
查询时，用hash函数计算这个key,找到对应的具体位置，遍历链表的数据，找到需要的数据。
优点：查找速度快
缺点：只适合等值查询，不适合范围查询。
使用场景：等值查询场景。Memcached 和 nosql引擎。mysql只有memory引擎支持。
有序数组
数据存储在有序数组中
优点：适合等值查询和范围查询，查找非常快，使用二分法查找。
缺点：更新数据成本高，比如往中间插入一个数据，在中间位置插入新的数据，并且要复制原来的数据。可以java的数组元素的增加。
使用场景：静态存储引擎，比如2017年某个城市所有的人口信息。mysql没有这种索引类型。
搜索树
每个节点的左儿子节点比父节点小，右儿子比父节点大。查找的时间复杂度是O(long(N)).
索引不止只存在内存中，还要写在磁盘上，为了减少读磁盘，因此查询过程中要尽量访问少的数据块）。因此不适用二叉树，而是使用N叉树（mysql中,在整形字段中，N的值差不多是1200.），深度一般是3~5个高度
mysql目前支持以下索引类型
BTREE 索引 ： 最常见的索引类型，大部分引擎都支持 B 树索引。
HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
Full-text （全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。
用于文本数据检索，类似like查询，但是比like查询快。背后的原理类似es查询原理。
只有特定的数据类型才能建全文索引 char、varchar、text
独特的语法格式，使用关键字（ match 和 against） ：select * from fulltext_test where match(content,tag) against('xxx xxx');
B-Tree
度(Degree)-节点的数据存储个数
叶节点具有相同的深度
叶节点的指针为空
节点中的数据key从左到右递增排列（b+tree数据排列是有序的）
B+tree
为什么用B+树而不用b树呢？
非叶子节点不存储data，只存储key（索引）,可以增大度（度增大了，一次性查取出来的数据就多）。
数据存储在叶子节点。
叶子节点之间用指针（范围查找，更加方便,如果查询的数据在多个数据页之间，可以顺序定位到下一个数据页）相连。数据按顺序排序。
B+Tree索引的性能分析
一般使用磁盘I/O次数评价索引结构的优劣
预读：磁盘一般会顺序向后读取一定长度的数据(页的整数倍)放入内存
局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用
B+Tree节点的大小设为等于一个页，每次新建节点直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，就实现了一个节点的载入只需一次I/O
B+Tree的度d一般会超过100，因此h非常小(一般为3到5之间)
度是不是越大越好？
查询一行数据，是把整个数据页查出到内存中（参考上面内存和磁盘是怎么交互的），然后在内存横向查找该节点所要的数据。
因此并不是度越大越好，否则会导致内存不够，同时取出数据也会很久（每次查询几页，数据量大，要查询多次，进行多次IO,所以取数据久）。
数据容量越小，节点存储数据的个数越多，度就越大。
存储引擎是基于表的。
myisam（非聚集索引）
索引文件和数据文件是分离的
.frm (存储表定义)；
.MYD(MYData , 存储数据)；
.MYI(MYIndex , 存储索引)；
叶子节点存储文件指针。
主键索引，非主键索引都是叶子节点存储文件指针。
对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表
innodb。
数据文件本身就是索引文件（也叫聚集索引）
主键索引存储的是数据本省
非主键索引存储的是主键索引的值。（字符串存储比较ascii码大小）
innodb为什么一定要有主键？并且推荐使用整型的自增主键？
数据存储在主键索引上。没有主键则系统自动帮忙生成。
自增，插入数据有序，不会导致页分裂。当连续插入的时候可以少分配新的空白页
整型，所需要的空间小。整型要比字符串占用空间小。
支持事务，崩溃恢复的能力。
为什么主键和非主键存储的值是由这么的区别？
插入数据，由两份数据，浪费空间,还需要考虑数据一致性问题。
联合索引数据长什么样子
varchar:key1+key2+...组成一个key
那不同数据类型的联合索引是怎样的？
索引最左前缀原理？
叶子节点和非叶子节点的区别？
myisam主键索引上存储的是文件指针，而innodb却是数据内容，那innodb没有指针怎么找文件？
猜测：myisam索引文件和数据文件是分离的。innodb索引和文件内容是是存在一起的，不需要指针。
innodb的btree索引
表都是根据主键顺序以索引的形式存放，数据都是存储在B+树中。
主键索引：key值是主键，value是整行数据。
非主键索引：key值是索引列的值，value值是主键值。
所以基于非主键索引的查询，都要先查询主键值，然后拿着主键值，到主键索引中查询出整行数据。
从性能和空间上来看，表中要有自增索引，一般不使用业务字段做索引。
如果新插入的键值不是递增，那么可以能导致数据页分裂，不但影响到性能，还会影响到数据页的利用率（比如，一个数据页，分裂成两个，那么利用率只有50%）
因此，一般的表都需要一个自增主键，从而避免触发叶子节点的分裂。
由于非主键索引的value值都是主键值，那么主键长度越小，普通索引的叶子节点就越小，占用的空间就越小（因此主键的字段类型越小越好，最好是整型）
什么场景是适合业务字段做主键的？
1.只有一个索引（不用考虑其他索引的叶子节点大小）
2.索引必须是唯一索引。（避免回表，每次都要搜索两棵树）
索引优化：
自增唯一主键
覆盖索引：
从非主键索引上查询到主键的值，然后到主键索引上查询需要的数据，叫做回表。
覆盖索引，索引上有我们需要的查询结果，不需要回表，这种索引叫做覆盖索引。使用覆盖索引会提升性能，缺点是浪费空间。
最左前缀：
几个字段组合成的索引，查询条件是联合索引的前几个字段，也是可以走索引查询的。爬楼梯式的例子。 这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 N个字符
如何安排字段的顺序？
第一原则是：通过调整顺序少维护一个索引，是优先考虑的。
第二原则是：空间原则。假如有ab两个字段都要建索引，a字段大，b字段小，那么建立联合索引ab,和索引b.
索引下推：
联合索引中，如果查询条件在联合索引中不满足。在mysql5.6之前是查询到了这个数据，还是会进行回表查询。在mysql5.6之后，如果不满足条件的直接过滤掉。不在回表查询。
索引失效：
b+树能够快速定位的能力源于同一层兄弟节点的有序性。对索引字段做函数操作，会破坏索引的有序性，优化器就会放弃走树搜索功能，但并不是要放弃使用这个索引。
1.字段是字符串类型，输入的是整型，触发了函数操作，放弃走树搜索功能。
2.关联查询，关联字段字符集不一样，超集会向子集的字符集转化数据类型（超向子转换是为了避免数据精度丢失），触发了函数操作，也会放弃走树搜索功能。
高可用 24节
mysql的高可用架构，是通过主备一致性，主备延迟小来得以保证的。
主备复制的基本原理
什么是主备：主库A负责更新，备库B同步A更新的数据（一个库负责更新，一个库负责同步），一般备库B设置只读的状态。只读状态对supper权限用户是无效的，可以继续同步。
原理：
备库B和主库A维持一个长连接，主库A有一个线程专门服务于备库B这个长连接。
1.备库通过change master 命令，得知主库的IP,端口，账号，密码，以及这次要请求的位置。
2.备库执行start slave命令，启动两个线程，io_thread和sql_thread,io_thread负责跟主库建立连接。
3.主库校验完账号，密码，从指定的位置发送binlog给到B.
4.备库B拿到binlog，写入中转日志（relay log）（为什么要有中转日志？因为binlog都不同的格式，需要解析出来才能执行具体的sql命令）
5.sql_thread读取中转日志，解析出日志里的命令，执行sql语句
比较白话的解释：
1. master 将数据变更写入二进制日志（其实就是binlog中）, （这些记录叫做二进制日志事件，binary log events, 可以通过show binlog events 进行查看）
2.slave 将master的Binary log events拷贝到它的中继日志（relay log）。
3.slave 重放 relay log中继事件，将改变反映它自己的数据。
https://blog.csdn.net/u013256816/article/details/52536283
binlog的三种格式
statement
记录的是sql原句。对哪个库哪个表，做了什么操作。还有xid（事务是否被正确的提交）.
delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;
可能会出现主备不一致的情况。在主库走的是A索引，在备库走的是B索引。mysql认为这是有风险的。
row
当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样
row 格式的 binlog 里没有了 SQL 语句的原文，而是替换成了两个 event：Table_map 和 Delete_rows。
通过借助mysqlbinlog工具，可以查看具体的内容
server id 1，表示这个事务是在 server_id=1 的这个库上执行的；
Table_map event表示要操作哪个库，哪个表。
可以得知各个字段的值。
binlog_row_image 的默认配置是 FULL
因此 Delete_event 里面，包含了删掉的行的所有字段的值。如果把 binlog_row_image 设置为 MINIMAL，则只会记录必要的信息
Xid event，用于表示事务被正确地提交了
mixed
为什么有mixed格式？
statement可能会导致主备不一致，row占用空间，mysql会判断语句是否会引起主备不一致，是的话就使用row格式，不是的话就使用statement。
生产推荐使用row格式。好处是可以恢复数据。
delete可以转为insert，因为保存了删除整行的数据
insert可以转为delete,可以准确定位到刚才删除的信息
update记录更新前和更新后的句子，只要把event对调即可。
mixed格式的数据 有些采用statement格式，传到备库的时间字段的数据可能会不一致，导致了主备不一致的问题。
用 binlog 来恢复数据的标准做法是，用 mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行
循环复制问题的解决方案
mysql中互为主备的双master结构，
规定两个库的server id不同，
A传binlog给B B传回binlog给A的server id相同。
A判断到B传回来的server id是自己，那么就丢弃这个日志。
主备切换的场景，主备延迟的原因，主备切换的策略参考第25节。
主备延迟
主备切换可能是一个主动运维动作，比如软件升级、主库所在机器按计划下线等，也可能是被动操作，比如主库所在机器掉电。
主备延迟的来源
1.备库所在机器的性能要比主库所在的机器性能差
2.备库的压力大，运维在后台分析语句。
3.大事务。一次delete很多语句。
4.大表进行ddl.
5.备库的并行复制能力
主备切换的策略
可靠性优先策略。当主备延迟时间小于多少秒的时候，进行主备切换，会有短暂的业务暂停。
可用性优先策略。不会有业务暂停，但可能会造成主备不一致。
备库延迟好几个小时的原因 26节
sql_thread如果用的是用单线程，那么就会有这个问题出现。因此有多线程复制。
按表分发，按行分发，按库分发
一主多从的主备切换流程 27节
1.基于位点的主备切换：
在备库执行命令change master,并指定位点，说明从库要从备库的什么文件的哪个地方开始，同步更新数据。
但是会有一个问题，如果备库的同步位点之后的更新操作，在从库已经执行了，那么从库接收备库的binlog时，解析并执行语句会报错。
那么此时，有两种办法解决。
1.通过命令主动跳过事务，直到不再报错为止。
2.设置参数为slave_skip_errors 1062 1032 跳过指定错误。一般报错都是唯一键冲突1062，或者删除数据找不到行1032。
2.基于GTID的主备切换
事务提交的时候，会生成一个全局事务ID,并且会加入到一个GTID集合中，如果集合已经有了这个id,那么就会跳过这个事务的执行。比如从备库里，从了一个GTID，在从库里，这个gtid是存在gtid集合中的，那么就会跳过这个事务。
读写分离 28节 （这节在实际工作中用的不多，一般都是dba帮忙实现，或者是数据库中间件就帮你完成了。读写分离必然带来性能上的损耗。）
1.读写分离架构，
客户端直连
带proxy中间件
2.读写分离导致的过期读的解决方案。
1.强制走主库查询方案，根据查询分类，必须拿到最新结果的走主库，否则走从库。
2.sleep方案：主库执行完之后，睡眠1秒，再去查询从库。可能还是会有过期读的问题。
3.判断主备无延迟方案。
A.通过命令查看备库是否有延迟，知道延迟为0秒，才去从库查。
B.通过对比主备库之前的位点是否一致，一致就去从库查。
C.对比 GTID 集合确保主备无延迟,查看从库接收到gtid集合和已经执行完的gtid集合是否一致。
4.配合 semi-sync.
在从库收到binlog后，返回一个ack给到主库，主库接收到这个ack之后，才表示事务更新完成。再结合第三种方案判断，再决定是否去从库查询。在一主一备上才成立。
5.等主库位点方案
通过命令得到主库的位点，再查询从库从这个位点之后的事务集合，如果查询出来是大于0的，则表示从库已经更新了该语句。
count(*)
1.不同的存储引擎，计算count(*)的方式是不一致的。myisam会把表的总记录数，存储到磁盘上。innodb则由于多版本并发控制的原因，返回多少行是不确定的，因此是需要一行一行读取数据的。
2.那假如有一个功能需求，就是要显示你这张表的记录总数怎么办？1.使用缓存，但是会出现数据不一致情况。可使用分布式锁解决。2.存在表里。不同的会话，在更新同一张表的时候，会加锁。同时，因为事务的特性，所有当前会话对数据的修改，对其他的会话不可见。
3.对比count(*)、count(主键 id)、count(字段) 和 count(1)
server 要什么 innodb就返回什么。比如count(id)，innodb就返回id值。
mysql对于count(*)有优化。count(*)直接记录总行。
order by
explain 执行计划 的extra 字段 的“using filesort”表示排序，“using index”表示使用覆盖索引,"
Using temporary"表示使用临时表。
每个线程会分配一块内存sort_buffer用于排序。
排序语句的执行流程：
全字段排序：根据查询条件找到主键id,到主键索引查询出需要的字段，放入sort_buffer.按照排序要求(升序还是降序)进行排序，并返回给到客户端。
如果sort_buffer太小放不下要排序的数据量。就需要用到磁盘临时文件
这个磁盘临时文件，不叫内存临时表。执行计划里没有using temporary。
有专门的命令查询是否用到磁盘临时文件 通过查看 OPTIMIZER_TRACE 的结果来确认的，你可以从 number_of_tmp_files 中看到是否使用了临时文件
rowid排序。如果，每次查询要返回的字段数太多，sort_buffer里面要放的字段数太多，那么就得需要很多个临时文件，排序性能非常差。因此可以优化为，到主键索引，取值时，只取要排序的字段和主键放到sort_buffer中，排序，返回id,再到原表去查询所需的字段，再返回给客户端。多访问了一次主键索引。
mysql的选择：优先选择全字段排序（内存够，就尽量使用内存，少进行磁盘访问）
oderby 优化：
1.创建查询条件字段和排序字段的联合索引，使得数据是有序的。那么查询的时候，就不用再进行排序了。
2.创建查询所需要的字段的覆盖索引，可以不走回表查询。但是会有比较浪费空间。
order by rand(): 17
会使用到内存临时表和rowid排序算法。开销大。会把所需要的数据和对应的随机数读取到临时表，初始化sort_buffer，读取临时表的随机数值，和对应的位置信息到sort_buffer。在sort_buffer对随机数排序，取最小前几位的位置信息，再次回查临时表，并把数据返回给客户端。
内存临时表大小是有限制的（mysql所有的设置，都可以命令设置参数），当超过这个大小，会使用磁盘临时表。
不管是优先队列算法（并不需要所有的数据都排序好，只需确保在排序时，随时保证堆的前几位是最小即可），还是归并排序算法（字段所有内容排序），都需要扫描大量的行数，浪费大量的资源。
真随机：
取整个表的行数，
得到随机数Y1，Y2,(要取前几个，就得到多少个随机数)
执行主键的limit y 1;（使用的优先队列算法）
order by 开销
1.要初始化内存sort_buffer
2.排序量大的话，还要借助临时文件。
3.rowId排序要求回表.
对大表进行全表扫描，会不会把数据库内存打爆。 33
不会
mysql是边读边发的。
重复获取行直到net_buffer（默认16k）写满。调用网络接口发送。本地网络栈写满了（状态是sending to client）就进入等待状态。
sending data 和sending client的区别。
sending data并不一定值“正在发送数据”，可能是处于执行器过程中的任意阶段。
innodb的buffer pool
改进LRU算法，用链表来实现。
分为young区和old区。处于young区的数据，每访问一次，将数据页移到链表的头部。新插入的数据页处于old区，第一次和最后一次访问的时间不超过1秒，那么数据页会保留在old区。当进行全表扫描大表的时候，会保留young区数据，清除掉old数据，提升buffer pool查询命中率。
多表关联查询 35
总的来说，就是多表关联查询可以使用join .前提是被驱动表上有索引，驱动表是小表。有MRR优化，会对被驱动表的主键字段进行顺序的排序，可以磁盘顺序读。此外还有BKA优化，将驱动表的数据放入join buffer中。
NLJ算法： Index Nested-Loop Join 被驱动表用得到索引 使用到被驱动表的索引，比通过应该层拆分成多条语句执行效果好。
select * from t1 straight_join t2 on (t1.a=t2.a); a有索引 （实际项目中，肯定会指定某一个查询条件，不会对驱动表进行全表扫描）
这条语句会遍历表驱动表t1中所有的行，然后根据a值，到被驱动表查询，使用到了被驱动表的索引。 使用到了被驱动表的索引，叫做NLJ算法。
所以要不要使用join，还是进行单表查询？
结论：要使用join（前提是用得到被驱动表的索引）。客户端要拼接sql语句和结果，性能相对没有那么好。
怎样选择驱动表？
让小表做驱动表。
BNL算法 Block Nested-Loop Join 被驱动表用不到索引 没有使用到被驱动表的索引，扫描行数是两张表的乘积。
算法的流程是这样的（跟被驱动表的算法流程不一样。）
把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；
扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回
扫描的行数还是两张表的行数的乘积，只不过这种算法是在内存中进行的，比较快。
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放
这种情况下尽量不要使用join。
Multi-Range Read（MRR）优化 这个优化的主要目的是尽量使用顺序读。
1.回表查询，是一行一行查询的。
2.顺序读，比随机读性能要好（因此主键一般是顺序递增）
3.因此，会根据索引a,找到所有的id,放入read_rnd_buffer中，按顺序排序，到主键索引中查记录，返回结果。（在被驱动表上，会对主键做排序。）
4.使用MRR，explain计划的extra字段多了using MRR
BKA算法（Batched Key Access） 将驱动表的数据放入join buffer 中，避免多差对驱动表进行磁盘IO
1.NLJ算法逻辑，从驱动表t1，一行行取值，再到被驱动表t2做jion.不能使用到mRR
2.优化，取出驱动表t1的一部分数据，放入到join_buffer(在BNL算法里暂存驱动表数据).
3.join buffer放不下，就会分段放。
4.BKA启用。
BNL算法缺点
1.多次扫描被驱动表。占用磁盘io资源
2.占用cpu资源。
3.导致buffer pool的热点数据被淘汰，影响内存命中率。
实际应用中，查看执行计划，如果是使用BNL算法，就需要对这个语句进行优化。
1. 在被驱动表中建立索引，BNL转BKA. 缺点浪费空间。
2.使用临时表加索引。
3.总体思路是，可以使用得上被驱动表上的索引，采用BKA算法提升查询性能。
4.最后一种优化办法。由于mysql不支持hash join。可以在业务端代码实现，取出t1表的数据，放在hash结构的数据。从t2表中筛选出需要的数据。
临时表
可以看到，临时表在使用上有以下几个特点：
建表语法是 create temporary table …。
一个临时表只能被创建它的 session 访问，对其他线程不可见。
临时表可以与普通表同名。session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
show tables 命令不显示临时表。
临时表可以重名
innodb的临时表的表结构定义是以frm为后缀。前缀是“#sql{进程 id}_{线程 id}_ 序列号
5.7版本开始，mysql有一个临时文件空间，专门存储临时表的数据。
每个线程都维护了自己的临时表链表。每次操作表时，都先查询临时表链表，如果临时表存在，优先使用临时表。
线程结束是，执行 “DROP TEMPORARY TABLE + 表名”操作。
主备复制
如果当前的 binlog_format=row，那么跟临时表有关的语句，就不会记录到 binlog 里。也就是说，只在 binlog_format=statment/mixed 的时候，binlog 中才会记录临时表的操作。
gourp by --37
如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。
查看是否使用到内部临时表
执行计划，extra字段会有Using temporary
使用union会用到内部临时表
group by 执行流程
创建临时表（临时表的字段，就是sql语句所需返回的结果字段），到主表查询记录，将统计结果记录到临时表中，最后到sort buffer进行排序，最后将返回结果。(sort buffer是用来进行排序的)
如果不使用排序可以在语句末尾加上order by null（也就是可以不用到sort buffer）
内部临时表默认大小是16M,超过这个大小，就会使用磁盘临时文件。
group by 优化 --使用索引
1.使用到临时表是因为计算结果是无序，需要临时表来记录并统计结果。
2.使用mysql5.7支持generated column,可实现列数据的关联更新。此时不需要用到临时表，也不需要排序。因为索引是有序的，每扫描一行，就在对应的列中增加1。具体怎么实现可以看文章。
group by 优化 --直接排序。
不适合使用索引功能（即某些字段不适合建立索引），可以用此优化。
当要统计的数据量大，会用到磁盘临时表
在 group by 语句中加入 SQL_BIG_RESULT（告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。），直接排序算法。
使用SQL_BIG_RESULT，会用到sort buffer。用不到临时表。
mysql什么时候会使用到内部临时表。
1.mysql可以一边执行，一边得到结果,就不需要而外的内存保存中间结果，否则需要。
2.jion buffer 是无序数据，sort buffer 是有序数组。临时表是二维表。
3.执行逻辑需要用到二维特性。就会优先考虑临时表。
未知：41
insert … select
为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文本文件，然后再写回目标表
mysqldump 命令将数据导出成一组 INSERT 语句。你可以使用下面的命令：
导出文件
mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
执行文件
mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"
导出 CSV 文件
MySQL 提供了下面的语法，用来将查询结果导出到服务端本地目录：
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
load data infile '/server_tmp/t.csv' into table db2.t;
mysqldump -h$host -P$port -u$user ---single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv
物理拷贝方法。
可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能
相比于逻辑导入的方法，import 语句的耗时是非常短的。
源表和目标表都是使用 InnoDB 引擎时才能使用
正常情况下，grant 命令之后，没有必要跟着执行 flush privileges 命令 42
查询慢：
1.等MDL锁。表被其他线程持有住锁了。 通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。（z）
explain
什么是explain?
使用EXPLAIN关键字可以模拟优化器执行SQL语句，从而知道MySQL是 如何处理你的SQL语句的。分析你的查询语句或是结构的性能瓶颈
怎么用？
在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）
两个变种：
explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以 得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。
explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。
explain列中的信息。（通常关注哪几个字段？这些字段的值是怎样的才算是最好的？）
一般关注type key row extra这几列。从这几列可以得知使用了什么索引，扫描了多少行，使用了唯一索引还是普通索引，还是全表扫描，有没有使用到临时表，是否用到排序。
1.id列的编号是select的序列号。有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行
2. select_type列
simple：简单查询。查询不包含子查询和union
primary：复杂查询（包含子查询）中最外层的 select
subquery：包含在 select 中的子查询（不在 from 子句中）
derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
union：在 union 中的第二个和随后的 select
union result：从 union 临时表检索结果的 select
3.table列
这一列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。
4.type列
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到range级别，最好达到ref
NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
const, system：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system
eq_ref：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
index：扫描全表索引，这通常比ALL快一些。（index是从索引中读取的，而all是从硬盘中读取）（我自己尝试对字符串的列。查询时不加单引号，但走的是全表扫描（all），可能mysql本身会判断，究竟是选择哪个全表扫描索引好，还是全表扫描索好。）
ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了
5. possible_keys列
这一列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。
6. key列
这一列显示mysql实际采用哪个索引来优化对该表的访问。
如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。
7.key_len列（如果选择字段类型，参考alibaba开发手册）
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
key_len计算规则如下：
字符串
char(n)：n字节长度
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
时间类型
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
8. ref列
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
9. rows列
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。（也就是查找所需要的扫描的行数）
10. Extra列
（优化的目标是达到using index）
这一列展示的是额外信息。常见的重要值如下
Using index：查询的列被索引覆盖，并且where筛选条件是索引的前导列，是性能高的表现。一般是使用了覆盖索引(索引包含了所有查询的字段)。对于innodb来说，如果是辅助索引性能会有不少提高
Using where：查询的列未被索引覆盖，where筛选条件非索引的前导列
Using where Using index：查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据。（走索引，但是索引失效了。）
NULL：查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引
Using index condition：与Using where类似，查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。（比如进行排序order by就会用到临时表，使用索引优化可以避免排序，因为b+tree索引是有序的。查出来的数据就是有序的。一般会创建查询条件字段和排序字段的联合索引。）
Using filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
索引的最佳实践（背后的原理是什么，要弄清楚。）
1. 全值匹配
2. 最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
比如：字符串不加单引号索引失效
b+树能够快速定位的能力源于同一层兄弟节点的有序性。对索引字段做函数操作，会破坏索引的有序性，优化器就会放弃走树搜索功能，但并不是要放弃使用这个索引。
4.存储引擎不能使用索引中范围条件右边的列
5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select * 语句
不要使用select * 的主要原因有两个，其他都是扯淡。
在有可能使用到覆盖索引的场景中，无法使用到覆盖索引。
查询出来多的字段，影响传输性能。
6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
7.is null,is not null 有时索引会失效，mysql会根据具体的情况选择走索引还是全表扫描。
8.like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作
a）使用覆盖索引，查询字段必须是建立覆盖索引字段
b）当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效！
10.少用or,用它连接时很多情况下索引会失效
or查询语句不一定会索引失效。or查询后面的查询条件，没有索引，才会导致整个查询的索引失效。
or前后的字段都有索引，索引不会失效。
其实完全不用记住哪些情况下索引会不会失效。真正的好习惯是，查看执行计划，看是否会走索引。
视图：
视图（View）是一种虚拟存在的表。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。
视图相对于普通的表的优势主要包括以下几项。
简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
使用场景：
平时并没有使用。是因为没有遇到相关的使用场景。这个视图更像是某张我们不能访问到的表，dba人员写好了sql语句提供给我们的。 然后实际工作中并没有这种情况，都是自己建表，自己写查询语句。
存储过程：
SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
阿里巴巴开发手册明确指出，禁止使用存储过程，使用存储过程难以调试和扩展，更没有可移植性可言。
常用的语句
-- 查看事务的隔离级别。
show variables like 'transaction_isolation'
show variables like 'tx_isolation';
mysql> select k from t where id=1 lock in share mode; 读锁，共享锁
mysql> select k from t where id=1 for update; 写锁 排他锁。
加读锁 ： lock table table_name read;
加写锁 ： lock table table_name write；
innodb_file_per_table
设置为off，表数据存储在共享空间 删表（drop table）不会删除
设置为on,innodb表数据存储在.ibd后缀的空间中。
mysql> show binlog events in 'master.000001'; 查看binlog记录的statement格式。 24
借助mysqlbinlog工具查看：mysqlbinlog -vv data/master.000001 --start-position=8900;查看binlog记录的row格式 24
mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd; 24
慢查询日志 17节
iptimizer_trace 17节
explain执行计划 16 17
show processlist 19
索引的相关优化文章
https://www.cnblogs.com/yxy2081/p/12454757.html
https://www.cnblogs.com/yxy2081/p/12458987.html
https://www.cnblogs.com/yxy2081/p/12458992.html
关联查询语句。DDL语句，创建索引语句（含索引。不要到时候面试写不上sql语句就尴尬了）
浙公网安备 33010602011771号