我们的生活比蜜甜

mysql 关于索引 - [mysql]

MySQL索引 在数据库表中，对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表：

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL

);

我们随机向里面插入了10000条记录，其中有一条：5555, admin。

在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时，如果在username上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。相反，MySQL会扫描 所有记录，即要查询10000条记录。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

索引的类型包括：

（1）普通索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：

●     创建索引

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

●     修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

●     创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

INDEX [indexName] (username(length))

);

删除索引的语法：

DROP INDEX [indexName] ON mytable;

（2）唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

●     创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

●     修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

●     创建表的时候直接指定

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

UNIQUE [indexName] (username(length))

);

（3）主键索引
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

PRIMARY KEY(ID)

);

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

（4）组合索引
为了形象地对比单列索引和组合索引，为表添加多个字段：

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

city VARCHAR(50) NOT NULL,

age INT NOT NULL

);

为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age

usernname,city

usernname

为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：

SELECT * FROM mytable WHREE username="admin" AND city="郑州"

SELECT * FROM mytable WHREE username="admin"

而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city="郑州"

SELECT * FROM mytable WHREE city="郑州"

（5）建立索引的时机
到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为 MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

SELECT t.Name

FROM mytable t LEFT JOIN mytable m

ON t.Name=m.username WHERE m.age=20 AND m.city='郑州'

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

SELECT * FROM mytable WHERE username like'admin%'

而下句就不会使用：

SELECT * FROM mytable WHEREt Name like'%admin'

因此，在使用LIKE时应注意以上的区别。

（6）索引的不足之处
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

●     虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

●     建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

（7）使用索引的注意事项
使用索引时，有以下一些技巧和注意事项：

●     索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

●     使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

●     索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

●     like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

●     不要在列上进行运算

select * from users where YEAR(adddate)<2007;将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成select * from users where adddate<‘2007-01-01’;

●     不使用NOT IN和<>操作

NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以使用NOT EXISTS代替，id<>3则可以使用id>3 or id<3来代替。

mysqldumpslow分析mysql运行 - [mysql]

mysql自带的这个玩意挺好使的，可以对慢查询里的sql进行排序、计算等操作。

首先得配置my.cnf：
log_slow_queries = /path/slow.log # 定义log位置，注意要有写入的权限

具体的使法如下：
mysqldumpslow -s c -t 40 /path/slow.log

出来的结果是访问次数最多的40个sql，几个参数大概意思如下：

-t 显示多少条
-s 排序，默认是at。c是次数，t是时间，l是lock时间，r是返回结果。如果是ac，at，al，ar则是倒序
-g 可以用正则匹配部分语句

可以参考mysqldumpslow --help，通过这个工具可以看到哪些锁表，或者其他性能问题，还能看到某些SQL_NO_CACHE提示呢，去想办法优化把！

中文全文搜索解决方案sphinx+mysql+mmseg，安装，配置，API调用 - [service]

最近在研究中文搜索引擎，找了许多解决方案，有JAVA下的的Lucene，还有强大的C++下的fietex，哪些比较复杂，对现在的项目来讲，不是很实用，所以把目光瞄向了轻便的sphinx，今天安装还算顺利，以下备忘。
首先，Sphinx默认不支持中文索引及检索，以前用补丁搞定，现在，写补丁那个项目组的人又弄了个CSFT，全称是：CoreSeek Fulltext Search Server,CoreSeek 全文检索服务器。于是呢，就把sphinx集成过来，挺好用的。

1. 去Coreseek官网下载源码，需要mmseg和Coreseek Fulltext Server最好用源码安装，那个RPM文件包反正我没搞定，呵呵。
2. 装mmseg。
      # tar zxf mmseg3_0b3.tar.gz
      # cd mmseg3_0b4;
      # ./configure –prefix=/Data/apps/mmseg (这个目录可以改)
      # make && make install
     运行mmseg，如果成功会有显示
     
3. 装CSFT   
　　# tar zxf csft3_0b4.tar.gz
      # cd csft3_0b3
      # ./configure -prefix=/Data/apps/sphinx  （这个需要mysql和mmseg，别把目录写错了）
      -with-mysql=/Data/apps/mysql        
      -with-mmseg=/Data/apps/mmseg
      -with-mmseg-includes=/Data/apps/mmseg/include/mmseg
      -with-mmseg-libs=/Data/apps/mmseg/lib/
      -with-mysql-includes=/Data/apps/mysql/include/mysql/
      -with-mysql-libs=/Data/apps/mysql/lib/mysql/
      # make && make install

