mysql性能优化

本文不从DBA的角度去讲解mysql Server的参数如何设置，而是从程序员和架构师的角度，去说明在写程序和设计系统的时候，需要注意的mysql的一些优化点。因此，没有在本文中详细论述服务器参数的设置含义和调优。

一、 关于索引

1. mysql使用的B+tree的深度的计算：

B+树每块数值的大小 = 每个block的大小 / 索引值的大小

比如mysql每个block为4K，索引值设为4个字节的int，那么每个索引块的大小为4k/4 = 1k。

那么存储一亿行的数据，需要的B+树的高度为

h = log1000(一亿) =3 （以1000为底，一亿的对数）。可见B+树的高度不会很高，一般深度都不会超过5.

2. 为了避免随机IO操作，可以建立联合index来避免随机IO操作。即用“索引-值”对来建立一个联合index。这样，就可以直接从索引中读取数据，而不需要根据索引再去磁盘读取实际的数据。

比如 ： select age from user_info where name=lisi;

那么，建立name-age的联合索引，那么从索引就可以直接读取数据。如果仅仅建立name的索引，那么它需要根据name=lisi定位到这行数据的位置，再从这个位置读取出age。

3. 如果仅仅是点查询，而不需要范围查询，那么使用hash索引会比B+tree索引更快。就是仅仅需要 where aaa=bbb这样的查询条件，而不需要where aaa> bbb这样的查询条件。

 4. 使用logloader或者dump的操作速度，会比简单的sql语句快5倍以上。

 5. log放在单独的磁盘上，可缓解io瓶颈。

 6.慢查询是影响mysql性能的主要因素。慢查询主要通过分析慢查询日志来处理。很多现有的工具可以分析。诸如 mysqldumpslow,  mysql_slow_log_filter,  mysql_slow_log_parser, mysqlsal等。

二、关于事务的理解

 事务应该具有ACID属性，（atomicity, consistency, isolation, durability），一致性和持久性比较好理解。原子性和隔离性需要做一些说明。

事务的原子性仅仅指同一个事务本身的原子性。一个事务未执行或者执行一半时，另外的事务（另外一个连接中）是可以并发执行的。这涉及到事务隔离度的概念（isolation）。可以做一个测试，用两个客户端去连接数据库，分别开始事务，分别执行两个进程。只有在事务中执行了upate，insert，delete的语句，并且affect的行数大于0的时候，才会阻塞另外一个事务。而且这个跟数据库的隔离度等级也有关系。

下面是关于隔离度的解释。

（1）查看当前会话隔离级别

select @@tx_isolation;

（2）查看系统当前隔离级别

select @@global.tx_isolation;

（3）设置当前会话隔离级别

set session transaction isolatin level repeatable read;

（4）设置系统当前隔离级别

set global transaction isolation level repeatable read;

（5）命令行，开始事务时

set autocommit=off 或者 start transaction

 

三、关于隔离级别的理解

（1）read uncommitted

可以看到未提交的数据（脏读），举个例子：别人说的话你都相信了，但是可能他只是说说，并不实际做。

（2）read committed

读取提交的数据。但是，可能多次读取的数据结果不一致（不可重复读，幻读）。用读写的观点就是：读取的行数据，可以写。

（3）repeatable read(MySQL默认隔离级别)

在MySQL中，其他事务新增的数据，看不到，不会产生幻读。采用多版本并发控制（MVCC）机制解决幻读问题。

（4）serializable

可读，不可写。写数据必须等待另一个事务结束。

 两个事务是可能发生死锁的，

举个例子：

Transaction1

     Start transaction;

     Update stockprice set close=45.5 where stock_id =4;

     Update stockprice set close=47.7 where stock_id=3;

     Commit;

Transaction2

    Start transaction;

     Update stockprice set close=45.5 where stock_id =3;

     Update stockprice set close=47.7 where stock_id=4;

     Commit;

当两个事务都执行了第一条而未执行第二条时，就发生了死锁。

四、关于数据类型

1. 避免用DEFAULT  NULL数值，原因一方面是为了索引速度更快(尽管在高版本的mysql已经不存在这个问题)，另一方面是避免应用程序产生一些不必要的困惑和bug.

