MySQL2-key and index, MySQL2-keyindex

MySQL2-key and index, MySQL2-keyindex
Directory zero, reference page one, Overview 2, syntax three, foreign key four, index zero, reference page http://zhidao.baidu.com/link? Url = ik8ZtrHL2qfZMgQStSFEcP2ORechkwBzbxBQjMQ15SoCV11-rRv5buPIPLZBZu570-YWRoAFsqANBiIIsQbBsKhttp: // summary I. Overview 1. Basic concepts (1) key is the physical structure of the database, which has two roles, one is the constraint function (constraint ), it is used to restrict the uniqueness and integrity of data. The first layer is the index function, which is used to create an index and optimize the query speed, which is the same as the index function. (2) normal key: it does not have a constraint, but an index will be created on this key. (3) primary key: primary key. A table can have a primary key. The primary key can be divided into a single primary key (containing only one column) and a composite primary key (also called a joint primary key, which can contain multiple columns ); you can specify a primary key and standardize the uniqueness of the Data. An index is created on the key. The primary key is not required, but it is strongly recommended that you [do not change or reuse the primary key] (4) unique key: unique key; standardize the uniqueness of data; at the same time, an index is created on this key. (5) foreign key: foreign key; standardizes the integrity of data references; and creates an index on this key. (6) index: A dimension used by the key. In some cases, the key can be replaced.

2. primary key and unique key

(1) similarities: uniqueness constraints (2) Differences

1) The starting point/function is different: the former is the unique identifier of a row of data, and the latter is only used to avoid data duplication.
2) one or more columns of the former must all be not null. If one column is null, it will become not null when it is added as the primary key. If you delete the primary key, the nullable attribute of the column is changed back. The column of the latter can be null.
3) A table can have only one primary key and multiple unique keys. [Can a table have no primary key ???]
4) for columns corresponding to the unique key, null can be inserted multiple times (although it is also a type of repetition). This is determined by the indexing principle, that is, the processing of the index to null.

Ii. Syntax 1. Add at creation-field level (1) normal key: create table t (id int not null key); (2) primary key: create table t (id int not null primary key); both have the same role, that is, specifying the key is also specifying the primary key, you can only specify one time in a table (you cannot specify multiple times as the joint primary key) (3) unique key: create table t (id int not null unique key); (4) foreign key: it should not work (5) index: All keys cannot be changed to index 2. Add at creation-table level (1) normal key: It is different from field level, the normal key here is no longer the same as the primary key. Even if the primary key is not specified, MySQL will not use the key as the primary key.