4. sphinx.conf
    安装完成后，会生成/Data/apps/sphinx/etc/sphinx.conf.dist  要更改为.conf
    #cp /Data/apps/sphinx/etc/sphinx.conf.dist /Data/apps/sphinx/etc/sphinx.conf
   
5. 生成字典
    因为用到中文分词，需要生成字典，去安装目录，比如我的是 /home/changyou/mmseg.3.0b3/data/
    #mmseg -u unigram.txt 该命令执行后，将会产生一个名为unigram.txt.uni的文件，将该文件改名为uni.lib，完成词典的构造。
    然后，在/Data/apps/sphinx/var/下建立文件夹 dict，然后把uni.lib放进去
   
6. 配置
    sphinx.conf 文件中需要对charset_dictpath指定uni.lib的路径/Data/apps/sphinx/var/dict/
    还有连接mysql的信息，这个不用说了罢。
    这里有个参数对于中文用户要注意一下：
    charset_type
    字符集编码类型。可选选项，默认为“sbcs”。已知的值包括“sbcs”和“utf-8”。
    对于中文用户，可选的值还可以有“zh_cn.utf-8 ”和“zh_cn.gbk”。当设置charset_type值为上面的两种时，系统默认您开启了中文分词特性。
    但是我设置了zh_cn.utf-8，报错说“unknown charset type 'zh_cn.utf-8'”，不知道为什么。。。。。

7. 运行
     # /Data/apps/sphinx/bin/indexer
     启动索引服务时，如果报下面的错：
     error while loading shared libraries: libmysqlclient.so.15: cannot open shared object file: No such file or directory
     原因应该是因为mysql的lib文件不在系统的lib搜索路径上导致的。
     运行命令，解决：
     # ln -s /Data/apps/mysql/lib/mysql/libmysqlclient.so.15 /usr/lib/libmysqlclient.so.15
     
8. 导入测试数据
     # mysql -uroot -p test < /Data/apps/sphinx/etc/example.sql
     这里千万注意，mysql编码一定要为UTF-8 ！！！
    
