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

1、 MYSQL专题 1. 查询优化2. 使用索引3. 安全隐患4. 事务与锁 作者：邝伟林 梧桐网络工作室MySQL优化查询操作数据库最重要的一个环节就是查询，如何优化数据库查询加快查询速度与最优化数据库空间显得尤为重要，下面我将从多个方面以分析原理与具体采取怎样的措施的方式进行讲解。(一)给字段选取最合适的数据类型选择CHAR还是VARCHAR ?我们知道，这两个属性都是给字符分配空间的，一个是定长，一个是变长，对于使用MyISAM数据存储引擎的表，在能够比较事先确定长度的情况下，比如一个邮政编码，最好使用CHAR类型,因为查询定长的数据比查询变长的数据要快，再则，尽可能地将字段的宽度设得小些

2、，以免增加不必要的空间；对于MEMORY数据表，由于目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的 CHAR列不一定比使用可变长度VARCHAR列简单。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。 需要注意的是，如果MySQL运行在严格模式，使用CHAR类型时超过列长

3、度的值将不被保存，并且会出现错误；再则，诸如CHAR(10)与VARCHAR(10)检索到的值并不总是相同，因为CHAR列会删除尾部的空格，而VARCHAR列会保留尾部的空格。在选择FLOAT/DOUBLE/REAL（浮点数）时需要说明的是浮点数能够表示更大的数据范围，但会引起精度的问题，即，类型为浮点数的字段值比如123456.31，检索出来时值可能会是123456.30，这时如果用于做数据的比较时就可能出错，要避免这个问题。同理，给数值类型选择字段属性时，尽可能地减小字段的宽度，如可以使用MEDIUMINT满足要求的情况就不要将字段设置为BIGINT,以节省空间，而一旦表占用的空间越小，检

4、索的速度就会越快。(二)BLOB与TEXT的问题第一：在对设置为BLOB和TEXT字段属性的值做大量的删除或更新操作的时，这种值会在数据表中留下很大的"空洞"，以后填入这些"空洞"的记录可能长度不同,为了提高性能,建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理. 第二：在有BLOB或TEXT时，尽可能地避免使用：select *.，这样的查询语句，因为这样可能会引起大量的值在网络上做无谓的传输，此时，我的建议是你可以把有BLOB或TEXT的字段单独拿出来用另外一个表来存储，这样就可以避免做无谓的检索大型的值；还有一种方法就是使用合成

5、的索引，它是根据其它的列的内容（或使用MD5，SHA1,CRC32等函数）建立一个散列值，并把这个值存储在单独的数据列中，通过检索散列值来找到数据行，用散列标识符值查找的速度比搜索BLOB或TEXT列本身的速度快很多。(三)使用NOT NULL把数据列定义成不能为空（NOT NULL），这样有利于简化查询，因为不需要检查值的NULL属性，有利于检索引擎做出判断。(四)使用ENUM如果你拥有的某个数据列的基数很低（包含的不同的值数量有限），那么可以考虑把它转换为ENUM列。ENUM值可以被更快地处理，因为它们在内部表现为数值,通常，检索数值要比检索字符文本要快。(五)优化GROUP BY 语句默

6、认情况下，MySQL排序所有GROUP BY 包含的字段，为了避免排序结果的消耗，你可以指定ORDER BY NULL禁止排序。(六)优化JOIN 语句Mysql4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN) 替代。 假设我们要将所有没有订单记录的用户取出来，可以用下面这个子查询方式完成： SELECT * FROM customeri

7、nfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo ) 如果使用连接(JOIN)来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下： SELECT * FROM customerinfo LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID WHERE salesinfo.CustomerID IS NULL 连接(JOIN)之所以更有效率一些，是因为 MySQL不需要在

