MySQL面试题-性能优化

红太狼 2023-09-27 15:32 63阅读 0赞

**目录**

1.MySQL的优化可以从哪些方面考虑？

2.索引优化有哪些建议

3.如何优化sql的性能

4.大数据量（超100万行）的批量写（UPDATE、DELETE、INSERT）操作会导致什么问题

5.MySQL修改大表的表结构会出现什么问题

--------------------

## 1.MySQL的优化可以从哪些方面考虑？ ##

1.索引优化

索引是加速数据库查询的关键。在设计表结构时，应该根据查询的需求添加合适的索引。常用的索引包括主键、唯一索引、普通索引、全文索引等。

同时，要避免过多的索引，因为每个索引都需要占用存储空间，会影响写入性能。

2.查询优化

优化查询语句是提高MySQL性能的重要手段。要尽可能使用索引，避免全表扫描。同时，要避免使用子查询，尽可能使用连接查询；避免在查询中使用“%”通配符；避免多余的字段等等。

3.数据库表结构优化

合理的表结构可以提高查询效率和减少存储空间。应该避免使用大字段，如TEXT、BLOB等，因为这些字段会占用大量的存储空间。同时，应该避免冗余字段，避免更新和维护时的复杂性。

4.缓存优化

使用缓存可以大大减轻MySQL数据库的压力，提高查询效率。常用的缓存技术包括Memcached和Redis等。

5.分区优化

对于数据量较大的表，可以使用分区技术将表分成多个部分。这样可以提高查询效率，同时降低了单个表的存储空间和索引大小。

6.配置优化

MySQL的参数配置会影响MySQL的性能。需要根据实际情况进行调整，包括缓冲区、连接数、线程数、查询缓存等等。

7.硬件优化

硬件设备也会影响MySQL的性能。要选择更快速的硬件设备，如更快的磁盘、更快的CPU和更多的内存等等。同时，要根据实际情况来决定使用RAID、SSD等技术。

## 2.索引优化有哪些建议 ##

1.  创建主键索引：每张表都应该有一个主键，可以使用自增主键或者UUID等方式创建。主键索引可以大大提高查询效率。
2.  创建唯一索引：对于需要唯一性约束的列，可以创建唯一索引。唯一索引可以避免重复数据的插入，并且可以大大提高查询效率。
3.  创建联合索引：对于经常用到多个列进行查询的情况，可以创建联合索引。联合索引是指包含多个列的索引。在使用联合索引时，需要注意索引列的顺序，应该将选择性高的列放在前面。
4.  创建前缀索引：对于文本类型的列，可以使用前缀索引来提高查询效率。前缀索引是指只对文本的一部分进行索引。
5.  不要使用全文索引代替普通索引：全文索引可以用来搜索文本内容，但是不能用来排序或者分组。如果需要排序或者分组，应该使用普通索引。
6.  避免使用过多的列创建索引：索引列越多，索引的大小就越大，维护的代价也越高。应该根据实际情况来决定创建哪些索引。
7.  避免使用含有NULL值的列作为索引列：含有NULL值的列无法使用B-Tree索引，会影响查询效率。
8.  对于大表的索引优化，可以使用索引覆盖扫描、索引合并等技术，以提高查询效率。

## 3.如何优化sql的性能 ##

1.  尽量减少查询次数：查询的次数越多，数据库的负担就越重。可以通过合并多个查询语句、使用子查询等方式来减少查询次数。
2.  使用索引：在查询语句中使用索引可以大大提高查询效率。应该根据具体的查询需求来选择合适的索引类型。
3.  避免在查询条件中使用函数：在查询条件中使用函数会导致索引失效，影响查询效率。应该尽可能避免在查询条件中使用函数。
4.  使用适当的JOIN方式：在使用JOIN时，应该选择合适的JOIN方式。INNER JOIN是最常用的JOIN方式，但是在某些情况下，LEFT JOIN或者RIGHT JOIN可能更合适。
5.  避免使用SELECT \*：SELECT \*会返回所有列的数据，包括不需要的列，会增加网络传输的开销和数据库的负担。应该尽可能指定需要的列。
6.  避免使用子查询：子查询是一种方便的查询方式，但是在某些情况下，子查询的性能可能比较低。可以考虑使用JOIN方式来代替子查询。
7.  避免在查询语句中使用OR：在查询语句中使用OR会导致查询优化器无法使用索引，影响查询效率。可以考虑使用UNION ALL来代替OR。
8.  使用LIMIT限制返回的数据行数：使用LIMIT可以限制返回的数据行数，可以减少数据库的负担和网络传输的开销。
9.  使用EXPLAIN分析查询计划：使用EXPLAIN可以查看MySQL优化器在执行查询时选择的查询计划，可以根据查询计划来优化查询语句。

