Mysql重要知识点

一、索引

MySQL索引是一种数据结构，用于加快MySQL数据库的查询速度。索引可以理解为一本书的目录，它提供了一个快速查找特定信息的方法。在MySQL中，可以为表中的一列或多列创建索引。当使用索引列作为查询条件时，MySQL可以快速定位匹配的行，从而加快查询速度。

好处：

1.提高查询速度：索引可以快速定位符合条件的记录，避免了对整个数据表进行扫描和比对。
2.减少磁盘I/O：索引可以把数据表分成更小的块，减少磁盘I/O次数，从而提高查询效率。
3.加速排序：在对结果排序时，索引可以帮助数据库更快地完成排序操作。
4.强制唯一性：可以为某些列设置唯一索引，保证数据的唯一性。
5.提高连接速度：在多表关联查询中，索引可以加快表之间的连接速度。

坏处：

1.占用存储空间：每个索引都需要占用一定的存储空间，如果数据表中索引过多，会占用大量的存储空间。
2.降低写入速度：每次对数据表进行修改，索引都需要更新，因此索引会降低写入速度。
3.可能导致查询变慢：如果不恰当地使用索引，可能会导致查询变慢。比如，在某些情况下，全表扫描可能比使用索引更快。
4.维护成本高：索引需要定期维护和优化，否则会影响数据库性能。
MySQL支持多种类型的索引，包括B-Tree索引、哈希索引、全文索引等。其中，B-Tree索引是最常用的索引类型，它可以用于精确匹配、范围查找和排序等操作。

在使用索引时，需要注意以下几点：

1.只为需要经常进行查询的列创建索引，不要为所有列都创建索引。
2.索引的选择性越高，效果越好。选择性是指索引列中不同值的数量与总行数的比值。
3.索引会占用额外的存储空间，并且会影响插入、更新和删除操作的性能。因此，需要在索引和性能之间做出权衡。
4.使用EXPLAIN命令可以分析查询语句的执行计划，从而确定是否使用了索引，以及如何使用索引。
总之，合理地使用索引可以显著提高MySQL数据库的查询性能。

B-tree索引、哈希索引区别

1.构建方式：

B-tree索引是一种树状索引结构，根据键值建立多层的平衡树，每个节点存储一个范围的键值和指向下一层节点的指针。B-tree索引适用于范围查询，如区间查询。
哈希索引是一种基于哈希表的索引结构，根据键值计算哈希值，将键值与哈希值一一映射，加快查询速度。哈希索引适用于等值查询，如精确匹配。

2.查询性能：

B-tree索引的查询性能相对较低，但是支持范围查询，对于大量的数据和频繁的范围查询场景比较适用。
哈希索引的查询性能较高，但是不支持范围查询，只能进行等值查询。当数据量较小且查询主键频率高时，哈希索引效果最佳。

3.适用场景：

B-tree索引适用于范围查询，适合处理大量数据和频繁范围查询的场景。常见的使用场景包括MySQL的InnoDB存储引擎、Oracle数据库等。
哈希索引适用于等值查询，适合处理小量数据和频繁等值查询的场景。常见的使用场景包括Redis、Memcached等内存数据库。

索引的作用主键和唯一的区别

索引（Index）是一种用于优化数据库查询性能的数据结构。索引可以加速数据库中数据的查找、排序和分组等操作，从而提高数据库查询的效率。
在关系型数据库中，常见的索引类型包括普通索引、唯一索引、主键和全文索引等。
主键是一种特殊的唯一索引，它可以用来标识一张表中的唯一记录。主键的作用是保证表中每一行数据都有一个唯一的标识。通常情况下，主键由一个或多个列组成，可以在创建表时指定或者在表已经创建后通过 ALTER TABLE 命令进行添加。
唯一主键是一种限制表中某个列或几个列的值必须唯一的约束，它类似于普通主键，但不强制表中一定要有一个主键。唯一主键可以用来确保表中某个列或几个列的值不重复。一个表只能有一个主键，但可以有多个唯一主键。
区别在于：

