网站统计分析,网站免费虚拟主机申请,网络营销有本科吗,平台推广方案数据库三大范式是什么
第一范式#xff1a;每个列都不可以再拆分。
第二范式#xff1a;在第一范式的基础上#xff0c;非主键列完全依赖于主键#xff0c;而不能是依赖于主键的一部分。
第三范式#xff1a;在第二范式的基础上#xff0c;非主键列只依赖于主键#…数据库三大范式是什么
第一范式每个列都不可以再拆分。
第二范式在第一范式的基础上非主键列完全依赖于主键而不能是依赖于主键的一部分。
第三范式在第二范式的基础上非主键列只依赖于主键不依赖于其他非主键。
在设计数据库表结构的时候要尽量遵守三大范式如果不遵守必须有足够的理由。比如性能事实上我们经常会为了性能而妥协数据库的设计。
引擎
MySQL存储引擎MyISAM与InnoDB区别
存储引擎Storage engineMySQL中的数据、索引以及其他对象是如何存储的是一套文件系统的实现。
常用的存储引擎有以下
Innodb引擎Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。MyISAM引擎(原本MySQL的默认引擎)不提供事务的支持也不支持行级锁和外键。MEMORY引擎所有的数据都在内存中数据的处理速度快但是安全性不高。
MyISAM与InnoDB区别
InnodbMyISAM存储结构每张表都保存在同一个数据文件中每张表被存放在三个文件表定义文件、数据文件、索引文件数据和索引存储方式数据和索引是集中存储的查询时做到覆盖索引会非常高效数据和索引是分开存储的索引的叶子节点存储的是行数据地址需要再寻址一次才能得到数据记录存储顺序按主键大小有序插入按记录插入顺序保存索引聚簇索引非聚簇索引索引的实现方式B树索引Innodb 是索引组织表B树索引myisam 是堆表全文索引不支持支持哈希索引支持不支持外键支持不支持事务支持不支持锁粒度锁是避免资源争用的一个机制MySQL锁对用户几乎是透明的行级锁定、表级锁定锁定力度越小并发能力越高表级锁定SELECTMyISAM更优select count(*)myisam更快因为myisam内部维护了一个计数器可以直接调取。INSERT、UPDATE、DELETEInnoDB更优
存储引擎选择
MyISAM适用于管理非事务表它提供高速存储和检索 以及全文搜索能力的场景。比如博客系统、新闻门户网站。
InnoDB适用于更新操作频繁或者要保证数据的完整性并发量高支持事务和外键的场景。比如OA自动化办公系统。
如果没有特别的需求使用默认的Innodb即可。
索引
什么是索引
索引是一种数据结构是数据库管理系统中一个排序的数据结构以协助快速查询数据库表中数据。索引的实现通常使用B树或hash表。
更通俗的说索引就相当于目录。为了方便查找书中的内容通过对内容建立索引形成目录。
索引有哪些优缺点
索引的优点
可以大大加快数据的检索速度这也是创建索引的最主要的原因。通过使用索引可以在查询的过程中使用优化器提高系统的性能。
索引的缺点
时间方面创建索引和维护索引要耗费时间具体地当对表中的数据进行增加、删除和修改的时候索引也要动态的维护会降低增/删/改的执行效率空间方面索引需要占物理空间。
索引有哪几种类型
主键索引数据列不允许重复不允许为NULL一个表只能有一个主键。 唯一索引数据列不允许重复允许为NULL值一个表允许多个列创建唯一索引。
可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
普通索引基本的索引类型没有唯一性的限制允许为NULL值一个表允许多个列创建普通索引。
可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
全文索引是目前搜索引擎使用的一种关键技术MyISAM存储引擎才有全文索引。
可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引
索引的数据结构B树Hash
在MySQL中使用较多的索引有Hash索引B树索引等索引的数据结构和具体存储引擎的实现有关而我们经常使用的InnoDB存储引擎默认索引实现为B树索引。对于哈希索引来说底层的数据结构就是哈希表因此在绝大多数需求为单条记录等值查询的时候可以选择哈希索引查询性能最快其余大部分场景建议选择B树索引。
1B树索引
MySQL通过存储引擎存取数据基本上90%的人用的就是InnoDB了按照实现方式分InnoDB的索引类型目前只有两种BTREEB树索引和HASH索引。B树索引是MySQL数据库中使用最频繁的索引类型基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是B树索引实际是用B树实现的因为在查看表索引时mysql一律打印BTREE所以简称为B树索引
B树数据结构 众所周知一颗传统的M阶B树需要满足以下几个要求
从根节点到叶节点的所有路径都具有相同的长度所有数据信息都存储在叶子节点非叶子节点仅作为叶节点的索引存在叶子节点通过指针连在一起根节点至少拥有两个子树每个树节点最多拥有M个子树每个树节点(除了根节点)拥有至少M/2个子树
B树是为了磁盘及其他存储辅助设备而设计的一种平衡查找树(不是二叉树)在B树中所有记录的节点按大小顺序存放在同一层的叶节点中各叶子节点用指针进行连接而B树索引本质上就是B树在数据库中的实现与纯粹的B树数据结构还是有点区别。
B树的一些特性
1、B树中的B不是代表的二叉Binary 而是代表平衡Balance因为B树是从最早的平衡二叉树演化而来但是B树不是一个二叉树。
2、B树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树在B树中所有的记录节点都是按照键值大小顺序存在同一层的叶子节点由叶子节点指针进行相连。
3、B树在数据库中的特点就是高扇出因此在数据库中B树的高度一般都在24层这也就是说查找一个键值记录时最多只需要2到4次IO当前的机械硬盘每秒至少可以有100次IO24次IO意味着查询时间只需要0.02~0.04秒。
4、B树索引并不能找到一个给定键值的具体行B树索引能找到的只是被查找的键值所在行的页然后数据库把页读到内存再内存中进行查找最后找到要查找的数据。
5、数据库中B树索引可以分为聚簇索引和非聚簇索引但是不管是聚簇索引还是非聚簇索引其内部都是B树实现的即高度是平衡的叶子节点存放着所有的数据聚簇索引和非聚簇索引不同的是叶子节点是否存储的是一整行信息。每张表只能有一个聚簇索引。
6、B树的每个数据页叶子节点是通过一个双向链表进行链接数据页上的数据的顺序是按照主键顺序存储的。
2哈希索引
简要说下类似于数据结构中简单实现的HASH表散列表一样当我们在MySQL中用哈希索引时主要就是通过Hash算法常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法将数据库字段数据转换成定长的Hash值与这条数据的行指针一并存入Hash表的对应位置如果发生Hash碰撞两个不同关键字的Hash值相同则在对应Hash键下以链表形式存储。当然这只是简略模拟图。 数据库为什么使用B树而不是B树 B树的叶子节点存储了所有的数据非叶子节点中存储的是比较关键字。而B树所有的节点都会存储数据。B树的叶子节点之间存在一个指针连接B树不存在指针连接。B树这种设计结构能带来什么好处呢B树所有的数据都存储在叶子节点那么顺着叶子节点从左往右即可完成对数据的遍历极大了简化了排序操作。这也是mysql设计索引是采用B树的原因不仅仅能方便查找而且有助于排序在mysql的索引中叶子节点之间数双向链表可正反遍历更加灵活 B树只适合随机检索而B树同时支持随机检索和顺序检索 B树空间利用率更高可减少I/O次数磁盘读写代价更低。一般来说索引本身也很大不可能全部存储在内存中因此索引往往以索引文件的形式存储的磁盘上。这样的话索引查找过程中就要产生磁盘I/O消耗。B树的内部结点并没有指向关键字具体信息的指针只是作为索引使用其内部结点比B树小盘块能容纳的结点中关键字数量更多一次性读入内存中可以查找的关键字也就越多相对的IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素 B树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束越靠近根节点的记录查找时间越短只要找到关键字即可确定记录的存在其性能等价于在关键字全集内做一次二分查找。而在B树中顺序检索比较明显随机检索时任何关键字的查找都必须走一条从根节点到叶节点的路所有关键字的查找路径长度相同导致每一个关键字的查询效率相当。 B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B树的叶子节点使用指针顺序连接在一起只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的而B树不支持这样的操作。 增删文件节点时效率更高。因为B树的叶子节点包含所有关键字并以有序的链表结构存储这样可很好提高增删效率。
索引算法有哪些
索引算法有 BTree算法和Hash算法
BTree算法
BTree是最常用的mysql数据库索引算法也是mysql默认的算法。因为它不仅可以被用在,,,,和between这些比较操作符上而且还可以用于like操作符只要它的查询条件是一个不以通配符开头的常量 例如
-- 只要它的查询条件是一个不以通配符开头的常量
select * from user where name like jack%;
-- 如果一通配符开头或者没有使用常量则不会使用索引例如
select * from user where name like %jack;
Hash算法
Hash算法只能用于对等比较例如,相当于操作符。由于是一次定位数据不像BTree索引需要从根节点到枝节点最后才能访问到叶子节点这样多次IO访问所以检索效率远高于BTree索引。
创建索引的原则索引设计的原则
索引虽好但也不是无限制的使用最好符合以下几个原则 为常作为查询条件的字段建立索引where子句中的列或者连接子句中指定的列 为经常需要排序、分组操作的字段建立索引 更新频繁字段不适合创建索引 不能有效区分数据的列不适合做索引列(如性别男女未知最多也就三种区分度实在太低) 对于定义为text、image和bit的数据类型的列不要建立索引 最左前缀原则就是最左边的优先。指的是联合索引中优先走最左边列的索引。对于多个字段的联合索引如 index(a,b,c) 联合索引则相当于创建了 a 单列索引(a,b)联合索引和(a,b,c)联合索引但并不是建立了多个索引树。mysql会一直向右匹配直到遇到范围查询(、、between、like)就停止匹配比如a 1 and b 2 and c 3 and d 4 如果建立(a,b,c,d)顺序的索引d是用不到索引的如果建立(a,b,d,c)的索引则都可以用到a,b,d的顺序可以任意调整。使用短索引如果对长字符串列进行索引应该指定一个前缀长度这样能够节省大量索引空间 非空字段应该指定列为NOT NULL除非你想存储NULL。在mysql中含有空值的列很难进行查询优化因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值 不要过度索引。索引需要额外的磁盘空间并降低写操作的性能。在修改表内容的时候索引会进行更新甚至重构索引列越多这个时间就会越长
什么情况使用了索引查询还是慢
索引全表扫描索引过滤性不好频繁回表的开销
MySQL使用自增主键的好处
自增主键按顺序存放增删数据速度快对于检索非常有利数字型占用空间小易排序使用整形才可以使用AUTO_INCREAMENT不用担心主键重复问题。
什么是聚簇索引何时使用聚簇索引与非聚簇索引 聚簇索引将数据与索引放到了一块索引结构的叶子节点存储了行数据找到索引也就找到了数据非聚簇索引将数据与索引分开存储索引结构的叶子节点存储的是行数据的地址
聚簇索引的优点
数据访问更快。聚族索引将索引和数据保存在同一个B树中因此从聚族索引中获取数据通常比非聚族索引中查找更快。当你需要取出一定范围内的数据时用聚簇索引也比用非聚簇索引好。使用覆盖索引扫描的查询可以直接使用节点中的主键值。
聚簇索引的缺点
插入速度严重依赖于插入顺序按照主键的顺序插入是最快的方式否则将会出现页分裂严重影响性能。因此对于InnoDB表我们一般都会定义一个自增的ID列作为主键。更新主键的代价很高因为将会导致被更新的行移动。因此对于InnoDB表我们一般定义主键为不可更新。通过辅助索引访问需要两次索引查找第一次找到主键值第二次根据主键值找到行数据。
几个概念 对于普通索引如 name 字段则需要根据 name 字段的索引树非聚簇索引找到叶子节点对应的主键然后再通过主键去主键索引树查询一遍才可以得到要找的记录这就叫回表查询。先定位主键值再定位行记录它的性能较扫描一遍索引树的效率更低 InnoDB的行锁是建立在索引的基础之上的行锁锁的是索引不是数据所以提高并发写的能力要在查询字段添加索引 主索引和辅助索引主索引就是主键索引辅助索引就是根据业务需要自己设置的普通的非主键的索引。这个在Myisam里面区别不大但是在Innodb的时候差别很大 聚簇索引Innodb的主索引采用的是聚簇索引一个表只能有1个聚簇索引因为表数据存储的物理位置是唯一的。聚簇索引的value存的就是真实的数据不是数据的地址。主索引树里面包含了真实的数据。key是主键值value值就是datakey值按照B树的规则分散排布的叶子节点。 非聚簇索引Myisam的主索引和辅助索引都采用的是非聚簇索引索引和表数据是分离的索引的value值存储的是行数据的地址。 Innodb的索引主索引采用聚簇索引叶子节点的value值直接存储的真实的数据。辅助索引是非聚簇索引value值指向主索引的位置。所以在Innodb中根据辅助索引查询值需要遍历2次B树同时主键的长度越短越好越短辅助索引的value值就越小。Innodb中根据主键进行范围查询会特别快。 Myisam的索引主索引和辅助索引都是非聚簇索引 B树不管是什么索引在mysql中的数据结构都是B树的结构可以充分利用数据块来减少IO查询的次数提升查询的效率。一个数据块data里面存储了很多个相邻key的value值所有的非叶子节点都不存储数据都是指针。 mysql采用B树的优点IO读取次数少每次都是页读取范围查找更快捷相邻页之间有指针
联合索引是什么组合索引是什么
MySQL可以使用多个字段组合建立一个索引叫做联合索引。在联合索引中如果想要命中索引需要按照建立索引时的字段顺序挨个使用否则无法命中索引。
联合索引数据结构和实现原理使用联合索引是怎么进行查询的
假设我们对(a,b)字段建立索引那么入下图所示 如上图所示他们是按照a来进行排序在a相等的情况下才按b来排序。
因此我们可以看到a是有序的112233。而b是一种全局无序局部相对有序状态什么意思呢
从全局来看b的值为121412是无序的因此直接执行b 2这种查询条件没有办法利用索引。
从局部来看当a的值确定的时候b是有序的。例如a 1时b值为12是有序的状态。当a2时候b的值为1,4也是有序状态。因此你执行a 1 and b 2是a,b字段能用到索引的。而你执行a 1 and b 2时a字段能用到索引b字段用不到索引。因为a的值此时是一个范围不是固定的在这个范围内b值不是有序的因此b字段用不上索引。
综上所示最左匹配原则在遇到范围查询的时候就会停止匹配。
什么是最左前缀原则什么是最左匹配原则为什么需要注意联合索引中的顺序 最左前缀原则就是最左边的优先。指的是联合索引中优先走最左边列的索引。对于多个字段的联合索引如 index(a,b,c) 联合索引则相当于创建了 a 单列索引(a,b)联合索引和(a,b,c)联合索引但并不是建立了多个索引树。mysql会一直向右匹配直到遇到范围查询(、、between、like)就停止匹配比如a 1 and b 2 and c 3 and d 4 如果建立(a,b,c,d)顺序的索引d是用不到索引的如果建立(a,b,d,c)的索引则都可以用到a,b,d的顺序可以任意调整。 和in可以乱序比如a 1 and b 2 and c 3 建立(a,b,c)索引可以任意顺序mysql的查询优化器会帮你优化成索引可以识别的形式。 如果建立的索引顺序是 (a,b) 那么直接采用 where b 5 这种查询条件是无法利用到索引的这一条最能体现最左匹配的特性。
事务
什么是数据库事务
事务是逻辑上的一组操作要么都执行要么都不执行。
事务的四大特性(ACID)介绍一下?
关系性数据库需要遵循ACID规则具体内容如下
特性说明原子性 Atomic事务是最小的执行单位不允许分割。事务包含的所有操作要么全部成功要么全部失败回滚。一致性 Consistency事务执行之前和执行之后都必须处于一致性状态。举例拿转账来说假设用户A和用户B两者的钱加起来一共是5000那么不管A和B之间如何转账转几次账事务结束后两个用户的钱相加起来应该还得是5000这就是事务的一致性。隔离性 Isolation隔离性是当多个用户并发访问数据库时比如操作同一张表时数据库为每一个用户开启的事务不能被其他事务的操作所干扰多个并发事务之间是相互隔离的。数据库规定了多种事务隔离级别不同的隔离级别对应不同的干扰程度。隔离级别越高数据一致性越好但并发性越差。持久性 Durability持久性是指一个事务一旦被提交了那么对数据库中的数据的改变就是永久性的即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
什么是脏读不可重复读幻读 脏读(Dirty Read)一个事务读取到另外一个事务未提交的数据。举例一个事务1读取了被另一个事务2修改但还未提交的数据。由于某种异常事务2回滚则事务1读取的是无效数据。 不可重复读(Non-repeatable read)一个事务读取同一条记录2次得到的结果不一致。这可能是两次查询过程中间另一个事务更新了这条记录。 幻读(Phantom Read)幻读发生在两个完全相同的查询得到的结果不一致。这可能是两次查询过程中间另一个事务增加或者减少了行记录。
不可重复度和幻读区别
不可重复读的重点是修改幻读的重点在于新增或者删除。
什么是事务的隔离级别MySQL的默认的隔离级别是什么
为了达到事务的四大特性数据库定义了4种不同的事务隔离级别由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable后三个级别可以逐个解决脏读、不可重复读、幻读这几类问题。
隔离级别脏读不可重复读幻读READ-UNCOMMITTED会出现会出现会出现READ-COMMITTED不会出现会出现会出现REPEATABLE-READ不会出现不会出现会出现SERIALIZABLE不会出现不会出现不会出现
SQL 标准定义了四个隔离级别
READ-UNCOMMITTED(读未提交)最低的隔离级别一个事务可以读取另一个事务更新但未提交的数据。可能会导致脏读、不可重复读或幻读。READ-COMMITTED(读已提交)一个事务提交后才能被其他事务读取到可以阻止脏读但是不可重复读或幻读仍有可能发生。REPEATABLE-READ(可重复读)对同一记录的多次读取结果都是一致的除非数据是被本身事务所修改可以阻止脏读和不可重复读但幻读仍有可能发生。SERIALIZABLE(可串行化)最高的隔离级别完全服从ACID的隔离级别。所有的事务依次逐个执行这样事务之间就完全不可能产生干扰也就是说该级别可以防止脏读、不可重复读以及幻读。
这里需要注意的是MySQL 默认采用的 REPEATABLE_READ隔离级别Oracle 默认采用的 READ_COMMITTED隔离级别
事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC多版本并发控制通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。
因为隔离级别越低事务请求的锁越少所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取已提交)但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ可重复读并不会有任何性能损失。
InnoDB 存储引擎在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。
MySQL数据库可重复读隔离级别是怎么实现的MVCC并发版本控制原理
MySQL可重复读是通过MVCC实现的
MVCC(Multi Version Concurrency Control的简称)代表多版本并发控制。与MVCC相对的是基于锁的并发控制Lock-Based Concurrency Control)。MVCC最大的优势读不加锁读写不冲突。在读多写少的OLTP应用中读写不冲突是非常重要的极大的增加了系统的并发性能
MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列一个保存了行的创建时间一个保存行的过期时间或删除时间。当然存储的并不是实际的时间值而是系统版本号system version number)。每开始一个新的事务系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录的版本号进行比较。
InnoDB MVCC 实现原理
InnoDB 中 MVCC 的实现方式为每一行记录都有两个隐藏列DATA_TRX_ID、DATA_ROLL_PTR如果没有主键则还会多一个隐藏的主键列。
DATA_TRX_ID
记录最近更新这条行记录的事务 ID大小为 6 个字节
DATA_ROLL_PTR
表示指向该行回滚段rollback segment的指针大小为 7 个字节InnoDB 便是通过这个指针找到之前版本的数据。该行记录上所有旧版本在 undo 中都通过链表的形式组织。
DB_ROW_ID
行标识隐藏单调自增 ID大小为 6 字节如果表没有主键InnoDB 会自动生成一个隐藏主键因此会出现这个列。另外每条记录的头信息record header里都有一个专门的 bitdeleted_flag来表示当前记录是否已经被删除。
增删改查
假设初始版本号为1
INSERT
insert into user (id,name) values (1,Tom);idnamecreate_versiondelete_version1Tom1
下面模拟一下文章开头的场景
SELECT (事务A)
select * from user where id 1;
此时读到的版本号为1
UPDATE(事务B)
update user set name Jerry where id 1;
在更新操作的时候该事务的版本号在原来的基础上加1所以版本号为2。先将要更新的这条数据标记为已删除并且删除的版本号是当前事务的版本号然后插入一行新的记录
idnamecreate_versiondelete_version1Tom121Jerry2
SELECT (事务A)
此时事务A再重新读数据
select * from user where id 1;由于事务A一直没提交所以此时读到的版本号还是为1所以读到的还是Tom这条数据也就是可重复读
DELETE
delete from user where id 1;在删除操作的时候该事务的版本号在原来的基础上加1所以版本号为3删除时将当前版本号作为删除版本号
idnamecreate_versiondelete_version1Jerry22
锁
对MySQL的锁了解吗
当数据库有并发事务的时候可能会产生数据的不一致这时候需要一些机制来保证访问的次序锁机制就是这样的一个机制。
隔离级别与锁的关系
在Read Uncommitted级别下读取数据不需要加共享锁这样就不会跟被修改的数据上的排他锁冲突
在Read Committed级别下读操作需要加共享锁在语句执行完以后释放共享锁
在Repeatable Read级别下读操作需要加共享锁事务执行完毕后才释放共享锁。
在SERIALIZABLE级别下是限制性最强的隔离级别该级别下锁定整个范围的键并一直持有锁直到事务完成。
按照锁的粒度分数据库锁有哪些
在关系型数据库中可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。
MyISAM和InnoDB存储引擎使用的锁
MyISAM采用表级锁(table-level locking)。InnoDB支持行级锁(row-level locking)和表级锁默认采用行级锁
行级锁表级锁和页级锁对比
行级锁 行级锁是MySQL中锁定粒度最细的一种锁表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
特点锁定粒度最小对当前操作的行记录加锁发生锁冲突的概率最低并发度也最高加锁开销大加锁慢会出现死锁
表级锁 表级锁是MySQL中锁定粒度最大的一种锁表示对当前操作的整张表加锁它实现简单资源消耗较少被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁共享锁与表独占写锁排他锁。
特点锁定粒度大对当前操作的整张表加锁发出锁冲突的概率最高并发度最低加锁开销小加锁快不会出现死锁
页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快但冲突多行级冲突少但速度慢。所以取了折衷的页级一次锁定相邻的一组记录。
特点开销和加锁时间界于表锁和行锁之间会出现死锁锁定粒度界于表锁和行锁之间并发度一般
共享锁和排他锁的区别
共享锁
共享锁 share lock 又称读锁 read lock简称S锁是读取操作创建的锁。其他用户可以并发读取数据但任何事务都不能对数据进行修改获取数据上的排他锁直到已释放所有共享锁。
如果事务T对数据A加上共享锁后则其他事务只能对A再加共享锁不能加排他锁。获得共享锁的事务只能读数据不能修改数据
读取为什么要加读锁呢防止数据在被读取的时候被别的线程加上写锁
使用方式在需要执行的语句后面加上 for update就可以了
排他锁
排他锁 exclusive lock 又称写锁 writer lock简称X锁。排他锁是悲观锁的一种实现。
若事务T对数据A加上排他锁则只允许事务T读取和修改数据A其他任何事务都不能再对A加任何类型的锁直到事务T释放X锁。排他锁会阻塞所有的排他锁和共享锁
数据库的乐观锁和悲观锁是什么怎么实现的
数据库管理系统DBMS中的并发控制的任务是确保在多个事务同时存取同一数据时不破坏事务的隔离性和一致性以及数据库的统一性。乐观并发控制乐观锁和悲观并发控制悲观锁是并发控制主要采用的技术手段。
悲观锁假定会发生并发冲突每次去查询数据的时候都认为别人会修改每次查询完数据的时候就把事务锁起来直到提交事务。实现方式使用数据库中的锁机制
乐观锁假设不会发生并发冲突每次去查询数据的时候都认为别人不会修改所以不会上锁在修改数据的时候才把事务锁起来。实现方式乐观锁一般会使用版本号机制或CAS算法实现
两种锁的使用场景
从上面对两种锁的介绍我们知道两种锁各有优缺点不可认为一种好于另一种像乐观锁适用于写比较少的情况下多读场景即冲突真的很少发生的时候这样可以省去了锁的开销加大了系统的整个吞吐量。
但如果是多写的情况一般会经常产生冲突这就会导致上层应用会不断的进行retry这样反倒是降低了性能所以一般多写的场景下用悲观锁就比较合适。
SQL优化
如何定位及优化SQL语句的性能问题创建的索引有没有被使用到或者说怎么才可以知道这条语句运行很慢的原因
对于低性能的SQL语句的定位最重要也是最有效的方法就是使用执行计划MySQL提供了explain命令来查看语句的执行计划。我们知道不管是哪种数据库或者是哪种数据库引擎在对一条SQL语句进行执行的过程中都会做很多相关的优化对于查询语句最重要的优化方式就是使用索引。而执行计划就是显示数据库引擎对于SQL语句执行的详细情况其中包含了是否使用索引使用什么索引使用的索引的相关信息等。
执行计划包含的信息
id 由一组数字组成。表示一个查询中各个子查询的执行顺序;
id相同执行顺序由上至下。id不同id值越大优先级越高越先被执行。id为null时表示一个合并结果集的操作的执行id为null常出现在包含union等查询语句中。
select_type 每个子查询的查询类型一些常见的查询类型。
idselect_typedescription1SIMPLE不包含任何子查询或union查询2PRIMARY包含子查询时最外层查询就显示为 PRIMARY3SUBQUERY在select或where子句中出现的子查询4DERIVEDfrom字句中出现的子查询5UNIONunion连接的两个select查询第一个查询是dervied派生表除了第一个表外第二个以后的表select_type都是union。6UNION RESULT包含union的结果集在union和union all语句中因为它不需要参与查询所以id字段为null7dependent subquery与dependent union类似表示这个subquery的查询要受到外部表查询的影响。8dependent union与union一样出现在union 或union all语句中但是这个查询要受到外部查询的影响
table 显示的查询表名如果查询使用了别名那么这里显示的是别名。
type访问类型(非常重要可以看到有没有走索引)
依次从好到差systemconsteq_refreffulltextref_or_nullunique_subqueryindex_subqueryrangeindex_mergeindexALL。
除了all之外其他的type都可以使用到索引除了index_merge之外其他的type只可以用到一个索引。
类型描述system表中只有一行数据或者是空表且只能用于myisam和memory表。如果是Innodb引擎表type列在这个情况通常都是all或者index。const使用唯一索引或者主键返回记录是1行记录的等值where条件时通常type是const。其他数据库也叫做唯一索引扫描。eq_ref出现在要连接多个表的查询计划中驱动表只返回一行数据且这行数据是第二个表的主键或者唯一索引且必须为not null唯一索引和主键是多列时只有所有的列都用作比较时才会出现eq_ref。ref像eq_ref那样要求连接顺序也没有主键和唯一索引的要求只要使用相等条件检索时就可能出现常见于普通索引的等值查找。或者多列主键、唯一索引中使用第一个列之外的列作为等值查找也会出现总之返回数据不唯一的等值查找就可能出现。fulltext全文索引检索要注意全文索引的优先级很高若全文索引和普通索引同时存在时mysql不管代价优先选择使用全文索引。ref_or_null与ref方法类似只是增加了null值的比较。实际用的不多。unique_subquery用于where中的in形式子查询子查询返回不重复值唯一值。index_subquery用于in形式子查询使用到了辅助索引或者in常数列表子查询可能返回重复值可以使用索引将子查询去重。range索引范围扫描常见于使用,,is null,between ,in ,like等运算符的查询中。index_merge表示查询使用了两个以上的索引最后取交集或者并集。常见and or的条件使用了不同的索引官方排序这个在ref_or_null之后但是实际上由于要读取多个索引性能可能都不如range。index索引全表扫描。把索引从头到尾扫一遍常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。ALL全表扫描数据文件
possible_keys 可能使用的索引注意不一定会使用。查询涉及到的字段上若存在索引则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。
key 显示MySQL在查询中实际使用的索引若没有使用索引显示为NULL。
key_length 索引长度
ref 表示上述表的连接匹配条件即哪些列或常量被用于查找索引列上的值
rows 这里是执行计划中估算的扫描行数不是精确值。
extra 的信息非常丰富常见的有
Using index 使用覆盖索引Using where 使用了where子句来过滤结果集Using filesort 使用文件排序使用非索引列进行排序时出现非常消耗性能尽量优化。Using temporary 使用了临时表
SQL优化的目标可以参考阿里开发手册 【推荐】SQL性能优化的目标至少要达到 range 级别要求是ref级别如果可以是consts最好。 说明 1 consts 单表中最多只有一个匹配行主键或者唯一索引在优化阶段即可读取到数据。 2 ref 指的是使用普通的索引normal index。 3 range 对索引进行范围检索。 反例explain表的结果typeindex索引物理文件全扫描速度非常慢这个index级别比较range还低与全表扫描是小巫见大巫。 SQL的生命周期一条SQL查询语句是如何执行的MySQL总体架构—SQL执行流程—语句执行顺序
MySQL 的逻辑架构图
-- 比如你有个最简单的表表里只有一个 ID 字段在执行下面这个查询语句时
mysql select * from T where ID10;MySQL的框架有几个组件, 各是什么作用? Server层和存储引擎层各是什么作用
大体来说MySQL 可以分为 Server 层和存储引擎层两部分。
Server 层包括连接器、分析器、优化器、执行器等涵盖 MySQL 的大多数核心服务功能以及所有的内置函数如日期、时间、数学和加密函数等所有跨存储引擎的功能都在这一层实现比如存储过程、触发器、视图等。
而存储引擎层负责数据的存储和提取。其架构模式是插件式的支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB它从 MySQL 5.5.5 版本开始成为了默认存储引擎。
SQL执行流程SQL的生命周期
连接器
第一步客户端与数据库server层的连接器进行连接。连接器负责跟客户端建立连接、获取权限、维持和管理连接。
查询缓存
连接建立完成后会判断查询缓存是否开启如果已经开启会判断sql是select还是update/insert/delete对于select尝试去查询缓存如果命中缓存直接返回数据给客户端 如果缓存没有命中或者没有开启缓存 会进入到下一步分析器。
分析器
分析器进行词法分析和语法分析分析器先会做“词法分析”分析SQL中的字符串分别是什么校验数据库表和字段是否存在然后进行语法分析判断SQL是否满足MySQL语法。
优化器
优化器对sql执行计划分析得到最终执行计划得到优化后的执行计划交给执行器。
优化器是在表里面有多个索引的时候决定使用哪个索引或者在一个语句有多表关联join的时候决定各个表的连接顺序。
执行器
开始执行的时候要先判断一下你对这个表 T 有没有执行查询的权限如果没有就会返回没有权限的错误如果有权限执行器调用存储引擎api执行sql得到响应结果 将结果返回给客户端如果缓存是开启状态 会更新缓存
详细逻辑架构图 1.连接应用服务器与数据库服务器建立一个连接
2.获得请求SQL数据库进程拿到请求sql
3.查询缓存如果查询命中缓存则直接返回结果
4.语法解析和预处理
首先通过mysql关键字将语句解析会生成一个内部解析树mysql解析器将对其解析查看是否是有错误的关键字关键字顺序是否正确预处理器则是根据mysql的规则进行进一步的检查检查mysql语句是否合法如库表是否存在字段是否存在字段之间是否模棱两可等等预处理器也会验证权限。
5.查询优化器sql语句在优化器中转换成执行计划一条sql语句可以有多种方式查询最后返回的结果肯定是相同但是不同的查询方式效果不同优化器的作用就是选择一种合适的执行计划。mysql是基于成本的优化器他将预测执行此计划的成本并选择成本最小的那条
6.执行计划执行SQL在解析和优化后MySQL将生成查询对应的执行计划由执行计划调用存储引擎的API来执行查询
7.将结果返回给客户端
8.关掉连接释放资源
一条更新语句的执行流程又是怎样的呢
-- 如果要将 ID2 这一行的值加 1
mysql update T set cc1 where ID2;你执行语句前要先连接数据库这是连接器的工作。
前面我们说过在一个表上有更新的时候跟这个表有关的查询缓存会失效所以这条语句就会把表 T 上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。
接下来分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后执行器负责具体执行找到这一行然后更新。
与查询流程不一样的是更新流程还涉及两个重要的日志模块它们正是我们今天要讨论的主角redo log重做日志和 binlog归档日志。
在 MySQL 里也有这个问题如果每一次的更新操作都需要写进磁盘然后磁盘也要找到对应的那条记录然后再更新整个过程 IO 成本、查找成本都很高。为了解决这个问题MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。
而粉板和账本配合的整个过程其实就是 MySQL 里经常说到的 WAL 技术WAL 的全称是 Write-Ahead Logging它的关键点就是先写日志再写磁盘也就是先写粉板等不忙的时候再写账本。
具体来说当有一条记录需要更新的时候InnoDB 引擎就会先把记录写到 redo log粉板里面并更新内存这个时候更新就算完成了。同时InnoDB 引擎会在适当的时候将这个操作记录更新到磁盘里面而这个更新往往是在系统比较空闲的时候做这就像打烊以后掌柜做的事。
如果今天赊账的不多掌柜可以等打烊后再整理。但如果某天赊账的特别多粉板写满了又怎么办呢这个时候掌柜只好放下手中的活儿把粉板中的一部分赊账记录更新到账本中然后把这些记录从粉板上擦掉为记新账腾出空间。
write pos 是当前记录的位置一边写一边后移写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置也是往后推移并且循环的擦除记录前要把记录更新到数据文件。
write pos 和 checkpoint 之间的是“粉板”上还空着的部分可以用来记录新的操作。如果 write pos 追上 checkpoint表示“粉板”满了这时候不能再执行新的更新得停下来先擦掉一些记录把 checkpoint 推进一下。
有了 redo logInnoDB 就可以保证即使数据库发生异常重启之前提交的记录都不会丢失这个能力称为crash-safe。
要理解 crash-safe 这个概念可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上之后即使掌柜忘记了比如突然停业几天恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。
重要的日志模块binlog
前面我们讲过MySQL 整体来看其实就有两块一块是 Server 层它主要做的是 MySQL 功能层面的事情还有一块是引擎层负责存储相关的具体事宜。上面我们聊到的粉板 redo log 是 InnoDB 引擎特有的日志而 Server 层也有自己的日志称为 binlog归档日志。
我想你肯定会问为什么会有两份日志呢
因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM但是 MyISAM 没有 crash-safe 的能力binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的既然只依靠 binlog 是没有 crash-safe 能力的所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。
这两种日志有以下三点不同。
redo log 是 InnoDB 引擎特有的binlog 是 MySQL 的 Server 层实现的所有引擎都可以使用。redo log 是物理日志记录的是“在某个数据页上做了什么修改”binlog 是逻辑日志记录的是这个语句的原始逻辑比如“给 ID2 这一行的 c 字段加 1 ”。redo log 是循环写的空间固定会用完binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个并不会覆盖以前的日志。
常用SQL查询语句优化方法 不要使用select * from t用具体的字段列表代替“*”使用星号会降低查询效率如果数据库字段改变可能出现不可预知隐患。 应尽量避免在where子句中使用!或操作符避免在where子句中字段进行null值判断存储引擎将放弃使用索引而进行全表扫描。 避免使用左模糊左模糊查询将导致全表扫描。 IN语句查询时包含的值不应过多否则将导致全表扫描。 为经常作为查询条件的字段经常需要排序、分组操作的字段建立索引。 在使用联合索引字段作为条件时应遵循最左前缀原则。 OR前后两个条件都要有索引整个SQL才会使用索引只要有一个条件没索引整个SQL就不会使用索引。 尽量用union all代替unionunion需要将结果集合并后再进行唯一性过滤操作这就会涉及到排序增加大量的CPU运算加大资源消耗及延迟。
数据库优化
数据库结构优化
一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。
需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。
将字段很多的表分解成多个表
对于字段较多的表如果有些字段的使用频率很低可以将这些字段分离出来形成新表。
因为当一个表的数据量很大时会由于使用频率低的字段的存在而变慢。
增加中间表
对于需要经常联合查询的表可以建立中间表以提高查询效率。
通过建立中间表将需要通过联合查询的数据插入到中间表中然后将原来的联合查询改为对中间表的查询。
增加冗余字段
设计数据表时应尽量遵循范式理论的规约尽可能的减少冗余字段让数据库设计看起来精致、优雅。但是合理的加入冗余字段可以提高查询速度。
表的规范化程度越高表和表之间的关系越多需要连接查询的情况也就越多性能也就越差。
注意
冗余字段的值在一个表中修改了就要想办法在其他表中更新否则就会导致数据不一致的问题。
大表怎么优化某个表有近千万数据CRUD比较慢如何优化分库分表是怎么做的分表分库了有什么问题有用到中间件么他们的原理知道么
当MySQL单表记录数过大时数据库的CRUD性能会明显下降一些常见的优化措施如下 限定数据的查询范围 务必禁止不带任何限制数据范围条件的查询语句。比如当用户在查询订单历史的时候我们可以控制在一个月的范围内 读/写分离 经典的数据库拆分方案主库负责写从库负责读 缓存 使用MySQL的缓存另外对重量级、更新少的数据可以考虑使用应用级别的缓存
分库分表
分库分表主要有垂直分表和水平分表
垂直分表
垂直拆分是指数据表列的拆分把一张列比较多的表拆分为多张表。例如用户表中既有用户的登录信息又有用户的基本信息可以将用户表拆分成两个单独的表甚至放到单独的库做分库。如下图所示这样来说大家应该就更容易理解了。
适用场景如果一个表中某些列常用另外一些列不常用
垂直拆分的优点可以使得行数据变小在查询时减少读取的Block数减少I/O次数。此外垂直分区可以简化表的结构易于维护。
垂直拆分的缺点主键会出现冗余需要管理冗余列并会引起Join操作可以通过在应用层进行Join来解决。对于应用层来说逻辑算法增加开发成本。此外垂直分区会让事务变得更加复杂
水平分表
保持数据表结构不变通过某种策略进行存储数据分片。这样每一片数据分散到不同的表或者库中达到了分布式的目的。水平拆分可以支撑非常大的数据量。
水平拆分是指数据表行的拆分表的行数超过200万行时就会变慢这时可以把一张表的数据拆成多张表来存放。举个例子我们可以将用户信息表拆分成多个用户信息表这样就可以避免单一表数据量过大对性能造成影响。 水平拆分可以支持非常大的数据量。需要注意的一点是水平分表仅仅是解决了单一表数据过大的问题但由于表的数据还是在同一台机器上其实对于提升MySQL并发能力没有什么意义所以 水平拆分最好分库 。
适用场景支持非常大的数据量存储
水平拆分优点支持非常大的数据量存储
水平拆分缺点给应用增加复杂度通常查询时需要多个表名查询所有数据都需UNION操作分片事务难以解决 跨库join性能较差逻辑复杂。
《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片因为拆分会带来逻辑、部署、运维的各种复杂度 一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片尽量选择客户端分片架构这样可以减少一次和中间件的网络I/O。
下面补充一下数据库分片的两种常见方案
客户端代理 分片逻辑在应用端封装在jar包中通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。中间件代理 在应用和数据库中间加了一个代理层。分片逻辑统一维护在中间件服务中。 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。
MySQL的主从复制原理以及流程
主从复制将主数据库中的DDL和DML操作通过二进制日志BINLOG传输到从数据库上然后将这些日志重新执行从而使得从数据库的数据与主数据库保持一致。
主从复制的作用
高可用和故障切换主数据库出现问题可以切换到从数据库。
负载均衡可以进行数据库层面的读写分离。
数据备份可以在从数据库上进行日常备份。
复制过程 Binary log主数据库的二进制日志
Relay log从数据库的中继日志
第一步master在每个事务更新数据完成之前将该操作记录串行地写入到binlog文件中。
第二步salve开启一个I/O Thread该线程在master打开一个普通连接将这些事件写入到中继日志中。如果读取的进度已经跟上了master就进入睡眠状态并等待master产生新的事件。
第三步SQL Thread会读取中继日志并顺序执行该日志中的SQL事件从而与主数据库中的数据保持一致。
读写分离有哪些解决方案
读写分离是依赖于主从复制而主从复制又是为读写分离服务的。主从复制要求slave不能写只能读
方案一
利用中间件来做代理使用mysql-proxy代理负责对数据库的请求识别出读还是写并分发到不同的数据库中。
优点直接实现读写分离和负载均衡不用修改代码数据库和应用程序弱耦合master和slave用一样的帐号mysql官方不建议实际生产中使用
缺点降低性能 不支持事务代理存在性能瓶颈和可靠性风险增加。
方案二
使用AbstractRoutingDataSourceaopannotation在dao层决定数据源。
如果采用了mybatis 可以将读写分离放在ORM层比如mybatis可以通过mybatis plugin拦截sql语句所有的insert/update/delete都访问master库所有的select 都访问salve库这样对于dao层都是透明。plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。
不过这样依然有一个问题 也就是不支持事务 所以我们还需要重写一下DataSourceTransactionManager 将read-only的事务扔进读库 其余的有读有写的扔进写库。
方案三
使用AbstractRoutingDataSourceaopannotation在service层决定数据源可以支持事务
缺点类内部方法通过this.xx()方式相互调用时aop不会进行拦截需进行特殊处理
大数据量处理
大批量数据删除怎么一次删除100万条数据
方法一
在MySQL数据库使用中有的表存储数据量比较大达到每天三百万条记录左右此表中建立了三个索引这些索引都是必须的其他程序要使用。由于要求此表中的数据只保留当天的数据所以每当在凌晨的某一时刻当其他程序处理万其中的数据后要删除该表中昨天以及以前的数据使用delete删除表中的上百万条记录时MySQL删除速度非常缓慢每一万条记录需要大概4分钟左右这样删除所有无用数据要达到八个小时以上这是难以接受的。
查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的于是删除掉其中的两个索引后测试发现此时删除速度相当快一百万条记录在一分钟多一些可是这两个索引其他模块在每天一次的数据整理中还要使用于是想到了一个折中的办法
在删除数据之前删除这两个索引此时需要三分钟多一些然后删除其中无用数据此过程需要不到两分钟删除完成后重新创建索引因为此时数据库中的数据相对较少约三四十万条记录(此表中的数据每小时会增加约十万条)创建索引也非常快约十分钟左右。这样整个删除过程只需要约15分钟。对比之前的八个小时大大节省了时间。
删除大表的部分数据通常采用以下步骤
删除大表的多行数据时会超出innod block table size的限制最小化的减少锁表时间的方案是
1、选择不需要删除的数据并把它们存在一张相同结构的空表里
2、重命名原始表并给新表命名为原始表名
3、删掉原始表
方法二
分批删除如果你要删除一个表里面的前 10000 行数据有以下三种方法可以做到
第一种直接执行 delete from T limit 10000;
第二种在一个连接中循环执行 20 次 delete from T limit 500;
第三种在 20 个连接中同时执行 delete from T limit 500。
方案一单个语句占用锁的时间较长会导致其他客户端等待资源时间较长。
方案二串行化执行将相对长的事务分成多次相对短的事务则每次事务占用锁的时间相对较短其他客户端在等待相应资源的时间也较短。这样的操作同时也意味着将资源分片使用每次执行使用不同片段的资源可以提高并发性。
方案三人为制造锁竞争加剧并发。
方案二相对比较好具体还要结合实际业务场景。
MySQL大数据量分页查询方法及其优化
limit偏移量不变随着查询记录量越来越大所花费的时间也会越来越多。
limit查询记录数不变随着查询偏移的增大尤其查询偏移大于10万以后查询时间急剧增加。
原因分析
select * from user where sex 1 limit 100,10由于 sex 列是索引列MySQL会走 sex 这棵索引树命中 sex1 的数据。
然后又由于非聚簇索引中存储的是主键 id 的值且查询语句要求查询所有列所以这里会发生一个回表的情况在命中 sex 索引树中值为1的数据后拿着它叶子节点上的值也就是主键 id 的值去主键索引树上查询这一行其他列name、sex的值最后返回到结果集中这样第一行数据就查询成功了。
最后这句 SQL 要求limit 100, 10也就是查询第101到110个数据但是 MySQL 会查询前110行然后将前100行抛弃最后结果集中就只剩下了第101到110行执行结束。
小结一下在上述的执行过程中造成 limit 大偏移量执行时间变久的原因有
limit a, b会查询前ab条数据然后丢弃前a条数据
MySQL数据库的查询优化器是采用了基于代价的方式而查询代价的估算是基于CPU代价和IO代价。如果MySQL在查询代价估算中认为全表扫描方式比走索引扫描的方式效率更高的话就会放弃索引直接全表扫描
优化方式
t5表有200万数据id为主键text为普通索引
使用覆盖索引 如果一条SQL语句通过索引可以直接获取查询的结果不再需要回表查询就称这个索引为覆盖索引。
在MySQL数据库中使用explain关键字查看执行计划如果extra这一列显示Using index就表示这条SQL语句使用了覆盖索引。
让我们来对比一下使用了覆盖索引性能会提升多少吧。
没有使用覆盖索引
select * from t5 order by text limit 1000000, 10;这次查询花了3.690秒让我们看一下使用了覆盖索引优化会提升多少性能吧。
使用了覆盖索引
select id, text from t5 order by text limit 1000000, 10;从上面的对比中超大分页查询中使用了覆盖索引之后花了0.201秒而没有使用覆盖索引花了3.690秒提高了18倍多这在实际开发中就是一个大的性能优化了。
子查询优化
因为实际开发中用SELECT查询一两列操作是非常少的因此上述的覆盖索引的适用范围就比较有限。
所以我们可以通过把分页的SQL语句改写成子查询的方法获得性能上的提升。
select * from t5 where id(select id from t5 order by text limit 1000000, 1) limit 10;其实使用这种方法提升的效率和上面使用了覆盖索引基本一致。
但是这种优化方法也有局限性
这种写法要求主键ID必须是连续的
Where子句不允许再添加其他条件
延迟关联 和上述的子查询做法类似我们可以使用JOIN先在索引列上完成分页操作然后再回表获取所需要的列。
select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.idb.id;