## 4.大数据量（超100万行）的批量写（UPDATE、DELETE、INSERT）操作会导致什么问题 ##

1.  阻塞其他操作：批量写操作可能需要占用大量的系统资源，包括CPU、内存、磁盘等，如果写操作持续时间过长，可能会阻塞其他操作，导致系统响应变慢。
2.  磁盘空间不足：批量写操作可能会占用大量的磁盘空间，如果磁盘空间不足，可能会导致写操作失败或者数据库无法正常工作。
3.  日志过大：在执行写操作时，MySQL会生成事务日志用于保证数据的一致性。如果写操作的数据量过大，事务日志也会变得非常大，可能会导致磁盘空间不足或者日志写入速度变慢。
4.  死锁：如果多个客户端同时进行批量写操作，并且操作的数据范围有重叠，可能会导致死锁。
5.  数据库性能下降：如果批量写操作的负载太大，可能会导致数据库性能下降，查询响应时间变慢，甚至出现数据库崩溃的情况。
6.  主从延迟：在MySQL主从复制架构中，如果主服务器上发生了大量的写操作，从服务器需要读取和应用这些操作，就会导致主从延迟。特别地，如果从服务器在处理写操作的过程中遇到了锁冲突或者主服务器上的写操作太多，从服务器的复制进程可能会被阻塞，从而导致主从延迟。

为了避免以上问题，可以采取以下措施：

1.  尽量减少批量写操作的数据量，可以将大批量数据分批进行写入，以避免对系统的影响。
2.  预留足够的磁盘空间和系统资源，以确保批量写操作能够正常执行。
3.  优化数据库表结构和索引，以提高写操作的性能和效率。
4.  使用事务进行批量写操作，以确保数据的一致性。
5.  使用数据库中的队列或者缓存机制，将批量写操作异步化处理，以避免对系统的影响。

## 5.MySQL修改大表的表结构会出现什么问题 ##

1.  长时间的阻塞：当对大表进行修改时，MySQL 会将表锁定，这将阻塞其他用户对该表的读写操作。在修改期间，如果有其他用户尝试访问该表，则会被阻塞，可能会导致应用程序的延迟或响应时间变慢。
2.  需要大量的空间和时间：在修改大表结构时，需要为该表创建一个新的临时表并将数据复制到该表中。这将需要大量的磁盘空间和时间，特别是对于具有大量数据的表。
3.  数据丢失：如果在修改表结构时发生错误，可能会导致数据丢失或不一致。因此，在进行此类操作之前，应备份数据库以便出现问题时可以进行恢复。
4.  应用程序问题：如果应用程序依赖于表的结构或数据格式，则修改表结构可能会导致应用程序出现问题。在进行此类操作之前，应先检查应用程序的代码以确保它们不会受到影响。

使用 pt-online-schema-change 工具可以在一定程度上减少 MySQL 修改大表结构所带来的问题。

1.  长时间的阻塞：pt-online-schema-change 会在新表上进行修改，然后使用临时表来追踪原表中的更改，最后将更改应用到新表中。这意味着在修改期间，原表可以继续使用，不会被锁定。
2.  需要大量的空间和时间：pt-online-schema-change 将使用少量的临时空间，因为它只需要跟踪原表中的更改。此外，它也会自动分批次复制数据到新表中，减少对磁盘的压力。
3.  数据丢失：pt-online-schema-change 不会对原表进行直接修改，因此不会对数据造成丢失。
4.  应用程序问题：使用 pt-online-schema-change 可以最大程度上减少对应用程序的影响。由于原表可以继续使用，因此应用程序可以继续读写数据，直到新表完全准备好并替换原表。