mysql

MySQL查询优化技术系列讲座之使用索引

八 14th

由admin发表在DataBase

没有评论

MySQL查询优化技术系列讲座之使用索引

　　索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用，但是一般来说引起最大性能差异的都是索引的正确使用。在MySQL邮件列表中，人们经常询问那些让查询运行得更快的方法。在大多数情况下，我们应该怀疑数据表上有没有索引，并且通常在添加索引之后立即解决了问题。当然，并不总是这样简单就可以解决问题的，因为优化技术本来就并非总是简单的。然而，如果没有使用索引，在很多情况下，你试图使用其它的方法来提高性能都是在浪费时间。首先使用索引来获取最大的性能提高，接着再看其它的技术是否有用。

　　这一部分讲述了索引是什么以及索引是怎么样提高查询性能的。它还讨论了在某些环境中索引可能降低性能，并为你明智地选择数据表的索引提供了一些指导方针。在下一部分中我们将讨论MySQL查询优化器，它试图找到执行查询的效率最高的方法。了解一些优化器的知识，作为对如何建立索引的补充，对我们是有好处的，因为这样你才能更好地利用自己所建立的索引。某些编写查询的方法实际上让索引不起作用，在一般情况下你应该避免这种情形的发生。

　　索引的优点

　　让我们开始了解索引是如何工作的，首先有一个不带索引的数据表。不带索引的表仅仅是一个无序的数据行集合。例如，图1显示的ad表就是不带索引的表，因此如果需要查找某个特定的公司，就必须检查表中的每个数据行看它是否与目标值相匹配。这会导致一次完全的数据表扫描，这个过程会很慢，如果这个表很大，但是只包含少量的符合条件的记录，那么效率会非常低。


图1：无索引的ad表

　　图2是同样的一张数据表，但是增加了对ad表的company_num数据列的索引。这个索引包含了ad表中的每个数据行的条目，但是索引的条目是按照company_num值排序的。现在，我们不是逐行查看以搜寻匹配的数据项，而是使用索引。假设我们查找公司13的所有数据行。我们开始扫描索引并找到了该公司的三个值。接着我们碰到了公司14的索引值，它比我们正在搜寻的值大。索引值是排过序的，因此当我们读取了包含14的索引记录的时候，我们就知道再也不会有更多的匹配记录，可以结束查询操作了。因此使用索引获得的功效是：我们找到了匹配的数据行在哪儿终止，并能够忽略其它的数据行。另一个功效来自使用定位算法查找第一条匹配的条目，而不需要从索引头开始执行线性扫描（例如，二分搜索就比线性扫描要快一些）。通过使用这种方法，我们可以快速地定位第一个匹配的值，节省了大量的搜索时间。数据库使用了多种技术来快速地定位索引值，但是在本文中我们不关心这些技术。重点是它们能够实现，并且索引是个好东西。


图2：索引后的ad表

　　你可能要问，我们为什么不对数据行进行排序从而省掉索引？这样不是也能实现同样的搜索速度的改善吗？是的，如果表只有一个索引，这样做也可能达到相同的效果。但是你可能添加第二个索引，那么就无法一次使用两种不同方法对数据行进行排序了（例如，你可能希望在顾客名称上建立一个索引，在顾客ID号或电话号码上建立另外一个索引）。把与数据行相分离的条目作为索引解决了这个问题，允许我们创建多个索引。此外，索引中的行一般也比数据行短一些。当你插入或删除新的值的时候，移动较短的索引值比移动较长数据行的排序次序更加容易。

　　不同的MySQL存储引擎的索引实现的具体细节信息是不同的。例如，对于MyISAM数据表，该表的数据行保存在一个数据文件中，索引值保存在索引文件中。一个数据表上可能有多个索引，但是它们都被存储在同一个索引文件中。索引文件中的每个索引都包含一个排序的键记录（它用于快速地访问数据文件）数组。

　　与此形成对照的是，BDB和InnoDB存储引擎没有使用这种方法来分离数据行和索引值，尽管它们也把索引作为排序后的值集合进行操作。在默认情况下，BDB引擎使用单个文件存储数据和索引值。InnoDB使用单个数据表空间（tablespace），在表空间中管理所有InnoDB表的数据和索引存储。我们可以把InnoDB配置为每个表都在自己的表空间中创建，但是即使是这样，数据表的数据和索引也存储在同一个表空间文件中。
前面的讨论描述了单个表查询环境下的索引的优点，在这种情况下，通过减少对整个表的扫描，使用索引明显地提高了搜索的速度。当你运行涉及多表联结（jion）查询的时候，索引的价值就更高了。在单表查询中，你需要在每个数据列上检查的值的数量是表中数据行的数量。在多表查询中，这个数量可能大幅度上升，因为这个数量是这些表中数据行的数量所产生的。

　　假设你拥有三个未索引的表t1、t2和t3，每个表都分别包含数据列i1、i2和i3，并且每个表都包含了1000条数据行，其序号从1到1000。查找某些值匹配的数据行组合的查询可能如下所示：

SELECT t1.i1, t2.i2, t3.i3
FROM t1, t2, t3
WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;

　　这个查询的结果应该是1000行，每个数据行包含三个相等的值。如果在没有索引的情况下处理这个查询，那么如果我们不对这些表进行全部地扫描，我们是没有办法知道哪些数据行含有哪些值的。因此你必须尝试所有的组合来查找符合WHERE条件的记录。可能的组合的数量是1000 x 1000 x 1000（10亿！），它是匹配记录的数量的一百万倍。这就浪费了大量的工作。这个例子显示，如果没有使用索引，随着表的记录不断增长，处理这些表的联结所花费的时间增长得更快，导致性能很差。我们可以通过索引这些数据表来显著地提高速度，因为索引让查询采用如下所示的方式来处理：

　　1．选择表t1中的第一行并查看该数据行的值。

　　2．使用表t2上的索引，直接定位到与t1的值匹配的数据行。类似地，使用表t3上的索引，直接定位到与表t2的值匹配的数据行。

　　3．处理表t1的下一行并重复前面的过程。执行这样的操作直到t1中的所有数据行都被检查过。

　　在这种情况下，我们仍然对表t1执行了完整的扫描，但是我们可以在t2和t3上执行索引查找，从这些表中直接地获取数据行。理论上采用这种方式运行上面的查询会快一百万倍。当然这个例子是为了得出结论来人为建立的。然而，它解决的问题却是现实的，给没有索引的表添加索引通常会获得惊人的性能提高。

　　MySQL有几种使用索引的方式：

　　· 如上所述，索引被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。

　　· 对于使用了MIN()或MAX()函数的查询，索引数据列中最小或最大值可以很快地找到，不用检查每个数据行。

　　· MySQL利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。

　　· 有时候MySQL会利用索引来读取查询得到的所有信息。假设你选择了MyISAM表中的被索引的数值列，那么就不需要从该数据表中选择其它的数据列。在这种情况下，MySQL从索引文件中读取索引值，它所得到的值与读取数据文件得到的值是相同的。没有必要两次读取相同的值，因此没有必要考虑数据文件。

　　索引的代价

　　一般来说，如果MySQL能够找到方法，利用索引来更快地处理查询，它就会这样做。这意味着，对于大多数情况，如果你没有对表进行索引，就会使性能受到损害。这就是我所描绘的索引优点的美景。但是它有缺点吗？有的，它在时间和空间上都有开销。在实践中，索引的优点的价值一般会超过这些缺点，但是你也应该知道到底有一些什么缺点。

　　首先，索引加快了检索的速度，但是减慢了插入和删除的速度，同时还减慢了更新被索引的数据列中的值的速度。也就是说，索引减慢了大多数涉及写操作的速度。发生这种现象的原因在于写入一条记录的时候不但需要写入数据行，还需要改变所有的索引。数据表带有的索引越多，需要做出的修改就越多，平均性能的降低程度也就越大。在本文的"高效率载入数据"部分中，我们将更细致地了解这些现象并找出处理方法。

　　其次，索引会花费磁盘空间，多个索引相应地花费更多的磁盘空间。这可能导致更快地到达数据表的大小限制：

　　· 对于MyISAM表，频繁地索引可能引起索引文件比数据文件更快地达到最大限制。

　　· 对于BDB表，它把数据和索引值一起存储在同一个文件中，添加索引引起这种表更快地达到最大文件限制。

