索引

• Left Join分析
• mysql常用shell
• mysql版本问题
• 全文索引
• 主从库配置
• 常见错误及解决方案

slow sqlbin

 
自动修复数据库  mysqlcheck -A -o -r -uroot -p

myisamchk -r -q *.MYI

指定 sock的php写法

 $link = mysql_connect('10.11.5.132:/opt/mysql_server/mysql.sock', 'ctech', 'HwvAQT');

对latin1编码的mysql， mysqldump 时文件乱码。可以加 –default-character-set=latin1 参数

 /usr/local/mysql/bin/mysqldump  -uroot -psports --default-character-set=latin1 sportsdata > ms.sql 

mysql的一些特性。

对mysql的索引一直有几个问题没有搞清楚。 — 屈伟 2007/04/29 18:43

1. 如果对字段a、b 设置了索引，那么 where a='' and b='' and c='' 时，索引有效吗？

索引有效，mysql针对where条件和表状况决定使用哪一个作为索引，依据通常是使用该索引可以让第一次条件过滤后的结果集最小，如果经常使用这种 a AND b AND c的查询，建立a,b,c三个字段的联合索引将会是更好的选择。explain语句可以告诉你mysql对sql语句分析的结果，比如explain select * from table where a=”foo” and b=”bar” and c=”yeah”–shawn

1. and关系索引生效，or关系索引失效，like关系索引失效（但 like ‘keyword%’索引生效）我一直这么理解的，有错误请指正–kingkong

order by rand 效率问题

这个语句

 SELECT * FROM `news`  ORDER BY rand( )  LIMIT 10 ; 

在记录数为 94,505 的表里执行时间是 ： 43.5606 秒

• http://www.s135.com/read.php?356
• 全文索引 SELECT * FROM `article_1` WHERE MATCH (`description`) AGAINST (’Wino’);

方法一：进入MYSQL安装目录 打开MYSQL配置文件 my.ini 或 /etc/my.cnf查找 max_connections=100 修改为 max_connections=1000 服务里重起MYSQL即可

方法二：MySQL的最大连接数默认是100客户端登录：mysql -uusername -ppassword

设置新的最大连接数为200：mysql> set GLOBAL max_connections=200

显示当前运行的Query：mysql> show processlist

显示当前状态：mysql> show status

退出客户端：mysql> exit

查看当前最大连接数：mysqladmin -uusername -ppassword variables

方法三：以centos 4.4 下面的mysql 5.0.33 手工编译版本为例说明：

　　vi /usr/local/mysql/bin/mysqld_safe

　　找到safe_mysqld编辑它，找到mysqld启动的那两行，在后面加上参数 ：

　　-O max_connections=1500

　　具体一点就是下面的位置：

　　用加粗字体特别说明：

　　then $NOHUP_NICENESS $ledir/$MYSQLD

　　$defaults –basedir=$MY_BASEDIR_VERSION

　　–datadir=$DATADIR $USER_OPTION

　　–pid-file=$pid_file

　　–skip-external-locking

　　-O max_connections=1500

　　» $err_log 2>&1 else

　　eval “$NOHUP_NICENESS $ledir/$MYSQLD

　　$defaults –basedir=$MY_BASEDIR_VERSION

　　–datadir=$DATADIR $USER_OPTION

　　–pid-file=$pid_file

　　–skip-external-locking $args

　　-O max_connections=1500 »

　　$err_log 2>&1”

　　保存。

　　# service mysqld restart

　　# /usr/local/mysql/bin/mysqladmin -uroot -p variables

　　输入root数据库账号的密码后可看到

　　max_connections 1500 即新改动已经生效。

　　还有一种方法，

　　修改原代码:

　　解开MySQL的原代码，进入里面的sql目录修改mysqld.cc找到下面一行：

