Mysql中面试必考

最新推荐文章于 2022-11-16 23:12:19 发布

置顶私念

最新推荐文章于 2022-11-16 23:12:19 发布

阅读量218

点赞数

分类专栏：面试必考

本文链接：https://blog.csdn.net/tiancityycf/article/details/103612010

版权

面试必考专栏收录该内容

21 篇文章 0 订阅

订阅专栏

参考： http://www.phpmianshi.com/?id=24

通用规则是：

多读少写加缓存，少读多写加队列

带事务的不要整体commit，分段commit

一、要保证数据库的效率，要做好以下四个方面的工作：

① 数据库设计

② sql语句优化

③ 数据库参数配置

④ 恰当的硬件资源和操作系统

二、通俗地理解三个范式，对于数据库设计大有好处。

在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。没有冗余的数据库设计可以做到。

但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

三、SQL优化的一般步骤

①通过show status命令了解各种SQL的执行频率。

②定位执行效率较低的SQL语句-（重点select）

③通过explain分析低效率的SQL语句的执行情况

④确定问题并采取相应的优化措施

下面的例子：

#其中Com_XXX表示XXX语句所执行的次数。

show global status like 'Com_%';

重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

#其中Com_XXX表示XXX语句所执行的次数。

show global status like 'com_%';

#试图连接MySQL服务器的次数

show status like 'Connections';

#服务器工作的时间（单位秒）

show status like 'Uptime';

#慢查询的次数 (默认是10)

show status like 'Slow_queries'

4.如何查询mysql的慢查询时间

Show variables like 'long_query_time';

5. 修改mysql 慢查询时间

set long_query_time=2

四、哪些列上适合添加索引

1、唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

select * from emp where sex = '男'

2、更新非常频繁的字段不适合创建索引

select * from emp where logincount = 1

全文索引FULLTEXT => 只在MYISAM 存储引擎支持, 目的是全文索引，在内容系统中用的多，在全英文网站用多(英文词独立). 中文数据不常用，意义不大国内全文索引通常使用 sphinx 来完成.

#查看索引的使用情况

show status like 'Handler_read%';

大家可以注意：

handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。含义：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。”

优化group by 语句

默认情况，MySQL对所有的group by col1,col2进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗，则可以使用order by null禁止排序

Explain举例

mysql> explain select * from event;
+—-+————-+——-+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | |
+—-+————-+——-+——+—————+——+———+——+——+——-+
1 row in set (0.00 sec)

各个属性的含义

select查询的序列号

select_type

select查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。

table

输出的行所引用的表。

type

联合查询所使用的类型。

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys

指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。

key

显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。

key_len

显示MySQL决定使用的键长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。

ref

显示哪个字段或常数与key一起被使用。

rows

这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。

Extra

如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。

如果是where used，就是使用上了where限制。

如果是impossible where 表示用不着where，一般就是没查出来啥。

如果此信息显示Using filesort或者Using temporary的话会很吃力，WHERE和ORDER BY的索引经常无法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。

五、最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大

　　innodb_additional_mem_pool_size = 64M

　　innodb_buffer_pool_size =1G

对于myisam，需要调整key_buffer_size

innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数

推荐的做法是

innodb_flush_log_at_trx_commit=2

sync_binlog=N (N为500 或1000) 默认sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

且使用带蓄电池后备电源的缓存cache，防止系统断电异常。

一参数意义

innodb_flush_log_at_trx_commit

InnoDB的innodb_flush_log_at_trx_commit属性可以控制每次事务提交时InnoDB的行为。当属性值为0时，事务提交时，不会对重做日志进行写入操作，而是等待主线程按时写入；当属性值为1时，事务提交时，会将重做日志写入文件系统缓存，并且调用文件系统的fsync，将文件系统缓冲中的数据真正写入磁盘存储，确保不会出现数据丢失；当属性值为2时，事务提交时，也会将日志文件写入文件系统缓存，但是不会调用fsync，而是让文件系统自己去判断何时将缓存写入磁盘。

innodb_flush_log_at_commit是InnoDB性能调优的一个基础参数，涉及InnoDB的写入效率和数据安全。

当sync_binlog =N (N>0) ，MySQL 在每写 N次二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

注:

如果启用了autocommit，那么每一个语句statement就会有一次写操作；否则每个事务对应一个写操作。

根据上述描述，我做了一张图，可以方便大家查看。

二性能

两个参数在不同值时对db的纯写入的影响表现如下：

测试场景1

innodb_flush_log_at_trx_commit=2

sync_binlog=1000

测试场景2

innodb_flush_log_at_trx_commit=1

sync_binlog=1000

测试场景3

innodb_flush_log_at_trx_commit=1

sync_binlog=1

测试场景4

innodb_flush_log_at_trx_commit=1

sync_binlog=1000

测试场景5

innodb_flush_log_at_trx_commit=2

sync_binlog=1000

场景	TPS
场景1	41000
场景2	33000
场景3	26000
场景4	33000

由此可见，当两个参数设置为双1的时候，写入性能最差，sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 时，(在当前模式下)MySQL的写操作才能达到最高性能。

三安全

当innodb_flush_log_at_trx_commit和sync_binlog 都为 1 时是最安全的，在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得，双11 会导致频繁的io操作，因此该模式也是最慢的一种方式。

当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。

双1适合数据安全性要求非常高，而且磁盘IO写能力足够支持业务，比如订单,交易,充值,支付消费系统。双1模式下，当磁盘IO无法满足业务需求时比如11.11 活动的压力

总结
一、为了保证事务的ACID特性，理论上每次事务提交都应该刷盘，但此时效率很低，有两种优化方向：
（1）随机写优化为顺序写；
（2）每次写优化为批量写；

二、redo log是一种顺序写，它有三层架构：
（1）MySQL应用层：Log Buffer
（2）OS内核层：OS cache
（3）OS文件：log file

三、为了满足不用业务对于吞吐量与一致性的需求，MySQL事务提交时刷redo log有三种策略：
（1）0：每秒write一次OS cache，同时fsync刷磁盘，性能好；
（2）1：每次都write入OS cache，同时fsync刷磁盘，一致性好；
（3）2：每次都write入OS cache，每秒fsync刷磁盘，折衷；

四、高并发业务，行业内的最佳实践，是：
innodb_flush_log_at_trx_commit=2