您的位置:

mysql数据库优化的思路是什么

本文目录一览:

浅谈数据库查询优化的几种思路

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

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

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

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操作。

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

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

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

通过拆分表可以提高表的访问效率。 有 2 种拆分方法

1.垂直拆分

把主键和一些列放在一个表中, 然后把主键和另外的列放在另一个表中。 如果一个表中某些列常用, 而另外一些不常用, 则可以采用垂直拆分。

2.水平拆分

根据一列或者多列数据的值把数据行放到二个独立的表中。

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

选择多核和主频高的 CPU。

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

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 调整磁盘调度算法

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

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

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

数据库该如何优化?

数据库优化可以从以下几个方面进行:

1.结构层: web服务器采用负载均衡服务器,mysql服务器采用主从复制,读写分离

2.储存层: 采用合适的存储引擎,采用三范式

3.设计层: 采用分区分表,索引,表的字段采用合适的字段属性,适当的采用逆范式,开启mysql缓存

4.sql语句层:结果一样的情况下,采用效率高,速度快节省资源的sql语句执行

数据库如何优化

body{

line-height:200%;

}

如何优化MySQL数据库

当MySQL数据库邂逅优化,它有好几个意思,今天我们所指的是性能优化。

我们究竟该如何对MySQL数据库进行优化呢?下面我就从MySQL对硬件的选择、Mysql的安装、my.cnf的优化、MySQL如何进行架构设计及数据切分等方面来说明这个问题。

1.服务器物理硬件的优化

1)磁盘(I/O),MySQL每一秒钟都在进行大量、复杂的查询操作,对磁盘的读写量可想而知,所以推荐使用RAID1+0磁盘阵列,如果资金允许,可以选择固态硬盘做RAID1+0;

2)cpu对Mysql的影响也是不容忽视的,建议选择运算能力强悍的CPU。

2.MySQL应该采用编译安装的方式

MySQL数据库的线上环境安装,我建议采取编译安装,这样性能会较大的提升。

3.MySQL配置文件的优化

1)skip

-name

-resolve,禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间;

2)back_log

=

384,back_log指出在MySQL暂时停止响应新请求之前,短时间内的多少个请求可以被存在堆栈中,对于Linux系统而言,推荐设置小于512的整数。

3)如果key_reads太大,则应该把my.cnf中key_buffer_size变大,保持key_reads/key_read_requests至少在1/100以上,越小越好。

4.MySQL上线后根据status状态进行适当优化

1)打开慢查询日志可能会对系统性能有一点点影响,如果你的MySQL是主-从结构,可以考虑打开其中一台从服务器的慢查询日志,这样既可以监控慢查询,对系统性能影响也会很小。

2)MySQL服务器过去的最大连接数是245,没有达到服务器连接数的上限256,应该不会出现1040错误。比较理想的设置是:Max_used_connections/max_connections

*

100%

=85%

5.MySQL数据库的可扩展架构方案

1)MySQL

cluster,其特点为可用性非常高,性能非常好,但它的维护非常复杂,存在部分Bug;

2)DRBD磁盘网络镜像方案,其特点为软件功能强大,数据可在底层块设备级别跨物理主机镜像,且可根据性能和可靠性要求配置不同级别的同步。

优化MYSQL数据库的方法

在开始演示之前,我们先介绍下两个概念。

概念一,数据的可选择性基数,也就是常说的cardinality值。

查询优化器在生成各种执行计划之前,得先从统计信息中取得相关数据,这样才能估算每步操作所涉及到的记录数,而这个相关数据就是cardinality。简单来说,就是每个值在每个字段中的唯一值分布状态。

比如表t1有100行记录,其中一列为f1。f1中唯一值的个数可以是100个,也可以是1个,当然也可以是1到100之间的任何一个数字。这里唯一值越的多少,就是这个列的可选择基数。

那看到这里我们就明白了,为什么要在基数高的字段上建立索引,而基数低的的字段建立索引反而没有全表扫描来的快。当然这个只是一方面,至于更深入的探讨就不在我这篇探讨的范围了。

概念二,关于HINT的使用。

这里我来说下HINT是什么,在什么时候用。

HINT简单来说就是在某些特定的场景下人工协助MySQL优化器的工作,使她生成最优的执行计划。一般来说,优化器的执行计划都是最优化的,不过在某些特定场景下,执行计划可能不是最优化。

比如:表t1经过大量的频繁更新操作,(UPDATE,DELETE,INSERT),cardinality已经很不准确了,这时候刚好执行了一条SQL,那么有可能这条SQL的执行计划就不是最优的。为什么说有可能呢?

来看下具体演示