　　· 在InnoDB的共享表空间中分配的所有表都竞争使用相同的公共空间池，因此添加索引会更快地耗尽表空间中的存储。但是，与MyISAM和BDB表使用的文件不同，InnoDB共享表空间并不受操作系统的文件大小限制，因为我们可以把它配置成使用多个文件。只要有额外的磁盘空间，你就可以通过添加新组件来扩展表空间。

　　使用单独表空间的InnoDB表与BDB表受到的约束是一样的，因为它的数据和索引值都存储在单个文件中。

　　这些要素的实际含义是：如果你不需要使用特殊的索引帮助查询执行得更快，就不要建立索引。

　　选择索引

　　假设你已经知道了建立索引的语法，但是语法不会告诉你数据表应该如何索引。这要求我们考虑数据表的使用方式。这一部分指导你如何识别出用于索引的备选数据列，以及如何最好地建立索引：

　　用于搜索、排序和分组的索引数据列并不仅仅是用于输出显示的。换句话说，用于索引的最好的备选数据列是那些出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。仅仅出现在SELECT关键字后面的输出数据列列表中的数据列不是很好的备选列：

SELECT
col_a <- 不是备选列
FROM
tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c <- 备选列
WHERE
col_d = expr; <- 备选列

　　当然，显示的数据列与WHERE子句中使用的数据列也可能相同。我们的观点是输出列表中的数据列本质上不是用于索引的很好的备选列。

　　Join子句或WHERE子句中类似col1 = col2形式的表达式中的数据列都是特别好的索引备选列。前面显示的查询中的col_b和col_c就是这样的例子。如果MySQL能够利用联结列来优化查询，它一定会通过减少整表扫描来大幅度减少潜在的表－行组合。

　　考虑数据列的基数（cardinality）。基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是4。索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数据列用于记录性别（只有"M"和"F"两种值），那么索引的用处就不大。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是"30%"。现在查询优化器更加复杂，把其它一些因素也考虑进去了，因此这个百分比并不是MySQL决定选择使用扫描还是索引的唯一因素。

　　索引较短的值。尽可能地使用较小的数据类型。例如，如果MEDIUMINT足够保存你需要存储的值，就不要使用BIGINT数据列。如果你的值不会长于25个字符，就不要使用CHAR(100)。较小的值通过几个方面改善了索引的处理速度：

　　· 较短的值可以更快地进行比较，因此索引的查找速度更快了。

　　· 较小的值导致较小的索引，需要更少的磁盘I/O。

　　· 使用较短的键值的时候，键缓存中的索引块（block）可以保存更多的键值。MySQL可以在内存中一次保持更多的键，在不需要从磁盘读取额外的索引块的情况下，提高键值定位的可能性。

　　对于InnoDB和BDB等使用聚簇索引（clustered index）的存储引擎来说，保持主键（primary key）短小的优势更突出。聚簇索引中数据行和主键值存储在一起（聚簇在一起）。其它的索引都是次级索引；它们存储主键值和次级索引值。次级索引屈从主键值，它们被用于定位数据行。这暗示主键值都被复制到每个次级索引中，因此如果主键值很长，每个次级索引就需要更多的额外空间。

　　索引字符串值的前缀（prefixe）。如果你需要索引一个字符串数据列，那么最好在任何适当的情况下都应该指定前缀长度。例如，如果有CHAR(200)数据列，如果前面10个或20个字符都不同，就不要索引整个数据列。索引前面10个或20个字符会节省大量的空间，并且可能使你的查询速度更快。通过索引较短的值，你可以获得那些与比较速度和磁盘I/O节省相关的好处。当然你也需要利用常识。仅仅索引某个数据列的第一个字符串可能用处不大，因为如果这样操作，那么在索引中不会有太多的唯一值。

　　你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀。

　　使用最左（leftmost）前缀。建立多列复合索引的时候，你实际上建立了MySQL可以使用的多个索引。复合索引可以作为多个索引使用，因为索引中最左边的列集合都可以用于匹配数据行。这种列集合被称为"最左前缀"（它与索引某个列的前缀不同，那种索引把某个列的前面几个字符作为索引值）。

　　假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列，因此它们也会自动地按照state/city和state次序排列。这意味着，即使你在查询中只指定了state值，或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：

state, city, zip
state, city
state

　　MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照city或zip来搜索，就不会使用到这个索引。如果你搜索给定的state和具体的ZIP代码（索引的1和3列），该索引也是不能用于这种组合值的，尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围。

　　不要过多地索引。不要认为"索引越多，性能越高"，不要对每个数据列都进行索引。我们在前面提到过，每个额外的索引都会花费更多的磁盘空间，并降低写操作的性能。当你修改表的内容的时候，索引就必须被更新，甚至可能重新整理。如果你的索引很少使用或永不使用，你就没有必要减小表的修改操作的速度。此外，为检索操作生成执行计划的时候，MySQL会考虑索引。建立额外的索引会给查询优化器增加更多的工作量。如果索引太多，有可能（未必）出现MySQL选择最优索引失败的情况。维护自己必须的索引可以帮助查询优化器来避免这类错误。

　　如果你考虑给已经索引过的表添加索引，那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。如果是这样的，不用增加索引，因为已经有了（例如，如果你在state、city和zip上建立了索引，那么没有必要再增加state的索引）。

　　让索引类型与你所执行的比较的类型相匹配。在你建立索引的时候，大多数存储引擎会选择它们将使用的索引实现。例如，InnoDB通常使用B树索引。MySQL也使用B树索引，它只在三维数据类型上使用R树索引。但是，MEMORY存储引擎支持散列索引和B树索引，并允许你选择使用哪种索引。为了选择索引类型，需要考虑在索引数据列上将执行的比较操作类型：

　　· 对于散列（hash）索引，会在每个数据列值上应用散列函数。生成的结果散列值存储在索引中，并用于执行查询。散列函数实现的算法类似于为不同的输入值生成不同的散列值。使用散列值的好处是散列值比原始值的比较效率更高。散列索引用于执行=或<=>操作等精确匹配的时候速度非常快。但是对于查询一个值的范围效果就非常差了：

id < 30
weight BETWEEN 100 AND 150

　　· B树索引可以用于高效率地执行精确的或者基于范围（使用操作<、<=、=、>=、>、<>、!=和BETWEEN）的比较。B树索引也可以用于LIKE模式匹配，前提是该模式以文字串而不是通配符开头。

　　如果你使用的MEMORY数据表只进行精确值查询，散列索引是很好的选择。这是MEMORY表使用的默认的索引类型，因此你不需要特意指定。如果你希望在MEMORY表上执行基于范围的比较，应该使用B树索引。为了指定这种索引类型，需要给索引定义添加USING BTREE。例如：

CREATE TABLE lookup
(
id INT NOT NULL,
name CHAR(20),
PRIMARY KEY USING BTREE (id)
) ENGINE = MEMORY;

　　如果你希望执行的语句的类型允许，单个MEMORY表可以同时拥有散列索引和B树索引，即使在同一个数据列上。

　　有些类型的比较不能使用索引。如果你只是通过把值传递到函数（例如STRCMP()）中来执行比较操作，那么对它进行索引就没有价值。服务器必须计算出每个数据行的函数值，它会排除数据列上索引的使用。

　　使用慢查询（slow-query）日志来识别执行情况较差的查询。这个日志可以帮助你找出从索引中受益的查询。你可以直接查看日志（它是文本文件），或者使用mysqldumpslow工具来统计它的内容。如果某个给定的查询多次出现在"慢查询"日志中，这就是一个线索，某个查询可能没有优化编写。你可以重新编写它，使它运行得更快。你要记住，在评估"慢查询"日志的时候，"慢"是根据实际时间测定的，在负载较大的服务器上"慢查询"日志中出现的查询会多一些。

,

MySQL查询中的分页思路的优化

八 14th

由admin发表在DataBase

没有评论

MySQL 查询中的分页思路的优化


作者:steeven 
 似乎讨论分页的人很少，难道大家都沉迷于limit m,n?
在有索引的情况下，limit m,n速度足够，可是在复杂条件搜索时，
where somthing order by somefield+somefield
mysql会搜遍数据库，找出“所有”符合条件的记录，然后取出m,n条记录。
如果你的数据量有几十万条，用户又搜索一些很通俗的词，
然后要依次读最后几页重温旧梦。。。mysql该很悲壮的不停操作硬盘。

所以，可以试着让mysql也存储分页，当然要程序配合。
（这里只是提出一个设想，欢迎大家一起讨论）

