MYSQL专题查询优化_使用索引_安全隐患_事务与锁.docVIP

MYSQL专题 查询优化 使用索引 安全隐患 事务与锁 作者：邝伟林 梧桐网络工作室 MySQL优化查询 操作数据库最重要的一个环节就是查询，如何优化数据库查询加快查询速度与最优化数据库空间显得尤为重要，下面我将从多个方面以分析原理与具体采取怎样的措施的方式进行讲解。(一)给字段选取最合适的数据类型选择CHAR还是VARCHAR ?我们知道，这两个属性都是给字符分配空间的，一个是定长，一个是变长，对于使用MyISAM数据存储引擎的表，在能够比较事先确定长度的情况下，比如一个邮政编码，最好使用CHAR类型,因为查询定长的数据比查询变长的数据要快，再则，尽可能地将字段的宽度设得小些，以免增加不必要的空间；对于MEMORY数据表，由于目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的 CHAR列不一定比使用可变长度VARCHAR列简单。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。 需要注意的是，如果MySQL运行在严格模式，使用CHAR类型时超过列长度的值将不被保存，并且会出现错误；再则，诸如CHAR(10)与VARCHAR(10)检索到的值并不总是相同，因为CHAR列会删除尾部的空格，而VARCHAR列会保留尾部的空格。在选择FLOAT/DOUBLE/REAL（浮点数）时需要说明的是浮点数能够表示更大的数据范围，但会引起精度的问题，即，类型为浮点数的字段值比如123456.31，检索出来时值可能会是123456.30，这时如果用于做数据的比较时就可能出错，要避免这个问题。同理，给数值类型选择字段属性时，尽可能地减小字段的宽度，如可以使用MEDIUMINT满足要求的情况就不要将字段设置为BIGINT,以节省空间，而一旦表占用的空间越小，检索的速度就会越快。(二)BLOB与TEXT的问题第一：在对设置为BLOB和TEXT字段属性的值做大量的删除或更新操作的时，这种值会在数据表中留下很大的空洞，以后填入这些空洞的记录可能长度不同,为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理. 第二：在有BLOB或TEXT时，尽可能地避免使用：select *...，这样的查询语句，因为这样可能会引起大量的值在网络上做无谓的传输，此时，我的建议是你可以把有BLOB或TEXT的字段单独拿出来用另外一个表来存储，这样就可以避免做无谓的检索大型的值；还有一种方法就是使用合成的索引，它是根据其它的列的内容（或使用MD5，SHA1,CRC32等函数）建立一个散列值，并把这个值存储在单独的数据列中，通过检索散列值来找到数据行，用散列标识符值查找的速度比搜索BLOB或TEXT列本身的速度快很多。(三)使用NOT NULL把数据列定义成不能为空（NOT NULL），这样有利于简化查询，因为不需要检查值的NULL属性，有利于检索引擎做出判断。(四)使用ENUM如果你拥有的某个数据列的基数很低（包含的不同的值数量有限），那么可以考虑把它转换为ENUM列。ENUM值可以被更快地处理，因为它们在内部表现为数值,通常，检索数值要比检索字符文本要快。(五)优化GROUP BY 语句默认情况下，MySQL排序所有GROUP BY 包含的字段，为了避免排序结果的消耗，你可以指定ORDER BY NULL禁止排序。(六)优化JOIN 语句Mysql4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN) 替代。 假设我们要将所有没有订单记录的用户取出来，可以用下面这个子查询方式完成： SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT Cus