2009年4月

使用自己编码的SESSION保证多平台的会话通信

已经测试成功.改天整理写出来.下面定下基本思想.
1.解决了soap无登录会话问题
2.session再加密

3. session_start()之前使用自己编码的session_id($newId);
4. 传输中加session_id自验证 (类各类防刷URL. 如alipay接口的一级加密)

MYSQl left join 联合查询效率分析

user表:

id | name
———
1 | libk
2 | zyfon
3 | daodao

user_action表:

user_id | action
—————
1 | jump
1 | kick
1 | jump
2 | run
4 | swim

sql:
select id, name, action from user as u
left join user_action a on u.id = a.user_id

result:
id | name | action
——————————–
1 | libk | jump ①
1 | libk | kick ②
1 | libk | jump ③
2 | zyfon | run ④
3 | daodao | null ⑤

分析:
注意到user_action中还有一个user_id=4, action=swim的纪录,但是没有在结果中出现,
而user表中的id=3, name=daodao的用户在user_action中没有相应的纪录,但是却出现在了结果集中
因为现在是left join,所有的工作以left为准.
结果1,2,3,4都是既在左表又在右表的纪录,5是只在左表,不在右表的纪录

结论:
我们可以想象left join 是这样工作的
从左表读出一条,选出所有与on匹配的右表纪录(n条)进行连接,形成n条纪录(包括重复的行,如:结果1和结果3),
如果右边没有与on条件匹配的表,那连接的字段都是null.
然后继续读下一条。

引申:
我们可以用右表没有on匹配则显示null的规律, 来找出所有在左表,不在右表的纪录, 注意用来判断的那列必须声明为not null的。
如:
sql:
select id, name, action from user as u
left join user_action a on u.id = a.user_id
where a.user_id is NULL
(注意:1.列值为null应该用is null 而不能用=NULL
2.这里a.user_id 列必须声明为 NOT NULL 的)
result:
id | name | action
————————–
3 | daodao | NULL

——————————————————————————–

Tips:
1. on a.c1 = b.c1 等同于 using(c1)
2. INNER JOIN 和 , (逗号) 在语义上是等同的
3. 当 MySQL 在从一个表中检索信息时,你可以提示它选择了哪一个索引。
如果 EXPLAIN 显示 MySQL 使用了可能的索引列表中错误的索引,这个特性将是很有用的。
通过指定 USE INDEX (key_list),你可以告诉 MySQL 使用可能的索引中最合适的一个索引在表中查找记录行。
可选的二选一句法 IGNORE INDEX (key_list) 可被用于告诉 MySQL 不使用特定的索引。
4. 一些例子:
mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
-> LEFT JOIN table3 ON table2.id=table3.id;
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
-> WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
-> WHERE key1=1 AND key2=2 AND key3=3;

7.2.9. MySQL如何优化LEFT JOIN和RIGHT JOIN
在MySQL中,A LEFT JOIN B join_condition执行过程如下:

· 根据表A和A依赖的所有表设置表B。

· 根据LEFT JOIN条件中使用的所有表(除了B)设置表A。

· LEFT JOIN条件用于确定如何从表B搜索行。(换句话说,不使用WHERE子句中的任何条件)。

· 可以对所有标准联接进行优化,只是只有从它所依赖的所有表读取的表例外。如果出现循环依赖关系,MySQL提示出现一个错误。

· 进行所有标准WHERE优化。

· 如果A中有一行匹配WHERE子句,但B中没有一行匹配ON条件,则生成另一个B行,其中所有列设置为NULL。

· 如果使用LEFT JOIN找出在某些表中不存在的行,并且进行了下面的测试:WHERE部分的col_name IS NULL,其中col_name是一个声明为 NOT NULL的列,MySQL找到匹配LEFT JOIN条件的一个行后停止(为具体的关键字组合)搜索其它行。

RIGHT JOIN的执行类似LEFT JOIN,只是表的角色反过来。

联接优化器计算表应联接的顺序。LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作,因为检查的表交换更少。请注意这说明如果执行下面类型的查询,MySQL进行全扫描b,因为LEFT JOIN强制它在d之前读取:

SELECT *
FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
在这种情况下修复时用a的相反顺序,b列于FROM子句中:

SELECT *
FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
MySQL可以进行下面的LEFT JOIN优化:如果对于产生的NULL行,WHERE条件总为假,LEFT JOIN变为普通联接。

例如,在下面的查询中如果t2.column1为NULL,WHERE 子句将为false:

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
因此,可以安全地将查询转换为普通联接:

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
这样可以更快,因为如果可以使查询更佳,MySQL可以在表t1之前使用表t2。为了强制使用表顺序,使用STRAIGHT_JOIN。

HAVING语句