8、内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。(七)使用查询缓存(mysql query cache)查询缓存的功能在于存储SELECT查询的文本以及发送给客户端的相应结果。如果随后收到一个相同的查询，服务器会从查询缓存中重新得到查询结果，而不再需要解析和执行查询，这样可以大大提高那些重复执行的SELECT语句的处理速度。查询缓存是基于服务器所接收到的查询字符串的文本内容的。如果某些查询的文本完全相同，那它就认为这些查询是相同的。如果某些查询的字符不同，或者来自那些使用了不同的字符集或通讯协议的客户端，那么它会认为这些查询是不同的。同样，如果某些查询采用其它的功能相当、但实际上没有

9、指向相同的数据表（例如引用了不同的数据库中的同名数据表），那么它们也是不同的。当数据表被更新了之后，涉及到该数据表的任何缓存查询都变成无效的，并且会被丢弃;这可以防止服务器返回过期的结果。 在默认情况下，MySQL对查询缓存的支持是内建的。如果你不希望使用这种缓存，并且想避免它所导致的性能开销，可以使用-without-query-cache选项来运行配置脚本建立服务器。关于查询缓存的操作有一下几个系统变量需要掌握：have_query_cache检查服务器是否支持查询缓存使用语句：SHOW VARIABLES LIKE 'have_query_cache'query_cach

10、e_type查询缓存的操作模式它有三个模式值：0:不要缓存查询结果或检索缓存的结果;1:缓存查询，除非它们以SELECT SQL_NO_CACHE开头;2:根据需要只缓存那些以SELECT SQL_CACHE开头的query_cache_size决定分配给缓存的内存数量，单位是字节说明：即使query_cache_type的值设置为零，query_cache_size指定内存数量也会被分配。为了避免浪费内存，只有在希望激活缓存 的时候才把大小设置成大于零。同时，即使query_cache_type不为零，查询缓存的大小设置为零也会禁用缓存。query_cache_limit设置被缓存的最大结果

11、集大小,比这个值大的查询结果不会被缓存说明：SELECT SQL_CACHE语句会让查询结果被缓存；SELECT SQL_NO_CACHE语句会使查询结果不被缓存；它们并不会受到缓存模式的影响，前提是服务器支持查询缓存并分配了内存大小。使用索引使用索引属于优化查询的范畴，我把它单独成章来讲解，是为了突出它的重要性。关于索引，我将以下面几个方面来阐述：(1)索引的工作原理(2)索引的创建方式(3)设计索引的要点(4)控制MySQL对索引的使用(5)正视使用索引的缺陷(一)索引的工作原理当你在一张没有索引的表里进行某种查询时，服务器会从表的第一行开始逐条进行查找，即使在中间段就已经找到了匹配的数据

12、行，它仍然会继续往下找，直到表尾。如果这张表的数据行数量很大，比如成千上万条，那么，这种查询肯定会耗去很大部分的时间。 使用索引的表的查询方式就不同，当在某列上面使用索引后，该索引就包含了该列每行数值的有序排列和指向各自实际位置的"指针"，查询时，服务器不是逐行地查看数据表，而是首先扫描索引，扫描索引时，也不必从索引头进行线性扫描，而是使用定位算法快速地找到第一条匹配的索引条目，由于索引是事先排序过的，所以，一旦发现了不匹配的记录，就会终止对索引的扫描。通过索引便能够快速地找到查找的数据行。为什么索引能过快速地找到相应的数据行呢？这是因为索引采用了某种数据结构进行查找，不同

13、的MySQL存储引擎，索引采用的数据结构也有所区别，对于MyISAM数据表，使用的是R树索引，并且索引与数据使用单独的文件存储，即MYI与MYD文件;MEMORY(HEAP)存储引擎使用hash索引，但也支持B-树索引,其它的引擎大多使用B树索引，只有MyISAM支持空间类型，使用了R树。举例说明：现有三张表Table1,Table2,Table3,其中Name字段使用了索引，我们的查询任务是查找出表1中在另外两张表中都出现的的名字,查询语句可能为：select Table1.Name from Table1,Table2,Table3 where Table2.Name=Table1.Nam