譬如,以下两条SQL,

A:

select * from t1 where f1 = 20;

B:

select * from t1 where f1 = 30;

如果f1的值刚好频繁更新的值为30,并且没有达到MySQL自动更新cardinality值的临界值或者说用户设置了手动更新又或者用户减少了sample page等等,那么对这两条语句来说,可能不准确的就是B了。

这里顺带说下,MySQL提供了自动更新和手动更新表cardinality值的方法,因篇幅有限,需要的可以查阅手册。

那回到正题上,MySQL 8.0 带来了几个HINT,我今天就举个index_merge的例子。

示例表结构:

mysql desc t1;+------------+--------------+------+-----+---------+----------------+| Field      | Type         | Null | Key | Default | Extra          |+------------+--------------+------+-----+---------+----------------+| id         | int(11)      | NO   | PRI | NULL    | auto_increment || rank1      | int(11)      | YES  | MUL | NULL    |                || rank2      | int(11)      | YES  | MUL | NULL    |                || log_time   | datetime     | YES  | MUL | NULL    |                || prefix_uid | varchar(100) | YES  |     | NULL    |                || desc1      | text         | YES  |     | NULL    |                || rank3      | int(11)      | YES  | MUL | NULL    |                |+------------+--------------+------+-----+---------+----------------+7 rows in set (0.00 sec)

表记录数:

mysql select count(*) from t1;+----------+| count(*) |+----------+|    32768 |+----------+1 row in set (0.01 sec)

这里我们两条经典的SQL:

SQL C:

select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;

SQL D:

select * from t1 where rank1 =100  and rank2 =100  and rank3 =100;

表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引。

那我们来看SQL C的查询计划。

显然,没有用到任何索引,扫描的行数为32034,cost为3243.65。

mysql explain  format=json select * from t1  where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "3243.65"    },    "table": {      "table_name": "t1",      "access_type": "ALL",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "rows_examined_per_scan": 32034,      "rows_produced_per_join": 115,      "filtered": "0.36",      "cost_info": {        "read_cost": "3232.07",        "eval_cost": "11.58",        "prefix_cost": "3243.65",        "data_read_per_join": "49K"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))"    }  }}1 row in set, 1 warning (0.00 sec)

我们加上hint给相同的查询,再次看看查询计划。

这个时候用到了index_merge,union了三个列。扫描的行数为1103,cost为441.09,明显比之前的快了好几倍。

mysql explain  format=json select /*+ index_merge(t1) */ * from t1  where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "441.09"    },    "table": {      "table_name": "t1",      "access_type": "index_merge",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "key": "union(idx_rank1,idx_rank2,idx_rank3)",      "key_length": "5,5,5",      "rows_examined_per_scan": 1103,      "rows_produced_per_join": 1103,      "filtered": "100.00",      "cost_info": {        "read_cost": "330.79",        "eval_cost": "110.30",        "prefix_cost": "441.09",        "data_read_per_join": "473K"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))"    }  }}1 row in set, 1 warning (0.00 sec)

我们再看下SQL D的计划:

不加HINT,

mysql explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "534.34"    },    "table": {      "table_name": "t1",      "access_type": "ref",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "key": "idx_rank1",      "used_key_parts": [        "rank1"      ],      "key_length": "5",      "ref": [        "const"      ],      "rows_examined_per_scan": 555,      "rows_produced_per_join": 0,      "filtered": "0.07",      "cost_info": {        "read_cost": "478.84",        "eval_cost": "0.04",        "prefix_cost": "534.34",        "data_read_per_join": "176"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))"    }  }}1 row in set, 1 warning (0.00 sec)

加了HINT,

mysql explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "5.23"    },    "table": {      "table_name": "t1",      "access_type": "index_merge",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "key": "intersect(idx_rank1,idx_rank2,idx_rank3)",      "key_length": "5,5,5",      "rows_examined_per_scan": 1,      "rows_produced_per_join": 1,      "filtered": "100.00",      "cost_info": {        "read_cost": "5.13",        "eval_cost": "0.10",        "prefix_cost": "5.23",        "data_read_per_join": "440"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))"    }  }}1 row in set, 1 warning (0.00 sec)

对比下以上两个,加了HINT的比不加HINT的cost小了100倍。

总结下,就是说表的cardinality值影响这张的查询计划,如果这个值没有正常更新的话,就需要手工加HINT了。相信MySQL未来的版本会带来更多的HINT。

mysql数据库如何优化?谁能给出点具体的解决方案?

1、explain:解释sql的执行计划,后边的sql不执行

2、explain partitions :用于查看存在分区的表的执行计划

3、explain extended:待验证