2. int(1)和int(20)的区别仅仅是在显示上，对于计算和存储过程，是完全一样的。

3. DECIMAL在4.1版本及之前，仅仅是存储类型，它的效率比float和double都低。DEMICAL仅仅应该用在金融数字上，因为它可以指定所需要的计算精度。

4. Blob和text是最耗性能的。因为内存存储机制（memory storage engine）不支持这两个类型，因为查询这两个类型的数据都会用到磁盘临时表。尽可能避免使用这两种类型数据。如果要对这两种类型的数据排序，用order by substring(column, length)转成string类型。此时如果substring小，可以转成内存临时表，速度会快很多。额外插一句，字符匹配查找算法的最高效率，不会高于KMP算法的效率。

5.可以用enum代替string类型。Enum最多可以存65535个字符。但此时排序是按enum值排序而非string值排序。

6. datetime 和timestamp的比较。

Datatime:  8字节，与时区无关，缺省值是NULL

Timestamp: 4字节，与时区相关，缺省插入值是当前时间。

如果没有特别需要，建议用timestamp。别用整数型数据来存储时间，尽管可以，但不推荐，因为得不到任何好处。

7.一般不推荐使用bit的数据类型，用tinyint会更利于扩展。Bit set也可以通过tiny int的位运算来替代。

8. 不要滥用MySQL的类型自动转换功能

五、查询优化

1. 用跨库操作语句，可能导致master和slaver不一致现象

就是对db.table这样的语句，可能导致master和slaver不一致。

2.mysql用于处理连接和释放连接的效率很高。它被设计成适合简单快速的查询方式。

因此，如果能将很复杂的查询分割成多个简单的查询，而在应用层将这些小查询连接起来，效率会更高。

主要原因是复杂查询会导致比较大面积的锁定，影响效率。而多个小查询锁的范围就小很多。

连接的开销，在mysql中基本可以忽略。

比如：

Select * from tag

Join tag_post on tag_post.tag_id=tag.id

Join post on tag_post.post_id=post.id

Where tag.tag=’mysql’;

拆分成

Select * from tag where tag=’mysql’;

Select * from tag_post where tag_id=1234;

Select * from post where post.id in (123,2343,4545);

如果应用层能处理这些数据的拆分和组合，那么效率会更高。

这样做的好处：

（1）cache会更有效，不需要中间表临时表的生成。

（2）在应用层做组合，更有利于扩展，可以把某些表单独放在独立的server上，分开放。

（3）锁的范围更小

（4）in的执行效率比join的执行效率高。Mysql的子查询的效率很低。据说6.0会引入semijoin的计划来解决。但目前能少用Join还是少用join.

3、几个原则

（1）优化更需要优化的query

 (2) 定位优化对象的性能瓶颈，是io还是cpu还是内存

（3）明确优化目标

（4）从explain入手，可以用force index来检查走不同索引的效率。SELECT * FROM TABLE1 FORCE INDEX (FIELD1).

（5）打开profile: 用命令”set profiling”命令。用”show profile”查看概要信息。其它profile操作这里不详细叙述。

（6）永远用晓得结果集去驱动大的结果集

（7）尽可能在索引中完成排序

（8）不取多余的数据

（9） 使用最有效的过滤条件

（10）尽可能避免使用复杂的join和子查询

4. 查询语句不要对表格数据进行操作

比如不要使用select * from tablename where  from_unixtime(operatetime) < xxxxx;

而使用select * from tablename where operatetime < unixtimstamp(xxxxx); 

 六、表设计优化

1. 存储的数据编码最好和表的编码一致。

2.根据业务特点合理利用冗余数据，减少Join查询。

3.合理利用数据类型，能简单不要复杂，能定长不要变长，能不用字符型就别用字符型。

4.索引很重要，合理设计索引。

5.将不常用的大字段单独拆分出去。某个字段很大，而且不常用，就不要跟常用的一些信息存储在一张表中。

七、其他

(1)Select for update

用于查询-更新原子操作

(2)LAST_INSERT_ID()

用于保证唯一性，比如订单编号