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

MYSQL 专题 1. 查询优化 2. 使用索引 3. 安全隐患 4. 事务与锁 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 操