邢栋博客

邢栋博客,Action博客,记录工作和生活中的点点滴滴

mysql索引之聚簇索引和非聚簇索引

(一)各种树结构
1 搜索二叉树:每个节点有两个子节点,数据量的增大必然导致高度的快速增加,显然这个不适合作为大量数据存储的基础结构。

2 B树:一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足:┌m/2┐ - 1 <= j <= m - 1;一个节点的子节点数量会比关键字个数多1,这样关键字就变成了子节点的分割标志。一般会在图示中把关键字画到子节点中间,非常形象,也容易和后面的B+树区分。由于数据同时存在于叶子节点和非叶子结点中,无法简单完成按顺序遍历B树中的关键字,必须用中序遍历的方法。

3 B+树:一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足:┌m/2┐ - 1 <= j <= m;子树的个数最多可以与关键字一样多。非叶节点存储的是子树里最小的关键字。同时数据节点只存在于叶子节点中,且叶子节点间增加了横向的指针,这样顺序遍历所有数据将变得非常容易。

4 B*树:一棵m阶B树是一棵平衡的m路搜索树。最重要的两个性质是1每个非根节点所包含的关键字个数 j 满足:┌m2/3┐ - 1 <= j <= m;2非叶节点间添加了横向指针

B+树适合作为数据库的基础结构,完全是因为计算机的内存-机械硬盘两层存储结构。内存可以完成快速的随机访问(随机访问即给出任意一个地址,要求返回这个地址存储的数据)但是容量较小。而硬盘的随机访问要经过机械动作(1磁头移动 2盘片转动),访问效率比内存低几个数量级,但是硬盘容量较大。典型的数据库容量大大超过可用内存大小,这就决定了在B+树中检索一条数据很可能要借助几次磁盘IO操作来完成。



(二)存储引擎和索引
有两种常见的方法可以解决多个B+树访问同一套表数据的问题,一种叫做聚簇索引(clustered index ),一种叫做非聚簇索引(secondary index)。这两个名字虽然都叫做索引,但这并不是一种单独的索引类型,而是一种数据存储方式。对于聚簇索引存储来说,行数据和主键B+树存储在一起,辅助键B+树只存储辅助键和主键,主键和非主键B+树几乎是两种类型的树。对于非聚簇索引存储来说,主键B+树在叶子节点存储指向真正数据行的指针,而非主键。

InnoDB使用的是聚簇索引,将主键组织到一棵B+树中,而行数据就储存在叶子节点上,若使用"where id = 14"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据。

MyISM使用的是非聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。


(三)聚簇索引的优势
1 由于行数据和叶子节点存储在一起,这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。

2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是,减少了当出现行移动或者数据页分裂时辅助索引的维护工作,使用主键值当作指针会让辅助索引占用更多的空间,换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置(实现中通过16K的Page来定位,后面会涉及)会随着数据库里数据的修改而发生变化(前面的B+树节点分裂以及Page的分裂),使用聚簇索引就可以保证不管这个主键B+树的节点如何变化,辅助索引树都不受影响。


原文地址:http://www.admin10000.com/document/5372.html


回顾mongo索引

测试索引

