Mysql2-key and Index

Table 0, reference page One, overview two, syntax three, foreign key four, index 0, reference page http://zhidao.baidu.com/link?url= ik8ztrhl2qfzmgqstsfecp2orechkwbzbxbqjmq15socv11-rrv5bupiplzbzu570-ywroafsqanbiiisqbbskhttp://www.cppblog.com/ Wolf/articles/69089.htmlhttp://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html I. Overview 1, Basic concept (1) key is the physical structure of the database, there are two layers of action, the first layer is the constraint (constraint), to constrain the uniqueness of the data, integrity; The first layer is the index function, used to build the index, optimize the query speed, the same as the index role. (2) Normal key: There is no binding effect, but an index will be set on this key. (3) Primary key: Primary key, a table can have a primary key, the primary key is divided into a single primary key (only one column) and the composite primary key (also known as the Union primary key, can contain multiple columns), you can specify a storage primary key, and standardize the uniqueness of the data, at the same time will establish an index. The primary key is not required, but it is highly recommended to "Use primary keys for several good habits: do not change, do not Reuse" (4) Unique key: Unique key, the uniqueness of the canonical data, and an index on this key. (5) FOREIGN key: foreign key; canonical data referential integrity, and an index is created on this key. (6) A dimension of Index:key function, in some cases can replace the keyword key.

2. Primary key and unique key

(1) Same point: Uniqueness constraint (2) different points

1) Starting point/function differs: The former is a unique identifier for a row of data, which is only used to avoid duplication of data.
2) One or more columns of the former must be all not NULL, and if one column is null, it becomes NOT NULL when the primary key is added, and the Nullable property of the column will be changed back if you delete it again. The latter column 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 the column corresponding to the unique key, you can insert null multiple times (although it is also a repetition), which is determined by the principle of the index, which is the processing of the index to null.

Second, Syntax 1, add at Creation-field level (1) Ordinary key:create table T (ID int not null key), (2) Primary key:create table T (ID int not null primary key); Is the same, indicating that key is also the specified primary key, and can only be specified once in a table (cannot be specified multiple times as a federated primary key) (3) Unique key:create table T (ID int not null unique key); (4) Foreign key: it should not be (5) Index: All keys cannot be swapped for index 2, add at Creation-table level (1) Normal key: Unlike field-level designations, the normal key here is no longer the same as the primary key, even if no primary is specified Key,mysql also does not use key as primary key.

CREATE TABLE t (ID int, key (ID)), if there are other keys that use the ID (such as foreign key), he is named with a different key, if none are named, and if more than one column is specified as the key at a time, the first column name is used as the key name. CREATE TABLE t (id int, key Kismet (ID)); Specifies the name of the key constraint: cannot be used, after all, ordinary key does not have a 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, but 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 convention differs from key by using only the first column as the key name CREATE TABLE t (ID int, unique key Kismet (ID)); Specify the name of the key Call CREATE TABLE t (id int, constraint kismet unique key (ID)); Specify the name of the key

(4) Foreign key "Personally, the so-called creation of two key, is the logical two levels, that is, data integrity constraints and index optimization"

CREATE TABLE t (ID int, foreign KEY (dage_id) references dage (ID)), can execute, execute result to create 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, execution result creates an auto-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 result is the creation of a foreign key named Kismet and a common key known as Kismet.

(5) The key in Index:key and unique key (table level) can be replaced by the index, the same effect. 3, after creation (1) Add the key: Add, for example: ALTER TABLE T add primary key (ID), (2) Delete key, drop,primary key using ALTER TABLE t drop PRIMARY key; Drop, note the difference between deleting a key and deleting a column.   4. View information: show CREATE TABLE table_name; You can view various properties of a table, including key properties, storage engines, character sets, partitioning, and so on. Third, foreign key 1, the role: can make two tables association, to ensure the consistency of data and implementation of some cascade operations; 2. Support for foreign key storage engine: InnoDB, memory authentication support, other unverified. 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) Use: This syntax can be used when creating table and ALTER TABLE (3) CONSTRAINT symbol specifies the name of the key, and if not specified, automatically generates (4) on Delete and on Update indicates the event trigger settings, which can be set as parameters:

