mysql数据库相关

MySQL存储引擎－－MyISAM与InnoDB区别

MyISAM 和InnoDB 讲解

　　InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持以及外部键等高级数据库功能。

　　以下是一些细节和具体实现的差别：

　　◆1.InnoDB不支持FULLTEXT类型的索引。

　　◆2.InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含 where条件时，两种表的操作是一样的。

　　◆3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。

　　◆4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。

　　◆5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用。

　　另外，InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时mysql不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”

　　两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁。而MyISAM不支持.所以MyISAM往往就容易被人认为只适合在小项目中使用。

　　作为使用MySQL的用户角度出发，Innodb和MyISAM都是比较喜欢的，如果数据库平台要达到需求：99.9%的稳定性，方便的扩展性和高可用性来说的话，MyISAM绝对是首选。

　　原因如下：

　　1、平台上承载的大部分项目是读多写少的项目，而MyISAM的读性能是比Innodb强不少的。

　　2、MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

　　3、经常隔1，2个月就会发生应用开发人员不小心update一个表where写的范围不对，导致这个表没法正常用了，这个时候MyISAM的优越性就体现出来了，随便从当天拷贝的压缩包取出对应表的文件，随便放到一个数据库目录下，然后dump成sql再导回到主库，并把对应的binlog补上。如果是Innodb，恐怕不可能有这么快速度，别和我说让Innodb定期用导出xxx.sql机制备份，因为最小的一个数据库实例的数据量基本都是几十G大小。

　　4、从接触的应用逻辑来说，select count(*) 和order by 是最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是where对它主键是有效，非主键的都会锁全表的。

　　5、还有就是经常有很多应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的frm.MYD,MYI的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

　　6、如果和MyISAM比insert写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，虽然MyISAM可能会逊色Innodb,但是那么高并发的写，从库能否追的上也是一个问题，还不如通过多实例分库分表架构来解决。

　　7、如果是用MyISAM的话，merge引擎可以大大加快应用部门的开发速度，他们只要对这个merge表做一些select count(*)操作，非常适合大项目总量约几亿的rows某一类型(如日志，调查统计)的业务表。

　　当然Innodb也不是绝对不用，用事务的项目就用Innodb的。另外，可能有人会说你MyISAM无法抗太多写操作，但是可以通过架构来弥补。

blob和text区别

text分为4种类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT，分别对应不同的长度。text是非二进制字符串，并且需要指定字符集，并按照该字符集进行校验和排序。只能存储纯文本，可以看作是VARCHAR在长度不足时的扩展。

blob也分为4种类型：TINYBLOB,BLOB,mediumblob和LongBlob，分别对应不同的长度，blob存储的是二进制数据，因此无需字符集校验，blob除了存储文本信息外，由于二进制存储格式，所以还可以保存图片等信息，blob可以看作是VARBINARY在长度不足时的扩展。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

BLOB 列被视为二进制字符串(字节字符串)。TEXT列被视为非二进制字符串(字符字符串)。BLOB列没有字符集，并且排序和比较基于列值字节的数值值。TEXT列有一个字符集，并且根据字符集的 校对规则对值进行排序和比较。

在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。

当未运行在严格模式时，如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值，值被截取以保证适合。如果截掉的字符不是空格，将会产生一条警告。使用严格SQL模式，会产生错误，并且值将被拒绝而不是截取并给出警告。

在大多数方面，可以将BLOB列视为能够足够大的VARBINARY列。同样，可以将TEXT列视为VARCHAR列。BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHAR：

