mysql优化1.MYSQL优化主要分为以下四个方面：设计：存储引擎、字段类型、范式和反范式功能：索引、缓存、分区和表。架构：主从复制、读写分离、负载均衡。RationalSQL：测试，经验。优先级是表结构，选择合适的字段，索引优化，结合Redis缓存，主从分离，（分区、分表、分库只有在没办法的时候才用）保存的数据格式是什么通过mysql？安装mysql时选择的存储引擎是MYISAM，数据存储在.MYD文件中；如果选择了innodb存储引擎，则数据统一存储在一个名为ibdata1的文件中。mysql是如何把它编译成我们可以识别的数据格式的呢？？mysql的索引在内存中是以什么格式存储的？B+树是什么树？为什么？什么是交易？锁、行锁、表锁、间隙锁、幻读、脏读、重复读？MySQL的锁可以分为：乐观锁和悲观锁。按照粒度可以分为表级锁、行级锁、页级锁。表锁从锁的粒度上，我们可以分为两类：表锁：开销小，加锁速度快；没有死锁；锁强，锁冲突概率高，并发度最低。行锁：开销大，加锁慢；可能会出现死锁；锁粒度小，锁冲突概率低，并发度高不同的存储引擎支持不同的锁粒度。*InnoDB行锁和表锁都支持，MyISAM只支持表锁！*InnoDB仅在通过索引条件检索数据时使用行级锁。否则，InnoDB使用表锁。也就是说，InnoDB的行锁是基于索引的！表锁分为两种模式：TableReadLock&&TableWriteLock从下图可以清楚的看出，在表读锁和表写锁的环境下：读写锁阻塞，读写阻塞，写和写作阻塞！读读不阻塞：当前用户正在读数据，其他用户也在读数据，不会被锁定。读写阻塞：当前用户正在读取数据，其他用户无法修改当前用户读取的数据，会被锁定！写写阻塞：当前用户正在修改数据，其他用户无法修改当前用户正在修改的数据，会被锁定！由上可见：读锁和写锁是互斥的，读写操作是串行的。如果一个进程想要获取一个读锁，另一个进程想要获取一个写锁。在mysql中，写锁优先于读锁！写锁和读锁的优先级可以通过参数调整：max_write_lock_count和low-priority-updates行锁InnoDB和MyISAM有两个本质区别：InnoDB支持行锁，InnoDB支持事务。InnoDB实现了以下两种类型的行锁：共享锁（S锁，读锁）：允许一个事务读取一行，防止其他事务获取同一数据集上的排它锁。即多个客户端可以同时读取同一个资源，但不允许其他客户端对其进行修改。独占锁（X锁、写锁）：允许获取独占锁的事务更新数据，防止其他事务获取同一数据集的读写锁。写锁是排他的，写锁会阻塞其他的写锁和读锁。另外，为了让行锁和表锁共存，实现多粒度的锁机制，InnoDB内部还有两种意向锁（IntentionLocks），都是表锁：IntentionSharedLock（IS):Transaction意在给数据行加行共享锁，事务在给数据行加共享锁之前必须先获取表的IS锁。意向排他锁（IX）：事务打算给数据行加排他锁，事务在给数据行加排他锁之前必须先获得表的IX锁。意向锁也是数据库隐式帮我们做的，程序员不用关心！死锁1.成因所谓死锁：是指两个或多个进程在执行过程中由于争夺资源而相互等待的现象。如果没有外力，他们将无法推进。这时候就说系统处于死锁状态或者系统已经发生了死锁。这些始终相互等待的进程称为死锁进程。表级锁不会造成死锁。所以死锁的解决方法主要针对最常用的InnoDB。死锁的关键是两个（或多个）Session加锁的顺序不一致。那么对应的解决死锁问题的关键就是：让不同的session依次加锁2.生成实例需求：将投资资金分成几份，随机分配给借款人。起初，业务程序的思路是这样的：投资人投资后，金额随机分成几份，然后从借款人表中随机抽取几份，然后借款人表中的余额为通过selectforupdate一一更新。例如：两个用户同时投资，用户A的金额随机分成2份分配给借款人1，用户2B的金额随机分成2份分配给借款人2和1。由于不同的加锁顺序，死锁当然很快就上来了。这个问题的改进很简单，一次性锁定所有分配的借款人即可。select*fromxxxwhereidin(xx,xx,xx)forupdatemysql中的列表值会自动从小到大排序，锁从小到大一个一个加。MVCC行级锁MVCC（Multi-VersionConcurrencyControl）多版本并发控制可以简单的认为：MVCC是行级锁的变种（升级版）。在表锁中，我们的读写都被阻塞了。基于提高并发性能的考虑，MVCC一般不会阻塞读写（很多情况下会避免加锁操作）。可以简单理解为：对数据库的任何修改的提交都不会直接覆盖之前的数据，而是会生成一个与旧版本共存的新版本，这样就可以完全不用加锁读取了。悲观锁如果我们使用悲观锁，其实很简单（手动加行锁即可）：select*fromxxxxforupdate，在select语句后加forupdate相当于加了排他锁（写锁），加了writelock以后其他事务不能修改！需要等待当前事务被修改后才能修改。也就是说，如果操作1使用select...forupdate，操作2不能修改记录，可以避免更新丢失。乐观锁乐观锁不是数据库级别的锁，需要用户手动添加。一般我们在数据库表中添加一个version字段version来实现。例如操作1和操作2更新User表时，执行语句如下：updateAsetName=lisi,version=version+1whereID=#{id}andversion=#{version},atthis时间，可以避免更新丢失。交易并发？事务隔离级别，每个级别会导致什么问题，MySQL默认是哪个级别？脏读是指在一个事务的处理过程中读取了另一个事务未提交的数据。不可重复读：对于数据库中的某条数据，在一个事务范围内多次查询返回不同的数据值。一个事务可能会插入一条新的数据记录，这会影响事务的读结果-----（幻读：读取其他事务新插入的数据）MySQL默认的隔离级别是Repeatableread，可重复读。理论上，事务之间应该完全隔离，避免并发事务带来的问题。但是，这会对性能产生很大影响，因为事务必须按顺序运行。在实际开发中，为了提高性能，事务会运行在较低的隔离级别，可以通过IsolateTransaction属性指定。事务并发问题事务并发问题1.脏读：事务A读取事务B更新的数据，然后B回滚操作，那么A读取的数据就是脏数据2.不可重复读：事务A多次读取同样的数据，事务B在事务A的多次读取过程中更新并提交了数据，导致事务A多次读取同一个数据，结果就是这个事务两次读取的数据结果会不一致。3.幻读：可重复读的隔离级别解决了不可重复读的问题，保证在同一个事务中，查询的结果是事务开始时的状态（一致性）。总结：不可重复读和幻读很容易混淆。不可重复读重在修改，幻读重在增删改查。解决不可重复读只需要对满足条件的行进行加锁，解决幻读需要对表进行加锁。事务隔离级别*读未提交：另一个事务修改了数据，但是还没有提交，本次事务中的SELECT会读取这些未提交的数据脏读*不可重复读：事务A多次读取同一个数据，事务B更新并且在事务A的多次读取中提交数据，导致事务A多次读取同一个数据，导致本事务两次读取数据的结果不一致。*可重复读：在同一个事务中，SELECT的结果是事务开始时间点的状态，所以同一个SELECT操作读取的结果会是一致的。但是会出现幻读*序列化：最高的隔离级别，在这个隔离级别下，不会产生异常。并发事务，就像事务是一个一个顺序执行的。未提交读（Readuncommitted），表示一个事务可以看到其他事务未提交的修改，从而导致脏读发生。读提交（Readcommitted），一个事务能看到的数据是其他事务已经提交的修改，并且保证看不到中间状态，当然也不会出现脏读。可重复读（Repeatablereads），保证在同一个事务中多次读取的数据是一致的，这是MySQLInnoDB引擎默认的隔离级别，可序列化（Serializable），并发事务之间的序列化，通常意味着读取需要获取共享读锁，更新需要获取独占写锁。SQL如果使用WHERE语句，也会获取一个区间锁（MySQL以GAP锁的形式实现，在可重复读级别也是默认使用的）。这是最高的隔离级别。MySQL默认的事务隔离级别是repeatable——阅读MySQL的复制原理和进程基本原理流程，3个线程以及它们之间的关系；master：binlog线程——记录所有改变数据库数据的语句，并放入master上的binlog中；from:iothread——使用startslave后，负责从master拉取binlog内容，放入自己的relaylog；from：sql执行线程——执行relaylog中的语句；MySQL数据库的CPU飙升到500%他怎么处理？1.列出所有进程showprocesslist，观察所有进程，如果几秒没有状态变化（kill）2.查看超时日志或错误日志（经过几年的发展，通常查询和大规模插入会导致cpu而I/o上升，当然也不排除网络状态突然断掉，服务器只收到一半的请求，比如where子句或者paging子句没有发送，当然是坑经验）sql优化的各种方法（一），各条的含义自解释；select_type：表示查询中每个select子句的类型type：表示MySQL在表中查找所需行的方式，也称为“访问类型”possible_keys：表示MySQL可以使用哪个键索引在表中查找行。如果查询涉及的字段上有索引，则列出该索引，但不一定被查询键使用：显示MySQL在查询中实际使用的索引。如果没有使用索引，则显示为NULLkey_len：表示索引使用的字节数，可以通过该列计算查询使用的索引长度ref表示以上表的连接匹配条件，即使用哪些列或常量来查找索引列上的值Extra包含不适当的其他列中显示但非常重要的附加信息（2），配置文件的含义和使用场景；查询执行SQL需要多少时间，查看CPU/内存使用情况，执行过程中Systemlock和Tablelock花费了多少时间等等。你如何监控你的数据库？你如何检查你的慢日志？监控工具有很多，比如zabbix，lepus，我这里用的是lepus，请问你们做过主从一致性检查吗，有的话怎么做，没有的话打算怎么办？主从一致性校验的工具有很多，比如checksum、mysqldiff、pt-table-checksum等。你的数据库支持emoji表情吗，不支持怎么操作？如果是utf8字符集，需要升级成utf8_mb4支持开放题：据说腾讯有一个表a6亿，表b3亿。通过外部的tid关联，如何查询满足最快查询的条件呢？第50000条到第50200条的200条数据记录1.如果A表的TID是自增连续的，那么B表的ID就是索引select*froma,bwherea.tid=b.idanda.tid>500000限额200；2、如果A表的TID不连续，那么就需要使用覆盖索引。TID要么是主键，要么是二级索引，B表的ID也需要有索引。select*fromb,(selecttidfromalimit50000,200)awhereb.id=a.tid;什么是数据库中的乐观锁和悲观锁？数据库管理系统（DBMS）中并发控制的任务是在多个事务同时访问数据库中的同一数据时，保证事务的隔离性和统一性以及数据库的统一性不被破坏。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制中使用的主要技术手段。悲观锁：假设会发生并发冲突，屏蔽所有可能违反数据完整性的操作乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性如何概括地理解三种范式？第一范式：1NF是对属性的原子约束，要求属性是原子的，不能分解；第二范式：2NF是对记录的唯一约束，要求记录具有唯一标识，即实体的唯一性；Paradigm：3NF是对字段冗余的约束，即任何字段都不能从其他字段派生，要求字段不冗余。.范式设计的优缺点：优点：可以尽可能减少数据冗余，使更新速度快，体积小。缺点：对于查询，需要关联多张表，降低写入效率，提高读取效率，索引优化反规范化难度更大：优点：可以减少表的关联，可以更好的优化索引。缺点：数据冗余和数据异常，数据修改需要更多的成本。MySQLbinlog有几种输入格式？有什么不同？有statement、row和mixed三种格式。在语句模式下，每条修改数据的SQL语句都会记录在binlog中。无需记录每一行的变化，减少了binlog日志量，节省了IO，提高了性能。由于sql的执行是有上下文的，所以在保存的时候需要保存相关信息，而一些使用函数的语句是无法记录和复制的。在行级别，不记录sql语句上下文的相关信息，只保存修改了哪条记录。记录单位是每一行的变化，基本可以记录下来。但由于操作较多，会改变大量的行（如altertable），所以这种文件方式保存的信息过多，日志量过大。混合，折衷方案。普通操作使用语句记录，不能使用语句时使用行。另外，新版MySQL中对行级别做了一些优化。当表结构发生变化时，语句将被记录，而不是逐行记录。参考：https://mp.weixin.qq.com/s/哈...