14、e and Table3.Name=Table2.Name查询流程如下图：我们来比较一下不使用索引与使用索引的查询方式：不使用索引时，存储引擎会扫描所有的数据行，即，如果每个表的行数都为1000行的情况下就得查找1000 x 1000 x 1000(10亿！)，可能是匹配记录数量的上百万倍，明显的浪费了大量的工作，导致性能下降；而使用索引时，它的流程是这样的：(1)选择表1中的第一行并查看该数据行的值.(2)使用表2的索引，直接定位到与表1的值匹配的数据行；类似地，使用表3上的索引，直接定位到与表2的值匹配的数据行。(3)处理表1的下一行并重复前面的过程。执行这样的操作直到t1中的所有数据行都

15、被检查过。在这种情况下，我们仍然对表1执行了完整的扫描，但是我们可以在表2和表3上执行索引查找，从这些表中直接地获取数据行。理论上采用这种方式运行上面的查询会快一百万倍。(二)创建索引的方式索引有三种类型：(1)主键索引，即primary key,只能在创建表时创建；(2)唯一值索引，即unique key,值不能重复；(3)普通索引，即 key,值可以重复。可以在创建表时就创建索引，方式为： PRIMARY|UNIQUE KEY (col_name);比如：create table book(isbn int unsigned not null,title varchar(50) not n

16、ull,author char(20) not null,unique key isbn(isbn),key autor(author)engin=myisam default charset=utf8也可以在表创建完毕后创建索引： CREATE UNIQUEINDEX index_name ON table_name(col_name(length),col_name(lenght);比如：create unique index isbn on book(isbn(5); (三)设计索引时要考虑到的1.索引所在列最好是出现在where，order by等子句中的列，而不是出现在select关

17、键字后选择列表中的列,因为索引最大的特点在于根据查询条件快速定位到查询的数据行中；2.对于重复值少的列进行索引，如果满足查询的结果占整个表记录的30%以上时MySQL会自动放弃使用索引，所以这时你设置的索引并不能起到作用，比如你把一张表的性别字段设置为索引，那么，索引指向的数据行有可能是整个表数据行的50%，这是没有意义的，何况这样还会把时间浪费在索引排序上；如果重复值少，索引指向的数据行范围就会越窄，查询速度就会大幅度加快。3.利用短索引，即指定索引的前缀长度，例如，如果有一个CHAR(200) 列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10个或20个

18、字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘I/O 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值。4.不要过多地索引。不要认为"索引越多，性能越高"，不要对每个数据列都进行索引。每个额外的索引都会花费更多的磁盘空间，并降低写操作的性能。当你修改表的内容的时候，索引就必须被更新，甚至可能重新整理。如果你的索引很少使用或永不使用，你就没有必要减小表的修改操作的速度。此外，为检索操作生成执行计划的时候，MySQL会考虑采用哪一个索引，建立额外的索引会给查询优化器增加更多的工作量，如果索引太多，有可能（未

19、必）出现MySQL选择最优索引失败的情况，维护自己必须的索引可以帮助查询优化器来避免这类错误。对于如何控制MySQL选择最优索引接下来我会谈到。5.利用最左前缀。在创建一个n列的索引时,实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。（这与索引一个列的前缀不同，索引一个列的前缀是利用该的前n 个字符作为索引值。）例如当你准备给索引过的表添加索引时，你可以先考虑下将增加的索引是否是已有的多列索引的最左前缀，如果是这样的，就不用再增加索引。(四)控制MySQL对索引的使用一般情况下，在查询时MySQL将自己决定是否使

20、用索引，使用哪一个索引。但在一些特殊情况下，我们希望MySQL只使用一个或几个索引，或者不希望使用某个索引。这就需要使用MySQL的控制索引的一些查询选项。(1)限制使用索引的范围:USE INDEX有时我们在数据表里建立了很多索引，当MySQL对索引进行选择时，这些索引都在考虑的范围内。但有时我们希望MySQL只考虑几个索引，而不是全部的索引，这就需要用到USE INDEX对查询语句进行设置;比如:SELECT * FROM TABLE1 USE INDEX (FIELD1, FIELD2);这时，无论在TABLE1中已经建立了多少个索引，MySQL在选择索引时，只考虑在FIELD1和FIE

21、LD2上建立的索引。(2)限制不使用索引的范围:IGNORE INDEX如果我们要考虑的索引很多，而不被使用的索引又很少时，可以使用IGNORE INDEX进行反向选取;比如:SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2);这时，TABLE1表中FIELD1和FIELD2上的索引不被使用。(3)强迫使用某一个索引:FORCE INDEX有时我们希望MySQL必须要使用某一个索引(由于 MySQL在查询时只能使用一个索引，因此只能强迫MySQL使用一个索引)。这就需要使用FORCE INDEX来完成这个功能;比如:SELECT * FROM

22、TABLE1 FORCE INDEX (FIELD1);这时,只使用建立在FIELD1上的索引，而不使用其它字段上的索引。(五)正视索引的缺陷首先，索引加快了检索的速度，但是减慢了插入和删除的速 度，同时还减慢了更新被索引的数据列中的值的速度。也就是说，索引减慢了大多数涉及写操作的速度。发生这种现象的原因在于写入一条记录的时候不但需要写入 数据行，还需要改变所有的索引。数据表带有的索引越多，需要做出的修改就越多，平均性能的降低程度也就越大;所以对于写操作更频繁而需检索查询很少的表，最好不要设置索引。其次，索引会花费磁盘空间，多个索引相应地花费更多的磁盘空间。这可能导致更快地到达数据表的大小限制

23、. 对于MyISAM表，频繁地索引可能引起索引文件比数据文件更快地达到最大限制;对于BDB表，它把数据和索引值一起存储在同一个文件中，添加索引引起这种表更快地达到最大文件限制; 在InnoDB的共享表空间中分配的所有表都竞争使用相同的公共空间池，因此添加索引会更快地耗尽表空间中的存储。但是，与MyISAM和BDB表使用的文件不同，InnoDB共享表空间并不受操作系统的文件大小限制，因为我们可以把它配置成使用多个文件。只要有额外的磁盘空间，你就可以通过添加新组件来 扩展表空间;使用单独表空间的InnoDB表与BDB表受到的约束是一样的，因为它的数据和索引值都存储在单个文件中;所以，如果你不需要使

24、用特殊的索引帮助查询执行得更快，就不要建立索引。到此，我们为查询优化与使用索引做一番总结从上面的分析我们不难得出以下几点优化原则：第一：设置表的字段时要恰当，尽可能地减小字段占用空间；第二：SQL查询语句要采用优化方式，并且避免对大宗量的数据段做无谓的检索；第三：使服务器尽可能地从缓存区获取数据，少指向内存或磁盘；第四：合理地使用索引，可以极大地提高服务器性能和查询速度;第五：对数据库的操作，一定要注重优化措施，特别是有大宗量的访问时更是如此，当然，关于优化还有更多的细节需要我们掌握好。 安全隐患数据库的安全隐患来自多方面，比如权限设置不恰当，服务器系统本身存在漏洞等等，下面我以SQL注入为例

25、讲解数据库的安全隐患，以及如何采取防范措施。什么是SQL注入？它是如何入侵数据库系统的？首先用一个简单的示例展示SQL如何注入的：如果程序员对用户传递的数据没有进行处理，使用下面的查询语句：$page=$_GET"p"$sql="select * from tb where page_id='$page'". 在正常输入的情况下，用户进入查询某条信息时，地址栏可能是这样的： 这时你在该地址的后面加入SQL语句，比如简单地输入 "and 1=2",即地址栏里是这样的： 如果出现返回错误信息的情况，就可以确定数据库存在注入

26、的漏洞，通过采取一系列有针对性的SQL盲注技术，最终非法地获取操控数据库系统的权限。SQL注入是存在于常见的多连接的应用程序中一种漏洞，攻击者通过在应用程序中预先定义好的查询语句结尾加上额外的SQL语句元素，欺骗数据库服务器执行非授权的任意查询。这类应用程序一般是网络应用程序，它允许用户输入查询条件，并将查询条件嵌入SQL请求语句中，发送到与该应用程序相关联的数据库服务器中去执行。通过构造一些畸形的输入，攻击者能够操作这种请求语句去获取预先未知的结果.SQL注入攻击利用的是SQL语法，这使得这种攻击具有广泛性。理论上说，对于所有基于SQL语言标准的数据库软件包括SQL Server，Oracl

27、e，MySQL, DB2，Informix等以及与之连接的网络应用程序包括Active/Java Server Pages, Cold Fusion Management, PHP或Perl等都是有效的。当然各种软件有自身的特点，实际的攻击代码可能不尽相同。SQL注入的一般步骤是这样的：第一步，首先像上述那样简单地在地址栏加入SQL语句判断是否存在注入漏洞；第二步，根据各种数据库使用的SQL语句上的差异，如，不同的SQL服务器连结字符串的语法不同，MS SQL Server使用符号"+"来连结字符串，Oracle使用符号"|"来连结，MySQL使用符号&

28、quot;.",进行试探，得出数据库的类型；第三步，使用ORDER BY 加数字的方式试探出字段数，这是为了UNION SELECT 语句做准备的；第四步，通过ORDER BY得出字段数后，再用UNION SELECT 匹配字段数目n-1个NULL值，另外一个在字符，数值，时间类型的值里一一试探，直到可以确定每个字段的数据类型；第五步，在明确了字段数与数据类型后，就可以大大地发挥UNION SELECT 语句的功效了，包括试探出管理员帐号，密码所在表名字段名。至于SQL注入的详细过程我在此就点到为止了，我只是提醒网站开发程序员要注意对用户传递过来的数据进行严格的检查，不能让非法的语句

29、成为SQL查询条件的一部分。防止SQL注入攻击的防御手段从上面谈到的可以看出，SQL之所以能够成功注入，就是让注入者利用了SQL语法上的特点进行攻击的，如何让传递的数据在SQL语句中安全使用，是我们采取防御手段的核心所在。对此，我们可以从一下几个方面防御SQL注入：(1)在有必要的情况下，对提交的数据进行客户端与服务器端两重的的合法性检验，确保服务器按我们指定的方式正常执行；(2)屏蔽出错信息，因为粗错信息里大都包含了了一些重要信息，比如目录，甚至与数据库有关的信息都会因此出来；(3)替换或删除敏感的字符或字符串；(4)对于潜在的而难以预料的错误，比如可能是服务器本身的问题，可以使用跳转页面的

30、方式，以免因此暴露信息；(5)对于PHP+MySQL，要充分利用好异常处理与错误处理（专题PHP对此做了讲述）. 事务与锁（一）事务(Transaction)什么是事务？事务（transation）指的是能够使对数据库的一系列操作，要么都成功，要么都失败的一种机制。事务有什么用？它的作用就在于能够保证彼此有制约有关联的操作成为一个有机的整体，即使发生了错误，或服务器崩溃的情况下，也能保证数据库一致的的状态。举个例子说明：最形象的例子莫过于转账时发生的情况，转账至少包括两个操作，一个是从汇款人的卡里扣掉寄款，一个是收款人的卡要加上这个数目，如果由于某种原因，这其中有一个操作失败了，可能就会造成比

31、较严重的后果，为了防止这单方面的失败，我们就引入了事务这种机制，引入事务后，如果其中有一个操作失败了，那么系统会自动回复到操作前的状态，也就是说，当你的卡上汇出了款，而对方卡上又没有增加款项时，你卡上的款也不会少。怎么创建事务？在默认状态下，MySQL是使用自动提交(autocommit)的方式，也就是说，一旦有操作，它立刻就会执行，并且不会有"撤销"功能，因为它已经把执行结果写入物理磁盘上了。为了实现事务，首先就要取消自动提交的方式；当然你可以先查看一下是否打开了自动提交，语句为：select autocommit; 如果返回值为非0，则证明是自动提交方式，这时你可以使用：set autocommit=0;来关闭自动提交。接着就开始启动一个事务：start transation;这时我们就可以写入我们想要放到事务里的操作语句了。比如：insert into tb1 values(.);i