MySql中常用俩种引擎

MyISAM

(Indexed Sequential Access Method有索引的顺序访问方法)

• 不支持事务，但是每次查询都是原子的
• 支持表级锁，即每次操作是对整个表加锁；
• 存储表的总行数；
• 一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
• 采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本 一致，但是辅索引不用保证唯一性。

InnoDB

• 支持ACID的事务，支持事务的四种隔离级别；
• 支持行级锁及外键约束，因此可以支持写并发；
• 不存储总行数；
• 一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个 表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统 文件大小限制，一般为2G），受操作系统文件大小的限制；
• 主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

MyISAM与 InnoDB区别

• count运算上的区别：因为MylSAM缓存有表meta-data(行数等)，因此在做COUNT(*)时对于一个结构很好的查询是不需要消耗多少资源的。而对于InnoDB来说，则没有这种缓存。
• 是否支持事物和崩溃后的安全恢复：MyISAM强调的是性能，每次查询具有原子性，其执行速度比InnoDB类型更快，但是不提供事物支持。但是InnoDB提供事物支持，外键等高级数据库功能。具有事物(commit)、回滚(rollback)、和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
• 是否支持外键： MyISAM不支持，而InnoDB支持。

MyISAM更适合读密集的表，而InnoDB更适合写密集的的表。 在数据库做主从分离的情况下，经常选择MyISAM作 为主库的存储引擎。 一般来说，如果需要事务支持，并且有较高的并发读取频率(MyISAM的表锁的粒度太大，所以 当该表写并发量较高时，要等待的查询就会很多了)，InnoDB是不错的选择。如果你的数据量很大（MyISAM支持压 缩特性可以减少磁盘的空间占用），而且不需要支持事务时，MyISAM是好的选择。

MySQL的基本存储结构

页的组成部分

• MySQL的基本存储结构是页（记录都存在页里），页的基本结构如下图：

一个页面的存储由以下几部分组成：

页中的存储

当我们在存储数据的时候，记录会存储到User Records部分 。但是在一个页新形成的时候是不存在User Records 这个部分的，每当我们在插入一条记录的时候，都会从Free Space中去申请一块大小符合该记录大小的空间并划分到User Records，当Free Space的部分空间全部被User Records部分替换掉之后，就意味着当前页使用完毕，如果还有新的记录插入，需要再去申请新的页，过程如下：

• 每个数据页可以组成一个双向列表；
• 每个数据页中的记录又可以生成一个单向列表：
  • 每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录使用二分法快速定位到对应的槽，然后再遍历该槽中对应分组中的记录即可快速找到指定的记录。
  • 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录
    所以说，如果我们写select * from user where indexname = 'xxx’这样没有进行任何优化的sql语句，默认会这样做：
    1.定位到记录所在的页:需要遍历双向链表，找到所在的页
    2.从所在的页内中查找相应的记录:由于不是根据主键查询，只能遍历所在页的单链表了
    很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。

使用索引之后，索引做了些什么可以让我们查询加快速度呢？其实就是将无序的数据变成有序(相对)：

要找到id为8的记录简要步骤：

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过 “目录” 就可以很快地定位到对应的页 上了！（二分查找，时间复杂度近似为O(logn)）

其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

数据库四大特性

原子性（Atomicity）

原子性是指事物包含的所有操作要么全部成功，要么全部失败回滚。因此事物的操作如果成功就必须完全应用到数据库，如果操作失败则不能对数据库有任何影响。

一致性（Consistency）

一致性是指事物必须使数据库从一个一致状态变换到另一个一致性状态，也就是说一个事物执行之前和执行之后都必须处于一致性状态。

隔离性（Isolation）

多个事物并发操作时，多个并发事物之间要相互隔离。

持久性（Durability）

持久性是指一个事物一旦提交了，那么对数据库中的数据的改变就是永久性的。

数据库的四种隔离级别

读取未提交（Read Uncommitted）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。这是万万不可出现的。

读已提交（Read Committed）

这是大多数数据库系统的默认隔离级别（但不是MySql默认的）。它满足了隔离的简单定义：一个事物只能看见已经提交的事务所做的改变。但是该隔离级别可能会产生不可重复读的现象，即同一事务的其他实例在该实例处理期间可能会有新的commit对数据更新，所以同一select可能返回不同的结果。

采用MVCC多版本并发控制解决不可重复读的问题。

可重读（Repeatable Read）

这是MySql的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。但这会导致里另一个问题：幻读。幻读是指用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影”行。

可串行化（Serializable）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加共享锁，在这个级别，可能导致大量的超时现象和锁竞争。

最左前缀原则

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是 (name,city)o而左前原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以 被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引 
select * from user where name=xx ; // 可以命中索引 
select * from user where city=xx; // 无法命中索引 

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx ，那么现在 的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的.

由于左前原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。 ORDERBY子句也遵循此规则。

注意避免冗余索引：冗余索引指的是索引的功能相同，能够命中 就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）这两 个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而 不是创建新索引。
MySQLS.7 版本后，可以通过查询 sys 库的 schemal_r dundant_indexes 表来查看冗余索引

MySql如何为表字段添加索引

1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

索引

聚集索引

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一 个聚集索引。 如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

辅助索引

辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

hash索引

哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

