20191127 数据库查询优化方案_select * from people procedure analyse();-优快云博客

本文链接：https://blog.youkuaiyun.com/chenrushui/article/details/103274062

本文深入探讨了数据库优化的各种策略，包括SQL语句优化、索引管理、表结构优化、硬件升级和MySQL配置调整。覆盖了从SQL语法改进到硬件资源利用的全方位优化技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

不能跨库查询是一种理念，每个库所有的数据出口、入口，只能有一个中间件，要统一收口，要不然不能确定数据从哪里落库的，从哪里出去的。

-- 不为null这个条件经常忘记
select pd.id,ai.id from p_doctor pd  
inner join account_info ai
on ai.id=pd.acct_id
where pd.register_source!=ai.register_source 
and pd.register_source=0 
and ai.register_source!=0 
and ai.register_source is not null;

每个微服务只能访问自己的表，每个微服务的表存放在一个单独的数据库中(大公司都这样)。

分库后如果想刷数据，只能进行数据的导入导出。

使用事务进行管理

1、把数据插入本地account_info；

2、远程调用写入到一个事务方法里面。

mysql时间类型比较。

SELECT * FROM p_hospital where modify_time > '2019-12-20 00:00:00' and modify_time < '2019-12-20 11:00:00' （datetime类型）

SELECT * FROM p_doctor where modify_time > '2019-12-19 20:00:00' and modify_time < '2019-12-20 11:00:00'

写查询语句的时候单词拼写错误，导致查询报错（一般都是拼写错误）。 primary（"id"）通过sql语句创建表。一定要在线下环境执行多次sql，避免在生产环境执行不了。分号，库名必须要带。

delete与truncate的区别？

1）delete删除的时候是一条一条的删除记录，它配合事务，可以将删除的数据找回。

2）truncate删除，它是将整个表摧毁（会重置auto_increment），然后再创建一张一模一样的表。它删除的数据无法找回。

日志表

就算大于500万条记录，也不用拆表，因为日志表如果数据满了，就不会在写入数据，只用来保存数据，直接切换到新的数据表即可。

每天创建一张表：通过代码创建，mybatis支持。

mysql复制表结构和数据，什么情况下需要复制表结构和数据？

比如日志表需要复制表结构和数据。

select * from 表名 procedure analyse();

PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。

最后一列为建议列。

explain:执行计划中的执行概况；分析当前sql执行过程中的资源消耗情况。

哪些操作耗时最大？执行"概况"

1.使用索引

应尽量避免全表扫描，首先应考虑在 where 及 order by ,group by 涉及的列上建立索引。

2.优化 SQL 语句

2.1通过 explain(查询优化神器)用来查看 SQL 语句的执行效果（查看sql执行计划，选择最优路径）

可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。通常我们可以对比较复杂的尤其是涉及到多表的 SELECT 语句，把关键字 EXPLAIN 加到前面，查看执行计划。

例如： explain select * from news;

2.2任何地方都不要使用 select * from t；

用具体的字段列表代替“*” ，不要返回用不到的任何字段。

mysql innodb上的理解。

1），不需要的字段会增加数据传输的时间，即使mysql服务器和客户端是在同一台机器上，使用的协议还是tcp，通信也是需要额外的时间。

2），要取的字段、索引的类型，和这两个也是有关系的。举个例子，对于user表，有name和phone的联合索引，select name from user where phone=12345678912 和 select * from user where phone=12345678912，前者要比后者的速度快，因为name可以在索引上直接拿到，不再需要读取这条记录了（是否需要回表查询）。

3），大字段，例如很长的varchar，blob，text。准确来说，长度超过728字节的时候，会把超出的数据放到另外一个地方，因此读取这条记录会增加一次io操作。

2.3索引列不能参与计算，保持列“干净”

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);（列上不能进行计算，否则会导致索引失效）

2.4查询尽可能使用 limit 减少返回的行数，减少数据传输时间和带宽浪费。

3 优化数据库对象

3.1优化表的数据类型

使用 procedure analyse()函数对表进行分析，该函数可以对表中列的数据类型提出优化建议。能小就用小。表数据类型第一个原则是：使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、 cpu 缓存的使用。

使用方法： select * from 表名 procedure analyse();

3.2 对表进行拆分

通过拆分表可以提高表的访问效率。有 2 种拆分方法1.垂直拆分。把主键和一些列放在一个表中，然后把主键和另外的列放在另一个表中。如果一个表中某些列常用，而另外一些不常用，则可以采用垂直拆分。2.水平拆分。根据一列或者多列数据的值把数据行放到二个独立的表中。

3.3 使用中间表来提高查询速度

创建中间表，表结构和源表结构完全相同，转移要统计的数据到中间表，然后在中间表上进行统计，得出想要的结果。

4.硬件优化

4.1 CPU 的优化

选择多核和主频高的 CPU。

4.2 内存的优化

使用更大的内存。将尽量多的内存分配给 MYSQL 做缓存。

4.3 磁盘 I/O 的优化

4.3.1 使用磁盘阵列

RAID 0 没有数据冗余，没有数据校验的磁盘陈列。实现 RAID 0至少需要两块以上的硬盘，它将两块以上的硬盘合并成一块，数据连续地分割在每块盘上。

RAID1 是将一个两块硬盘所构成 RAID 磁盘阵列，其容量仅等于一块硬盘的容量，因为另一块只是当作数据“镜像”。使用 RAID-0+1 磁盘阵列。 RAID 0+1 是 RAID 0 和 RAID 1 的组合形式。它在提供与 RAID 1 一样的数据安全保障的同时，也提供了与 RAID 0 近似的存储性能。

4.3.2 调整磁盘调度算法

选择合适的磁盘调度算法，可以减少磁盘的寻道时间

5.MySQL 自身的优化（配置文件）

对 MySQL 自身的优化主要是对其配置文件 my.cnf 中的各项参数进行优化调整。如指定 MySQL 查询缓冲区的大小，指定 MySQL 允许的最大连接进程数等。

6.应用优化

6.1 使用数据库连接池

6.2 使用查询缓存

它的作用是存储 select 查询的文本及其相应结果。如果随后收到一个相同的查询，服务器会从查询缓存中直接得到查询结果。查询缓存适用的对象是更新不频繁的表，当表中数据更改后，查询缓存中的相关条目就会被清空。

数据库sql常见优化方案

为什么要优化：随着实际项目的启动，数据库经过一段时间的运行，最初的数据库设置，会与实际数据库运行性能会有一些差异，这时我们就需要做一个优化调整。

（主机性能，内存使用性能，网络传输性能，架构方面，sql语句执行性能等）

1、SELECT子句中避免使用*号