MySql 100118：MySQL Optimization 优化(1/2)

@ http://zcatt.cublog.cn

1.    基本的性能瓶颈

1）磁盘寻道时间， disk seeks

一般小于10ms左右

 

2）磁盘读写带宽，disk reading and writing

单磁盘一般是10-20MB/s

 

3）CPU速度， CPU cycles

 

4）内存带宽，memory bandwidth

 

 

2.    MySQL的几个特别点

1） 使用MyISAM引擎时，MySQL使用的是fast table locking，支持多读者单写者。但这种方式对于 在一个table上持续同时有update和 slow select操作的情况，效率低下。

 

2）对于 not strict SQL mode，或者使用IGNORE修饰的INSERT or UPDATE，MySQL处理notransactional tables时，会按如下规则进行：

a．所有columns都有default values

b．如果插入的值超出范围，MySQL会自动设置一个‘最可能的值’，而不是报错。例如，数字值将设置成0，最小或最大值。字符串设置成空串。

c．所有算式都会返回值，即使错误，也不会报告。例如，1/0返回NULL。

 

 

 

3.    Benchmark

MySQL源码包中的sql-bench目录下是一些性能评价程序。还可以从其他地方找到benchmark程序。不赘述。

 

4. 优化SELECT和其他语句

1）简化权限设置。复杂权限和访问限制会使权限检查的开销增大。

 

例如，对table或column的许可限制，都会在相应操作上增加检查的开销。为了提高效率，应当最小化权限限制。

 

2）使用EXPLAIN。

 

使用EXPLAIN可以得到MySQL执行SELECT语句的信息。据此可以优化数据库结构和查询方式。细节不赘述，参见手册。

 

EXPLAIN [EXTENDED | PARTITIONS] SELECT select_options

 

3）使用index

加快SELECT...WHERE语句的一个一般方法是考虑使用index.

 

 

 

（这节内容较多，待学习）

 

 

5.Locking Issues

内部锁，interal locking, 是MySQL server自己使用，用于管理多线程（或会话 session）竞争。外部锁，exteranl locking，是MySQL server和其他外部程序用于协同管理表文件的访问的。

 

5.1 内部锁

MyISAM, MEMORY，和MERGE表使用table-level locking; InnoDB表使用row-level locking。

row-level有deadlock的问题。

下面的场景应当使用table locks:

1) 绝大多数是读操作；

2）Statements for the table are a mix of reads and writes, where writes are updates or deletes for a single row that can be fetched with one key read:

3）SELECT combined with concurrent INSERT statement, and very few UPDATE or DELETE statements

4)Many scans or GROUP BY operations on the entire table without any writers

 

UPDATE优先于SELECT获得lock.

 

5.1.1 table locking issues

InnoDB避免使用LOCK TABLES。

For large tables, table locking is often better than row locking, but there are some disadvantages:

 

1）Table locking enables many sessions to read from a table at the same time, but if a session wants to write to a table, it must firstget exclusive access. During the update, all other sessions that want to access this particular table must wait until the update isdone.

 

2）Table locking causes problems in cases such as when a session is waiting because the disk is full and free space needs to becomeavailable before the session can proceed. In this case, all sessions that want to access the problem table are also put in a waiting state until more disk space is made available.

 

The MyISAM storage engine supports concurrent inserts to reduce contention between readers and writers for a given table: If a MyISAM table has no holes in the data file (deleted rows in the middle), an INSERT statement can be executed to add rows to the end of the table at the same time that SELECT statements are reading rows from the table. If there are multiple INSERT statements, they are queued and performed in sequence, concurrently with the SELECT statements.

 

5.1.2 External Locking

omit