图1
FROM或 WHERE 子句中指定，WHERE 和 HAVING 子句可包含其搜索条件，以供进一步筛选连接之后的结果集。 目前可实现的连接有：自然连接(Natural Join)，内连接(Inner Join), 外连接(Outer Join), 交叉连接(Cross Join) etc.t1 JOIN t2 USING(a,b)。t1 JOIN t2 ON t1.a = t2.a。... USIGN(a)... 等价于 ... ON t1.a = t2.a..., 使用两者在结果集上体现的不同请见注 1。SELECT * FROM t1, t2 WHERE t1.a = t2.a; or SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;。NULL。其作用是在做连接操作时避免丢失信息。外连接有 3 类：LEFT [OUTER] JOIN，其结果表中保留左关系的所有元组。eg: SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;。RIGHT [OUTER] JOIN, 其结果表中保留右关系的所有元组。MySQL 对其的优化策略见注 2。FULL [OUTER] JOIN, 其结果表中保留左右关系的所有元组。 MySQL 中， FULL JOIN... USING的结果集和INNER JOIN ... USING的结果集相同，且 MySQL 不支持 FULL OUTER JOIN。eg: SELECT * FROM t1, t2; or SELECT * FROM t1 INNER JOIN t2;。NATURAL JOIN 和 JOIN ... USING SQL 时采用 SQL 2003 的标准，从 MySQL 5.0.12 开始，这两种 SQL 的解析都有做调整, NATURAL JOIN 时， 查询结果中只会显示单独的唯一一列， 即 t1.a, t2.2 两列做自然连接后在结果中只有一列 a (= COELSCE(t1.a, t2.a)); 而 JOIN ... USING时，对 USING 中 指定使用做连接的列，查询结果中也只会显示单独的唯一一列， JOIN ... USING形式的变体如 LEFT JOIN ... USING, RIGHT JOIN ... USING 等解析优化也是采用同样的处理。除此之后，其他的连接方式还是遵照以前的规则。详细说明，可查看如下引用部分或MySQL 官方文档 JOIN Syntax一节。
Join Processing Changes in MySQL 5.0.12
Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard.
The single result column that replaces two common columns is defined using the coalesce operation. That is, for two t1.a and t2.a the resulting single join column a is defined as a = COALESCE(t1.a, t2.a), where:
COALESCE(x, y) = (CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END)
If the join operation is any other join, the result columns of the join consists of the concatenation of all columns of the joined tables. This is the same as previously.
T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =
(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)
At the parser stage, queries with right outer joins operations are converted to equivalent queries containing only left join operations. In the general case, the conversion is performed according to the following rule:
T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =
(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)
EXPLAIN EXTENDED, SHOW WARNINGS, 查看 MySQL 解析优化后的右外连接：grep 'test_string' filename.txt > /tmp/test.txt 表示将 filename.txt 中包含有 test_string 字符串行的定向输出到 /tmp 下 test.txt 文件中。cp /usr/local/share/aclocal /usr/share ps aux | grep 'application name'ReflectionMethod PHP class, 获得指定的类的所有方法信息，详细信息，可PHP官方文档。Cache-Control必须设置为 maxage=time 的格式, maxage 是指生存缓存生效时间， 1 为 1 秒； Expires 必须要设置，它的时间稍大于 Date(页面请求时间)即可；Pragma 必须设置为 public。ALTER TABLE tbl_name DROP PRIMARY KEY;, DROP INDEX idx_name ON tbl_name lower_case_table_names=2, 官方文档。skip-name-reslovemysqlslap --help可以获得可用的选项，这里列一些主要的参数，更详细的说明参考官方手册。--auto-generate-sql, -a 自动生成测试表和数据。--auto-generate-sql-load-type=type 测试语句的类型。取值包括：read，key，write，update和mixed(默认)。--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认 1。--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认 1。--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)。--query=name,-q 使用自定义脚本执行测试，eg: 可以调用自定义的一个存储过程或者 SQL 语句来执行测试。--create-schema 指定测试的数据库。--commint=N 多少条 DML 后提交一次。--compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。--concurrency=N, -c N 并发量，也就是模拟多少个客户端同时执行 SELECT。可指定多个值，以逗号或者--delimiter参数指定的值做为分隔符。--engine=engine_name, -e engine_name 创建测试表所使用的存储引擎，可指定多个。--iterations=N, -i N 测试执行的迭代次数。--detach=N执行 N 条语句后断开重连。--debug-info, -T打印内存和CPU的信息。--only-print 只打印测试语句而不实际执行。--defaults-file=mysql_configuration_file_directory 配置文件存放位置。--socket=socket_directory, -S socket_directory socket文件位置。--only-print来打印实际的测试过程。
DROP SCHEMA IF EXISTS `mysqlslap`;
CREATE SCHEMA `mysqlslap`;
use mysqlslap;
CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));
INSERT INTO t1 VALUES (1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL');
......
DROP SCHEMA IF EXISTS `mysqlslap`;
Benchmark
Average number of seconds to run all queries: 0.676 seconds
Minimum number of seconds to run all queries: 0.676 seconds
Maximum number of seconds to run all queries: 0.676 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Average number of seconds to run all queries: 0.922 seconds
Minimum number of seconds to run all queries: 0.922 seconds
Maximum number of seconds to run all queries: 0.922 seconds
Number of clients running queries: 100
Average number of queries per client: 10
User time 0.17, System time 0.40
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 1653, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 14740, Involuntary context switches 6135
Voluntary context switches 7319, Involuntary context switches 681
Benchmark
Average number of seconds to run all queries: 0.833 seconds
Minimum number of seconds to run all queries: 0.803 seconds
Maximum number of seconds to run all queries: 0.865 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Average number of seconds to run all queries: 0.980 seconds
Minimum number of seconds to run all queries: 0.948 seconds
Maximum number of seconds to run all queries: 1.007 seconds
Number of clients running queries: 100
Average number of queries per client: 10
User time 1.03, System time 2.35
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 7148, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 115255, Involuntary context switches 4125
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.781 seconds
Minimum number of seconds to run all queries: 0.766 seconds
Maximum number of seconds to run all queries: 0.800 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.908 seconds
Minimum number of seconds to run all queries: 0.880 seconds
Maximum number of seconds to run all queries: 0.929 seconds
Number of clients running queries: 100
Average number of queries per client: 10
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.897 seconds
Minimum number of seconds to run all queries: 0.858 seconds
Maximum number of seconds to run all queries: 0.929 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.189 seconds
Minimum number of seconds to run all queries: 1.086 seconds
Maximum number of seconds to run all queries: 1.306 seconds
Number of clients running queries: 100
Average number of queries per client: 10
User time 2.02, System time 4.24
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 14556, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 172917, Involuntary context switches 52011
Benchmark
Average number of seconds to run all queries: 0.271 seconds
Minimum number of seconds to run all queries: 0.256 seconds
Maximum number of seconds to run all queries: 0.288 seconds
Number of clients running queries: 50
Average number of queries per client: 20
Benchmark
Average number of seconds to run all queries: 0.324 seconds
Minimum number of seconds to run all queries: 0.292 seconds
Maximum number of seconds to run all queries: 0.333 seconds
Number of clients running queries: 100
Average number of queries per client: 10
User time 0.22, System time 0.63
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 4355, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 16691, Involuntary context switches 2837
extension=handlersocket.so加入 php.ini, 重启 PHP 服务。$host MySQL 服务器 host name。$port HandlerSocket 的端口地址。$id HandlerSocket ID; 1 SELECT, 2 UPDATE, 3 INSERT, 4 DELETE。$db 数据库名$table 表名$index 索引名, 可以是手动创建的索引名。这个参数可为空，一般指定时是用于 SELECT，eg: 指定为主键：HandlerSocket::PRIMARY$fields 字段名(多个字段名，用逗号分隔)，可为空。$id HandlerSocket ID; 1 SELECT, 2 UPDATE, 3 INSERT, 4 DELETE。$op 操作符，有如下可选项, '=', '>=', '<=', '>', '<', '+'。$fields 查询中所用到的字段，数组，其长度必须等于或小于指定的列数。$limit 最多影响的行数(最开始根据这个函数名称有在怀疑这个参数，测试时发现，如果存在满足条件的多条记录时，会根据这个参数指定的值返回记录数)。$skip 在检索记录前忽略掉的行数。$modop 指定修改操作，可选值：'U', 'D'。$values 数组，用于做 UPDATE 操作时指定修改的值。$filters 过滤的选项。$invalues_key ? (enabled : 0 / disabled : -1).$invalues IN options$requrest 多组 executeSingle 参数，用数组的形式体现。HandlerSocket::executeSingle($requests00, $requests01, ...), HandlerSocket::executeSingle($requests10, ...) ...。$id HandlerSocket ID; 2 UPDATE 。$op 操作符，有如下可选项, '=', '>=', '<=', '>', '<', '+'。$fields 查询中所用到的字段，数组，其长度必须等于或小于指定的列数。$values UPDAET 时指定修改的值。$limit 最多影响的行数。$skip 在检索记录前忽略掉的行数。$filters 过滤的选项。$invalues_key ? (enabled : 0 / disabled : -1).$invalues IN optionsHandlerSocket::executeSingle($id, $op, $fields, $limit, $skip, 'U', $values, $filters, $invalues_key, $invalues)。$id HandlerSocket ID; 4 DELETE 。$op 操作符，有如下可选项, '=', '>=', '<=', '>', '<', '+'。$fields 查询中所用到的字段，数组，其长度必须等于或小于指定的列数。$limit 最多影响的行数。$skip 在检索记录前忽略掉的行数。$filters 过滤的选项。$invalues_key ? (enabled : 0 / disabled : -1).$invalues IN optionsHandlerSocket::executeSingle($id, $op, $fields, $limit, $skip, 'D', NULL, $filters, $invalues_key, $invalues)。$id HandlerSocket ID; 3 INSERT 。$values HandlerSocket::openIndex 指定的字段参数所对应的值，但是以数组的形式体现。HandlerSocket::executeSingle($id, '+', $values, 0, 0, NULL, NULL, NULL) ，第三个参数中指定的值必须和在此之前调用 HandlerSocket::openIndex 时第五个参数指定的字段对应。
......
configure: error:
You should include support for at least one database!
Reconfigure with one or more of:
--with-mysql
--with-pgsql
--with-oracle
......
dictionary.h:93: error: ‘strlen’ was not declared in this scope
super-smack.cc:126: error: ‘strlen’ was not declared in this scope
src 目录，找到 dictionary.h, super-smack.cc，分别加上#include <string.h>。
query.cc:200: error: cast from ‘char*’ to ‘unsigned int’ loses precision
query.cc:200: error: cast from ‘char*’ to ‘unsigned int’ loses precision
query.cc:219: error: cast from ‘char*’ to ‘unsigned int’ loses precision
query.cc:219: error: cast from ‘char*’ to ‘unsigned int’ loses precision
src 目录， 找到 query.cc文件，将上面指定的 200, 219 行中的unsigned int改为unsigned long。/usr/share/smacks 这个目录下(开始并不知道源代码中有一份), 产生的数据文件是在/var/smack-data目录下-d 指定测试的数据库管理系统的类型。20 20 个线程1000 每个线程 1000 个查询-D参数来指定数据文件，默认路径如前面提到是 /var/smack-data， 这个路径需跟 select-key.smack 和 update-select.smack 指定的一致。
handlersocket/Makefile.am:3: Libtool library used but `LIBTOOL' is undefined
This is caused because FreeBSD renames the default tools, like aclocal to aclocal19. When aclocal is run, it looks for files in its FreeBSD set directory,
/usr/local/share/aclocal19 but most normal apps, like libtool, install to /usr/local/share/aclocal. The solution? Simple! Just merge the contents into one
directory, delete the other, and create a symlink!
class="idTabs" element 中，任何<a href="#tabs">以这种形式调用，被点击时都会显示对应 id="tab" element 中的内容。如下：
...
| Com_select | 107069 |
| Com_select | 108873 |
| Com_select | 108921 |
| Com_select | 109511 |
| Com_select | 108084 |
| Com_select | 108483 |
| Com_select | 108115 |
...
r b swpd free buff cache in cs us sy id wa st
23 0 0 963004 224216 29937708 58242 163470 59 28 12 0 0
24 0 0 963312 224216 29937708 57725 164855 59 28 13 0 0
19 0 0 963232 224216 29937708 58127 164196 60 28 12 0 0
16 0 0 963260 224216 29937708 58021 165275 60 28 12 0 0
20 0 0 963308 224216 29937708 57865 165041 60 28 12 0 0
samples % app name symbol name
259130 4.5199 mysqld MYSQLparse(void*)
196841 3.4334 mysqld my_pthread_fastmutex_lock
106439 1.8566 libc-2.5.so _int_malloc
94583 1.6498 bnx2 /bnx
284550 1.4748 ha_innodb_plugin.so.0.0.0 ut_delay
67945 1.1851 mysqld _ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array
63435 1.1065 mysqld JOIN::optimize()
55825 0.9737 vmlinux wakeup_stack_begin
55054 0.9603 mysqld MYSQLlex(void*, void*)
50833 0.8867 libpthread-2.5.so pthread_mutex_trylock
49602 0.8652 ha_innodb_plugin.so.0.0.0 row_search_for_mysql
47518 0.8288 libc-2.5.so memcpy
46957 0.8190 vmlinux .text.elf_core_dump
46499 0.8111 libc-2.5.so malloc
HANDLER ，但是因为在解析 SQL时，opening/closing table 还是必须的，所以对于提高吞吐量，它还是只能爱莫能助。
approx qps server CPU util
MySQL via SQL 105,000 %us 60% %sy 28%
Memcached 420,000 %us 8% %sy 88%
MySQL via HandlerSocket 750,000 %us 45% %sy 53%
samples % app name symbol name
984785 5.9118 bnx2 /bnx2
847486 5.0876 ha_innodb_plugin.so.0.0.0 ut_delay
545303 3.2735 ha_innodb_plugin.so.0.0.0 btr_search_guess_on_hash
317570 1.9064 ha_innodb_plugin.so.0.0.0 row_search_for_mysql
298271 1.7906 vmlinux tcp_ack
291739 1.7513 libc-2.5.so vfprintf
264704 1.5891 vmlinux .text.super_90_sync
248546 1.4921 vmlinux blk_recount_segments
244474 1.4676 libc-2.5.so _int_malloc
226738 1.3611 ha_innodb_plugin.so.0.0.0 _ZL14build_template P19row_prebuilt_structP3THDP8st_tablej
206057 1.2370 HandlerSocket.so dena::hstcpsvr_worker::run_one_ep()
183330 1.1006 ha_innodb_plugin.so.0.0.0 mutex_spin_wait
175738 1.0550 HandlerSocket.so dena::dbcontext:: cmd_find_internal(dena::dbcallback_i&, dena::prep_stmt const&, ha_rkey_function, dena::cmd_exec_args const&)
169967 1.0203 ha_innodb_plugin.so.0.0.0 buf_page_get_known_nowait
165337 0.9925 libc-2.5.so memcpy
149611 0.8981 ha_innodb_plugin.so.0.0.0 row_sel_store_mysql_rec
148967 0.8943 vmlinux generic_make_request
SHOW GLOBAL STATUS 获得统计信息，Innodb_rows_read 达到了 750000+ 是值得一看的。| 型号 | 戴尔PowerEdge R710 |
|---|---|
| CPU | Nehalem 8核，E5540@2.53GHz |
| 内存 | 32GB(所有数据都装入缓冲池) |
| MySQL | 5.1.50 InnoDB |
| Memcached/libMemcached | 1.4.5(Memcached)，0.44(libMemcached) |
| Network | Boradcom NetXtreme II BCM5709 1000Base-T(内建四端口，使用了其中三个) |
multi_get()(类似于in(1,2,3), 只需一次网络往返)还可获取多行数据。到这里可查询详细信息。epoll()和 worker-thread/thread-pooling 架构，而 MySQL 内部线程的数量是有限的(可以由 my.cnf中的 handlersocket_threads参数控制)，所以即使建立上千万的网络连接到 HandlerSocket，它的稳定性也不会受到任何影响(消耗太多的内存，会造成巨大的互斥竞争等其他问题，如bug#26590，bug#33948，bug#49169)。SHOW GLOBAL STAUTS，SHOW ENGINE INNODB STATUS和SHOW PROCESSLIST等.
ERROR 1289 (HY000): The 'plugin' feature is disabled; you need MySQL built with 'HAVE_DLOPEN' to have it working
--with-mysqld-ldflags=-all-static
- Why does the compilation process not recognize that Ubuntu default install supports HAVE_DLOPEN ?
Server was built with --with-mysqld-ldflags=-all-static
checking build system type... i686-pc-linux-gnu
checking host system type... i686-pc-linux-gnu
checking for gcc... gcc
checking for C compiler default output file name...
configure: error: C compiler cannot create executables
See `config.log' for more details.
--with-plugins=myisam,innodb_plugin,innobase後, 再編譯安裝, OK.
/bin/rm: cannot remove `libtoolT’: No such file or directory
make，出錯.網上查了下，有很多提到採用$RM -f "$cfgfile", 遂按此方法重新編譯安裝，還是失敗.
Package: libtool
Version: 2.2.6a-2
Normal run of libtool, produces no other warnings until the end of the master configure script.
At which point out pops:
config.status: executing libtool commands
/bin/rm: cannot remove `libtoolT': No such file or directory
This is resolved by placing:
$RM -f "$cfgfile"
at line 680 of /usr/share/aclocal/libtool.m4
One thing to note, is that the package producing this error has been bootstrapped to install libLtdl and related stuff under lib/libLtdl instead of the defaults.
The actual error may be elsewhere with a failure of the macro to detect and handle that case.
libtoolize --ltdl, 用這種方法再重新編譯，雖然編譯還是報如上同樣的錯誤，但安裝已能順利進行下去.
— Macro: LT_CONFIG_LTDL_DIR (directory)
Declare directory to be the location of the libltdl source files, for libtoolize --ltdl to place them.
SHOW SLAVE STATUS 提示：
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file
-s ORDER ORDER, 主要有 c, t, l, r 和 ac, at, al, ar, 分别是按照 query次数, 时间, lock的时间和返回的记录数来排序, 前面加了a时倒序.-t NUM top NUM, 即为返回前面多少条的数据.-g PATTERN grep: 后边可以写一个正则匹配模式, 大小写不敏感mysqlsla -lt slow /usr/local/mysql/var/mysql_slow_query.log slow logmysqlsla -lt general /usr/local/mysql/var/mysql_query.log general logmysqlbinlog /usr/local/mysql/var/mysql-bin.000001 | mysqlsla -lt binary - binary log--top 100的参数来修改, 如果觉得每次输入麻烦, 还可以建立一个配置文件 ~/.mysqlsla, 在文件里写上:--log-type=TYPE LOGS or -lt TYPE LOGS TYPE LOGS 可为 slow, general, binary, msl or udl. 官方文档说是没有指定时, 会根据给定的日志文件自动检测, 但测试时失败, so, 最好指定该参数.mysqlbinlog命令将其解析为文本,. mysqlbinlog 带有 --short-form参数时, 则 LOG TYPE 需指定为 udl. 命令可类似于如下:--explain or -ex explain 每句 SQL, 默认没有启用--databases=dbname1,dbnam2[,...] or -db dbname1,dbnam2[,...] or -D dbname1,dbnam2[,...]用于 --explain--flush-qc 强制刷新查询缓存, 默认没有启用--grep="PATTERN" 只解析满足条件的 SQL --meta-filter="CONDTIONS" or -mf "CONDTIONS" CONDITIONS 格式为[meta][op][value],多个条件时, 中间以,分割.--reports=REPORTS or -R REPORTS, 默認是 standard, REPORTS 可以是以,為分割的列表. 可選選項：standard, time-all, print-unique, print-all, dumpPURGE {MASTER | BINARY} LOGS TO 'binlog-name';PURGE {MASTER | BINARY} LOGS BEFORE 'date';
The number of days for automatic binary log file removal. The default is 0, which means “no automatic removal.” Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in
-d, --database=dbname 列出查看的数据库--start-datetime='2011-03-25 00:00:00' 查看发生在该指定时间之后的 events.--stop-datetime='2011-03-25 23:59:59' 查看发生在该指定时间之前的 events.--start-position=10 查看该指定偏移点之后的 events.--stop-position=100 查看该指定偏移点之前的 events.--set-charset=utf8 在输出的 events 之前添加上 "SET NAMES utf8". > /tmp/1.log 将其重定向到 1.log, 更便于查看.