The index is used to quickly find rows with a specific value on a column. Without an index, MySQL had to start with the first record first and then read the full table until it finds the relevant rows.
The bigger the table, the more time it takes. If the table has an index for the columns of the query, MySQL can quickly reach a location to search for the middle of the data file, and there is no need to consider all data.
If a table has 1000 rows, this is at least 100 times faster than sequential reads. Note that you need to access almost all 1000 rows, which reads faster in order, because at this point we avoid disk seeking.
All MySQLindex(PRIMARY, UNIQUE and INDEX) inStore in B-tree. Strings automatically compress prefix and end space.
Index is used for:
Quickly find the line matching a WHERE clause;
When performing a join, search rows from other tables;
Find the MAX() or MIN() value for a specific index column;
If sorting or grouping is done on the leftmost prefix of an available key (for example, ORDER BY key_part_1, key_part_2), sort or group a table.
If all key-value parts follow DESC, the keys are read in reverse order.
In some cases, a query can be optimized to retrieve values without consulting data files.
If all columns used for certain tables are numeric and constitute the leftmost prefix of certain keys, the values can be retrieved from the index tree for faster times.
—————————————————————————————————————————————————————————————————————————————
The following are the statements for creating a table:
CREATE TABLE `phpcolor_ad` (
`id` mediumint(8) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`type` mediumint(1) NOT NULL,
`code` text,
PRIMARY KEY (`id`),
KEY `type` (`type`)
);What does the last sentence mean by KEY `type` (`type`)?
If it's just a key, it's a normal index。
MySQL's key and index are somewhat confusing.The actual meaning of the key (primary key) combined with other keywords is different., this actually examines the correctdatabaseUnderstanding of architecture.
1 ：
key is the physical structure of a database, which contains two layers of meaning and function.
First, constraints (focus on constraints and standardize the structural integrity of the database).
The second is index (for auxiliary query).
Including primary key, unique key, foreign key, etc.
primary keyIt has two functions. One is the constraint function, which is used to standardize a storage primary key and uniqueness, but at the same time, it also establishes a primary key index on this key;
PRIMARY KEY Constraint: Uniquely identify each record in the database table;
The primary key must contain a unique value;
Primary key columnCannot contain NULL values；
Each table should have a primary key, and each table can only have one primary key. (PRIMARY KEY has automatic UNIQUE constraints)
unique keyThere are also two functions, one is the constraint, which regulates the uniqueness of data, but at the same time, a unique index is established on this key; UNIQUE constraint: Uniquely identifies each record in the database table.
Both UNIQUE and PRIMARY KEY constraints provide a guarantee of uniqueness for columns or column sets.
(Each table can have multiple UNIQUE constraints, but each table can only have one PRIMARY KEY constraint)
visible,MySQL key has both constraint and index meaning, this may be different from other database performance.
(At least, the foreign key is created on oracle, and the index will not be automatically created), so there are several ways to create a key:
(1)Field levelCreate in the key form, such as create table t (id int not null primary key);
(2)Table levelbyConstraint methodCreate, such as create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
(3)Table levelbyKey methodCreate, such as create table t(id int, primary key (id));
Other key creation is similar, but no matter which way, both the constraint and the index are created, but the index uses this constraint or key.
2：index is the physical structure of the database, it is only auxiliary query.When it is created, it will be stored in another tablespace (innodb tablespace in mysql) in a directory-like structure. If the index is to be classified, it is divided into prefix index, full text index, etc.
Therefore, the index is just an index.It does not constrain the behavior of indexed fields(That's what the key has to do). For example, create table t(id int,index inx_tx_id (id));
3 Summary, the final explanation:
(1) We sayIndex classification, divided into
Primary key index (must be specified as "PRIMARY KEY”, no PRIMARY Index),
Unique index (unique index, generally written as unique key）、
Normal index (index, only this one is pure index)wait,It is also based on whether the index is regarded as a key。
For example, create table t(id int,unique indexinx_tx_id (id));--index is used as key
(2) The most important thing is that no matter how you describe it, you need to understand that index is a pure index (or ordinary key, or ordinary index index) or is regarded as a key (such as: unique index, unique key and primary key). If used as a key, it will have two meanings or play two roles.
—————————————————————————————————————————————————————————————————————————————
The meaning of MySQL Key value (PRI, UNI, MUL)：
PRI primary key constraints;
Unique constraints for UNI;
MUL can be repeated.
Note: If it is an ordinary key or an ordinary index (in fact, ordinary key is synonymous with ordinary index).
When we are in the desc table name;, there is a Key value indicating whether the column contains an index.
Assume the table structure is as follows
mysql> desc aa;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
| xx | int(11) | YES | PRI | NULL | |
+-------+---------+------+-----+---------+-------+
| yy | int(11) | YES | UNI | NULL | |
+-------+---------+------+-----+---------+-------+
| zz | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
When we see that Key column, there may be 4 values, namely 'nothing', 'PRI', 'UNI', 'MUL'
1. If the Key is empty, then the column value can be repeated to indicateThis column has no index, or oneNot uniquecompound index ofNoLeading column
2. If the Key is a PRI, then the column is a component of the primary key
3. If Key is UNI, then the column is aThe first column of the unique value index (leading column),andCannot contain null values (NULL)
4. If the Key is MUL, then the value of the column can be repeated, and the column is aNot uniqueThe leading column of the index (first column)Or oneComponents of unique indexbutCan contain null value NULL
Note:
1. If the definition of a column meets the above four situations at the same time, for example, a column is both PRI and UNI (if it is PRI, it must be UNI)
Then when "desc table name";, the displayed Key value is displayed according to priority. PRI->UNI->MUL
Then at this time, the PRI is displayed.
2、If a column cannot contain a null value and the table does not have a primary key, a unique index column can be displayed as a PRI.
3. If multiple columns form a unique composite index, then a unique index column can be displayed as MUL. (Because although the multiple column combinations of indexes are unique, such as ID+NAME is unique, each individual column can still have duplicate values, because as long as ID+NAME is unique)
1. The difference between key and primary key
CREATE TABLE wh_logrecord (
logrecord_id int(11) NOT NULL auto_increment,
user_name varchar(100) default NULL,
operation_time datetime default NULL,
logrecord_operation varchar(100) default NULL,
PRIMARY KEY (logrecord_id),
KEY wh_logrecord_user_name (user_name)
) Analysis:
KEY wh_logrecord_user_name (user_name)
The user_name field of this table and the user_name field of the wh_logrecord_user_name table user_name field create foreign keys
Outside brackets are the corresponding tables for creating foreign keys, and within brackets are the corresponding fields.
Similarly, KEY user(userid)
certainly,Keys may not be foreign keys
Summarize:
Key is an index constraint. Those who constrain fields in the table are created through primary foreign unique, etc. Commonly used for foreign keys, foreign keys.
KEY forum (status,type,displayorder) # is a multi-column index (key)
KEY tid (tid) �
If when creating a table: KEY forum (status, type, displayorder)
select * from table group by status,type,displayorder Whether this index is automatically used,
And when select * from table group by status is this index useful?
The purpose of key: mainly used to speed up query speed.
CREATE TABLE `admin_role` (
`adminSet_id` varchar(32) NOT NULL,
`roleSet_id` varchar(32) NOT NULL,
PRIMARY KEY (`adminSet_id`,`roleSet_id`),
KEY `FK9FC63FA6DAED032` (`adminSet_id`),
KEY `FK9FC63FA6C7B24C48` (`roleSet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Primary key, two columns are combined together, are unique, built-in unique index, and cannot be NULL
In addition, the two key definitions are equivalent to indexing the two columns respectively.
innodb
primary key primary key clustered index
key Normal index
KEY is usually synonymous with INDEX. If the keyword attribute PRIMARY KEY is given in the column definition, PRIMARY KEY can also be specified as KEY only.
The purpose of this is to be compatible with other database systems.
PRIMARY KEY is a unique KEY, at this time, all keyword columns must be defined as NOT NULL.
If these columns are not explicitly defined as NOT NULL, MySQL should define these columns implicitly. There is only one PRIMARY KEY for a table.
The difference between Index and Key in MySQL
Key is a key value, which is part of the theory of relational model, such as primary key, foreign key, etc., which are used for data integrity checks and uniqueness constraints.
Index is at the implementation level. For example, you can index any column of the table. When the column that has indexed is in the Where condition in the SQL statement, you can get fast data positioning and quickly search.
As for Unique Index, it is just one of the Index. A Unique Index is established to indicate that this column of data cannot be repeated. I guess MySQL can further optimize the index of Unique Index type.
Therefore, when designing tables, Key only needs to be at the model level.When query optimization is required, index the relevant columns.
In addition, in MySQL, for a Primary Key column, MySQL has automatically established a Unique Index for it, and there is no need to repeatedly index it on it.
A paragraph of searched explanation:
Note that “primary” is called PRIMARY KEY not INDEX.
KEY is something on the logical level, describes your table and database design (. enforces referential integrity …)
INDEX is something on the physical level, helps improve access time for table operations.
Behind every PK there is (usually) unique index created (automatically).
3. What is the difference between UNIQUE KEY and PRIMARY KEY in mysql
1. One or more columns of the Primary key must be NOT NULL. If the column is NULL, the column will automatically change to NOT NULL when PRIMARY KEY is added.
UNIQUE KEY does not have this requirement for columns.
2. A table can only have one PRIMARY KEY, but it can have multiple UNIQUE KEY3. Primary and unique key constraints are implemented through reference indexes. If the values inserted are NULL,
butAccording to the principle of index, all NULL values are not recorded on the index, so when inserting all NULL values, there can be duplicates, while others cannot be inserted.
alter table t add constraint uk_t_1 unique (a,b);insert into t (a ,b ) values (null,1); # Cannot be repeated
insert into t (a ,b ) values (null,null);# can be repeated
4. Use UNIQUE KEY
CREATE TABLE `secure_vulnerability_warning` (
`id` int(10) NOT NULL auto_increment,
`date` date NOT NULL,
`type` varchar(100) NOT NULL,
`sub_type` varchar(100) NOT NULL,
`domain_name` varchar(128) NOT NULL,
`url` text NOT NULL,
`parameters` text NOT NULL,
`hash` varchar(100) NOT NULL,
`deal` int(1) NOT NULL,
`deal_date` date default NULL,
`remark` text,
`last_push_time` datetime default NULL,
`push_times` int(11) default '1',
`first_set_ok_time` datetime default NULL,
`last_set_ok_time` datetime default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `date` (`date`,`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 The purpose of UNIQUE KEY: It is mainly used to prevent data from being duplicated when inserting.
1. When creating the table
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
) If you need to name UNIQUE constraints and define UNIQUE constraints for multiple columns, use the following SQL syntax:
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
) 2. When the table has been created, if you need to create a UNIQUE constraint in the "Id_P" column, please use the following SQL:
ALTER TABLE Persons
ADD UNIQUE (Id_P) To name UNIQUE constraints and define UNIQUE constraints for multiple columns, use the following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
3. Revoke UNIQUE constraints
To revoke UNIQUE constraints, use the following SQL:
ALTER TABLE Persons
DROP INDEX uc_PersonID