MySql数据库优化_数据库对象可以减少查询时间

MYSQL优化主要考虑如下

存储引擎、字段类型、三范式、索引、分库分表、主从复制、读写分离、优化sql语句

1. 存储引擎：

存储引擎是一种用来存储MySQL中对象（记录和索引）的一种特定的结构（文件结构），处于MySQL服务器的最底层，直接存储数据。

常用的主要是这两种，分别为myisam和innodb。

MySQL 5.5 及更高版本，默认的存储引擎是 InnoDB。在 5.5 版本之前，MySQL 的默认存储引擎是 MyISAM

InnoDB：

支持事务，回滚以及系统崩溃的修复能力，行级锁（每次操作锁住一行数据），不支持全文搜索

事务安全型存储引擎。更加注重数据的完整性和安全性。

优缺点：InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

MyISAM：

MyISAM 不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的。

访问速度快，对事务完整性没有要求。

MyISAM 适合查询、插入为主的应用场景。

适合于单方向（读或写）的任务场景、同时并发量不高、对于事务要求不高的场景。

2.字段类型选择：

选取最适合的字段属性

一般说来，数据库中的表越小其查询的速度就也快，在建表的时候，为了获取更好的性能，将表中的字段长度设的尽可能的小。

尽可能定长（占用存储空间固定）

尽可能使用整数

尽可能的把字段设置成NOT NULL，这样在执行查询的时候，数据库不用去比较NULL值。

3.三范式：

范式是数据库设计时遵循的一种规范，不同的规范要求遵循不同的范式。

最常用的三大范式：

第一范式，原子性，列或者字段不能再分。

第二范式的话要满足第一范式，唯一性，不可以把多种数据保存在同一张表中，即一张表只能保存一类数据，否则可能导致插入数据异常。

第三范式，直接性，不存在传递依赖，他要在满足第二范式的条件上，在每一列都和主键直接相关，而不能间接相关。

4.索引：

利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。索引是提高查询速度最有效的方法。一个没有建立任何索引的表，就相当于一本没有目录的书，在每次查询时就会进行全表扫描，这样会导致查询效率极低、速度也极慢。如果建立索引，那么就好比一本添加的目录，通过目录的指引，迅速翻阅到指定的章节，提升的查询性能，节约了查询资源。

索引种类：

主键索引：要求关键字不能重复，也不能为NULL。同时增加主键约束。(id)

唯一索引：要求关键字不能重复。同时增加唯一约束。(编码)

普通索引,index：对关键字没有要求。(经常搜索的一些字段:名称)

全文索引: 可以针对值中的某个单词.

单例索引：一个索引只包含一个列，一个表可以有多个单例索引。

组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

PS：这里主键索引和唯一索引的区别在于：主键索引不能为空值，唯一索引允许空值；主键索引在一张表内只能创建一个，唯一索引可以创建多个。主键索引肯定是唯一索引，但唯一索引不一定是主键索引。

创建索引的原则：

经常需要搜索的列上建立索引，可以加快搜索的速度。

在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。

在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速度。

在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的。

在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询。

在经常使用 WHERE 语句的列上创建索引，加快条件的判断速度。

导致索引失效的一些情况：

如果条件中有or，即使其中有条件带索引也不会使用，这也是为什么尽量少用or的原因，如果要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。

like查询是以%开头，会导致索引失效。

对索引列进行运算导致索引失效。

索引的弊端：

索引不是越多越好,索引是需要维护成本的。

不能盲目的创建索引，只为查询操作频繁的列创建索引，创建索引会使查询操作变得更加快速，但是会降低增加、删除、更新操作的速度，因为执行这些操作的同时会对索引文件进行重新排序或更新。

一般来说，查询操作远远大于其他操作的。

在大量数据导入时，可以先删除索引，再批量插入数据，最后再添加索引。

5.分区分库分表

分区

1. 定义

-数据库分区是将数据量大的表的数据均摊到不同的硬盘、系统或不同服务器存储介质中（将数据从物理上分成若干个小表存储），实际上还是一张表。

-将表的数据均衡到不同的地方，为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。提升查询效率。

2. 优点

• 相对于单个文件系统或是硬盘，分区可以存储更多的数据；

• 数据管理比较方便，比如要清理或废弃某年的数据，就可以直接删除该日期的分区数据即可；

• 精准定位分区查询数据，不需要全表扫描查询，大大提高数据检索效率；

• 可跨多个分区磁盘查询，来提高查询的吞吐量；

• 在涉及聚合函数查询时，可以很容易进行数据的合并；

3. 分类

