MySQL性能优化（一）

 

1.使用索引

不同存储引擎，索引实现不同。在MyISAM数据表，数据行在数据文件中，索引值在索引文件中。InnoDB存储引擎使用一个表空间，在表空间里管理InnoDB数据表的数据和索引（在同一个表空间内），不受操作系统文件尺寸限制，它可以包含多个文件。

1.1 索引的优点

1）一是查询中与where条件匹配；二是关联操作时，将其他表的匹配行快速找出。
2）min()、max()等函数的查询，能够直接快速找到而不用逐行检查查找。
3）索引可快速完成order by 和group by的排序、分类 。
4）使用索引可避免为一个查询整体读取数据行（覆盖索引）。如果只打算从索引数据列中选取值，不打算取数据表其他行，这时就不用再去读数据文件，因为可以直接从索引文件中取值，而不必两次取值。

1.2 索引的缺点

1）对MyISAM表来说，大量建立索引可能导致索引文件比数据文件更快达到其最大尺寸。
2）存储于InnoDB共享空间里的全部InnoDB数据表分享同一个存储空间，添加索引将使存储空间快速减少。（若InnoDB数据表使用单独表空间，因数据索引都在同一个文件里，增加索引会使表尺寸更快达到其最大尺寸）
总之，不要添加无用的索引，若不能提高查询速度就不要添加索引。

1.3 选择索引

1）挑选用来搜索、分类/分组或排序的字段来建立索引，不要给输出显示的字段建立索引。
2）综合考虑各数据列的维度势。“维度”是指它容纳非重复值的个数。如果一个字段为sex，它的数据列中的值只有’男’和’女’，其维度很小，没必要添加索引，添加索引反而增加了储存空间，又起不到提高查询速度的效果。
3）对短小的值进行索引。尽量挑选较“小”的数据类型来添加索引。
4)为字符串值的前缀编制索引。一般字符串的前20/30个字符就能唯一确定数据行。与对全部字符串建立索引相比，它可以提高查询速度，减少储存空间。
5)利用最左前缀。对n个字段建立联合索引时，相当于创建了mysql能使用的n个索引。只有查询语句的where条件包含建立索引时最左边的字段（where条件如果包含其他的建立联合索引时的字段，则最好按照建立索引时各字段的排序顺序来排列），才能使用此联合索引。
6）不要建立太多的索引
7)让索引类型与比较操作的类型保持匹配。“B树”索引<=、 >= 、！= 、like和between操作都比较有效率。而哈希索引只适用等=、不等<=>这种比较，而对范围的比较操作效率较低。
8)利用”慢查询”日志找出性能低劣的查询。可以文本阅读器去查看这种日志，也可用mysqldumpslow命令来汇总它的内容。

2.查询优化程序

1）使用ANANLYSE TABLE语句对数据表进行分析。这将生成关于索引值分布情况的统计数据，它们可以帮助优化器对SQL的使用效果做出更准确的评估。
2）使用EXPLAIN语句来验证优化器操作。①了解不同sql是否影响到索引的使用；②了解添加索引会对查询效果造成什么影响。
3) 向优化器提供提示或必要时屏蔽索引。要联表操作时，在要数据表列表中的某个数据表名字后利用FORCE INDEX、 USE INDEX 或IGNORE INDEX限定词。此外还可以利用STRAIGHT_JOIN(让限制性最强、候选行越少的先执行)强制优化器按特定的顺序使用数据表。STRAIGHT_JOIN可放在select关键字后，也可放在From tabName之后，两种效果一样。
4）尽量使用数据类型相同的数据列进行比较。实质上是减少类型转换的性能消耗。
5)使用索引的数据列在比较表达式中单独出现。不对数据列进行算术或函数计算或作为算术表达式中很复杂项目的一部分时，索引不会被使用,因为它必须对每个数据行计算出表达式的值.
6）不要在LIKE模式的开始位置使用通配符。
7）使用联结查询代替子查询。
8)测试各种查询的变化格式，且要多次运行，找出最高效的SQL。
9）避免过多使用自动类型转换功能。Eg: select * from mytb1 where num_col= ‘4’.

3.选择合适的数据类型

1）尽量使用数值操作，少使用字符串操作。数值之间的比较只用一个操作即可完成，而字符串之间的比较需要多次逐字符或字节的比较。
2）如果“小”类型够用，就不用“大”类型。
3）若能选择数据行的存储格式，尽量选用最合适当前存储引擎的格式。
对MyISAM数据表，选用定长的字符串。对于变长的数据行，删除更新操作后会造成空间碎片，需要定期运行OPTIMIZE TABLE语句保持性能，而定长的数据行则没这样的问题。对于InnoDB数据表，使用变长的字符串更好，因为其内部对定长和变长的数据列不作区分（每行有一个标头，存入指向数据列的指针）。
4）尽量把数据列声明为NOT NULL（最好设置一个非空的默认值）。‘…where clo is (not) null …’语句不能使用索引。
5）考虑使用ENUM数据列(enum内部表示为一系列数值).
6）利用PROCEDURE ANALYSE()语句看看mysql会对数据列的声明提出哪些建议。Eg:select * from tab1 procedure analyse()
7)对容易产生碎片的数据表进行整理。OPTIMIZE TABLE语句对myisam数据表碎片整理。对各存储引擎都适用的整理方法：先用mysqldump转储数据表，再利用转储谁的删除并重建一个。
8）把数据压缩到BLOB或TEXT数据列里。
9）使用人造索引。将其他数据列计算出一个散列值并保存到一个数据列里。
10）避免对很大BLOB或text值进行检索
11）把BLOB或Text数据列剥离到一个单独的数据表里。