SQL index--Basic theory

1, the definition of the index

　　An index is a sort of a column or column of values in a database table that is used to quickly find a specific value in a column.

2, the principle of the index

If you do not use an index, the usual query data requires a one by one comparison of the data in the table until all related behaviors are found. That is, the larger the amount of data in a database table, the less efficient the query, and the index is to quickly reach a location to query the data file without having to query all the data. Simply put, the index is the same as the directory, first find the corresponding directory, and then according to the ROWID saved in the index, quickly find the corresponding data required.

3, the characteristics of the index

　　A. Pros: Speed up the rate of queries, which is the most important point of the index

Speeds up the link between table and table, especially in terms of referential integrity of the data (referential integrity is achieved by defining the corresponding relationship between foreign keys and the other), with special significance

When querying using grouping and sorting clauses, you can also significantly reduce the time to group and sort in a query

B. Cons: When creating and maintaining indexes, it takes time, and with the increase in quantity, the time is increasing

Indexes need to occupy a certain amount of disk space. In addition to the data table occupies the data space, each index must occupy a certain physical space, when the data volume is increasing, the space occupied by the index is even greater than the data space when the data table is increased, deleted, changed, the index also needs to be dynamic maintenance, which increases the burden of the system, reducing maintenance speed.

4, the classification of the index

　　A. Common indexes and unique indexes

Normal index: Allows null values and duplicates in data table columns;

Unique index: Allow null values in data table columns, but do not repeat. The primary key is a special unique index because the primary key is not allowed to be empty;

Syntax: CREATE INDEX index name on table (field name)--------------Normal index

Create unique index index name on table (field name)-------Unique indexes

B. Single-column indexes and combined indexes

Single index: One index specifies only one column, and one table can have multiple singleton indexes

Composite index (compound index): An indexed column contains more than one field in a table and can contain up to 16 fields, and the composite index will only be valid if the left field is used in the query criteria. The combined index adheres to the principle that the field with the higher frequency is ranked first.

Syntax: Create unique index index name on table (field name 1, field Name 2, ...) )----Combined Index

C. Full-text indexing

The

Full-text index type is fulltext, supports full-text lookups on values that define columns, allows columns to be inserted in duplicate or null values, and full-text indexes can be created on columns of type char, varchar, text, CLOB, and so on, and the full-text index is primarily through the Span style= "COLOR: #0000ff" >contains to implement. For example   select * from table name where contains (Field 1, Field 2), ' Yao Ming In addition, only the MyISAM storage engine supports full-text indexing in mysql

　　

5, the design principles of the index

< Span style= "COLOR: #000000" > The index is designed to increase the query rate of the data, But if a bad index design not only takes up a lot of disk space, it also < Span style= "COLOR: #0000ff" > great impact on system performance .

< Span style= "COLOR: #000000" > 1, a database table with a small amount of data, without having to add an index. Because the time to traverse the index may be longer than the normal query time, the index does not produce any optimization effect.

< Span style= "COLOR: #000000" > 2, frequently increase, delete, change the database table, as little as possible to add indexes. Because each DDL operation on the table, the corresponding indexes are updated and adjusted as changes are made to the table data.

< Span style= "COLOR: #000000" > 3, fields that are frequently used in conditional expressions, but those with a lower value are not necessary to add an index. For example, in the Gender field, only ' male ', ' female ', there is no need to add an index, otherwise it will not only reduce the performance of the update data, but also on the query when there is no performance improvement.

4. Add an index on a field that frequently uses order by or group by, and if you have multiple fields, you can use a composite index.

5. You can use a unique index when a field has a unique (unique) feature. It not only ensures data integrity, but also improves query performance.

　　

>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>

as Tristan Nitot , President of Firefox , explains the reasons for Firefox's success, Tristan Nitot said: "In the last 6 years, Web technology has been evolving while the browser has not improved, as Microsoft has fallen asleep." ”

And I also believe that today's technology is updated every day, if we fall asleep today, tomorrow we will be behind.

Each of my words is my own hand, I can not guarantee that every word is correct, but I guarantee that every word has been heart. ------Wish we could make progress together

SQL index--Basic theory