3.1水平分区

-背景：数据量庞大

-介绍：对表的行进行分区，不同物理不同分组里面的物理分割数据集得以组合，从而进行个体分割或集体分割。所有在表中定义的列在每个数据中都能找到。所以表的特性依然得以保持。（每行数据的字段不减少）

3.2垂直分区

-背景：每行数据字段多，但有些字段包含大text且不经常被访问，这些字段就需要被切割出去。

-介绍：对表的垂直划分来减少目标表的宽度，使某些特定的列被划分到特定的分区，每个列都包含了其中的列所对应的行。

4. 使用场景

* 一张表的查询速度已经慢到影响使用；

* SQL经过优化还是很慢；

* 数据量大；

* 表中的数据是分段的；

* 对数据的操作往往只涉及一部分，而不是所有的数据。

分表

1. 定义

就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。

2. 作用

* 减少查询时间

* 磁盘I/O性能提高（数据分布在不同的文件）

* 读写锁影响的数据量小

* 插入数据库需要建立索引的数据减少

3. 使用场景

* 一张表的查询速度慢到影响使用时；

* SQL经过优化；

* 数据量大；

* 当插入数据或联合查询速度变慢时。

4. 分区和分表的区别

* 目的都是减少数据库的负担，提高表的增删改查效率。

* 分区只是一张表中的数据的存储位置发生改变，分表是将一张表分成多张表。

* 当访问量大且表数据比较大时，两种方式可以互相配合使用。

* 当访问量不大，但表数据比较多时，可以只进行分区。

5. 常见分区分表的规则策略

* Range(区域、距离等)

* Hash

* 按照时间拆分

* Hash之后按照分表个数取模

分库

1. 目的

为突破单节点数据库服务器的I/O能力限制，解决数据库扩展性问题。

2. 水平拆分

-将一张表分成多张表并存放在不同的数据库中。

-问题：

1.影响主键自增；

2.单表查询会变成多表（如count函数操作）

3. 垂直拆分

-将系统中不存在关联关系或需要join的表可以放在不同数据库不同的服务器中。

-问题：

1.ACID被打破；

2.Join操作困难；

3.外键约束受到影响。

4. 应用背景

* 单台DB的存储空间不够

* 随着查询量的增加，单台数据库服务器已经没法支撑

垂直分库-->水平分库-->读写分离

总结：优先考虑分区，当分区不能满足需求时，开始考虑分表，合理的分表对效率的提升会优于分区。

分库分表详解

分库分表就是把较大的数据库和数据表按照某种策略进行拆分。目的在于：降低每个库、每张表的数据量，减小数据库的负担，提高数据库的效率。

主要分为两种方式，一个是水平拆分，另一个是垂直拆分

垂直分表定义：将一个表的字段分散到多个表中，每个表存储其中一部分字段。

比如30个字段，拆分为两个表，一个表20个字段，一个表10个字段这样，或者按照其他方式拆分成3个表，这样的拆分原则呢就是将大字段或者不经常修改的或者经常查询的字段拆分出来，作为单独的表存储，然后跟主表一对一的关系存储，这样的话对功能做了分离，高并发场景下，垂直拆分一定程度的提升IO性能，不过依然存在单表数据量过大的问题；

垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，从而达到多个服务器共同分摊压力的效果。

解决业务层面的耦合，业务清晰

能对不同业务的数据进行分级管理、维护、扩展等

高并发场景下，垂直分库在一定程度上可以提升IO、数据库连接数、单机硬件资源的性能。

水平分库是把同一个表的数据按一定规则拆分到不同的数据库中，每个库可以放在不同的服务器上。

比如判断ID是奇数还是偶数，然后把数据分别存放到两个数据库中。也就是说，要操作某条数据，先分析这条数据的ID，如果ID为奇数，将此操作映射至数据库1；如果ID为偶数，将操作映射至数据库2，这就叫水平分库。

它带来的提升是：

解决了单库大数据，高并发的性能瓶颈。

按照合理拆分规则拆分，join操作基本避免跨库。

提高了系统的稳定性及可用性。

水平分表与水平分库的思路类似，这次拆分的目标是表，如果ID为奇数，将此操作映射至信息表1；如果ID为偶数，将操作映射至表2，这就叫水平分表。水平分表是在同一个数据库内，把同一个表的数据按一定规则拆分到多个表中。

它带来的提升是：

优化单一表数据量过大而产生的性能问题

避免IO争抢并减少锁表的几率.