ASP的分页：在ASP系统中有Recordset对象来实现分页，但是大量数据放在内存中，而且不知道什么时候才失效(请ASP高手
指点).
SQL数据库分页：用存储过程+游标方式分页，具体实现原理不是很清楚，设想如果用一次查询就得到需要的结果，或者是
id集，需要后续页时只要按照结果中的IDs读出相关记录。这样只要很小的空间保留本次查询的所有IDs. (SQL中的查询结
果不知道怎样清楚过期垃圾?)

这样，可以让mysql模拟存储分页机制：
1. select id from $table where $condition order by $field limit $max_pages*$count;
查询符合条件的IDs.
限定最大符合条件的记录数量，也可以不加。
2. 因为php在执行结束后所有变量都要lost，所以可以考虑:
方案a. 在mysql建立临时表，查询结果用一个时间或随机数作为唯一标志插入。
其中建立page1~pagen个字段，每个字段保存该页中需要的ids, 这样一个id对一条记录.
方案b. 如果打开session,也可以放在session中保存，实际上是放在文件中保存。
建立一个$IDs数组，$IDs[1]~$IDs[$max_pages]. 考虑到有时候用户会开几个
窗口同时查询，要为$ids做一个唯一标志，避免查询结果相互覆盖。二维数组
和$$var都是好办法。
3. 在每页页的请求中，直接找到对应的IDs，中间以","间隔：
select * from $table where id in ($ids); 速度绝对快
4. 收尾要考虑查询结果的自动清除，可以设置定时或者按比例随机清楚。如果用mysql临时表要加上一个时间标志字段，
session中要加入$IDs["time"]=time(); 在一定时间以后不操作视为过期数据。

5. 如果要优化，可以考虑用把1和2.a中的语句合并成select …… into ….

Note:
1.以上只是针对mysql的修补方案，希望mysql哪天能把这些功能加进去
2.其它数据库也可以套用。
3.如果其它数据库还有更先进的分页方式，请告诉我或mailto: steeven@kali.com.cn
4.如果真的有很多数据要查询，还是和mysql再见吧，sql,oracle都提供了更先进的关键词索引查询。

精益求精，以上只是抛砖引玉，欢迎共同探讨分页问题。（也可关于其它数据库）
希望有一天能把各种分页方式整理出来供新手参考。

,

MySQL索引分析和优化

八 14th

由admin发表在DataBase

没有评论

MySQL索引分析和优化


作者:松下客 来源:赛迪网
一、什么是索引？

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

假设我们创建了一个名为people的表：

 

CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );

然后，我们完全随机把1000个不同name值插入到people表。下图显示了people表所在数据文件的一小部分：

 

可以看到，在数据文件中name列没有任何明确的次序。如果我们创建了name列的索引，MySQL将在索引中排序name列：

 

对于索引中的每一项，MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此，如果我们要查找name等于“Mike”记录的peopleid（SQL命令为“SELECT peopleid FROM people WHERE name=\’Mike\’;”），MySQL能够在name的索引中查找“Mike”值，然后直接转到数据文件中相应的行，准确地返回该行的peopleid（999）。在这个过程中，MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引，MySQL要扫描数据文件中的所有记录，即1000个记录！显然，需要MySQL处理的记录数量越少，则它完成任务的速度就越快。

二、索引的类型

　　MySQL提供多种索引类型供选择：

 

  • 普通索引
    这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：
    • 创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);
    • 修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
    • 创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

     

  • 唯一性索引
    这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：
    • 创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
    • 修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
    • 创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );

     

  • 主键
    主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列，你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

     

  • 全文索引
    MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中，全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引，要了解更多信息，请参见MySQL documentation。

三、单列索引与多列索引

索引可以是单列索引，也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样一个people表：

 

ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);

由于索引文件以B-树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！

那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

四、最左前缀

多列索引还有另外一个优点，它通过称为最左前缀（Leftmost Prefixing）的概念体现出来。继续考虑前面的例子，现在我们有一个firstname、lastname、age列上的多列索引，我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时，MySQL将使用fname_lname_age索引：

 

  • firstname，lastname，age
  • firstname，lastname
  • firstname

从另一方面理解，它相当于我们创建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)这些列组合上的索引。下面这些查询都能够使用这个fname_lname_age索引：

 


table type possible_keys key key_len ref rows Extra
people ref fname_lname_age fname_lname_age 102 const,const,const 1 Where used

下面我们就来看看这个EXPLAIN分析结果的含义。

 

  • table：这是表的名字。
  • type：连接操作的类型。下面是MySQL文档关于ref连接类型的说明：

    “对于每一种与另一个表中记录的组合，MySQL将从当前的表读取所有带有匹配索引值的记录。如果连接操作只使用键的最左前缀，或者如果键不是UNIQUE或PRIMARY KEY类型（换句话说，如果连接操作不能根据键值选择出唯一行），则MySQL使用ref连接类型。如果连接操作所用的键只匹配少量的记录，则ref是一种好的连接类型。”

    在本例中，由于索引不是UNIQUE类型，ref是我们能够得到的最好连接类型。

    如果EXPLAIN显示连接类型是“ALL”，而且你并不想从表里面选择出大多数记录，那么MySQL的操作效率将非常低，因为它要扫描整个表。你可以加入更多的索引来解决这个问题。预知更多信息，请参见MySQL的手册说明。

  • possible_keys：
    可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称；如果索引没有昵称，则默认显示的是索引中第一个列的名字（在本例中，它是“firstname”）。默认索引名字的含义往往不是很明显。
  • Key：
    它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
  • key_len：
    索引中被使用部分的长度，以字节计。在本例中，key_len是102，其中firstname占50字节，lastname占50字节，age占2字节。如果MySQL只使用索引中的firstname部分，则key_len将是50。
  • ref：
    它显示的是列的名字（或单词“const”），MySQL将根据这些列来选择行。在本例中，MySQL根据三个常量选择行。
  • rows：
    MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。
  • Extra：
    这里可能出现许多不同的选项，其中大多数将对查询产生负面影响。在本例中，MySQL只是提醒我们它将用WHERE子句限制搜索结果集。

七、索引的缺点

到目前为止，我们讨论的都是索引的优点。事实上，索引也是有缺点的。

首先，索引要占用磁盘空间。通常情况下，这个问题不是很突出。但是，如果你创建每一种可能列组合的索引，索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表，索引文件的大小可能达到操作系统允许的最大文件限制。

第二，对于需要写入数据的操作，比如DELETE、UPDATE以及INSERT操作，索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件，而且它还要把这些改动写入索引文件。

【结束语】在大型数据库中，索引是提高速度的一个关键因素。不管表的结构是多么简单，一次500000行的表扫描操作无论如何不会快。如果你的网站上也有这种大规模的表，那么你确实应该花些时间去分析可以采用哪些索引，并考虑是否可以改写查询以优化应用。要了解更多信息，请参见MySQL manual。另外注意，本文假定你所使用的MySQL是3.23版，部分查询不能在3.22版MySQL上执行。

,

如何在windows系统下安装MySQL

五 30th

由admin发表在DataBase

没有评论

可以运行在本地windows版本的MySQL数据库程序自从3.21版以后已经可以从MySQL AB公司获得,而且 MYSQL每日的下载百分比非常大.这部分描述在windows上安装MySQL的过程. 安装程序是针对windows版本的MySQL 5.0,结合了图形安装向导,自动的安装MySQL,创建 一个配置文件,启动服务器,和保护默认的用户帐户. 如果你是升级安装现有的MySQL 4.1.5版本.你必须完成下列步骤:

1. 获得和安装 2. 如果有必要安装配置文件 3. 选择一个想要使用的服务器. 4. 启动服务器 5. 为MYSQL帐户设置密码. 这个过程在没有安装配置的myslq安装程序里完成.

MySQL 5.0 for Windows 可用的种格式:

· 包含二进制的安装程序可以安装所有你需要的依次你可以立即启动服务器. · 原始类包括所有代码和支持文件建造执行使用Visual Studio 2003编译系统.

一般来讲,你应该使用二进制形式.较其他比较简单,和你不需要额外的工具让mysql使用 和运行. 本文主要描述怎样在windows上安装使用二进制格式的MYSQL.

一、要在WINDOWS上运行MYSQL,你要具备下列条件:

· 一个32位WINDOWS操作系统,9x,ME,NT,2000,XP,或者 windows server2003.基于 WINDOWS NT操作系统(NT,2000,XP,2003)允许你运行MYSQL 服务器为一个服务.使用基于 WINDOWS NT操作系统是我们强烈推荐的.