那我们如何对函数产生的值来设定条件呢?举例来说,我们可能只需要知道哪些店的营业额有超过 $1,500。在这个情况下,我们不能使用 WHERE 的指令。那要怎么办呢?很幸运地,SQL 有提供一个 HAVING 的指令,而我们就可以用这个指令来达到这个目标。 HAVING 子句通常是在一个 SQL 句子的最后。一个含有 HAVING 子句的 SQL 并不一定要包含 GROUP BY 子句。HAVING 的语法如下:SELECT "栏位1", SUM("栏位2")
FROM "表格名"
GROUP BY "栏位1"
HAVING (函数条件)

请读者注意: GROUP BY 子句并不是一定需要的。

在我们

Store_Information

表格这个例子中,

Store_Information

表格

store_nameSalesDate
Los Angeles$1500Jan-05-1999
San Diego$250Jan-07-1999
Los Angeles$300Jan-08-1999
Boston$700Jan-08-1999

我们打入,
SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500

结果:

 

store_nameSUM(Sales)
Los Angeles
$1800

last_insert_id对于AUTO_INCREMENT是可靠的

由于last_insert_id 对应php的mysql_insert_id,但mysql_insert_id对于int自增才可靠,对于bigint却不可信.
由此对于自增ID的字段可以单独执行一次mysql_query("select last_insert_id()");

Mysql的API有个很有意义的函数last_insert_id()。这个函数的作用是,针对auto_increment字段,返回给定的 数据库链接,
上一步 INSERT 查询中产生的 AUTO_INCREMENT 的 ID 号。如果没有指定数据库链接,则使用上一个打开的连接。

很多人质疑last_insert_id()是否是可靠的,以前我也犹豫过。