·         当保存或检索BLOB和TEXT列的值时不删除尾部空格。(这与VARBINARY和VARCHAR列相同）。

请注意比较时将用空格对TEXT进行扩充以适合比较的对象，正如CHAR和VARCHAR。

·         对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。

·         BLOB和TEXT列不能有 默认值。

LONG和LONG VARCHAR对应MEDIUMTEXT数据类型。这是为了保证兼容性。如果TEXT列类型使用BINARY属性，将为列分配列字符集的二元 校对规则。

MySQL连接程序/ODBC将BLOB值定义为LONGVARBINARY，将TEXT值定义为LONGVARCHAR。

由于BLOB和TEXT值可能会非常长，使用它们时可能遇到一些约束：

·        当排序时只使用该列的前max_sort_length个字节。max_sort_length的 默认值是1024；该值可以在启动d服务器时使用--max_sort_length选项进行更改。

运行时增加max_sort_length的值可以在排序或组合时使更多的字节有意义。任何客户端可以更改其会话max_sort_length变量的值：

mysql> SET max_sort_length = 2000;

mysql> SELECT id, comment FROM tbl_name

    -> ORDER BY comment;

当你想要使超过max_sort_length的字节有意义，对含长值的BLOB或TEXT列使用GROUP BY或ORDER BY的另一种方式是将列值转换为固定长度的对象。标准方法是使用SUBSTRING函数。例如，下面的语句对comment列的2000个字节进行排序：

mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name

    -> ORDER BY SUBSTRING(comment,1,2000);

·         BLOB或TEXT对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序。例如，可以使用mysql和mysqldump来更改客户端的max_allowed_packet值。

每个BLOB或TEXT值分别由内部分配的对象表示。这与其它列类型形成对比，后者是当打开表时为每1列分配存储引擎。

数据库中char与varchar类型的区别

在建立数据库表结构的时候，为了给一个String类型的数据定义一个数据库的数据库类型，一般参考的都是char或者varchar，这两种选择有时候让人很纠结，今天想总结一下它们两者的区别，明确一下选择塔门的理由。

      首先明确的是，char的长度是不可变的，而varchar的长度是可变的，也就是说，定义一个char[10]和varchar[10],如果存进去的是‘csdn’,那么char所占的长度依然为10，除了字符‘csdn’外，后面跟六个空格，而varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的，尽管如此，char的存取数度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。再者，char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。

浮点数与定点数

在 m y s q l 中 f l o a t 、 d o u b l e （ 或 r e a l ） 是 浮 点 数 ， d e c i m a l （ 或 n u m b e r i c ） 是 定 点 数 。

浮 点 数 相 对 于 定 点 数 的 优 点 是 在 长 度 一 定 的 情 况 下 ，浮 点 数 能 够 表 示 更 大 的 数 据 范 围 ；

它 的 缺 点 是 会 引 起 精 度 问 题 。

在 今 后 关 于 浮 点 数 和 定 点 数 的 应 用 中 ， 大 家 要 记 住 以 下 几 点 ：

1 、 浮 点 数 存 在 误 差 问 题 ；

2 、 对 货 币 等 对 精 度 敏 感 的 数 据 ， 应 该 用 定 点 数 表 示 或 存 储 ；

3 、 编 程 中 ， 如 果 用 到 浮 点 数 ， 要 特 别 注 意 误 差 问 题 ， 并 尽 量 避 免 做 浮 点 数 比 较 ；

4 、 要 注 意 浮 点 数 中 一 些 特 殊 值 的 处 理 。

索引的设计和使用

1、索引概述

（1）可以定义每个表的最大索引数（至少16个）和最大索引长度（总索引长度只是256字节）

（2）MyISAM和InnoDB存储引擎的表默认创建的都是btree索引。

（3）MySQL目前不支持函数索引

（4）支持前缀索引（对索引字段的前N非字符创建索引）。MyISAM索引的前缀长度可以达到1000字节长，InnoDB索引的长度可达767字节。

（5）支持全文本（FULLTEXT）索引，可以用于全文搜索。5.0版只有MyISAM支持，并且只限于CHAR、VARCHAR和TEXT列。索引总是对整个列进行的，不支持局部（前缀）索引。

（6）只有MyISAM支持空间类型索引，且索引的字段必须是非空的。

（7）默认情况，MEMORY存储引擎使用HASH索引，但也支持btree索引。

2、设计索引的原则

（1）最适合索引的列是出现在where子句中的列，或连接子句中指定的列，而不是出现在select关键字后的选择列表中的列。

（2）使用唯一索引。索引的列的基数越大，索引的效果越好。

（3）使用短索引。若对字符串进行索引，应该指定一个前缀长度。

（4）利用最左前缀

（5）不要过度索引

（6）InnoDB的表默认按主键顺序保存。没有主键有唯一索引那就按唯一索引的顺序保存。既没主键又没唯一索引，表中自动生成一个内部列，按这个列的顺序保存。按主键或内部列的访问是最快的，所以InnoDB的表尽量自己指定主键。而且InnoDB表的普通索引都会保持主键的键值，所以主键要尽可能选择较短的数据类型。

3、btree索引和hash索引

索引是帮助MySQL获取数据的数据结构。最常见的索引是Btree索引和Hash索引。

不同的引擎对于索引有不同的支持：Innodb和MyISAM默认的索引是Btree索引；而Mermory默认的索引是Hash索引。

Hash索引

所谓Hash索引，当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引可以一次定位，其效率很高，而Btree索引需要经过多次的磁盘IO，但是innodb和myisam之所以没有采用它，是因为它存在着好多缺点：

1、因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询

1、每次都要全表扫描

2、由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作

3、不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。

4、当哈希值大量重复且数据量非常大时，其检索效率并没有Btree索引高的。

Btree索引

至于Btree索引，它是以B+树为存储结构实现的。

但是Btree索引的存储结构在Innodb和MyISAM中有很大区别。

在MyISAM中，我们如果要对某张表的某列建立Btree索引的话，如图：

所以我们经常会说MyISAM中数据文件和索引文件是分开的。

因此MyISAM的索引方式也称为非聚集，Innodb的索引方式成为聚集索引。

至于辅助索引，类似于主索引，唯一区别就是主索引上的值不能重复，而辅助索引可以重复。

因此当我们根据Btree索引去搜索的时候，若key存在，在data域找到其地址，然后根据地址去表中查找数据记录。

至于Innodb它跟上面又有很大不同，它的叶子节点存储的并不是表的地址，而是数据

我们可以看到这里并没有将地址放入叶子节点，而是直接放入了对应的数据，这也就是我们平常说到的，Innodb的索引文件就是数据文件，

那么对于Innodb的辅助索引结构跟主索引也相差很多，如图：

我们可以发现，这里叶子节点存储的是主键的信息，所以我们在利用辅助索引的时候，检索到主键信息，然后再通过主键去主索引中定位表中的数据，这就可以说明Innodb中主键之所以不宜用过长的字段，由于所有的辅助索引都包含主索引，所以很容易让辅助索引变得庞大。

我们还可以发现：在Innodb中尽量使用自增的主键，这样每次增加数据时只需要在后面添加即可，非单调的主键在插入时会需要维持B+tree特性而进行分裂调整，十分低效。

Btree索引中的最左匹配原则：

Btree是按照从左到右的顺序来建立搜索树的。比如索引是(name,age,sex)，会先检查name字段，如果name字段相同再去检查后两个字段。

所以当传进来的是后两个字段的数据（age，sex），因为建立搜索树的时候是按照第一个字段建立的，所以必须根据name字段才能知道下一个字段去哪里查询。

所以传进来的是（name，sex）时，首先会根据name指定搜索方向，但是第二个字段缺失，所以将name字段正确的都找到后，然后才会去匹配sex的数据。

建立索引的规则：

1、利用最左前缀：mysql会一直向右查找直到遇到范围操作（>，<，like、between）就停止匹配。比如a=1 and b=2 and c>3 and d=6；此时如果建立了（a,b,c,d）索引，那么后面的d索引是完全没有用到，当换成了（a,b,d,c）就可以用到。

2、不能过度索引：在修改表内容的时候，索引必须更新或者重构，所以索引过多时，会消耗更多的时间。

3、尽量扩展索引而不要新建索引

4、最适合的索引的列是出现在where子句中的列或连接子句中指定的列。

5、不同值较少的列不必要建立索引（性别）。

SQL 注入处理办法

对于上面提到的SQL 注入隐患，后果可想而知是很严重的，轻则获得数据信息，重则可以将数据进行非法更改。那么对这种情况有没有防范措施呢？

1、PrepareStatement+Bind-variable

对Java、JSP 开发的应用,可以使用PrepareStatement+Bind-variable 来防止SQL 注入，另
外从PHP 5 开始，也在扩展的MySQLI 中支持PrepareStatement，所以在使用这类语言作数
据库开发时，强烈建议使用PrepareStatement+Bind-variable 来实现，而尽量不要使用拼接的
SQL，下面以Java 为例说明一下实现方法：
…
String sql = "select * from users u where u.id = ? and u.password = ?";
preparedstatement ps = connection.preparestatement(sql);
ps.setint(1,id);
ps.setstring(2,pwd);
resultset rs = ps.executequery();

 

2、使用应用程序提供的转换函数

很多应用程序接口都提供了对特殊字符进行转换的函数，恰当地使用这些函数，可以
防止应用程序用户输入使应用程序生成不期望的语句。
MySQL C API：使用mysql_real_escape_string() API 调用。
mysql++：使用escape 和quote 修饰符。
php：使用mysql_real_escape_string()函数（适用于PHP 4.3.0 版本）。从PHP 5 开始,
可以使用扩展的MySQLI，这是对MySQL 新特性的一个扩展支持，其中的一个优点就是支持
PrepareStatement。
Perl DBI：使用placeholders 或者quote()方法。
Ruby DBI：使用placeholders 或者quote()方法。

3、自己定义函数进行校验

如果现有的转换函数仍然不能满足要求，则需要自己编写函数进行输入校验。输入验
证是一个很复杂的问题。输入验证的途径可以分为以下几种：
整理数据使之变得有效；
拒绝已知的非法输入；
只接受已知的合法输入。
所以如果想要获得最好的安全状态，目前最好的解决办法就是对用户提交或者可能改
变的数据进行简单分类，分别应用正则表达式来对用户提供的输入数据进行严格的检测和验
证。