· TCP/IP协议支持. · 二进制版本的MYSQL for windows,可以从 http://dev.mysql.com/downloads/下 载。注意: 如果你通过FTP下载.我们推荐使用适当的FTP软件很重要.避免在现在过程中文件 损坏..

· 一个可以打开.zip 文件的工具，用来打开安装文件

· 硬盘的空间推荐最小200M. 如果准备通过ODBC连接MYSQL,你也需要ODBC连接驱动

· 你过你需要的表大于4GB,安装MYSQL在NTFS或者新的文件系统上.当你创建表的时不 要忘记使用 MAX-ROWS和AVG_ROW_LENGTH.

二、选择一个安装组件

对于MYSQL 5.0,有三个组件用来选择安装MYSQL在WINDOWS上.下列是组件:

· 基本组件:这个组件有一个类似于mysql-essential-5.0.13-rc-win32.msi和包含最 小需求文件组件不包含可选组件与内含的服务器和基准组件

· 全部组件:这个组件有一个文件名类似 mysql-5.0.13-rc-win32.zip和包含所有可需 文件来完成WINDOWS安装.包括配置向导.这个组件包括可选组件与内含的服务器和基准 组件

· 无安装存档:. 这个组件有一个类似于mysql-noinstall-5.0.13-rc-win32.zip的文 件名和包含建立完成安装组件的文件.配置向导除外.这个组件不包含自动安装,必须手 动安装和配置.大多数拥护推荐使用基本组件.

三、使用配置向导

MySQL配置向导可以帮助您实现WINDOWS系统下的服务器自动配置。MySQL配置向导首先会根据一一系列的问题创 建一个定制好的my.ini文件，然后根据填写内容并将其应用到模板中从而产生一个跟安装相适应的my.ini文件 。配置向导包含在MySQL 5.0服务器中，现在只适合WINDOWS用户。配置向导很大程度上来源于用户近几年在My SQL AB上的反馈信息。如果你觉得向导内容缺少你所需要的重要内 容，或者，你发现有错误，请通过MySQL 错误报告系统要求添加新特性或报告错误信息。

安装完毕后可以直接进入配置向导或通过点击WINDOWS开始程序中的MySQL服务器实例向导进入MySQL配置向导。 另外，也可以找到MySQL安装目录下的bin目录下的MySQLInstanceConfig.exe文件并直接运行它。

如果你的MySQL配置向导发现了一个已经存在的my.ini文件，你可以重新配置已经存在的服务器，或者通过删除 my.ini文件、停止并移除MySQL服务的方法移除服务器实例。重新配置已经存在的服务器，选择“重新配置实例”选项并且选择“下一步”按钮。已经存在的my.ini文件被 重新命名为mytimestamp.ini.bak，时间戳是已经存在的my.ini创建时的日期和时间。移除已经存在的数据库实 例，选择“移除实例”选项并选择“下一步”按钮。如果选择了“移除实例”选项，进入到确认界面。单击“运行”按钮：MySQL配置向导停止并开始移除MySQL服 务，并删除my.ini文件。但服务器安装目录并没有移除。如果选择了“重新配置实例”选项，进入到“配置类型”界面，可以选择安装想要配置的安装类型。

选择了MySQL配置向导的 “安装MySQL”，或“重新配置实例”，都将进入到“配置类型”界面有两个配置类型可供选择：详细配置和标准配置。标准配置选项是为那些不需要考虑复杂服务器配置的新手而 设计的。详细配置选项是给那些能更高好的操作服务器的高级用户使用的。如果您刚开始使用MySQL，并且把它做为一个单一用户的开发机器使用的话，标准配置能满足您的需求。选择标 准配置后，除了服务选项和安全选项外配置向导对其他部分都做了自动配置。如果你的机器中有已经安装了MySQL，标准配置可能不太适合你的系统。如果愿意进一步配置的话，“详细配置 ”选项是推荐的。

然后进入服务器类型页面，有三种不同的服务器类型可供选择，选择服务器类型后配置向导将会根据选择的不同确定内存、磁盘、处理器 的使用。

• 开发版：这个选项是典型的桌面工作站，是MySQL为个人使用而设计的。有很多其他的桌面应用程序也 可以在此上运行。MySQL服务器使用了最小的系统资源。

• 服务器版：选择此选项MySQL可以和其他一些服务器应用程序如FTP，email，Web服务同时运行。MySQL 服务器配置使用了中等程度的系统资源。

• MySQL服务器专业版：这个选项是只能运行MySQL服务器，其他应用程序不可以运行。MySQL服务器配置 将使用所有可用资源。

“数据库使用”界面允许在创建MySQL表时可以指示想使用的表操作。选择项将决定InnoDB存储引擎是否可用， InnoDB所能使用系统资源的百分比。多功能的数据库：此选项InnoDB 和 MyISAM存储引擎可用，并且在两者之间平均分配资源。推荐在通常情况下 会使用这两个存储引擎的的用户使用。事务型数据库：此选项InnoDB 和 MyISAM存储引擎均可用，但更倾向于系统大部门资源为InnoDB存储引擎所使 用。推荐经常使用InnoDB而很少使用MyISAM的用户使用。

• 非事务型数据库：此选项完全禁止InnoDB存储引擎，完全专注于MyISAM存储引擎。推荐不使用InnoDB 的用户使用。

InnoDB表空间界面

有些用户更愿意将InnoDB表空间文件放在不同的位置而不是MySQL服务器数据目录。如果系统有更高的存储能力 或更高性能的存储设备可用，比如RAID存储系统，可以把表空间文件放置在不同的位置。可以修改InnoDB表空间文件的默认位置，从下拉列表框的盘符列表中选择一个并选择一个新的路径。创建定制 的路径，单击“…”。 如果修改已经存在的服务器配置，在修改路径之前必须单击“修改”按钮。在启动服务器之前必须先手工把已 经存在的表空间文件移到新的位置。

并发连接界面

控制连接到MySQL服务器的并发连接数目能有效的防止耗进系统资源。并发连接界面允许选择服务器按所需选择 服务器的使用情况， 并同时设置并发连接限制。也可以手工设定并发连接数。

• 决策支持（DSS）/联机分析处理（OLAP）：服务器不要求大数目的并发连接时选择此项。最大连接数 目的连接可达100，但平均会有20个并发连接。

• 在线事务处理（OLTP）：服务器要求大数量的并发连接时选择此项。最大连接数可达500。

• 手工设置：选择此项可以手工设置并发访问服务器的最大树木。从下拉列表框中选择最大可连接数， 如果下拉列表框中列出的数目没有可选择的。可手工输入最大连接数目。

网络配置选项对话框

可以使用网络配置对话框来激活或者禁止TCP/IP网络服务同时配置和MySQL服务器连接的端口号。TCP/IP网络服 务是默认激活的。Windows可以通过不选择TCP/IP网络选项的选项框禁止TCP/IP网络服务。通常默认使用的端口 是3306。如果需要改变访问MySQL的端口，可以从下拉选项框中选取新的端口号或者在选项框中直接输入新的端 口号。如果你选定的端口号已经被使用，系统将会提示你去确认对端口号的选择。

字体设置对话框

MySQL服务器支持多字体设置，同时可以为服务器设置默认服务字体，该字体可以应用于所有的数据表、列和未 被超级控制的数据库。可以使用字体设置对话框来改变MySQL服务器的默认字体设置。

1 标准的字体设置：该选项用于将Latin1作为默认的服务字体. Latin1可以用于英文和众多西文字体。

2 对于多语言字体的最佳支持:该选项用于UTF8作为默认的服务字体。在单一的字体设置中，UTF8可以存储来自 很多不同语言的字体。

3 手动选择默认字符设置/排序：该选项用于手动设置默认字体。你可以从下拉选择框所提供的字体中选择所需 要的字符。

服务选择对话框

在基于Windows NT的平台上，MySQL服务器可以被作为Windows的一项服务被安装。当作为一项服务安装时， MySQL服务器可以随着系统的启动而自启动，并且在服务失败时，MySQL服务器可以自动重起。在MySQL的默认配置中，将MySQL服务器作为服务安装，同时该服务的名称为MySQL。如果你不希望安装该项服务 ，不选择作为Windows服务的选择框即可。你可以从所提供的下拉选择框中选取一个新的服务名称或者直接在下 拉选择框中输入新的服务名称。如果需要将MySQL服务器作为一项服务来安装，同时使MySQL不随着系统启动而自启动，则不选择自启动Mysql服 务选择框。

安全选择对话框