hash索引的示意图：

hash索引的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
• 哈希索引只支持等值比较查询，包括=、IN()、<>（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如WHERE price>100。
• 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

B-树

在说B+树之前我们先介绍一下B树与B+树的数据结构。

B-树定义：

一棵m阶的B-Tree有如下特性：

1. 每个节点最多有m个孩子。
2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
3. 若根节点不是叶子节点，则至少有2个孩子
4. 所有叶子节点都在同一层，且不包含其它关键字信息
5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
7. ki(i=1,…n)为关键字，且关键字升序排序。
8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)。

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6. 在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

数据库系统的设计者巧妙利用了磁盘预读原理， 将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧： 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里， 加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B+树

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

1. 非叶子节点只存储键值信息。
2. 所有叶子节点之间都有一个链指针。
3. 数据记录都存放在叶子节点中。

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^{3）。也就是说一个深度为3的B+Tree索引可以维护10}3 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2_{4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1}3次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据

局部性原理与磁盘预读

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速 度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目 的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的**局部性原理：**当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间 所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统 往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系 统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

常见大表的优化手段

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

限定数据的范围

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时 候，我们可以控制在一个月的范围内。

读写分离

经典的数据库拆分方案，主库负责写，从库负责读。

垂直分区

• 根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信 息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。简单来说垂直拆分是指数据表列的拆分， 把一张列比较多的表拆分为多张表。

• 垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简 化表的结构，易于维护。垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过 在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；

水平分区

• 保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达 到了分布式的目的。 水平拆分可以支撑非常大的数据量。 水平拆分是指数据表行的拆分，表的行数超过200万 行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多 个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。
• 水平拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的 数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。水平拆分能 够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨界点Join性能较差，逻辑复杂。 《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂 度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择 客户端分片架构，这样可以减少一次和中间件的网络I/O。

数据库分片的两种常见方案

• 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 ShardingJDBC 、阿里的TDDL是两种比较常用的实现。

• 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

Sql注入和防注入

Sql注入

简介

SQL注入是比较常见的网络攻击方式之一，它不是利用操作系统的BUG来实现攻击，而是针对程序员编程时的疏忽，通过SQL语句，实现无帐号登录，甚至篡改数据库。

SQL注入攻击的总体思路

1.寻找到SQL注入的位置

2.判断服务器类型和后台数据库类型

3.针对不通的服务器和数据库特点进行SQL注入攻击

SQL注入攻击实例

比如在一个登录界面，要求输入用户名和密码：

可以这样输入实现免帐号登录：

用户名： ‘or 1 = 1 –

密 码：

点登陆,如若没有做特殊处理,那么这个非法用户就很得意的登陆进去了.(当然现在的有些语言的数据库API已经处理了这些问题)这是为什么呢? 下面我们分析一下：从理论上说，后台认证程序中会有如下的SQL语句：

String sql = "select * from user_table where username=' "+userName+" ' and password=' "+password+" '";
//当输入了上面的用户名和密码，上面的SQL语句变成：
SELECT * FROM user_table WHERE username='’or 1 = 1 -- and password='’

分析SQL语句：

条件后面username=”or 1=1 用户名等于 ” 或1=1 那么这个条件一定会成功；然后后面加两个-，这意味着注释，它将后面的语句注释，让他们不起作用，这样语句永远都能正确执行，用户轻易骗过系统，获取合法身份。这还是比较温柔的，如果是执行SELECT * FROM user_table WHEREusername='' ;DROP DATABASE (DB Name) --' and password='' ….其后果可想而知…

防注入方法

预编译语句集（PreparedStatement）

采用预编译语句集，它内置了处理SQL注入的能力，只要使用它的setXXX方法传值即可。

使用好处：

(1).代码的可读性和可维护性.

(2).PreparedStatement尽最大可能提高性能.

(3).最重要的一点是极大地提高了安全性.原理：

PreparedStatement preparedStatement =connection.prepareStatement(preSql);
preparedStatement.setString(1, username);
preparedStatement.setString(2, pwd);

原理：

sql注入只对sql语句的准备(编译)过程有破坏作用

而PreparedStatement已经准备好了,执行阶段只是把输入串作为数据处理,

而不再对sql语句进行解析,准备,因此也就避免了sql注入问题.

使用正则表达式过滤传入的参数

下面是具体的正则表达式：

检测SQL meta-characters的正则表达式 ：

/(%27)|(\’)|(--)|(%23)|(#)/ix

修正检测SQL meta-characters的正则表达式 ：/((%3D)|(=))[^\n]*((%27)|(\’)|(--)|(%3B)|(😃)/i

典型的SQL 注入攻击的正则表达式 ：/\w*((%27)|(\’))((%6F)|o|(%4F))((%72)|r|(%52))/ix

检测SQL注入，UNION查询关键字的正则表达式 ：/((%27)|(\’))union/ix(%27)|(\’)

检测MS SQL Server SQL注入攻击的正则表达式：

/exec(\s|+)+(s|x)p\w+/ix

字符串过滤

过滤掉一些敏感特殊字符关键字

String inj_str = "'|and|exec|insert|select|delete|update|
count|*|%|chr|mid|master|truncate|char|declare|;|or|-|+|,";
String inj_stra[] = split(inj_str,"|");