Posts Tagged ‘mysql’
My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings. 在面试MySQL DBA或者那些打算做MySQL性能优化的人时，我最喜欢问题是：MySQL服务器按照默认设置安装完之后，应该做哪些方面的调节呢？ I’m surprised how many people fail to provide any reasonable answer [...]
昨晚花了一个晚上时间hll的写了这个存储过程，刚好也复习下存储过程的语法。这个存储过程是为西瓜猫下配送订单的时候作的，实现扣卡金额，减少商品数量，生成配送单，插入配送单详情的一系列操作。 drop procedure if exists createOrder; create procedure createOrder (IN uid int, IN goods varchar(800), IN orderNo varchar(25), IN orderType tinyint, IN rate smallint, IN cid int, OUT goMoney int) soone_pro:BEGIN DECLARE allPrice int DEFAULT 0; DECLARE theFlag int DEFAULT 0; DECLARE oRate int DEFAULT 0; DECLARE leaInfo varchar(255); DECLARE gInfo varchar(20); DECLARE gid int; [...]
一个最好的剖析服务器的方法是，用SHOW STATUS来查看什么占用了它大量的处理时间。SHOW STATUS显示了很多的信息，我们仅仅关心其中的一小部分。 为了查看最近服务器的表现。你可以周期性的抽取SHOW STATUS并且把它和先前的结果进行比较。命令如下： mysqladmin extended -r -i 10（注：要连接服务器所以要加 -u -p 参数：mysqladmin -u root -p extended -r -i 10） 这些变量并不是一直增长的。因此你可能看到一些奇怪的输出，比如Threads_running有的时候是负数。不必担心这个，意思就是相对于上一 个例子，这个计数在减少。 因为这个输出是非常大的。我们可以使用grep（windows 强烈推荐cygwin）来查取一些想要的变量。也可以使用Innotop或其他的一些工具来查看这些结果。这个将在以后章节说明。下面就说一下比较有用的 变量。 Bytes_received ，Bytes_sent：服务器进出的流量。 Com_*：服务器执行的命令。 Created_*：在语句执行的时候，临时表和文件的创建。 Handler_*：存储引擎的操作。 Select_*：多种类连接的执行安排。 Sort_*：多种类排序的信息。 你可以使用这个方法来监控MySQL内部的操作。比如Key(键)的访问次数，对于MyISAM，从硬盘的Key(键)的读取，数据访问的频率 以及等等。这样才能帮助你知道在系统中哪些是潜在的瓶颈。而不用去查看一个单独的语句。你也可以使用单独的工具去分析SHOW STATUS，如mysqlreport.这样是为了得到一个总的服务器”健康”信息. 我们不会详细解释每个状态变量(status variables)的意思，但是当我们在例子中使用它们的时候，会详细说明。因此现在没必要担心我们对这些变量一无所知。 另一个剖析服务器的方法是使用SHOW PROCESSLIST.使用它不仅仅能看到什么类型的语句正在执行，也能知道你连接的状态。一些如在锁的状态下，连接数过高，是显而易见的瓶颈。与 SHOW STATUS一样，SHOW PROCESSLIST的信息量很大，我们也需要工具取代用手工查看结果的方法，比如INNOTOP。 via:http://xiayuanfeng.javaeye.com/blog/411453
结合FLUSH STATUS和SHOW SESSION STATUS对于查看语句执行和批量语句来说是非常有用的。这是优化语句的最佳方法。 让我们来看一个例子，首先，执行FLUSH STATUS把当前会话状态变量清零，因此你可以知道MySQL执行这个语句做了多少的工作。 mysql> FLUSH STATUS; 接下来，执行语句，我们添加了一个参数叫SQL_NO_CACHE。所以MySQL执行的语句并不是缓存所提供的。 mysql> SELECT SQL_NO_CACHE film_actor.actor_id, COUNT(*) -> FROM sakila.film_actor -> INNER JOIN sakila.actor USING(actor_id) -> GROUP BY film_actor.actor_id -> ORDER BY COUNT(*) DESC; … 200 rows in set (0.18 sec) 这个查询语句返回了200行。但是它真正的做了什么？SHOW STATUS可以更深入的查看。首先，让我们看看服务器所提供查询计划（query plan）的类型. mysql> SHOW SESSION STATUS LIKE ‘Select%’; +————————+——-+ | Variable_name | Value | +————————+——-+ [...]
show profile是由Jeremy Cole捐献给MySQL社区版本的。默认的是关闭的，但是会话级别可以开启这个功能。开启它可以让MySQL收集在执行语句的时候所使用的资源。为了统 计报表,把profiling设为1 mysql> SET profiling = 1; 之后在运行一个查询 mysql> SELECT COUNT(DISTINCT actor.first_name) AS cnt_name, COUNT(*) AS cnt -> FROM sakila.film_actor -> INNER JOIN sakila.actor USING(actor_id) -> GROUP BY sakila.film_actor.film_id -> ORDER BY cnt_name DESC; … 997 rows in set (0.03 sec) 这个执行语句的剖析信息存储在这个会话中。使用SHOW PROFILES进行查看。 mysql> SHOW PROFILES\G *************************** 1. row *************************** Query_ID: 1 Duration: [...]
MySQL支持很多字符串类型，以及它们的许多变化类型。这些数据类型在 4.1和5.0版本变化都比较大。可以说变得更复杂了。早在4.1版本中，每个字符串列都有自己的字符集和对于那些字符集的排序规则，或者叫做 collation（校对）。 VARCHAR 和CHAR 有两个主要的字符串类型就是VARCHAR和CHAR。它们都存储了字符 值。不幸的是，很难准确解释这些值怎样存储在硬盘和内存中，因为这些都是有存储引擎实现的。我们假设你使用的是MyISAM和/或者InnoDB.如果不 是，你最好查阅你使用存储引擎的相关文档。 让我们先看看VARCHAR和CHAR是怎样存储在硬盘上的。要注意的是 存储引擎可能存储的CHAR或者VARCHAR在存入内存和硬盘上有所不同的，以及当服务器从存储引擎获得了这个值的时候，会把这个值转为另一个存储引擎 的格式。下面就是两种类型的比较 VARCHAR： VARCHAR存储了变长的字符串以及它是最常见的字符串数据类型。它占 用的空间要少于定长的类型，因为它根据所需来决定需要的空间大小。特例就是MyISAM参数设为ROW_FORMAT=FIXED.这个参数使表的每一行 使用固定大小的空间以及浪费大量的空间。 varchar使用了1或者2额外的字节记录值的长度：如果长度大约为 255字节的话，大概使用1个字节，如果更多的话，那么就是2个字节。假如是latin1字符节，一个varchar(10)将会占用11字节的存储空 间。一个varchar(1000)使用上限为1002字节。因为需要2字节存储长度信息。 VARCHAR会对性能有所帮助，因为它节约了空间。然而，因为行是变 长，当你更新它们的时候，它们就会增长。这样会导致需要额外的一些工作。如果一行增长以及并不在原来的地址了。这些行为依赖于存储引擎。举个例 子，MyISAM可能把行分为碎片。以及InnoDB可能切开页，把行填充到它的内部。其他的存储引擎可能在适当的地方不会更新数据。 当最大列长度比平均长度要大的时候就值得使用VARCHAR.当很少更新 字段的时候，存储碎片就不是一个问题。以及当你使用复杂的字符集如UTF-8时，每个字符都使用变量的字节数存储。 在5.0以及最新的版本，当你存储和获取值的时候，MySQL会保留尾部 的空格。在4.1和更早的版本，MySQL去掉尾部空格。 CHAR CHAR是定长的：MySQL总是分配指定的内存空间去存储字符。当存储 了一个CHAR值，MySQL去掉尾部空格。值附加了空格是比较的需要。 如果你存储的字符串是非常短的，用CHAR是非常有用的。举个例子，存储 密码的MD5值使用CHAR是个好的选择。因为这个MD5一直是定长的。如果数据经常修改的话CHAR要优于VARCHAR。因为定长的行并不会有存储残 片。对于非常短的列，CHAR要比VARCHAR高效。一个CHAR(1)用来存储Y和N值。这只会使用1个字节，但是一个VARCHAR(1)会使用两 个字节。有个字节用来保存它的长度信息。 这个行为让人有点困惑，因此我们举一个例子。首先，我们创建一个有单独 CHAR(10)列的表以及存储一些数值。 mysql> CREATE TABLE char_test( char_col CHAR(10)); mysql> INSERT INTO char_test(char_col) VALUES -> (‘string1′), (‘ string2′), (‘string3 ‘); 当我们获取这个值的时候，尾部空格被去掉了。 mysql> SELECT CONCAT(“‘”, [...]
非常棒的一篇Mysql相关文章 MySQL程序剖析 （Profiling） 我们将要详细的讲到MySQL的剖析(Profiling),因为它很少依赖于你的应用。应用和服务器 级别的剖析有的时候都是有必要的。虽然应用级别的剖析可以给你整个应用性能的总揽。，但是对MySQL的剖析提供了信息是服务器级别所提供不了的。比如， 对PHP代码进行剖析不会显示MySQL有多少行语句执行了。 与应用剖析一样，目标是找出MySQL哪部分消耗过多的时间。我们不会剖析MySQL源码的，虽然有的 时候定制化MySQL安装很有用，但是这是另一本书的主题了。所替代的是，我们将教你一些可以技术来获取和分析不同种类的MySQL执行语句的信息。 你可以用在任意的颗粒级别以满足你的需求：你可能对整个服务器进行剖析或者单独检查一个语句或者一组语 句。下列信息你可以一点点的收集： MySQL经常访问的那些数据 MySQL经常执行语句的类型 MySQL线程大部分时间的状态 MySQL经常执行语句的子系统 MySQL执行语句所访问的数据类型 不同活动的类型，比如扫描索引。 我们先从范围最广的剖析开始，那就是服务器剖析，将教你更多细节。 记录执行的语句 MySQL有两种记录语句的类型：general log和slow log。他们都是记录执行语句，但是却在语句执行进程的两端。general log记录了每个服务器收到的语句，因此它的语句可能包含了那些没有执行导致错误的语句。general Log记录了所有的语句，包括了一些非执行语句的事件，比如连接和断开连接。你可以用一个指令来启用它。 log = <file_name> 根据设计，general log不会包括执行时间和其他一些仅仅在语句执行完毕的信息。相比较而言，slow log记录执行完毕的语句。尤其是，它记录那些超过指定时间执行的语句。这两种日志都对程序剖析很有用，但是slow log是获取问题语句的主要工具。我们常常推荐把它开启。 下面列出的配置会开启这个日志。获取所有执行时间超过2s的语句，以及记录那些不使用索引的语句。它也 会记录一些执行慢的管理语句，比如OPTIMIZE TABLE: log-slow-queries = <file_name> long_query_time = 2 log-queries-not-using-indexes log-slow-admin-statements 你可以自定义这些配置，然后把它们放到my.conf文件中。更多的服务器配置将在以后的章节详细讲 [...]
我们大家都知道MySQL QueryCache(下 面简称QC)它是根据实际应用的SQL语句来cache 的。一个相关的SQL查询，如果它是以select开头的话，其MySQL服务器就会尝试对其使用 QC。每个Cache都是以SQL文本作为key来存的。 在应用QC之前，SQL文本不会被作任何处理。 也就是说，两个SQL语句，只要相差哪怕是一个字 符（例如大小写不一样；多一个空格等），那么这两个SQL将使用不同的一个CACHE。不过SQL文本有可能会被客户端做一些处理。例如在官方的命令行客 户端里，在发送SQL给服务器之前，会做如下处理： 1、过滤所有注释。 2、去掉SQL文本前后的空格,TAB等字符。注意，是文本前面和后面的。中间的不会被去掉。 下面的三条SQL里，因为SELECT大小写的关系，最后一条和其他两条在QC里肯定是用的不一样的存储位置。而第一条和第二条，区别在于后者有个 注释，在不同客户端，会有不一样的结果。所以，保险起见，请尽量不要使用动态的注释。在PHP的mysql扩展里，SQL的注释是不会被去掉的。也就是三 条SQL会被存储在三个不同的缓存里，虽然它们的结果都是一样的。 select * FROM people where name=’surfchen’; select * FROM people where /*hey~*/name=’surfchen’; SELECT * FROM people where name=’surfchen’; 目前只有select语句会被cache，其他类似show,use的语句则不会被cache。 因为QC是如此前端，如此简单的一个缓存系统，所以如果一个表被更新，那么和这个表相关的SQL的所有QC都会被失效。假设一个联合查询里涉及到了 表A和表B，如果表A或者表B的其中一个被更新（update或者delete），这个查询的QC将会失效。 也就是说，如果一个表被频繁更新，那么就要考虑清楚究竟是否应该对相关的一些SQL进行QC了。一个被频繁更新的表如果被应用了QC，可能会加重数 据库的负担，而不是减轻负担。一般的做法是默认打开QC，而对一些涉及频繁更新的表的SQL语句加上SQL_NO_CACHE关键词来对其禁用 CACHE。这样可以尽可能避免不必要的内存操作，尽可能保持内存的连续性。 那些查询很分散的SQL语句，也不应该使用QC。例如用来查询用户和密码的语句——“select pass from user where name=’surfchen’”。这样的语句，在一个系统里，很有可能只在一个用户登陆的时候被使用。每个用户的登陆所用到的查询，都是不一样的SQL 文本，QC在这里就几乎不起作用了，因为缓存的数据几乎是不会被用到的，它们只会在内存里占地方。 存储块 在本节里“存储块”和“block”是同一个意思。QC缓存一个查询结果的时候，一般情况下不是一次性地分配足够多的内存来缓存结果的。而是在查询 结果获得的过程中，逐块存储。当一个存储块被填满之后，一个新的存储块将会被创建，并分配内存（allocate）。 单个存储块的内存分配大小通过 query_cache_min_res_unit参数控制，默认为4KB。最后一个存储块，如果不能被全部利用，那么没使用的内存将会被释放。如果被缓 存的结果很大，那么会可能会导致分配内存操作太频繁，系统系能也随之下降；而如果被缓存的结果都很小，那么可能会导致内存碎片过多，这些碎片如果太小，就 很有可能不能再被分配使用。 除了查询结果需要存储块之外，每个SQL文本也需要一个存储块，而涉及到的表也需要一个存储块（表的存储块是所有线程共享的，每个表只需要一个存储 块）。存储块总数量=查询结果数量*2+涉及的数据库表数量。 也就是说，第一个缓存生成的时候，至少需要三个存储块：表信息存储块，SQL文本存储块，查 询结果存储块。而第二个查询如果用的是同一个表，那么最少只需要两个存储块：SQL文本存储块，查询结果存储块。 通过观察Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每个缓存结果占用的存储块。它们的 比例如果接近1:2，则说明当前的query_cache_min_res_unit参数已经足够大了。如果Qcache_total_blocks比 Qcache_queries_in_cache多很多，则需要增加query_cache_min_res_unit的大小。 Qcache_queries_in_cache*query_cache_min_res_unit（sql文本和表信息所在的block占用的 内存很小，可以忽略）如果远远大于query_cache_size-Qcache_free_memory，那么可以尝试减小 query_cache_min_res_unit的值。 关于MySQL QueryCache原理 ：调整大小 如果Qcache_lowmem_prunes增长迅速，意味着很多缓存因为内存不够而被释放，而不是因为相关表被更新。尝试加大 query_cache_size，尽量使Qcache_lowmem_prunes零增长。 启动参数 show variables [...]
alter table运行时会对原表进行临时复制，在副本上进行更改，然后删除原表，再对新表进行重命名。在执行alter table时，其它用户可以阅读原表，但是对表的更新和修改的操作将被延迟，直到新表生成为止。新表生成后，这些更新和修改信息会自动转移到新表上。 注 意，如果您在执行alter table时使用除了rename以外的选项，则MySQL会创建一个临时表。即使数据并不需要进行复制（例如当您更改列的名称时），MySQL也会这么 操作。对于Myisam表，您可以通过把myisam_sort_buffer_size系统变量设置到一个较高的值，来加快重新创建索引（该操作是变更 过程中速度最慢的一部分）的速度。 如果您使用alter table tbl_name rename to new_tbl_name并且没有其它选项，则MySQL只对与table tbl_name相对应的文件进行重命名。不需要创建一个临时表。 也 就是说我们在执行alter table操作时，会阻塞所有的dml操作，但可以进行select查询，这个和oracle有很大的不同。 mysql> select count(*) from test; +———-+ | count(*) | +———-+ | 2097152 | +———-+ 1 row in set (2.11 sec) mysql> alter table test add c varchar(100); Query OK, 2097152 rows affected (1 min 24.02 sec) –用了85s秒左右 Records: 2097152 [...]
无论是在小得可怜的免费数据库空间或是大型电子商务网站，合理的设计表结构、充分利用空间是十分必要的。这就要求我们对数据库系统的常用数据类型有 充分的认识。下面我就将我的一点心得写出来跟大家分享。 一、数字类型 数字类型按照我的分类方法分为三类：整数类、小数类和数字类。 我所谓的“数字类”，就是指DECIMAL和NUMERIC，它们是同一种类型。它严格的说不是一种数字类型，因为他们实际上是将数字以字符串形式 保存的；他的值的每一位（包括小数点）占一个字节的存储空间，因此这种类型耗费空间比较大。但是它的一个突出的优点是小数的位数固定，在运算中不会“失 真”，所以比较适合用于“价格”、“金额”这样对精度要求不高但准确度要求非常高的字段。 小数类，即浮点数类型，根据精度的不同，有FLOAT（单精度）和DOUBLE（双精度）两种。它们的优势是精确度，FLOAT可以表示绝对值非常 小、小到约 1.17E-38 （0.000…0117， 小数点后面有37个零）的小数，而DOUBLE更是可以表示绝对值小到约 2.22E-308 （0.000…0222， 小数点后面有307个零）的小数。FLOAT类型和DOUBLE类型占用存储空间分别是4字节和8字节。如果需要用到小数的字段，精度要求不高的，当然用 FLOAT了！可是说句实在话，我们“民用”的数据，哪有要求精度那么高的呢？这两种类型至今我没有用过——我还没有遇到适合于使用它们的事例。 用的最多的，最值得精打细算的，是整数类型。从只占一个字节存储空间的TINYINT到占8个字节的BIGINT，挑选一个“够用”并且占用存储空 间最小的类型是设计数据库时应该考虑的。TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT占用存储空间分别为1字节、2字 节、3字节、4字节和8字节，就无符号的整数而言，这些类型能表示的最大整数分别为255、65535、16777215、4294967295和 18446744073709551615。如果用来保存用户的年龄（举例来说，数据库中保存年龄是不可取的），用TINYINT就够了；九城的《纵横》 里，各项技能值，用SMALLINT也够了；如果要用作一个肯定不会超过16000000行的表的AUTO_INCREMENT的IDENTIFY字段， 当然用 MEDIUMINT 不用 INT ，试想，每行节约一个字节，16000000行可以节约10兆多呢！ 二、日期时间类型 日期和时间类型比较简单，无非是 DATE、TIME、DATETIME、TIMESTAMP和YEAR等几个类型。只对日期敏感，而对时间没有要求的字段，就用DATE而不用 DATETIME是不用说的了；单独使用时间的情况也时有发生——使用TIME；但最多用到的还是用DATETIME。在日期时间类型上没有什么文章可 做，这里就不再详述。 三、字符（串）类型 不要以为字符类型就是 CHAR ！CHAR和VARCHAR的区别在于CHAR是固定长度，只要你定义一个字段是CHAR(10),那么不论你存储的数据是否达到了10个字节，它都要占 去10个字节的空间；而VARVHAR则是可变长度的，如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的，VARCHAR 类型的实际长度是它的值的(实际长度+1)。为什么“+1”呢？这一个字节用于保存实际使用了多大的长度呀！从这个“+1”中也应该看到，如果一个字段， 它的可能值最长是10个字符，而多数情况下也就是用到了10个字符时，用VARCHAR就不合算了：因为在多数情况下，实际占用空间是11个字节，比用 CHAR(10)还多占用一个字节！ 举个例子，就是一个存储股票名称和代码的表，股票名称绝大部分是四个字的，即8个字节；股票代码，上海的是六位数字，深圳的是四位数字。这些都是固 定长度的，股票名称当然要用 CHAR(8) ；股票代码虽然是不固定长度，但如果使用VARVHAR(6)，一个深圳的股票代码实际占用空间是5个字节，而一个上海的股票代码要占用7个字节！考虑到 上海的股票数目比深圳的多，那么用VARCHAR(6)就不如CHAR(6)合算了。 虽然一个CHAR或VARVHAR的最大长度可以到255，我认为大于20的CHAR是几乎用不到的——很少有大于20个字节长度的固定长度的东东 吧？不是固定长度的就用VARCHAR！大于100的VARCHAR也是几乎用不到的——比这更大的用TEXT就好了。TINYTEXT，最大长度为 255，占用空间也是(实际长度+1)；TEXT，最大长度65535，占用空间是(实际长度+2)；MEDIUMTEXT，最大长度16777215， 占用空间是(实际长度+3)；LONGTEXT，最大长度4294967295，占用空间是(实际长度+4)。为什么 “+1”？“+2”？“+3”？“+4”？你要是还不知道就该打PP了。这些可以用在论坛啊、新闻啊，什么的，用来保存文章的正文。根据实际情况的不同， 选择从小到大的不同类型。 四、枚举和集合类型 枚举（ENUM）类型，最多可以定义65535种不同的字符串从中做出选择，只能并且必须选择其中一种，占用存储空间是一个或两个字节，由枚举值的 数目决定；集合（SET）类型，最多可以有64个成员，可以选择其中的零个到不限定的多个，占用存储空间是一个到八个字节，由集合可能的成员数目决定。 举个例子来说，在SQLServer中，你可以节约到用一个Bit类型来表示性别（男/女），但MySQL没有Bit，用TINTINT？不，可以 [...]
