MySQL引擎: InnoDB vs MyISAM – 比较利弊

网上看到的一篇文章,挺不错,虽然是英文但是很易懂,就不翻译了。


The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM. To summarize the differences of features and performance,

  1. InnoDB is newer while MyISAM is older.
  2. InnoDB is more complex while MyISAM is simpler.
  3. InnoDB is more strict in data integrity while MyISAM is loose.
  4. InnoDB implements row-level lock for inserting and updating while MyISAM implements table-level lock.
  5. InnoDB has transactions while MyISAM does not.
  6. InnoDB has foreign keys and relationship contraints while MyISAM does not.
  7. InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
  8. MyISAM has full-text search index while InnoDB has not.

继续阅读MySQL引擎: InnoDB vs MyISAM – 比较利弊

[10/12周主题] – MySQL性能分析命令:Explain 详解

对explain的各个属性一知半解,今天详细的了解一下。 Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看SQL语句的执行效果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句。

本周主题 — MySQL Explain 详解

Explain语法:explain select … from … [where …]

例如:explain select * from news;

输出:

+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

下面对各个属性进行了解:

1、id:这是SELECT的查询序列号

2、select_type:select_type就是select的类型,可以有以下几种:

SIMPLE:简单SELECT(不使用UNION或子查询等)

PRIMARY:最外面的SELECT

UNION:UNION中的第二个或后面的SELECT语句

DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询

UNION RESULT:UNION的结果。

SUBQUERY:子查询中的第一个SELECT

DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询

DERIVED:导出表的SELECT(FROM子句的子查询)

继续阅读[10/12周主题] – MySQL性能分析命令:Explain 详解

用mysqlsla来帮助分析慢查询日志

项目做压力测试的时候难免会碰上数据库压力比预期的高,这个时候就要进行数据库和sql优化,其中一个很好的办法便是分析数据库的慢查询日志。

以前都是手动打开浏览,或者通过Mysql自带的mysqldumpslow来查看,今天分享另外一个工具:mysqlsla

hackmysql.com推出的一款MySQL的日志分析工具

整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等.

格式说明如下:

总查询次数 (queries total), 去重后的sql数量 (unique)

输出报表的内容排序(sorted by)

最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数.

Count, sql的执行次数及占总的slow log数量的百分比.

Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.

95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.

Lock Time, 等待锁的时间.

95% of Lock , 95%的慢sql等待锁时间.

Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.

Rows examined, 扫描的行数量.

Database, 属于哪个数据库

Users, 哪个用户,IP, 占到所有用户执行的sql百分比

Query abstract, 抽象后的sql语句

Query sample, sql语句

除了以上的输出, 官方还提供了很多定制化参数, 是一款不可多得的好工具.

安装和使用也非常简单
安装:

tar zxf http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
cd mysqlsla-2.03
perl Makefile.PL
make
make install

简单使用:

— Slow log: mysqlsla -lt slow slow.log

— General log: mysqlsla -lt general general.log

— Binary log: mysqlbinlog bin.log | mysqlsla -lt binary –

mysqlsla会自动判断日志类型,缺省会打印出前十条结果,可以通过类似“–top 100”的参数来修改,如果觉得每次输入麻烦,还可以建立一个配置文件“~/.mysqlsla”,在文件里写上:top=100,这样就不用每次都手动输入参数了。

今天在做数据库备份时遇到的两个问题

1. 使用备份脚本时, 出现 Error: bin/bash^M: bad interpreter: no such file or directory

原因: 不同操作系统换行符导致, Windows里面换行是CR+LF,Linux只有LF    (CR = \r,LF = \n ….. in ascii code)

最简单的解决办法: Linux下, 运行 dos2unix filename

当然也有其他方法, 网上很多, 由于我们的服务器都是Linux, 这么用最简单了.

2. 将备份数据恢复后, 运行服务器, 出现: Exception Description: Could not deserialize object from byte array.

原因: 往数据库存的数据对象中使用了blob, 在做数据备份的脚本时,  没有考虑这点.

解决方法: 在mysqldump 语句的末尾添加 –hex-blob, 使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 BINARY、VARBINARY、BLOB。

Linux平台下忘记mysql的root密码解决办法

昨天晚上在Linux下更改了Mysql的root用户密码, 结果因为敲错了命令, 导致密码丢失, 无法进去, 无奈网上找忘记mysql密码的解决办法。

google, 百度上方法倒是不少, 大部分也类似, 下面的内容算是在Linux下比较完整有效的解决办法了, 贴出来共享下:

1.首先确认服务器出于安全的状态,也就是没有人能够任意地连接MySQL数据库。
因为在重新设置MySQL的root密码的期间,MySQL数据库完全出于没有密码保护的
状态下,其他的用户也可以任意地登录和修改MySQL的信息。可以采用将MySQL对
外的端口封闭,并且停止Apache以及所有的用户进程的方法实现服务器的准安全
状态。最安全的状态是到服务器的Console上面操作,并且拔掉网线。

2.修改MySQL的登录设置:
# vi /etc/my.cnf
在[mysqld]的段中加上一句:skip-grant-tables
例如:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
保存并且退出vi。

3.重新启动mysqld
# /etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

4.登录并修改MySQL的root密码
# /usr/bin/mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.56

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> USE mysql ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> UPDATE user SET Password = password ( ‘new-password’ ) WHERE User = ‘root’ ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0

mysql> flush privileges ;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

5.将MySQL的登录设置修改回来
# vi /etc/my.cnf
将刚才在[mysqld]的段中加上的skip-grant-tables删除
保存并且退出vi。

6.重新启动mysqld
# /etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

7.恢复服务器的正常工作状态
将步骤一中的操作逆向操作。恢复服务器的工作状态。