RESTRICT (Restriction of foreign key changes in appearance, default) CASCADE (following foreign key changes) set NULL (set NULL) set default (set defaults) 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(‘铜锣湾‘); insert into xiaodi(dage_id,name) values(1,‘铜锣湾_小弟A‘);(2) If in the case of younger brother to delete the eldest 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 ' ( ' dage_id ' ) REFERENCES ' dage ' ( ' ID ' (3) If you want to be forced to insert the younger brother without the establishment of the eldest brother, the result is as follows [SQL] insert into xiaodi(dage_id,name) values(2,‘旺角_小弟A‘); [err] 1452 - cannot add or update a child row: A foreign key constraint fails ( ' sample ' .< span class= "str" > ' Xiaodi ' , CONSTRAINT ' xiaodi_ibfk_1 ' FOREIGN KEY ( ' dage_id ' ) ' dage ' ( ' ID ' /span> (4) Modify Event trigger settings show create table xiaodi;#查看键名称 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 there are younger brother in the case of the removal of eldest brother: eldest brother and eldest brother of the corresponding brother was deleted; If you want to not build a big brother, forcibly inserted into the younger brother, the result is not changed, that is, failure. Iv. index "Reference: http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html" 1, Index Primer (1) Role: Indexing has a critical impact on the speed of queries. If there is no index, the query scans the entire table, and if there is an index, the query is only for the index. Because the data in the database is not in memory, each query requires that the data be transferred from the hard disk into memory, and IO will waste a lot of time. Given that the index is much smaller than the data, using an index can greatly improve the query speed, especially when the volume of data is large. (2) The index is implemented in the storage engine, not in the server tier. Therefore, the indexes for each storage engine are not necessarily identical, and not all storage engines support all index types. The most commonly used storage engine today is InnoDB. 2. Select the data type of the index: MySQL supports many data types, and choosing the right data type to store data has a significant impact on performance. In general, some of the following guidelines may be followed "(1) (2) does not apply to hash indexes": (1) Smaller data types are usually better: smaller data types typically require less space in disk, memory, and CPU caches, and are processed faster. (2) Simple data types are better: integer data is less expensive to handle than characters, because string comparisons are more complex. In MySQL, you should use a built-in date and time data type instead of a string to store the time, and an integer data type to store the IP address.Note that, for an index, you can use an integer, not a string, especially when the amount of data is large; one drawback of the integral type is that it may require some extra work (especially a large integer) for the client, but with little effect on efficiency. (3) Try to avoid null: The column should be specified as NOT NULL unless you want to store null. In MySQL, columns with null values are difficult to query optimization because they complicate indexing, index statistics, and comparison operations. You should use 0, a special value, or an empty string instead of a null value. 3. B-tree Index: The result is b-tree (Balanced binary tree) (1) Overview: The values stored by the index are sorted in the order of the indexed columns. You can use the B-tree index for full keyword, keyword range, and keyword prefix queries. If multiple columns are indexed (combined index), the order of the columns is important, MySQL can only perform a valid lookup on the leftmost prefix of the index. (2) Example: Its index contains last_name, first_name, and DOB columns for 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: Can find, can also order by "results are sorted, so search soon"

1) Match all values: Specify a specific value for all columns in the index. 2) match the leftmost prefix: You can use the index to find the last person named Allen, using only the 1th column in the index. 3) match column prefix: for example, you can use the index to find the last name of the person starting with J, which only uses the 1th column in the index. 4) Range Query matching values: You can use the index to find the last name between Allen and Barrymore, using only the 1th column in the index. 5) The matching section is accurate and the rest of the range matches: You can use the index to find last name Allen, and first name begins with the letter K. 6) Query the index only: 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, when querying A+c, the A index is used, and the explain can be used to confirm

(4) Restrictions

1) The query must start at the leftmost column of the index. 2) You cannot skip an indexed column. For example, you cannot use an index to find a person who was named Smith and was born on a day. 3) The storage engine cannot use the column to the right of the range criteria in the index. For example, if your query statement is where last_name= "Smith" and first_name like ' j% ' and dob= ' 1976-12-23 ', then the query will only use the first two columns in the index because like is a range query.

4, hash index (1) Overview

1) hash index by hash function to calculate the hash value for retrieval, you can find the data to check the row pointer, so as to locate the data. 2) The hash value does not depend on the data type of the column, and the index of a tinyint column is as large as the index of a long string column. 3) The memory storage engine supports non-unique hash indexes, and if multiple values have the same hash code, the index saves their row pointers to the same hash table item with the linked list.

(2) Restrictions

1) Because the index contains only hash code and record pointers, MySQL cannot avoid reading records by using an index. But accessing the in-memory records is very rapid and does not have too much impact on sex. 2) cannot be sorted using hash index. 3) The hash index does not support partial matching of keys, because the hash value is calculated by the entire index value. 4) The hash index only supports equivalent comparisons, such as using =,in () and <=>. For where PRICE>100 does not speed up the query.

(3) Example CREATE TABLE testhash ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, KEY USING HASH(fname) )ENGINE=MEMORY;5. Other index (1) space (r-tree) index: MyISAM supports spatial indexes, primarily for geospatial data types, such as geometry. (2) Full-text (full-text) Index: Full-text indexing is a special index type for MyISAM, mainly used for full-text retrieval.

Mysql2-key and Index