1. 主键要求表中必须有且只能有一个列为主键，而唯一主键可以有多个。
2. 主键具有非空、唯一、不重复的特性，而唯一主键只能保证列的唯一性，但可以为空。
3. 在查询表时，通过主键查询的效率比唯一主键查询的效率更高。

总之，主键和唯一主键都是用来保证表中数据的唯一性的约束，但主键是一种特殊的唯一约束，通常作为表的标识符使用，可以用于相对高效地检索和查询数据。

索引的优化思路

索引是数据库中非常重要的组成部分，可以提高查询性能和数据访问速度。以下是一些索引优化的思路：
1.确定需要创建索引的列：只有在经常用于查询的列上创建索引才有意义。不需要对所有列都创建索引，因为这会增加存储和维护的成本。
2.选择适当的索引类型：根据查询类型和数据类型选择适当的索引类型。例如，对于全文搜索，可以使用全文索引，而对于数字列可以使用B-tree索引。
3.限制索引的数量：尽量避免创建过多的索引，因为这会使查询变慢并增加存储和维护成本。只保留必要的索引。
4.避免在索引列上进行计算：如果在索引列上进行计算，那么查询将无法使用索引。因此，应该尽可能避免在索引列上进行计算。
5.避免在索引列上使用函数：函数对索引的效率有很大的影响，因为它们会使查询无法使用索引。应该尽可能避免在索引列上使用函数。
6.定期维护索引：定期重新组织索引以提高性能。在数据增加或删除较多时，可以使用重建索引来优化性能。
7.使用覆盖索引：覆盖索引是一种特殊类型的索引，可以包含所有需要查询的列，从而避免了对表的访问。这可以显著提高查询性能。
7.使用联合索引：联合索引是多个列的组合索引，可以提高复合条件查询的性能。但是，过多的联合索引可能会导致索引失效，因此需要权衡。
综上所述，索引优化需要根据具体情况进行选择和调整，以便在提高查询性能的同时最小化存储和维护成本。

二、MySQL三大日志

redo log（重做日志）

记录MySQL引擎执行的所有修改 数据 的操作（例如插入、更新、删除等）。在系统崩溃时，MySQL可以使用redo log来恢复数据。

binlog（二进制日志）

记录所有对MySQL 数据库 进行的修改操作，包括对数据表结构的修改（例如创建、删除、重命名表等）。binlog通常用于数据备份、数据恢复和数据同步等操作。

undo log（回滚日志）

记录MySQL事务对数据所做的修改，当事务执行时，MySQL会将修改记录写入Undo Log。如果事务需要回滚，则可以使用Undo Log中的信息来将数据恢复到事务执行前的状态。
除了支持事务回滚外，Undo Log还用于实现MVCC（多版本并发控制）。在MVCC中，每个事务看到的数据都是在它开始读取数据时的版本。当事务修改数据时，会创建新的版本，而旧版本则被保留在Undo Log中。这样，其他事务仍然可以读取旧版本的数据，实现了并发控制。

三、MySQL事务隔离级别

MySQL 中的事务隔离级别指的是在并发情况下，事务之间对于数据的隔离程度。MySQL 支持四种事务隔离级别，分别是：
1.读未提交（Read Uncommitted）：一个事务可以读取另一个事务未提交的数据，可能导致 脏读、幻读和不可重复读 等问题。
2.读已提交（Read Committed）：一个事务只能读取另一个事务已经提交的数据，避免了脏读，但是可能仍会出现 幻读和不可重复读 等问题。
3.可重复读（Repeatable Read）：一个事务在执行期间多次读取同一数据时，多次读取的结果是一致的，避免了脏读和不可重复读，但是可能仍会出现幻读问题。
4.串行化（Serializable）：强制事务串行执行，避免了脏读、幻读、不可重复读等问题，但是会导致性能降低。
在实际应用中，应该根据业务场景选择合适的事务隔离级别，权衡数据一致性和性能。通常情况下，使用可重复读已经能够满足大部分业务需求。

四、InnoDB存储引擎对MVCC的实现

