精华内容
下载资源
问答
  • 怎么完成信息索引
    千次阅读
    2021-02-07 16:23:29

    一、索引创建方式

    前台方式
    缺省情况下,当为一个集合创建索引时,这个操作将阻塞其他的所有操作。即该集合上的无法正常读写,直到索引创建完毕
    任意基于所有数据库申请读或写锁都将等待直到前台完成索引创建操作

    后台方式
    将索引创建置于到后台,适用于那些需要长时间创建索引的情形
    这样子在创建索引期间,MongoDB依旧可以正常的为提供读写操作服务
    等同于关系型数据库在创建索引的时候指定online,而MongoDB则是指定background
    其目的都是相同的,即在索引创建期间,尽可能的以一种占用较少的资源占用方式来实现,同时又可以提供读写服务

    后台创建方式的代价:索引创建时间变长
    后台创建索引的示例

    db.people.createIndex( { zipcode: 1}, {background: true} )
    db.people.createIndex( { city: 1}, {background: true, sparse: true } )
    

    缺省情况下background选项的值为false

    二、索引创建期间注意事项

    如前所述,基于后台创建索引时,其他的数据库操作能被完成。但是对于mongo shell会话或者你正在创建索引的这个连接将不可用,直到所有创建完毕。如果需要做一些其它的操作。则需要再建立其它的连接。
    在索引创建期间,即使完成了部分索引的创建,索引依旧不可用,但是一旦创建完成即可使用。
    基于后台创建索引期间不能完成涉及该集合的相关管理操作

    repairDatabase
    db.collection.drop()
    compact
    

    意外中断索引创建
    如果在后台创建索引期间,mongod实例异常终止,当mongod实例重新启动后,未完成的索引创建将作为前台进程来执行
    如果索引创建失败,比如由于重复的键等,mongod将提示错误并退出
    在一个索引创建失败后启动mongod,可以使用storage.indexBuildRetry or --noIndexBuildRetry跳过索引创建来启动

    三、索引创建期间性能

    后台创建索引比前台慢,如果索引大于实际可用内存,则需要更长的时间来完成索引创建
    所有涉及到该集合的相关操作在后台期间其执行效能会下降,应在合理的维护空挡期完成索引的创建

    四、索引的命名规则

    缺省情况下,索引名以键名加上其创建顺序(1或者-1)组合而成。

    db.products.createIndex( { item: 1, quantity: -1 } )
    

    比如上面的索引创建后,其索引名为item_1_quantity_-1
    可以指定自定义的索引名称

    db.products.createIndex( { item: 1, quantity: -1 } , { name: "inventory_idx" } )
    

    如上方式,我们指定了了索引名称为inventory_idx

    五、查看索引创建进度

    可使用 db.currentOp() 命令观察索引创建的完成进度

     > db.currentOp(
     {
    
     $or: [
     { op: "command", "query.createIndexes": { $exists: true } },
     { op: "insert", ns: /\.system\.indexes\b/ }
     ]
     }
     )
    

    //下面通过一个索引创建示例来查看索引完成进度

    //首选创建一个500w文档的集合

     > db.version() // Author : Leshami
    
     3.2.10 // Blog : http://blog.csdn.net/leshami
    
     > for (var i=1;i<=5000000;i++){
     db.inventory.insert({id:i,item:"item"+i,stock:Math.floor(i*Math.random())})
     }
    
     WriteResult({ "nInserted" : 1 })
    
     > db.inventory.find().limit(3)
     { "_id" : ObjectId("581bfc674b0d633653f4427e"), "id" : 1, "item" : "item1", "stock" : 0 }
     { "_id" : ObjectId("581bfc674b0d633653f4427f"), "id" : 2, "item" : "item2", "stock" : 0 }
     { "_id" : ObjectId("581bfc674b0d633653f44280"), "id" : 3, "item" : "item3", "stock" : 1 }
     
     > db.inventory.find().count()
     5000000
    

    //下面开始创建索引

     > db.inventory.createIndex({item:1,unique:true})
    

    //使用下面的命令查看索引完成进度

     > db.currentOp(
     {
     $or: [
     { op: "command", "query.createIndexes": { $exists: true } },
     { op: "insert", ns: /\.system\.indexes\b/ }
     ]
     }
     )
    

    结果如下

     {
     "inprog" : [
     {
     "desc" : "conn1", //连接描述 
     "threadId" : "139911670933248", //线程id
     "connectionId" : 1, 
     "client" : "127.0.0.1:37524", //ip及端口
     "active" : true, //活动状态
     "opid" : 5014925,
     "secs_running" : 21, //已执行的时间
     "microsecs_running" : NumberLong(21800738), 
     "op" : "command",
     "ns" : "test.$cmd",
     "query" : {
     "createIndexes" : "inventory", //这里描述了基于inventory正在创建索引
     "indexes" : [
     {
     "ns" : "test.inventory",
     "key" : {
     "item" : 1,
     "unique" : true
     },
     "name" : "item_1_unique_true"
     }
     ]
     },
     "msg" : "Index Build Index Build: 3103284/5000000 62%", //这里是完成的百分比
     "progress" : {
     "done" : 3103722,
     "total" : 5000000
     },
     "numYields" : 0,
     "locks" : { //当前持有的锁
     "Global" : "w",
     "Database" : "W",
     "Collection" : "w"
     },
     "waitingForLock" : false,
     "lockStats" : { //锁的状态信息
     "Global" : {
     "acquireCount" : {
     "r" : NumberLong(1),
     "w" : NumberLong(1)
     }
     },
     "Database" : {
     "acquireCount" : {
     "W" : NumberLong(1)
     }
     },
     "Collection" : {
     "acquireCount" : {
     "w" : NumberLong(1)
     }
     }
     }
     }
     ],
     "ok" : 1
     }
    

    基于后台方式创建索引

     > db.inventory.createIndex({item:1,unique:true},{background: true})
    

    六、终止索引的创建

    db.killOp()
    
    更多相关内容
  • mysql索引详解

    万次阅读 多人点赞 2021-07-07 21:40:09
    分别为:表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 InnoDB:所有的表都...

    🍅 作者简介:哪吒,CSDN2021博客之星亚军🏆、新星计划导师✌、博客专家💪

    🍅 哪吒多年工作总结:Java学习路线总结,搬砖工逆袭Java架构师

    🍅 关注公众号【哪吒编程】,回复1024,获取Java学习路线思维导图、大厂面试真题、加入万粉计划交流群、一起学习进步

    目录

    一、MySQL三层逻辑架构

    1、第一层负责连接管理、授权认证、安全等等。

    2、第二层负责解析查询

    3、第三层是存储引擎

    二、对比InnoDB与MyISAM

    1、 存储结构

    2、 存储空间

    3、 可移植性、备份及恢复

    4、 事务支持

    5、 AUTO_INCREMENT

    6、 表锁差异

    7、 全文索引

    8、表主键

    9、表的具体行数

    10、CRUD操作

    11、 外键

    三、sql优化简介

    1、什么情况下进行sql优化

    2、sql语句执行过程

    3、sql优化就是优化索引

    四、索引

    1、索引的优势

    2、索引的弊端

    3、索引的分类

    4、创建索引

    5、MySQL索引原理 -> B+树

    五、如何触发联合索引

    1、对user表建立联合索引username、password

    2、触发联合索引

    六、分析sql的执行计划---explain

    1、explan使用简介

    2、explain查询结果简介


    一、MySQL三层逻辑架构

    MySQL的存储引擎架构将查询处理与数据的存储/提取相分离。下面是MySQL的逻辑架构图:

    1、第一层负责连接管理、授权认证、安全等等。

    每个客户端的连接都对应着服务器上的一个线程。服务器上维护了一个线程池,避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时,服务器对其进行认证。可以通过用户名和密码的方式进行认证,也可以通过SSL证书进行认证。登录认证通过后,服务器还会验证该客户端是否有执行某个查询的权限。

    2、第二层负责解析查询

    编译SQL,并对其进行优化(如调整表的读取顺序,选择合适的索引等)。对于SELECT语句,在解析查询前,服务器会先检查查询缓存,如果能在其中找到对应的查询结果,则无需再进行查询解析、优化等过程,直接返回查询结果。存储过程、触发器、视图等都在这一层实现。

    3、第三层是存储引擎

    存储引擎负责在MySQL中存储数据、提取数据、开启一个事务等等。存储引擎通过API与上层进行通信,这些API屏蔽了不同存储引擎之间的差异,使得这些差异对上层查询过程透明。存储引擎不会去解析SQL。

    二、对比InnoDB与MyISAM

    1、 存储结构

    MyISAM:每个MyISAM在磁盘上存储成三个文件。分别为:表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

    InnoDB:所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。

    2、 存储空间

    MyISAM: MyISAM支持支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后,不会再进行修改操作,可以使用压缩表,极大的减少磁盘的空间占用。

    InnoDB: 需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

    3、 可移植性、备份及恢复

    MyISAM:数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

    InnoDB:免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。

    4、 事务支持

    MyISAM:强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持。

    InnoDB:提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

    5、 AUTO_INCREMENT

    MyISAM:可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。

    InnoDB:InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引,如果是组合索引也必须是组合索引的第一列。

    6、 表锁差异

    MyISAM: 只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。

    InnoDB: 支持事务和行级锁,是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的。

    7、 全文索引

    MyISAM:支持 FULLTEXT类型的全文索引

    InnoDB:不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。

    8、表主键

    MyISAM:允许没有任何索引和主键的表存在,索引都是保存行的地址。

    InnoDB:如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。

    9、表的具体行数

    MyISAM: 保存有表的总行数,如果select count() from table;会直接取出出该值。

    InnoDB: 没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样。

    10、CRUD操作

    MyISAM:如果执行大量的SELECT,MyISAM是更好的选择。

    InnoDB:如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。

    11、 外键

    MyISAM:不支持

    InnoDB:支持

    三、sql优化简介

    1、什么情况下进行sql优化

    性能低、执行时间太长、等待时间太长、连接查询、索引失效。

    2、sql语句执行过程

    (1)编写过程

    select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

    (2)解析过程

    from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

    3、sql优化就是优化索引

    索引相当于书的目录。

    索引的数据结构是B+树。

    四、索引

    1、索引的优势

    (1)提高查询效率(降低IO使用率)

    (2)降低CPU使用率

    比如查询order by age desc,因为B+索引树本身就是排好序的,所以再查询如果触发索引,就不用再重新查询了。

    2、索引的弊端

    (1)索引本身很大,可以存放在内存或硬盘上,通常存储在硬盘上。

    (2)索引不是所有情况都使用,比如①少量数据②频繁变化的字段③很少使用的字段

    (3)索引会降低增删改的效率

    3、索引的分类

    (1)单值索引

    (2)唯一索引

    (3)联合索引

    (4)主键索引

    备注:唯一索引和主键索引唯一的区别:主键索引不能为null

    4、创建索引

    alter table user add INDEX `user_index_username_password` (`username`,`password`)

    5、MySQL索引原理 -> B+树

    MySQL索引的底层数据结构是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有几点不同:

    非叶子节点只存储键值信息。
    所有叶子节点之间都有一个链指针。
    数据记录都存放在叶子节点中。
    将上一节中的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存储引擎会遍历辅助索引找到主键,然后再通过主键在聚集索引中找到完整的行记录数据。

    五、如何触发联合索引

    1、对user表建立联合索引username、password

    2、触发联合索引

    (1)使用联合索引的全部索引键可触发联合索引

    (2)使用联合索引的全部索引键,但是用or连接的,不可触发联合索引

    (3)单独使用联合索引的左边第一个字段时,可触发联合索引

    (4)单独使用联合索引的其它字段时,不可触发联合索引

    六、分析sql的执行计划---explain

    explain可以模拟sql优化执行sql语句。

    1、explan使用简介

    (1)用户表

    (2)部门表

    (3)未触发索引

    (4)触发索引

    (5)结果分析

    explain中第一行出现的表是驱动表。

    1. 指定了联接条件时,满足查询条件的记录行数少的表为[驱动表]
    2. 未指定联接条件时,行数少的表为[驱动表]

    对驱动表直接进行排序就会触发索引,对非驱动表进行排序不会触发索引。

    2、explain查询结果简介

    (1)id:SELECT识别符。这是SELECT的查询序列号。

    (2)select_type:SELECT类型:

    1. SIMPLE: 简单SELECT(不使用UNION或子查询)
    2. PRIMARY: 最外面的SELECT
    3. UNION:UNION中的第二个或后面的SELECT语句
    4. DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
    5. UNION RESULT:UNION的结果
    6. SUBQUERY:子查询中的第一个SELECT
    7. DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
    8. DERIVED:导出表的SELECT(FROM子句的子查询)

    (3)table:表名

    (4)type:联接类型

    1. system:表仅有一行(=系统表)。这是const联接类型的一个特例。
    2. const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
    3. eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
    4. ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
    5. ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
    6. index_merge:该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。
    7. unique_subquery:该类型替换了下面形式的IN子查询的ref:value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
    8. index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
    9. range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range
    10. index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
    11. all:对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。

    (5)possible_keys:possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

    (6)key:key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

    (7)key_len:key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

    (8)ref:ref列显示使用哪个列或常数与key一起从表中选择行。

    (9)rows:rows列显示MySQL认为它执行查询时必须检查的行数。

    (10)Extra:该列包含MySQL解决查询的详细信息。

    1. Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
    2. Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
    3. range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。对前面的表的每个行组合,MySQL检查是否可以使用range或index_merge访问方法来索取行。
    4. Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。
    5. Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时,可以使用该策略。
    6. Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
    7. Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。
    8. Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
    9. Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。并且,按最有效的方式使用索引,以便对于每个组,只读取少量索引条目。

    通过相乘EXPLAIN输出的rows列的所有值,你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时,也用这个乘积来确定执行哪个多表SELECT语句。

    🍅 作者简介:哪吒,CSDN2021博客之星亚军🏆、新星计划导师✌、博客专家💪

    🍅 哪吒多年工作总结:Java学习路线总结,搬砖工逆袭Java架构师

    🍅 关注公众号【哪吒编程】,回复1024,获取Java学习路线思维导图、大厂面试真题、加入万粉计划交流群、一起学习进步

    关注公众号,回复1024,获取Java学习路线思维导图,加入万粉计划交流群

    展开全文
  • 我秃了!唯一索引、普通索引我该选谁?

    千次阅读 多人点赞 2021-09-06 00:35:26
    你遇到过不知道如何选择唯一索引和普通索引的场景么?你知道他俩的原理和区别么?来不及了,快上车!!

    小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL江湖路 | 专栏目录
    在这里插入图片描述

      提到唯一索引普通索引,相信大家都不陌生,当同事小姐姐问你这俩有什么区别时?或许你会脱口而出:“这还用问?见名知意啊,一个是允许字段重复,一个不允许存在重复数据!”

      是否解决小姐姐的疑问我不知道,但你在同事心目中,肯定不是啥好玩意儿~ 要知道,一眼就看出的答案,一般不会有人问,除非问傻子~

    那么当你处理一张市民信息表时,其中一列为市民的身份证号信息,你会怎么选择哪个索引?为什么?

      对于一个经历过风风雨雨、日日夜夜的程序员来说,需要你考虑的东西可不仅是重不重复这类问题,而是…

    在这里插入图片描述

    开个玩笑~~应当结合实际情况,对各个场景进行综合考虑。

      其实,如果在业务代码中保证了不会写入重复的身份证号,那么这两个选择逻辑上都是正确的。但是在SELECT和DML场景中,唯一索引和普通索引却有很多不同。

    1、在SELECT中,唯一索引和普通索引的区别

      本文测试引擎选择我们最常用的InnoDB,版本为MySQL8.0;

    假设,执行查询的语句是:

    select id from T where id_card = 666;
    

      (身份证太长,咱们用简单数据做演示)我们知道,MySQL的InnoDB采用的是B+树实现的索引结构,查找过程从B+树的树根起,按层搜索到666所在的叶子节点,然后取出该节点所在的数据页,把数据页读到内存后,通过二分法在数据页中定位id_card=666的行数据。

    在这里插入图片描述

    B+ 树的查找过程如上图:

    1. 将磁盘块1从磁盘加载到内存,发生一次IO ,在内存中使用二分查找方式找到 666 在600和700 之间,锁定磁盘块1的P2 指针。
    2. 通过磁盘块1 的 P2 指针地址把磁盘块3 加载到内存,发生第二次IO ,锁定磁盘块3 的 P2 指针
    3. 通过磁盘块3 的P2指针加载磁盘块7到内存,发生第三次 IO,同时根据二分查找找到666 查询结束。

    普通索引和唯一索引的定位方式:

    • 普通索引:查到第一条id_card=666 后,然后继续往后查找直到碰到第一个 id_card<>666 的记录时,结束。
    • 唯一索引:由于索引定义了唯一性,查找到第一个满足条件的记录后,直接结束。

      两者在查询方面的性能差距微乎其微。对于普通索引多的那一次操作,因为本身就是以数据页为单位读进内存,数据页大小默认16KB(大概1000行),要多做的那一次“查找和判断下一条记录”的操作,就只需要一次指针寻找和一次计算。当然,不可避免查询的数据是该数据页的最后一位,这样还要再读下一块数据页,算法会复杂一些。

      但你知道的,这种概率很小,我们程序员要相信逆墨菲定律:大概率不会出现且未被发现的BUG,在难以改动的前提下,你就当不知道就完了,发生了又能咋地?有测试顶着呢!

    在这里插入图片描述

      有同学问我了:普通索引为什么要继续向下查找?继续向下查找的原因是由于普通索引允许重复值,且B+Tree是天然有序的。SQL中并没有指定limit 1,所以他还要往下查,看是否有同条件的数据一起返回,直到查到第一条不满足条件的数据为止。

    2、在DML中,唯一索引和普通索引的区别

      ding!这是本篇文章的重点,在看之前,我们需要先了解什么是change buffer

      了解MySQL机制的同学们知道,当执行 DML(INSERT、UPDATE、DELETE)等操作时,InnoDB会利用 change buffer进行加速写操作,可以将写操作的随机磁盘访问调整为局部顺序操作,而在机械硬盘时代,随机磁盘访问(随机I/O)也是数据库操作中的最耗性能的硬伤。当普通索引(非唯一索引)的数据页发生写操作时,把操作内容写到内存中的change buffer后就可以立刻返回(执行完成)了。

      这里我以UPDATE操作为例,当需要更新某一行数据时,会先判断该行所在数据页是否在内存中,如果在就直接在内存数据页中更新,如果这个数据页没有内存中的话,在不影响数据一致性的前提下,InnoDB 会将这些UPDATE操作缓存在 change buffer 中,这样就不需要从磁盘读入数据页,当有SQL查询需要访问这个数据页的数据时,将数据页读入内存后,然后先执行 change buffer 中与这个页的相关UPDATE操作,通过这种方式保证这个数据页的逻辑正确性

    在这里插入图片描述

      可见,change buffer是会被从内存持久化到磁盘中的,将 change buffer 中的操作应用到原数据页,得到最新结果的过程被称为 merge。除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。在数据库正常关闭(shutdown)的过程中,也会执行 merge 操作,相当于刷脏页啦(把已修改的数据更新到实际数据文件中)。

    触发merge的操作主要有以下几种(你该记住的点):

    • 有SQL线程访问这个数据页;
    • master thread线程每秒或每10秒进行一次merge change buffer的操作;
    • 在数据库正常关闭的时候。

      小朋友,你是否有很多问号??DB服务器宕机,数据不是就丢了?这就得redo log + binlog来保证了,可以参考作者另一篇文章《听我讲完redo log、binlog原理,面试官老脸一红》,本篇不再赘述。

      跑远了?言归正传~~上文提到普通索引(非唯一索引)会使用到change buffer进行加速写操作,你是不是已经get到点了呢~

      是的,唯一索引不会使用 Change buffer ,如果索引设置了唯一属性,在进行插入或者修改操作时,InnoDB 必须进行唯一性检查,如果不读取索引页到缓冲池,无法校验索引是否唯一,如果都把索引页读到内存了,那直接更新内存会更快,就没必要使用change buffer了。

    • 对于普通索引(非唯一索引)的DML操作来说,当待更新的数据页在内存中时,找到前值和后值的区间插入即可;当待更新的数据页在不在内存中时,直接把操作写到Change buffer就完事儿了。舒服!

    • 对于唯一索引,当待更新的数据页在不在内存中时,索引每次都得把数据页读到内存中判断唯一性,将数据从磁盘读入内存涉及大量随机IO的访问,慢的一批,当遇到高频写操作时??唉,别想了,难受!

      到这里,相信你对普通索引和唯一索引的取舍有了一定的概念,普通索引和唯一索引在查询能力上是没差别的,主要考虑的是更新的影响。还得结合实际业务场景来判断,如果是读取远大于更新和插入的表,唯一索引和普通索引都可以,但是如果业务需求相反,个人觉得应该使用普通索引,当然如果是那种更新完要求立即可见的需求,就是刚更新完就要再查询的,这种情况下反而不推荐普通索引,因为这样会频繁的产生merge操作,起不到change buffer的作用,反而需要额外空间来维护change buffer就有点得不偿失了。

      当我们使用普通索引,尤其在使用机械盘的场景下,尽量把change buffer开大从而确保数据的写入速度。最后,通过列举一下 change buffer 的配置,结束今天的分享,相信看到这里的都是有心人,也是喜爱MySQL的崽子,记得不要吝啬你的点赞哦~~

    在这里插入图片描述

    change buffer 配置

    • innodb_change_buffer_max_size% 配置写缓冲的大小,占整个缓冲池的比例,默认值是25%,可以通过修改该值提高InnoDB写效率,最大值是50%。
    mysql> show variables like '%innodb_change_buffer_max_size%';
    +-------------------------------+-------+
    | Variable_name                 | Value |
    +-------------------------------+-------+
    | innodb_change_buffer_max_size | 25    |
    +-------------------------------+-------+
    1 row in set (0.00 sec)
    
    • innodb_change_buffering配置是否缓存辅助索引页的修改,默认为 all,即缓存 INSERT/DELETE/UPDATE等DML操作。
    mysql> show variables like '%innodb_change_buffering%';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | innodb_change_buffering | all   |
    +-------------------------+-------+
    1 row in set (0.00 sec)
    

    MySQL系列文章汇总与《MySQL江湖路 | 专栏目录》

    往期热门MySQL系列文章

    展开全文
  • MySQL索引系列:全文索引

    千次阅读 2020-11-07 16:34:12
    什么是全文索引?...全文索引是将存储在数据库中的大段文本中的任意内容信息查找出来的技术。 既然是查找包含某些内容的文本,用 like + 通配符 或者正则表达式就可以实现模糊匹配,为什么还要全文索引

    什么是全文索引?

    全文索引首先是 MySQL 的一种索引类型,也是搜索引擎的关键技术。

    试想在1M大小的文件中搜索一个词,可能需要几秒,在100M的文件中可能需要几十秒,如果在更大的文件中搜索那么就需要更大的系统开销,这样的开销是不现实的。

    所以在这样的矛盾下出现了全文索引技术,有时候有人叫倒排文档技术。

    全文索引的作用是什么?

    全文索引是将存储在数据库中的大段文本中的任意内容信息查找出来的技术。

    既然是查找包含某些内容的文本,用 like + 通配符 或者正则表达式就可以实现模糊匹配,为什么还要全文索引?

    • 性能:通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行(而且这些搜索极少使用表索引)。因此,由于被搜索行数不断增加,这些搜索可能非常耗时。
    • 明确控制:使用通配符和正则表达式匹配,很难明确地控制匹配什么和不匹配什么。例如,指定一个词必须匹配,一个词必须不匹配;而一个词仅在第一个词确实匹配的情况下,才可以匹配或者才可以不匹配等。这些情况,使用通配符和正则表达式都不满足。
    • 智能化的结果:虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索方式,但它们都不能提供一种智能化的选择结果的方法。 例如,一个特殊词的搜索将会返回包含该词的所有行,而不区分包含单个匹配的行和包含多个匹配的行(按照可能是更好的匹配来排列它们)。类似,一个特殊词的搜索将不会找出不包含该词但 包含其他相关词的行。

    所有这些限制以及更多的限制都可以用全文本搜索来解决。在使用全文本搜索时,MySQL不需要分别查看每个行,不需要分别分析和处理每个词,可以根据需要获取全文中有关章,节,段,句,词等信息,也可以进行各种统计和分析。MySQL创建指定列中各词的一个索引,搜索可以针对这些词进行。这样,MySQL可以快速有效地决定哪些词匹配(哪些行包含它们), 哪些词不匹配,它们匹配的频率,等等。

    但是全文索引可能存在精度问题

    假如我们要搜索 胡歌很帅,拿百度来举个例子:
    在这里插入图片描述

    可以看到我明明搜索的是 ‘胡歌很帅’,但是百度搜索的关键字(标红的就是关键字)却不只是完整的 ‘胡歌很帅’,这一句话被分割为’胡歌’,‘很帅’,‘帅’,‘胡歌很’,'胡歌很帅’等,这就是全文索引里的分词机制,也是导致精度问题的原因。

    版本支持

    1. MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引,InnoDB存储引擎并不支持全文索引技术,大多数的用户转向MyISAM存储引擎,虽然可以通过表的拆分,将进行全文索引的数据存储为MyIsam表,这样方式解决逻辑业务的需求,但是却丧失了INNODB存储引擎的事务性;
    2. MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
    3. 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

    索引的创建、修改、删除

    具体操作就不重复了,请看上一篇博客:MySQL索引系列:索引概述

    使用全文索引

    首先创建表,插入测试数据

    create table test (
        id int(11) unsigned not null auto_increment,
        content text not null,
        primary key(id),
        fulltext key content_index(content)
    ) engine=Innodb default charset=utf8;
    
    insert into test (content) values ('a'),('b'),('c');
    insert into test (content) values ('aa'),('bb'),('cc');
    insert into test (content) values ('aaa'),('bbb'),('ccc');
    insert into test (content) values ('aaaa'),('bbbb'),('cccc');
    

    按照全文索引的使用语法执行下面查询:

    select * from test where match(content) against('a');
    select * from test where match(content) against('aa');
    select * from test where match(content) against('aaa');
    
    # 使用完整的 Match() 说明传递给 Match() 的值必须与 FULLTEXT() 定义中的相同。如果指定多个列,则必须列
    # 出它们(而且次序正确)。且搜索不区分大小写。
    

    结果发现只有最后那条SQL有一条记录,为什么呢?

    这个问题有很多原因,其中最常见的就是 最小搜索长度 导致的。另外插一句,使用全文索引时,测试表里至少要有 4 条以上的记录,否则,会出现意想不到的结果。

    MySQL 中的全文索引,有两个变量,最小搜索长度和最大搜索长度,对于长度小于最小搜索长度和大于最大搜索长度的词语,都不会被索引。通俗点就是说,想对一个词语使用全文索引搜索,那么这个词语的长度必须在以上两个变量的区间内。

    这两个的默认值可以使用以下命令查看

    show variables like '%ft%';
    

    可以看到这两个变量在 MyISAM 和 InnoDB 两种存储引擎下的变量名和默认值

    // MyISAM
    ft_min_word_len = 4;
    ft_max_word_len = 84;
    
    // InnoDB
    innodb_ft_min_token_size = 3;
    innodb_ft_max_token_size = 84;1234567
    

    可以看到最小搜索长度 MyISAM 引擎下默认是 4,InnoDB 引擎下是 3,也即,MySQL 的全文索引只会对长度大于等于 4 或者 3 的词语建立索引,而刚刚搜索的只有 rabbit 的长度大于等于 3。

    配置最小搜索长度

    全文索引的相关参数都无法进行动态修改,必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1,首先打开 MySQL 的配置文件 /etc/my.cnf,在 [mysqld] 的下面追加以下内容

    [mysqld]
    innodb_ft_min_token_size = 1
    

    然后重启 MySQL 服务器,并修复全文索引。注意,修改完参数以后,一定要修复下索引,不然参数不会生效。

    两种修复方式,可以使用下面的命令修复

    repair table productnotes quick;
    

    或者直接删掉重新建立索引,再次执行上面的查询,就都可以查出来了。

    全文搜索的分类

    • 自然语言的全文搜索

    • 布尔全文搜索

    • 带查询扩展的全文搜索

    关于这几个分类,具体描述还是看官方手册手册吧

    参考文章
    官方手册

    MySQL必知必会
    全文索引的原理

    展开全文
  • MySql索引总结

    千次阅读 2021-01-25 12:47:17
    索引概念B+树索引分为聚集索引和非聚集索引(辅助索引),但是两者的数据结构都和B+树一样,区别是存放的内容。可以说数据库必须有索引,没有索引则检索过程变成了顺序查找,O(n)的时间复杂度几乎是不能忍受的。我们...
  • SQL Server 数据库之索引

    千次阅读 2022-03-20 18:00:13
    MySQL 数据库的索引1. 索引介绍2. 索引的概述2.1 索引2.2 索引的特点1. 使用索引能提升数据库的性能,主要体现在以下几个方面:2. 在提升数据性能的同时,索引有一些负面影响2.3 设计索引的注意事项3. 索引的类型3.1...
  • MySQL索引及常见面试题

    千次阅读 2022-01-19 09:33:44
    一、索引是什么? 索引(Index)是帮助 MySQL 高效获取数据的数据结构,是对表中一列或多列值进行排序的结构。 就比如索引是一本书的目录,可以通过目录快速查找自己想要查询的东西。 二、索引为什么使用B+树? 先看...
  • MySQL索引的数据结构

    千次阅读 2022-01-22 17:42:09
    索引及其优缺点2.1 索引概述2.2 优点2.3 缺点3. InnoDB中索引的推演3.1 索引之前的查找1. 在一个页中的查找2. 在很多页中查找3.2 设计索引1. 一个简单的索引设计方案2. InnoDB中的索引方案3.3 常见索引概念1. 聚簇...
  • 关于MySQL索引知识与小妙招 — 学到了!

    千次阅读 多人点赞 2020-12-19 15:28:52
    一、索引基本知识 1.1 索引的优点 1、大大减少了服务器需要扫描的数据量 2、帮助服务器避免排序和临时表 3、将随机io变成顺序io 1.2 索引的用处 ...6、如果排序或分组时在可用索引的最左前缀上完成的,则对表进行
  • 一文搞懂 MySQL 中的索引

    千次阅读 多人点赞 2021-12-01 09:47:24
    1. 什么是索引 MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。 举一个例子,平时看任何一本书,首先看到的都是目录,通过目录去查询书籍里面...
  • 文章目录分布式NoSQL列存储数据库Hbase(七)知识点02:课程目标知识点03:Phoenix二级索引设计知识点04:二级索引:全局索引设计知识点05:二级索引:全局索引实现知识点06:二级索引:覆盖索引设计知识点07:二级...
  • Oracle创建索引的基本规则

    千次阅读 2021-05-03 05:20:03
    创建索引的基本规则一、B-Tree索引1. 选择索引字段的原则:在WHERE子句中最频繁使用的字段联接语句中的联接字段选择高选择性的字段(如果很少的字段拥有相同值,即有很多独特值,则选择性很好)Oracle在UNIQUE和主键字段...
  • 非聚簇索引:数据存储和索引分开放,索引结构的叶子节点指向了数据的对应行,myisam通过 key_buffer 把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘...
  • 它提供了丰富的RESTful风格的API方便开发者使用,本文就介绍了Elasticsearch 6.6 版本如何通过RESTful API 接口获取索引统计和状态信息。 获取索引状态接口 索引级别统计信息提供有关索引上发生的不同操作的统计信息...
  • 分区表按照类型可以分为范围分区(Range)、列表分区(List)以及哈希分区(Hash),表被分区后,其对应的索引也会与普通表的索引有所不同。 基本概念   对于分区表上的索引可以分为两类:本地索引和全局索引。其中全局...
  • MySQL高级-索引优化(超详细)

    千次阅读 2022-02-14 17:05:14
    Mysql中由专门负责优化SELECT语句的优化器,主要功能就是通过计算分析系统中收集到的统计信息,为客户端请求的Query提供他认为最优的执行计划(他认为最优的,但**不一定是DBA觉得最优的,这部分最耗时间**)。...
  • 正排索引和倒排索引理解详解

    千次阅读 2020-09-18 11:29:14
    正排索引和倒排索引理解详解 一、正排索引 二、 倒排索引 三、为什么搜索引擎选用倒排索引? 四、倒排索引优点 五、小结 叮嘟!这里是小啊呜的学习课程资料整理。好记性不如烂笔头,今天也是努力进步的一天。一起...
  • mysql全文索引

    千次阅读 2022-01-13 13:28:08
    全文索引(Full-Text Search)是将存储于数据库中的整本书或整篇文章中的任意信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息,也可以进行各种统计和分析。 全文索引一般是通过倒排索引...
  • Bitmap位图索引与普通的B-Tree索引锁的比较 通过以下实验,来验证Bitmap位图索引较之普通的B-Tree索引锁的“高昂代价”。位图索引会带来“位图段级锁”,实际使用过程一定要充分了解不同索引带来的锁代价情况。 ...
  • 今天继续给大家介绍Linux运维相关知识,本文主要内容是Elasticsearch索引管理。 一、索引查看 二、索引删除 (一)删除指定索引 (二)索引模糊匹配删除 三、禁用索引通配符 四、定时删除索引
  • 文章目录一, 索引定义 (排序 + 定位)二, 索引的优缺点三, 索引的底层数据结构3.1 哈希表3.2 B树 & B+树3.2.1 B树的特点3.2.2 B+树的特点3.2.1 B+树和哈希的区别3.2.2 B+树和B树的区别3.2.3 B+树和红黑树的区别四...
  • mysql之联合索引

    千次阅读 2021-01-19 04:27:11
    mysql之联合索引测试:前期准备:建立联合索引?CREATE TABLE `test` (`id` bigint(16) NOT NULL AUTO_INCREMENT,`aaa` varchar(16) NOT NULL,`bbb` varchar(16) NOT NULL,`ccc` int(11) NOT NULL,PRIMARY KEY (`id`...
  • 一文搞懂MySQL索引(清晰明了)

    万次阅读 多人点赞 2021-02-02 17:30:43
    索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 MySQL中常用的索引结构(索引底层的数据结构)有:B-TREE ,B+TREE ,...
  • MySQL高级篇——索引的数据结构

    千次阅读 多人点赞 2022-04-11 16:35:12
    1.为什么使用索引? 2.索引的优缺点 3.InnoDB中的索引 3.1 设计索引 3.2 常见索引概念 3.2.1 聚簇索引 3.2.2 非聚簇索引 3.2.3 联合索引 4.InnoDB与MyISAM的索引对比 5.B-Tree和B+Tree的差异 1.为什么使用...
  • MongoDB 索引创建

    千次阅读 2016-12-28 15:16:20
    那这两种方式有什么差异呢,在创建索引是是否能观察到索引完成的进度呢。本文将是基于此的描述,同时也描述了索引创建相关的注意事项。 一、索引创建方式 前台方式 缺省情况下,当为一个集合创建索引
  • mysql索引的数据结构

    千次阅读 2022-03-27 14:43:57
    为什么使用索引 我们假如不使用索引的话,就像我们左边的这样,造成全文索引 加入索引的话,像我们右边的这样,那么它的速度就会快上很多。 打个比方,假如我们需要查字典的话,索引就像我们的目录一样,没有索引...
  • Lucene倒排索引简述 之索引

    千次阅读 2018-09-27 09:57:42
    Lucene倒排索引的核心内容,索引表,你对这部分真的熟悉了吗?那你知道FST用什么地方吗?FST又存储了什么内容呢?有什么功能呢?关于Burst-Trie,你知道Lucene是如何采用它的思想来加速Lucene搜索性能的吗?
  • MySQL 索引结构

    万次阅读 多人点赞 2021-05-26 20:42:33
    在上一篇 MySQL 索引类型 中,我们已经了解了索引的基本概念以及分类,那么,索引的结构是什么样的?为什么索引可以这么快?这一篇文章将继续探讨索引的实现原理和数据结构。 文章目录前言索引数据结构二叉树的局限...
  • 如何创建高性能的索引

    千次阅读 2022-01-08 20:27:44
    如何创建高性能的索引 EXPLAIN 类型分析 explain 指令可以帮助我们查看查询优化器 处理 执行计划 的一些细节信息 语法: explain + 执行计划 假如我们有这样的两张表(分类表和商品表),我们将结合explain 字段...
  • SQL索引工作原理

    千次阅读 2019-04-26 22:17:06
    SQL 当一个新表被创建之...这里,每个8K空间被称为一个数据页(Page),又名页面或数据页面,并分配从0-7的页号,每个文件的第0页记录引导信息,叫文件头(File header);每8个数据页(64K)的组合形成扩展区(Ext...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 528,915
精华内容 211,566
关键字:

怎么完成信息索引