sql调优数据库调优


Database tuning is

nothing but a set of activities that is required for the purpose of optimization

of the overall performances by the database. During the database performance tuning, the developers need to know few

important things regarding the coding. Let us now discuss about those few

important tips.



Image Source

Statistics Of Database: The database statistics contain all the

information regarding the indexes and their relation with the distribution. The

developer needs to optimize the information to decide the query path which is

the least expensive. Now, let’s go

through the following query to understand the statistics in a proper way.

SELECT *

FROM Client 

WHERE city =”Mexico City”

AND phone = +52 211 4650

Therefore you can only

use a single index for each table. The optimizer can run the query much faster

if the Idx phone numbers are used. After updating the statistics the database

can be informed about the data distribution and can rightly select the index to

run the right query.
Also Read: What is PHP’s MySQLnd and How It Performs Easy Read/Write Splitting?

Creating Optimized Index: You need to create a master key for each table

and set the key as the Enterprise Manager. You must be careful about the number

of indexes as you cannot work with a single index but a large number of indexes

will be responsible for a sluggish performance in the DML (INSERT, UPDATE,

DELETE) queries. Therefore, you are required to estimate the number of indexes

needed for a particular field.

For example, if you

select the Idx city index and look out for all the clients in the Mexico City,

you may end up with thousands of rows and columns. In this case, the index

optimization will not be a good idea as there can be unlimited number of

outcome for indexes.

There can be 2 indexes

which can be significantly used.

Composite Index: The index which include more than one field and

is responsible for multiple tasks can be considered as the composite index.

When you need to run the queries in the multiple fields, the composite index

can be very beneficial for you.

Clustered Index:  A table

may include the physical order of data that can be determined by the clustered

index. Each table can include only a single clustered index. The process is

quite similar to a telephone directory where the data can be arranged according

to the last name. When the range of values is being searched, the clustered

index can be essential in the procedure. Oracle

calls it with a special name which is Index-Organized Table while the SQL

server and Informix call it as Clustered Index itself.

Avoiding The Functions On RHS: When the operator uses the SQL queries, they

may end up using on the extreme right hand side and may lose some important

data in the process.

For example:

SELECT *

FROM Client 

WHERE Year (AccountStartedOn) = 2009

AND Month (AccountStartedOn)= 

3

Here you can observe

that though the AccountStartedOn has an index, but when the query changes

during the process, the index cannot be used optimally.

So, to increase the

efficiency and speed, the query can be written somewhat like the following.

SELECT *

FROM Client 

WHERE AccountStartedOn between 3/1/ 2009

AND 3/30/2009

Expected Growth can Be Pre-determined: As the indexes have a negative impact on all the

DML queries, the effect can be minimized if a specific value of the fill factor

can be mentioned during the creation of the indexes. It is also observed that

during the creation of the indexes the data is being saved in the hard disk on

its own. If the rows and the columns of the indexes increase, automatically the

database storage space needs to be reorganized according to the number of rows

and columns.If you can presume the increased number of the indexes, you can

easily assume the expected growth of the indexes naturally.

State The Optimizer Hints In SELECT: The optimizer can easily choose the query based

on the statistics which is specifically table based.  If you can mention the index name in the SELECT

option, the process can be smoother e.g.

SELECT *

FROM client 

