精华内容
下载资源
问答
  • MySQL优化建议

    千次阅读 热门讨论 2020-12-23 11:50:20
    MySQL优化的本质: 1. 结合MySQL数据库的主要特性(数据存储与数据查询),使得数据存储占用空间更小,更新、查询速度更快,并发程度更高,使得服务器资源利用率更高。 2. 尤其是对于大数据量、高并发的业务场景,...

    仅个人总结,欢迎一起交流!

    MySQL优化的本质:

    1. 结合MySQL数据库的主要特性(数据存储与数据查询),使得数据存储占用空间更小,更新、查询速度更快,并发程度更高,使得服务器资源利用率更高。

    2. 尤其是对于大数据量、高并发的业务场景,避免因不合理的设计拖垮数据库,造成数据库宕机、数据丢失、业务无法正常进行等问题,保证服务的可用性。

    以下为本人总结的部分优化建议:

    • 选择InnoDB引擎。InnoDB引擎支持事务、行级锁、并发性能更好,CPU及内存缓存页优化使得资源利用率更高。

    • 使用UTF8字符集,UTF-8MB4。无需转码,不存在乱码的风险,更节省空间。

    • 减少存储过程、视图、触发器等使用,尽量将计算逻辑设计运用至业务代码层面,避免因耗时计算影响到数据库的并发,严重时拖垮数据库。

    • 使用自增主键。Innodb是基于B+树的索引组织表,使用自增主键,数据行写入可以提高插入性能,可以避免页分裂,减少表碎片提升空间和内存的使用。

    • 禁止使用外键,外键导致表和表之间的耦合。更新和删除操作涉及关联的表,极大地影响sql的性能,甚至可能造成死锁。

    • 禁止使用TEXT、BLOB类型。更多的磁盘和内存空间将被浪费,且大字段查询将会清理掉热点数据的缓存,导致缓存命中率急剧下降并影响数据库性能。如有必要,创建另一个表记录,将热点数据与大字段数据分离。

    • 适当的建立索引,避免全表扫描。

    • 不要过多的建立索引,建议控制在5个以内。索引的本质是对数据顺序的记录,占用存储空间,不宜过多。可使用EXPLAIN来查看了解索引命中情况。

    • 索引的建立原则:为常用于where或orderby语句后的字段建立索引,但对于数据值变化少(区分度不大)的情况不适用,如“性别”字段,一般值为“男/女/未知”等,建立这种索引并不能加快查询速度,却浪费了磁盘空间。

    • 更新十分频繁的字段上不宜建立索引。更新操作会变更B+树,重建索引,这个过程十分耗性能。

    • 联合索引的建立要满足“最左匹配”原则,将命中率更高的字段建立在最前面。

    • 列值设置默认值,禁止null值,以防破坏索引,影响查询效率;减少额外空间占用。

    • 不在where语句后使用函数或者表达式计算,会造成索引失效。

    • 不使用NOT IN和<>操作,%开头的模糊查询,避免全表扫描;

    • 避免使用属性隐式转换。如phone='123456789’为字符串不写成phone=123456789。

    • 避免SELECT *,从数据库里读出的数据越多,查询就会变得越慢。另外,如果数据库服务器和WEB服务器是两台独立的服务器,这还会增加网络传输的负载。

    • 尽量建立覆盖索引,避免回表查询。

    • 唯一/普通索引的选择。唯一索引需要唯一性检查,不能利用 change buffer特性。需要频繁从磁盘读取数据,涉及随机 IO 的访问,效率变低。所以能使用普通索引的场景尽量不使用唯一索引。

    • 更新条件列要使用索引,避免无法通过索引键加锁而升级为表级锁。

    • varchar字段过长时考虑使用前缀索引。

    • 禁止对大型表使用JOIN查询或子查询。会生成临时表,消耗内存和CPU,极大地影响数据库性能。关联查询可在业务代码层拆分为多次单表查询。

    • 避免存储大文件。大文件可存至专门的文件系统,如OSS。

    • 根据业务需要合理地确定字段类型与长度,避免空间浪费。

    • 大事务尽量拆成多个小事务,减少锁记录和时间。

    • 拆分大的 DELETE 或 INSERT 语句,避免较长时间的锁表,防止应用被crash。

    • 最长事务代码块尽量放在程序的最后执行。

    • 尽量减少基于范围的数据检索过滤条件更新,避免间隙锁带来的影响而锁定了不该锁定的记录。

    • 注意间隙锁,批量更新&删除操作尽量避免并发,以免造成意外的死锁。

    • 数据量大的业务系统,考虑分库分表,按业务系统的划分垂直分库,单表数据量过大考虑分表(水平/垂直分表)

    最后,送大家一份免费MySQL学习资料,立即前往>>>。学无止境,愿大家都能够少走弯路。

    展开全文
  • Mysql优化建议

    千次阅读 2016-02-23 23:15:55
    今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。...这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库。希望下面的这些优化技巧对你有用。
    今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能。这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库。希望下面的这些优化技巧对你有用。
    1. 为查询缓存优化你的查询
    大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了。
    这里最主要的问题是,对于程序员来说,这个事情是很容易被忽略的。因为,我们某些查询语句会让MySQL不使用缓存。请看下面的示例:
    // 查询缓存不开启
    $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
    // 开启查询缓存
    $today = date("Y-m-d");
    $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");
    上面两条SQL语句的差别就是 CURDATE() ,MySQL的查询缓存对这个函数不起作用。所以,像 NOW() 和 RAND()或是其它的诸如此类的SQL函数都不会开启查询缓存,因为这些函数的返回是会不定的易变的。所以,你所需要的就是用一个变量来代替MySQL的函数,从而开启缓存。
    2. EXPLAIN 你的 SELECT 查询
    使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。
    EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的……等等,等等。
    挑一个你的SELECT语句(推荐挑选那个最复杂的,有多表联接的),把关键字EXPLAIN加到前面。你可以使用phpmyadmin来做这个事。然后,你会看到一张表格。下面的这个示例中,我们忘记加上了group_id索引,并且有表联接:
    当我们为 group_id 字段加上索引后:
    我们可以看到,前一个结果显示搜索了 7883 行,而后一个只是搜索了两个表的 9 和 16行。查看rows列可以让我们找到潜在的性能问题。
    3. 当只要一行数据时使用 LIMIT 1
    当你查询表的有些时候,你已经知道结果只会有一条结果,但因为你可能需要去fetch游标,或是你也许会去检查返回的记录数。
    在这种情况下,加上 LIMIT 1可以增加性能。这样一样,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。
    下面的示例,只是为了找一下是否有“中国”的用户,很明显,后面的会比前面的更有效率。(请注意,第一条中是Select*,第二条是Select 1)
    // 没有效率的:
    $r = mysql_query("SELECT * FROM user WHERE country = 'China'");
    if (mysql_num_rows($r) > 0) {
    // ...
    }

    // 有效率的:
    $r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
    if (mysql_num_rows($r) > 0) {
    // ...
    }
    4. 为搜索字段建索引
    索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么,请为其建立索引吧。
    从上图你可以看到那个搜索字串 “last_name LIKE ‘a%'”,一个是建了索引,一个是没有索引,性能差了4倍左右。
    另外,你应该也需要知道什么样的搜索是不能使用正常的索引的。例如,当你需要在一篇大的文章中搜索一个词时,如: “WHEREpost_content LIKE ‘%apple%'”,索引可能是没有意义的。你可能需要使用MySQL全文索引或是自己做一个索引(比如说:搜索关键词或是Tag什么的)
    5. 在Join表的时候使用相当类型的例,并将其索引
    如果你的应用程序有很多 JOIN查询,你应该确认两个表中Join的字段是被建过索引的。这样,MySQL内部会启动为你优化Join的SQL语句的机制。
    而且,这些被用来Join的字段,应该是相同的类型的。例如:如果你要把 DECIMAL 字段和一个 INT字段Join在一起,MySQL就无法使用它们的索引。对于那些STRING类型,还需要有相同的字符集才行。(两个表的字符集有可能不一样)
    // 在state中查找company
    $r = mysql_query("SELECT company_name FROM users
    LEFT JOIN companies ON (users.state = companies.state)
    WHERE users.id = $user_id");

    // 两个 state 字段应该是被建过索引的,而且应该是相当的类型,相同的字符集。
    6. 千万不要 ORDER BY RAND()
    想打乱返回的数据行?随机挑一个数据?真不知道谁发明了这种用法,但很多新手很喜欢这样用。但你确不了解这样做有多么可怕的性能问题。
    如果你真的想把返回的数据行打乱了,你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是:MySQL会不得不去执行RAND()函数(很耗CPU时间),而且这是为了每一行记录去记行,然后再对其排序。就算是你用了Limit1也无济于事(因为要排序)
    下面的示例是随机挑一条记录


    // 千万不要这样做:
    $r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

    // 这要会更好:
    $r = mysql_query("SELECT count(*) FROM user");
    $d = mysql_fetch_row($r);
    $rand = mt_rand(0,$d[0] - 1);

    $r = mysql_query("SELECT username FROM user LIMIT $rand, 1");
    7. 避免 SELECT *
    从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和WEB服务器是两台独立的服务器的话,这还会增加网络传输的负载。
    所以,你应该养成一个需要什么就取什么的好的习惯。


    // 不推荐
    $r = mysql_query("SELECT * FROM user WHERE user_id = 1");
    $d = mysql_fetch_assoc($r);
    echo "Welcome {$d['username']}";

    // 推荐
    $r = mysql_query("SELECT username FROM user WHERE user_id = 1");
    $d = mysql_fetch_assoc($r);
    echo "Welcome {$d['username']}";
    8. 永远为每张表设置一个ID
    我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的AUTO_INCREMENT标志。
    就算是你 users 表有一个主键叫 “email”的字段,你也别让它成为主键。使用 VARCHAR类型来当主键会使用得性能下降。另外,在你的程序中,你应该使用表的ID来构造你的数据结构。
    而且,在MySQL数据引擎下,还有一些操作需要使用主键,在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区……
    在这里,只有一个情况是例外,那就是“关联表”的“外键”,也就是说,这个表的主键,通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如:有一个“学生表”有学生的ID,有一个“课程表”有课程ID,那么,“成绩表”就是“关联表”了,其关联了学生表和课程表,在成绩表中,学生ID和课程ID叫“外键”其共同组成主键。
    9. 使用 ENUM 而不是 VARCHAR
    ENUM 类型是非常快和紧凑的。在实际上,其保存的是TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。
    如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,你知道这些字段的取值是有限而且固定的,那么,你应该使用ENUM 而不是 VARCHAR。
    MySQL也有一个“建议”(见第十条)告诉你怎么去重新组织你的表结构。当你有一个 VARCHAR 字段时,这个建议会告诉你把其改成ENUM 类型。使用 PROCEDURE ANALYSE() 你可以得到相关的建议。
    10. 从 PROCEDURE ANALYSE() 取得建议
    PROCEDURE ANALYSE() 会让 MySQL帮你去分析你的字段和其实际的数据,并会给你一些有用的建议。只有表中有实际的数据,这些建议才会变得有用,因为要做一些大的决定是需要有数据作为基础的。
    例如,如果你创建了一个 INT 字段作为你的主键,然而并没有太多的数据,那么,PROCEDUREANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是你使用了一个 VARCHAR字段,因为数据不多,你可能会得到一个让你把它改成 ENUM 的建议。这些建议,都是可能因为数据不够多,所以决策做得就不够准。
    在phpmyadmin里,你可以在查看表时,点击 “Propose table structure” 来查看这些建议

    一定要注意,这些只是建议,只有当你的表里的数据越来越多时,这些建议才会变得准确。一定要记住,你才是最终做决定的人。


    11. 尽可能的使用 NOT NULL
    除非你有一个很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL。这看起来好像有点争议,请往下看。
    首先,问问你自己“Empty”和“NULL”有多大的区别(如果是INT,那就是0和NULL)?如果你觉得它们之间没有什么区别,那么你就不要使用NULL。(你知道吗?在Oracle 里,NULL 和 Empty 的字符串是一样的!)
    不要以为 NULL 不需要空间,其需要额外的空间,并且,在你进行比较的时候,你的程序会更复杂。当然,这里并不是说你就不能使用NULL了,现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。
    下面摘自MySQL自己的文档:
    1
    “NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”
    12. Prepared Statements
    Prepared Statements很像存储过程,是一种运行在后台的SQL语句集合,我们可以从使用 preparedstatements 获得很多好处,无论是性能问题还是安全问题。
    Prepared Statements可以检查一些你绑定好的变量,这样可以保护你的程序不会受到“SQL注入式”攻击。当然,你也可以手动地检查你的这些变量,然而,手动的检查容易出问题,而且很经常会被程序员忘了。当我们使用一些framework或是ORM的时候,这样的问题会好一些。
    在性能方面,当一个相同的查询被使用多次的时候,这会为你带来可观的性能优势。你可以给这些PreparedStatements定义一些参数,而MySQL只会解析一次。
    虽然最新版本的MySQL在传输Prepared Statements是使用二进制形势,所以这会使得网络传输非常有效率。
    当然,也有一些情况下,我们需要避免使用Prepared Statements,因为其不支持查询缓存。但据说版本5.1后支持了。
    在PHP中要使用prepared statements,你可以查看其使用手册:mysqli 扩展 或是使用数据库抽象层,如:PDO.
    // 创建 prepared statement
    if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {

    // 绑定参数
    $stmt->bind_param("s", $state);

    // 执行
    $stmt->execute();

    // 绑定结果
    $stmt->bind_result($username);

    // 移动游标
    $stmt->fetch();

    printf("%s is from %s\n", $username, $state);

    $stmt->close();
    }


    13. 无缓冲的查询
    正常的情况下,当你在当你在你的脚本中执行一个SQL语句的时候,你的程序会停在那里直到没这个SQL语句返回,然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。
    关于这个事情,在PHP的文档中有一个非常不错的说明: mysql_unbuffered_query() 函数:
    1
    “mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don’t have to wait until the complete SQL query has been performed.”
    上面那句话翻译过来是说,mysql_unbuffered_query()发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存,尤其是那些会产生大量结果的查询语句,并且,你不需要等到所有的结果都返回,只需要第一行数据返回的时候,你就可以开始马上开始工作于查询结果了。
    然而,这会有一些限制。因为你要么把所有行都读走,或是你要在进行下一次的查询前调用 mysql_free_result()清除结果。而且, mysql_num_rows() 或 mysql_data_seek()将无法使用。所以,是否使用无缓冲的查询你需要仔细考虑。
    14. 把IP地址存成 UNSIGNED INT
    很多程序员都会创建一个 VARCHAR(15)字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放,只需要4个字节,并且你可以有定长的字段。而且,这会为你带来查询上的优势,尤其是当你需要使用这样的WHERE条件:IPbetween ip1 and ip2。
    我们必需要使用UNSIGNED INT,因为 IP地址会使用整个32位的无符号整形。
    而你的查询,你可以使用 INET_ATON() 来把一个字符串IP转成一个整形,并使用 INET_NTOA()把一个整形转成一个字符串IP。在PHP中,也有这样的函数 ip2long() 和 long2ip()。

    展开全文
  • 在这里,我们分享二十一条在实践中经常需要注意到的mysql优化建议。 在当今信息化的时代,数据库的大数据量操作越来越成为一些应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才...
    使用mysql数据库的用户,都在不断的面临着数据库里的数据越来越多,而不得不去考虑mysql数据库优化的方面问题了。在这里,我们分享二十一条在实践中经常需要注意到的mysql优化建议。

    在当今信息化的时代,数据库的大数据量操作越来越成为一些应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能。这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库(mysql数据库语句的优化:带你深入了解MySQL语句优化的基本原则)。希望下面的这些优化技巧对你有用。

    1.为查询缓存优化你的查询

      大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了。

      这里最主要的问题是,对于程序员来说,这个事情是很容易被忽略的。因为,我们某些查询语句会让MySQL不使用缓存。请看下面的示例:

      上面两条SQL语句的差别就是CURDATE(),MySQL的查询缓存对这个函数不起作用。所以,像NOW()和RAND()或是其它的诸如此类的SQL函数都不会开启查询缓存,因为这些函数的返回是会不定的易变的。所以,你所需要的就是用一个变量来代替MySQL的函数,从而开启缓存。

    2.EXPLAIN你的SELECT查询

      使用EXPLAIN关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

      EXPLAIN的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的……等等,等等。

      挑一个你的SELECT语句(推荐挑选那个最复杂的,有多表联接的),把关键字EXPLAIN加到前面。你可以使用phpmyadmin来做这个事。然后,你会看到一张表格。下面的这个示例中,我们忘记加上了group_id索引,并且有表联接:ww.phperz.com

     

     

    MySQL性能优化的21条最佳经验

     

    当我们为group_id字段加上索引后:

     

    MySQL性能优化的21条最佳经验

     


      我们可以看到,前一个结果显示搜索了7883行,而后一个只是搜索了两个表的9和16行。查看rows列可以让我们找到潜在的性能问题。

     

    3.当只要一行数据时使用LIMIT1

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

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

      下面的示例,只是为了找一下是否有“中国”的用户,很明显,后面的会比前面的更有效率。(请注意,第一条中是Select *,第二条是Select 1)

     

     

    MySQL性能优化的21条最佳经验

     


    4.为搜索字段建索引

     

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

     

     

    MySQL性能优化的21条最佳经验

     


      从上图你可以看到那个搜索字串 “last_name LIKE ‘a%’”,一个是建了索引,一个是没有索引,性能差了4倍左右。

     

      另外,你应该也需要知道什么样的搜索是不能使用正常的索引的。例如,当你需要在一篇大的文章中搜索一个词时,如: “WHERE post_content LIKE ‘%apple%’”,索引可能是没有意义的。你可能需要使用MySQL全文索引或是自己做一个索引(比如说:搜索关键词或是Tag什么的)

    5.在Join表的时候使用相当类型的例,并将其索引

      如果你的应用程序有很多JOIN查询,你应该确认两个表中Join的字段是被建过索引的。这样,MySQL内部会启动为你优化Join的SQL语句的机制。

      而且,这些被用来Join的字段,应该是相同的类型的。例如:如果你要把DECIMAL字段和一个INT字段Join在一起,MySQL就无法使用它们的索引。对于那些STRING类型,还需要有相同的字符集才行。(两个表的字符集有可能不一样)程序员站

    6.千万不要ORDER BY RAND()

      想打乱返回的数据行?随机挑一个数据?真不知道谁发明了这种用法,但很多新手很喜欢这样用。但你确不了解这样做有多么可怕的性能问题。

      如果你真的想把返回的数据行打乱了,你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是:MySQL会不得不去执行RAND()函数(很耗CPU时间),而且这是为了每一行记录去记行,然后再对其排序。就算是你用了Limit 1也无济于事(因为要排序)

      下面的示例是随机挑一条记录

    7.避免 SELECT *

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

      所以,你应该养成一个需要什么就取什么的好的习惯。

    8.永远为每张表设置一个ID

      我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的(推荐使用UNSIGNED),并设置上自动增加的AUTO_INCREMENT标志。

      就算是你users表有一个主键叫“email”的字段,你也别让它成为主键。使用VARCHAR类型来当主键会使用得性能下降。另外,在你的程序中,你应该使用表的ID来构造你的数据结构。

      而且,在MySQL数据引擎下,还有一些操作需要使用主键,在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区……

      在这里,只有一个情况是例外,那就是“关联表”的“外键”,也就是说,这个表的主键,通过若干个别的表的主键构成。我们把这个情况叫做“外键”。比如:有一个“学生表”有学生的ID,有一个“课程表”有课程ID,那么,“成绩表”就是“关联表”了,其关联了学生表和课程表,在成绩表中,学生ID和课程ID叫“外键”其共同组成主键。www~phperz~com

    9.使用ENUM而不是VARCHAR

      ENUM类型是非常快和紧凑的。在实际上,其保存的是TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。

      如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,你知道这些字段的取值是有限而且固定的,那么,你应该使用ENUM而不是VARCHAR。

      MySQL也有一个“建议”(见第十条)告诉你怎么去重新组织你的表结构。当你有一个VARCHAR字段时,这个建议会告诉你把其改成ENUM类型。使用PROCEDURE ANALYSE() 你可以得到相关的建议。

    10.从PROCEDURE ANALYSE()取得建议p程序员站

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

      例如,如果你创建了一个INT字段作为你的主键,然而并没有太多的数据,那么,PROCEDURE ANALYSE()会建议你把这个字段的类型改成MEDIUMINT。或是你使用了一个VARCHAR字段,因为数据不多,你可能会得到一个让你把它改成ENUM的建议。这些建议,都是可能因为数据不够多,所以决策做得就不够准。

      在phpmyadmin里,你可以在查看表时,点击“Propose table structure”来查看这些建议

     

     

    MySQL性能优化的21条最佳经验

     


      一定要注意,这些只是建议,只有当你的表里的数据越来越多时,这些建议才会变得准确。一定要记住,你才是最终做决定的人。

     

    11.尽可能的使用NOT NULL php程序员站

      除非你有一个很特别的原因去使用NULL值,你应该总是让你的字段保持NOT NULL。这看起来好像有点争议,请往下看。

      首先,问问你自己“Empty”和“NULL”有多大的区别(如果是INT,那就是0和NULL)?如果你觉得它们之间没有什么区别,那么你就不要使用NULL。(你知道吗?在Oracle里,NULL 和 Empty的字符串是一样的!)

      不要以为 NULL 不需要空间,其需要额外的空间,并且,在你进行比较的时候,你的程序会更复杂。当然,这里并不是说你就不能使用NULL了,现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。

      下面摘自MySQL自己的文档:

    12. Prepared Statements

      Prepared Statements很像存储过程,是一种运行在后台的SQL语句集合,我们可以从使用prepared statements获得很多好处,无论是性能问题还是安全问题。

      Prepared Statements可以检查一些你绑定好的变量,这样可以保护你的程序不会受到“SQL注入式”攻击。当然,你也可以手动地检查你的这些变量,然而,手动的检查容易出问题,而且很经常会被程序员忘了。当我们使用一些framework或是ORM的时候,这样的问题会好一些。

      在性能方面,当一个相同的查询被使用多次的时候,这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数,而MySQL只会解析一次。

      虽然最新版本的MySQL在传输Prepared Statements是使用二进制形势,所以这会使得网络传输非常有效率。

      当然,也有一些情况下,我们需要避免使用Prepared Statements,因为其不支持查询缓存。但据说版本5.1后支持了。 php程序员之家

      在PHP中要使用prepared statements,你可以查看其使用手册:mysqli扩展或是使用数据库抽象层,如:PDO.

     

     

    MySQL性能优化的21条最佳经验

     


    13.无缓冲的查询

     

      正常的情况下,当你在当你在你的脚本中执行一个SQL语句的时候,你的程序会停在那里直到没这个SQL语句返回,然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。 ww~phperz~com

      关于这个事情,在PHP的文档中有一个非常不错的说明:mysql_unbuffered_query()函数:

      上面那句话翻译过来是说,mysql_unbuffered_query()发送一个SQL语句到MySQL而并不像mysql_query()一样去自动fethch和缓存结果。这会相当节约很多可观的内存,尤其是那些会产生大量结果的查询语句,并且,你不需要等到所有的结果都返回,只需要第一行数据返回的时候,你就可以开始马上开始工作于查询结果了。

      然而,这会有一些限制。因为你要么把所有行都读走,或是你要在进行下一次的查询前调用 mysql_free_result() 清除结果。而且, mysql_num_rows() 或 mysql_data_seek() 将无法使用。所以,是否使用无缓冲的查询你需要仔细考虑。

    14.把IP地址存成UNSIGNED INT

      很多程序员都会创建一个VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放,只需要4个字节,并且你可以有定长的字段。而且,这会为你带来查询上的优势,尤其是当你需要使用这样的WHERE条件:IP between ip1 and ip2。

      我们必需要使用UNSIGNED INT,因为IP地址会使用整个32位的无符号整形。

      而你的查询,你可以使用 INET_ATON()来把一个字符串IP转成一个整形,并使用INET_NTOA()把一个整形转成一个字符串IP。在PHP中,也有这样的函数 ip2long()和long2ip()。

    15.固定长度的表会更快

      如果表中的所有字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”。 例如,表中没有如下类型的字段: VARCHAR,TEXT,BLOB。只要你包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用另一种方法来处理。

      固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。

      并且,固定长度的表也更容易被缓存和重建。不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间。 php程序员站

      使用“垂直分割”技术(见下一条),你可以分割你的表成为两个一个是定长的,一个则是不定长的。

    16.垂直分割

      “垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。(以前,在银行做过项目,见过一张表有100多个字段,很恐怖)

      示例一:在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢?这样会让你的表有更好


    it动力总结mysql优化:mysql优化是一个不断跟据自己业务需求和数据量的增加量来不断考虑的问题。当然如果你前期能够确定到你的业务数据量,也是可以提前部署好mysql的优化方案的。我们在这里列出的21条优化方案应该只是比较常用的。具体的更多的优化,可能还包括了mysql数据库的分表、数据表结构方面的知识,还有mysql读写分享等。希望大家在不断的学习中,不断提高自己处理问题的能力。
    展开全文
  • Java后端接口、Mysql优化建议与规范

    千次阅读 2018-04-28 11:25:55
    一、后端接口 ...使用基本类型定义变量时,千万注意该变量值可能为null的情况,此时建议使用对应的包装类来定义变量; 避免在同一接口中过多的访问数据库,建议次数控制在3次以内; 避免过多使用static...

    一、后端接口

    • 禁止使用递归;原因:每次递归调用时会向栈中push当前方法的运行状态(现场),而Java栈内存的使用超过限制的大小时,程序会出现栈异常;
    • 避免层级嵌套循环;
    • 注意方法、类文件中的代码量,适度分离;
    • 使用基本类型定义变量时,千万注意该变量值可能为null的情况,此时建议使用对应的包装类来定义变量;
    • 避免在同一接口中过多的访问数据库,建议次数控制在3次以内;
    • 避免过多使用static变量;原因:静态变量和类的生命周期同步;
    • 尽量采用lazy loading的策略,即在需要的时候才开始创建变量和对象等;
    • 将一些需要变动的配置或文案写在属性文件中;
    • 后端接口需要提供必要的校验,不要过于依赖前端校验;
    • SQL语句较长时建议采用Provider拼接,拼接查询Sql时加上” where 1 = 1 “;
    • 提倡异常封装,提高代码可读性和可维护性等;
    • 避免使用硬编码,多使用final定义常变量,提高可读性;

    2、SQL优化

    • 尽量使用简单的SQL,避免多表查询以及在SQL中处理复杂的逻辑;
    • 避免使用SQL语句排序,尽量在程序中进行排序;
    • 查询时避免全表扫描,适度增加索引;
    • 设计表结构时:id、created、updated、version NOT NULL;id AUTO_INCREMENT
    • 在查询时不要对所查询列使用函数或者运算,否则索引无法使用到;
    • 尽量使用GROUP BY替换DISTINCT
    • JOIN时使用小结果集驱动大结果集;
    • 建议使用”临时表”暂存中间结果;
    • 模糊查询(LIKE)时避免在关键词前使用”%”(如:LIKE '%小分期'),否则查询必然是全表扫描;
    • 对查询进行优化,应尽量避免全表扫描,首先应考虑在where 及 order by涉及的列上建立索引。
    • 应尽量避免在 where 子句中使用!=或<>操作符、函数操作、表达式(如:num/2)以及null值判断,否则将引擎放弃使用索引而进行全表扫描。
    • 应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
      select id from t where num=10 or num=20
      可以这样查询:
      select id from t where num=10
      union all
      select id from t where num=20
    • in 、 not in 、or 也要慎用,否则条件超过一定数量会导致索引失效(特别注意:严格禁止in后面使用子查询),如:
      select id from t where num in(1,2,3)
      对于连续的数值,能用 between 就不要用 in 了:
      select id from t where num between 1 and 3
    • 索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insertupdate 的效率,因为 insertupdate 时有可能会重建索引。一个表的索引数最好不要超过6个。
    • 任何地方都要慎重使用 select * from t取出所有列 ,不要返回用不到的任何字段。
    • 适当将复杂查询切分为若干个单表查询或简易查询。如将关联查询分解为若干个单表查询。
    • 老老实实按照规范写SQL,比如字符串一律该加引号就加引号。WHERE username = 1380000000WHERE username = '1380000000'是不同的。后者能正确使用索引,前者不能。
    • 数据库表有version字段的,应当强制不为空。因为baseDao每次更新记录都会在这个字段加1,为空的话会出现异常。
    • where/group by/order by 的条件里,禁止给索引列套上函数,会让索引失效。例如:WHERE date(created) > '2017-01-01' 这样的写法绝对禁止。

    写在最后:以上为实习期总结的后端接口、sql优化的一些建议,由于后期很少写业务代码了,就懒得更新了,欢迎补充…

    展开全文
  • 这里有七点建议能够保证 MySQL 的平稳运行。性能是我们衡量应用的一种方式,而应用性能的一项指标就是用户体验,也就是平时我们常说的:“用户需要等待超过合理的时间,才能获得他们想要的东西吗?”在不同的情况和...
  • mysql参数优化建议

    千次阅读 2015-01-10 09:37:40
    要想提高mysql的性能,首先就是必须对mysql的配置参数进行了解,在了解了mysql的配置参数后,根据自己的项目需要以及运行环境,再做出相应的调整,那么以下这些参数是经过对mysql的官方配置参数说明、网上资料查询,...
  • MySQL优化

    万次阅读 2019-03-12 11:14:28
    MySQL优化方式 表的设计合理化(符合3NF),实际设计中不可能百分百遵守3NF 添加适当索引(index)普通索引,主键索引,唯一索引,全文索引 SQL语句优化 分表技术(水平分割,垂直分割) 读写分离 存储过程 ...
  • MySql优化

    千次阅读 多人点赞 2017-10-15 19:41:48
    了解MySQL优化步骤,指导学习MySQL优化指南。
  • Mysql 优化

    千次阅读 2017-12-15 09:51:36
    MySQL 优化 下面说的优化基于 MySQL 5.6,理论上 5.5 之后的都算适用,具体还是要看官网 服务状态查询 查看当前数据库的状态,常用的有: 查看系统状态:SHOW STATUS;查看刚刚执行 SQL 是否有警告信息:...
  • Mysql性能优化Mysql参数优化Mysql配置优化Mysql参数优化对于不同的网站,及在线量等,以及机器硬件的配置都有关系,优化不可能一次性完成,需要不断的观察以及调试,才有可能的得到最佳的效果 Mysql参数优化...
  • mysql优化工具 MySQL Tuner

    千次阅读 2018-12-14 17:56:14
    MySQL Tuner是一个Perl脚本,它连接到正在运行的MySQL实例,并根据工作负载提供配置建议。理想情况下,MySQL实例应该在运行脚本之前至少运行24小时。实例运行的时间越长,MySQL Tuner给出的建议就越好。 github网址...
  • MySQL优化之——优化

    千次阅读 2015-07-07 21:09:19
    mysql优化是多方面的,原则是减少系统的瓶颈,减少资源的占用,增加系统的反应速度。例如,通过优化文件系统,提高磁盘I/O的读写速度;通过优化操作系统调度策略,提高mysql在高负荷情况下的负载能力;优化表结构、...
  • mysql优化

    万次阅读 2018-08-13 18:42:41
    Mysql优化综合性的问题: A、表的是设计合理化(符合 3范式) B、添加适当的索引(index)[四种:普通索引,主键索引,唯一索引,unique,全文索引] C、分表技术(水平分割,垂直分割) D、读写[写:update/delete/add...
  • MySQL优化技术——20条建议

    万次阅读 2018-10-12 10:06:21
    MySQL优化   数据库操作往往是当今大多数Web应用程序的主要瓶颈。不仅是DBA(数据库管理员)不得不担心这些性能问题。作为程序员,我们需要通过正确地构造表、编写优化的查询和更好的代码来完成我们的工作。在本文...
  • mysql 优化

    千次阅读 2012-07-21 20:47:59
    mysql缓存
  • MySQL:常用的MySQL优化工具

    千次阅读 2020-05-21 10:37:07
    影响数据库性能的常见因素如下: (1)磁盘IO; (2)网卡流量; (3)服务器硬件; (4)SQL查询速度。...针对潜在的问题,给出改进的建议,帮助进行MySQL优化。MySQLTuner支持MySQL / MariaDB / Perc
  • Mysql高性能优化规范建议

    千次阅读 2019-07-30 08:42:26
    Mysql高性能优化规范建议
  • MYSQL优化大法1. SQL优化1.1 查询SQL执行频率1.2 定位低效率执行SQL1.3 explain分析执行计划1.3.1 explain之ID1.3.2 explain之select_type1.3.3 explain之select_table1.3.4 explain之type1.3.5 explain之key1.3.6 ...
  • MySQL优化面试准备

    万次阅读 多人点赞 2018-10-07 20:18:31
    想了很久要不要发一篇关于MySql优化的文章最后还是决定把它写出来。以输出倒逼自己复习与输入。以下内容大都参考了《高性能MySQL》一书也好把一些的章节做一个总结。全文的聊到的三个主要能容是: MySql的特点与...
  • MySQL优化详解

    千次阅读 2016-10-19 16:52:03
    MYSQL优化 MYSQL优化主要分为以下四大方面: 设计:存储引擎,字段类型,范式与逆范式 功能:索引,缓存,分区分表。 架构:主从复制,读写分离,负载均衡。 合理SQL:测试,经验。 一、存储引擎 在创建表的时候我们...
  • MySQL优化之——查询优化

    千次阅读 2019-07-10 17:10:03
    谈谈项目中常用的MySQL优化方法,共19条,具体如下: 1、EXPLAIN 做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。 下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据: type列,连接类型。一个好的...
  • MySQL优化原理

    千次阅读 2017-05-02 13:31:04
    说起MySQL的查询优化,相信大家收藏了一堆奇淫技巧:不能...因而理解这些优化建议背后的原理就尤为重要,希望本文能让你重新审视这些优化建议,并在实际业务场景下合理的运用。 MySQL逻辑架构 如果能在头脑中构建一
  • tunning-prime优化mysql建议

    万次阅读 2017-11-09 14:25:12
    #!/bin/sh       # set tabstop=8   ######################################################################### ...# MySQL performance tuning primer
  • Mysql优化

    千次阅读 2019-01-25 11:44:43
    今天就给大家列举 MySQL 数据库中,最经典的十大错误案例,并附有处理问题的解决思路和方法,希望能给刚入行,或数据库爱好者一些帮助。 今后再遇到任何报错,我们都可以很淡定地去处理。学习任何一门技术的同时,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 113,977
精华内容 45,590
关键字:

mysql优化建议

mysql 订阅