How to create mysql indexes and the advantages and disadvantages of indexes _ MySQL

How to create mysql indexes and the advantages and disadvantages of indexes bitsCN.com

Mysql Tutorial: how to create mysql indexes and the advantages and disadvantages of indexes:

1. index is a data structure that helps MySQL efficiently obtain data. It is critical for high performance, but people often forget or misunderstand it. The more data the index is, the more important the index is. Databases with small sizes and low loads can have good performance even if they do not have indexes. However, when data increases, the performance decreases rapidly. Tip: worm replication, which can quickly replicate a large number of data examples: insert into emp select * from emp;

2. common indexes in MySQL

Common index

Unique index

Primary key index

Composite Index

Full-text index

Foreign key (only supported by the innodb storage engine)

2.1 Normal Index: This is the most basic index, which has no restrictions. There are several creation methods:

Create index indexName ON tablename (username (length ));

Alter table structure ALTER tablename add index indexName (username (length) Tip: length can be smaller than the actual length of the field. if it is BLOB or TEXT, length must be specified, the same below

When creating a TABLE, specify create table mytableuuu (id int not null, username VARCHAR (16) not null, INDEX indexName (username (length ))); create table mytable (id int not null, username VARCHAR (16) not null); create index index1 on mytable (id); // CREATE a common index

Delete an index: drop index index1 on mytable. a row definition is defined when Fields (columns) are declared. for example, in the primary key table definition, all fields (columns) are defined) defined after declaration, such as primary key, index create table mytable (id int not null, username VARCHAR (16) not null, index index1 (username )); 3.0 the unique index column value must be unique, but null values are allowed. 1) Create an INDEX: Create unique index indexName ON tableName (tableColumns (length) 2) modify the table structure: Alter tableName add unique [indexName] ON (tableColumns (length) 3) when creating a TABLE, specify: Create TABLE tableName ([...], UNIQUE [indexName] (tableColumns (length); 4.0 primary key index (primary key), which is a special UNIQUE index and does not allow null values. Generally, when creating a TABLE, CREATE a primary key index: create table mytable (id int not null, username VARCHAR (16) not null, primary key (ID )); of course, you can also use the ALTER command. Tip: Remember: a table can only have one primary key. The primary key index is what we call the primary key. In a table, only one primary key can exist, but multiple common indexes and unique indexes can exist. 5.0 composite indexes over 5.1 columns of indexes, which are composed of multiple columns. Add a composite index alter table mytable add index name_city_age (username, city, age). For composite indexes, you must use the leftmost index as the prefix and sort it in order to use composite indexes, there cannot be an interval between them. The following can be used for composite indexes:

Username, city, age

Usernname, city

Composite indexes cannot be used under usernname:

City, age

City

Age pay attention to composite indexes. if multiple indexes exist in a table, we can consider using composite indexes for optimization.

5.2 Explain statement: you can query the explain statement of the index type used by the SQL query statement.

6. why are there indexes and query acceleration? This is because creating an index can greatly improve the system performance. First, you can create a unique index to ensure the uniqueness of each row of data in the database table. Second, it can greatly speed up data retrieval, which is also the main reason for creating an index. Third, it can accelerate the connection between tables, especially in achieving data reference integrity. Fourth, when you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries. Fifth, by using indexes, you can use the optimizer during the query process to improve system performance. 6.1 In MySQL, BTREE and binary tree sorting 35 17 39 9 28 65 56 87 6.2 index advantages: faster query speed. 6.2 index disadvantage: occupying a large amount of disk space. But it has an impact on insertion, deletion, and update. 6.3 Tips and notes for using indexes:

If an index does not contain a column with a NULL value, it will not be included in the index as long as the column contains a NULL value. if one column in the composite index contains a NULL value, this column is invalid for this compound index. Therefore, do not set the default value of a field to NULL during database design.

Use short indexes to index string columns. if possible, specify a prefix length. For example, if a CHAR (255) column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.

Index column sorting MySQL queries only use one index. Therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. try not to include the sorting of multiple columns. it is best to create a composite index for these columns if necessary.

Like statement operations are generally not encouraged to use like operations. if they are not usable, how to use them is also a problem. Like "? A % "does not use indexes, but like" aaa % "can use indexes.

Do not perform the select * from users where YEAR (adddate) <2007 operation on the column. The operation is performed on each row, which causes the index to fail and scans the entire table, therefore, we can change it to select * from users where adddate <'2017-01-01 ';

Do NOT use not in and <> Operations

The above is how to create a mysql index and the advantages and disadvantages of the index _ MySQL content, for more information, please follow the PHP Chinese network (www.php1.cn )!