MySQL基础知识点总结

1.COUNT(1)/(*)/(某一列)

count(*) 和 count(1)和count(列名)区别

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

• count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

• 列名为主键，count(列名)会比count(1)快 。

• 列名不为主键，count(1)会比count(列名)快。

• 如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*） 。

• 如果有主键，则 select count（主键）的执行效率是最优的。

• 如果表只有一个字段，则 select count（*）最优。

count(1)与count(*)比较：

• 如果你的数据表没有主键，那么count(1)比count(*)快

• 如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快

• 如果你的表只有一个字段的话那count(*)就是最快的啦

• count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。

• 如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。

• 因为count(),自动会优化指定到那一个字段。所以没必要去count(?)，用count(),sql会帮你完成优化的。

面试官提问：

1. count(1) 与 count(*)以及count（字段）有什么区别？ 详见上面所述；

2. 在INNODB与MYISAM中统计当前数据行，用count(*）有什么区别？

   MyISAM对于表的行数做了优化，具体做法是有一个变量存储了表的行数，如果查询条件没有WHERE条件则是查询表中一共有多少条数据，MyISAM可以做到迅速返回，前提条件是没有where语句的哦， InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。那么为什么InnoDB没有了这个变量呢？因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。 InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在，InnoDB还会尝试去遍历其他聚簇索引。 如果索引并没有完全处于InnoDB维护的缓冲区（Buffer Pool）中，count操作会比较费时。

3. MySQL查询一定大范围的数据和在Redis中查询一定大范围的数据量，那个更快一点？ redis的查询效率比Mysql查询效率要快；因为Redis的数据是保存在内存中，我们可以直接去内存中读取数据，这样的效率更快一点，而MySql数据是保存在磁盘中，每次查询数据我们都要去磁盘进行IO读取，大大增加了查询时间，同时还会涉及到回表的问题，影响查询效率。 Redis存储的是k-v格式的数据。时间复杂度是O(1),常数阶，而MySQL引擎的底层实现是B+Tree，时间复杂度是O(logn)，对数阶。Redis会比MySQL快一点点。 Redis是单线程的多路复用IO，单线程避免了线程切换的开销，而多路复用IO避免了IO等待的开销，在多核处理器下提高处理器的使用效率可以对数据进行分区，然后每个处理器处理不同的数据。

总结：

• 如果在开发中确实需要用到count()聚合，那么优先考虑count()，因为mysql数据库本身对于count()做了特别的优化处理。

• 有主键或联合主键的情况下，count(*)略比count(1)快一些。

• 没有主键的情况下count(1)比count(*)快一些。

• 如果表只有一个字段，则count(*)是最快的。

• 使用count()聚合函数后，最好不要跟where age = 1；这样的条件，会导致不走索引，降低查询效率。除非该字段已经建立了索引。使用count()聚合函数后，若有where条件，且where条件的字段未建立索引，则查询不会走索引，直接扫描了全表。

• count(字段),非主键字段，这样的使用方式最好不要出现。因为它不会走索引.

2.内、左、右连接的区别

1. 内连接,显示两个表中有联系的所有数据;

2. 左链接,以左表为参照,显示所有数据,右表中没有则以null显示

1. 右链接,以右表为参照显示数据，左表中没有则以null显示

3.MySQL事务

事务的隔离级别有哪些？MySQL的默认隔离级别是什么？

什么是幻读，脏读，不可重复读呢？

MySQL事务的四大特性以及实现原理

MVCC熟悉吗，它的底层原理？

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

ACID — 事务基本要素

事务是由一组SQL语句组成的逻辑处理单元，具有4个属性，通常简称为事务的ACID属性。

• A (Atomicity) 原子性：整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样

• C (Consistency) 一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏

• I (Isolation)隔离性：一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰

• D (Durability) 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚

4.MySQL事务四个隔离级别(举例)

简介

SQL 标准定义了四个隔离级别：

• 读未提交：指数据库可以读取未提交的数据；这种隔离级别是最低的，他可能会出现脏读，幻读，不可重复读。

• 读已提交：指数据库可读取并发事务已经提交的数据，可以阻止脏读，但是不能阻止不可重复读和幻读。

• 可重复读：指对同一字段的多次读取结果是一致的，除非数据是被事务本身修改。可以阻止脏读和不可重复读，但幻读依旧可能发生。

• 可串行化：最高的隔离级别，完全服从ACID的隔离级别，所有事务依次执行。可以阻止所有并发事务可以会导致的数据异常。

---

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

数据库事务的隔离级别有4种，由低到高分别为

• READ-UNCOMMITTED(读未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

• READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

查看当前数据库的事务隔离级别：

mysql
show variables like 'tx_isolation'

下面通过事例一一阐述在事务的并发操作中可能会出现脏读，不可重复读，幻读和事务隔离级别的联系。

数据库的事务隔离越严格，并发副作用越小，但付出的代价就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

Read uncommitted

读未提交，就是一个事务可以读取另一个未提交事务的数据。

事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。

Read committed

读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

那怎么解决可能的不可重复读问题？Repeatable read ！

Repeatable read

重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。 MySQL的默认事务隔离级别

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

什么时候会出现幻读？

事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

那怎么解决幻读问题？Serializable！

Serializable 序列化

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。简单来说，Serializable会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

比较

事务隔离级别	读数据一致性	脏读	不可重复读	幻读
读未提交（read-uncommitted）	最低级被，只能保证不读取物理上损坏的数据	是	是	是
读已提交（read-committed）	语句级	否	是	是
可重复读（repeatable-read）	事务级	否	否	是
串行化（serializable）	最高级别，事务级	否	否	否

需要说明的是，事务隔离级别和数据访问的并发性是对立的，事务隔离级别越高并发性就越差。所以要根据具体的应用来确定合适的事务隔离级别，这个地方没有万能的原则。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

这里需要注意的是：与 SQL 标准不同的地方在于InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server)是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化)隔离级别，而且保留了比较好的并发性能。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读已提交):，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

