MySql优化之二三事

SQL语句之优劣

• SELECT * FROM node_revisions WHERE nid IN ( SELECT max(nid) FROM node )

Better way

SELECT @maxid :=max(nid) FROM node;

SELECT * FROM node_revisions WHERE nid = @maxid

• SELECT * FROM node WHERE nid > 10 OR created > 1209793459

Better way (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 way (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 &lt; 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";

允许xoops_root用户可以从任意机器上登入MySQL。

$sudo gedit /etc/mysql/my.cnf

老的版本中

&gt;skip-networking =&gt; # skip-networking

新的版本中

&gt;bind-address=127.0.0.1 =&gt; bind-address= 你机器的IP

127.0.0.1知允许本地访问