分库分表带来的问题：分库分表有效的缓解了大数据、高并发带来的性能和压力，分库分表把数据分布在不同库甚至不同服务器，不可避免会带来分布式事务问题，经过分库分表后，数据不在一个数据库或一个表中，甚至不在一台服务器上，无法通过sql语句进行关联查询等。

6.主从复制、读写分离

主从复制、读写分离一般是一起使用的。目的很简单，就是为了提高数据库的并发性能。

主从复制：

概念：使用两台或两台以上的数据库服务器，分别设置主服务器和从服务器，对主服务器的任何操作都会同步到从服务器上。

主要作用：

（1）当主服务器出现问题时，可以切换到从服务器，避免数据丢失。

（2）可以在从服务器上查询数据，降低主服务器压力。

实现原理：

mysql会将所有修改过数据库的sql语句记录在二进制bin-log日志中。主从复制实际是将多台服务器bin日志的开启，然后主服务器会把执行过的sql语句记录到bin日志中，之后从服务器读取该日志，在从服务器再把bin日志中记录的sql语句同样的执行一遍。这样从服务器上的数据就和主服务器同步了。

主从复制解决的问题

使数据不被丢失，备份，读写分离，高可用

关于Mysql主从复制的配置，主从复制是Mysql自带的功能

实现在主从服务器的mysql配置文件中进行配置。

读写分离

读写分离就是只在主服务器上写，只在从服务器上读。

实现数据读写分离：

可以减轻单台数据库服务器的并发访问压力

提高机器硬件的利用率

读写分离的原理：

多台MySQL服务器

分别提供读、写服务、均衡流量

通过主从复制保持数据的一致性

如何实现数据的读写分离：

通过程序实现（写代码实现）

通过安装软件提供的服务实现（中间件）MyCat、mysql-proxy、Amoeba

MyCat是目前最流行的基于Java语言编写的数据库中间件，是一个实现了MySql协议的服务器，其核心功能是分库分表。配合数据库的主从模式还可以实现读写分离。

MySQL- Proxy为 MySQL 开源项目，通过其自带的 lua 脚本进行SQL 判断，需要写大量的脚本，这些脚本并不是现成的，而是需要自己去写，不推荐使用。

Amoeba：该程序由Java语言进行开发。这个软件致力于mysql的分布式数据库前端代理层，它主要为应用层访问mysql的时候充当sql路由功能。

7.sql优化

• 使用limit对查询结果的记录进行限定

• 避免select *，将需要查找的字段列出来

• 使用join来代替子查询

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

• 应尽量避免在 where 子句中对字段进行 null 值判断

• 应尽量避免在 where 子句中使用!=或<>操作符

• 应尽量避免在 where 子句中使用 or 来连接条件

• in 和 not in 也要慎用， 否则会导致全表扫描， 对于连续的数值，能用 between 就不要用 in 了。

• 应尽量避免like查询以%开头，%开头不使用索引，%结尾会使用索引

• 应尽量避免在 where 子句中对字段进行表达式操作

• 应尽量避免在 where 子句中对字段进行函数操作

• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，

• 并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引 列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段 sex，男、女的值 几乎各一半，那么即使在 sex 上建了索引也对查询效率起不了作用。

• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低 了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索 引需要慎重考虑，视具体情况而定。

• 多表关联查询时，小表在前，大表在后。

• 尽量使用数值替代字符串类型。

8.查询优化过程

1.索引。数据量增多的时候，查询速度开始变慢，这个时候一般会考虑到用索引

复合索引（A,B）相当于：

索引（A）

索引（A,B）

最左边的优先约束，因此最左边的定义约束数据量多的字段。

2.分区。加了索引，查询速度提升了一点，但是由于数据量还是大，查询还是不够快，考虑分区，把数据打到不同的物理区域上。

分区：同一张表数据打在不同的物理区域。

适合：数据量大，访问量不大的时候

3.分表。加了分区之后，数据量还是大，这个时候考虑分表。

分表：拆成多张表

适合：数据量大，访问量大的时候，考虑分区分表。

4. 分库。单台的数据库空间不够了，或者分表后I/O瓶颈，性能还是上不去，考虑分库。

适合：单台DB空间不够的时候；访问量增加，单台无法支撑。

解决：单台DB的I/O瓶颈

总结：

• 查询慢，优先考虑索引-->分区-->分表分库

• 索引，普通索引、唯一索引、复合索引。

9.索引的数据结构

1.为什么不用数组

数组查询效率是很快，但是插入很慢，从中间插入需要整体往后挪动。

2.为什么不用链表

链表查询慢，需要从头遍历，肯定不适合索引。

3.为什么不用二叉树（红黑树、AVL树）