InnoDB存储引擎是MySQL数据库中常用的一种存储引擎。MVCC（多版本并发控制）是InnoDB存储引擎实现事务隔离级别的一种方式。
MVCC技术是在读取数据时，不会加锁，而是通过版本号来判断数据是否可见，从而提高并发性能。在InnoDB中，每行数据都有一个隐藏的版本号，称作“系统版本号”（system versioning）。每次对数据进行修改时，都会生成一个新的版本号，并将旧版本号保留在历史版本链中。在读取数据时，根据事务的隔离级别，InnoDB会选择合适的版本返回给用户。
在实现MVCC时，InnoDB存储引擎还使用了一些附加的数据结构来管理历史版本链和事务的可见性。例如，每个事务都有一个唯一的“事务ID”（transaction ID），用于标识该事务启动的时间戳。在读取数据时，InnoDB会根据事务ID和版本号来判断该数据是否可见。
另外，为了避免历史版本链无限制地增长，InnoDB存储引擎还会定期进行“垃圾回收”（garbage collection），将不再需要的历史版本从链表中删除。
综上所述，InnoDB存储引擎通过MVCC技术实现了高并发的事务隔离级别。通过隐藏的版本号和附加的数据结构，可以提高并发性能，同时保证数据的一致性和隔离性。

五、SQL语句在MySQL中的执行过程

查询语句的执行流程：
权限校验（如果命中缓存）—>查询缓存—>分析器—>优化器—>权限校验—>执行器—>引擎
更新语句执行流程：
分析器---->权限校验---->执行器—>引擎—redo log(prepare 状态)—>binlog—>redo log(commit 状态)

六、MySQL 缓存机制

1.查询缓存：MySQL 的查询缓存是将查询结果缓存到内存中，当再次执行相同的查询时，MySQL 直接从缓存中返回结果，从而避免了对数据库的访问和查询操作，提高了查询效率。但是，查询缓存并不是适用于所有情况，因为对于表中数据的更新、插入或删除操作，都会导致与该表相关的查询缓存被清除，所以在更新频繁的应用中，查询缓存的效果并不好。

2.InnoDB 缓存：InnoDB 存储引擎是 MySQL 的默认存储引擎，它提供了一个称为 buffer pool 的缓存机制，可以缓存热点数据到内存中，从而提高查询效率。buffer pool 的大小可以通过配置文件进行设置，一般来说，需要根据应用的特点和硬件配置来决定 buffer pool 的大小。

需要注意的是，MySQL 的缓存机制可以提高查询效率，但是过多的缓存会占用大量内存资源，降低系统的稳定性和性能。因此，需要根据实际情况来合理地设置缓存大小，避免过度缓存。

七、对大表做修改有什么注意事项

1.备份：在进行大表修改之前，一定要先备份数据，以防修改过程中出现意外情况或错误操作导致数据丢失。
2.事务控制：针对大表进行修改时需要用到事务来确保修改操作的原子性和一致性，避免修改过程中出现中断或错误情况。
3.性能和效率问题：大表修改操作会涉及大量的数据，一些传统的查询和修改方式可能无法胜任，需要采用特别的优化方案，如优化SQL语句，通过分区操作以减少影响到的行数，使用批量处理等方式来提高性能和效率。
4.锁问题：当修改大表时可能会导致锁问题，需要小心处理，建议使用读写分离等技术来减少锁竞争问题的发生。
5.磁盘空间问题：对大表进行修改操作时需要注意磁盘空间的问题，需预留足够的磁盘空间进行修改操作。

八、对大表做修改对某些字段上锁

在数据库中，可以通过行级锁来锁定某些字段，以保证对这些字段的修改不会被其他事务同时进行而造成数据不一致的问题。具体实现方法取决于所使用的数据库系统，在这里以 MySQL 数据库为例进行说明。

MySQL 数据库中，可以使用 SELECT … FOR UPDATE 语句来锁定某些字段。例如，假设有一个名为 user 的表，其中包含 id 和 name 两个字段，我们想要对 name 字段进行修改并锁定，可以使用如下 SQL 语句：

复制
BEGIN;
SELECT name FROM user WHERE id = 1 FOR UPDATE;
-- 进行一些修改操作
COMMIT;

这里的 FOR UPDATE 表示对查询结果进行行级锁定，从而确保其他事务不能同时修改这些行。在事务结束时，锁也会自动释放。