WITH (INDEX ( (IdxPhone))

WHERE city = Mexico City 

AND phone = +52 211 4650

You can note the ‘With’

value mentioned after the ‘From’ value in this example. It can only be used in

the MS SQL server. Different database can use different syntaxes for mentioning

different values.

Using The EXPLAIN Value: During the

performance tuning of the SQL queries, the EXPLAIN value can be used for

optimization.

For example:

In Oracle it can

be used as EXPLAIN PLAN FOR  >YOUR

QUERY< 

In Informix it can

be used as SET EXPLAIN 

In Sybase ASE it

can be mentioned as SET SHOWPLAN_ALL ON >YOUR QUERY< 

Foreign Key Obstructions Can Be

Avoided :  The

constraints that can be faced due to the foreign keys can easily be controlled.

The data integrity formulas can be altered and converted towards the

application layers. As the relational database management systems have

different system tables with the database designs, they may contain few Meta

data information regarding the database users. In this case, the database

itself is the client and so can be used during the creation of the indexes.

Input-Output Operations : When both the Input-Output operations

can be observed in the database management , the performance can be expected

to  be more efficient. When multiple data

comes into the scene, it can definitely be split into different hard disks for

serving different purposes.

Choosing Limited Data : If the developer can use limited

data , the performance of the query will be much better. The filtering process

can be taken care of in the end of the server instead of the client. This will

definitely optimize the data and will not take much data space.

Skip The Index Before Inserting Data

:  If you can skip the index before inserting a

large amount of data, there can be an optimum use of it. You can recreate the

index after the loading process is over.

Therefore, the database

performance tuning can be practiced by the developers with the right coding in

the right places.

Author Bio

George Watson is a

well known database expert who is specializing in the performance tuning since

10 years. In this article, he is sharing a few of his valuable suggestions to

the developers for using the right code for performance tuning. For more information you can opt

for the remote dba services – remotedba.com.



Database tuning is

nothing but a set of activities that is required for the purpose of optimization

of the overall performances by the database. During the database performance tuning, the developers need to know few

important things regarding the coding. Let us now discuss about those few

important tips.

 

Image Source 

Statistics Of Database:  The database statistics contain all the

information regarding the indexes and their relation with the distribution. The

developer needs to optimize the information to decide the query path which is

the least expensive. Now, let’s go

through the following query to understand the statistics in a proper way.

SELECT *

FROM Client 

WHERE  city =”Mexico City”

AND  phone = +52 211 4650

Therefore you can only

use a single index for each table. The optimizer can run the query much faster

if the Idx phone numbers are used. After updating the statistics the database

can be informed about the data distribution and can rightly select the index to

run the right query.
 Also Read: What is PHP’s MySQLnd and How It Performs Easy Read/Write Splitting? 


Creating Optimized Index:  You need to create a master key for each table

and set the key as the Enterprise Manager. You must be careful about the number

of indexes as you cannot work with a single index but a large number of indexes

will be responsible for a sluggish performance in the DML (INSERT, UPDATE,

DELETE) queries. Therefore, you are required to estimate the number of indexes

needed for a particular field.

For example, if you

select the Idx city index and look out for all the clients in the Mexico City,

you may end up with thousands of rows and columns. In this case, the index

optimization will not be a good idea as there can be unlimited number of

outcome for indexes.

There can be 2 indexes

which can be significantly used.

Composite Index:  The index which include more than one field and

is responsible for multiple tasks can be considered as the composite index.

When you need to run the queries in the multiple fields, the composite index

can be very beneficial for you.

Clustered Index:  A table

may include the physical order of data that can be determined by the clustered

index. Each table can include only a single clustered index. The process is

quite similar to a telephone directory where the data can be arranged according

to the last name. When the range of values is being searched, the clustered

index can be essential in the procedure. Oracle

calls it with a special name which is Index-Organized Table while the SQL

server and Informix call it as Clustered Index itself.

Avoiding The Functions On RHS:  When the operator uses the SQL queries, they

may end up using on the extreme right hand side and may lose some important

data in the process.

For example:

SELECT *

FROM Client 

WHERE  Year (AccountStartedOn) = 2009

AND Month (AccountStartedOn)= 

3

Here you can observe

that though the AccountStartedOn has an index, but when the query changes

during the process, the index cannot be used optimally.

So, to increase the

efficiency and speed, the query can be written somewhat like the following.

SELECT *

FROM  Client 

WHERE AccountStartedOn between 3/1/ 2009

AND 3/30/2009

Expected Growth can Be Pre-determined:  As the indexes have a negative impact on all the

DML queries, the effect can be minimized if a specific value of the fill factor

can be mentioned during the creation of the indexes. It is also observed that

during the creation of the indexes the data is being saved in the hard disk on

its own. If the rows and the columns of the indexes increase, automatically the

database storage space needs to be reorganized according to the number of rows

and columns.If you can presume the increased number of the indexes, you can

easily assume the expected growth of the indexes naturally.

State The Optimizer Hints In SELECT:  The optimizer can easily choose the query based

on the statistics which is specifically table based.  If you can mention the index name in the SELECT

option, the process can be smoother e.g.

SELECT *

FROM client 

WITH  (INDEX ( (IdxPhone))

WHERE  city = Mexico City 

AND  phone = +52 211 4650

You can note the ‘With’

value mentioned after the ‘From’ value in this example. It can only be used in

the MS SQL server. Different database can use different syntaxes for mentioning

different values.

Using The EXPLAIN Value:  During the

performance tuning of the SQL queries, the EXPLAIN value can be used for

optimization.

For example:

In Oracle it can

be used as EXPLAIN PLAN FOR  >YOUR

QUERY< 

In Informix it can

be used as SET EXPLAIN 

In Sybase ASE it

can be mentioned as SET SHOWPLAN_ALL ON >YOUR QUERY< 

Foreign Key Obstructions Can Be

Avoided :  The

constraints that can be faced due to the foreign keys can easily be controlled.

The data integrity formulas can be altered and converted towards the

application layers. As the relational database management systems have

different system tables with the database designs, they may contain few Meta

data information regarding the database users. In this case, the database

itself is the client and so can be used during the creation of the indexes.

Input-Output Operations :  When both the Input-Output operations

can be observed in the database management , the performance can be expected

to  be more efficient. When multiple data

comes into the scene, it can definitely be split into different hard disks for

serving different purposes.

Choosing Limited Data :  If the developer can use limited

data , the performance of the query will be much better. The filtering process

can be taken care of in the end of the server instead of the client. This will

definitely optimize the data and will not take much data space.

Skip The Index Before Inserting Data

:   If you can skip the index before inserting a

large amount of data, there can be an optimum use of it. You can recreate the

index after the loading process is over.

Therefore, the database

performance tuning can be practiced by the developers with the right coding in

the right places.

Author Bio 

George Watson is a

well known database expert who is specializing in the performance tuning since

10 years. In this article, he is sharing a few of his valuable suggestions to

the developers for using the right code for performance tuning. For more information you can opt

for the remote dba services – remotedba.com . 

翻译自: https://www.thecrazyprogrammer.com/2014/12/database-tuning.html
sql调优数据库调优

目录

 

1、mysql服务器调优

1.1 查询缓存query_cache优化

1.2 使用Explain查看Select语句的执行计划    

1.3 当只需要单行数据返回时请使用Limit 1

1.4  为经常作为查询条件的字段建立索引

1.5  Join边表查询的字段建立索引

1.6 避免 Select  * 

1.7 为每张表设置一个非业务ID字段主键 

1.8 分析表的字段获取建议

1.9 尽可能不要使用Null字段

1.10 把IP字段设置为UnsignedInt

1.11 固定长度的表会更快

1.12 垂直分表

2、mysql客户端调优

1、mysql服务器调优

1.1 查询缓存query_cache优化

设置query_cache_type=on query_caches_size=20M并重启mysql服务





命中率：Qcache_hits/(Qcache_hits+Qcache_inserts)=0.83

1.2 使用Explain查看Select语句的执行计划

    

需要熟悉下面输出的各选项的详细释义。



 

1.3 当只需要单行数据返回时请使用Limit 1

       当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去 fetch 游标，或是你也许会去检查返回的记录数。在这种情况下，

加上 LIMIT 1 可以增加性能。这样一样， MySQL 数据库引擎在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

1.4  为经常作为查询条件的字段建立索引

     索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。

1.5  Join边表查询的字段建立索引

       如果你的应用程序有很多 JOIN 查询，你应该确认两个表中 Join 的字段是被建过索引的。这样， MySQL 内部会启动为你优化 Join 的 SQL 语句的机制。而且，这些被用来 Join 的字段，应该是相同的类型的。例如：如果你要把

DECIMAL 字段和一个 INT 字段 Join 在一起， MySQL 就无法使用它们的索引。对于那些 STRING 类型，还需要有相同的字符集才行。 (两个表的字符集有可能不一样)

1.6 避免 Select  * 

     从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和 WEB 服务器是两台独立的服务器的话，这还会增加网络传输的负载。

1.7 为每张表设置一个非业务ID字段主键 

     为每张表设置一个非业务ID字段，Number类型主键比Varchar类型的主键性能更好；此外，对于集群与分区表，主键的设置就更为重要了。

1.8 分析表的字段获取建议

     select * from goods procedure analyse(10);



1.9 尽可能不要使用Null字段

          Null字段的比较会导致索引失效。不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进行比较的时

候，你的程序会更复杂。 当然，这里并不是说你就不能使用 NULL 了，现实情况是很复杂的，依然会有些情况下，你需要使用 NULL 值。

1.10 把IP字段设置为UnsignedInt

      很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的 IP 而不是

整形的 IP。如果你用整形来存放，只需要 4 个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的 WHERE 条

件： IP between ip1 and ip2。我们必需要使用 UNSIGNED INT，因为 IP 地址会使用整个 32 位的无符号整形。而你的查询，你可以使用 INET_ATON() 来把一个字符串 IP 转成一个整形，

并使用 INET_NTOA() 把一个整形转成一个字符串 IP。在 PHP 中，也有这样的函数 ip2long() 和 long2ip()。



1.11 固定长度的表会更快

        如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static”或 “fixed-length”。 例如，表中没有如下类型的字段： VARCHAR， TEXT，

BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样， MySQL 引擎会用另一种方法来处理。

        固定长度的表会提高性能，因为 MySQL 搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，

那么，每一次要找下一条的话，需要程序找到主键。使用“垂直分割”技术(见下一条)，你可以分割你的表成为两个一个是定

长的，一个则是不定长的。



1.12 垂直分表

      “垂直分区”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。 (以前，在银行做过项目，

见过一张表有 100 多个字段，很恐怖)。

     示例一：在 Users 表中有一个字段是家庭地址，这个字段是可选字段，相

比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是

改写这个字段。那么，为什么不把他放到另外一张表中呢? 这样会让你的表有

更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户

ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性

能。

      示例二： 你有一个叫 “last_login” 的字段，它会在每次用户登录时被

更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个

字段放到另一个表中，这样就不会影响你对用户 ID，用户名，用户角色的不停

地读取了，因为查询缓存会帮你增加很多性能。

     另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性

地去 Join 他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数

级的下降。

 

1.13  减少数据库事务的执行时间

       如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT操作，你需要非常小心，要避免你的操作让你的整个网站停止响应。因为这两个操作

是会锁表的，表一锁住了，别的操作都进不来了。

       Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。

      如果你把你的表锁上一段时间，比如 30 秒钟，那么对于一个有很高访问量的站点来说，这 30 秒所积累的访问进程/线程，数据库链接，打开的文件数，

可能不仅仅会让你泊 WEB 服务 Crash，还可能会让你的整台服务器马上掛了。

     所以，如果你有一个大的处理，你定你一定把其拆分，使用 LIMIT 条件是

一个好的方法。下面是一个示例：


 

1.14 选择合适的Mysql存储引擎

 

 

 

 

 

 

 

 

 

 

 

 

 

2、mysql客户端调优