Normal and composite indexes for MySQL

For general index and composite Index issues:

Index-separated index and composite index: Single-column index, that is, an index contains only single columns, a table can have multiple single-column indexes, but this is not a composite index;
MySQL index types include:
(1) The normal index is the most basic index, it has no restrictions. It is created in the following ways:
Create an index
CREATE INDEX indexname on mytable (username (length));
If it is a Char,varchar type, length can be less than the actual length of the field, and if it is a blob and text type, length must be specified.
Modify Table Structure
ALTER mytable ADD INDEX [IndexName] on (username (length))
Specify directly when creating a table
CREATE TABLE mytable (ID INT not null,username VARCHAR (+) not NULL, Index[indexname] (username (length)));
Syntax for dropping an index:
DROP INDEX [IndexName] on mytable;
(2) A unique index is similar to a normal index, except that the value of an indexed column must be unique, but a null value is allowed. If it is a composite index, the combination of column values must be unique. It is created in the following ways:
Create an index
CREATE UNIQUE INDEX indexname onmytable (username (length))
Modify Table Structure
ALTER mytable ADD UNIQUE [IndexName] on (username (length))
Specify directly when creating a table
CREATE TABLE mytable (ID INT not null,username VARCHAR (+) not NULL, UNIQUE [IndexName] (username (length)));
(3) The primary key index is a special unique index and is not allowed to have null values. The primary key index is typically created at the same time as the table is built:
CREATE TABLE mytable (id INT not null,username VARCHAR (+) not NULL, PRIMARY KEY (id));
Of course, you can also use the ALTER command.Remember: A table can have only one primary key .
(4) Combined index
To visually compare single-column and composite indexes, add multiple fields to the table:
CREATE TABLE mytable (ID int not null,username VARCHAR (+) is not null, City VARCHAR (a) not NULL, and age INT is not null);
To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build name, city, and age into an index:
ALTER TABLE mytable ADD INDEX name_city_age (name (ten), city,age);
When the table is built, the usernname length is 16, which is used here in 10. This is because, in general, the length of the name does not exceed 10, which speeds up the index query, reduces the size of the index file, and increases the update speed of the insert.
If you set up a single-column index on Usernname,city,age, so that the table has 3 single-column indexes, the efficiency of the query and the combined index above is very different, much lower than our combined index. Although there are three indexes at this point, MySQL can only use one of the single-column indexes that it considers to be the most efficient.
The establishment of such a composite index, in fact, is equivalent to the following three sets of composite indexes:
Usernname,city,age Usernname,cityusernname
Why is there no such combination of city,age? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not as long as the combined index is used for queries that contain these three columns, the following SQL uses this combined index:
SELECT * FROM MyTable whreeusername= "admin" and city= "Zhengzhou" select * FROM MyTable whree username= "admin"
And the next few are not used:
SELECT * FROM MyTable whree age=20 andcity= "Zhengzhou" select * FROM MyTable whreecity= "Zhengzhou"
(5) Timing of index creation
Here we have learned to index, so where do we need to index it? In general, the columns that appear in the where and join need to be indexed, but not entirely, because MySQL is only for <,<=,=,>,>=,between, In, and sometimes like will use the index. For example:
SELECT t.name from MyTable T left joinmytable m on T.name=m.username WHERE m.age=20 and m.city= ' Zhengzhou '
The city and age need to be indexed, because the userame of the MyTable table also appears in the join clause, and it is necessary to index it.
Just now it is only necessary to index the like at certain times. Because MySQL does not use indexes when querying with wildcards% and _. For example, the following sentence will use the index:
SELECT * FROM mytable WHERE usernamelike ' admin% '
And the following sentence will not be used:
SELECT * FROM mytable wheret namelike '%admin '
Therefore, you should pay attention to the above differences when using like.
(6) Deficiencies of the index
The benefits of using indexes are described above, but excessive use of indexes will result in abuse. So the index has its drawbacks as well:
Although the index greatly improves query speed, it also slows down the updating of tables, such as INSERT, update, and delete on tables. Because when updating a table, MySQL not only saves the data, but also saves the index file.
Index files that create indexes that consume disk space. The general situation is not too serious, but if you create multiple combinations of indexes on a large table, the index file will swell up quickly.
Indexing is just one factor in efficiency, and if your MySQL has a large data size table, you need to spend time studying to build the best indexes, or refine the query statements.
(7) Considerations for using Indexes
There are some tips and considerations when working with indexes:
The index does not contain a column with null values
This column is not valid for this composite index as long as the column contains null values that will not be included in the index, as long as there is a column in the composite index that contains null values. So we don't want the default value of the field to be null when the database is designed.
Use short Index
Index A string, or specify a prefix length if possible. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.
Indexed column Sorting
The MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.
Like statement operations
It is generally discouraged to use the like operation, which is also an issue if it is not used. Like "%aaa%" does not use the index and like "aaa%" can use the index.
Do not perform calculations on columns
SELECT * from Users whereyear (adddate) <2007;
The operation will be performed on each line, which will cause the index to fail with a full table scan, so we can change to
SELECT * from users whereadddate< ' 2007-01-01 ';
Do not use not in and <> operations
Reference: Http://zhidao.baidu.com/link?url=xF1BrmM9CNSE_ptqmRcEZQgTHIw5UK7WbMI_iAOMWzYS8MdNYbVMrpv820wzn6nxlkpyx2JAH_ oxmaqfbfr32k

Normal and composite indexes for MySQL