5.MVCC多版本并发控制

引用

MySQL的大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性考虑，一般都同时实现了多版本并发控制（MVCC），包括Oracle、PostgreSQL。只是实现机制各不相同。

可以认为 MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。

MVCC 的实现是通过保存数据在某个时间点的快照来实现的。也就是说不管需要执行多长时间，每个事物看到的数据都是一致的。

典型的MVCC实现方式，分为乐观（optimistic）并发控制和悲观（pressimistic）并发控制。下边通过 InnoDB的简化版行为来说明 MVCC 是如何工作的。

InnoDB 的 MVCC，是通过在每行记录后面保存两个隐藏的列来实现。这两个列，一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的并不是真实的时间，而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

REPEATABLE READ（可重读）隔离级别下MVCC如何工作：

• SELECT

  InnoDB会根据以下两个条件检查每行记录：

  • InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在开始事务之前已经存在要么是事务自身插入或者修改过的

  • 行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除

  只有符合上述两个条件的才会被查询出来

• INSERT：InnoDB为新插入的每一行保存当前系统版本号作为行版本号

• DELETE：InnoDB为删除的每一行保存当前系统版本号作为行删除标识

• UPDATE：InnoDB为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识

保存这两个额外系统版本号，使大多数操作都不用加锁。使数据操作简单，性能很好，并且也能保证只会读取到符合要求的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。

MVCC 只在 COMMITTED READ（读提交）和REPEATABLE READ（可重复读）两种隔离级别下工作。

多版本并发控制:维持一个数据的多个版本，使得读写操作没有冲突

MVCC在MYSQL InnoDB引擎中主要的作用是提高数据库的并发性能，做到读写有冲突时，不加锁，可以实现非阻塞并发读。

MVCC最大的优势：读不加锁，读写不冲突。MVCC机制也是乐观锁的一种体现。

MVCC的实现原理

MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的

• 隐式字段 每行记录除了我们自定义的字段外，还有数据库隐式定义的 DB_TRX_ID, DB_ROLL_PTR, DB_ROW_ID 等字段

  • DB_TRX_ID 6 byte，最近修改(修改/插入)事务 ID：记录创建这条记录/最后一次修改该记录的事务 ID

  • DB_ROLL_PTR 7 byte，回滚指针，指向这条记录的上一个版本（存储于 rollback segment 里）

  • DB_ROW_ID 6 byte，隐含的自增 ID（隐藏主键），如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引

• undo日志 undo log 主要分为两种：

  • insert undo log 代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃

  • update undo log 事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被 purge 线程统一清除

