Archive for the 'MySQL' Category

Page 3 of 4

MySQL性能优化4 – 分析 SQL 执行计划

Published on September 8, 2010 in database and MySQL. 0 Comments Tags: .

通过以上步骤查询到效率低的 SQL 语句 后，可以通过 EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序，比如我们想计算 2006 年所有公司的销售额，需要关联 sales 表和 company 表，并且对 profit 字段做求和(sum)操作，相应 SQL 的执行计划如下： Continue reading ‘MySQL性能优化4 – 分析 SQL 执行计划’

Share

MySQL性能优化3 – 定位效率底的 SQL

Published on September 8, 2010 in database and MySQL. 0 Comments Tags: .

一般通过以下两种方式定位执行效率较低的 SQL 语句。

通过慢查询日志定位那些执行效率较低的 SQL 语句，用 --log-slow-queries[=file_name]选项启动时， mysqld 会 写一个包含所有执行时间超过  long_query_time 秒的 SQL 语句的日志文件，通过查看这个日志文件定位效率较低的 SQL 。
慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的 执行情况，同时对一些锁表操作进行优化。 Continue reading ‘MySQL性能优化3 – 定位效率底的 SQL’

Share

MySQL性能优化2 – 了解 SQL 执行频率

Published on September 8, 2010 in database and MySQL. 0 Comments Tags: .

MySQL 客户端连接成功后，通过 show [session|global]status 命令 可以提供服务器状态信息，也可以在操作系统上使用 mysqladmin extended-status 命令获得这些消息。 show [session|global] status 可以根据需要加上参数“ session ”或者“ global ”来显示 session 级（当前连接）的统计结果和 global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“ session ”。 Continue reading ‘MySQL性能优化2 – 了解 SQL 执行频率’

Share

MySQL性能优化1 – 简介

Published on September 8, 2010 in database and MySQL. 0 Comments Tags: .

简单的说， SQL 优化就是通过使用一些工具和方法，找到那些问题 SQL, 并对其执行计划进行分析和改进，从而改善和提高这些问题 SQL 的执行效率，使他们更快、更好的执行。

SQL 优化中，了解和使用 EXPLAIN( 执行计划 ) ，是非常重要的。 EXPLAIN 可以告诉你 SQL 语句在 MySQL 中是如何执行的。 Continue reading ‘MySQL性能优化1 – 简介’

Share

ubuntu下MySQL服务器更改目录后问题

Published on August 11, 2010 in MySQL and ubuntulinux. 0 Comments Tags: , .
1
2
3
4
5
6
100811 18:42:06  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

修改下面文件中得权限列表可解决问题。

1
/etc/apparmor.d/usr.sbin.mysqld
1
2
/web/data/mysql/ r,
/web/data/mysql/** rwk,
Share

How to Repair a Crashed MySQL Table

Published on August 10, 2010 in MySQL. 0 Comments Tags: , , .

Today, I had a server crash when the /var partition filled up while reindexing. I attempted to clear out extra log files and such but df was still showing greater than 100% utilization. Checking /var/db/mysql showed I had one table that was taking up too much space. du -d 1 -h is a very useful command when tracking down the largest folder usage on partition.

Jul 21 07:39:06 plab2catsg01 kernel: pid 36500 (mysqld), uid 88 inumber 329992 o
n /var: filesystem full
Jul 21 07:39:22 plab2catsg01 kernel: pid 36500 (mysqld), uid 88 inumber 353413 o
n /var: filesystem full
Jul 21 07:39:23 plab2catsg01 kernel: pid 36500 (mysqld), uid 88 inumber 353288 o
n /var: filesystem full

I attempted to stop MySQL by executing /usr/local/etc/rc.d/mysql-server stop and unfortunately, it kept looping while trying to stop the process. I was eventually forced to reboot the server.

On reboot, I thought all was well but then I found the Eventum application I use wasn't showing support emails. I checked the MySQL log files:

plab2catsg01# tail -f plab2catsg01.ten-net.net.err
...
090721  9:15:54 [ERROR] /usr/local/libexec/mysqld: Table './eventum/eventum_mail
_queue' is marked as crashed and last (automatic?) repair failed
090721  9:15:54 [ERROR] /usr/local/libexec/mysqld: Table './eventum/eventum_mail
_queue' is marked as crashed and last (automatic?) repair failed
090721  9:15:55 [ERROR] /usr/local/libexec/mysqld: Table './eventum/eventum_mail
_queue' is marked as crashed and last (automatic?) repair failed
090721  9:15:55 [ERROR] /usr/local/libexec/mysqld: Table './eventum/eventum_mail
_queue' is marked as crashed and last (automatic?) repair failed
...

Continue reading 'How to Repair a Crashed MySQL Table'

Share

Use MySQL Regexp Sample

Published on August 10, 2010 in MySQL. 0 Comments Tags: , .
1
DELETE FROM `google_data` WHERE site_id=4 AND url REGEXP '\.php\\?matchid=[0-9]+$'
Share

MySQL主从热备配置(含innodb)

Published on March 9, 2010 in MySQL. 0 Comments Tags: , , .

Replication Mysql

mysql主从热备有2种配置方式,备份某些库或者忽略备份某些库,建议选择后者。
Continue reading ‘MySQL主从热备配置(含innodb)’

Share

提高mysql随机查询的效率

Published on July 26, 2008 in MySQL and PHP. 0 Comments Tags: , .
  1. 基本使用:
    1
    SELECT * FROM `table` ORDER BY RAND() LIMIT 0,10;

    但是在ORDER BY后面用到RAND(),查询速度非常慢.

  2. MAX()*RAND()提高效率
    1
    2
    3
    SELECT * FROM `table`
    WHERE id >= (SELECT floor( RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`)) + (SELECT MIN(id) FROM `table`)))
    ORDER BY id LIMIT 1;
  3. JOIN
    1
    2
    3
    4
    SELECT *
    FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM `table`)-(SELECT MIN(id) FROM `table`))+(SELECT MIN(id) FROM `table`)) AS id) AS t2
    WHERE t1.id >= t2.id
    ORDER BY t1.id LIMIT 1;

Continue reading ‘提高mysql随机查询的效率’

Share

中文词库

Published on July 13, 2008 in MySQL and PHP. 0 Comments Tags: , .

最近遇到和搜索有关的工作，所以找了一个20万记录的中文词库，再加以缩减就可以在不同的项目中应用了。 ;)

Share