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

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字段属性的值做大量的删除或更新操作的时，这种值会在数据表中留下很大的空洞，以后填入这些空洞的记录可能长度不同,为了提高性能,建议定期使用OPTIMIZETABLE功能对这类表进行碎片整理.第二：在有BLOB或TEXT时，尽可能地避免使用：select*...，这样的查询语句，因为这样可能会引起大量的值在网络上做无谓的传输，此时，我的建议是你可以把有BLOB或TEXT的字段单独拿出来用另外一个表来存储，这样就可以避免做无谓的检索大型的值；还有一种方法就是使用合成的索引，它是根据其它的列的内容（或使用MD5，SHA1,CRC32等函数）建立一个散列值，并把这个值存储在单独的数据列中，通过检索散列值来找到数据行，用散列标识符值查找的速度比搜索BLOB或TEXT列本身的速度快很多。(三)使用NOTNULL把数据列定义成不能为空（NOTNULL），这样有利于简化查询，因为不需要检查值的NULL属性，有利于检索引擎做出判断。(四)使用ENUM如果你拥有的某个数据列的基数很低（包含的不同的值数量有限），那么可以考虑把它转换为ENUM列。ENUM值可以被更快地处理，因为它们在内部表现为数值,通常，检索数值要比检索字符文本要快。(五)优化GROUPBY语句默认情况下，MySQL排序所有GROUPBY包含的字段，为了避免排序结果的消耗，你可以指定ORDERBYNULL禁止排序。(六)优化JOIN语句Mysql4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)替代。假设我们要将所有没有订单记录的用户取出来，可以用下面这个子查询方式完成：SELECT*FROMcustomerinfoWHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)如果使用连接(JOIN)来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：SELECT*FROMcustomerinfoLEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerIDWHEREsalesinfo.CustomerIDISNULL连接(JOIN)之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。(七)使用查询缓存(mysqlquerycache)查询缓存的功能在于存储SELECT查询的文本以及发送给客户端的相应结果。如果随后收到一个相同的查询，服务器会从查询缓存中重新得到查询结果，而不再需要解析和执行查询，这样可以大大提高那些重复执行的SELECT语句的处理速度。查询缓存是基于服务器所接收到的查询字符串的文本内容的。如果某些查询的文本完全相同，那它就认为这些查询是相同的。如果某些查询的字符不同，或者来自那些使用了不同的字符集或通讯协议的客户端，那么它会认为这些查询是不同的。同样，如果某些查询采用其它的功能相当、但实际上没有指向相同的数据表（例如引用了不同的数据库中的同名数据表），那么它们也是不同的。当数据表被更新了之后，涉及到该数据表的任何缓存查询都变成无效的，并且会被丢弃;这可以防止服务器返回过期的结果。在默认情况下，MySQL对查询缓存的支持是内建的。如果你不希望使用这种缓存，并且想避免它所导致的性能开销，可以使用--without-query-cache选项来运行配置脚本建立服务器。关于查询缓存的操作有一下几个系统变量需要掌握：have_query_cache检查服务器是否支持查询缓存使用语句：SHOWVARIABLESLIKE'have_query_cache';query_cache_type查询缓存的操作模式它有三个模式值：0:不要缓存查询结果或检索缓存的结果;1:缓存查询，除非它们以SELECTSQL_NO_CACHE开头;2:根据需要只缓存那些以SELECTSQL_CACHE开头的query_cache_size决定分配给缓存的内存数量，单位是字节说明：即使query_cache_type的值设置为零，query_cache_size指定内存数量也会被分配。为了避免浪费内存，只有在希望激活缓存的时候才把大小设置成大于零。同时，即使query_cache_type不为零，查询缓存的大小设置为零也会禁用缓存。query_cache_limit设置被缓存的最大结果集大小,比这个值大的查询结果不会被缓存说明：SELECTSQL_CACHE语句会让查询结果被缓存；SELECTSQL_NO_CACHE语句会使查询结果不被缓存；它们并不会受到缓存模式的影响，前提是服务器支持查询缓存并分配了内存大小。使用索引使用索引属于优化查询的范畴，我把它单独成章来讲解，是为了突出它的重要性。关于索引，我将以下面几个方面来阐述：(1)索引的工作原理(2)索引的创建方式(3)设计索引的要点(4)控制MySQL对索引的使用(5)正视使用索引的缺陷(一)索引的工作原理当你在一张没有索引的表里进行某种查询时，服务器会从表的第一行开始逐条进行查找，即使在中间段就已经找到了匹配的数据行，它仍然会继续往下找，直到表尾。如果这张表的数据行数量很大，比如成千上万条，那么，这种查询肯定会耗去很大部分的时间。使用索引的表的查询方式就不同，当在某列上面使用索引后，该索引就包含了该列每行数值的有序排列和指向各自实际位置的指针，查询时，服务器不是逐行地查看数据表，而是首先扫描索引，扫描索引时，也不必从索引头进行线性扫描，而是使用定位算法快速地找到第一条匹配的索引条目，由于索引是事先排序过的，所以，一旦发现了不匹配的记录，就会终止对索引的扫描。通过索引便能够快速地找到查找的数据行。为什么索引能过快速地找到相应的数据行呢？这是因为索引采用了某种数据结构进行查找，不同的MySQL存储引擎，索引采用的数据结构也有所区别，对于MyISAM数据表，使用的是R树索引，并且索引与数据使用单独的文件存储，即MYI与MYD文件;MEMORY(HEAP)存储引擎使用hash索引，但也支持B-树索引,其它的引擎大多使用B树索引，只有MyISAM支持空间类型，使用了R树。举例说明：现有三张表Table1,Table2,Table3,其中Name字段使用了索引，我们的查询任务是查找出表1中在另外两张表中都出现的的名字,查询语句可能为：selectTable1.NamefromTable1,Table2,Table3whereTable2.Name=Table1.NameandTable3.Name=Table2.Name查询流程如下图：我们来比较一下不使用索引与使用索引的查询方式：不使用索引时，存储引擎会扫描所有的数据行，即，如果每个表的行数都为1000行的情况下就得查找1000x1000x1000(10亿！)，可能是匹配记录数量的上百万倍，明显的浪费了大量的工作，导致性能下降；而使用索引时，它的流程是这样的：(1)选择表1中的第一行并查看该数据行的值.(2)使用表2的索引，直接定位到与表1的值匹配的数据行；类似地，使用表3上的索引，直接定位到与表2的值匹配的数据行。(3)处理表1的下一行并重复前面的过程。执行这样的操作直到t1中的所有数据行都被检查过。在这种情况下，我们仍然对表1执行了完整的扫描，但是我们可以在表2和表3上执行索引查找，从这些表中直接地获取数据行。理论上采用这种方式运行上面的查询会快一百万倍。(二)创建索引的方式索引有三种类型：(1)主键索引，即primarykey,只能在创建表时创建；(2)唯一值索引，即uniquekey,值不能重复；(3)普通索引，即key,值可以重复。可以在创建表时就创建索引，方式为：[PRIMARY|UNIQUE]KEY(col_name);比如：createtablebook(isbnintunsignednotnull,titlevarchar(50)notnull,authorchar(20)notnull,uniquekeyisbn(isbn),keyautor(author))engin=myisamdefaultcharset=utf8也可以在表创建完毕后创建索引：CREATE[UNIQUE]INDEXindex_nameONtable_name(col_name[(length)][,col_name[(lenght)]]);比如：createuniqueindexisbnonbook(isbn(5));(三)设计索引时要考虑到的1.索引所在列最好是出现在where，orderby等子句中的列，而不是出现在select关键字后选择列表中的列,因为索引最大的特点在于根据查询条件快速定位到查询的数据行中；2.对于重复值少的列进行索引，如果满足查询的结果占整个表记录的30%以上时MySQL会自动放弃使用索引，所以这时你设置的索引并不能起到作用，比如你把一张表的性别字段设置为索引，那么，索引指向的数据行有可能是整个表数据行的50%，这是没有意义的，何况这样还会把时间浪费在索引排序上；如果重复值少，索引指向的数据行范围就会越窄，查询速度就会大幅度加快。3.利用短索引，即指定索引的前缀长度，例如，如果有一个CHAR(200)列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10个或20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘I/O较