Archive for category MySQL

添加与不添加索引的差距

Posted by Neo in MySQL on 2011/02/26

创建两张表user,user_on_index

CREATE TABLE USER(
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  dob datetime NOT NULL,
  gender enum("m","f") NOT NULL,
  KEY(first_name,last_name,dob)
)

CREATE TABLE user_no_index(
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  dob datetime NOT NULL,
  gender enum("m","f") NOT NULL
)

向两个表里面各插入10W数据(使用ruby脚本)

>> 1.upto(100000) do |i|
?>     User.create :first_name => "meng"+i.to_s,:last_name => "qinglei"+i.to_s,:dob => Time.now,:gender => "m"
>>   end

>> 1.upto(100000) do |i|
?>     UserNoIndex.create :first_name => "meng"+i.to_s,:last_name => "qinglei"+i.to_s,:dob => Time.now,:gender => "m"
>>   end

执行select SQL_NO_CACHE * from user where first_name like “meng100%”
select SQL_NO_CACHE * from user_no_index where first_name like “meng100%”
时间差距很大

 p Benchmark.realtime{
     UserNoIndex.find_by_sql("select SQL_NO_CACHE * from user_no_index where first_name like 'meng100%'")
   }
0.0462138652801514


 p Benchmark.realtime{
     User.find_by_sql("select SQL_NO_CACHE * from user where first_name like 'meng100%'")
  }
0.00258684158325195

 a = 0.0462138652801514
 b = 0.00258684158325195
 a/b == 17.8649769585254

1 Comment

mysql opened tables

Posted by Neo in MySQL on 2010/10/04

1.mysql’s opened_tables
查看mysql的opend_tables有两种方法，第一种是

1.show status like '%Opend_tables%';
        +---------------+-------+
        | Variable_name | Value |
        +---------------+-------+
        | Opened_tables | 0     |
        +---------------+-------+

    2.mysqladmin -uroot -p status
        Uptime: 24704  Threads: 4  Questions: 5463  
        Slow queries: 1  Opens: 1410  Flush tables: 1  
        Open tables: 4  Queries per second avg: 0.221

有的时候出现opened_tables值是12,但是你只有2张表的时候,这个很两人费解

因为mysql是多线程的，所以有的时候连接数量多的话mysql的每个线程会以不同的状态去打开一个表

2.mysql怎样打开和关闭表
table_cache、max_connection和max_tmp_tables系统全局变量决定mysql打开的最大文件数，如果增大他们其中的一个或者多个，应该检查一下当前正在使用的操作系统每个进程最大的打开文件数

table_cache与max_connections有关。例如，200个并发连接，至少要将table_cache的大小调整到200*N，N是一个查询里面join表的数量，还要额外的考虑到使用tempfile的情况。

确定你的操作系统能够处理table_cache大小的打开文件数量，如果table_cache设置的很大，mysql会发生打开过量的文件，从而导致拒绝连接。也要注意myisam存储引擎，没个打开的table会维持两个文件句柄。配置mysql最大打开文件数量是open-files- limit。

table_cache默认的大小是64,mysql可能会维持更多的连接，以确保能正常的完成查询。

mysql 将一个table缓存删除的案例：

1.table cache满了，但是某个线程打开一个表，并且表没有在缓存中。

2.cache的数量超出了table_cahce的大小，并且一个被打开的表一段时间内不会再被用了。

3.flush tables操作

handler table_name open打开一个表直到执行handler table_name close为止!

http://www.devshed.com/c/a/MySQL/MySQL-Optimization-part-2/4/

没有评论

rails sphinx全文检索

Posted by Neo in Linux, MySQL, Ruby On Rails on 2010/09/26

1.安装mmseg这个ruby中文分词的扩展

wget http://www.coreseek.cn/uploads/csft/3.1/Source/mmseg-3.1.tar.gz
    tar xzvf mmseg-3.1.tar.gz
    cd mmseg-3.1
    chmod a+x configure
    ./configure && make && make install

安装ruby扩展

cd ruby
    cp /usr/local/include/mmseg/*.h .
    cp ../src/*.h .
    cp ../src/css/*.h .
    ruby extconf.lin.rb
    make && make install
cd ../data
    mmseg -u unigram.txt
    将生成的词库重命名为uni.lib
    cp uni.lib ../ruby
    cd ../ruby
    ruby test.rb

如果运行成功的话，安装完毕了

2.安装 csft3.2

 wget http://www.coreseek.cn/uploads/csft/3.2/csft-3.2.12.tar.gz
    tar xzvf csft-3.2.12.tar.gz
    cd csft-3.2.12
    ./configure --with-mysql=/usr/local/mysql
    make && make install

3.安装sphinx插件
ruby script\plugin install git://github.com/freelancing-god/thinking-sphinx.git
注释掉： Rails.configuration.cache_classes = false,这个在我这里是第行

rake ts:config
vi config/development.sphinx.conf
将 charset_type = utf-8 改为 charset_type = zh_cn.utf-8
并且在下面添加：charset_dictpath = /usr/local/csft/var/data # 这里的意思是存放词库的地址,也就是刚刚使用mmseg -u生成的那个文件

4.测试

1 Comment

mysql order by 优化

Posted by Neo in MySQL on 2010/09/02

排序的时候能用到索引的,key为一个复合的索引

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

不能使用索引排序的查询

1.不同的索引

SELECT * FROM t1 ORDER BY key1,key2

2.不连贯的索引使用

SELECT * FROM t1 WHERE key_part1='' ORDER BY key_part3

3.desc和asc混合

SELECT * FROM t1 ORDER BY key_pary1 ASC,key_part2 DESC

4.order by 里面有表达式

SELECT * FROM t1 ORDER BY abs(KEY)

5.join table 的时候，order by内不是第一个非常量表的

6.order by 在有OR字句的时候不起作用，一把使用union

SELECT * FROM t1 force INDEX (key1_key2_key3)
WHERE key1=1 OR key1=2
ORDER BY key2,key3 LIMIT 5, 5;

使用union的话会好一些:

(SELECT * FROM t1 force INDEX (key1_key2_key3)
     WHERE key1=1 ORDER BY key2,key3 LIMIT 10)
UNION
(SELECT * FROM t1 force INDEX (key1_key2_key3)
     WHERE key1=2 ORDER BY key2,key3 LIMIT 10)
ORDER BY key2,key3 LIMIT 5, 5

mysql performace blog
Suboptimal ORDER BY implementation, especially together with LIMIT is often the cause of MySQL
Performance problems.
Here is what you need to know about ORDER BY … LIMIT optimization to avoid these problems

不理想的order by语句，特别是和limit搭配在一起使用的，经常造成mysql的性能问题。

http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

mysql docs
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

没有评论

mysql子查询中使用sum排序

Posted by Neo in MySQL on 2010/08/30

I’ve a basic table like and want to SUM the top 5 values. For example if I
have

id, rating
0, 10
1, 25
2, 5
3, 10
4, 50
5, 1
6, 15
7, 20
8, 9

I want my query to sum the values

4,50
1,25
7,20
6.15
0,10

Suming a value of 120

Any suggestions on how to achieve this ?

Cheers

邮件列表里面的问题,我给的答案:

SELECT SUM(t1.rating) FROM your_table t1 JOIN
  (SELECT * FROM your_table ORDER BY rating DESC LIMIT 5) t2
  ON t1.rating=t2.rating

没有评论