强烈推荐你为你的MySQL服务器设置一个相应的根用户密码，同时MySQL配置向导需要你设置一个默认的根用户 密码。如果你不希望设置根用户密码，则不要选择修改安全设置选项。为了设置根用户密码，需要将设定的根 用户密码同时输入新根密码和确认选择框里。如果你需要重新配置一个已存在的服务器，你需要将其的根用户 密码输入到当前的根密码选择框里。为了防止通过网络的根用户登录，可以在Root的选择框中设置为只允许从本地连接。这将增强你的根用户的安 全性。为了生成一个匿名用户帐户，选择生成匿名帐户的选择框。生成一个匿名用户帐号可能会降低服务器的安全性 ，同时造成登陆和认证的困难。出于这种原因，该选项一般不被推荐。

确认对话框

MySQL配置向导中的最后的对话框是确认对话框。可以点击“执行”按钮来启动配置操作。为了返回到前一个对 话框，可以点击“返回”按钮。可以点击“取消”按钮退出MySQL配置向导而无需配置服务器。当你点击“执行”按钮后而无需配置服务器，MySQL配置向导执行一系列任务，该任务在被执行时，执行进度将 会被显示在屏幕上。 MySQL配置向导首先使用由MySQL AB开发者和工程师所准备的模版配置文件来决定基于你的选择的配置文件选项 。该模版被命名为my-template.ini，同时位于你的服务期安装目录下。MySQL配置向导将这些选择项写入至 my.ini文件中。my.ini文件的最终位置将会被显示在写配置文件任务中。如果你选择为MySQL服务器生成一项服务，MySQL配置向导将会生成并启动该服务。如果你正在重新配置一个已 存在的服务，MySQL服务向导将会重起服务以重新应用改变的配置。如果你选择设置一个根密码，MySQL配置向导将会和服务器连接，设置你的新的根用户密码同时应用你已经选择 的其他安全设置。在MySQL配置向导完成其的任务后，一个摘要将会被显示出来。点击“结束”按钮退出MySQL配置向导。

配置文件my.ini的位置

MySQL配置向导将my.ini文件放置在MySQL服务器的安装目录中。这将有助于将配置文件和具体的服务器实例相 关联。为了保证MySQL服务器知道到哪里查找my.ini文件，和下面内容类似的参数将会被作为服务安装的一部分 传递给MySQL服务器：–defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini C:\Program Files\MySQL\MySQL Server 5.0可以被指向MySQL服务器的安装路径所代替。

编辑my.ini文件

可以使用文本编辑器打开该文件同时做出必要的编辑和修改。你也可以以MySQL Administrator的应用程序来修 改服务器配置。 MySQL客户端和应用程序，例如mysql命令行客户端和mysqldump并不能确定位于服务器安装目录中的my.ini文件 的位置。为了配置客户端和应用程序，根据你的Windows版本的不同，在C:\Windows下或者在C:\WINNT目录下生 成新的文件my.ini 文件

四、从Noinstall Zip Archive中安装MySQL

正在从Noinstall软件包安装MySQL的用户可以使用这个说明来手动安装MySQL。从Zip archive 中安装MySQL的 步骤如下：

1 在指定的安装目录下解压软件包。 2 制造选择文件。 3 选择MySQL服务类型。 4 开始MySQL服务。 5 保护默认账户。

解压安装软件包

为了手工安装MySQL,需要按以下步骤进行:

1 如果你需要对以前的版本更新，请参考在更新步骤刚开始时的文章 “Upgrading MySQL on Windows”。

2 如果你正在使用基于Windows NT的操作系统例如Windows NT, Windows 2000, Windows XP或者Windows Server 2003，必须保证你作为拥有管理员特权的用户登录该系统。

3 选择一个安装位置。传统的MySQL服务器安装在 C:\mysql，而MySQL安装向导将 MySQL 安装到 C:\Program Files\MySQL。如果不将 MySQL 安装到 C:\mysql下，你必须在启动或者在选择文件中具体指出安装路径。

4 选用压缩工具将安装压缩软件解压缩至选择安装的位置上。有些压缩工具有可能将其解压到你的被选择的安 装位置的子目录中。如果是这种情况，你可以将该子目录的内容从子目录移至安装路径下。

生成选择文件

如果你需要在运行服务器时，具体化启动选项，你可以在命令行标志出他们或者在一个选择文件中设置他们。 对于每次服务器启动时所使用的服务，你将会发现使用选择文件来具体化你的MySQL配置是非常便利的。尤其是 在以下情况中：当MySQL服务在Windows上启动时，其在两个文件中寻找选项：在Windows目录下的 my.ini 文件以及C:\my.cnf 文件。Windows目录典型的命名如下：C:\WINDOWS or C:\WINNT。你可以通过下面的命令来决定WINDIR环境变量 的值： C:\> echo %WINDIR% MySQL首先在my.ini 文件中寻找选项，然后是在my.cnf 文件中。然而，为了避免混淆，最好是只用一个文件。 如果你的PC使用加载的启动装备同时C:不是启动区，那你只能通过my.ini文件来进配置。不管你使用哪种选择 文件，该文件必须是文本格式。你同时也可以充分利用包含在你的MySQL发布版的样本文件。在你的安装目录下 寻找诸如 my-small.cnf, my-medium.cnf, my-large.cnf和my-huge.cnf的文件，这些文件可以被重新命名同时 拷贝到合适的位置作为一个基本的配置文件来使用。一个选择文件可以被任何文本编辑器生成和修改，诸如 Notepad等。例如，如果MySQL被安装在E:\mysql 目录下，而数据目录是在E:\mydata\data下，你可以生成一个 包含 [mysqld] 部分的选择文件来具体化基本目录和数据目录的参数值：

[mysqld] # set basedir to your installation path basedir=E:/mysql # set datadir to the location of your data directory datadir=E:/mydata/data

在这里需要注意Windows路径名在事先使用的斜线而不是反斜线选择文件中被具体化。因此当你使用反斜线时， 你必须使用双斜线。

[mysqld] # set basedir to your installation path basedir=E:\\mysql # set datadir to the location of your data directory datadir=E:\\mydata\\data

在Windows中，MySQL安装程序直接将数据目录安装在你安装MySQL的目录下。如果你需要在另外不同的位置使用 数据目录，你需要将整个数据目录的内容拷贝至新的位置。例如，如果MySQL安装在C:\Program Files\MySQL\MySQL Server 5.0 目录下，则数据目录的位置默认是在 C:\Program Files\MySQL\MySQL Server 5.0\data下。如果你需要将E:\mydata 作为你的数据目录，你需要做以下两件事情：

1 将整个数据目录和它的所有内容从C:\Program Files\MySQL\MySQL Server 5.0\data 移至E:\mydata.。

2 每次启动服务时，使用a–datadir选项来确认新的数据目录位置。

选择MySQL服务类型

以下显示了MySQL5.0Windows版本提供的服务类型:

mysqld-debug带有完全调试和自动内存分配检查的编译，该编译对象也包括 InnoDB和BDB数据表。 mysqld 优化InnoDB支持的二进制数据。 mysqld-nt优化命名管道支持的二进制数据。 mysqld-max优化InnoDB 和 BDB数据表所支持的二进制数据 mysqld-max-nt同mysqld-max功能一样，但是编译时支持命名管道

上述优化选项都是针对Intel 处理器的，但是应该可以工作在任何Intel i386-class 或者更高的处理器。在MySQL 5.0中，所有的Windows版本服务器都提供了对数据库目录的符号链接。 MySQL提供了对Windows平台上的TCP/IP 的支持。mysqld-nt和 mysql-max-nt 服务器则提供了对Windows NT,2000,XP和2003下的命名管道的支持。然而，不管在何种平台下，默认使用的都是TCP/IP。(在很多Windows 配置下，命名管道要比TCP/IP服务要慢)

命名管道的使用受以下条件的制约:

? 命名管道只有当你启动服务时选择 –enable-named-pipe才会被激活。非常有必要的显式使用该选项 ，因为有些用户在命名管道正在使用时关掉MySQL服务器遇到过故障问题。 ? 命名管道只有在mysqld-nt 或mysqld-max-nt 服务器下并且仅当该服务器运行在支持命名管道的 Windows版本的平台下才能使用。? 在Windows 98 or Me系统下，只有当其安装了TCP/IP后，这些服务才能够运行。命名管道的连接不能 使用。? 上述服务在Windows 95下不能够运行。注意：在参考手册中的大部分例子都使用mysqld作为服务名。如果你使用另外一个服务器，例如mysqld-nt，则 需对上述例子作合理的替代。

