MySQL storage engine, field type, Index introduction

One: Common storage Engine:
1,myisam:
I built a tb_demo table for the MyISAM engine, and then I generated the following three files:
1>TB_DEMO.FRM, storage table definition;
2>tb_demo. MYD, storing data;
3>tb_demo. MYI, stores the index.
Features: Fast query, slow write, support for table locks, support for full-text indexing
Suitable for managing mail, Web server log data, when selecting dense structure tables, inserting dense structures
When the table is used
2,innodb
1 "Update the dense table. The InnoDB storage engine is ideal for handling multiple concurrent update requests.
2 "business. The InnoDB storage engine is a standard MySQL storage engine that supports transactions.
3 "Automatic disaster recovery. Unlike other storage engines, the InnoDB table can automatically recover from a disaster.
4 "FOREIGN KEY constraint. MySQL supports the foreign key storage engine only InnoDB.
5 supports automatic increment of column auto_increment attribute.
In general, InnoDB is a good choice if transaction support is required and there is a high frequency of concurrent reads.
Features: Fast write, slow query, automatic disaster recovery, high security, suitable for large data volume, high concurrency, support foreign key, row lock,
Pay composite index, update dense table, support automatic increase

Two: The table must have four field fields:
ID insert_time update_time Status
Three, 5 large index
1 index: Like a catalogue of books
2 "When to use:
3 "principle: The index will be stored on the corresponding pointer, when querying a certain data, he will go to the pointer to a data module
4 "Benefits: Improved Query speed
5 "Disadvantages:
Take up the hard drive, will save the pointer to the corresponding content
2--to change the data and delete the affected
1, primary key primary self-indexed
2, the only unique mobile phone number, user nickname,
3, normal user nickname
4, compound (InnoDB support) left principle, multi-field for example: Name (name), age (Ages), Address (location) select * FROM table where
ALTER TABLE mytable ADD INDEX name_age_address (name (ten), age,,address);
Equivalent to the creation of (Name,age,address;name,address;name) three types of queries
SELECT * FROM MyTable whree name= "admin" and age= ' and address= ' Zhengzhou '
SELECT * FROM MyTable whree name= "admin" and address= "Zhengzhou"
SELECT * FROM MyTable whree name= "admin"
5, full text (MyISAM support) article
6, Short Index
Four, the field type of the table
Plastic
1,tinyint 1 bytes have a sub-character 127, no characters 255
2,smallint 2 bytes have a sub-character 32767 no characters 65535
3,mediumint 3 bytes have a sub-character 8388607 no characters 16777215
4,int 4 bytes (11 digits, 1 billion levels, if signed, Max deposit 2190000000, unsigned 4200000000)
5,bigint 8 bytes (19-digit, billion-billion level)
8,float 4 bytes
9,double 8 bytes
String
10,char 1 bytes Fixed length characters
11,varchar 2 bytes Default 255, maximum 65,535 characters
12,tinytext variable length maximum of 255 characters
13,text variable length, maximum 65,535 characters
14,mediumtext variable length, max 2 24 times-1 characters
15,longtext variable length, up to 2 of 32 square-1 characters
Time format
Data 3 byte date, format: 2014-09-18
Time 3 bytes Temporal format: 08:48:15
DateTime 8-byte datetime format 2014-09-18 08:48:15
Timestamp 4 bytes Automatically stores the time that a record was modified
Year 1 byte years
Five, what data types are used in some characters commonly used segments
1>user_name varchar
2>tel bigint//occupies 8 bytes
3>sex char (1)/tinyint/enum//1 bytes
4>card varchar (16)//32 bytes
5>status tinyint//occupies 1 bytes
6>age tinyint//occupies 1 bytes
7>is_hot tinyint//occupies 1 bytes
8> timestamp int//accounted for 4 bytes
9> time format datetime//accounted for 8 bytes
Six, the fields of the news table are for example:
1>id primary key auto-increment int
2>title News title varchar (30)
3>add_time Press Release time
4>author author
5>content article content Longtext
6>keyword keywords
7>url website
8>imges News Cover
9>is_hot is the hottest
10>is_new is the latest
Whether 11>is_user is a member release
12>is_agree whether to audit
13>stick_rank Sticky level
14>is_promote whether to promote
15>is_business whether commercial promotion
16>is_header is the headline
17>user_id Author ID
18>press Publishing Set
19>hits_num Click Volume
20>is_comment Comment
21>type News Types
22>img_ thumb thumbnail
23>TITLE_TYPE_ID Title Category ID
24>status status
25>update_time modification Time
Seven: Order number timestamp. user_id. User Login Pc/app
Eight: After the completion of the table to use three paradigm to check

MySQL storage engine, field type, Index introduction