MySql中的常见技术

引用

第一部分：引擎选择
MySQL 存储引擎该如何选择
一、InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

二、MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及设计到安全性就高的应用

三、InnoDB支持外键，MyISAM不支持

四、MyISAM是默认引擎，InnoDB需要指定

五、InnoDB不支持FULLTEXT类型的索引

六、InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表

七、对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引

八、清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表

九、InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

通过以上九点区别，结合个人博客的特点，推荐个人博客系统使用MyISAM，因为在博客里主要操作是读取和写入，很少有链式操作。所以选择MyISAM引擎使你博客打开也页面的效率要高于InnoDB引擎的博客，当然只是个人的建议，大多数博客还是根据实际情况下谨慎选择。我的小站目前就使用MyISAM引擎！


第二部分：数据库性能的优化
主要分三个方面进行优化：优化查询，优化数据结构，优化MySQL服务器。
优化查询
但是有些时候即使查询时使用的是索引，但索引并没有起作用。比如使用了LIKE关键字进行查询时，如果匹配字符串的第一个字符为‘%’，索引不会被使用。如果‘%’不是在第一个位置，索引就会被使用。
优化子查询
很多查询中需要使用子查询。子查询可以使查询语句很灵活，但子查询的执行效率不高。MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询 语句在临时表中查询记录。查询完毕后，MySQL需要插销这些临时表。所以在MySQL中可以使用连接查询来代替子查询。连接查询不需要建立临时表，其速 度比子查询要快。
优化数据库结构
1将字段很多的表分解成多个表
   有些表在设计时设置了很多的字段。但是这个表中的有些字段的使用频率很低。当这个表的数据量很大时，查询数据的速度就会很慢。对于这种字段特别多的并且有字段的使用频率不高的表，就可以将其分解成多个表。
2增加中间表
  有时需要经常查询某两个表中的几个字段。如果经常进行联表查询，就会降低MySQL数据库的查询速度。对于这种情况可以建立中间表来提高查询速度。
3增加冗余字段
设计数据库表时尽量让表达到三范式。但是，有时为了提高查询速度，可以有意识的在表中增加冗余字段。表的规范程度越高，表与表之间的关系就越多，查询时可 能经常需要在多个表之间进行连接查询，而进行连接查询会降低查询速度。
4优化插入记录的速度
       插入记录时，索引和唯一性校验都会影响到插入记录的速度。而且，一次插入多条记录和多次插入一条记录所消耗的时间是不一样的。根据这些情况，分别进行不同的优化。
       禁用索引：插入记录时，MySQL会根据表的索引对插入的记录进行排序。如果插入大量数据时，这些排序会降低插入的速度。为了解决这种情况，在插入记录之 前先禁用索引。等插入之后再启用索引。对于新创建的表，可以先不创建索引，等记录都导入以后再创建索引。这样可以提高导入数据的速度。
       ALTER TABLE 表名 DISABLE KEYS；
       ALTER TABLE 表名 ENABLE KEYS；
       优化INSERT语句：当大量插入数据时，建议使用一个INSERT语句插入多条记录，而不是使用多次INSERT语句。这样可以减少与数据库之间的连接等操作。
5分析表，检查表，和优化表
分析表的主要作用是分析关键字的分布。检查表的作用是检查表是否存在错误。优化表主要作用是消除删除或者更新造成的空间浪费。
分析表   ANALYZE TABLE 表名；  使用ANALYZE TABLE分析表的过程中，数据库系统会对表加一个只读锁。在分析表的过程中，只能读取表的内容，不能插入和更新表的内容。ANALYZE TABLE 语句能够分析InnoDB和MyISAM类型的表。
      检查表使用CHECK TABLE语句。在执行过程中也会给表加上只读锁。
      优化表使用OPTIMIZE TABLE语句。只能优化表中的VARCHAR,BLOB,TEXT类型的字段。OPTIMIZE TABLE语句可以消除删除和更新造成的磁盘碎片，从而减少空间浪费。因为如果一个表使用了TEXT或者BLOB这样的数据类型，那么更新，删除等操作就 会造成磁盘空间的浪费。因为，更新和删除操作以后，以前分配的磁盘空间不会自动回收。使用OPTIMIZE TABLE语句可以将这些磁盘碎片整理出来，以便再利用。