首次开启服务

这部分的信息主要应用在使用Noinstall版本安装MySQL时，或者希望手动而不是使用GUI工具配置和检测MySQL 的情况下。在Windows 95, 98, or Me下，MySQL客户端经常和使用TCP/IP的服务器相连接。(这允许在你网络上的任意机器 可以和MySQL服务器连接)。基于这一点，你必须保证在启动MySQL前，TCP/IP 支持安装在你的机器上。你可以 到在你Windows CD-ROM中找到TCP/IP服务。 在这里需要注意的是如果你正在使用老的Windows 95发布版本(例如OSR2)，那很有可能你在使用一个旧的 Winsock软件包；MySQL需要的是Winsock 2。你可以从http://www.microsoft.com/.下载最新的Winsock软件包 。Windows 98具有新的Winsock 2库，所以无需更新库。在基于NT的系统例如 Windows NT, 2000, XP, or 2003上，客户可以有两个选择。他们可以使用TCP/IP或者当 服务器支持命名管道的连接时使用命名管道。为了得到在TCP/IP下工作的Mysql版本，你必须安装service pack 3(或者更新的版本) 如果在启动时，选择 –shared-memory, Windows版本的MySQL 5.0同样也支持共享内存的连接。客户端可以选 择 –protocol=memory 选项来进行共享内存的连接。

以上对启动MySQL服务进行了总体概述，下面的内容提供了从命令行或者作为Windwos服务来启动MySQL服务器更 加具体的信息：

假设MySQL安装在默认位置C:\Program Files\MySQL\MySQL Server 5.0下。如果你把MySQL安装在不同的位置上 ，则需调整相应的路径名。测试工作可以从控制台窗体(或者通过“DOS window”)通过一个即时命令来完成。 这样你就可以会在窗体中比较容易的看到服务器状态信息。如果你的配置有误时，这些信息将有助于你识别和 修复任何故障。为了开启服务，输入以下命令：

C:\> C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld –console 对于InnoDB支持的服务器，在服务启动时你可以看到下列信息： InnoDB: The first specified datafile c:\ibdata\ibdata1 did not exist: InnoDB: a new database to be created! InnoDB: Setting file c:\ibdata\ibdata1 size to 209715200 InnoDB: Database physically writes the file full: wait… InnoDB: Log file c:\iblogs\ib_logfile0 did not exist: new to be created InnoDB: Setting log file c:\iblogs\ib_logfile0 size to 31457280 InnoDB: Log file c:\iblogs\ib_logfile1 did not exist: new to be created InnoDB: Setting log file c:\iblogs\ib_logfile1 size to 31457280 InnoDB: Log file c:\iblogs\ib_logfile2 did not exist: new to be created InnoDB: Setting log file c:\iblogs\ib_logfile2 size to 31457280 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: creating foreign key constraint system tables InnoDB: foreign key constraint system tables created 011024 10:58:25 InnoDB: Started

当服务器完成启动序列后，你可以看到如下信息，这些信息表明服务器已经开始服务客户端连接：

mysqld: ready for connections Version: ’5.0.13-rc’ socket: ” port: 3306

服务器将会继续将其所产成的判断信息输入到控制台上。而你则可以打开一个新的控制台来运行客户端程序。如果你省略了 –console 选项，服务器将会把诊断信息输出到 数据目录下的错误日志中(默认是C:\Program Files\MySQL\MySQL Server 5.0\data )。错误日志以.err为扩展名。注意：在MySQL授权数据表的帐号初始化是没有密码的。

在Windows命令行下开启MySQL服务

MySQL服务器可以手动从命令行启动。该项操作可以在Windows的任何版本下完成。为了从命令行启动mysqld服 务，你应该启动一个控制台窗体(或者"DOS 窗体")同时输入以下命令：

C:\> C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld

在上述例子中使用的路径可以根据你的系统安装MySQL的路径不同而改变。在非NT版本的Windows版本上， mysqld是在后台启动的。这也就意味着在服务启动之后，你查看另外的即时命令。基于这一点，你应该在服务 运行时打开另外一个控制窗体来运行客户端程序。

你可以通过执行以下命令停止MySQL服务：

C:\> C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqladmin -u root shutdown

该命令激活MySQL管理程序和服务器连接同时告诉他关闭服务。该名令是作为MySQL根用户连接的，在Mysql权限 系统中，根用户默认是管理员帐户。注意在MySQL授权系统中的用户和任意在Windows下登陆的用户是完全独立 的。如果mysqld没有启动，检查错误日志文件查看是否服务器向其中写入了信息，该信息说明问题产生的原因。错 误日志位于C:\Program Files\MySQL\MySQL Server 5.0\data 目录下。该日志是以扩展名为.err的文件。你也 可以试着重新开启mysqld 控制台服务，这样你就有可能获取一些有助于解决问题的信息。最后一个选项是开启mysqld时选择–standalone –debug。在这种情况下，mysqld 将会写一个 C:\mysqld.trace的日志文件，该日志文件将会包含mysqld为何没有启动的原因。

使用mysqld –verbose –help 可以看到mysqld所提供的所有选项的帮助信息。

作为一项Windows服务开启MySQL

在NT系列的Windows版本(Windows NT, 2000, XP, 2003)中，推荐运行MySQL服务的方法是将其作为Windows服务 进行安装，在这种情况下MySQL随着Windows开始和结束自动开始和结束。一个作为服务被安装的MySQL服务器能 够被从命令行上使用NET命令或者使用图形界面服务程序所控制。服务程序(Windows 服务控制管理器)可以被在 Windows控制面板中找到(在Windows 2000, XP, and Server 2003的管理工具下)。建议在执行服务器安装程序 时或者在命令行中执行删除操作时关闭服务应用程序。这将会防止很多错误。在将MySQL作为Windows服务安装之前，如果当前服务器正在运行，应该首先使用下述命令停止其的运行：

C:\> C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin -u root shutdown

注意：如果MySQL根用户有密码，那么你需要这样激活命令：

C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin -u root -p shutdown ，同时必须根据提示输入 用户密码。

这调用了MySQL的管理功能mysqladmin，mysqladmin可以连接到服务器，让服务器关闭。这个命令是作为MySQL 的 root用户而和服务器建立连接的，MySQL的 root用户在MySQL授权系统中是默认管理帐户。需要注意的是在 MySQL授权系统中的用户是完全独立于Windows上登录用户。

用下列命令可将服务器作用一项服务安装：

C:\> mysqld –install

如果你用server名字在将服务器安装成一项服务的过程中遇到问题， 那就试试用全称路径名。举例来说：

C:\> C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld –install

你也可以将到mysql bin目录的路径加入到Windows 系统PATH 环境变量:

? 在Windows桌面上，右击“我的电脑”，选择“属性” ? 出现系统属性菜单，选择“高级”，然后单击“环境变量”按钮。? 在“系统变量”列表中选择“路径”，然后单击“编辑”按钮。这时会出现编辑系统变量对话框。 ? 将光标放在“变量值”对应的文字的末尾(请使用 End key，以确保光标在最后的位置) 。然后输入 MySQL bin 目录的全称路径， (举个例子, C:\Program Files\MySQL\MySQL Server 5.0\bin), 注意应该用分 号将这个路径与其它值分开。 点击“确定”逐个关闭所有打开的对话框。现在你就可以在DOS系统下从系统的 任意目录中，通过输入任意可执行的 MySQL 程序的名字而找到该程序，不必再输入路径。这包括服务器、 mysql 客户和所有 MySQL 命令行比如 mysqladmin 和 mysqldump。 ? 注意如果你在同一台机器上运行多个MySQL 服务器的话，就不要将MySQL bin 目录加入到 Windows 路 径中。

警告: 在手工编辑系统路径的时候一定要特别小心，如果不小心删除或改变了系统路径中的任何部分都将导致 误操作甚至导致系统不稳定。服务安装命令并不启动服务器，关于这点后面将详细介绍。

MySQL 5.0 在安装服务的过程中对附加参数提供的支持有限：? 你可以在安装选项之后立即为服务命名，否则默认的服务名字是 MySQL。

? 如果设置了一个服务名字，就可以用使用单一选项。通常按照约定，这应当是–defaults- file=file_name 来设置选项文件的名字，服务器在启动时会从选项文件中读取选项。可以使用单一选项而不用–defaults-file，但不推荐这样做。因为，–defaults-file 更加灵活，你可以 通过将多个选项放到指定的选项文件中而为服务器设置多个启动选项。同样，在MySQL 5.0中我们也不支持使用 不同于–defaults-file的选项，但5.0.3版本就可以这样做了。

