Mysql调优

1.Mysql优化大体方面：

表的设计合理化(符合3NF)

添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]

SQL语句优化（核心）

分库分表技术(取模分表，水平分割，垂直分割)

读写[写: update/delete/add]分离

存储过程 [模块化编程，可以提高速度]

对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]

mysql服务器硬件升级

定时的去清除不需要的数据,定时进行碎片整理(MyISAM）

 

 

2.mysql和orcle的区别：

查询返回限制条数：  myslq ：limit 10                                   orcle：  where   rownum<=10

 

 

3.什么数据库设计三范式：

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

第二范式：2NF确保表中的每列都和主键相关，第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中（比如学生表中不能存放学校教职工的信息）。

第三范式：3NF确保每列都和主键列直接相关而不是间接相关，要求字段没有冗余。（比如学生表中，不能直接存放老师的名字，所教科目等，可以直接存放老师表的id，把老师表的主键当作学生表的外键）。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

 

4.什么是mvcc:

MVCC多版本控制: 指的是一种提高并发的技术。最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入多版本之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了InnoDB的并发度。

MVCC的实现，是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据是一致的。根据事务开始的时间不同，每个事物对同一张表，同一时刻看到的数据可能是不一样的。不同存储引擎的MVCC实现是不同的，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制

 

 

4.分库分表

垂直拆分：

垂直拆分就是要把表按模块划分到不同数据库中（当一个项目较大的时候可以拆分成很多子系统，这些子系统就可以拥有独立的数据库，让各个系统互不影响）。

垂直拆分用于分布式场景。

 

水平拆分：

水平拆分就是把一张表拆分成几张表，具体根据业务需求，有的按照注册时间、取模、账号规则、年份等（例如现在对最近几年的记录查询较多，则可以把以前的和最近的数据进行分表）。

应用场景：当一张表的数据量过大，即使使用索引查询可能还是较慢，这时就需要把一张表分成多张表来进行优化。

 

取模运算：

当前用的较多还是取模运算，其他的（注册时间，年份，账号规则等）会造成每张表的数据量可能相差较大，取模一般就是对数据表的主键id进行取模运算，不过此时需要一张表来存放所有分表的id（分表的主键id不能设置为自增，需要提前确定id才能判断操作那张表），在并发场景中，一般会提前生成这些id然后存放在redis里。

 

 

5.慢查询：

什么是慢查询

 MySQL默认10秒内没有响应SQL结果,则为慢查询

 可以去修改MySQL慢查询默认时间

如何修改慢查询

--查询慢查询时间

show variables like 'long_query_time';

--修改慢查询时间

set long_query_time=1;                              ---但是重启mysql之后，long_query_time依然是my.ini中的值

如何将慢查询定位到日志中

在默认情况下，我们的mysql不会记录慢查询，需要在启动mysql时候，指定记录慢查询才可以

bin\mysqld.exe --safe-mode  --slow-query-log 。[mysql5.5 可以在my.ini指定]（安全模式启动，数据库将操作写入日志，以备恢复）

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

先关闭mysql,再启动, 如果启用了慢查询日志，默认把这个文件放在

my.ini 文件中记录的位置

#Path to the database root

datadir=" C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

 

6.那些列适合添加索引：

• 肯定在where条件经常使用
• 该字段的内容不是唯一的几个值
• 字段内容不是频繁变化

  索引的类型：

• UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值
• INDEX(普通索引)：允许出现相同的索引内容
• PROMARY KEY(主键索引)：不允许出现相同的值
• fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维
• 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

 

7.索引的代价

占用磁盘空间

对DML(update、delete、insert)语句的效率影响

增删改会对索引影响，因为索引要重新整理。

 

 

8.MySQL explain执行计划解读：

MySql提供了EXPLAIN语法用来进行查询分析，在SQL语句前加一个"EXPLAIN"即可。比如我们要分析如下SQL语句：

explain select * from table where table.id = 1 

运行上面的sql语句后你会看到，下面的表头信息：

table | type | possible_keys | key | key_len | ref | rows | Extra

EXPLAIN列的解释

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

type 
这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

说明：不同连接类型的解释（按照效率高低的顺序排序）

system：表只有一行：system表。这是const连接类型的特殊情况。

const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待。

eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。

ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。

 

9.mysql数据引擎：

使用的存储引擎 myisam / innodb/ memory

myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表，回复表.

INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.

MyISAM 和 INNODB的区别

1. 事务安全（MyISAM不支持事务，INNODB支持事务）

2. 查询和添加速度（MyISAM批量插入速度快）

3. 支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）

4. 锁机制（MyISAM时表锁，innodb是行锁）

5. 外键 MyISAM 不支持外键， INNODB支持外键. (在PHP开发中，通常不设置外键，通常是在程序中保证数据的一致)

Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快. （如果mysql重启的话，数据就不存在了）

 

 

10.mysql主从复制，读写分离：

 

主从复制原理：

 依赖于二进制日志，binary-log，二进制日志中记录引起数据库发生改变的语句 Insert 、delete、update、create table。

 

什么是读写分离：

在数据库集群架构中，让主库负责处理事务性查询，而从库只负责处理select查询，让两者分工明确达到提高数据库整体读写性能。当然，主数据库另外一个功能就是负责将事务性查询导致的数据变更同步到从库中，也就是写操作。

 

读写分离的好处：

1.分摊服务器压力，提高机器的系统处理效率

    读写分离适用于读远比写的场景，如果有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能并不高，而主从只负责各自的写和读，极大程度的缓解X锁和S锁争用；

   假如我们有1主3从，不考虑上述1中提到的从库单方面设置，假设现在1分钟内有10条写入，150条读取。那么，1主3从相当于共计40条写入，而读取总数没变，因此平均下来每台服务器承担了10条写入和50条读取（主库不承担读取操作）。因此，虽然写入没变，但是读取大大分摊了，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了，说白了就是拿机器和带宽换性能；

  2.增加冗余，提高服务可用性，当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务