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知允許本地訪問