? 对于MySQL 5.0.1，你也可以在服务名字下设置一个 –local-service（本地服务）选项。这会使服务 器用LocalService Windows帐户运行。这种帐户只在Windows XP或更新的系统上才有。如果服务名字下既有– defaults-file 也有 –local-service，那么它们之间不论次序。 对于安装成Windows服务的MySQL服务器来说，由下面的规则决定服务名字和服务器使用的选项文件： ? 如果服务安装命令没有指定服务名字，那么安装选项将默认服务名字为MySQL, 服务器将使用 MySQL服 务，并从标准选项文件中的 [mysqld]群中读取选择。 ? 如果服务安装命令没有指定了一个服务名字，服务器将使用指定的服务名字并从从标准选项文件中与 服务名字相同的群中读取选项。 服务器也从标准文件选项文件中的[mysqld]群中读取选项。这保证可以从[mysqld]群中读取所有MySQL服务都 用的选项，而用指定服务名字按照的服务器使用和与指定服务名字相同的选项群。 ? 如果安装命令在服务名字之后指定一个 –defaults-file 选项，那么服务器只从指定文件的 [mysqld] 群中读取选项而忽略标准选项文件。

下面的命令是一个复杂的例子：

C:\> C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld –install MySQL –defaults-file=C:\my- opts.cnf

在这里，由安装选项给出了默认服务名字MySQL。如果没有给出–defaults-file选项，那么这个命令将使服务 器从标准选项文件中的[mysqld]群中读取。然而在这里给出了–defaults-file选项，所以服务器只从指定文件 的[mysqld] 选项群读取。

你也可以在启动MySQL服务之前，在Windows Services 工具栏中将选项定义为启动参数。一旦MySQL服务器被安装成一项服务，Windows就会在启动时自动启动该服务。也可以在Services 工具栏中直接 启动MySQL服务器，或使用NET START MySQL命令也可。NET 命令不区分大小写。

作为服务运行时，mysqld无权使用控制窗口，因此在这里看不到消息。如果mysqld不能启动，就查错误记录看 看服务器是不是写下了什么说明问题原因的消息。错误记录在MySQL数据目录下（比如: C:\Program Files\MySQL\MySQL Server 5.0\data）。错误记录的后缀名是 .err。

MySQL作为服务安装的情况下，如果服务正在运行，Windows关闭的时候会自动停止服务。 如果你不想在boot 进程中自动启动服务，你可以将服务器作为手动服务安装。手动服务的安装需要选择“手动 安装” 选项而不是 “安装”选项：

C:\> C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld –install-manual

要删除作为服务安装的服务器时，首先要通过NET STOP MYSQL关闭正在运行的MYSQL，然后用“删除”选项删除 它：

C:\> C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld –remove 如果mysqld不是作为服务运行，可以用命令行启动它。

五、测试 MySQL安装

你可以通过执行下列任何一个命令来测试MySQL 服务器是否安装:

C:\> C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqlshow C:\> C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqlshow -u root mysql C:\> C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin version status proc C:\> C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql test

如果mysqld对来自客户程序的TCP/IP 连接反应很慢，那么说明你的DNS可能有问题。在这种情况下，用–skip -name-resolve选项启动mysqld，而且在MySQL 授权列表的Host栏中只使用本地Host和IP。 你可以强迫 MySQL客户运用管理连接，而不用TCP/IP。方法是选定–pipe 或–protocol=PIPE 按钮，或指定 . (period)作为主机名。运行–socket 选项来规定管道名。

九、排除在Windows下安装MySQL的故障

如果是初次安装MySQL，你可能会遇到一些阻止MySQL启动的错误，这一部分的目的就是帮助你诊断和排除某些 故障。 在排查故障时，可用的第一项资源就是错误记录。MySQL 服务器的错误记录会记下阻止服务器启动的相关错误 。错误记录在数据目录下，my.ini文件中记载了数据目录的地址。默认的文件目录位置是C:\Program Files\MySQL\MySQL Server 5.0\data。 另外在MySQL服务运行过程中的控制信息也可能会记载一些相关的错误。在mysqld作为服务安装之后，从命令行 中执行NET START mysql命令查看是否有关于作为服务启动MySQL服务器的错误信息。

下面的例子是一些首次安装MySQL和启动服务器的过程中，可能遇到的常见错误信息：

?System error 1067 has occurred. ?Fatal error: Can’t open privilege tables: Table ‘mysql.host’ doesn’t exist

? 当 MySQL 服务器找不到 mysql privileges database 或其它关键文件时，就会产生这样的信息。当 MySQL的原始数据或数据目录安装在不同于默认位置(分别是C:\mysql 和 C:\Program Files\MySQL\MySQL Server 5.0\data )时，常常遇到这个问题。 一种情况是MySQL已升级并安装到新的位置，但配置文件并没有反映新的安装位置。另外 ，可能会产生相互冲 突的新、旧配置文件。因此，在升级MySQL时，应确保删除旧的配置文件或重命名。 如果你将MySQL安装到了C:\Program Files\MySQL\MySQL Server 5.0以外的其它位置，你必须确保MySQL服务器 通过一个配置文件(my.ini) 知道安装的位置。my.ini文件必须在Windows目录下，通常是C:\WINNT 或 C:\WINDOWS。你可以从WINDIR环境变量的值来确定其具体位置，需要从命令提示符发出下列命令：

C:\> echo %WINDIR%

生成一个选项文件，并且可以用任何文本编辑程序修改，比如Notepad。举例来说，如果MySQL安装在E:\mysql 、数据目录是D:\MySQLdata，你可以创建一个选项文件，在其中创建一个[mysqld]部分来定义basedir 和 datadir参数：

[mysqld] # set basedir to your installation path basedir=E:/mysql # set datadir to the location of your data directory datadir=D:/MySQLdata

注意在选项文件中，Windows 路径名是用(forward) slashes 定义的，而不是用 backslashes。如果你使用的 是 backslashes，你就需要将它们加倍：

[mysqld] # set basedir to your installation path basedir=C:\\Program Files\\MySQL\\MySQL Server 5.0 # set datadir to the location of your data directory datadir=D:\\MySQLdata

如果你在MySQL配置文件改变了datadir值，那么你在重新启动MySQL服务器之前就必须将已有的MySQL数据目录 转移到相应的位置。

? Error: Cannot create Windows service for MySql. Error: 0

? 如果你不先停止并删除现有的MySQL而重新安装或升级时，或用MySQL Configuration Wizard安装 MySQL时，就会产生这样的错误。因为当Configuration Wizard试图安装服务时，就会发现已经有一个同名的服 务。 对这个问题的一个解决方法是，在使用configuration wizard时选择一个服务名称，而不用默认的mysql 。这 可以使新的服务正确地安装，但旧的还放在那里。建议最好还是把不用的删掉。

要永久删除旧的mysql服务，需要用户以管理权限在命令行上执行下列命令： C:\>sc delete mysql [SC] DeleteService SUCCESS

如果你的Windows版本上没有sc工具栏，就从下面的网址上下载delsrv工具栏： http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/delsrv-o.asp 然后运用delsrv mysql syntax。

六、在Windows上升级MySQL

这部分介绍一些在Windows上升级MySQL的必需步骤。

1． 在升级之前，你应当一直支持目前的MySQL安装。 “Database Backups” 2． 在http://dev.mysql.com/downloads可以下载在Windows上安装MySQL 的最新内容。 3． 在升级MySQL之前，必须停止服务器。

如果服务器是作为服务安装的，要从命令提示符通过下列命令停止服务：

C:\> NET STOP MYSQL

如果不是作为服务安装的，就用下列命令停止MySQL服务器：

C:\> C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin -u root shutdown

当将4.1.5以前的版本升级到 MySQL5.0时，或将用Zip压缩文件安装的MySQL升级到用MySQL Installation Wizard安装的MySQL版本时，必须手动删除以前的安装和MySQL服务（如果服务器有作为服务安装的话）. 用下列命令删除原来的MySQL服务:

C:\> C:\mysql\bin\mysqld –remove

如果不删除原有服务，MySQL Installation Wizard可能无法安装新的MySQL服务。

如果你从Zip 压缩文档安装MySQL，要么覆盖原有的MySQL安装（通常在C:\mysql），要么在不同的目录下安装 ，比如C:\mysql4。建议覆盖原有安装。