9. 测试建立索引
     #indexer --all
     出错，Coreseek Full Text Server 3.1
     Copyright (c) 2006-2008 coreseek.com
     FATAL: no readable config file (looked in /usr/local/sphinx/etc/csft.conf, ./csft.conf)
     这是因为 Coreseek 默认要去读 csft.conf，所以要复制一份
     # cp /Data/apps/sphinx/etc/sphinx.conf /Data/apps/sphinx/bin/csft.conf
     ( 或者，也可以指定conf文件，#indexer --config /Data/apps/sphinx/etc/sphinx.conf --all  这里我没有指定，所以用了2个conf文件，很不靠谱）

10. 然后继续运行 #indexer --all ，应该会看到如下输出：
        Coreseek Full Text Server 2.1
        Copyright (c) 2006-2008 coreseek.com
        using config file '/usr/local/etc/csft.conf'...
        indexing index 'test1'...
        collected 5 docs, 0.0 MB
        sorted 0.0 Mhits, 100.0% done
        total 5 docs, 230 bytes
        total 0.146 sec, 1577.50 bytes/sec, 34.29 docs/sec
        indexing index 'test1stemmed'...
        collected 5 docs, 0.0 MB
        sorted 0.0 Mhits, 100.0% done
        total 5 docs, 230 bytes
        total 0.011 sec, 21879.74 bytes/sec, 475.65 docs/sec

11. 测试全文检索
    # search doc     应该会看到如下输出：
        Coreseek Full Text Server 3.1
         Copyright (c) 2006-2008 coreseek.com
        using config file './csft.conf'...
        index 'test1': query 'doc ': returned 2 matches of 2 total in 0.000 sec
        
        displaying matches:
        1. document=3, weight=1, group_id=2, date_added=Sat Aug 22 03:54:19 2009
                id=3
                group_id=2
                group_id2=7
                date_added=2009-08-22 03:54:19
                title=another doc
                content=this is another group
        2. document=4, weight=1, group_id=2, date_added=Sat Aug 22 03:54:19 2009
                id=4
                group_id=2
                group_id2=8
                date_added=2009-08-22 03:54:19
                title=doc number four
                content=this is to test groups
        words:
        1. 'doc': 2 documents, 2 hits
        index 'test1stemmed': query 'doc ': returned 2 matches of 2 total in 0.000 sec
        displaying matches:
        1. document=3, weight=1, group_id=2, date_added=Sat Aug 22 03:54:19 2009
                id=3
                group_id=2
                group_id2=7
                date_added=2009-08-22 03:54:19
                title=another doc
                content=this is another group
        2. document=4, weight=1, group_id=2, date_added=Sat Aug 22 03:54:19 2009
                id=4
                group_id=2
                group_id2=8
                date_added=2009-08-22 03:54:19
                title=doc number four
                content=this is to test groups
        words:

        1. 'doc': 2 documents, 2 hits
12. 启动searchd进程
     # searchd

PHP接口在源安装包下面的api/下，可以调用测试，很好用，明天继续研究！

接上一回，调用sphinx，我们先插入一个中文的数据：
INSERT INTO `test`.`documents` (`id` ,`group_id` ,`group_id2` ,`date_added` ,title` ,`content`)VALUES (NULL , '3', '9', NOW( ) ,'sphinx中文搜索','Sphinx是一个基于SQL的全文检索引擎，可以结合MySQL,PostgreSQL做全文搜索，它可以提供比数据库本身更专业的搜索功能，使得应用程序更容易实现专业化的全文检索。Sphinx特别为一些脚本语言设计搜索API接口，如 PHP,Python,Perl,Ruby等，同时为 MySQL也设计了一个存储引擎插件');

因为有新数据，所以要重建索引（关于这个问题，以后遇到再研究，如果每天有新数据都要重建索引，很不靠谱）
先kill searchd进程，运行：
# searchd --stop    然后
# indexer --all

1.调用sphinx：
   一是通过Sphinx官方提供的API接口（接口有Python，Java，Php三种版本）
   二是通过安装SphinxSE（具体见1.2部分），然后创建一个中介sphinxSE类型的表，再通过执行特定的SQL语句实现。
现在，我们用API接口，在sphinx安装目录有一个API目录，里面有三个PHP文件：test.php，test2.php和 sphinxapi.php。 sphinxapi.php是sphinx调用接口封装文件，test.php是一个在命令行下执行的查询例子文件，test2.php是一个生成摘要的例子文件。
运行：
# php /home/changyou/csft-3.1/api/test.php -i test1 doc
返回结果：
Query 'doc ' retrieved 2 of 2 matches in 0.000 sec.
Query stats:  'doc' found 2 times in 2 documents
Matches:
1. doc_id=3, weight=100, group_id=2, date_added=2009-08-22 03:54:19
2. doc_id=4, weight=100, group_id=2, date_added=2009-08-22 03:54:19

注意：这里，如果不输入参数 -i test1 doc ，那test.php就不会知道你要查那个索引并且搜索什么字句。
所以，如果想引入生产环境中，我们需要手动修改一下test.php

a.  注释掉10行-43行，
b.  然后添加：$search_info = array("1" => "-i", "2" => "test1", "3" => "$keyword");
c.  foreach ( $search_info as $arg ) 替换原来的foreach
d.  自己定义下$keyword="设计"，记得把文件另存为utf-8
e.   然后，运行：     # php /home/changyou/csft-3.1/api/test.php
   Query '（乱码）' retrieved 1 of 1 matches in 0.000 sec.
   Query stats:
    '（乱码）' found 8 times in 1 documents
Matches:
1. doc_id=5, weight=2, group_id=3, date_added=2009-08-26 20:18:33

哈哈，OK啦！可以将sphinx文件封装好，就可以用与生产环境了。

从上面可以看出Query并不能全部取得我们想要的记录内容，比如说Title,Contents字段就没有取出来，根据官方的说明是sphinx 并没有连到mysql去取记录，只是根据它自己的索引内容进行计算，因此如果想用sphinxAPI去取得我们想要的记录，还必须将Query的结果为依据去查询MySQL才可以得到最终我们想要的结果集。

2.搜索全文时的摘要，高亮显示。
假设我要搜索关键词"test"，通过sphinx可以取到搜索结果，在显示搜索结果时，我希望将含有"test"的进行红色或加粗显示，同时，我不希望全部都显示出来，只需要显示一段摘要，就象google或百度那样，搜出来的结果不是全篇显示，只是部分显示，这个就是摘要的作用。
以test2.php中为例，可以利用BuildExcerpts方法可以实现摘要的功能。具体可以看看test2.php的代码。这里不说了。

3.后记
从网上看到，还可以用SphinxSE方式调用Sphinx。但问题是，我安装的时候，不知道为什么没有安装SphinxSE引擎，在mysql 里用mysql> show engine; 查看。可能是我没有直接编译sphinx然后打2个中文补丁吧，而是用了官网推荐的中文搜索安装包。从索引得到ID号，然后再去查询数据库，也可以实现中文搜索的效果，但是具体效率怎么样就不知道了。
最后，在sphinx配置文件里，如要用自己的数据，需要修改源数据，这一项：sql_query = \SELECT id, group_id, UNIX_TIMESTAMP(date_added) AS date_added, title, content \ FROM documents；
让他自己抓取合适你数据结构的语句即可。

参考：http://blog.c1gstudio.com/archives/722
        http://www.zhengjinjun.com/index.php/2009/06/28/sphinxmysql%E4%B8%AD%E6%96%87%E5%88%86%E8%AF%8D%E5%AE%89%E8%A3%85-%E5%AE%9E%E7%8E%B0%E4%B8%AD%E6%96%87%E5%85%A8%E6%96%87%E6%90%9C%E7%B4%A2/
        http://www.htmldata.cn/?p=171

linux 安装Mysql - [mysql]

很简单，但是，草，过程很痛苦，因为少做一步，最后真是要骂街了。

# useradd mysql

# tar zxvf mysql-5.0.56.tar.gz  //解压
# cd /home/changyou/mysql-5.0.56 
# ./configure --prefix=/Data/apps/mysql  // 指定安装目录，公司的应用都在这里，怪异
--without-debug //去除debug模式
--with-extra-charsets=gb2312 //添加gb2312中文字符支持
--enable-assembler //使用一些字符函数的汇编版本
--without-isam //去掉isam表类型支持 现在很少用了 isam表是一种依赖平台的表
--without-innodb //去掉innodb表支持 innodb是一种支持事务处理的表,适合企业级应用
--with-pthread //强制使用pthread库(posix线程库)
--enable-thread-safe-client //以线程方式编译客户端
--with-client-ldflags=-all-static 
--with-mysqld-ldflags=-all-static //以纯静态方式编译服务端和客户端

# make
# make install
# scripts/mysql_install_db //生成mysql用户数据库和表文件，草，就是这里！！
# cp support-files/my-medium.cnf /etc/my.cnf  //copy配置文件,有large,medium,small三个环境下的,根据机器性能选择,如果负荷比较大,可修改里面的一些变量的内存使用值
# cp support-files/mysql.server /etc/init.d/mysqld  //copy启动的mysqld文件
# chmod 700 /etc/init.d/mysqld
# cd /web
# chmod 750 mysql -R
# chgrp mysql mysql -R
# chown mysql mysql/var -R    (如果没有var目录，可以自己建一个，记得权限)
# cd /web/mysql/libexec
# cp mysqld mysqld.old
# strip mysqld
# chkconfig --add mysqld
# chkconfig --level 345 mysqld on
# service mysqld start
# netstat -atln
看看有没有3306的端口打开,如果mysqld不能启动,看看/web/mysql/var下的出错日志,一般都是目录权限没有设置好的问题
# ln -s /web/mysql/bin/mysql /sbin/mysql
# ln -s /web/mysql/bin/mysqladmin /sbin/mysqladmin
# mysqladmin -uroot password "youpassword"   //设置root帐户的密码
# mysql -uroot -p
# 输入你设置的密码
mysql>use mysql;
mysql>delete from user where password=""; #删除用于本机匿名连接的空密码帐号
mysql>flush privileges;
mysql>quit

注意，如果mysql服务启动不了，提示：
Starting MySQL/etc/init.d/mysqld: line 159: kill: (21755) - No such process
看看是否是启动脚本的问题。
编辑mysqld：
#vi /etc/init.d/mysqld
找到类似这样一行：
$bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &
改为（加上参数--user=root）：
$bindir/mysqld_safe --user=root --datadir=$datadir --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

然后再试：
service mysqld start

分表的操作 - [mysql]

<?php
//分库分表算法
function calc_hash_db($u, $s = 4)
{
    $h = sprintf("%u", crc32($u));
    $h1 = intval(fmod($h, $s));
    return $h1;
}
 
 
for($i=1;$i<100;$i++)
{
	echo calc_hash_db($i);
	echo "<br>";
}
 
function calc_hash_tbl($u, $n = 256, $m = 16)
{
    $h = sprintf("%u", crc32($u));
    $h1 = intval($h / $n);
    $h2 = $h1 % $n;
    $h3 = base_convert($h2, 10, $m);
    $h4 = sprintf("%02s", $h3);
    return $h4;
}

Mysql用户访问权限设置 - [mysql]

1.权限查看
mysql> show grants for 'root'@'localhost' ;
+-----------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                         |
+-----------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

2.权限设置

mysql-> GRANT ALL PRIVILEGES ON BD_name.T_name TO 'user'@'localhost'   IDENTIFIED BY 'passwrod';
mysql-> GRANT ALL PRIVILEGES ON BD_name.* TO 'user'@'%' IDENTIFIED BY 'passwrod' WITH GRANT OPTION;
两个账户均为超级用户账户，一个账户 ('user'@'localhost')只用于从本机连接时，只能操作，DB_name的库里的T_table表，另一个账户('user'@'%')可用于从其它主机连接，只操作DB_name库。

3.具体用户操作语句的权限设置

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON bankaccount.*
    ->     TO 'custom'@'localhost'
    ->     IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON expenses.*
    ->     TO 'custom'@'whitehouse.gov'
    ->     IDENTIFIED BY 'obscure';

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON customer.*
    ->     TO 'custom'@'server.domain'
    ->     IDENTIFIED BY 'obscure';

这3个账户可以用于：
·         第1个账户可以访问bankaccount数据库，但只能从本机访问。
·         第2个账户可以访问expenses数据库，但只能从主机whitehouse.gov访问。
·         第3个账户可以访问customer数据库，但只能从主机server.domain访问。

mysql性能调试，不断总结！ - [mysql]

mysql在遇到严重性能问题时，一般都有这么几种可能：
1、索引没有建好;
2、sql写法过于复杂;
3、配置错误;
4、机器实在负荷不了;

1.索引没建好，一个办法，后台执行脚本，show processlist 实时查看Mysql的工作情况，记录，锁死Mysql的语句。然后，desc（explain）语句，查看用到了什么索引，然后看情况建立合适的索引。（这个索引比较麻烦，有时候这里解决了，其他地方又慢了，所以要通盘考虑）

2.检查查询效率慢的SQL语句，看怎么优化，也是用desc或者explain来看。

3.配置：

配置里主要参数是key_buffer，sort_buffer_size，myisam_sort_buffer_size，
key_buffer=128M：全部表的索引都会尽可能放在这块内存区域内，索引比较大的话就开稍大点都可以，我一般设为128M，有个好的建议是把很少用到并且比较大的表想办法移到别的地方去，这样可以显著减少mysql的内存占用。
sort_buffer_size=1M：单个线程使用的用于排序的内存，查询结果集都会放进这内存里，如果比较小，mysql会多放几次，所以稍微开大一点就可以了，重要是优化好索引和查询语句，让他们不要生成太大的结果集。
另外一些配置：
thread_concurrency=8：这个配置标配=cpu数量x2
interactive_timeout=30
wait_timeout=30：这两个配置使用10-30秒就可以了，这样会尽快地释放内存资源，注意：一直在使用的连接是不会断掉的，这个配置只是断掉了长时间不动的连接。
query_cache：这个功能不要使用，现在很多人看到cache这几个字母就像看到了宝贝，这是不唯物主义的。mysql的query_cache 在每次表数据有变化的时候都会重新清理连至该表的所有缓存，如果更新比较频繁，query_cache不但帮不上忙，而且还会对效率影响很大。这个参数只 适合只读型的数据库，如果非要用，也只能用query_cache_type=2自行用SQL_CACHE指定一些sql进行缓存。
max_connections：默认为100，一般情况下是足够用的，但是一般要开大一点，开到400-600就可以了，能超过600的话一般就有效率问题，得另找对策，光靠增加这个数字不是办法。
其它配置可以按默认就可以了，个人觉得问题还不是那么的大，提醒一下：1、配置虽然很重要，但是在绝大部分情况下都不是效率问题的罪魁祸首。2、mysql是一个数据库，对于数据库最重要考究的不应是效率，而是稳定性和数据准确性。

注意：这里wait_timeout，会kill超过这个时间sleep的语句，还需要改：interactive_timeout！

其他一些：

4.Mysql负载不了

A：通过mysql同步功能将数据同步到数台从数据库，由主数据库写入，从数据库提供读取。

B：最靠谱的，使用memcachedb

C：加入缓存（网上看到的，并没有研究）
加入缓存之后，就可以解决并发的问题，效果很明显。如果是实时系统，可以考虑用刷新缓存方式使缓存保持最新。
在前端加入squid的架构比较提倡使用，在命中率比较高的应用中，基本上可以解决问题。
如果是在程序逻辑层里面进行缓存，会增加很多复杂性，问题会比较多而且难解决，不建议在这一层面进行调整。

Mysql关于sleep进程 - [mysql]

最近公司更换新服务器，出了不少问题，用户登录不上，初步诊断Mysql服务器问题，然后在服务器下uptime和top，发现负载平衡最高达到了36，mysql的CPU占用到了90%，真是大跌眼镜。

进入Mysql，show processlist，有200多个sleep进程，并且有locked进程。杀掉locked，优化了该条语句。但是情况还未好转，后来问了DBA，发现他正进行mysql大数据的操作，汗死。随后关掉网站，提示用户。

过了两天，数据导完。发现sleep进程还是特别多，发现程序用了长连接，pconnect()的链接方式会检查是否有已经存在的链接，如果有的话就是用旧的链接，没有才会开启新链接。mysql_close无法关闭。这种连接方式会占用部分资源，如果不及时关闭，性能不佳的服务器甚至会导致瘫痪。

因此想到修改配置文件：interactive_timeout，wait_timeout这两个值，默认是8小时，改未60秒。然后后台运行kill掉sleep进程的PHP。

可问题是：DBA说sleep连接不用杀，因为系统用得到，我想可能是别的部门在用这台Mysql服务器吧，也就没管了，因为processlist的时候，看到的sleep进程不是我们这边发起的，也就没管了。记录下。

Linux命令+NFS配置+show processlist - [linux]

Linux ：http://hi.baidu.com/shengit/blog/item/3f61d48a6b4521769e2fb49b.html

NFS：http://www.freebsd.org/doc/zh_CN/books/handbook/network-nfs.html
        http://server.ccw.com.cn/yyjq/htm2008/20080415_407746.shtml

show processlist:http://hi.baidu.com/24xinhui/blog/item/e2947f13621a96d8f7039edb.html

查看mysql的sleep进程，并且kill掉，可在后台运行 - [mysql]

mysql数据库的优化（入门） - [mysql]

碰到过好几次这样的Mysql题目,可每次都不能答的完全正确.

现在大概列出如下:
1.数据库的设计
尽量把数据库设计的更小的占磁盘空间.
1).尽可能使用更小的整数类型.(mediumint就比int更合适).
2).尽可能的定义字段为not null,除非这个字段需要null.
3).如果没有用到变长字段的话比如varchar,那就采用固定大小的纪录格式比如char.
4).表的主索引应该尽可能的短.这样的话每条纪录都有名字标志且更高效.
5).只创建确实需要的索引。索引有利于检索记录，但是不利于快速保存记录。如果总是要在表的组合字段上做搜索，那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段.如果总是需要用到很多字段，首先就应该多复制这些字段，使索引更好的压缩。
6).所有数据都得在保存到数据库前进行处理。
7).所有字段都得有默认值。
8).在某些情况下,把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时，它可能使用更小的静态格式表的情况下更是如此。

2.系统的用途
1).尽量使用长连接.
2).explain 复杂的SQL语句。
3).如果两个关联表要做比较话，做比较的字段必须类型和长度都一致.
4).LIMIT语句尽量要跟order by或者 distinct.这样可以避免做一次full table scan.
5).如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename.
6).能使用STORE PROCEDURE 或者 USER FUNCTION的时候.
7).在一条insert语句中采用多重纪录插入格式.而且使用load data infile来导入大量数据，这比单纯的indert快好多.
8).经常OPTIMIZE TABLE 来整理碎片.
9).还有就是date 类型的数据如果频繁要做比较的话尽量保存在unsigned int 类型比较快。

3.系统的瓶颈

1).磁盘搜索.
并行搜索,把数据分开存放到多个磁盘中，这样能加快搜索时间.
2).磁盘读写(IO)
可以从多个媒介中并行的读取数据。
3).CPU周期
数据存放在主内存中.这样就得增加CPU的个数来处理这些数据。
4).内存带宽
当CPU要将更多的数据存放到CPU的缓存中来的话,内存的带宽就成了瓶颈.
---------------------------------------------------------------------------------------
在整体的系统运行过程中，数据库服务器 MySQL 的压力是最大的，不仅占用很多的内存和 cpu 资源，而且占用着大部分的磁盘 io 资源，连 PHP 的官方都在声称，说 PHP 脚本 80% 的时间都在等待 MySQL 查询返回的结果。由此可见，提高系统的负载能力，降低 MySQL 的资源消耗迫在眉睫。

1、页面缓存功能：

页面缓存功能降低MySQL的资源消耗的（系统本身就已经考虑，采用生成HTML页面，大大降低了数据库的压力）。
2、mysql服务器的优化

  2.1、修改全站搜索
    修改my.ini(my.cnf) ，在 [mysqld] 后面加入一行“ft_min_word_len=1”，然后重启Mysql，再登录网站后台（模块管理->全站搜索）重建全文索引。
  2.2、记录慢查询sql语句，修改my.ini(my.cnf)，添加如下代码：
    #log-slow-queries
    long_query_time = 1 #是指执行超过多久的 sql 会被 log 下来
    log-slow-queries = E:/wamp/logs/slow.log #设置把日志写在那里，可以为空，系统会给一个缺省的文件
    #log-slow-queries = /var/youpath/slow.log linux下     host_name-slow.log
    log-queries-not-using-indexes
  2.3、mysql缓存的设置
   mysql>show variables like '%query_cache%';     mysql本身是有对sql语句缓存的机制的，合理设置我们的mysql缓存可以降低数据库的io资源。
    #query_cache_type= 查询缓存的方式(默认是 ON)
    query_cache_size 如果你希望禁用查询缓存，设置 query_cache_size=0。禁用了查询缓存，将没有明显的开销
    query_cache_limit 不缓存大于这个值的结果。(缺省为 1M)
   2.4、查询缓存的统计信息
   mysql> SHOW STATUS LIKE ‘qcache%’;
     Qcache_free_blocks 缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。
    Qcache_free_memory 缓存中的空闲内存。
    Qcache_hits 每次查询在缓存中命中时就增大。
    Qcache_inserts 每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；用 1 减去这个值就是命中率。在上面这个例子中，大约有 87% 的查询都在缓存中命中。
    Qcache_lowmem_prunes 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）。
    Qcache_not_cached 不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。
    Qcache_queries_in_cache 当前缓存的查询（和响应）的数量。
    Qcache_total_blocks 缓存中块的数量。通常，间隔几秒显示这些变量就可以看出区别，这可以帮助确定缓存是否正在有效地使用。运行 FLUSH STATUS 可以重置一些计数器，如果服务器已经运行了一段时间，这会非常有帮助。
   2.5、my.ini(my.conf)配置
    2.5.1、key_buffer_size = 256M
    # key_buffer_size指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。     对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低！
    2.5.2、
    max_allowed_packet = 4M
    thread_stack = 256K
    table_cache = 128K
    sort_buffer_size = 6M
    查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占！如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。
    2.5.3、
    read_buffer_size = 4M
    读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享！
    2.5.4、join_buffer_size = 8M
    联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享！
    2.5.5、
    myisam_sort_buffer_size = 64M
    table_cache = 512
    thread_cache_size = 64
    query_cache_size = 64M
    指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察：
    # > SHOW VARIABLES LIKE '%query_cache%';
    # > SHOW STATUS LIKE 'Qcache%';
    # 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况；
    #如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；
    Qcache_free_blocks，如 果该值非常大，则表明缓冲区中碎片很多
    2.5.6、
    tmp_table_size = 256M
     max_connections = 768
    指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提示，则需要增大该参数值。
    2.5.7、
    max_connect_errors = 10000000
    wait_timeout = 10
    指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
    2.5.8、
    thread_concurrency = 8
    该参数取值为服务器逻辑CPU数量×2，如果服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4 × 2 ＝ 8
     2.5.9、
    skip-networking
    开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！

mysql对两个表的操作 - [mysql]

A。最近用到把一个表的数据重新按主键唯一建立，最简单的方法是：

create   table   tmp   as   select   *   from   youtable   group   by   name；

然后删除不同的表，重命名tmp，因为group   by，所以可以唯一，呵呵

B。将一个表的其中一个字段导入数据导另一个表内

1. INSERT INTO SELECT语句
语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1

2.SELECT INTO FROM语句
语句形式为：SELECT vale1, value2 into Table2 from Table1
要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中

3.Select * from table 1 where ID not in (Select ID from table2)

其实，也可作为表的备份，来看！！！！

mysql中查询相同的字段，两种查询！效率差别很大！ - [mysql]

表内数据为2200个！！！！两种方式，效率差别很大！！
mysql> desc coop_user;
+-----------+-------------+------+-----+---------+-------+
| Field          | Type           | Null    | Key | Default   | Extra  |
+-----------+-------------+------+-----+---------+-------+
| uid             | int(11)        |         | PRI | 0   
| email          | varchar(32) |         | MUL |       
| pass          | varchar(36) |    
| org_email   | varchar(32) |     
| org_pass   | varchar(36) |    
| coop          | varchar(32) |     
| addtime     | varchar(20) |    
+-----------+-------------+------+-----+---------+-------+

用两种方法！！查询coop_user表中，email相同的字段！！

1>  select * from coop_user where email in (select  email  from  coop_user  group  by  email  having  count(*) > 1);   用时：17s
此查询，打印出所有相同的字段：有22行！

2>  select *,count(email) as a from coop_user group by email having a>1 ;  用时0.06S

此查询，只打印出有所有相同的唯一1个信息：有11行！

Mysql:select * into outfile && load data infile 数据交换 - [mysql]

1.

LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。两个命令的FIELDS和LINES子句的语法是相同的。两个子句是可选的，但是如果指定两个，FIELDS必须在LINES之前。

mysql> USE db1;
mysql> LOAD DATA INFILE "./data.txt" INTO TABLE db2.my_table;

2.

select * from tablename into outfile '/tmp/xxx.log';

这里用/tmp目录，因为mysql服务上不知道有没有权限写入，而且此句法只能在mysql服务器下生成文档，不能在本地，所以只有先放到/tmp下，再通过rsnyc同步，或者scp拷贝到本地

如何提高mysql load data infile的速度 - [mysql]

测试数据2.5G，共有数据9427567条。用的mysql的large服务器的配置。
load一次需要大概10分钟左右。
建的表用的是MYISAM，调整了几个session的参数值

运行结果如下

Query OK, 9427567 rows affected, 1558 warnings (3 min 55.21 sec)
Records: 9427567 Deleted: 0 Skipped: 0 Warnings: 0
google到的还可以

如何load数据里面带反斜杠(backslash)”\” 的数据
由于如果你没有指定FIELDS子句，则默认值为假设您写下如下语句时的值：
FIELDS TERMINATED BY ‘\t’ ENCLOSED BY ” ESCAPED BY ‘\\’
所以，如果你的数据里面有反斜杠(backslash)”\”的时候，数据会出现被截断的问题。出现这种问题，只要写上如下的fields子句即可
FIELDS TERMINATED BY ‘\t’ ENCLOSED BY ” ESCAPED BY ”

如何load不同编码的数据
原来用的4.X的mysql，我是select INTO OUTFILE ，只后用iconv,或者其他软件来做。可以参考这里，但是由于这次数据大，用ultraedit等软件打开都要半天。好在新版的mysql可以增加一个新的参数
CHARACTER SET gbk
我的文本数据是GBK的编码，数据表是utf8的，用这种方法测试成功。
如何load的时候只insert特定的列
比如表里面有比元数据多的列。可以在load的时候指定要插入的字段名字。

示例的代码如下：

其中表fb0505里面还有一列是id。