Create table t (id int, key (id); If there are other keys using id (such as foreign key), use other keys to name them; if none of them are named, id is used. If multiple columns are specified as keys at a time, the first column name is used as the key name. Create table t (id int, key kismet (id); specify the name of the key constraint: not usable. After all, normal keys have no binding effect.

(2) primary key

Create table t (id int, primary key (id); create table t (id int, primary key kismet (id); can be executed, however, the name does not work. create table t (id int, constraint kismet primary key (id) can be executed, but the name does not work.

(3) unique key

Create table t (id int, unique key (id); the naming rule is different from the key. Only the first column is used as the key name create table t (id int, unique key kismet (id); specify the key name create table t (id int, constraint kismet unique key (id); specify the key name

(4) foreign key [I personally think that creating two keys is two logical layers, namely data integrity constraints and INDEX OPTIMIZATION]

Create table t (id int, foreign key (dage_id) references dage (id); can be executed, the execution result creates an automatically named foreign key and an automatically named normal key. Create table t (id int, foreign key kismet (dage_id) references dage (id); can be executed, the execution result creates an automatically named foreign key and a common key named kismet. Create table t (id int, constraint kismet foreign key (dage_id) references dage (id); can be executed, the execution result is a foreign key named kismet and a common key named kismet.

(5) index: the keys in the key and unique key (Table-level) can be changed to the index. 3. After creation (1) add key: add, for example: alter table t add primary key (id); (2) delete key, drop, primary key uses alter table t drop primary key; other keys use names to drop. Note the differences between deleting keys and deleting columns. 4. View information: show create table table_name; you can view various attributes of a table, including key attributes, storage engine, Character Set, and partition information. Iii. Foreign key 1. function: Associate two tables to ensure data consistency and perform cascade operations. 2. A storage engine supporting foreign keys: supported InnoDB and Memory verification. Others are not verified. 3. Complete syntax (1) [CONSTRAINT symbol] foreign key [id] (index_col_name ,...) REFERENCES tbl_name (index_col_name ,...) [on delete {RESTRICT | CASCADE | set null | no action | set default}] [on update {RESTRICT | CASCADE | set null | no action | set default}] (2) usage: this syntax can be used to specify the key name in create table and alter table (3) CONSTRAINT symbol. If no key name is specified, it is automatically generated (4) on delete and on update indicate event trigger settings. You can set the following parameters:

RESTRICT (RESTRICT external key changes, DEFAULT) CASCADE (follow external key changes) set null (set null) set default (set default) no action (no action)

4. Example (1) create a table, set a foreign key, and insert data CREATE TABLE `dage` ( `id` int(11) NOT NULL auto_increment, `name` varchar(32) default '', PRIMARY KEY (`id`) ); CREATE TABLE `xiaodi` ( `id` int(11) NOT NULL auto_increment, `dage_id` int(11) default NULL, `name` varchar(32) default '', PRIMARY KEY (`id`), KEY `dage_id` (`dage_id`), CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`) ); Insert into dage (name) values ('causeway Bay '); Insert into xiaodi (dage_id, name) values (1, 'causeway Bay _ younger brother ');(2) If you still have a younger brother, the result is as follows: [SQL] delete from dage where id=1; [Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`sample`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))(3) If you want to forcibly insert a younger brother without a new eldest brother, the result is as follows: [SQL] insert into xiaodi (dage_id, name) values (2, 'mong Kok _ '); [Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`sample`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))(4) Modify event trigger settings Show create table xiaodi; # view the key name alter table xiaodi drop foreign key xiaodi_ibfk_1; alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;(5) If you still have a younger brother, delete the eldest brother: the younger brother corresponding to the eldest brother is deleted; if you want to forcibly insert the younger brother without a new eldest brother, the results remain unchanged, that is, failure. Iv. Index [Reference: http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html?1= (1): Index has a crucial impact on the query speed. If there is no index, the query scans the entire table. If there is an index, the query only performs the index. Since the database data is not in the memory, each query requires the data to be transferred from the hard disk to the memory, IO will waste a lot of time. Considering that indexes are much smaller than data, using indexes can greatly increase the query speed, especially when the data volume is large. (2) indexes are implemented in the storage engine, rather than on the server layer. Therefore, the indexes of each storage engine are not necessarily the same, and not all storage engines Support all index types. Currently, the most common storage engine is InnoDB. 2. Select the index data type: MySQL supports many data types. Selecting the appropriate data type to store data has a great impact on performance. Generally, the following guidelines can be followed: (1) (2) hash indexes are not applicable.] (1) smaller data types are generally better: A smaller data type usually requires less space in the disk, memory, and CPU cache for faster processing. (2) A simple data type is better: the processing overhead of integer data is smaller than that of characters, because the strings are more complex. In MySQL, the built-in Date and Time data types should be used instead of strings to store the time, and the IP addresses of integer data types should be used to store the time. Note: For indexes that can use integer types, do not use strings, especially when the data volume is large. One drawback of integer types is that, the cooperation with the client may require some additional work (especially the big Integer type), but it has almost no impact on the efficiency. (3) Avoid NULL as much as possible: the column should be specified as not null unless you want to store NULL. In MySQL, it is difficult to query and optimize columns with null values, because they make the index and index statistics and comparison operations more complex. You should replace null values with 0, a special value, or an empty string. 3. B-tree index: the result is B-tree (balanced binary tree) (1) Overview: The values stored in the index are arranged in the order in the index column. You can use B-Tree indexes to query full keywords, keyword ranges, and keyword prefixes. If you index multiple columns (composite indexes), the column order is very important.MySQL can only search for the leftmost index prefix. (2) Example: Its Index contains the last_name, first_name, and dob columns of each row in the table. CREATE TABLE People ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum('m', 'f') not null, key(last_name, first_name, dob) );(3) Matching Method: either search or order by [the results are sorted, so the search is fast]

1) match full value: specify a specific value for all columns in the index. 2) match the leftmost Prefix: You can use the index to find the person whose last name is Allen and only use the 1st columns in the index. 3) match the column prefix. For example, you can use the index to search for people whose last name starts with J. This only applies to the 1st columns in the index. 4) Range Query of matched values: You can use the index to find the person with the last name between Allen and Barrymore, and only use the 1st columns in the index. 5) The matching part is precise while the other part is for range matching: You can use the index to find the person whose last name is Allen and whose first name starts with the letter K. 6) only query the index: If the queried columns are in the index, you do not need to read the values of the tuples. 7) if the index field is A + B, will the index be used when querying A + C?-> yes, the explain statement can be used to confirm

(4) Restrictions

1) the query must start with the leftmost column of the index. 2) You cannot skip an index column. For example, you cannot use the index to find the person whose last name is Smith and was born on a certain day. 3) the storage engine cannot use the column on the right of the range condition in the index. For example, if your query statement is WHERE last_name = "Smith" AND first_name LIKE 'J % 'AND dob = '2017-12-23 ', only the first two columns in the index are used for this query, because LIKE is a range query.

4. Hash index (1) Overview

1) the Hash Index uses the Hash function to calculate the Hash value for retrieval. You can find the row pointer of the data to be queried to locate the data. 2) the Hash value does not depend on the Data Type of the column. The index of a TINYINT column is as large as that of a long string column. 3) The Memory storage engine supports non-unique hash indexes. If multiple values have the same hash code, the indexes store their row pointers in a linked list to the same hash table item.

(2) Restrictions

1) Because indexes only contain hash code and record pointers, MySQL cannot avoid reading records by using indexes. However, the access records in the memory are very fast and will not have a huge impact on the performance. 2) hash indexes cannot be used for sorting. 3) Hash indexes do not support partial key matching because the hash value is calculated using the entire index value. 4) Hash indexes only support equivalent comparison, for example, using =, IN () and <=>. WHERE price> 100 cannot accelerate queries.

(3) Example CREATE TABLE testhash ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, KEY USING HASH(fname) )ENGINE=MEMORY;5. other indexes (1) Spatial (R-Tree) indexes: MyISAM supports spatial indexes and is mainly used for geospatial data types, such as GEOMETRY. (2) Full-text index: Full-text index is a special index type of MyISAM and is mainly used for Full-text search.