优化MySQL服务器
硬件上的优化：增加内存和提高磁盘读写速度，都可以提高MySQL数据库的查询，更新的速度。另一种提高MySQL性能的方式是使用多块磁盘来存储数据。因为可以从多块磁盘上并行读取数据，这样可以提高读取数据的速度。
       MySQL参数的优化：内存中会为MySQL保留部分的缓冲区。这些缓冲区可以提高MySQL的速度。缓冲区的大小都是在MySQL的配置文件中进行设置的。
       下面对几个重要的参数进行详细介绍：
    key_buffer_size：表示索引缓存的大小。这个值越大，使用索引进行查询的速度就越快
    table_cache：表示同时打开的表的个数。这个值越大，能同时打开的表的个数就越多。这个值不是越大越好，因为同时打开的表过多会影响操作系统的性能。
    query_cache_size：表示查询缓冲区的大小。使用查询缓存区可以提高查询的速度。这个方式只使用与修改操作少且经常执行相同的查询操作的情况；默认值是0.
    Query_cache_type：表示查询缓存区的开启状态。0表示关闭，1表示开启。
    Max_connections：表示数据库的最大连接数。这个连接数不是越大越好，因为连接会浪费内存的资源。
    Sort_buffer_size：排序缓存区的大小，这个值越大，排序就越快。
    Innodb_buffer_pool_size：表示InnoDB类型的表和索引的最大缓存。这个值越大，查询的速度就会越快。这个值太大了就会影响操作系统的性能。
合理配置这些参数可以提高MySQL数据库的性能。配置完参数后，需要重启MySQL服务才会生效
第三部分：数据库的存储过程
一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。

        存储过程通常有以下优点：

       （1）存储过程在服务器端运行，执行速度快。

       （2）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。

       （3）确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。

        (4) 存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型：IN、OUT、INOUT。

IN 输入参数：表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数：该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数：调用时指定，并且可被改变和返回

        小结：如果仅仅想把数据传给 MySQL 存储过程，那就使用“in” 类型参数；如果仅仅从 MySQL 存储过程返回值，那就使用“out” 类型参数；如果需要把数据传给 MySQL 存储过程，还要经过一些计算后再传回给我们，此时，要使用“inout” 类型参数。

第四部分：SQL语句的执行过程
http://www.cnblogs.com/kerrycode/p/3356845.html


SQL语句的执行过程一般如下：

解析（PARSE）—— 绑定（BIND）——执行（EXECUTE）——提取（FETCH 只有SELECT才需要这步）
解析



服务器进程接收到一个SQL语句时，首先要将其转换成执行这个SQL语句的最有效步骤，这些步骤被称为执行计划。

Step 1：检查共享池中是否有之前解析相同的SQL语句后所存储的SQL文本、解析树和执行计划。如果能从共享池的缓存库中找到之前解析过生成的执行计划，则SQL语句则不需要再次解析，便可以直接由库缓存得到之前所产生的执行计划，从而直接跳到绑定或执行阶段，这种解析称作软解析。

但是如果在共享池的库缓存中找不到对应的执行计划，则必须继续解析SQL、生成执行计划，这种解析称作硬解析
Step 2：语法分析，分析SQL语句的语法是否符合规范，衡量语句中各表达式的意义

Step 3：检查是否存在语义错误和权限。语义分析，检查语句中设计的所有数据库对象是否存在，且用户有相应的权限。

Step 4：视图转换和表达式转换 将涉及视图的查询语句转换为相应的对基表查询语句。将复杂表达式转化较为简单的等效连接表达式。

Step 5：决定最佳执行计划。优化器会生成多个执行计划，在按统计信息带入，找出执行成本最小的执行计划，作为执行此SQL语句的执行计划

Step 6：将SQL文本、解析树、执行计划缓存到库缓存，存放地址以及SQL语句的哈希值。

绑定

如果SQL语句中使用了绑定变量，扫描绑定变量的声明，给绑定变量赋值。则此时将变量值带入执行计划。

执行



此阶段按照执行计划执行SQL，产生执行结果。不同类型的SQL语句，执行过程也不同。

SELECT查询

检查所需的数据块是否已经在缓冲区缓存中，如果已经在缓冲区缓存中，直接读取器内容即可。这种读取方式称为逻辑读取。如果所需数据不在缓冲区缓存中，则服务器进程需要先扫描数据块，读取相应数据块到缓冲区缓存，这种读取方式称为物理读。和逻辑读相比较，它更加耗费CPU和IO资源。

修改操作（INSERT、UPDATE、DELETE）

Step 1：检查所需的数据库是否已经被读取到缓冲区缓存中。如果已经存在缓冲区缓存，则执行Step 3

Step 2：若所需的数据库并不在缓冲区缓存中，则服务器将数据块从数据文件读取到缓冲区缓存中

Step 3：对想要修改的表取得的数据行锁定（Row Exclusive Lock），之后对所需要修改的数据行取得独占锁

Step 4：将撤销数据的Redo记录复制到日志缓冲区，产生数据行的撤销数据，将数据行修改的Redo记录复制到日志缓冲区，修改数据行。

Step 5: 产生数据修改的撤销数据

Step 6：复制数据修改的Redo记录到日志缓冲区

Step 7：修改数据行的内容，如果之前的缓冲为干净缓冲，则此时将变为脏缓冲。

提取

提取只有SELECT查询语句才有的步骤。获取查询的记录行，必要的时候对查询结果排序。

第五部分：两种索引方式（B+tree和hashcode）
http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

第六部分：数据库的事物
http://blog.csdn.net/leshami/article/details/5717020