Shulou(Shulou.com)06/01 Report--

Not null and defaultcreate table T1 (id int primary key auto_increment, set the primary key id, if searched through id, it will greatly reduce the IO time name varchar (16) not null, cannot be empty sex enum ('male','female') not null default' male' cannot be empty and default is male); insert into T1 (name) values ('egon'), (' lxx'), ('alex'); unique key: cannot repeat

Primary key: can not be repeated and can not be empty, there is a key function

Looking at primary key=not null unique from the Angle of constraint

Whenever you build a table in the future, you must pay attention to:

1. Must have one and only one primary key

2. Usually the id field is set as the primary key

Create table T5 (

Id int primary key auto_increment

);

Storage engine

Foreign key: restricts that the value of a field in an associated table must come from a field of the associated table

1. The associated field must be a key, usually an id field

2. When creating a table: the associated table must be established before the associated table can be established.

1 the associated field must be a key, usually the id field 2 when creating a table: the associated table must be created before the associated table 3 when inserting records: records must be inserted into the associated table before records can be inserted into the associated table

Tips for finding the relationship between two tables

One-to-one: there is no need to refer to tricks. A piece of data in the left table corresponds to a record in the right table. Why is the index on mysql using a Btree instead of a B tree?

(1) B+ tree is more suitable for external storage (generally refers to disk storage), because the internal node (non-leaf node) does not store data, so a node can store more internal nodes, and each node can index a larger and more accurate range. In other words, the amount of information of using a single disk IO of B + tree is larger than that of B tree, and IO is more efficient.

(2) mysql is a relational database, which often accesses a certain index column according to the interval. The chain pointers between the leaf nodes of the B + tree are established sequentially, which enhances the interval accessibility, so the B + tree is very friendly to the interval range query on the index column. On the other hand, the key and data of each node of the B-tree are together, so it is impossible to find the interval.

The difference between a B + tree and a B tree is:

(1) all keywords are stored in leaf nodes, and non-leaf nodes do not store real data

(2) A chain pointer is added to all leaf nodes.

Locality principle and disk pre-reading

To improve efficiency, minimize the number of disk IO. In the actual process, the disk is not strictly read on demand every time, but pre-read every time. After the disk has read the required data, it will read more of the data into memory in order, based on the locality principle stated in computer science:

When a data is used, the data near it is usually used immediately, and the data needed during the run of the program is usually more centralized.

(1) due to the high efficiency of disk sequential reading (no seek time, only a little rotation time)

Therefore, for local programs, pre-reading can improve the efficiency of Icano. The length of the pre-read is generally an integral multiple of the page.

(2) MySQL (using InnoDB engine by default), records are managed as pages, and each page size defaults to 16K (this value can be modified). The default page size for linux is 4K.

When the system reads data from disk to memory, it takes disk block (block) as the basic unit, and the data located in the same disk block will be read out at once, rather than what is needed.

There is the concept of Page in the InnoDB storage engine, which is the smallest unit of disk management. The default size of each page in the InnoDB storage engine is 16KB. You can set the page size to 4K, 8K, and 16K by parameter innodb_page_size. In MySQL, you can view the page size by using the following command:

Mysql > show variables like 'innodb_page_size'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.