Shulou(Shulou.com)06/01 Report--

1 mysql index type [mainly divided into 4 types of indexes]

Create an index:

1. Add PRIMARY KEY (primary key index) [primary key: a unique index that must be specified as primary key]

Mysql > ALTER TABLE `table_ name` ADD PRIMARY KEY (`column`)

two。 Add UNIQUE (unique index)

Mysql > ALTER TABLE `table_ name` ADD UNIQUE (`column`)

3. Add INDEX (normal index)

Mysql > ALTER TABLE `table_ name` ADD INDEX index_name (`column`)

4. Add FULLTEXT (full-text index) [support for full-text indexing and full-text retrieval from version 3.23.23, FULLTEXT, which can be created on columns of type char, varchar or text]

Mysql > ALTER TABLE `table_ name` ADD FULLTEXT (`column`)

5. Add multi-column index [Note: the query effect of multiple single-column indexes is different from that of a single multi-column index, because: when executing a query, MySQL can only use one index and will select the most restricted index from multiple indexes]

Mysql > ALTER TABLE `table_ name` ADD INDEX index_name (`column1`, `column2`, column3`)

Delete the index:

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

2 mysql index storage format

The storage engine can choose the Hash or BTree index [default] method, and the Hash index method can only be used for equation comparison of = or.

B-tree and Hash and full-text search will be subdivided in the next issue.

3 how to determine the index type according to the sql statement

a. During performance tuning, choosing which column to create an index on is one of the most important steps. There are two main types of columns that you can consider using an index: columns that appear in the where clause and columns that appear in the join clause.

b. Considering the distribution of values in the column, the larger the cardinality of the column of the index, the better the effect of the index.

c. Using short indexes, if you index string columns, you should specify a prefix length, which can save a lot of index space and improve query speed.

d. Using the leftmost prefix, as the name implies, is the leftmost priority. For example, we created a lname_fname_age multi-column index, which is equivalent to (lname) a single-column index, (lname,fname) a composite index, and (lname,fname,age) a combined index.

e. Don't over-index, just keep the index you need. Each additional index takes up extra disk space and degrades the performance of write operations.

When you modify the contents of a table, the index must be updated, and sometimes it may need to be refactored, so the more indexes, the longer it takes.

MySQL uses the index only on the following operator: =, between,in

And sometimes like (in cases where it doesn't start with the wildcard% or _).

4 cases and principles

Take our company's housing database as an example, a total of 700w of data. The same search statement SELECT id from t_basic_house where storey_name like'6 jumps 7%'

Non-indexed: 28.525s, indexed: 0.263s.

Principle: if you want to search for "6 jump 7" data in a non-indexed state, you must have each row of the entire table, which is extremely inefficient.

In the index state, the index is sorted on storey_name. Now, when looking for storey_name information, you don't need to search the whole table row by row, you can use the index to search sequentially (such as binary search), and quickly locate the matching value, so as to save a lot of search time.

Extension: the performance of UUID is not much worse than that of self-increasing ID, depending on the generation algorithm of UUID. For example, the ObjectId adopted by MongoDB is an excellent UUID strategy, which is composed of timestamp + machine code + process code + self-increment, in which both machine code and process code can be generated at one time, so the acquisition of an ObjectId is only one more timestamp than the self-increment ID. In addition, considering that the self-increasing ID has to do the primary key unique index, and UUID can only do the index, do not do the unique index (using its characteristics, can not consider unique filtering), its performance can be said to be no worse than the self-increasing ID.

As for the use of UUID or self-increasing ID, it mainly depends on whether the project is large enough and whether the amount of data is enough. In terms of convenience, self-increasing ID is easy to use and does not need additional support, while UUID is relatively troublesome, which involves the selection of UUID algorithm, program embedding and so on. In terms of the effect of dealing with large systems, UUID is much better than self-increasing ID. How to choose is to look at your actual situation and choose according to your needs.

MySQL uses self-increasing ID primary key and UUID as primary key to compare the pros and cons (from millions to tens of millions of tables to record tests)

Http://blog.csdn.net/mchdba/article/details/52336203

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.