• Read View读视图

  说白了 Read View 就是事务进行快照读操作的时候生产的读视图 (Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的 ID (当每个事务开启时，都会被分配一个 ID , 这个 ID 是递增的，所以最新的事务，ID 值越大)

  trx—id事务d

正是 Read View 生成时机的不同，从而造成 RC , RR 级别下快照读的结果的不同

• 在 RR 级别下的某个事务的对某条记录的第一次快照读会创建一个快照及 Read View, 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个 Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个 Read View，所以对之后的修改不可见；

• 即 RR 级别下，快照读生成 Read View 时，Read View 会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见

• 而在 RC 级别下的，事务中，每次快照读都会新生成一个快照和 Read View , 这就是我们在 RC 级别下的事务中可以看到别的事务提交的更新的原因

  总之在 RC 隔离级别下，是每个快照读都会生成并获取最新的 Read View；而在 RR 隔离级别下，则是同一个事务中的第一个快照读才会创建 Read View, 之后的快照读获取的都是同一个 Read View

MVCC工作流程

InnoDB的MVCC，是通过在每行纪录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建版本号，一个保存了行的删除版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行纪录的版本号进行比较。

MVCC优缺点

MVCC在大多数情况下代替了行锁，实现了对读的非阻塞，读不加锁，读写不冲突。缺点是每行记录都需要额外的存储空间，需要做更多的行维护和检查工作。

补充：

1.MVCC手段只适用于Msyql隔离级别中的读已提交（Read committed）和可重复读（Repeatable Read）。

2.Read uncimmitted由于存在脏读，即能读到未提交事务的数据行，所以不适用MVCC.

原因是MVCC的创建版本和删除版本只要在事务提交后才会产生。

3.串行化由于是会对所涉及到的表加锁，并非行锁，自然也就不存在行的版本控制问题。

4.通过以上总结，可知，MVCC主要作用于事务性的，有行锁控制的数据库模型。

MVCC适用的隔离级别

MVCC只在读已提交和可重复读两个隔离级别下工作。

MVCC是如何实现RC和RR的？

简单来说，他的不同实现方式取决于READVIEW的创建方法：

RC（读已提交）：每进行一次快照读，就创建一次视图，读取最新版本号的数据，所以才会出现不可重复读这个问题。

RR（可重复读）：只在第一次快照读的时候创建视图，之后每次再快照读的时候，读取的都是第一次的记录，所以解决了不可重复读这个问题。

什么是快照读和当前读？

快照读：既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。即基于事务ID的记录。

快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；

当前读：当前读就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

不加锁的简单的 SELECT 都属于快照读，例如：

SELECT * FROM t WHERE id=1

与 快照读 相对应的则是 当前读，当前读就是读取最新数据，而不是历史版本的数据。加锁的 SELECT 就属于当前读，例如：

SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE;
SELECT * FROM t WHERE id=1 FOR UPDATE;

mvcc怎么解决幻读

InnoDB为每行记录添加了一个版本号（系统版本号），每当修改数据时，版本号加一。

在读取事务开始时，系统会给事务一个当前版本号，事务会读取版本号<=当前版本号的数据，这时就算另一个事务插入一个数据，并立马提交，新插入这条数据的版本号会比读取事务的版本号高，因此读取事务读的数据还是不会变。

6.MySQL索引

1.什么是索引

索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。

索引的作用就相当于目录的作用。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

MySQL索引使用的数据结构主要有 B树索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

MySQL的BTree索引使用的是B树中的B+Tree，

2.索引优缺点

可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。毕竟大部分系统的读请求总是大于写请求的。 另外，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

索引的缺点

1. 创建索引和维护索引需要耗费许多时间：当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。

2. 占用物理存储空间 ：索引需要使用物理文件存储，也会耗费一定空间。

3.索引的原理

MySQL的InnoDB存储引擎的底层结构是B+树的结构，B+树是一个矮壮型的树，是一个多叉树，在非叶子节点和根节点，他的叶子节点是一个用指针相连的链表结构，当where 大于等于或小于等于会根据这个非叶子节点进行判断，一次一次判断，找到叶子节点进行查询，利用B+树增快查询效率。

红黑树是局部平衡的二叉树，二叉树树的层数多，影响效率，不如B+树这样的矮壮型的树，查询效率高。

因为B+树里面的叉是有很大的区间，类似一个二分的思想，就是每次判断都会减少一大部分区域，所以我觉得还是B+树的结构更适合。

4.B 树和 B+树区别

• B 树的所有节点既存放 键(key) 也存放 数据(data);而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。

• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。

• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显

5.最左前缀原则

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。

所谓最左前缀原则就是先要看第一列，在第一列满足的条件下再看左边第二列，以此类推，因此在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

6.索引的种类

1. 全文索引

2. Hash

3. BTREE

4. RTREE

Hash索引定位快

Hash索引指的就是Hash表，最大的优点就是能够在很短的时间内，根据Hash函数定位到数据所在的位置，这是B+树所不能比的。

Hash冲突问题

知道HashMap或HashTable的同学，相信都知道它们最大的缺点就是Hash冲突了。不过对于数据库来说这还不算最大的缺点。

Hash索引不支持顺序和范围查询(Hash索引不支持顺序和范围查询是它最大的缺点）

试想一种情况:

SELECT * FROM tb1 WHERE id < 500;

B+树是有序的，在这种范围查询中，优势非常大，直接遍历比500小的叶子节点就够了。而Hash索引是根据hash算法来定位的，难不成还要把 1 - 499的数据，每个都进行一次hash计算来定位吗?这就是Hash最大的缺点了。

7.mysql如何增加索引

1.添加PRIMARY KEY（主键索引）：数据列不能重复，不能为null，一个表只能有一个主键索引。

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

2.添加UNIQUE(唯一索引)： 这种索引和普通索引基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3.添加INDEX(普通索引)：基本的索引类型,可以为null。

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)： 主要就是用于文本内容的查找。

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5.添加多列索引：由多个字段组成的索引。

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

8.索引失效的条件

1. 如果列中有null的话，不会走索引，联合索引中有字段有null值的话，也不会走索引。

2. 对于like以%开头的，不会走索引，比如like %xx%、like %x， like x%是会走索引的。

3. 如果搜索语句里面含有or关键字的话，也是不会走索引的。如果想使用or，又让他走索引的话，那么就让or的每个字段都加上索引。

4. 联合索引查询时，如果查询的第一个字段不是联合索引的第一个字段，那么他也不会走索引的。

5. 如果字段类型是字符串的话，在条件中要将数据用括号括起来，不然他也不会走索引。

6. 索引列参与表达式计算；

7. 如果mysql认为全表扫描比索引快的，那么他也不会走索引的。

16.设计索引的原则

1.适合索引的列是可以出现在where语句之后的列。或者是join连接子句中指定的列。

2.使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间。

3.基数较小的类，加索引的速率还不如全表扫描。

4.索引要适度，够用就行，毕竟索引也是占用空间。

9.创建索引的原则（索引优化）

1.对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

2.更新频繁字段不适合创建索引

3.较频繁作为查询条件的字段才去创建索引

4.定义有外键的数据列一定要建立索引

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6.对于定义为text、image的数据类型的列不要建立索引。

7.最左前缀匹配原则，将查询最频繁的字段放到最左。

7.MySQL锁

1.mysql锁机制

• 表锁：偏性MyISAM，开销小加锁块，锁定力度大，锁冲突的概率最高；

• 行锁：偏性InnoDB，开销大但锁冲突概率小；

• 间隙锁：在可重复读的隔离级别中存在，用于预防幻读的出现。

2.MyISAM表锁

• 当执行select语句时，默认对表执行表锁，当执行修改操作时，默认加写锁；

• 当一个连接获取一张表读锁时，其他连接和该连接都不能修改该表，且该连接不能读其他表；

• 当一个连接对表加上写锁时，该连接可以读写；其他连接不能读写；

• 读锁会阻塞写，写锁会阻塞读写；

• 表锁适合做读为主的数据库；

3.InnoDB行锁

• 事务1获取一个行锁，若执行写操作（加写锁），其他事务不能对该行进行写操作；

• 事务1获取一个行锁，若执行读操作（读锁），其他事务可以对该行读写，但是对事务1不可见

4.间隙锁

间隙锁发生在可重复读的隔离级别中，如若是提交读隔离级别，是不存在间隙锁的。

定义：间隙锁就是将锁加在了不存在的空闲空间，防止其他事务去插入数据造成幻读。

当a无索引的时候

• where a=1时：会将所有行加间隙锁

• where a>5时：会锁所有行

• where a<5时：会锁所有行

当a是唯一索引/主键索引时

• where a=1时：只锁住a=1的左右间隙行。

• where a>5时：会锁住a>5的行。 由于是唯一索引，查询操作不会因为间隙锁阻塞。

• where a<5时：会锁a<5的行。由于是唯一索引，查询操作不会因为间隙锁阻塞。

• where a>5 and a<10时：锁全部行？a=1的行也被锁？

当a是普通索引时

• where a=1时：只锁住a=1的左右间隙行。

• where a>5时：会锁住所有行。

• where a<5时：会锁住所有行。

• where a>5 and a<10时：锁全部行。

优化总结：

• 尽可能让所有数据检索能通过索引完成，避免无索引行锁升级为表锁

• 尽量减少锁的范围

• 尽可能控制事务大小，减少锁定资源的数量和时间长度

8.日志

binlog二进制日志是mysql-server层的，主要是做主从复制，时间点恢复使用 redo log重做日志是InnoDB存储引擎层的，用来保证事务安全 undo log回滚日志保存了事务发生之前的数据的一个版本，可以用于回滚

bin log

bin log中记录的是整个mysql数据库的操作内容，对所有的引擎都适用，包括执行的DDL、DML，可以用来进行数据库的恢复及复制

redo log

redo log中记录的是要更新的数据，比如一条数据已提交成功，并不会立即同步到磁盘，而是先记录到redo log中，等待合适的时机再刷盘，为了实现事务的持久性

🍔MySQL 日志文件有哪些？分别介绍下作用？

分别有 undolog、redolog、 binlog。

1. undolog用于实现原子性，记录版本链用于回滚，并且属于逻辑日志，记录操作便于恢复

2. redolog用于宕机后的数据恢复，记录实际的数据，属于物理日志

3. binlog主要用于主从复制，全量备份，相比redolog可存储的内容更多，既可以物理也可以逻辑存储，一般使用Mix

其中server层还有其他的日志，比如：

• 错误日志（error log）：错误日志文件对 MySQL 的启动、运行、关闭过程进行了记录，能帮助定位 MySQL 问题。

• 慢查询日志（slow query log）：慢查询日志是用来记录执行时间超过 long_query_time 这个变量定义的时长的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。

• 一般查询日志（general log）：一般查询日志记录了所有对 MySQL 数据库请求的信息，无论请求是否正确执行。

🍔redolog如何刷入磁盘的

有三种策略，参数不同的时候，对应的刷入时机不同 首先都会先写到redolog的 buffer 区

参数为0：不写入，等待后台线程定时【每秒】将 buffer 区中的内容写入磁盘

若崩溃，会丢失上一秒中，buffer区里边所有的内容

参数为1：每次提交就写入，先写到 buffer 区，然后立马调用 fsync 写入磁盘

不会丢失，可以看成事务提交和写入磁盘是一个原子性的操作？

参数为2：每次提交，先写到 buffer 区，然后写入到 pageCache 里边，由操作系统来决定何时写入磁盘

只有操作系统崩溃了，才会丢失上一秒中

要选择哪种策略呢？

考虑性能：0>2>1 考虑安全性：1>2>0 综合：2

🍔为何要引入redolog来记录呢？

将磁盘的随机IO转换为追加写入

原本如果我们每次都去更改磁盘里边的内容，则需要先随机IO找到，然后更改 有了写入的话，我们每次都先追加写入到redolog，然后再一次性去磁盘里边找就好了，而不是每次都去磁盘找

🍔redolog 和 binlog 有什么区别

存储的容量不同

redolog以日志文件组作为存储的数据结构，容量是有限的，写满的时候会删除 undolog以追加文件的形式存储，理论上存储容量是无限的

🎈刷盘时机不同

redolog有三种不同的刷盘策略

标答：binlog只在事务提交的时候才写入，而redolog由于有后台进程，事务期间也会写入

所属的范畴不同

redolog是 innoDB 特有的，而binlog是 server 层的

用途不同

redolog用于宕机后的数据恢复 binlog主要用于主从复制，全量备份

🍔🎈为什么需要两阶段提交

1. redolog提交成功，则binlog提交失败了

此时主库没有影响，但从库由于binlog丢失了，从库得不到复制

1. binlog提交成功，但redolog提交失败了

从库由于有binlog的存在，记录了更新，binlog 会被复制到从库，从库执行了这条更新语句，而主库的redolog还没刷盘成功，导致崩溃后没法恢复，主库丢失了本次更新

🍔两阶段提交过程中出现异常了怎么办？

写入binlog的时候出异常

此时redolog虽然准备好了，但是binlog还没写入，也就还没关联好binlog，宕机恢复后扫描到这个redolog的时候发现其没有关联的binlog，认为是不完整的，会丢弃掉

commit阶段出异常

由于此时redolog其实已经准备好了，并且binlog也已经写入了，关联好了XA事务的ID，也可以认定为这个事务是完整的了，宕机恢复后，能找到该redolog对应的binlog

🍔redolog事务执行期间也写入，会不会有数据不一致的问题？

事务执行期间也会写入redolog，而binlog是事务提交后才写入，那这样宕机恢复后会不会出现不一致的情况呢？

• 其实是不会的，因为事务执行过程中宕机的话，binlog还没写呢，也就对应了我们上文的：写binlog出现异常的情况，宕机后恢复，扫描的时候会丢弃

9.存储引擎MyISAM和InnoDB的区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃恢复问题的话）。

两者的对比：

1. 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

2. 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持，外部键等高级数据库功能。 具有事务提交(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

3. 是否支持外键： MyISAM不支持，而InnoDB支持。

4. 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一

一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择MyISAM也是一个不错的选择。但是一般情况下，我们都是需要考虑到这些问题的

10.分库分表

MySQL分表

分表有两种分割方式，一种垂直拆分，另一种水平拆分。

• 垂直拆分

  垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起做为主要表。然后把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中；主要表和次要表的关系一般都是一对一的。

• 水平拆分(数据分片)

  单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。

  水平分割的几种方法：

  • 使用MD5哈希，做法是对UID进行md5加密，然后取前几位（我们这里取前两位），然后就可以将不同的UID哈希到不同的用户表（user_xx）中了。

  • 还可根据时间放入不同的表，比如：article_201601，article_201602。

  • 按热度拆分，高点击率的词条生成各自的一张表，低热度的词条都放在一张大表里，待低热度的词条达到一定的贴数后，再把低热度的表单独拆分成一张表。

     

  • 根据ID的值放入对应的表，第一个表user_0000，第二个100万的用户数据放在第二 个表user_0001中，随用户增加，直接添加用户表就行了。

MySQL分库

为什么要分库?

数据库集群环境后都是多台 slave，基本满足了读取操作; 但是写入或者说大数据、频繁的写入操作对master性能影响就比较大，这个时候，单库并不能解决大规模并发写入的问题，所以就会考虑分库。

分库是什么？

一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上， 通常是将表按照功能模块、关系密切程度划分出来，部署到不同库上。

优点：

• 减少增量数据写入时的锁对查询的影响

• 由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行数变少，减少了磁盘IO，时延变短

但是它无法解决单表数据量太大的问题

分库分表后的难题

分布式事务的问题，数据的完整性和一致性问题。

数据操作维度问题：用户、交易、订单各个不同的维度，用户查询维度、产品数据分析维度的不同对比分析角度。 跨库联合查询的问题，可能需要两次查询 跨节点的count、order by、group by以及聚合函数问题，可能需要分别在各个节点上得到结果后在应用程序端进行合并 额外的数据管理负担，如：访问数据表的导航定位 额外的数据运算压力，如：需要在多个节点执行，然后再合并计算程序编码开发难度提升，没有太好的框架解决，更多依赖业务看如何分，如何合，是个难题。

配主从，正经公司的话，也不会让 Javaer 去搞的，但还是要知道

分库分表之后,id 主键如何处理？

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。

生成全局 id 有下面这几种方式：

• UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。

• 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。

• 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。

• Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。

• 美团的Leaf分布式ID生成系统 ：Leaf 是 美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等中间件。感觉还不错。美团技术团队的一篇文章：Leaf——美团点评分布式ID生成系统 - 美团技术团队 。

• 本文通过搜罗网络资料和自己正例而来，感谢这些知识的提供者！！！！