需要注意的是，使用行级锁会对并发性能产生一定影响，因此应该谨慎使用，并根据实际情况进行优化。
当对大表进行修改时，需要上锁的字段包括：

1. 修改的字段：如果多个用户同时修改同一个字段，会出现数据不一致的问题，因此需要对该字段加锁。

2. 索引字段：如果修改的字段是索引字段，那么对该索引字段进行修改时，需要对该索引字段加锁。

3. 关联字段：如果修改的字段与其他表有关联，也需要对关联字段加锁，防止其他用户同时修改导致数据不一致。

需要注意的是，加锁会对性能造成一定的影响，因此需要根据实际情况进行权衡和优化。同时，如果大表的数据量比较大，建议采用分区表或分表的方式来减少锁冲突的可能性。

九、explain时候看什么值

在MySQL中，使用EXPLAIN语句可以用于分析SQL查询语句的执行计划，帮助开发者进行SQL优化。当进行分析时，我们主要需要关注以下几个值：

1. id: SELECT查询的序列号，可以用于表示查询的执行顺序。

2. select_type: 视图复杂查询中查询类型的标识符，有简单查询、联合查询、子查询、派生表查询等类型。

3. table: 查询数据的表名。

4. partitions：数据查询的分区情况。

5. type: 查询的方式，包括全表扫描、索引扫描、范围扫描、唯一索引、联合索引等类型，其中性能较好的查询方式是ref和eq_ref。

6. possible_keys：查询可能使用的索引。

7. key：实际使用的索引。

8. key_len：索引字段的长度。

9. ref：这个值表示了索引的哪一列被使用了。

10. rows：扫描出的数据行数。

11. filtered：这个值表示了查询结果的过滤程度，取值范围是0到1。

12. extra：补充信息，包括使用的索引类型、内存临时表等。

使用EXPLAIN语句可以帮助开发者分析SQL查询语句的执行计划，找出查询语句中需要优化的部分，以提高SQL查询的性能。具体来说，EXPLAIN语句可以做以下几件事情：

1. 分析查询语句的执行计划：EXPLAIN语句可以返回查询语句的执行计划，显示查询语句中每个操作执行的顺序、使用的索引和策略等。

2. 找出查询语句中需要优化的部分：通过查看查询语句的执行计划，可以确定可能导致查询性能下降的操作，例如使用全表扫描而不是索引扫描等。根据发现的问题，可以针对性地对查询语句和数据库结构进行优化。

3. 提高SQL查询的性能：通过优化查询语句和数据库结构，可以减少查询语句的执行时间，提高SQL查询的性能。

4. 了解索引使用情况：通过查看EXPLAIN语句的返回结果，可以了解每个查询语句中使用的索引，以帮助优化索引的选择和创建。

使用EXPLAIN语句可以帮助了解SQL查询语句的执行计划，从而发现需要优化的部分和问题。下面是如何了解索引使用情况：

1. 查看possible_keys列：该列列出了查询中可以使用的索引列表。如果该列中只有一个索引，表示该查询中只使用了单个索引。如果该列中列出了多个索引，则该查询可以使用多个索引。如果该列中有NULL值，则表示该查询可能需要创建新的索引。

2. 查看key列：该列列出了实际使用的索引名称。如果该列的值为NULL，则说明该查询没有使用任何索引。如果该列的值和possible_keys列中只有一个相同，表示查询使用了唯一的索引。如果该列的值和possible_keys列中有多个相同，表示查询可能使用了联合索引；如果该列的值为PRIMARY，则表示查询使用了主键索引。

3. 查看Extra列：该列提供了查询的其他执行信息。如：Using index表示查询使用覆盖索引，即所需的所有数据都可以从索引中读取，而不需要访问表本身，这样可以加快查询速度。另外，Using where表示查询中有where子句；Using temporary表示MySQL在查询中使用了临时表；Using filesort表示MySQL在查询中使用了文件排序。

综上所述，通过查询可能使用的索引和实际使用的索引，我们可以了解MySQL使用了哪些索引，可以根据这些信息进行索引的优化和调整。通过查看Extra列，我们还可以识别查询执行过程中的瓶颈，从而进一步优化查询语句。