MySql数据库应该这样优化

ゝ一世哀愁。 2024-04-08 09:15 56阅读 0赞

**目录**

一. 数据库设计优化

1.1 适度的违反范式

1.2 适当建立索引

1.3 对表进行水平划分

1.4 对表进行垂直划分

1.5 选择合适的引擎

二. SQL语句优化

2.1 尽量使用批量操作

2.2 选择适当的数据类型

2.3 使用连接（JOIN）来代替子查询(Sub-Queries)

2.4 对查询语句进行优化

三. 数据库参数配置优化

四. 硬件和系统的优化

4.1 合理采用操作系统

4.2 读写分离

五. 数据库专栏

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

## 一. 数据库设计优化 ##

### 1.1 适度的违反范式 ###

> 在开发应用程序时，数据库设计要最大程度的遵守三范式。当 然，三范式最大的问题在于查询时通常需要join很多表，导致查询效率很低。所以有时候基于性能考虑，我们需要有意的违反三范式。也就是适度的做冗余，以达到提高查询效率的目的。**注意这里的反范式是适度的**。

### 1.2 适当建立索引 ###

> 索引可以有效的提高查询速度，但这个提高是以插入、更新、删除的速度为代价的。由于索引的存储结构不同于表的存储，一个表的索引所占空间比可能数据所占空间还大。这意味着我们在写数据库的时候做了很多额外的工作，而这个工作只是为了提高读的效率。因此，我们建立一个索引，必须保证这个索引不会“亏本”。一般需要遵守这样的规则：
> 
>  *  对经常更新的表就避免对其进行过多的索引。
>  *  对经常用于查询的字段应该创建索引。
>  *  数据量小的表最好不要使用索引。
>  *  相同值较多的字段上不要建立索引（比如"性别"字段）。相反的，在不同值较多的字段上可以建立索引。

### 1.3 对表进行水平划分 ###

> 如果一个表的记录数太多了，比如上千万条，而且需要经常检索，那么我们就有必要化整为零了。如果我拆成100个表，那么每个表只有10万条记录。当然这 需要数据在逻辑上可以划分。一个好的划分依据，有利于程序的简单实现，也可以充分利用水平分表的优势。  
> 比如系统界面上只提供按月查询的功能，那么把表按月拆分成12个，每个查询只查询一个表就够了。

### 1.4 对表进行垂直划分 ###

> 有些表记录数并不多，但是字段却很多，导致表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。解决方案是：可以把一部分字段拆分到另一个表，并且该表与原表是一对一的关系。

### 1.5 选择合适的引擎 ###

> 在Oracle和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySQL数据库提供了多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎。比如：
> 
>  *  InnoDB存储引擎支持外键、支持事务、支持全文检索；
>  *  MyISAM存储引擎可以被压缩、不支持事务、不支持外键；如果需要执行大量的select语句，MyISAM存储引擎是更好的选择。
>  *  MEMORY存储引擎主要用于那些内容稳定的表，或者作为统计操作的中间表。

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

## 二. SQL语句优化 ##

### 2.1 尽量使用批量操作 ###

> 尽量使用MySql中的批量操作语句，这样可以避免频繁读写操作。比如：批量插入、批量更新、批量删除。

/* 使用多个insert语句效率低 */
    insert into dept values(null,'技术部','沈阳市')；
    insert into dept values(null,'销售部','上海市')；
    insert into dept values(null,'总部','北京市')；
    
    /* 使用批量insert语句效率高 */
    insert into dept values(null,'技术部','沈阳市'),(null,'销售部','上海市'),(null,'总部','北京市');

### 2.2 选择适当的数据类型 ###

> 选择字段的数据类型的一般原则是尽量使用占用字节小的数据类型。  
> 比如主键， 强烈建议用自增类型，既节省空间，又能满足大多数需求。

> 文件、图片等大文件用文件系统存储，不要使用数据库  
> **数据库只存储文件路径**。这是一个基本原则！

### 2.3 使用连接（JOIN）来代替子查询(Sub-Queries) ###

> MySQL从4.1开始支持SQL的子查询。但是，子查询可以被更有效率的连接替代。连接之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成需要多个步骤才能完成的子查询。

/* 查询部门地址在"NEW YORK"的所有员工 */
    
    /* 子查询方式效率低 */
    select * from emp where deptno in(select deptno from dept where loc='NEW YORK')
    
    /* 多表连接方式效率高 */
    select e.* from emp e,dept d where e.deptno=d.deptno and d.loc='NEW YORK'

### 2.4 对查询语句进行优化 ###

> 绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。下面是应该注意的几个方面。
> 
>  *  首先，最好是在相同类型的字段间进行比较的操作，可以避免转型的步骤。
>  *  其次，在建有索引的字段上尽量不要使用函数进行操作。使用函数后索引将失效。
>  *  在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。
>  *  应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

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

## 三. 数据库参数配置优化 ##

> MySql安装之后，默认参数并不能满足所有生产场合下的需求。所以，还需要根据需求，修改Mysql服务器的系统参数，达到合理利用服务器现有资源，最大合理的提高MySQL性能。比如：
> 
>  *  wait\_timeout：MySQL客户端的数据库连接闲置最大时间值。
>  *  max\_connections：MySQL的最大连接数。
>  *  max\_user\_connections：每个数据库用户的最大连接。
>  *  thread\_concurrency：线程最大并发数。
>  *  default-storage-engine：设置MySQL默认存储引擎

-- 查询MySQL的最大连接数。
    show global variables like '%max_connections%';
    -- 设置MySQL的最大连接数。
    set global max_connections=1024;

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

## 四. 硬件和系统的优化 ##

### 4.1 合理采用操作系统 ###

> 如果服务器内存超过4G，那么毋庸置疑应当采用64位操作系统和64位mysql

### 4.2 读写分离 ###

> 如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库的压力分散。  
> **比如：**主库用来写入，从库都用来做select，那么每个数据库分担的压力小了很多。当然，要实现这种方式，需要程序特别设计，给程序开发带来了额外负担。不过现在已经有中间件来实现这些功能。

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

## 五. 数据库专栏 ##

[https://blog.csdn.net/weixin\_53919192/category\_11813282.html?spm=1001.2014.3001.5482![icon-default.png_t_M666][]https://blog.csdn.net/weixin\_53919192/category\_11813282.html?spm=1001.2014.3001.5482][https_blog.csdn.net_weixin_53919192_category_11813282.html_spm_1001.2014.3001.5482_icon-default.png_t_M666_https_blog.csdn.net_weixin_53919192_category_11813282.html_spm_1001.2014.3001.5482]

[icon-default.png_t_M666]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/32412a0c82014ab4bbd298b625fde4b6.png
[https_blog.csdn.net_weixin_53919192_category_11813282.html_spm_1001.2014.3001.5482_icon-default.png_t_M666_https_blog.csdn.net_weixin_53919192_category_11813282.html_spm_1001.2014.3001.5482]: https://blog.csdn.net/weixin_53919192/category_11813282.html?spm=1001.2014.3001.5482