-
MySQL数据库性能优化之表结构优化
数据类型选择
数据库操作中最为耗时的操作就是 IO 处理,大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量,可以在很大程度上提高数据库操作的性能。
我们无法改变数据库中需要存储的数据,但是我们可以在这些数据的存储方式方面花一些心思。下面的这些关于字段类型的优化建议主要适用于记录条数较多,数据量较大的场景,因为精细化的数据类型设置可能带来维护成本的提高,过度优化也可能会带来其他的问题:
1.数字类型:非万不得已不要使用DOUBLE,不仅仅只是存储长度的问题,同时还会存在精确性的问题。同样,固定精度的小数,也不建议使用DECIMAL,建议乘以固定倍数转换成整数存储,可以大大节省存储空间,且不会带来任何附加维护成本。对于整数的存储,在数据量较大的情况下,建议区分开 TINYINT / INT / BIGINT 的选择,因为三者所占用的存储空间也有很大的差别,能确定不会使用负数的字段,建议添加unsigned定义。当然,如果数据量较小的数据库,也可以不用严格区分三个整数类型。
2.字符类型:非万不得已不要使用 TEXT 数据类型,其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段,建议使用 CHAR 类型,不定长字段尽量使用 VARCHAR,且仅仅设定适当的最大长度,而不是非常随意的给一个很大的最大长度限定,因为不同的长度范围,MySQL也会有不一样的存储处理。
3.时间类型:尽量使用TIMESTAMP类型,因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型,建议使用DATE类型,因为他的存储空间只需要3个字节,比TIMESTAMP还少。不建议通过INT类型类存储一个unix timestamp 的值,因为这太不直观,会给维护带来不必要的麻烦,同时还不会带来任何好处。
4.ENUM & SET:对于状态字段,可以尝试使用 ENUM 来存放,因为可以极大的降低存储空间,而且即使需要增加新的类型,只要增加于末尾,修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢?可以尝试使用SET类型,即使存在多种属性,同样可以游刃有余,同时还可以节省不小的存储空间。
5.LOB类型:强烈反对在数据库中存放 LOB 类型数据,虽然数据库提供了这样的功能,但这不是他所擅长的,我们更应该让合适的工具做他擅长的事情,才能将其发挥到极致。在数据库中存储 LOB 数据就像让一个多年前在学校学过一点Java的营销专业人员来写 Java 代码一样。
字符编码
字符集直接决定了数据在MySQL中的存储编码方式,由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异,所以通过使用合适的字符集,可以帮助我们尽可能减少数据量,进而减少IO操作次数。
1.纯拉丁字符能表示的内容,没必要选择 latin1 之外的其他字符编码,因为这会节省大量的存储空间。
2.如果我们可以确定不需要存放多种语言,就没必要非得使用UTF8或者其他UNICODE字符类型,这回造成大量的存储空间浪费。
3.MySQL的数据类型可以精确到字段,所以当我们需要大型数据库中存放多字节数据的时候,可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量,进而降低 IO 操作次数并提高缓存命中率。
适当拆分
有些时候,我们可能会希望将一个完整的对象对应于一张数据库表,这对于应用程序开发来说是很有好的,但是有些时候可能会在性能上带来较大的问题。
当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候,如果我们大部分访问这张表的时候都不需要这个字段,我们就该义无反顾的将其拆分到另外的独立表中,以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加,既减少物理 IO 次数,也能大大提高内存中的缓存命中率。
上面几点的优化都是为了减少每条记录的存储空间大小,让每个数据库中能够存储更多的记录条数,以达到减少 IO 操作次数,提高缓存命中率。下面这个优化建议可能很多开发人员都会觉得不太理解,因为这是典型的反范式设计,而且也和上面的几点优化建议的目标相违背。
适度冗余
为什么我们要冗余?这不是增加了每条数据的大小,减少了每个数据块可存放记录条数吗?
确实,这样做是会增大每条记录的大小,降低每条记录中可存放数据的条数,但是在有些场景下我们仍然还是不得不这样做:
1.被频繁引用且只能通过 Join 2张(或者更多)大表的方式才能得到的独立小字段。
2.这样的场景由于每次Join仅仅只是为了取得某个小字段的值,Join到的记录又大,会造成大量不必要的 IO,完全可以通过空间换取时间的方式来优化。不过,冗余的同时需要确保数据的一致性不会遭到破坏,确保更新的同时冗余字段也被更新。
尽量使用 NOT NULL
NULL 类型比较特殊,SQL 难优化。虽然 MySQL NULL类型和 Oracle 的NULL 有差异,会进入索引中,但如果是一个组合索引,那么这个NULL 类型的字段会极大影响整个索引的效率。此外,NULL 在索引中的处理也是特殊的,也会占用额外的存放空间。
很多人觉得 NULL 会节省一些空间,所以尽量让NULL来达到节省IO的目的,但是大部分时候这会适得其反,虽然空间上可能确实有一定节省,倒是带来了很多其他的优化问题,不但没有将IO量省下来,反而加大了SQL的IO量。所以尽量确保 DEFAULT 值不是 NULL,也是一个很好的表结构设计优化习惯。
-
数据库性能优化之缓存参数优化
(1)、max_connections:
允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加,否则,你将经常看到 too many connections 错误。 默认数值是100,我把它改为1024 。
(2)、record_buffer:
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,你可能想要增加该值。默认数值是131072(128k),我把它改为16773120 (16m)
(3)、key_buffer_size:
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小,增加它可得到更好处理的索引(对所有读和多重写),到你能负担得起那样多。如果你使它太大,系统将开始换页并且真的变慢了。默认数值是8388600(8m),我的mysql主机有2gb内存,所以我把它改为 402649088(400mb)。
(4)、back_log:
要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
back_log 值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表,发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null 的待连接进程时,就要加大 back_log 的值了。默认数值是50,我把它改为500。
(5)、interactive_timeout:
服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 client_interactive 选项的客户。 默认数值是28800,我把它改为7200。
(6)、sort_buffer:
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。默认数值是2097144(2m),我把它改为 16777208 (16m)。
(7)、table_cache:
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是64,我把它改为512。
(8)、thread_cache_size:
可以复用的保存在中的线程的数量。如果有,新的线程从缓存中取得,当断开连接的时候如果有空间,客户的线置在缓存中。如果有很多新的线程,为了提高性能可以这个变量值。通过比较 connections 和 threads_created 状态的变量,可以看到这个变量的作用。我把它设置为 80。
(9)mysql的搜索功能
用mysql进行搜索,目的是能不分大小写,又能用中文进行搜索
只需起动mysqld时指定 --default-character-set=gb2312
(10)、wait_timeout:
服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800,我把它改为7200。
注:参数的调整可以通过修改 /etc/my.cnf 文件并重启 mysql 实现。这是一个比较谨慎的工作,上面的结果也仅仅是我的一些看法,你可以根据你自己主机的硬件情况(特别是内存大小)进一步修改。
l lower_case_table_names
Linux或类Unix平台,对文件名称大小写敏感,也即对数据库、表、存储过程等对象名称大小写敏 感,为减少开发人员的开发成本,为此推荐大家设置该参数使对象名称都自动转换成小写;
l max_connect_errors
max_connect_errors默认值为10,也即mysqld线程没重新启动过,一台物理服务器只要连接 异常中断累计超过10次,就再也无法连接上mysqld服务,为此建议大家设置此值至少大于等于10W; 若异常中断累计超过参数设置的值,有二种解决办法,执行命令:FLUSH HOSTS;或者重新启动mysqld服务;
l interactive_timeout and wait_timeout
u interactive_timeout
处于交互状态连接的活动被服务器端强制关闭,而等待的时间,单位:秒;
u wait_timeout
与服务器端无交互状态的连接,直到被服务器端强制关闭而等待的时间,此参数只对基于TCP/IP或基于 Socket通信协议建立的连接才有效,单位:秒;
u 推荐设置
interactive_timeout = 172800
wait_timeout = 172800
l transaction-isolation and binlog-format
u transaction-isolation
可供设置的值:READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、
SERIALIZABLE,默认的值为: REPEATABLE-READ,事务隔离级别设置的不同,对二进制日志登记格
式影响非常大,详细信息可见文章解读MySQL事务的隔离级别和日志登记模式选择技巧;
u binlog-format
复制的模式,可供设置的值:STATEMENT、ROW、MIXED(注:5.0.*只有命令行式复制),
5.1.*版本默认设置:MIXED;
u 推荐配置
① 只读为主的业务应用场景
transaction-isolation = read-committed
binlog-format = mixed #5.1.*版本,5.0.*只能设置为 statement
① 非只读为主的业务应用场景
transaction-isolation = repeatabled-read
binlog-format = mixed #5.1.*版本,5.0.*只能设置为 statement
l event_scheduler
事务调度默认是关闭状态,也推荐源码编译的版本可不编译进来,以及实际生产环境保持默认禁用 状态,当真正需要用的时候,可以临时打开,命令:SET GLOBAL event_scheduler=1;
l skip_external_locking
外部锁,也即操作系统所实施的锁,只对MyISAM引擎有效,且容易造成死锁发生,为此我们一律禁用;
l innodb_adaptive_hash_index
InnoDB引擎会根据数据的访问频繁度,把表的数据逐渐缓到内存,若是一张表的数据大量缓存在 内存中,则使用散列索引(注:Hash Index)会更高效。InnoDB内有Hash Index机制,监控数据的访 问情况,可以自动创建和维护一个Hash Index,以提供访问效率,减少内存的使用;
l innodb_max_dirty_pages_pct
InnoDB主线程直接更新Innodb_buffer_pool_size中存在的数据,并且不实时刷回磁盘,而是等待 相关的处罚事件发生,则允许缓存空间的数据量不实时刷回磁盘的最大百分比。比例设置较小,有利于 减少mysqld服务出现问题的时候恢复时间,缺点则是需要更多的物理I/O,为此我们必须根据业务特点 和可承受范围进行一个折中,一般范围建议设置为5%~90%,像我们SNS游戏行业的写非常厉害,综合 各方面因素,设置为20%;
其他部分:http://www.mysqlops.com/2011/10/26/mysql-variable-two.html
Mysql 查询优化
1.为查询缓存优化你的查询
大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查询结果会被放到一个缓存中,这样,后续的相同的查询就不用操作表而直接访问缓存结果了。
这里最主要的问题是,对于程序员来说,这个事情是很容易被忽略的。因为,我们某些查询语句会让MySQL不使用缓存。请看下面的示例:
上面两条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.当只要一行数据时使用LIMIT1
当你查询表的有些时候,你已经知道结果只会有一条结果,但因为你可能需要去fetch游标,或是你也许会去检查返回的记录数。
在这种情况下,加上LIMIT 1可以增加性能。这样一样,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。
下面的示例,只是为了找一下是否有“中国”的用户,很明显,后面的会比前面的更有效率。(请注意,第一条中是Select *,第二条是Select 1)
4.为搜索字段建索引
索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么,请为其建立索引吧。
从上图你可以看到那个搜索字串 “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”来查看这些建议
一定要注意,这些只是建议,只有当你的表里的数据越来越多时,这些建议才会变得准确。一定要记住,你才是最终做决定的人。
11.尽可能的使用NOT NULL php程序员站
除非你有一个很特别的原因去使用NULL值,你应该总是让你的字段保持NOT NULL。这看起来好像有点争议,请往下看。
首先,问问你自己“Empty”和“NULL”有多大的区别(如果是INT,那就是0和NULL)?如果你觉得它们之间没有什么区别,那么你就不要使用NULL。(你知道吗?在Oracle里,NULL 和 Empty的字符串是一样的!)
不要以为 NULL 不需要空间,其需要额外的空间,并且,在你进行比较的时候,你的程序会更复杂。当然,这里并不是说你就不能使用NULL了,现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。
下面摘自MySQL自己的文档:
今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事情。当我们去设计数据库表结构,对操作数据库时(尤其是查表时的SQL语句),我们都需要注意数据操作的性能。这里,我们不会讲过多的SQL语句的优化,而只是针对MySQL这一Web应用最多的数据库。希望下面的这些优化技巧对你有用。
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
当我们为group_id字段加上索引后:
我们可以看到,前一个结果显示搜索了7883行,而后一个只是搜索了两个表的9和16行。查看rows列可以让我们找到潜在的性能问题。
3.当只要一行数据时使用LIMIT1
当你查询表的有些时候,你已经知道结果只会有一条结果,但因为你可能需要去fetch游标,或是你也许会去检查返回的记录数。
在这种情况下,加上LIMIT 1可以增加性能。这样一样,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。
下面的示例,只是为了找一下是否有“中国”的用户,很明显,后面的会比前面的更有效率。(请注意,第一条中是Select *,第二条是Select 1)
php程序员站
4.为搜索字段建索引
索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么,请为其建立索引吧。
从上图你可以看到那个搜索字串 “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”来查看这些建议
一定要注意,这些只是建议,只有当你的表里的数据越来越多时,这些建议才会变得准确。一定要记住,你才是最终做决定的人。
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.
13.无缓冲的查询
正常的情况下,当你在当你在你的脚本中执行一个SQL语句的时候,你的程序会停在那里直到没这个SQL语句返回,然后你的程序再往下继续执行。你可以使用无缓冲查询来改变这个行为。 ww~phperz~com
关于这个事情,在PHP的文档中有一个非常不错的说明:mysql_unbuffered_query()函数:
![](http://s7.sinaimg.cn/middle/001AqcCLzy6JOQ10502a6&690)
上面那句话翻译过来是说,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搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。并且,固定长度的表也更容易被缓存和重建。不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间。使用“垂直分割”技术(见下一条),你可以分割你的表成为两个一个是定长的,一个则是不定长的。
16.垂直分割
“垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。(以前,在银行做过项目,见过一张表有100多个字段,很恐怖)
示例一:在Users表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢?这样会让你的表有更好的性能,大家想想是不是,大量的时候,我对于用户表来说,只有用户ID,用户名,口令,用户角色等会被经常使用。小一点的表总是会有好的性能。
示例二:你有一个叫“last_login”的字段,它会在每次用户登录时被更新。但是,每次更新时会导致该表的查询缓存被清空。所以,你可以把这个字段放到另一个表中,这样就不会影响你对用户ID,用户名,用户角色的不停地读取了,因为查询缓存会帮你增加很多性能。hp程序员之家
另外,你需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去Join他们,不然的话,这样的性能会比不分割时还要差,而且,会是极数级的下降。 php程
17.拆分大的DELETE或INSERT语句
如果你需要在一个在线的网站上去执行一个大的DELETE或INSERT查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。Apache会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你泊WEB服务Crash,还可能会让你的整台服务器马上?了。所以,如果你有一个大的处理,你定你一定把其拆分,使用LIMIT条件是一个好的方法。下面是一个示例:
18.越小的列会越快
对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。所以,把你的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问。参看MySQL的文档Storage Requirements查看所有的数据类型。p程序员站
如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用INT来做主键,使用MEDIUMINT,SMALLINT或是更小的TINYINT会更经济一些。如果你不需要记录时间,使用DATE要比DATETIME好得多。
当然,你也需要留够足够的扩展空间,不然,你日后来干这个事,你会死的很难看,参看Slashdot的例子(2009年11月06日),一个简单的ALTER TABLE语句花了3个多小时,因为里面有一千六百万条数据。 perz~com
19.选择正确的存储引擎
在MySQL中有两个存储引擎MyISAM和InnoDB,每个引擎都有利有弊。酷壳以前文章《MySQL: InnoDB 还是 MyISAM?》讨论和这个事情MyISAM适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。另外,MyISAM对于 SELECT COUNT(*) 这类的计算是超快无比的。 www~phperz~com
InnoDB的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM还慢。他是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。
下面是MySQL的手册
20.使用一个对象关系映射器(Object Relational Mapper)
使用 ORM (Object Relational Mapper),你能够获得可靠的性能增涨。一个ORM可以做的所有事情,也能被手动的编写出来。但是,这需要一个高级专家。 phperz~com
ORM的最重要的是“Lazy Loading”,也就是说,只有在需要的去取值的时候才会去真正的去做。但你也需要小心这种机制的副作用,因为这很有可能会因为要去创建很多很多小的查询反而会降低性能。hperz.com
ORM还可以把你的SQL语句打包成一个事务,这会比单独执行他们快得多得多。
目前,个人最喜欢的PHP的ORM是:Doctrine。
21.小心“永久链接”
“永久链接”的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了,它会永远处在连接的状态,就算是数据库操作已经结束了。而且,自从我们的Apache开始重用它的子进程后----也就是说,下一次的HTTP请求会重用Apache的子进程,并重用相同的MySQL链接。
PHP手册:mysql_pconnect()
在理论上来说,这听起来非常的不错。但是从个人经验(也是大多数人的)上来说,这个功能制造出来的麻烦事更多。因为,你只有有限的链接数,内存问题,文件句柄数,等等。
而且,Apache运行在极端并行的环境中,会创建很多很多的了进程。这就是为什么这种“永久链接”的机制工作地不好的原因。在你决定要使用“永久链接”之前,你需要好好地考虑一下你的整个系统的架构。
-
一些DBA的建议及经验
-
不要迷恋传说,还是得具体情况具体分析
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当,那么用in和exists差别不大。
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:
例如:表A(小表),表B(大表)
1:
select * from A where cc in (select cc from B) 效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc) 效率高,用到了B表上cc列的索引。
相反的
2:
select * from B where cc in (select cc from A) 效率高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc) 效率低,用到了A表上cc列的索引。
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao');
与
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
的结果是相同的。
数据库中的每个表至少对应数据库目录中的一个文件,MySQL数据库名和表名的大小写敏感性跟操作系统关联,在UNIX中是敏感的,在Windows中是不敏感的.
而列,索引,存储子程序,和触发器名在任何平台上是大小写不敏感的.表别名在UNIX中是敏感的,在WIN中不敏感.
SQL优化
通过show status 命令了解各种SQL的执行效率.
Show [session | global]status 中的session代表当前连接, global代表自数据库上次启动至今.
我们通常比较关心一下几个统计参数:
Com_select: 执行select操作的次数.
Com_insert: 执行insert操作的次数.
Com_update: 执行update操作的次数.
Com_delete: 执行delete操作的次数.
Com_commit: 事物提交的次数.
Com_rollback: 事物回滚的次数. 对于回滚操作非常频繁的数据库,可能意味这应用编写存在问题.
Connections: 试图连接mysql服务器的次数.
Uptime: 服务器工作时间.
Slow_queries: 慢查询的次数.
可以通过两种方法定位执行效率低的SQL语句. 1,查看慢查询日志.2, show processlist 命令查看当前线程.
通过desc 或 explain都可以获取SELECT语句的执行信息.比如:
Explain select * fromkevin;
mysql> explainselect * from kevin \G
得到如下信息:
每个列的简单解释如下:
Select type:表示select的类型,
Table : 输出结果集的表.
Type : 表示表的链接类型,性能由好到差的类型:syetem,const, eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery,range, index, all
Possible_keys : 可能使用的索引.
Key: 实际使用的索引.
Key_len: 索引字段的长度.
Rows : 扫描行的数量.
Extra: 执行情况的说明和描述.
查询表上的索引状态: show index from tab_name \G;
对于创建的多列索引,只要查询的条件中用到了最左边的列,索引一般就会被使用.
对于使用like的查询,后面如果是常量并且只有”%”号不在第一个字符的时候,索引才可能被使用.
如果对大的文本进行索引,使用全文索引而不应使用like’%...%’.
Heap表只用在where中有”=”的条件下才会使用索引.
用or分割开的条件,如果有一个条件中的列没有用到索引,那么索引不会被使用.
如果列类型是字符串, 在where中要用把此字符常量用引号引起来,否则即使这个列上有索引,也不会被用到.
查看索引使用情况: show status like ‘Handler_read%’; 返回的数据其中:
Handler_read_key 代表了一个行被索引值读的次数,说明索引性能比较高.
Handler_read_rnd_next 是数据文件中读下一行的请求数,意味着查询运行低效,说明索引不正确或查询没有利用索引