4、show warnings:

5、show create table:查看表的详细的创建语句,便于用户对表进行优化

6、show indexes :产看表的所有索引,show indexes from table_name,同样也可以从information_schema.statistics表中获得同样的信息。cardinality列很重要,表示数据量。

7、show tables status: 查看数据库表的底层大小以及表结构,同样可以从information_schema.tables表中获得底层表的信息。

8、show [global|session]status:可以查看mysql服务器当前内部状态信息。可以帮助却行mysql服务器的负载的各种指标。默认是session。同information_schema.global_status和information_schema.session_status

9、show [global|session] variables :查看当前mysql系统变量的值,其中一些值能影响到sql语句的执行方式。同information_schema.global_variables和information_schema.session_variables;

10、information_schema:包含的表的数量和mysql的版本有关系。

MySQL数据库性能优化有哪些技巧?

1.存储引擎的选择如果数据表需要事务处理,应该考虑使用InnoDB,因为它完全符合ACID特性。如果不需要事务处理,使用默认存储引擎MyISAM是比较明智的。并且不要尝试同时使用这两个存储引擎。思考一下:在一个事务处理中,一些数据表使用InnoDB,而其余的使用MyISAM.结果呢?整个subject将被取消,只有那些在事务处理中的被带回到原始状态,其余的被提交的数据转存,这将导致整个数据库的冲突。然而存在一个简单的方法可以同时利用两个存储引擎的优势。目前大多数MySQL套件中包括InnoDB、编译器和链表,但如果你选择MyISAM,你仍然可以单独下载InnoDB,并把它作为一个插件。很简单的方法,不是吗?

2.计数问题如果数据表采用的存储引擎支持事务处理(如InnoDB),你就不应使用COUNT(*)计算数据表中的行数。这是因为在产品类数据库使用COUNT(*),最多返回一个近似值,因为在某个特定时间,总有一些事务处理正在运行。如果使用COUNT(*)显然会产生bug,出现这种错误结果。

3.反复测试查询查询最棘手的问题并不是无论怎样小心总会出现错误,并导致bug出现。恰恰相反,问题是在大多数情况下bug出现时,应用程序或数据库已经上线。的确不存在针对该问题切实可行的解决方法,除非将测试样本在应用程序或数据库上运行。任何数据库查询只有经过上千个记录的大量样本测试,才能被认可。

4.避免全表扫描通常情况下,如果MySQL(或者其他关系数据库模型)需要在数据表中搜索或扫描任意特定记录时,就会用到全表扫描。此外,通常最简单的方法是使用索引表,以解决全表扫描引起的低效能问题。然而,正如我们在随后的问题中看到的,这存在错误部分。

5.使用“EXPLAIN”进行查询当需要调试时,EXPLAIN是一个很好的命令,下面将对EXPLAIN进行深入探讨。

mysql数据库优化的思路是什么

2022-11-22
数据库的笔记mysql,数据库管理系统笔记

2022-11-24
mysql数据库的优化思路(mysql语句的优化)

2022-11-13
mysql数据库性能优化是什么(mysql数据库性能优化是什

2022-11-11
mysql数据库完整笔记(mysql数据库数据)

2022-11-13
mysql数据库开发笔记(mysql数据库应用案例教程电子版

2022-11-14
mysql数据库优化的一些笔记(MySQL数据库的优化)

2022-11-13
mysql数据库番外篇之(MySQL数据库笔记)

2022-11-16
mysql优化数据库方法(mysql 数据库优化)

2022-11-14
mysql数据库笔记1000行,sql数据库查询最后1000

2022-11-23
mysql数据库语句优化(mysql 语句优化)

2022-11-10
mysql数据库的sql优化(mysql数据库优化方法)

2022-11-14
mysql数据库优化策略(mysql数据库优化方案)

2022-11-14
怎么进行mysql数据库优化,怎么进行mysql数据库优化设

2022-11-17
mysql数据库探路,MySql是什么类型的数据库

2022-11-18
关于mysql数据库的优化,mysql数据库优化的几种方法

2022-11-18
如何进行mysql数据库的优化(mysql数据库优化及sql

2022-11-11
mysql在数据库级别进行优化,mysql数据库性能优化详解

本文目录一览: 1、mysql 优化包括哪些内容? 2、mysql数据库如何优化?谁能给出点具体的解决方案? 3、超详细MySQL数据库优化 4、MySQL数据库性能优化之分区分表分库 mysql 优

2023-12-08
mysql数据库优化总结2(mysql数据库性能优化详解)

2022-11-16
mysql数据库调优pdf(mysql数据库优化有什么方法)

2022-11-12