新建50万条数据
for(var i=0;i<500000;i++){db.myusers.insert({"i":i,"username":"user"+i,"age":Math.floor(Math.random()*120),"created":new Date()});}
db.myusers.find({username:"user111"}).explain(true)
{
............
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 238,//-------------
"totalKeysExamined" : 0,
"totalDocsExamined" : 500000,//-------------
"executionStages" : {
"stage" : "COLLSCAN",//-------------
"filter" : {
"username" : {
"$eq" : "user111"
},
..............
}

增加索引
db.myusers.createIndex({"username":1})
好处:提高查询效率
坏处:数据增删改时会变慢
限制:每个集合最多64个索引
db.myusers.find({username:"user111"}).explain(true)
{
.................
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0, //-------------
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,//-------------
"executionStages" : {
"stage" : "FETCH",//-------------
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
..................
}


复合索引
索引--{"age":1,"username":1}
db.myusers.find({"age":21}) 高效
db.myusers.find({"age":21}).sort({"username":-1})高效
db.myusers.find({"age":{"$gte":21,"$lte":30}})高效
db.myusers.find({"age":{"$gte":21,"$lte":30}}).sort({"username":-1})一般


特殊索引
创建一个固定集合,最大1000字节,最多100个文档,超过后会自动删除最老的文档
db.createCollection('my_collection',{'capped':true,'size':1000,'max':'100'})

TTL索引
在lastUpdated字段建立TTL索引,当服务器时间比该字段存储的日期类型时间晚86400秒之后,文档立即删除。
Mongodb每分钟对ttl索引执行一次清理
db.my_collection.createIndex({"lastUpdated":1},{"expireAfterSecs":86400})


地理空间索引
2dsphere,用于球体表面,使用GeoJSON格式(geojson.org)

{'name':'Sjm','loc':{'type':'Point','coordinates':[116.34,40.02]}}
线
{'name':'River','loc':{'type':'Line','coordinates':[[0,1],[0,2],[1,2]]}}

{'name':'Beijing','loc':{'type':'Polygon','coordinates':[[2,1],[2,2],[4,2]]}}

创建索引
db.world.createIndex({'loc':'2dsphere'})
查询
var littleVillage={
'type':'Polygon',
'coordinates':[[-70,30],[-71,40],[-70,38],[-71,40]]
}
与littleVillage有交集的区域的文档:
db.world.find({'loc':{'$geoIntersects':{'$geometry':littleVillage}}})
完全包含在littleVillage区域的文档
db.world.find({'loc':{'$within':{'$geometry':littleVillage}}})
littleVillage区域附近的文档

db.world.find({'loc':{'$near':{'$geometry':littleVillage}}})


mysql索引优化Note

Mysql支持的索引类型

B-tree索引
    特点
        B-tree索引以B+树的结构存储数据
        B-tree索引能加快数据的查询速度
        B-tree索引更适合进行范围查找[顺序存储]
    在什么情况下可以用到B数索引
        1.全值匹配的查询
            order_sn = '123456'
        2.匹配最左前缀的查询
        3.匹配列前缀查询
            order_sn like '123%'
        4.匹配范围值的查找
            order_sn > '123456' and order_sn < '654321'
        5.精确匹配左前列并范围匹配另外一列

        6.只访问索引的查询

    Btree索引的使用限制

        1.如果不是按照索引最左列开始查找,则无法使用索引
        2.使用索引时不能跳过索引中的列
        3.Not in 和 <> 操作无法使用索引
        4.如果查询中有某个列的范围查询,则其右边所有列都无法使用索引

Hash索引
    Hash索引是基于hash表实现的,只有查询条件精确匹配hash索引中所有列时,才能够使用到hash索引;
    对于Hash索引中的所有列,存储引擎都会为每一行计算一个hash码,hash索引中存储的就是hash码。

    Hash索引的限制
        1.Hash索引必须进行二次查找
        2.Hash索引无法用于排序
        3.Hash索引不支持部分索引查找也不支持范围查找
        4.Hash索引中的hash码的计算可能存在hash冲突


为什么要使用索引
    1.索引大大减少了存储引擎需要扫描的数据量
    2.索引可以帮助我们进行排序可以避免使用临时表
    3.索引可以把随机I/O变为顺序I/O

索引的缺点
    1.索引会增加写操作的成本
    2.太多的索引会增加查询优化器的选择时间


索引优化策略
    1.索引列上不能使用表达式或函数
    2.前缀索引和索引列的选择性
        create index index_name on table(col_name(n));
    索引的选择性是不重复的索引值和表的记录数的比值

3.联合索引
    如何选择索引列的顺序
        经常会被使用到的列优先
        选择性高的列优先
        宽度小的列优先

4.覆盖索引
    优点
        1.可以优化缓存,减少磁盘I/O操作
        2.可以减少随机I/O,变随机IO操作为顺序IO操作
        3.可以避免对Innodb主键索引的二次查询
        4.可以避免MyISAM表进行系统调用
    无法使用覆盖索引的情况
        1.存储引擎不支持覆盖索引
        2.查询中使用了太多的列
        3.使用了双%号的like查询

使用索引来优化查询
    使用索引扫描来优化排序
        1.索引的顺序和order by子句的顺序完全一致
        2.索引中所有列的方向(升序、降序)和order by子句完全一致
        3.order by 中的字段全部在关联表中的第一张表中
模拟hash索引优化查询
        1.只能处理键值的全职匹配查询
        2.所使用的hash函数决定着索引建的大小

利用索引优化锁
    1.索引可以减少锁定的行数
    2.索引可以加快处理速度,同时也加快了锁的是释放
        例子:
        begin;
        select * from test where name = 'x' for update;
        ----
        在开一个事务
        begin;
        select * from test where name = 'y' for update;
        如果name 没有索引,第二个事务将卡住,否则将顺利执行

删除重复和冗余的索引
    pt-duplicate-key-checker h = 127.0.0.1

查询未被使用过的索引
    select object_schema,object_name,index_name,b.`TABLE_ROWS` from performance_schema.table_io_waits_summary_by_index_usage a join information_schema.TABLES b on a.`OBJECT_SCHEMA`=b.`TABLE_SCHEMA` AND a.`OBJECT_NAME` = b.`TABLE_NAME` where index_name is not null and count_star = 0 order by object_schema,object_name;

更新索引统计信息及减少索引碎片
    analyze table table_name;
    optimize table table_name; //使用不当会导致锁表


mongo索引学习笔记

db.imooc_collection.getIndexes() //查询索引
db.imooc_collection.ensureIndex({x:1}) //增加索引
db.imooc_collection.dropIndex({x:1}) //删除索引


mongodb索引种类
_id索引 绝大多数集合默认建立的索引

单键索引 最普通的索引 
db.imooc_collection.ensureIndex({x:1})

多键索引,与单键索引创建形式相同。区别在于字段的值
单键索引:值为一个单一的值,如字符串,数字或者日期
多键索引:值具有多个记录,例如数组 
db.imooc_collection.ensureIndex({x:[1,2,3,4,5]})

复合索引,当我们查询条件不只一条时,就需要建立复合索引
插入{x:1,y:2,z:3}
db.imooc_collection.ensureIndex({x:1,y:1})


过期索引 在一段时候后会过期的索引,索引过期,相应的数据会自动删除。这适合存储一些在一段时间
之后会失效的数据,比如用户的登录信息,存储的日志。
db.imooc_collection.ensureIndex({time:1},{expireAfterSeconds:10})
db.imooc_collection.ensureIndex({time:new Date()})
存储在过期索引字段的值必须是指定的时间类型
说明:1必须是ISODate或者ISODate数组,不能使用时间戳,否则不能自动删除
2.如果指定了ISODate数组,则按照最小的时间进行删除
3.过期索引不能是复合索引
4.删除时间不是精确。说明:删除过程是由后台程序每60s跑一次,而且删除也需要一些时间,所以存在很差。

全文索引 对字符串与字符串数组创建全文可搜索的索引
适用情况{author:"",title:":",article:""}
db.articles.ensureIndex({key:"text"})
db.articles.ensureIndex({key_1:"text",key_2:"text"})
db.articles.ensureIndex({"$**":"text"})

db.articles.find({$text:{$search:"coffee"}})
db.articles.find({$text:{$search:"aa bb cc"}})
db.articles.find({$text:{$search:"aa bb -cc"}}) //不包含cc的
db.articles.find({$text:{$search:"\"aa\"bb cc"}}) // 既包含aa又包含bb的、

全文索引相似度
$meta操作符:{score:{$meta:"textScore"}}
写在查询条件后面可以返回返回结果的相似度。与sort一起使用,可以达到很好的实用效果。
db.articles.find({$text:{$search:"aa bb"}},{score:{$meta:"textScore"}}).score({score:{$meta:"textScore"}})
限制:全文索引非常强大,但是同样存在限制。每次查询,只能指定一个$text查询。$text查询中不能使用$nor查询中
查询中如果包含了$text,hint不再起作用;不支持中文。

索引属性:
创建索引时的格式:
db.collection.ensureIndex({param},{param}) 其中第二个参数便是索引的属性
比较重要的属性:名字 唯一性 稀疏性 是否定时删除
db.imooc_collection.ensureIndex({x:1,y:1,z:1,m:1},{name:"normal_index"})
唯一性,unique指定
db.imooc_collection.ensureIndex({},{unique:true/false})
稀疏性,sparse指定:
db.imooc_collection.ensureIndex({},{sparse:true/false})
是否定时删除,expireAfterSeconds指定:
TTL,过期索引

地理位置索引
概念:将一些点的位置存储在mongodb中,创建索引后,可以按照位置来查找其他点。
查找方式:
1.查找距离某个点一定距离内的点 2.查找包含在某区域内的点。
子分类:2d索引,用于存储和查找平面上的点。
创建方式:db.imooc_collection.ensureIndex({"w":"2d"})
位置表示方式:经纬度【经度,纬度】 取值范围:经度【-180,180】 纬度【-90,90】
查询方式:1.$near查询:查询距离某个点最近的点。2.$geoWithin查询:查询某个形状内的点。
形状的表示:1.$box:矩形,使用 {$box:[[<x1>,<y1>],[<x2>,<y2>]]}表示
2。$center 圆形,使用{$center:[[<x1>,<y1>],r]}
3. $polygon 多边形 使用{$polygon:[[<x1>,<y1>],[<x2>,<y2>],[<x3>,<y3>]]}表示 
db.imooc_collection.find({w:{$near:[1,1]}})
db.imooc_collection.find({w:{$near:[1,1],$maxDistance:10,$minDistance:3}})

geoNear查询
geoNear使用runCommand命令进行使用,常用使用如下
db.runCommand({geoNear:<collection>,near:[x,y],minDistance:(对2d索引无效),maxDistance:,num:}})

2dsphere索引,用于存储和查找球面上的点。
概念:球面地理位置索引
创建方式:db.imooc_collection.ensureIndex({w:"2dsphere"})
位置表示方式:
GeoJSON:描述一个点,一条直线,多边形等形状
格式:{type:"",coordinates:[<coordinates>]}
查询方式与2d索引查询方式类似:
支持$minDistance与$maxDistance

索引构建情况分析
索引好处:加快索引相关的查询,不好处:增加磁盘空间消耗,降低写入性能。

如何评判当前索引构建情况
1.mongostat工具介绍,查看mongodb运行状态的程序
使用说明:mongostat -h127.0.0.1:27017
字段说明:索引情况:idx miss
2.profile集合介绍
db.getProfilingStatus() //profile默认是关闭的 db.ProfilingLevel()
db.setProfilingLevel(2) //开启
db.system.profile.find().sort({$natural:-1})//查询profile日志,字段意义:ts 时间戳 info 具体的操作 milis 操作所花时间,毫秒

3.日志介绍
4.explain分析

db.imooc_collection.find().explain()


优惠券
广告位-淘宝
最新微语