http://blog.csdn.net/uestc_huan/article/category/536350 博主首页
http://blog.csdn.net/uestc_huan/article/details/6071081 博文地址
本文不从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等。
关于事务的理解
1. 事务应该具有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
可读，不可写。写数据必须等待另一个事务结束。
7. 两个事务是可能发生死锁的，
举个例子：
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()
用于保证唯一性，比如订单编号