事实上的结果是mysql_insert_id决不会取错。首先做个测试,
在mysql_query("insert.....);之后立刻sleep(1100),其间再做些其他的insert.

然后发现在mysql_insert_id取的值都不会和其他的冲突。

看了半天mysql的代码。mysql_insert_id是这么定义的

my_ulonglong STDCALL mysql_insert_id(MYSQL *mysql)
{
return mysql->;last_used_con->;insert_id;
}

MYSQL是个结构体,里面包括数据库链接和一些当前数据库链接的状态值,
其中在MYSQL结构体里面有insert_id,mysql_insert_id函数返回的就是结构体里面的这个值。

typedef struct st_mysql
{
NET net;
gptr connector_fd;
char *host,*user,*passwd,*unix_socket,*server_version,*host_info,*info;
char *db;
struct charset_info_st *charset;
MYSQL_FIELD *fields;
MEM_ROOT field_alloc;
my_ulonglong affected_rows;
my_ulonglong insert_id;
my_ulonglong extra_info;
unsigned long thread_id;
unsigned long packet_length;
unsigned int port;
unsigned long client_flag,server_capabilities;
unsigned int protocol_version;
unsigned int field_count;
unsigned int server_status;
unsigned int server_language;
unsigned int warning_count;
struct st_mysql_options options;
enum mysql_status status;
my_bool free_me;
my_bool reconnect;

char scramble[SCRAMBLE_LENGTH+1];

my_bool rpl_pivot;

struct st_mysql* master, *next_slave;
struct st_mysql* last_used_slave;

struct st_mysql* last_used_con;
LIST *stmts;
const struct st_mysql_methods *methods;
void *thd;

my_bool *unbuffered_fetch_owner;
} MYSQL;

把insert_id理解成max(id)是错误的,因为有并发存在。
结构体里面有insert_id是针对当前MYSQL连接的,而每次mysql_query操作在mysql服务器上可以理解为一次“原子”操作。

mysql优化方案

【总体优化建议】
1、 在一般情况下,应该尝试以非冗余方式(查看数据库理论中的第三正则形式)保存数据,但是为了获得更快的速度,可以冗余字段或创建总结表。

2、当MyISAM使用动态表格式时,偶尔使用OPTIMIZE TABLE可以避免碎片。可以写一个脚本程序定时执行优化。

3、在Web服务器中,图象和其它二进制资源应该作为文件存储。也就是仅在数据库中存储的文件的引用地址而不是文件本身。大多数Web服务器在缓存文件方面比数据库内容要好得多,因此使用文件一般要快得多。

4、对经常访问的不重要数据(如session)使用内存表。

5、在MYSQL的主写从读的架构中,一般将主库的表类型使用innoDB类型,而从库则使用myisam的表结构。因为innoDB支持事务,而且它是行锁,对插入、更新、删除的影响比较小,更改数据的性能也更高(很多人测试过)。
注:一定要记得要将innodb的auto_commit的默认值设置成0或false,它是默认值是1
InnoDB表的行锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表,例如update table set num=1 where name like “%aaa%”

6、用DELAY_KEY_WRITE=1选项声明MyISAM表可以使索引更新更快,因为在表关闭之前它们不刷新到硬盘上。不利之处是当表打开时如果杀掉服务器,应确保用–myisam-recover选项运行服务器保证没有问题,或者在重启服务器之前运行myisamchk。

【查询优化】
1、索引实际上是一个单独的、物理的数据库结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。理解了这个概念,对于合理使用索引很重要,比如单独查询一个索引列的时候速度非常快,因为mysql根本不去检索实际表,而只要查询排序好的索引表就行了。

2、使用explain EXTENDED SELECT …,可以得到重写并且执行优化规则后SELECT语句,其中查询时必须检查的行数[rows]的乘积(笛卡尔乘积)越小,查询效率越高。

3、MySQL用一遍扫描多次联接(single-sweep multi-join)的方式解决所有联接。这意味着MySQL从第一个表中读一行,然后找到在第二个表中的一个匹配行,然后在第3个表中等等。
所以关联的表的顺序应该是记录数从多到少的顺序,这样它的笛卡尔乘积就越小,查询效率就越高。

4、join的条件字段考虑实际情况建立索引,条件字段的类型、长度要一致。

5、使用FOUND_ROWS和SQL_CALC_FOUND_ROWS来计算分页,它们的主要作用是计算SQL语句中除去LIMIT的查询结果集的行数,因为它是在查询表扫描行时计算出来的,所以性能会更高。[见MYSQL分页统计的新方法]
如:
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

【插入优化】
1、插入一条记录大致的时间使用情况是:
连接:(3)
发送查询给服务器:(2)
分析查询:(2)
插入记录:(1x记录大小)
插入索引:(1x索引)
关闭:(1)

表的大小以logN 的速度减慢索引的插入。

2、尽量使用INSERT INTO TABLE (xxx,xxx),(xxx,xxx),这比使用单行INSERT语句快(在某些情况下快几倍)。
使用set bulk_insert_buffer_size=xxx,MyISAM 使用专用树状缓存来使INSERT … SELECT、INSERT … VALUES (…)、(…)、 …和LOAD DATA INFILE的大块插入更快。将它设置为0禁用优化。注释:只有向非空表添加数据时才使用该缓存。 默认值是8MB。

3、当不需要知道何时写入数据时,使用INSERT DELAYED。这样可以加快处理,因为很多记录可以通过一次性写入硬盘。比如记录日志。
注:DELAYED和LOW_PRIORITY的区别是LOW_ PRIORITY强迫客户端等待,直到那些数据行可以被插入数据表。DELAYED则允许客户端继续操作而无须等待。

4、可以使用INSERT `pv_table` (page_id, pv) VALUES (1, 20) ON DUPLICATE KEY UPDATE PV=PV+20 的方式来代替先查询表记录是否存在再插入或者更新的业务逻辑。

【索引】
1、一般来说,如果选择性超过 20% 那么全表扫描比使用索引性能更优。比如性别字段。也就是说如果字段值的可能性小于6个的话,是不用建立索引的。

2、组合索引比单一索引更有效,但是需要合理使用,清楚你在干什么,组合索引采用B树类型,最左前缀的特性,创建组合索引的时候,要考虑到具体的使用场景和逻辑。
最左前缀的特性:比如我们建立(filed_1,filed_2,filed_3)的索引的话,相当于我们建立了创建了(filed_1,filed_2,filed_3)、(filed_1,filed_2)以及(filed_1)这三种索引

3、在需要创建索引的字段上,不能设置默认值为NULL,或者也不能插入NULL值,否则索引没有意义。(好像5.1后部分类型的表可以支持NULL的索引)

4、不是索引越多越好,过多索引容易导致更新和插入数据效率大大降低。

5、要理解最基本的索引概念,只有在where和join用到的字段上创建索引才有价值,另外Max()和Min()函数用到的字段也需要

6、Where条件里面,有些情况下也是用不到索引的,比如<>, NOT IN, LIKE语句以 %_开头的时候,这时,可以用 id>m or id,用 NOT EXISTS代替NOT IN,可以用到索引的WHERE条件有:<,<=,=,>,>=,BETWEEN,IN,不以%_开头的LIKE。

7、对于CHAR和VARCHAR字段,只用字段的一部分就可创建索引。创建索引时,使用col_name(length)语法,因为多数字段的前10个字符通常不同,所以此索引不会比使用字段的全名创建的索引速度慢很多。另外,使用字段的一部分创建索引可以使索引文件大大减小,从而节省了大量的磁盘空间,有可能提高INSERT操作的速度。尤其一些md5()后结果的字段比较适用。

【附录】
有关EXPLAIN参数的说明
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:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免