关于MySQL数据库优化的部分整理

在之前我写过一篇关于这个方面的文章

《【原创】为什么使用数据索引能提高效率？（本文针对mysql进行概述）（更新）》

 

这次，主要侧重点讲下两种常用存储引擎。

 

我们一般从两个方面进行MySQL数据库优化：

 

A、SQL语句的优化。

这点，需要我们在写SQL的时候要特别注意，在建表的时候也非常注意。

1 尽量不要在列上进行运算，这样会导致索引失效。

2 使用JOIN时候，应使用小结果集驱动大结果集。把复杂的JOIN拆分成多个QUERY。在多JOIN的时候，最容易导致锁表和堵塞。

3 在使用LIKE进行模糊查询时候，避免使用%%。这样即使有索引的情况下，索引也会失效。

如：

SELECT * FROM TABLENAME WHERE name LIKE '%wang%'

需要进行优化：

SELECT * FROM TABLENAME WHERE name >= 'wang' AND name < 'wanh'

这样，将会使用到索引。前提是你对列name添加了索引。

 

4 仅列出查询的字段，这样节省内存。如果字段不是很多的话，也可以使用 * 

5 使用批量插入语句节省交互。

6 limit基数较大的时候，用BETWEEN。如

   SELECT * FROM TABLENAME LIMIT 100000，10

   优化下

   SELECT * FROM TABLENAME BETWEEN 100000，100010

   但是，用BETWEEN有个问题，如果中间有删除的，则数据肯定少

7 获取多条随机记录的时候，不要使用rand（）。可以用PHP产生随机数，然后使用IN

8 避免使用NULL。

9 不要使用COUNT(id)，使用COUNT(*)

10 尽量在索引中完成相关排序工作

 

 

在关键字段上，值得是出现在WHERE列中的，建不建索引差别很大。查询速度相差近100倍

并不是建了索引就有用，差的索引反而会导致查询速度下降

索引不是越多越好，MySQL维护索引需要成本

 

 

 

B 服务器相关优化

1 选择存储引擎

是选择MYISAM还是 InnoDB，取决于你的实际情况。

先了解下这两个的区别：MYSIAM表级锁。没有事务机制。快读。INNODB支持事务，行级锁。INNODB是行级锁，相对表级锁会带来较大的消耗。但是在系统并发量较大的情况下，性能高于MySIAM。INNODB的索引不仅缓存索引本身还缓存数据。

INNODB需要更多的内存支持。不过，现在硬件的投入都是比较廉价的。

 

在这个地方需要了解个 R/W 读写比。show global status 查看 Com_select 表示SELECT语句执行的次数，Com_insert表示INSERT语句执行的次数。通过计算读类型和写类型的语句比例。我们大概得到个读写比例。

 

理想的情况是100:1，当R/W 小于 10：1的时候，认为是写为主。一般而言，这个值是30:1

 

我们给出个存储引擎选择的原则：

1 采用MyISAM

a  R/W > 100:1且UPDATE较少

b 并发不高不需要事务

c 表数据量较小

d 硬件资源有限 如内存小

 

2 采用INNODB

a R/w < 20:1 OR < 10:1 且update频繁

b 表数据量较大，1000万左右的 并发较大的

c 安全性和可用性较高。如 事务机制