Mysql演讲人2020-09-3001三范式三范式033、非主键字段不能传递依赖022、有主键，非主键字段依赖主键，与主键直接相关011、确保每一列的原子性02loglogbinlogredologrelaylogundologserver层的归档日志，记录了所有更改操作逻辑日志，记录了语句所有的逻辑操作文件写完则生成新文件继续追加写属于InnoDB引擎，是物理日志，记录的是某个数据页做什么修改记录修改前的值，用来回滚单击此处添加文本03InnoDB和MyISAM的不同点InnoDB和MyISAM的不同点InnoDB支持事务和mvcc、外键默认采用行级锁InnoDB和MyISAM的不同点索引实现innodb数据文件本身就是主索引文件，而myisam是分开的innodb是聚集索引聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据。辅助索引：叶子页中保存主键值，通过这个主键值来回表查询到一条完整记录myisam叶子节点的数据区域是存放的是地址04事务隔离级别事务隔离级别如何实现事务隔离级别事务隔离级别断电/数据库崩溃时通过redolog重新刷入数据保证了事务的持久性如何实现事务010203通过预写日志实现的，redo和undo机制是实现数据库事务的基础undolog是在事务执行失败的时候撤销对数据库的操作，保证了原子性如何实现事务通过预写日志实现的，redo和undo机制是实现数据库事务的基础undolog是在事务执行失败的时候撤销对数据库的操作，保证了原子性断电/数据库崩溃时通过redolog重新刷入数据保证了事务的持久性事务隔离级别ReadUncommittedReadCommittedRepeatableRead隔离级别23145越高的隔离级别，能解决的数据一致性问题越多，理论上性能损耗更大，可并发性越低。隔离级别依次为SERIALIZABLE>RR>RC>Read-Uncommitedserializable隔离级别ReadUncommitted可以读未提交的脏数据ReadCommitted只能读已提交的数据默认隔离级别，如Oracle、SQLServerRepeatableRead其他的事务操作提交，每次返回不同的结果集mysql默认，RC不支持binlog隔离级别serializable读写都加锁隔离级别越高的隔离级别，能解决的数据一致性问题越多，理论上性能损耗更大，可并发性越低。隔离级别依次为SERIALIZABLE>RR>RC>Read-Uncommited05锁全局锁加锁方法一：Flushtableswithreadlock(FTWRL)。方法二：setglobalreadonly=true使用场景：做全库逻辑备份时表锁locktablesxxxread/writeunlocktables主动释放锁，也可以在客户端断开的时候自动释放表锁使用场景InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。MySQL这样设计并不是给你挖坑。它有自己的设计目的。即便你在条件中使用了索引字段，MySQL会根据自身的执行计划，考虑是否使用索引(所以explain命令中会有possible_key和key)。如果MySQL认为全表扫描效率更高，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。表锁使用场景第一种情况：全表更新。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。表锁使用场景第二种情况：多表查询。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。行锁行锁的劣势：开销大；加锁慢；会出现死锁行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：共享行锁行锁优化尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁。尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。锁页锁开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发处理能力一般。只需了解一下。锁间隙锁锁innodb如何加锁总结1InnoDB支持表锁和行锁，使用索引作为检索条件修改数据时采用行锁，否则采用表锁。2InnoDB自动给修改操作加锁，给查询操作不自动加锁3行锁可能因为未使用索引而升级为表锁，所以除了检查索引是否创建的同时，也需要通过explain执行计划查询索引是否被实际使用。4行锁相对于表锁来说，优势在于高并发场景下表现更突出，毕竟锁的粒度小。5当表的大部分数据需要被修改，或者是多表复杂关联查询时，建议使用表锁优于行锁。6为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接影响到一个数据库的并发处理能力和性能。06主从复制主从复制主库的更新事件写到binlog主从复制主库创建一个binlogdumpthread将binlog发送到从库主从复制从库创建一个I/O线程接收binlog并存到relaylog主从复制从库创建一个sql线程将数据写道slave的db复制方式异步成功写入binlog就返回客户端，无须等待传入给从库的过程但是一旦主库宕机，可能丢失数据半同步确保写入relaylog才会通知主库的等待线程若等待超时，则自动转为异步，直到有一台从库成功接收binlog07索引索引索引结构聚集索引与非聚集索引页回表最左匹配原则注意事项索引010203索引结构Mysql选?B+树这种数据结构作为索引，可以提?查询索引时的磁盘IO效率，并且可以提?范围查询的效率，并且B+树?的元素也是有序的。?个B+树的节点中到底存多少个元素？考B+树中?个节点到底多?合适？顺序1/0不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）索引结构Mysql选?B+树这种数据结构作为索引，可以提?查询索引时的磁盘IO效率，并且可以提?范围查询的效率，并且B+树?的元素也是有序的。hash索引：可以快速的精确查询（等于），但是不?持范围查询（大于小于）二叉树：数据越多，则查询成本随着树高增加而增加B树：要?完全平衡?叉树要“矮”，B树中的?个节点可以存储多个元素。但是B+树中的?叶?节点会冗余?份在叶?节点中，并且叶?节点之间?指针相连?个B+树的节点中到底存多少个元素？考B+树中?个节点到底多?合适？B+树中?个节点为??或?的倍数最为合适。因为如果?个节点的???于1?，那么读取这个节点的时候其实也会读出1?，造成资源的浪费。索引结构顺序1/0不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）索引页01各个数据?可以组成?个双向链表02?每个数据?中的记录?可以组成?个单向链表03每个数据?都会为存储内容?成?个??录，在通过主键查找某条记录的时候可以在??录中使??分法快速定位到对应的槽然后再遍历该槽对应分组中的记录即可快速找到指定的记录以其他列(?主键)作为搜索条件：只能从最?记录开始依次遍历单链表中的每条记录。04如果我们写selectfromuserwhereusername=''xxx''各个数据?可以组成?个双向链表?每个数据?中的记录?可以组成?个单向链表每个数据?都会为存储内容?成?个??录，在通过主键查找某条记录的时候可以在??录中使??分法快速定位到对应的槽然后再遍历该槽对应分组中的记录即可快速找到指定的记录以其他列(?主键)作为搜索条件：只如果我们写selectfromuserwhereusername=''xxx''定位到记录所在的?需要遍历双向链表，找到所在的?从所在的?内中查找相应的记录由于不是根据主键查询，只能遍历所在?的单链表了页有个主键为ID的索引，和?个普通name字段的索引执行selectfromuserwhereusername=''xxx''简单说就是mysql内部需要经过两次查询如何判断：执行计划只有在使用了索引，且Extra是Usingwhere的情况下，才代表回表查询数据。先查询name索引上的xxx，找到对应的id=2，然后通过主键索引，找到id=2对应的值覆盖索引索引回表回表有个主键为ID的索引，和?个普通name字段的索引执行selectfromuserwhereusername=''xxx''简单说就是mysql内部需要经过两次查询如何判断：执行计划只有在使用了索引，且Extra是Usingwhere的情况下，才代表回表查询数据。先查询name索引上的xxx，找到对应的id=2，然后通过主键索引，找到id=2对应的值覆盖索引避免回表，如果只查询ID，其实在Name字段的索引上就已经有了，那就不需要回表了。减少树的搜索次数，提升性能0102联合索引只能?于查找key是否存在（相等），遇到范围查询(>、<、between、like左匹配)等就不能进?步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到索引最左匹配原则最左匹配原则12联合索引只能?于查找key是否存在（相等），遇到范围查询(>、<、between、like左匹配)等就不能进?步匹配了，后续退化为线性查找。因此，列的排列顺序决定了可命中索引的列数。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到索引尽量选择区分度?的列作为索引，区分度的公式是COUNT(DISTINCTcol)/COUNT()。表示字段不重复的?率，?率越?我们扫描的记录数就越少。注意事项索引列不能参与计算，尽量保持列“?净”。?如，FROM_UNIXTIME(create_time)=''2016-06-06''就不能使?索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进?检索时，需要把所有元素都应?函数才能?较，显然这样的代价太?。所以语句要写成：create_time=UNIX_TIMESTAMP(''2016-06-06'')。尽可能的扩展索引，不要新建?索引。?如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。在经常使用在WHERE子句，需要排序的列中的列上面创建索引，加快条件的判断速度。索引将某一列设置为defaultnull，where是可以走索引，另外索引列是否设置null是不影响性能的。但是，还是不建议列上允许为空。注意事项删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗MySQL5.7可以通过查询sys库的chema_unused_indexes视图来查询哪些索引从未被使用注意事项尽量选择区分度?的列作为索引，区分度的公式是COUNT(DISTINCTcol)/COUNT()。表示字段不重复的?率，?率越?我们扫描的记录数就越少。索引列不能参与计算，尽量保持列“?净”。?如，FROM_UNIXTIME(create_time)=''2016-06-06''就不能使?索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进?检索时，需要把所有元素都应?函数才能?较，显然这样的代价太?。所以语句要写成：create_time=UNIX_TIMESTAMP(''2016-06-06'')。尽可能的扩展索引，不要新建?索引。?如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。在经常使用在WHERE子句，需要排序的列中的列上面创建索引，加快条件的判断速度。注意事项将某一列设置为defaultnull，where是可以走索引，另外索引列是否设置null是不影响性能的。但是，还是不建议列上允许为空。删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗MySQL5.7可以通过查询sys库的chema_unused_indexes视图来查询哪些索引从未被使用08基本架构基本架构一条查询sql执行过程一条更新sql执行过程0102连接器查询缓存(MySQL8.0版本后移除)基本架构030405分析器优化器执行器经过tcp握手，验证身份即可连接客户端此时处于空闲状态，showprocesslist，其中的Command列显示为Sleep的这??，就表示现在系统??有?个空闲连接。数据库的客户端太久没响应，连接器就会?动断开了，这个时间参数是wait_timeout控制住的，默认时?为8?时。使??连接。但是这?有个缺点，内存会飙得很快，我们知道MySQL在执?过程中临时使?的内存是管理在连接对象??的。只有在链接断开的时候才能得到释放，那如果?直使??连接，那就会导致OOM（OutOfMemory），会导致MySQL重启，在JVM??就会导致频繁的FullGC。解决方法1、定期断开长连接2、执行一个较大的查询后，执?mysql_reset_connection可以重新初始化连接资源连接器查询缓存(MySQL8.0版本后移除)缓存很容易失效
query_cache_type设置成为DEMAND，这样SQL默认不适?缓存分析器词法分析
语法分析优化器mysql认为的最优的执行方案去执行，确认索引，对执行顺序优化执行器09调优调优排除缓存干扰explain执行计划使用覆盖索引，联合索引索引下推唯一索引普通索引选择前缀索引调优0102条件字段函数操作flush调优explain执行计划字段含义字段含义1.id执行select子句或者操作表的顺序select_type查询的类型，区别于普通的查询、联合查询、子查询等primaryderived查询中包含任何复杂的子查询050103040206simplesubqueryunion简单的select查询，不包含子查询和union在select或where列表中包含了子查询字段含义table这一行的数据是关于哪张表的查询类型从最好到最差依次是:system>const>eq_ref>ref>range>index>All,一般情况下,得至少保证达到range级别,最好能达到refconst:表示通过索引一次就找到了字段含义typeeq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描ref:非唯一性索引扫描,返回匹配某个单独值的行,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体字段含义possible_keys显示可能应用在这张表中的索引字段含义key实际使用的索引,如果为null,则没有使用索引字段含义key_len表示索引中使用的字节数字段含义ref显示索引的哪一列被使用了字段含义rows估算出找到所需的记录所需要读取的行数字段含义重要的额外信息，特别注意损耗性能的两个情况，usingfilesort和usingtemporary。extraUsingfilesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为\"文件排序\"Usingtemporary:使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于orderby和分组查询groupby调优索引下推MySQL5.6引入的索引下推优化（indexconditionpushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。调优查询过程01更新过程02唯一索引普通索引选择业务场景03查询过程假设，执行查询的语句是selectidfromTwherek=5。先是通过B+树从树根开始，按层遍历搜索到叶子节点确定数据页数据页内部通过二分法来定位记录对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。基本没有性能差距InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。更新过程需要更新一个数据页时，在内存中则直接更新不在内存中，就将更新操作缓存在changbuffer中，下次查询需要访问数据页，把数据页读入内存时，再执行changebuffer操作减少磁盘IO01对于唯?索引来说，所有的更新操作都要先判断这个操作是否违反唯?性约束。要判断表中是否存在这个数据，?这必须要将数据?读?内存才能判断，如果都已经读?到内存了，那直接更新内存会更快，就没必要使?changebuffer了。因此，唯?索引的更新就不能使?changebuffer，实际上也只有普通索引可以使?。02业务场景?个业务的更新模式是写?之后?上会做查询不适合使用changebuffer01写多读少的业务，常?的就是账单类、?志类的系统02调优前缀索引字段长度比较长（邮箱）的字段，如何优化调优函数计算不走索引条件字段函数操作字符集不同可能不走索引字符集不同可能不走索引需要convert（）转换调优22flush·
如何把握刷flush的时机每个主机io能力不同，这就要?到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能?，这个值建议设置成磁盘的IOPS，磁盘的IOPS可以通过fio这个?具来测试。如何把握刷flush的时机10mvccmvcctodo感谢聆听 |
|
