，什么是MySQL？MySQL是一种关系型数据库，在Java企业级开发中非常常用，因为MySQL开源、免费、易于扩展。阿里巴巴数据库系统也大量使用MySQL，稳定性有保障。MySQL是开源的，所以任何人都可以在GPL（GeneralPublicLicense）许可下下载，并根据个人需要进行修改。MySQL默认的端口号是3306。1.事务相关什么是事务？事务是一组逻辑操作，可以执行也可以不执行。最经典的交易经常被引用为转账的例子。如果小明想转1000元给小红，这笔转账会涉及两个关键操作：减少小明余额1000元，增加小红余额1000元。万一这两个操作突然出现错误，比如银行系统崩溃，导致小明的余额减少，小红的余额没有增加，这是错误的。事务是为了确保这两个关键操作要么成功要么失败。让我介绍一下事物的四大特性（ACID）？原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作全部完成或完全没有效果；一致性：事务执行前后，数据保持一致，多个事务读取相同数据的结果相同；隔离性：并发访问数据库时，一个用户的事务不受其他事务的干扰，数据库在并发事务之间是独立的；持久性：事务提交后。它对数据库中数据的改变是持久化的，即使数据库出现故障也不应该对其产生任何影响。并发事务带来的问题有哪些？在一个典型的应用程序中，多个事务并发运行，往往对同一个数据进行操作以完成各自的任务（多个用户对同一个数据进行操作）。并发虽然是必要的，但是可能会导致以下问题：脏读：当一个事务正在访问数据和修改数据，而修改还没有提交到数据库，另一个事务也访问并使用了这段数据。因为这个数据是未提交的数据，另一个事务读取的数据是“脏数据”，基于“脏数据”的操作可能是错误的。丢失修改：当一个事务读取一条数据时，另一个事务也访问了该数据，那么在第一个事务修改了数据之后，第二个事务也修改了数据。这样第一笔交易中的修改结果就丢失了，所以称为丢失修改。例如：事务1读取了一张表中的数据A=20，事务2也读取了A=20，事务1修改了A=A-1，事务2也修改了A=A-1，最终结果为A=19，事务1修改丢失。不可重复读（Unrepeatableread）：指在一个事务内多次读取同一个数据。当这个事务没有结束时，另一个事务也访问了数据。那么，在第一个事务中的两次读取数据之间，由于第二个事务的修改，第一个事务两次读取的数据可能不同。这种情况恰好是一个事务中两次读取的数据是不同的，所以称为不可重复读。幻读：幻读类似于不可重复读。当一个事务(T1)读取几行数据，然后另一个并发事务(T2)插入一些数据时，就会发生这种情况。在后续的查询中，第一个事务（T1）会多出一些不存在的记录，好像出现了幻觉，所以称为幻读。不可重复读和幻读的区别：不可重复读的重点是修改，幻读的重点是增删改查。例1（同样的条件，你读过的数据，再读一遍发现值不一样）：事务1中的A先生还没有完成读取自己1000工资的操作，而事务中的B先生2修改A的工资为2000，导致A重新读取自己的工资时，工资变为2000；这是不可重复的阅读。示例2（相同条件，第一次和第二次读出的记录条数不同）：工资单表中有4个人工资大于3000，事务1读取所有工资为大于3000，共找到4条记录。这时事务2又插入了一条工资大于3000的记录，事务1再次读取时，发现的记录变成了5条，导致幻读。什么是事务隔离级别？MySQL的默认隔离级别是多少？SQL标准定义了四种隔离级别：READ-UNCOMMITTED（读未提交）：最低的隔离级别，允许读取未提交的数据变化，这可能导致脏读、幻读或不可重复读。READ-COMMITTED（已提交读）：允许读取已被并发事务提交的数据，可以防止脏读，但仍然可能出现幻读或不可重复读。REPEATABLE-READ（可重复读）：同一个字段的多次读取结果是一致的，除非数据被事务本身修改，可以防止脏读和不可重复读，但幻读还是有可能发生。SERIALIZABLE（可序列化）：最高隔离级别，完全服从ACID隔离级别。所有的事务都是一个一个执行的，这样事务之间就不存在相互干扰的可能，即这个级别可以防止脏读、不可重复读和幻读。隔离级别脏读不可重复读幻读READ-UNCOMMITTED√√√READ-COMMITTED×√√REPEATABLE-READ××√SERIALIZABLE×××MySQLInnoDB存储引擎默认支持的隔离级别为REPEATABLE-READ（可重读）.我们可以使用SELECT@@tx_isolation;命令查看mysql>SELECT@@tx_isolation;+----------------+|@@tx_isolation|+------------------+|REPEATABLE-READ|+----------------+这里需要注意的是，与SQL标准的区别在于InnoDB存储引擎使用了Next-REPEATABLE-READ（可重读）事务隔离级别下的KeyLock锁算法，因此可以避免幻读的产生，这是有别于其他数据库系统（如SQLServer）的。因此，InnoDB存储引擎默认支持的隔离级别是REPEATABLE-READ（可重读），可以充分保证事务的隔离要求，即达到了SQL标准的SERIALIZABLE（可序列化）隔离级别。因为隔离级别越低，事务请求的锁就越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED（读取提交的内容）：，但是需要知道的是InnoDB存储引擎使用的是REPEATABLE-READ（可以阅读）默认。重读）没有任何性能损失。InnoDB存储引擎一般在分布式事务的情况下使用SERIALIZABLE（可序列化）隔离级别。2.索引相关为什么索引可以提高查询速度以下内容编译自：《数据库两大神器【索引和锁】》作者：Java3y先从MySQL的基本存储结构说起。MySQL的基本存储结构是页（记录以页为单位存储）：每个数据页可以组成一个双向链表的每个数据页中的记录，也可以组成一个单向链表——每个数据页都会生成一个页directory为其中存放的记录，通过主键查找记录时，可以在页目录中使用二分法快速定位到对应的slot，然后遍历slot对应组中的记录即可快速查找指定记录——使用其他列（非主键）作为查找条件：只从最小的记录开始，按每条记录的顺序遍历单向链表。所以，如果我们写select*fromuserwhereindexname='xxx'而没有任何优化的sql语句，它会默认这样做：定位到记录所在的页面：需要遍历双向链表找到来自在页面中查找对应的记录：由于不是基于主键查询，所以只能遍历页面的单向链表。很明显，这种搜索在数据量很大的情况下会很慢！这个的时间复杂度是O(n)。索引对加快我们的查询有什么作用呢？其实就是把无序的数据（相对）改成有序的：找到id为8的记录，简单的步骤：很明显：不用索引，我们需要遍历双向链表定位到对应的页面，现在您可以通过“目录”快速定位到对应的页面了！（二分查找，时间复杂度约为O(logn)）其实底层结构是B+树，B+树作为树的一种实现，可以让我们快速的找到对应的记录。需要Java资料，转发文章+关注私信【Java】什么是最左前缀原则？MySQL中的索引可以按一定顺序引用多个列。这种索引称为联合索引。例如User表的name和city加上联合索引为(name,city)，最左前缀原则是指查询时如果查询条件正好匹配索引左边的一个或几个连续的列，则这个栏目可以用到。如下：select*fromuserwherename=xxandcity=xx;//索引可以命中select*fromuserwherename=xx;//索引可以命中select*fromuserwherecity=xx;//索引不能命中这里要注意即在查询的时候，如果两个条件都用到了，只是顺序不同，比如city=xx，name=xx，那么当前的查询引擎会自动优化去匹配联合索引的顺序，这样索引就可以被击中。由于前缀最左的原则，在创建联合索引时，索引字段的顺序需要考虑去重后字段值的个数，多的放在前面。ORDERBY子句也遵循此规则。注意避免冗余索引冗余索引指的是索引的功能是一样的，能命中就一定命中，那么就是冗余索引比如(name,city)和(name)这两个indexes是冗余索引，能命中前者的查询一定能命中前者。在大多数情况下，您应该尝试扩展现有索引而不是创建新索引。MySQLS.7版本之后，可以通过查询sys库的schema_redundant_indexes表来查看冗余索引。Mysql如何为表字段添加索引？1.添加PRIMARYKEY（主键索引）ALTERTABLE`table_name`ADDPRIMARYKEY(`column`)2.添加UNIQUE（唯一索引）ALTERTABLE`table_name`ADDUNIQUE(`column`)3.添加INDEX（普通索引）ALTERTABLE`table_name`ADDINDEXindex_name(`column`)4.添加FULLTEXT(全文索引)ALTERTABLE`table_name`ADDFULLTEXT(`column`)5.添加多列索引ALTERTABLE`table_name`ADDINDEXindex_name(`column1`,`column2`,`column3`)3.存储引擎的一些常用命令查看MySQL提供的所有存储引擎mysql>showengines;从上图可以看出MySQL目前默认的存储引擎是InnoDB，而在5.7版本的所有存储引擎中，只有InnoDB是事务型存储引擎，也就是说只有InnoDB支持事务。查看MySQL当前默认的存储引擎我们也可以通过以下命令查看默认的存储引擎。mysql>showvariableslike'%storage_engine%';查看表的存储引擎showtablestatuslike"table_name";MyISAM和InnoDB的区别MyISAM是MySQL的默认数据库引擎（5.5之前的版本）。虽然性能优异，提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，最大的缺陷是无法安全恢复崩溃。但是MySQL在5.5版本之后引入了InnoDB（事务数据库引擎），MySQL在5.5版本之后默认的存储引擎就是InnoDB。大多数时候我们使用InnoDB存储引擎，但在某些情况下使用MyISAM也是合适的，比如读取密集的情况。（如果你不介意MyISAM崩溃回复问题）。两者比较：是否支持行级锁：MyISAM只有表级锁，而InnoDB支持行级锁和表级锁，默认是行级锁。是否支持事务和崩溃后的安全恢复：MyISAM强调性能，每个查询都是原子的，执行速度比InnoDB快，但不提供事务支持。但InnoDB提供了事务支持事务、外键等高级数据库功能。具有提交、回滚和崩溃恢复功能的事务安全（符合ACID）表。是否支持外键：MyISAM不支持，InnoDB支持。是否支持MVCC：仅InnoDB支持。针对高并发事务，MVCC比简单的加锁更高效；MVCC只工作在READCOMMITTED和REPEATABLEREAD这两个隔离级别下；MVCC可以使用乐观（optimistic）锁和悲观（pessimistic）锁来实现；MVCC在各个数据库中的实现并不统一。......《MySQL高性能》上面有一句话是这样写的：不要轻易相信“MyISAM比InnoDB快”这样的经验，这个结论往往不是绝对的。在许多已知场景中，InnoDB的速度可能是MyISAM无法企及的，尤其是在使用聚簇索引时，或者需要访问数据的应用程序可以放在内存中时。一般情况下，我们选择InnoDB是没有问题的，但是如果你不关心扩展性和并发性，事务支持，或者崩溃后的安全恢复，选择MyISAM也是一个不错的选择。但总的来说，我们都需要考虑这些问题。四、乐观锁和悲观锁的区别1、悲观锁总是假设最坏的情况。每次去拿数据的时候都以为别人会修改，所以每次拿数据的时候都会加锁，这样别人想拿的数据就会阻塞，直到拿到锁（共享资源是一次只被一个线程使用，其他线程阻塞，使用完再转移给其他线程）。传统关系型数据库中使用了很多这样的锁机制，比如行锁、表锁等，读锁、写锁等，都是在操作执行前加锁。Java中的synchronized、ReentrantLock等独占锁都是悲观锁思想的实现。2.乐观锁总是假设最好的情况。每次去拿数据的时候，你以为别人不会修改，所以不会加锁，但是更新的时候，你会判断这段时间别人有没有更新过数据，可以使用版本号机制和CAS算法来实现。乐观锁适用于多读应用类型，可以提高吞吐量。和数据库提供的write_condition机制一样，其实是提供了一种乐观锁。Java中java.util.concurrent.atomic包下的原子变量类是使用CAS实现的，一种乐观锁的实现方式。3、两种锁的使用场景从上面两种锁的介绍中，我们知道两种锁各有优缺点，不能认为哪一种就比哪种好。乐观锁适用于写较少的情况（多读的场景），也就是真正很少发生冲突的时候，这样可以节省加锁的开销，提高系统的整体吞吐量。但是如果写的多了，往往会发生冲突，导致上层应用不断的重试，反而会降低性能。所以，悲观锁在写的比较多的场景下比较合适。4、乐观锁的两种常见实现方式乐观锁一般采用版本号机制或CAS算法实现。4.1.版本号机制一般是在数据表中增加一个数据版本号version字段，表示数据被修改的次数。当数据被修改时，版本值会加一。当线程A要更新数据值时，它在读取数据的同时也会读取版本值。提交更新时，如果刚刚读取的版本值等于当前数据库中的版本值，则更新，否则重试更新操作，直到更新成功。举个简单的例子：假设数据库中的账户信息表中有一个version字段，当前值为1；当前账户余额字段(balance)为100。操作员A现在读取它(version=1)并从他的账户余额($100-$50)中扣除$50。在运营商A的操作过程中，运营商B也读入了这条用户信息（version=1），并从他的账户余额（$100-$20）中扣除$20。操作员A完成修改工作，将数据版本号（version=2）加一，连同扣除后的账户余额（balance=$50），提交到数据库更新。此时由于提交的数据版本大于当前数据库记录的版本，删除数据更新，数据库记录版本更新为2。操作员B完成了操作，同时版本号也增加了1（version=2)，并尝试向数据库提交数据（balance=$80），但是此时在对比数据库记录版本时，发现操作员B提交的数据的版本号为2，即当前版本数据库记录的of也是2，不满足“commit版本必须大于当前记录版本才能执行更新”的乐观锁策略，所以operatorB的commit被拒绝。这样就避免了算子B用基于version=1的旧数据修改结果覆盖算子A的运算结果的可能性。4.2.CAS算法就是compareandswap（比较和交换），这是一种著名的无锁算法。无锁编程，即在不使用锁的情况下实现多线程之间的变量同步，即实现变量同步而不阻塞线程，所以又称为非阻塞同步（Non-blockingSynchronization）。CAS算法涉及三个操作数：待读写的内存值V、待比较的值A、待写入的新值B。当且仅当V的值等于A时，CAS以原子方式用新值B更新V。值，否则不执行任何操作（比较和替换是原子操作）。总的来说就是一个自旋操作，也就是不断重试。5.乐观锁的缺点ABA问题是乐观锁的通病5.1ABA问题如果一个变量V在第一次被读取的时候是A值，准备赋值的时候还是A值，那么我们可以解释一下它的值是不是被其他线程修改了？显然不是，因为在这期间它的值可能会被改成其他值，然后再改回A，那么CAS操作就会误认为它从来没有被修改过。这个问题被称为CAS操作的“ABA”问题。JDK1.5之后的AtomicStampedReference类提供了这种能力。其中的compareAndSet方法是先检查当前引用是否等于期望引用，当前标志是否等于期望标志。该值设置为给定的更新值。5.2循环时间长，开销大，自旋CAS高（即不成功则循环执行，直到成功）。如果长时间失败，会给CPU带来非常大的执行开销。如果JVM能够支持处理器提供的暂停指令，效率会得到一定程度的提升。暂停指令有两个功能。首先，它可以延迟流水线执行指令（de-pipeline），使CPU不会消耗过多的执行资源。延迟时间取决于具体的实现版本，在某些处理器上延迟时间为零。其次，它可以防止在退出循环时由于内存顺序违背导致CPU流水线被清空（CPUpipelineflush），从而提高CPU的执行效率。5.3共享变量的原子操作只能保证。CAS只对单个共享变量有效，当运算涉及多个共享变量时CAS无效。但是从JDK1.5开始，提供了AtomicReference类来保证被引用对象之间的原子性。您可以将多个变量放在一个对象中进行CAS操作。所以我们可以使用锁或者使用AtomicReference类，把多个共享变量组合成一个共享变量来操作。五、锁机制和InnoDB锁算法MyISAM和InnoDB存储引擎使用的锁：MyISAM使用表级锁。InnoDB支持行级锁（row-levellocking）和表级锁。默认是行级锁。表级锁和行级锁的比较：表级锁：Mysql中锁定粒度最大的一种锁。表锁实现简单，占用资源少，加锁速度快，不会出现死锁。它的锁定粒度最大，触发锁冲突的概率最高，并发度最低。MyISAM和InnoDB引擎都支持表级锁。行级锁：Mysql中粒度最小的锁，只对当前操作的行加锁。行级锁可以大大减少数据库操作中的冲突。它的加锁粒度最小，并发度高，但是加锁的开销也最大，加锁慢，可能会出现死锁。InnoDB存储引擎一共有三种锁算法：Recordlock：对单行记录加锁Gaplock：gaplock，锁定一个范围，不包括记录本身Next-keylock：record+gaplockanrange，包括关于记录的知识recorditself要点：InnoDB使用next-keylock进行行查询。Next-lockingkeying用于解决PhantomProblem。当查询索引包含唯一属性时，下一个键锁降级为记录键间隙锁。设计目的是为了防止多个A事务向同一个范围内插入记录，从而导致幻读问题。显式关闭间隙锁有两种方式：（除了外键约束和唯一性检查，其他情况只使用记录锁）A.将事务隔离级别设置为RCB.将参数innodb_locks_unsafe_for_binlog设置为1。6.大表优化当MySQL单表记录数过多时，数据库的CRUD性能会明显下降。一些常见的优化措施如下：1.限制数据范围一定要禁止无条件限制数据范围的查询语句。比如：当用户在查询历史订单时，我们可以控制在一个月以内；2.读写分离经典数据库拆分方案，主库负责写，从库负责读；3.垂直分区是根据数据库中数据表的依赖关系进行拆分。例如，如果用户表既包含用户的登录信息，又包含用户的基本信息，则可以将用户表拆分成两个单独的表，甚至可以作为分库放在单独的库中。简单的说，垂直拆分就是对数据表的列进行拆分，将一个列数较多的表拆分成多个表。如下图所示，大家应该更容易理解。垂直拆分的优点：可以使列数据变小，减少查询时读取的块数，减少I/O次数。另外，垂直分区可以简化表的结构，便于维护。垂直拆分的缺点：主键会冗余，冗余的列需要管理，会造成Join操作，可以在应用层进行Join来解决。此外，垂直分区会使交易变得更加复杂；4、水平分区保持数据表的结构不变，通过一定的策略存储数据分片。这样每条数据就分散到不同的表或库中，达到了分布的目的。水平拆分可以支持非常大的数据量。水平拆分是指数据表行的拆分。当一个表的行数超过200万时，速度就会变慢。这时可以将一张表中的数据拆分成多张表存储。例如：我们可以将用户信息表拆分成多个用户信息表，这样可以避免单表数据量大而带来的性能影响。水平拆分可以支持非常大的数据量。需要注意的一点是，分表只是解决了单表数据过大的问题，但是由于表中的数据还在同一台机器上，对于提高MySQL的并发性意义不大，所以最好水平拆分数据库。水平拆分可以支持超大数据量的存储，应用端修改很少，但分片事务难以解决，跨节点Join性能差，逻辑复杂。《Java工程师修炼之道》的作者建议尽量不要对数据进行分片，因为分片会带来逻辑、部署、运维等方面的各种复杂性。一般的数据表在适当优化的情况下，支持的数据量在千万以内。完全没有问题。如果真的要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。下面补充两种常见的分库方案：客户端代理：分库逻辑在应用端，封装在jar包中，通过修改或封装JDBC层实现。当当网的Sharding-JDBC和阿里的TDDL是两种常用的实现方式。中间件代理：在应用程序和数据之间增加了一个代理层。分片逻辑统一维护在中间件服务中。我们说的Mycat，360的Atlas，网易的DDB等都是这个架构的实现。
