Posts Tagged ‘Mysql’

【转】Mysql Explain 详解


18 7月
from IT技术博客大学习 by phpor
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
使用方法,在select语句前加上explain就可以了:
如:explain select * from test1
EXPLAIN列的解释:
table:显示这一行的数据是关于哪张表的
type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key: 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
rows:MYSQL认为必须检查的用来返回请求数据的行数
Extra:关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢
extra列返回的描述的意义
Distinct:一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
Not exists: MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了
Range checked for each Record(index map:#):没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort: 看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary 看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上
Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序)
system 表只有一行:system表。这是const连接类型的特殊情况
const:表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待
eq_ref:在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用
ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况
index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据)
ALL:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免
Mysql 的执行计划from IT技术博客大学习 by phpor1 person liked this - you标签:  explain mysql的执行计划: explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法,在select语句前加上explain就可以了: 如:explain select * from test1 EXPLAIN列的解释: table:显示这一行的数据是关于哪张表的 type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 key: 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引 key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好 ref:显示索引的哪一列被使用了,如果可能的话,是一个常数 rows:MYSQL认为必须检查的用来返回请求数据的行数 Extra:关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢 extra列返回的描述的意义 Distinct:一旦MYSQL找到了与行相联合匹配的行,就不再搜索了 Not exists: MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了 Range checked for each Record(index map:#):没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一 Using filesort: 看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行 Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候 Using temporary 看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上 Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序) system 表只有一行:system表。这是const连接类型的特殊情况 const:表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待 eq_ref:在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用 ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好 range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况 index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据) ALL:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免

【转】mysql sql 百万级数据库优化方案


08 6月
via IT技术博客大学习 by achao on 6/2/10
1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: select id from t where num=0 3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。 4.应尽量避免在 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 5.in 和 not in 也要慎用,否则会导致全表扫描,如: select id from t where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了: select id from t where num between 1 and 3 6.下面的查询也将导致全表扫描: select id from t where name like ‘%abc%’ 若要提高效率,可以考虑全文检索。 7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描: select id from t where num=@num <mailto:num=@num> 可以改为强制查询使用索引: select id from t with(index(索引名)) where num=@num <mailto:num=@num> 8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如: select id from t where num/2=100 应改为: select id from t where num=100*2 9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如: select id from t where substring(name,1,3)=’abc’-name以abc开头的id select id from t where datediff(day,createdate,’2005-11-30′)=0-‘2005-11-30’生成的id 应改为: select id from t where name like ‘abc%’ select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′ 10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。 11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。 12.不要写一些没有意义的查询,如需要生成一个空表结构: select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样: create table #t(…) 13.很多时候用 exists 代替 in 是一个好的选择: select num from a where num in(select num from b) 用下面的语句替换: select num from a where exists(select 1 from b where num=a.num) 14.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效 率起不了作用。 15.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。 16.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。 17.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。 18.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。 19.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。 20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。 21.避免频繁创建和删除临时表,以减少系统表资源的消耗。 22.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。 23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。 24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。 25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。 26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。 27.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。 28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。 29.尽量避免大事务操作,提高系统并发能力。 30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

Coreseek(Sphinx)中文分词设置,MySql默认编码设置&自动SET NAMES utf8


11 4月

——菜鸟 Linux 服务器搭建 随笔 连载之八

嗯,这两天休息在家,周六基本荒废掉了,不记得干了点什么,今天早上6点多莫名其妙醒了睡不着了,于是断断续续接着弄了点搜索引擎,结果过了会又睡着了,之后醒了就去老婆家陪她了,嗯,现在回家都弄好了,顺便记录一下,再睡一觉估计我就得忘光了,唉,脑子不灵光啊。。。 在开始写搜索引擎相关之前,先记录一段关于mysql编码的设置,现在一般编码全部是utf8了,而每次查询前set names utf8还是很浪费资源的,所以看了下,似乎set names utf8等于是: SET character_set_client = utf8; SET character_set_results = utf8; SET character_set_connection = utf8; 于是查了下,如何默认设置这几个东东utf8,只要在/etc/my.cnf中相应位置加入: [mysqld] default-character-set=utf8 [client] default-character-set=utf8 就可以了。ok,下面继续将搜索引擎,上面的mysql设置对coreseek也是很有用的,也是为用中文分词utf8时所准备的。 (注意,我所有的东西都以utf8为基础讲的,如果你还在用nc的gbk,我会无视你) P.S. 关于mysql的这些文末还有补充(2010/4/12)。 coreseek的中文分词设置很简单, 只需要修改/usr/local/coreseek/etc/csft.conf中的charset_type = zh_cn.utf-8, 同时必须添加一行charset_dictpath = /usr/local/coreseek/dict即可,这一行是字典文件的路径, 要用中文分词,必须设置这两行!缺一不可! 默认的字典文件在coreseek安装文件目录下的mmseg/data/中,复制其中的uni.lib到上面说的dict目录就好了。 如果像我一样索引时报了找不到mmseg.ini文件的错误的话,就复制mmseg安装目录中的src/win32/mmseg.ini到dict目录即可。 下面是自己做字典文件的方法,摘自coreseek官网: (比如使用搜狗的词库改造之类的) mmseg -u unigram.txt 该命令执行后,将会产生一个名为unigram.txt.uni的文件,将该文件改名为uni.lib,完成词典的构造。需要注意的是,unigram.txt 必须为UTF-8编码。 词典文件格式: .... 河 187 x:187 造假者 1 x:1 台北队 1 x:1 湖边 1 ...... 其中,每条记录分两行。其中,第一行为词项,其格式为:[词条]\t[词频率]。需要注意的是,对于单个字后面跟这个字作单字成词的频率,这个频率需要在大量的预先切分好的语料库中进行统计,用户增加或删除词时,一般不需要修改这个数值;对于非单字词,词频率处必须为1。第二行为占位项,是由于LibMMSeg库的代码是从Coreseek其他的分词算法库(N-gram模型)中改造而来的,在原来的应用中,第二行为该词在各种词性下的分布频率。LibMMSeg的用户只需要简单的在第二行处填"x:1"即可。 用户可以通过修改词典文件增加自己的自定义词,以提高分词法在某一具体领域的切分精度,系统默认的词典文件在data/unigram.txt中。 分词 mmseg -d tobe_segment.txt 其中,命令使用‘-d’开关指定词库文件所在的位置,参数dict_dir为词库文件(uni.lib )所在的目录;tobe_segment.txt 为待切分的文本文件,必须为UTF-8编码。如果一切正确,mmseg会将切分结果以及所花费的时间显示到标准输出上。 查到个资料表明,如果要合并某个词库到默认词库的话,直接按格式贴到后面还不够,还必须去掉重复的项,否则会报错,所以明天可能会写个程序合并下默认词库和搜狗词库试试,具体代码(如果有的话)到时候再贴。 下面贴一段暂时没用到,以后可能有用的东东,也是上面自制字典同一个位置的内容: 对特殊短语的支持 由于LibMMSeg是为Sphinx全文搜索引擎设计的,因此其内置了部分搜索引擎切分算法的特性,主要表现在对特殊短语的支持上。 在搜索引擎中,需要处理C++时,如果分词器中没有词组C++,则将被切分为C/x +/x +/x,在进一步的检索中,可能每个词会由于出现的过于频繁而被过滤掉,导致搜索的结果与C++相关度不高不说,也严重影响的全文搜索的速度。在LibMMSeg中,内置对特殊短语的支持。 其输入文件格式如下 // test commit .net => dotnet c# => csharp c++ => cplusplus 其中左侧是待支持的特殊短语,右侧是左侧的特殊短语需要被转换为的短语。这一转换在分词前进行。 可以在行的开头加入'//'作为注释符号,发现符号'//'后,整行将被忽略。 特殊短语词库构造命令: mmseg -b exceptions.txt 其中, 开关'-b'指示mmseg是要构造特殊短语词库;exceptions.txt是用户编辑的特殊短语转换规则。 该命令执行后,将在当前目录下产生一个名为"synonyms.dat"的文件,将该文件放在"uni.lib"同一目录下,分词系统将自动启动特殊短语转换功能。 注意: 1、在启用了该功能后,如果分词系统发现了一个特殊短语,将直接输出其在右侧对应的替换的值; 2、右侧被替换的值,请保证不会被分词器进行切分。(eg. C++ => C# 这个转换的意义不大,并且可能导致C++这个短语永远无法被检索到!) 嗯,暂时就这么多吧。目前为止,中文搜索似乎是ok了,但是用putty直接运行search的时候,返回的中文会是乱码,不知道原因ing,可能用php去调会好,后续有情况再记录。 明天争取合并了词库,并且完成用php查询的实验,如果能完成增量索引的概念则更佳,^ ^ That's all for today. ————————2010/4/12分割线———————— 今天到公司尝试了下php的api之类的,昨天mysql的编码问题其实并没有彻底解决,发现csft.conf里面还是加了 sql_query_pre = SET NAMES utf8 才能正常读中文的东东,于是查了些资料,参考了下公司的mysql设置, 发现,原来mysql4.1.2开始支持一个叫init_connect的参数了,也就是默认链接时运行的语句,只要 [mysqld] init_connect='SET NAMES utf8' 就可以了, 但是,必须注意的是,如果链接mysql的用户是super权限的话,这个设置是无效的!!! 希望和我一样偷懒用root链接的朋友注意了。csft.conf 中换了普通权限的用户后,一切正常了,昨天所有乱码问题全部迎刃而解了。^ ^

Zend Server,http.conf,Mysql,Chive


05 4月

——菜鸟 Linux 服务器搭建 随笔 连载之六

清明休息这几天,断断续续弄了一点,嗯,上次之后装了zend server,挺顺利的,不过因为装fedora系统的时候,选择了server里面的apache和php等,于是装完zend server就有2个地方有相应的程序和配置文件了,于是作为一个超级纠结有洁癖的NC小孩,我重开了一个新的fedora虚拟机,这次没选自带的apache,嗯,第N次装好zend server和svn等等配置,很顺利,这下完美了。不过偷了个懒,svnserve直接用root跑了,貌似方便许多,反正自己机器上的虚机就自己用而已。顺便提一句,zend server跑apache似乎是用daemon这个用户的,所以我把/home下的目录都归daemon了。

一开始还用zend server目录里的apache程序去启动服务,还在想怎么启动zend的那些gui等等,后来发现,原来 /usr/local/zend/bin/zendctl.sh 这个东东可以直接批量启动zend server相关的一切服务,用法一样,直接后面跟start之类的就可以了。

之后就是设置http.conf了,在zend server的apache conf目录里面,除了http.conf外,下面还有个extra目录,里面是一些可以被http.conf引用的额外配置文件,嗯,我搞了下vhost那个,虚拟主机还是需要的,贴个示例以备记录:

<VirtualHost *:80> ServerAdmin m@jjw.in DocumentRoot "/home/chive" ServerName chive.jjw.in ErrorLog "logs/chive-error_log" CustomLog "logs/chive-access_log" common </VirtualHost>

接下来是mysql了,一开始找了半天mysqld,运行了下,不过酱紫每次启动去运行似乎不太对,鬼使神差的找到了传说中的chkconfig命令,嗯,似乎只要chkconfig mysqld on,就可以开机直接启动了。

然后这次用烦了phpmyadmin,就想看看有没有其他选择,于是找到了chive这个东东,似乎比phpmyadmin强大一些,界面漂亮不少,然后可以高亮sql语句的,还可以直接在界面看到触发器和存储过程,似乎很实用,这东西很容易装,不多赘述了。

Ok,这些天差不多就是这些了,嗯,基本的环境差不多了,没怎么深入,接下来看要先进行什么了。

Todo:1. 搜索引擎   2. APE   3.Livezilla  4. 新的ZF架构

貓熊寶寶幼齒園

姜經緯的博客