二叉树容易导致单边数，就变成链表了，也不适合索引。

二叉树只有两个节点，树的高度必然会很高，所以二叉树都不适合。

4.为什么不用b树

b树是多叉树，但是他的叶子节点和非叶子节点都会有索引值和数据。

b+树非叶子节点放索引值，叶子节点放数据，这样节点可以放更多的索引值，树的高度更小。并且呢，b+树有一个向右的指针更加方便检索。

10.如何设计大数据量表

分区

1.分区就是数据打到不同的物理区域上。

2.注意分区字段是不能更改的

什么时候使用分区表：

1、表的大小超过2GB。

2、表中包含历史数据，新的数据被增加都新的分区中。

create table pay_record(
  pay_id VARCHAR2(15),
  out_user_id number(15),
  in_user_id number(15),
  amount number(10),
  status number(1),
  pay_time date,
  create_time date
)
partition by range(pay_time) 
(
partition pay_time_20210411 values less than (to_date('20210411','yyyyMMdd')),
partition pay_time_20210412 values less than (to_date('20210412','yyyyMMdd')),
partition pay_time_20210413 values less than (to_date('20210413','yyyyMMdd')),
partition pay_time_20210414 values less than (to_date('20210414','yyyyMMdd')),
partition pay_time_20210415 values less than (to_date('20210415','yyyyMMdd'))
);
---两个字段作为分区
drop table pay_record;
create table pay_record(
  pay_id VARCHAR2(15),
  home_city number(3),
  out_user_id number(15),
  in_user_id number(15),
  amount number(10),
  status number(1),
  pay_time date,
  create_time date
)
partition by range(home_city,pay_time) 
(
partition pay_time_20210411 values less than (591,to_date('20210411','yyyyMMdd')),
partition pay_time_20210412 values less than (591,to_date('20210412','yyyyMMdd')),
partition pay_time_20210413 values less than (591,to_date('20210413','yyyyMMdd')),
partition pay_time_20210414 values less than (591,to_date('20210414','yyyyMMdd')),
partition pay_time_20210415 values less than (591,to_date('20210415','yyyyMMdd')),
partition pay_time_59220210411 values less than (592,to_date('20210411','yyyyMMdd')),
partition pay_time_59220210412 values less than (592,to_date('20210412','yyyyMMdd')),
partition pay_time_59220210413 values less than (592,to_date('20210413','yyyyMMdd')),
partition pay_time_59220210414 values less than (592,to_date('20210414','yyyyMMdd')),
partition pay_time_59220210415 values less than (592,to_date('20210415','yyyyMMdd'))
);

分表

拆分原则：3年内，oracle单表达2000w，mysql单表达500万

拆表种类：水平分表、垂直分表。

水平分表：就是创建多个表结构相同的表。

垂直分表：就是一个表字段太多了，适当的拆分成多个结构不同的表。

分库

1.可以根据业务拆分到不同的库里。

2.需要考虑查询的时候不能跨库。

如何设计大数据量表

千万级：优先考虑分区、索引

亿级：考虑分库分表

1.首先要知道这个表是做什么操作：频繁读还是频繁写。

（1）不频繁读，比如定期去扫描这张表，这种没必要读写分离。

（2）频繁读写，读写分离环节数据库压力，读写分离需要考虑同步。

2.优先考虑分区跟索引

（1）有时间性的，就通过时间字段增加分区。

（2）分区字段是不能更改的，这一点要考虑进去，避免将来要修改的时候发现无法修改。

（3）复合索引要把能过滤大量数据的字段放在前面。

讨论点和结论

1.讨论：Mysql优化

1. 选择数据引擎、字段、范式、索引、分库分表、主从复制读写分离、SQL优化。

2. 可以使用explain ,show profiles 语句分析SQL语句;

3. 组合使用explain extended + show warnings，explain的扩展能在原本基础上额外提供一些查询优化的信息，通过show warnings命令得到。

4. 分表分库有如何使用

5. 建表时应尽量遵循三范式，字段数据类型和长度要适度。

6. IN连接查询结果集，往往和EXISTS做比较。

a.其中子查询WHERE里的条件不受外层查询的影响，这类查询一般情况下，自动优化转成EXISTS语句，也就是效率和EXISTS一样。

b.子查询WHERE里的条件受外层查询的影响，这类查询的效率要看相关条件涉及的字段的索引情况和数据量多少，一般效率不如EXISTS，数据量越大，效果越明显

2.结论:

数据库优化需要每一步流程都尽量遵守规范，才能达到较好的效果。