　　{”max_connections”, OPT_MAX_CONNECTIONS,

　　”The number of simultaneous clients allowed.”, (gptr*) &max_connections,

　　(gptr*) &max_connections, 0, GET_ULONG, REQUIRED_ARG, 100, 1, 16384, 0, 1,

　　0},

　　把它改为：

　　{”max_connections”, OPT_MAX_CONNECTIONS,

　　”The number of simultaneous clients allowed.”, (gptr*) &max_connections,

　　(gptr*) &max_connections, 0, GET_ULONG, REQUIRED_ARG, 1500, 1, 16384, 0, 1,

　　0},

　　存盘退出，然后./configure ;make;make install可以获得同样的效果。

不常用的语句有时候能起很大作用，持续更新吧

1: 确定表的结构

 desc 表名;

2: 处理null,ifnull(字段1,字段2),字段1为空，返回字段2，反之则返回字段1；nvl2(字段1,字段2,字段3),字段1不为空，返回字段3，为空返回字段2，数据类型一致。

 select ifnull(字段1,字段2) from 表名;

3: 联结字符串

 select 字段1||'字符串'||字段2 as '字符串' from 表名;

4: 使用子查询直接装载,/*+append*/为直接装载，对大批量数据处理很快

 insert /*+append*/ into 表名2(字段1,字段2,字段3,字段3) select 字段1,字段2,字段3,字段3 from 表名 字段4 deptno = 20;

5: 使用all,first操作符进行多表插入,

 insert all 
 when 字段=值 then into 表名3
 when 字段=值 then into 表名4
 when 字段=值 then into 表名5
 else into other
 select  * from 表名;

6: 使用default选项更新数据

 update 表名 set 字段=default where 字段2=变量;

7: 复制表数据

 update 表名2 set 字段1=(select 字段1 from 表名 where 字段2=变量)
 where 字段3=(select 字段3 from 表名 where 字段2=变量)

8: 使用truncate table 截断表

 truncate table 表名;

9: 事务提交与回退

 commit,rollbacK;

10: 分组函数只能出现在选择列表,having 和 order by 子句中,order by 放在最后.

 select 字段1,max(字段2),min(字段2) from 表名 group by 字段1 having max(字段2)>100 order by avg(字段2)

11: 使用rollup操作符,生成横向小计统计.cube生成横向和纵向统计.

 select 字段1,max(字段2),min(字段2) from 表名 group by rollup(字段1);
 select 字段1,max(字段2),min(字段2) from 表名 group by cube(字段1);

12: 内连接与外连接

 select 表1.字段,table2.字段 from 表1 [inner|left|right|full] 
 join 表2 on 表1.字段=表2.字段;

13: 合并查询,union,取并集,union all取并集且有重复,intersect,取交集,minus取两个结果的差集.

 select 表1.字段1,表1.字段2 from 表1 
 union [union all|intersect|minus]
 select 表2.字段1,表2.字段2 from 表2; 

14: 强制索引 FORCE INDEX

SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …

以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。

15: 忽略索引 IGNORE INDEX

SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …

以上的SQL语句中，TABLE1表中FIELD1和FIELD2上的索引不被使用。

16: 优先操作 HIGH_PRIORITY

HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。

SELECT HIGH_PRIORITY * FROM TABLE1;

17: 滞后操作 LOW_PRIORITY

LOW_PRIORITY可以使用在insert和update操作中，让mysql知道，这个操作滞后。

update LOW_PRIORITY table1 set field1= where field1= …

18: 延时插入 INSERT DELAYED

INSERT DELAYED INTO table1 set field1= …

INSERT DELAYED INTO，是客户端提交数据给服务器端得MySQL，MySQL返回状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql有空余时，再执行插入操作。另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块，这比执行许多独立的插入要快很多。

不足之处是不能返回自动递增的ID，以及系统崩溃时，MySQL还没有执行插入操作的数据将会丢失。

• MySQL配置文件my.cnf中文版 http://www.phpv.net/html/my.cnf_configure.html