精华内容
下载资源
问答
  • 数据库调优

    2017-12-03 18:15:31
    数据库调优
  • 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 ...

    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.
    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.
    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调优数据库调优

    展开全文
  • 创建数据库 create database Test on(name =N'Test',filename=N'e:\test\test.mdf',size = 3MB,filegrowth = 1MB)log on(name = N'Test_log',filename = N'e:\test\test_log.ldf',size = 1MB,filegrowth = 1MB) go ...
  • Mysql数据库调优

    2020-10-03 20:38:21
    数据库调优,慢查询语句,Myisam与InnoDb区别,索引的查询原理, 执行计划,profiling,索引优化,索引覆盖
  • mysql数据库调优示例

    千次阅读 2020-11-26 20:34:02
    数据库调优

    目录

     

    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客户端调优

    展开全文

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 10,732
精华内容 4,292
关键字:

数据库调优