Use MySQL database to support Schemaless Database storage solution _ MySQL

Using MySQL database to support Schemaless Database storage solution bitsCN.com

Lazy living-Coding for fun must face life

Use MySQL database to support Schemaless Database storage solutions

On PyCon, some kids shoes share similar concepts, but it is not suitable for general Internet projects. it feels a little too different. However, I implemented this solution before seeing PyCon's sharing. The same requirement is different. Here, I only implement a data access component, not a Server.

First, the method in this article is from how to use the MySQL database shared by FriendFeed. In short, the Python object is directly dumps and then zip compressed and stored in a MySQL field. Isn't Schemaless enough? MySQL does not need to know what data types and classes are stored, and does not need to modify the database table structure when adding an attribute, it is no longer suitable for Internet businesses with fast business changes and growth. The access object is directly queried through the primary key, which is fast and direct. But, what should I do? Some children's shoes may ask. OK. the query score is two points. for simple retrieval, you can create an index table or use an external index, such as lucent, you can also perform full-text search. Now, I have implemented the index table indexing method, because the external index method is quite strange, so you can write one as needed, you can implement several corresponding methods.

The preceding example is used to describe. Suppose you want to implement a blog and need to store the blog Information. First, define a model class for the blog (what do you need to import automatically)

1 class Blog (DynamicBase): 2 title = Column (unicode, max_length = 200) 3 content = Column (unicode) 4 post_date = Column (datetime. datetime, db_index = True) 5 auther = FkColumn (User) 6 class Meta: 7 table_name = "blogs" 8 connection = connections [DB]

This connection is because I haven't thought about how to seamlessly integrate it into Django and take into account the temporary measures that can be used independently of Django. the complete version will be removed.

If you are using django, you only need python manage. py shell and then Blog. objects. create_table ()

At this time, the table and index table defined by the model are automatically created.

Data table blogs:

Create table 'blogs '(

'Id' int (11) not null,

'Object' varbinary (20000) not null,

Primary key ('id ')

) ENGINE = InnoDB default charset = utf8;

Create two index tables at the same time

Create table 'blog _ idx_post_date '(

'Id' int (10) unsigned not null,

'Post _ date' datetime not null,

Primary key ('id '),

INDEX 'idx _ blogs_by_post_date '('post _ date') USING BTREE

) ENGINE = InnoDB default charset = utf8;

Create table 'blog _ idx_auther '(

'Id' int (10) unsigned not null,

'Audio' int not null,

Primary key ('id '),

INDEX 'idx _ blogs_by_auther '('auther') USING BTREE

) ENGINE = InnoDB default charset = utf8;

At this time, you can use the Blog. objects. create (title = u "title", content = u "content", post_date = datetime. datetime. now (), auther = user) or Blog. objects. create (title = u "title", content = u "content", post_date = datetime. datetime. now (), auther = user. id)

You can create a Blog object. Data is inserted to both the blogs table and the two index tables. However, the object column in the blogs table is an object column that humans cannot understand ..........

Get the Blog object directly by id. objects. get (1), get the Blog according to the index. objects. auther. query (auther = user. id) or Blog. objects. auther. query (auther = user)

This will generate an SQL statement, SELECT 'id' FROM 'blog _ idx_auther 'Where 'Audio' = % s, retrieve the id list of the matched object, traverse the id list, and use the blog. objects. get (id) object list.

Blog. objects. get (id) has an object cache (implemented through redis at this stage), so after testing, the speed is reliable. Compared with MangoDB, MySQL data storage is more reliable. Therefore, compared with mangodb, which is currently not very reliable, the MySQL-based Schemaless solution is relatively reliable.

As this item is still in the project incubator stage, it is not yet open-source for everyone to entertain, so please give more comments and suggestions on this solution, the source code is estimated to be able to be announced after the Spring Festival

Comrade Alexander

BitsCN.com