我的MYSQL学习心得（十六） 优化

10477777

我的MYSQL学习心得（十六） 优化
　　我的MYSQL学习心得（一） 简单语法
　　我的MYSQL学习心得（二） 数据类型宽度
　　我的MYSQL学习心得（三） 查看字段长度

　　我的MYSQL学习心得（四） 数据类型
　　我的MYSQL学习心得（五） 运算符
　　我的MYSQL学习心得（六） 函数
　　我的MYSQL学习心得（七） 查询
　　我的MYSQL学习心得（八） 插入 更新 删除
　　我的MYSQL学习心得（九） 索引
　　我的MYSQL学习心得（十） 自定义存储过程和函数
　　我的MYSQL学习心得（十一） 视图

　　我的MYSQL学习心得（十二） 触发器
　　我的MYSQL学习心得（十三） 权限管理
　　我的MYSQL学习心得（十四） 备份和恢复
　　我的MYSQL学习心得（十五） 日志
　　我的MYSQL学习心得（十七） 复制
　　
　　一步一步走来已经写到了第十六篇了~
　　
　　这一篇主要介绍MYSQL的优化，优化MYSQL数据库是DBA和开发人员的必备技能
　　MYSQL优化一方面是找出系统瓶颈，提高MYSQL数据库整体性能；另一方面需要合理的结构设计和参数调整，以提高
　　用户操作响应的速度；同时还有尽可能节省系统资源，以便系统可以提供更大负荷的服务
　　
　　如果大家看过我写的两篇文章，那么学习MYSQL的索引就不会太难，因为是相通的
　　SQLSERVER聚集索引与非聚集索引的再次研究（上）
　　SQLSERVER聚集索引与非聚集索引的再次研究（下）

　　
　　其实MYSQL也有SQLSERVER堆表的概念
　　myisam允许没有任何索引和主键的表存在，个人觉得没有主键的myisam表都属于堆表，因为MYSQL不支持非主键的聚集索引。
　　innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)
　　详细参考：MyISAM vs InnoDB：MySQL存储引擎详解
　　不过《MyISAM vs InnoDB：MySQL存储引擎详解》文章也有一点错误，意向共享锁就是表锁，其实是不对的
　　
　　1、优化简介
　　mysql优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。
　　例如，通过优化文件系统，提高磁盘I/O的读写速度；通过优化操作系统调度策略，提高mysql在高负荷情况下
　　的负载能力；优化表结构、索引、查询语句等使查询响应更快
　　
　　在mysql中，可以使用show status语句查询一些mysql的性能参数



show status like 'value';
　　其中value是要查询的参数值，一些常用性能参数如下：
　　connections：连接mysql服务器的次数
　　uptime：mysql服务器的上线时间
　　slow_queries：慢查询的次数
　　com_select:查询操作次数
　　com_insert:插入操作次数
　　com_update:更新操作次数
　　com_delete:删除操作次数
　　
　　如果查询mysql服务器的连接次数，可以执行如下语句



show status like 'connections';
　　如果查询mysql服务器的慢查询次数，可以执行如下语句



show status like 'slow_queries';
　　
　　
　　2、优化查询
　　查询是数据库最频繁的操作，提高查询速度可以有效地提高mysql数据库的性能
　　
　　（1）分析查询语句
　　通过对查询语句的分析，可以了解查询语句的执行情况找出查询语句执行的瓶颈
　　mysql中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句
　　
　　EXPLAIN语句的基本语法



EXPLAIN [EXTENDED] SELECT SELECT_OPTION
　　使用EXTENDED关键字，EXPLAIN语句将产生附加信息。SELECT_OPTION是SELECT 语句的查询选项，包括FROM WHERE子句等
　　
　　执行该语句，可以分析EXPLAIN后面的select语句的执行情况，并且能够分析所查询的表的一些特征
　　
　　使用EXPLAIN语句来分析1个查询语句



USE TEST;
EXPLAIN EXTENDED SELECT * FROM PERSON;
　　下面对结果进行解释
　　· id
　　SELECT识别符。这是SELECT的查询序列号。
　　
　　· select_type
　　SELECT类型，可以为以下任何一种：
　　SIMPLE：简单SELECT(不使用UNION或子查询)
　　PRIMARY：表示主查询，或者是最外层的查询语句（多表连接的时候）
　　UNION：表示连接查询的第二个或后面的查询语句
　　DEPENDENT UNION：UNION连接查询中的第二个或后面的SELECT语句，取决于外面的查询
　　UNION RESULT：UNION连接查询的结果
　　SUBQUERY：子查询中的第一个SELECT语句
　　DEPENDENT SUBQUERY：子查询中的第一个SELECT语句，取决于外面的查询
　　DERIVED：导出表的SELECT(FROM子句的子查询)
　　
　　· table
　　表示查询的表
　　
　　· type
　　表示表的联接类型
　　下面给出各种联接类型，按照从最佳类型到最坏类型进行排序：
　　（1）system
　　表仅有一行(=系统表)。这是const联接类型的一个特例。
　　（2）const
　　表最多只有一个匹配行，它将在查询开始时被读取。余下的查询优化中被作为常量对待。const表查询速度很快，因为它们只读取一次。
　　const用于常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。
　　在下面的查询中，tbl_name可以用于const表：



SELECT * from tbl_name WHERE primary_key=1;SELECT * from tbl_name
WHERE primary_key_part1=1和 primary_key_part2=2;
　　
　　（3）eq_ref
　　对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。
　　它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY时。
　　eq_ref可以用于使用“=” 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
　　在下面的例子中，MySQL可以使用eq_ref联接来处理ref_tables：



SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
　　
　　（4）ref
　　对于每个来自于前面的表的任意行组合，将从该表中读取所有匹配的行。
　　如果联接只使用索引键的最左边的前缀，或如果索引键不是UNIQUE或PRIMARY KEY，则使用ref。
　　如果使用的键仅仅匹配少量行，该联接类型是不错的。
　　ref可以用于使用=或操作符的带索引的列。
　　在下面的例子中，MySQL可以使用ref联接来处理ref_tables：



SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
　　
　　（5）ref_or_null
　　该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行，在解决子查询中经常使用该联接类型的优化。
　　在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：



SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
　　
　　（6） index_merge
　　该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了所用到的索引的清单，key_len列包含了所用到的索引的最长长度。
　　
　　（7） unique_subquery
　　该类型替换了下面形式的IN子查询的ref：



value IN (SELECT primary_key FROM single_table WHERE some_expr)
　　unique_subquery是一个索引查找类型，可以完全替换子查询，效率更高。
　　
　　（8） index_subquery
　　该联接类型类似于unique_subquery，不过索引类型不需要是唯一索引，可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：



value IN (SELECT key_column FROM single_table WHERE some_expr)
　　
　　（9） range
　　只检索给定范围的行，使用一个索引来检索行数据。key列显示使用了哪个索引，key_len显示所使用索引的长度。
　　在该类型中ref列为NULL。
　　当使用=、、>、>=、