织梦CMS - 轻松建站从此开始!

罗索

MySQL优化之二三事

jackyhwei 发布于 2011-09-27 11:08 点击:次 
MySQL优化之二三事
TAG:

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知允许本地访问

(robinhood)
本站文章除注明转载外,均为本站原创或编译欢迎任何形式的转载,但请务必注明出处,尊重他人劳动,同学习共成长。转载请注明:文章转载自:罗索实验室 [http://www.rosoo.net/a/201109/15055.html]
本文出处:cnblogs.com/robinhood 作者:robinhood
顶一下
(0)
0%
踩一下
(0)
0%
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
用户名: 验证码:点击我更换图片
栏目列表
将本文分享到微信
织梦二维码生成器
推荐内容