在MySQL中,索引是在存储引擎层实现的,不同存储引擎对索引的实现方式是不同的,下面我们探讨一下MyISAM和InnoDB两个存储引擎的索引实现方式。
MyISAM索引实现:(非聚簇索引)
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址,MyISAM索引的原理图如下。这里假设表一共有三列,假设我们以Col1为主键,则上图是一个MyISAM表的主索引(Primary key)示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求key是唯一的,而辅助索引的key可以重复。
如果我们在Col2上建立一个辅助索引,则此索引的结构如下图所示。同样也是一颗B+Tree,data域保存数据记录的地址。因此,MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址,读取相应数据记录。
InnoDB索引实现:(聚簇索引)
虽然InnoDB也使用B+Tree作为索引结构,但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。而在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。
下图是InnoDB主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。
第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说,InnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引。这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助,例如知道了InnoDB的索引实现后,就很容易明白为什么不建议使用过长的字段作为主键,因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。再例如,用非单调的字段作为主键在InnoDB中不是个好主意,因为InnoDB数据文件本身是一颗B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。
聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正是因为如此,所以一个表最多只能有一个聚簇索引,而非聚集索引一个表可以存在多个。聚集索引存储记录是物理上连续存在,物理存储按照索引排序,而非聚集索引是逻辑上的连续,物理存储并不连续,物理存储不按照索引排序。
Hash索引会将计算出的Hash值和对应的行指针信息记录在Hash表中。
检索效率非常高
不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引。
弊端和限制
不能使用范围查询
Hash 索引仅仅能满足"=",“IN"和”<=>"查询,不能使用范围查询。
排序操作
Hash 索引无法被用来避免数据的排序操作。
组合索引
Hash 索引不能利用部分索引键(组合索引)查询。
不能避免表扫描哈希冲突
Hash 索引在任何时候都不能避免表扫描(哈希冲突需要和数据记录比较)。
哈希冲突
Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
数据库事务 :数据库中一组原子性的SQL操作,要么完全地执行,要么完全地不执行。
事务 ACID 特性:
MySQL 4 种隔离级别
并发情况下,读操作可能存在的三类问题:
InnoDB支持四种隔离级别,每种级别解决掉的问题如下表:
脏读 | 不可重复读幻读 | 幻读 | |
---|---|---|---|
READ UNCOMMITTED | Y | Y | Y |
READ COMMITTED | N | Y | Y |
REPEATABLE READ(默认) | N | N | N |
SERIALIZABLE | N | N | N |
这四种隔离级别的实现机制如下:
锁策略:锁开销与数据安全性之间的平衡
行级锁
意向锁
如果对一个结点加意向锁,则说明该结点的下层结点正在被加锁;对任一结点加锁时,必须先对它的上层结点加意向锁。意向锁是放置在资源层次结构的一个级别上的锁,以保护较低级别资源上的共享或排它锁。意向锁意味着事务希望在更细粒度上进行加锁。意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁:
由于InnoDB存储引擎支持的是行级别的锁,因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下图所示。
意向共享锁和意向排它锁之间不会发生冲突。
意向锁也不会和数据行的共享锁S、排它锁X发生冲突。
对于行级锁,主要分为以下三类:
1. 行锁(Record Lock):锁定单个行记录的锁,防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
2. 间隙锁(Gap Lock):锁定索引记录间隙(不含该记录),确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。
3. 临键锁(Next-Key Lock):行锁和间隙锁组合,同时锁住数据,并锁住数据前面的间隙Gap。在RR隔离级别下支持。
InnoDB对死锁的处理:此处死锁与OS死锁类似,多个事务互相持有对方所有要申请资源的锁不释放,造成环路死锁。MySQL InnoDB引擎检测到死锁循环依赖后,回滚持有最少行级锁的事务。
InnoDB MVCC 多版本并发控制,是为了避免加锁而实现的。一般的实现方法是存储快照来实现的。InnoDB实现方式是在记录后添加两个隐藏列(表项),分别是事务创建时间、过期时间,存储的实际上是系统版本号(系统版本号随着事务的创建而递增)。 这样一来,INSERT 时加上开始版本号,UPDATE/DELETE时加上过期版本号,这样一来在SELETE时,就只访问开始系统版本号小于当前的事务的版本号、过期时间要么未定义要么在当前版本号之后的记录,这样就可以保证:访问的记录是在本事务开始前就存在而且在本事务期间没有过期(被删除或被修改过的)。可以避免脏读、不可重复读、幻读的问题。(个人觉得)
MySQL存储引擎简介
SQL注入的原理是将SQL代码伪装到输入参数中,传递到服务器解析并执行的一种攻击手法。也就是说,在一些对SERVER端发起的请求参数中植入一些SQL代码,SERVER端在执行SQL操作时,会拼接对应参数,同时也将一些SQL注入攻击的“SQL”拼接起来,导致会执行一些预期之外的操作。
举个例子:
比如我们的登录功能,其登录界面包括用户名和密码输入框以及提交按钮,登录时需要输入用户名和密码,然后提交。此时调用接口/user/login/ 加上参数username、password,首先连接数据库,然后后台对请求参数中携带的用户名、密码进行参数校验,即SQL的查询过程。假设正确的用户名和密码为ls和123456,输入正确的用户名和密码、提交,相当于调用了以下的SQL语句。
SELECT * FROM user WHERE username = 'ls' AND password = '123456'
SQL中会将#及--以后的字符串当做注释处理,如果我们使用 ' or 1=1 # 作为用户名参数,那么服务端构建的SQL语句就如下:
select * from user where username='' or 1=1 #' and password='123456'
而#会忽略后面的语句,而1=1属于常等型条件,因此这个SQL将查询出所有的登录用户。其实上面的SQL注入只是在参数层面做了些手脚,如果是引入了一些功能性的SQL那就更危险了,比如上面的登录功能,如果用户名使用这个 ' or 1=1;delete * from users; #,那么在";"之后相当于是另外一条新的SQL,这个SQL是删除全表,是非常危险的操作,因此SQL注入这种还是需要特别注意的。
如何解决SQL注入
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。