java面试题之MySql的性能优化

首先需要定位到具体是哪条sql语句执行效率低，通过

show status like 'Com_%select就能知道当前数据库做查询操作的次数，

Com_insert：执行INSERT

Com_update：

Com_delete：

用这些命令就能查看增删改的次数，通过这些来判断当前数据库主要做的是什么操作

再用show profile分析一下sql，得到所有sql语句执行效率之后，通过这条命令记录下来的时间呢，分析具体是哪条sql语句执行效率低

而后用slow_query_log = 1 #开启服务器的慢查询日志，时间超过超过三秒就是慢查询

explain找到时什么原因导致的sql查询慢

一般有六种情况

1. 查询优化不足：SQL查询可能没有充分利用数据库的索引或合适的查询计划，导致查询效率低下。可以通过检查查询计划和索引使用情况来进行优化。

2. 数据库设计问题：数据库的表结构可能不合理，导致查询需要扫描大量数据或执行复杂的连接操作。可以通过重新设计数据库结构来改善查询性能。

3. 数据库服务器配置不当：数据库服务器的配置参数可能没有根据实际需求进行调整，导致性能下降。可以通过调整缓冲区大小、并发连接数等参数来优化数据库服务器性能。

4. 数据库负载过重：数据库服务器可能同时处理了大量的查询请求，导致性能下降。可以通过负载均衡、分片等方式来分散查询压力。

5. 网络延迟：如果数据库服务器与应用程序之间存在网络延迟，会导致查询响应时间变长。可以通过优化网络连接或使用缓存来减少网络延迟对查询性能的影响。

6. 锁竞争：多个并发的查询可能会导致锁竞争，从而降低查询性能。可以通过优化事务隔离级别、减少锁的使用等方式来解决锁竞争问题

找到原因之后就能具体去优化了，对于优化索引可以

1. 对查询频次较高, 且数据量比较大的表, 建立索引.

2. 索引字段的选择, 最佳候选列应当从 where 子句的条件中提取, 如果 where 子句中的组合

比较多, 那么应当挑选最常用, 过滤效果最好的列的组合.

1. 使用唯一索引, 区分度越高, 使用索引的效率越高.

2. 索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维

护效率.

1. 使用短索引, 提高索引访问时的 I/O 效率, 因此也相应提升了 Mysql 查询效率.

2. 如果 where 后有多个条件经常被用到, 建议建立符合 索引, 复合索引需要遵循最左前缀法

则, N 个列组合而成的复合索引, 相当于创建了 N 个索引.

复合索引命名规则 index表名列名 1列名 2列明 3

比如:create index idx_seller_name_sta_addr on tb_seller(name, status, address)

2.2 避免索引失效

1. 如果在查询的时候, 使用了复合索引, 要遵循最左前缀法则, 也就是查询从索引的最左列开

始, 并且不能跳过索引中的列.

1. 尽量不要在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进

黑马程序员·济南校区行全表扫描

1. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全

表扫描。 4.不做列运算where age + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函

数.计算表达式等, 都会是索引失效.

1. 查询 like，如果是

‘%aaa’ 也会造成索引失效.

1. 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有

索引，将导致引擎放弃使用索引而进行全表扫描

而对于sql语句的优化可以

1.根据业务场景建立复合索引只查询业务需要的字段，如果这些字段被索引覆盖，将极

大的提高查询效率.

2.多表连接的字段上需要建立索引，这样可以极大提高表连接的效率.

3.where 条件字段上需要建立索引, 但 Where 条件上不要使用运算函数，以免索引失效.

4.排序字段上, 因为排序效率低, 添加索引能提高查询效率.

5.优化 insert 语句: 批量列插入数据要比单个列插入数据效率高.

6.优化 order by 语句: 在使用 order by 语句时, 不要使用 select *, select 后面要查有

索引的列, 如果一条 sql 语句中对多个列进行排序, 在业务允许情况下, 尽量同时用升

序或同时用降序.

7.优化 group by 语句: 在我们对某一个字段进行分组的时候, Mysql 默认就进行了排序,

但是排序并不是我们业务所需的, 额外的排序会降低效率. 所以在用的时候可以禁止

排序, 使用 order by null 禁用.

select age, count(*) from emp group by age order by null

8.尽量避免子查询, 可以将子查询优化为 join 多表连接查询.

如果对于优化表结构可以

1. 规范化数据库设计：将重复的数据拆分为单独的表，减少数据冗余，提高数据存储效率。使用合适的关联关系来连接这些表，便于查询和维护。

2. 添加合适的索引：通过为频繁查询的字段添加索引，可以加快查询速度。但是过多的索引也会增加写操作的开销，因此需要根据实际情况进行权衡。

3. 选择合适的数据类型：例如，对于较小的整数可以选择使用TINYINT或SMALLINT类型，而不是INT类型。

4. 减少使用过长的字段：过长的字段会消耗更多的存储空间，查询时需要更多的计算和传输时间。可以根据实际需求调整字段的长度，避免过度冗余。

5. 使用合适的主键和外键给表设置适当的主键和外键可以提高查询效率和数据完整性。主键可以用于快速查找和删除数据，外键可以用于建立表之间的关联关系。这几个方面入手 ，除此之外还可以层架硬件cpu，内存或者修改mysql服务器的配置增大缓存innodb_buffer_pool_size