MySQL优化之二三事

SQL语句之优劣

SELECT * FROM node_revisions WHERE nid IN ( SELECT max(nid) FROM node )
Better
SELECT @maxid :=max(nid) FROM node;
SELECT * FROM node_revisions WHERE nid = @maxid

SELECT * FROM node WHERE nid > 10 OR created > 1209793459;
Better (If either of nid and created is not index)
SELECT * FROM node WHERE nid > 10
UNION
SELECT * FROM node WHERE create > 1209793459

SELECT * FROM node WHERE nid IN (SELECT nid FROM term_node WHERE tid <10 )
Better(using subquery is not a good choice in common)
SELECT n.* FROM node n LEFT JOIN term_node t ON t.nid = n.nid WHERE tid < 10

Group By 之优化

The results of group by will be sort as group by column(s) in default, so if you don’t want to sort result, add ORDER BY NULL:
SELECT count(nid) FROM node group by vid
explain:
| id|select_type|table|type| possible_keys| key|key_len|ref |rows| Extra                     
| 1|SIMPLE    | node| ALL| NULL           |NULL|NULL| NULL| 23 | Using temporary;Using filesort

SELECT count(nid) FROM node group by vid ORDER BY NULL
explain:
| id |  select_type | table  | type  | possible_keys | key    | key_len | ref | rows | Extra 
| 1  |  SIMPLE    | node  | ALL  | NULL                 | NULL | NULL  | NULL |   23 | Using temporary

Sometimes group by sql statement is too slow in large table,the problem maybe is the variable tmp_table_size too small, which is 32M in default, so you need change it to the bigger value.

CREATE INDEX之优化

When you create index on varchar or char column, partial index is better than all index, especially in large column:
CREATE INDEX name_index ON USERS (name (10));
It will create index only the first 10 characters.

MYSQL Server Configuration

Add configuration for mysql section
[mysqld]
skip-name-resolve  #Don’t resolve hostnames

Compile MySQL on Linux

config.mysql.sh
#!/bin/bash
exe=configure   
$exe --prefix=/opt/mysql --localstatedir=/opt/mysql/data --with-mysqld-user=mysql --sysconfdir=/opt/mysql/etc --with-unix-socket-path=/opt/mysql/temp/mysql.sock --with-tcp-port=3307 --with-charset=utf8 --with-extra-charsets=all --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --enable-assembler --enable-thread-safe-client --with-pthread --without-debug --without--isam

#configure的相关参数：
#--prefix=/home/mysql/ \指定安装目录
#--without-debug \去除debug模式
#--with-extra-charsets=gbk,gb2312,utf8 \添加gbk,gb2312,utf8中文字符支持
#--with-pthread \强制使用pthread库(posix线程库)
#--enable-assembler \使用一些字符函数的汇编版本
#--enable-thread-safe-client \以线程方式编译客户端
#--with-client-ldflags=-all-static \以纯静态方式编译客户端
#--with-mysqld-ldflags=-all-static \以纯静态方式编译服务端
#--without-isam \去掉isam表类型支持，现在很少用了，isam表是一种依赖平台的表
#--without-innodb \去掉innodb表支持，innodb是一种支持事务处理的表，适合企业级应用

Problems In MySQL  

远程无法访问

1.mysql>GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY "1234";

2.$sudo gedit /etc/mysql/my.cnf
老的版本中
>skip-networking => # skip-networking
新的版本中
>bind-address=127.0.0.1 => bind-address= 你机器的IP
127.0.0.1知允许本地访问