重启服务器。如果MySQL是作为服务运行的话，用NET START MySQL，或者直接调用mysqld。

Windows 和 Unix两种系统下的MySQL比较

MySQL在Windows上已经相当稳定。Windows版本的MySQL和相应的Unix版本的MySQL具有相同的特性，但存在以下 几点不同：

• Windows 95和线程

Windows 95分出约200字节的主内存给每一个线程。MySQL的每个连接创建一个新的线程，所以如果服务器处理 多个连接时，不应该在Windows 95下运行 mysqld。其他版本的Windows不存在这个bug。

• 限制端口数量

Windows系统下大概有4，000个可用端口，当一个端口的连接关闭时，需要2-4分钟才能重新启用该端口。当处 于代理连接和断开连接的高峰期时，可能使所有的端口在重新启用之前都处于关闭状态。如果发生这种情况， MySQL服务器将停止相应，尽管它还处于运行状态下。需要注意的是，机器上运行的其它应用程序也可能使用端 口，这也会使可用端口的数量下降。更多的信息请参看下面链接： http://support.microsoft.com/default.aspx?scid=kb;en-us;196271.

• 同时读取

MySQL依赖于调用pread()和pwrite()系统来使INSERT和SELECT可用。目前，我们使用mutexes来代替pread()和 pwrite()。我们打算用虚拟的界面来替代文件水平界面，这样我们就可以在NT，2000和XP系统上使用readfile ()/writefile()界面并获得较高的速度。目前MySQL 5.0只能执行2，048个文件，这就意味着不能在Windows NT ，2000，XP和2003以及Unix下运行多个同时运行的线程。

• 封闭读取

MySQL对每一个连接使用封闭读取，如果指定的通道连接可用，这就意味着以下几点：

o一个连接不会在8小时后自动断开，这发生于Unix版本的MySQL。 o如果连接悬挂，只有关闭MySQL才能断开该连接。 omysqladmin kill 对睡眠状态的连接无效。 o只要存在睡眠连接，mysqladmin shutdown不会异常终止。

• ALTER TABLE

当你正在执行一个ALTER TABLE语句时，在被其他线程使用前表格将被锁定。在Windows系统下可发生该情况， 你不能删除被另外一个线程使用的文件。将来我们可能找到解决该问题的方法。

• DROP TABLE

因为MERGE 处理器处理隐藏于MySQL上一层的表格，这可使Windows下的被MERGE表格使用的DROP TABLE不工作。 因为Windows不允许dropping文件开启，你就必须转储清除所有的MERGE表格（使用FLUSH TABLES）或者在 dropping文件前先撤销MERGE表格。

• 数据目录和索引目录

Windows系统忽略了CREATE TABLE的数据目录和索引目录选项，这是由于Windows不支持符号连接造成的。在拥 有无功能realpath()调用的系统上也会忽略这些选项。

• 撤销数据库

你不能够撤销正在被某个线程使用的数据库。

• 从任务管理器内关闭MySQL

你不能够在任务管理器内关闭MySQL，也不能依靠Windows 95的关机操作关闭MySQL。你必须使用mysqladmin shutdown来关闭MySQL。

• 名称的大小写

在Windows下的文件名不区分大小写，所以Windows同样不区分MySQL数据库和表格的名称的大小写。唯一的限制 就是数据库和表格名称必须在给定的语句内全部使用相同的case来指定。

• ‘\’路径名分离字符

在Windows下使用‘\’来分离路径名，‘\’同样是MySQL里的换码符。如果你使用LOAD DATA INFILE 或者 SELECT … INTO OUTFILE,使用Unix格式的文件名内出现‘/’字符：

mysql> LOAD DATA INFILE ‘C:/tmp/skr.txt’ INTO TABLE skr; mysql> SELECT * INTO OUTFILE ‘C:/tmp/skr.txt’ FROM skr; 换句话说, 你必须使用两个‘\’字符。 mysql> LOAD DATA INFILE ‘C:\\tmp\\skr.txt’ INTO TABLE skr; mysql> SELECT * INTO OUTFILE ‘C:\\tmp\\skr.txt’ FROM skr;

• 进程管道的问题

进程管道不会按照Windows命令行的提示可靠的工作。如果进程管道包含有字符^Z / CHAR(24)，Windows便为认 为达到文件尾并终止程序。当你应用下面的一个二进制日志时便会出现问题：

C:\> mysqlbinlog binary-log-name | mysql –user=root 如果你需要应用该日志并遇到问题，你可以怀疑这是由于^Z / CHAR(24)字符导致的，可使用下面的工作区来解 决： C:\> mysqlbinlog binary-log-file –result-file=/tmp/bin.sql C:\> mysql –user=root –execute "source /tmp/bin.sql" 这样，后面的命令也可以可靠的读取含有二进制数据的SQL文件。

• 用户错误所导致的拒绝访问

如果你尝试运行MySQL代理程序来连接服务器，同时该服务器又在同一台机子上运行着，你就被当作’some- user’@'unknown’用户而被拒绝访问’mysql’数据库，这表明MySQL不能够恰当的解析主机名。要解决该问题，你必须创建一个名为\windows\hosts的文件，并包含下面的信息：

127.0.0.1 localhost

, ,

MySQL 5.0.16乱码问题的解决方法

五 11th

由admin发表在DataBase

没有评论

问：怎样解决MySQL 5.0.16的乱码问题？

答：MySQL 5.0.16的乱码问题可以用下面的方法解决：

1 设置phpMyAdmin

Language:Chinese simplified (zh-utf-8)

MySQL 字符集：UTF-8 Unicode (utf8)

MySQL 连接校对 gbk_chinese_ci

2 创建数据库时

整理设置成 gbk_chinese_ci

3 用SQL建立表中

ENGINE=MyISAM DEFAULT CHARSET=gbk;

ENGINE=InnoDB DEFAULT CHARSET=gbk AUTO_INCREMENT=40 ;

4 检查表结构中

varchar(100) 的整理属性为gbk_chinese_ci

其它类型的整理属性为空。

5 代码中加上

$db->query(“SET CHARACTER SET GBK”); Linux下需要，Windows下则不需要。

此种操作相当于Connection Character Sets and Collations

Mysql的最大连接数怎样用java程序测试

五 11th

由admin发表在DataBase

没有评论

　　以前工作的时候由于Oracle8i数据库经常出现用户过多的错误,由于数据量大,经常出现ORA:12500错误,但主要原因是访问过多而引起的,后来就用Java写了个简单的用JDBC连接来测试Oracle最大连接数的程序.现在常用MySQL,所以又写了一个简单的测试其最大连接数的程序，在此介绍给大家： 

import java.sql.Connection;


 import java.sql.DriverManager;


 import java.sql.Statement;


 import java.sql.ResultSet;


 import java.sql.SQLException;


 import java.io.IOException;


 class testMaxConn{


   public static void main(String args[]){


     int count=0;


     Connection []conn=new Connection[1000];


     Statement  []stmt =new Statement[1000];


     ResultSet  []rs =new ResultSet[1000];


    try{


     Class.forName


(“com.MySQL.jdbc.Driver”).newInstance();


for(count=0;count<300;count++){


conn[count] = DriverManager.getConnection


("jdbc:mysql://localhost/MySQL", "root", "");


      stmt[count]=conn[count].createStatement();


      rs[count]=stmt[count].executeQuery


("SELECT * FROM user");


      while (rs[count].next()){


  //System.out.println(rs.getString(1)+


"\t "+rs.getString(2));  


      }


      System.out.print(count+"\t");


     }


    }catch(SQLException ex1){


      System.out.println("\n"+ex1.toString());


    }catch(InstantiationException ex2){


      System.out.println("\n"+ex2.toString());


    }catch(ClassNotFoundException ex3){


      System.out.println("\n"+ex3.toString());


    }catch(IllegalAccessException ex4){


      System.out.println("\n"+ex4.toString());


    }finally{


      try{


        System.out.println("\nSystem has opened 


"+count--+" MySQL connections.\nPress Enter key 


to close the connections");


        System.in.read();


        System.out.println


("\nClose the Connections:");


        for(;count>=0;count–){


         rs[count].close();


         stmt[count].close();


         conn[count].close();


         System.out.print(count+”\t”);


        }


      }catch(SQLException ex){


       System.out.println


(“\n Close connection exception:”+ex.toString());


      }catch(IOException io_ex){}


    }//end the first “try”


   }


 }

12345102030...最后一页 »