一张图让你彻底理解聚簇索引与普通索引的区别[经典]

mysq_index

下面分析下索引和锁的关系。
1)delete from msg where id=2;

由于id是主键,因此直接锁住整行记录即可。
                                                                               图5
2)delete from msg where token=’ cvs’;

由于token是二级索引,因此首先锁住二级索引(两行),接着会锁住相应主键所对应的记录;
                                                                       图6
3)delete from msg where message=订单号是多少’;

message没有索引,所以走的是全表扫描过滤。这时表上的各个记录都将添加上X锁。
                                                                        图7

强烈推荐阅读:https://yq.aliyun.com/articles/5533

 

MySQL索引背后的数据结构及算法原理

摘要

本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题。特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等。为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引,至于哈希索引和全文索引本文暂不讨论。

文章主要内容分为三个部分。

第一部分主要从数据结构及算法理论层面讨论MySQL数据库索引的数理基础。 Continue reading

mongodb索引讲解与性能调优

mongodb索引规则基本上与传统的关系库一样,大部分优化MySQL/Oracle/SQLite索引的技巧也适用于mongodb。

一、 为什么用索引:

当查询中用到某些条件时,可以对该键建立索引,以提高查询速度。

如果数据量很多且查询多于更新时,可以用索引提高查询的速度。

二、 索引管理:

a)         查询索引:

  1. 查询已有索引的明细:

查询索引很简单,比如说需要查询mailaccess数据库中的Mail collection上的索引时:

mongo                          进入mongo

MongoDB shell version: 1.8.1

connecting to: test

> use mailaccess                  进入mailaccess database

switched to db mailaccess

> db.Mail.getIndexes()             查询索引明细

[

{

“name” : “_id_”,

“ns” : “mailaccess.Mail”,

“key” : {

“_id” : 1

},

“v” : 0

},

{

“_id” : ObjectId(“4df063ac48857df7ac35c348”),

“ns” : “mailaccess.Mail”,

“key” : {

“user” : 1,

“folderId” : 1,

“mailfilename” : 1

},

“name” : “user_1_folderId_1_mailfilename_1”,

“v” : 0

},

…… Continue reading

MongoDB 索引数据类型优化,节省60%内存

最近trunk.ly的工程师通过mongostat发现了大量的page fault,然后通过检查发现,他们的索引已经超出内存限制了(没有keep all index in RAM)。于是他们决定开始减小索引大小,通过测试得出了如下的数据,不同的数据类型的索引大小有2到3倍的差距。

虽然能够想像得到,但是直观的数据图可能让我们更深刻的认识到。他们的测试再一次告诉我们:给索引定一个好的数据结构是多么重要。

这是测试结果图,分别是用int、MongoDB的ObjectID、base64和md5的字符串做索引产生的索引大小:

测试过程也非常简单,首先用下面脚本将各种不同数据结构的数据写入到不同的collection里:

#!/usr/bin/env python

import pymongo
import bson
from pymongo import Connection

db = connection.test_database

print('ObjectID')
for i in range(1, 1000000):
    db.objectids.insert({'i': i})

print('int')
for i in range(1, 1000000):
    db.ints.insert({'_id': i, 'i': i})

print('Base64 BSON')
for i in range(1, 1000000):
    db.base64s.insert({'_id': \
        bson.Binary(hashlib.md5(str(i)).digest(),
        bson.binary.MD5_SUBTYPE), 'i': i})

print('string')
for i in range(1, 1000000):
    db.strings.insert({'_id': hashlib.md5(str(i)).digest(), 'i': i})

然后获取每个collection的index大小,得到如下的结果,画成上面的图:

> db.base64s.stats()
{
        "totalIndexSize" : 67076096,
}
> db.objectids.stats()
{
        "totalIndexSize" : 41598976,
}
> db.ints.stats()
{
        "totalIndexSize" : 32522240,
}
> db.strings.stats()
{
        "totalIndexSize" : 90914816,

}

原文链接:How to save 200% RAM by selecting the right key data type for #MongoDB

相关教程:

三招解决MongoDB的磁盘IO问题

由浅入深理解索引的实现

00 – 背景知识

– B-Tree & B+Tree

http://en.wikipedia.org/wiki/B%2B_tree
http://en.wikipedia.org/wiki/B-tree

– 折半查找(Binary Search)

http://en.wikipedia.org/wiki/Binary_search_algorithm

– 数据库的性能问题

A. 磁盘IO性能非常低,严重的影响数据库系统的性能。
B. 磁盘顺序读写比随机读写的性能高很多。

– 数据的基本存储结构

A. 磁盘空间被划分为许多大小相同的块(Block)或者页(Page).
B. 一个表的这些数据块以链表的方式串联在一起。
C. 数据是以行(Row)为单位一行一行的存放在磁盘上的块中,如图所示.
D. 在访问数据时,一次从磁盘中读出或者写入至少一个完整的Block。 Continue reading

查看mysql索引使用情况

查看索引使用情况

如果索引正在工作, Handler_read_key 的值将很高,这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用。

Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明表索引不正确或写入的查询没有利用索引。

语法:

mysql> show status like ‘Handler_read%’;

有关更多MySQL之Handler_read_*介绍参考:http://blog.haohtml.com/archives/4262