为什么索引可以提高查询速度先从MySQL的基本存储结构说起。MySQL的基本存储结构是页（记录以页为单位存储）：每个数据页可以组成一个双向链表的每个数据页中的记录可以组成一个单向链表。1、每个数据页都会为其中存放的记录生成一个页目录。使用二分法快速定位到对应的slot，然后遍历slot对应组中的记录，快速找到指定的记录2.使用其他列（非主键）作为查找条件：只从最小的记录开始遍历链表中的单条记录。所以，如果我们写一条select*fromuserwhereindexname='xxx'这样的sql语句，不做任何优化，它默认会这样：定位到记录所在的页面：需要遍历双向链表找到pagewhereit从所在的页面中查找其中对应的记录：由于不是基于主键查询，所以只能遍历页面的单向链表。很明显，这种搜索在数据量很大的时候会很慢！这样的时间复杂度是O(n)。索引做了什么使我们的查询更快？其实就是把无序的数据（相对）改成有序的：找到id为8的记录，简单步骤：显然：不使用索引，我们需要遍历双向链表定位到对应的页面，现在通过“目录”可以快速导航到相应的页面！（二分查找，时间复杂度约为O(logn)）其实底层结构就是B+树。B+树作为树的一种实现，可以让我们快速的找到对应的记录。什么是最左前缀原则？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(主键索引)ALTERTABLEtable_nameADDPRIMARYKEY(column)2.添加UNIQUE(唯一索引)ALTERTABLEtable_nameADDUNIQUE(column)3.添加INDEX(普通索引)ALTERTABLEtable_nameADDINDEXindex_name(column)4.添加FULLTEXT(全文索引)ALTERTABLEtable_nameADDFULLTEXT(column)5.添加多列索引ALTERTABLEtable_nameADDINDEXindex_name(column1,column2,column3)存储引擎一些常用命令查看提供的所有存储引擎通过MySQLmysql>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-InnoDB-MVCC多版本并发控制...《MySQL高性能》上面有一句话是这样写的：不要轻易相信“MyISAM比InnoDB快”这样的经验，这个结论往往不是绝对的的。在许多已知场景中，InnoDB的速度可能是MyISAM无法企及的，尤其是在使用聚簇索引时，或者需要访问数据的应用程序可以放在内存中时。一般情况下，我们选择InnoDB是没有问题的，但是如果你不关心扩展性和并发性，事务支持，或者崩溃后的安全恢复，选择MyISAM也是一个不错的选择。但总的来说，我们都需要考虑这些问题。乐观锁和悲观锁的区别。悲观锁定总是假设最坏的情况。每次去拿数据的时候，都以为别人会修改，所以每次拿数据的时候，都会加锁，这样别人就拿不到数据了。阻塞直到获得锁（共享资源一次只被一个线程使用，其他线程阻塞，使用完再转移给其他线程）。传统关系型数据库中使用了很多这样的锁机制，比如行锁、表锁等，读锁、写锁等，都是在操作执行前加锁。Java中的synchronized、ReentrantLock等独占锁都是悲观锁思想的实现。乐观锁定总是假设最好的情况。每次去拿数据，你以为别人不会修改，所以不会上锁。但是在更新的时候，你会判断这段时间别人有没有更新过数据。你可以使用版本号机制和CAS算法来实现。乐观锁适用于多读应用类型，可以提高吞吐量。和数据库提供的write_condition机制一样，其实是提供了一种乐观锁。Java中java.util.concurrent.atomic包下的原子变量类是使用CAS实现的，一种乐观锁的实现方式。两种锁的使用场景从上面两种锁的介绍中，我们知道两种锁各有优缺点，不能认为哪一种就比哪种好。乐观锁适用于写操作比较少的情况（多看Scenarios），也就是真正很少发生冲突的时候，这样可以节省加锁的开销，提高系统的整体吞吐量。但是如果写的多了，往往会发生冲突，导致上层应用不断的重试，反而会降低性能。所以，悲观锁在写的比较多的场景下比较合适。乐观锁的两种常见实现方式乐观锁一般使用版本号机制或者CAS算法来实现。1、版本号机制一般是在数据表中增加一个数据版本号version字段，表示数据被修改的次数。当数据被修改时，版本值会加一。当线程A要更新数据值时，它在读取数据的同时也会读取版本值。提交更新时，如果刚刚读取的版本值等于当前数据库中的版本值，则更新，否则重试更新操作，直到更新成功。举个简单的例子：假设数据库中的账户信息表中有一个version字段，当前值为1；当前账户余额字段(balance)为100。运营商A现在读出（version=1）并从他的账户余额中扣除50（50（50（100-$50））。在运营商A的操作过程中，运营商B也读入了这条用户信息（version=1），并从账户余额中扣除20(20(100-$20)。操作员A完成修改，数据版本号加一(version=2)，连同扣除后的账户余额(balance=$50)，提交到数据库更新，此时由于提交的数据版本大于当前数据库记录的版本，更新数据，将数据库记录版本更新为2。操作员B完成了操作，同样将版本号加一（version=2）并尝试向数据库提交数据（balance=$80），但此时在对比数据库记录版本时发现，操作员B提交的数据版本号为2，数据库记录的当前版本也是2，不满足“提交版本必须大于当前记录版本才能执行更新”的乐观锁策略，因此操作员B的提交被拒绝。这样就避免了算子B用基于version=1的旧数据修改结果覆盖算子A的运算结果的可能性。2、CAS算法是compareandswap（比较和交换），这是一个著名的无锁算法。无锁编程，即在不使用锁的情况下实现多线程之间的变量同步，即实现变量同步而不阻塞线程，所以又称为非阻塞同步（Non-blockingSynchronization）。CAS算法涉及三个操作数待读写的内存值V待比较的值A待写入的新值B当且仅当V的值等于A时，CAS用新的值更新V的值以原子方式取值B，否则不执行任何操作（比较和替换是原子操作）。总的来说就是一个自旋操作，也就是不断重试。关于自旋锁，可以看看这篇文章，写的很好：《 面试必备之深入理解自旋锁》乐观锁缺点ABA问题是乐观锁的通病1.ABA问题如果一个变量V在第一次读取时的值为A，而当它准备赋值的时候查到还是A值，能说明它的值没有被其他线程修改过吗？显然不是，因为在这期间它的值可能会被改成其他值，然后再改回A，那么CAS操作就会误认为它从来没有被修改过。这个问题被称为CAS操作的“ABA”问题。JDK1.5之后的AtomicStampedReference类提供了这种能力。其中的compareAndSet方法是先检查当前引用是否等于期望引用，当前标志是否等于期望标志。该值设置为给定的更新值。2、循环时间长，开销大SpinCAS（即会一直循环执行，直到成功）。如果长时间失败，会给CPU带来非常大的执行开销。如果JVM能够支持处理器提供的暂停指令，效率会得到一定程度的提升。暂停指令有两个功能。首先，它可以延迟流水线执行指令（de-pipeline），使CPU不会消耗过多的执行资源。延迟时间取决于具体的实现版本，在某些处理器上延迟时间为零。其次，可以避免退出循环时由于内存顺序违背导致CPU流水线被清空（CPUpipelineflush），从而提高CPU的执行效率。3.共享变量的原子操作只能保证。CAS只对单个共享变量有效，当运算涉及多个共享变量时CAS无效。但是从JDK1.5开始，提供了AtomicReference类来保证被引用对象之间的原子性。您可以将多个变量放在一个对象中进行CAS操作。所以我们可以使用锁或者使用AtomicReference类，把多个共享变量组合成一个共享变量来操作。锁机制和InnoDB锁算法MyISAM和InnoDB存储引擎使用的锁：MyISAM使用表级锁。InnoDB支持行级锁（row-levellocking）和表级锁。默认是行级锁。表级锁和行级锁的比较：表级锁：Mysql中锁定粒度最大的一种锁。表锁实现简单，占用资源少，加锁速度快，不会出现死锁。它的锁定粒度最大，触发锁冲突的概率最高，并发度最低。MyISAM和InnoDB引擎都支持表级锁。行级锁：Mysql中粒度最小的锁，只对当前操作的行加锁。行级锁可以大大减少数据库操作中的冲突。它的加锁粒度最小，并发度高，但是加锁的开销也最大，加锁慢，可能会出现死锁。详细可以参考：Mysql的锁机制简单看下InnoDB存储引擎的锁算法有三种：记录锁：对单行记录加锁间隙锁：间隙锁，锁定一个范围，排除记录本身下一篇-keylock：record+gap锁定一个范围，包括record本身相关的知识点：innodb使用next-keylock进行行查询Next-lockingkeying解决幻读问题幻读问题当查询索引包含唯一属性时，降级next-keylocktorecordkeyGap锁设计的目的是防止多个事务向同一个范围插入记录，从而导致幻读问题。显式关闭间隙锁有两种方式：（除了外键约束和唯一性检查，其余只使用记录锁）A.设置事务隔离级别为RCB.设置参数innodb_locks_unsafe_for_binlog为1.大表优化时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等都是这个架构的实现。
