快捷导航
通知内容:小白站长云与小白站长论坛两站强强合作,凡是小白站长论坛的正式会员注册小白站长云并需要购买云服务器或者服务器租用/托管、云防御、ssl证书的可联系管理员享受升级成小白站长云金牌会员,永久享受8折优惠。还等什么,小白站长论坛的用户们赶快行动起来吧。
1 70

DISCUZ技术文库

admin 于 2019-8-22 13:55 发表在 [Discuz!教程] [复制链接]
字段表和字段命名表和字段的命名以前面《4.4命名原则》的约定为基本准则。所有数据表名称,只要其名称是可数名词,则必须以复数方式命名,例如:cdb_members(用户表)、cdb_posts(帖子表);存储多项内容的字段,或代表数量的字段,也应当以复数方式命名,例如:params(parameters,自定义Discuz!代码的参数个数)、views(查看次数)、replies(回复次数)。当几个表间的字段有关连时,要注意表与表之间关联字段命名的统一,如cdb_threads表中的tid与cdb_posts表中的tid。代表id自增量的字段,通常用以下几种形式:
  • 最常用的核心id,或经常在URL中进行调用的,尽量用简写的形式,例如tid、pid、uid;
  • 有功能性作用,URL中偶尔用到的id,使用全称的形式,例如pluginid;
  • 没有功能性作用,只为管理和维护方便而设的id,可以使用全称的形式,也可只将其命名为id。
所有与表、字段相关的命名,请务必大量参考Discuz!现有字段的命名方式,以保证命名的系统性和统一性。
字段结构
  • 允许NULL值的字段,数据库在进行比较操作时,会先判断其是否为NULL,非NULL时才进行值的必对。因此基于效率的考虑,所有字段均不能为空,即全部NOT NULL;
  • 预计不会存储非负数的字段,例如各项id、发帖数等,必须设置为UNSIGNED类型。UNSIGNED类型比非UNSIGNED类型所能存储的正整数范围大一倍,因此能获得更大的数值存储空间
  • 存储开关、选项数据的字段,通常使用tinyint(1)非UNSIGNED类型,少数情况也可能使用enum()结果集的方式。tinyint作为开关字段时,通常1为打开;0为关闭;-1为特殊数据,例如N/A(不可用);高于1的为特殊结果或开关二进制数组合(详见Discuz!中相关代码);
  • MEMORY/HEAP类型的表中,要尤其注意规划节约使用存储空间,这将节约更多内存。例如cdb_sessions表中,就将IP地址的存储拆分为4个tinyint(3) UNSIGNED类型的字段,而没有采用char(15)的方式;
  • 任何类型的数据表,字段空间应当本着足够用,不浪费的原则,数值类型的字段取值范围见下表:
  • 字段类型存储空间(b)UNSIGNED取值范围
    tinyint1-128~127
    0~255
    smallint2-32768~32767
    0~65535
    mediumint3-8388608~8388607
    0~16777215
    int4-2147483648~2147483647
    0~4294967295
    bigint8-9223372036854775808~9223372036854775807
    0~18446744073709551615

    SQL语句
    • 所有SQL语句中,除了表名、字段名称以外,全部语句和函数均需大写,应当杜绝小写方式或大小写混杂的写法。例如select * from cdb_members;是不符合规范的写法。
    • 很长的SQL语句应当有适当的断行,依据JOIN、FROM、ORDER BY等关键字进行界定。
    • 通常情况下,在对多表进行操作时,要根据不同表名称,对每个表指定一个1~2个字母的缩写,以利于语句简洁和可读性。
    如下的语句范例,是符合规范的:$query = $db->query("SELECT s.*, m.* FROM {$tablepre}sessions s, {$tablepre}members m WHERE m.uid=s.uid AND s.sid='$sid');性能与效率定长与变长表包含任何varchar、text等变长字段的数据表,即为变长表,反之则为定长表。
    • 对于变长表,由于记录大小不同,在其上进行许多删除和更改将会使表中的碎片更多。需要定期运行OPTIMIZE TABLE以保持性能。而定长表就没有这个问题;
    • 如果表中有可变长的字段,将它们转换为定长字段能够改进性能,因为定长记录易于处理。但在试图这样做之前,应该考虑下列问题:
    • 使用定长列涉及某种折衷。它们更快,但占用的空间更多。char(n) 类型列的每个值总要占用n 个字节(即使空串也是如此),因为在表中存储时,值的长度不够将在右边补空格;
    • 而varchar(n)类型的列所占空间较少,因为只给它们分配存储每个值所需要的空间,每个值再加一个字节用于记录其长度。因此,如果在char和varchar类型之间进行选择,需要对时间与空间作出折衷;
    • 变长表到定长表的转换,不能只转换一个可变长字段,必须对它们全部进行转换。而且必须使用一个ALTER TABLE语句同时全部转换,否则转换将不起作用;
    • 有时不能使用定长类型,即使想这样做也不行。例如对于比255字符更长的串,没有定长类型;
    • 在设计表结构时如果能够使用定长数据类型尽量用定长的,因为定长表的查询、检索、更新速度都很快。必要时可以把部分关键的、承担频繁访问的表拆分,例如定长数据一个表,非定长数据一个表。例如Discuz!的cdb_members和cdb_memberfields表、cdb_forums和cdb_forumfields表等。因此规划数据结构时需要进行全局考虑;
    进行表结构设计时,应当做到恰到好处,反复推敲,从而实现最优的数据存储体系。
    运算与检索
    • 数值运算一般比字符串运算更快。例如比较运算,可在单一运算中对数进行比较。而串运算涉及几个逐字节的比较,如果串更长的话,这种比较还要多。
    • 如果串列的值数目有限,应该利用普通整型或emum类型来获得数值运算的优越性。
    • 更小的字段类型永远比更大的字段类型处理要快得多。对于字符串,其处理时间与串长度直接相关。一般情况下,较小的表处理更快。对于定长表,应该选择最小的类型,只要能存储所需范围的值即可。例如,如果mediumint够用,就不要选择bigint。对于可变长类型,也仍然能够节省空间。一个TEXT 类型的值用2 字节记录值的长度,而一个LONGTEXT 则用4字节记录其值的长度。如果存储的值长度永远不会超过64KB,使用TEXT 将使每个值节省2字节。
    结构优化与索引优化索引能加快查询速度,而索引优化和查询优化是相辅相成的,既可以依据查询对索引进行优化,也可以依据现有索引对查询进行优化,这取决于修改查询或索引,哪个对现有产品架构和效率的影响最小。索引优化与查询优化是多年经验积累的结晶,在此无法详述,但仍然给出几条最基本的准则。首先,根据产品的实际运行和被访问情况,找出哪些SQL语句是最常被执行的。最常被执行和最常出现在程序中是完全不同的概念。最常被执行的SQL语句,又可被划分为对大表(数据条目多的)和对小表(数据条目少的)的操作。无论大表或小表,有可分为读(SELECT)多、写(UPDATE/INSERT)多或读写都多的操作。对常被执行的SQL语句而言,对大表操作需要尤其注意:
    • 写操作多的,通常可使用写入缓存的方法,先将需要写或需要更新的数据缓存至文件或其他表,定期对大表进行批量写操作,例如Discuz!中点击数延迟更新机制,就是依据此原理实现。同时,应尽量使得常被读写的大表为定长类型,即便原本的结构中大表并非定长。大表定长化,可以通过改变数据存储结构和数据读取方式,将一个大表拆成一个读写多的定长表,和一个读多写少的变长表来实现;
    • 读操作多的,需要依据SQL查询频率设置专门针对高频SQL语句的索引和联合索引。
    而小表就相对简单,加入符合查询要求的特定索引,通常效果比较明显。同时,定长化小表也有益于效率和负载能力的提高。字段比较少的小定长表,甚至可以不需要索引。其次,看SQL语句的条件和排序字段是否动态性很高(即根据不同功能开关或属性,SQL查询条件和排序字段的变化很大的情况),动态性过高的SQL语句是无法通过索引进行优化的。惟一的办法只有将数据缓存起来,定期更新,适用于结果对实效性要求不高的场合。MySQL索引,常用的有PRIMARY KEY、INDEX、UNIQUE几种,详情请查阅MySQL文档。通常,在单表数据值不重复的情况下,PRIMARY KEY和UNIQUE索引比INDEX更快,请酌情使用。事实上,索引是将条件查询、排序的读操作资源消耗,分布到了写操作中,索引越多,耗费磁盘空间越大,写操作越慢。因此,索引决不能盲目添加。对字段索引与否,最根本的出发点,依次仍然是SQL语句执行的概率、表的大小和写操作的频繁程度。查询优化MySQL中并没有提供针对查询条件的优化功能,因此需要开发者在程序中对查询条件的先后顺序人工进行优化。例如如下的SQL语句:SELECT * FROM table WHERE a>’0’ AND b<’1’ ORDER BY c LIMIT 10;事实上无论a>’0’还是b<’1’哪个条件在前,得到的结果都是一样的,但查询速度就大不相同,尤其在对大表进行操作时。开发者需要牢记这个原则:最先出现的条件,一定是过滤和排除掉更多结果的条件;第二出现的次之;以此类推。因而,表中不同字段的值的分布,对查询速度有着很大影响。而ORDER BY中的条件,只与索引有关,与条件顺序无关。除了条件顺序优化以外,针对固定或相对固定的SQL查询语句,还可以通过对索引结构进行优化,进而实现相当高的查询速度。原则是:在大多数情况下,根据WHERE条件的先后顺序和ORDER BY的排序字段的先后顺序而建立的联合索引,就是与这条SQL语句匹配的最优索引结构。尽管,事实的产品中不能只考虑一条SQL语句,也不能不考虑空间占用而建立太多的索引。同样以上面的SQL语句为例,最优的当table表的记录达到百万甚至千万级后,可以明显的看到索引优化带来的速度提升。依据上面条件优化和索引优化的两个原则,当table表的值为如下方案时,可以得出最优的条件顺序方案:
    字段a字段b字段c
    1711
    2810
    3913
    最优条件:b<’1’ AND a>’0’
    最优索引:INDEX abc (b, a, c) 原因:b<’1’作为第一条件可以先过滤掉75%的结果。如果以a>’0’作为第一条件,则只能先过滤掉25%的结果
    注意:
    • 字段c由于未出现于条件中,故条件顺序优化与其无关
    • 最优索引由最优条件顺序得来,而非由例子中的SQL语句得来
    • 索引并非修改数据存储的物理顺序,而是通过对应特定偏移量的物理数据而实现的虚拟指针

    EXPLAIN语句是检测索引和查询能否良好匹配的简便方法。在phpMyAdmin或其他MySQL客户端中运行EXPLAIN+查询语句,例如EXPLAIN SELECT * FROM table WHERE a>’0’ AND b<’1’ ORDER BY c;这种形式,即使得开发者无需模拟上百万条数据,也可以验证索引是否合理,相关细节请参考MySQL说明。值得提出的是,Using filesort是最不应当出现的情况,如果EXPLAIN得出此结果,说明数据库为这个查询专门建立了一个用以缓存结果的临时表文件,并在查询结束后删除。众所周知,硬盘I/O速度始终是计算机存储的瓶颈,因此,查询中应当尽全力避免高执行频率的SQL语句使用filesort。尽管,开发者永远都不可能保证产品中的全部SQL语句都不会使用filesort。限于篇幅,本文档远远没有涵盖数据库优化的方方面面,例如:联合索引与普通索引的可重用性、JOIN连接的索引设计、MEMORY/HEAP表等。数据库优化实际上就是在很多因素和利弊间不断权衡、修改,惟有在成功与失败经验中反复推敲才能得出的经验,这种经验往往就是最难能可贵和价值连城的。兼容性问题
    • 由于MySQL 3.23至5.0的变化很大,因此程序中尽量不使用特殊的SQL语句,以免带来兼容性问题,并给数据库移植造成困难。
    • 通常在MySQL 4.1以上版本,Discuz!应使用相当的字符集来存储,例如GBK/BIG5/UTF-8。传统的latin1编码虽然有一定的兼容性,但仍然不是推荐的选择。使用相应非默认字符集时,程序每次运行时需要使用SET NAMES ‘character_set’;来规定连接、传输和结果的字符集。
    • Mysql 5.0以上新增了数种SQL_MODE,默认的SQL_MODE依服务器安装设置不同而不同,因此程序每次运行时需要使用SET SQL_MODE=’’;来规定当前的SQL模式。


版权申明:此文如未标注转载均为本站原创,自由转载请注明 本贴地址:https://www.xbadmin.cn/thread-2666-1-1.html 上篇帖子: 办公室装饰设计是重要的 下篇帖子: 讲讲PE保护膜给生活带来了什么便利?
举报 使用道具
| 回复

共 1 个关于本帖的回复 最后回复于 2019-9-23 11:35

豌豆思维 资深小白 发表于 2019-9-23 11:35:55 | 显示全部楼层
因此,查询中应当尽全力避免高执行频率的SQL语句使用filesort。尽管,开发者永远都不可能保证产品中的全部SQL语句都不会使用filesort。
举报 使用道具
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

精彩推荐

  • 小白极速云免费招收代理(0预存)
  • 小白站长论坛开站题词
  • 企业型/小说型/个人型网站用香港云服务器
  • 小白站长论坛账号注册教程
  • discuz!以及整个网络论坛的没落

明星用户

关闭

站长推荐上一条 /1 下一条

小白站长云与小白站长论坛两站强强合作,凡是小白站长论坛的正式会员注册小白站长云并需要购买云服务器或者服务器租用/托管、云防御、ssl证书的可联系管理员享受升级成小白站长云金牌会员,永久享受8折优惠。还等什么,小白站长论坛的用户们赶快行动起来吧。

第一站长论坛|云服务器赞助商|sitemap|站长交易论坛|小白SEO论坛|小白站长论坛

GMT+8, 2019-11-17 14:07 , Processed in 0.086037 second(s), 24 queries .