• notice
  • Congratulations on the launch of the Sought Tech site

Simple introduction of mysql's index type

First, introduce the types of indexes

Mysql common indexes are: primary key index, unique index, common index, full-text index, composite index
PRIMARY KEY (primary key index) ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) UNIQUE (unique index) ALTER TABLE `table_name` ADD UNIQUE (`column`)
INDEX (normal index)      ALTER TABLE `table_name` ADD INDEX index_name (`column`) FULLTEXT (full text index) ALTER TABLE `table_name` ADD FULLTEXT (`column`)
Composite Index    ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)

Mysql various index differences:

Ordinary index (INDEX): the most basic index, without any restrictions
Unique index (UNIQUE): Similar to "normal index", the difference is: the value of the index column must be unique, but null values ​​are allowed.
Primary key index (PRIMARY): It is a special unique index that does not allow null values.
Full-text index (FULLTEXT): It can only be used for MyISAM tables.It is used to retrieve textual information in an article.For large data, generating full-text indexes is time-consuming and space-consuming.
Combined index: In order to improve the efficiency of mysql, a combined index can be established, following the "leftmost prefix" principle.

For example, let's say you're making a loyalty card system for a mall.
This system has a membership table
Has the following fields:
Membership Number INT
Member Name VARCHAR(10)
Member ID number VARCHAR(18)
Member Phone VARCHAR(10)
Member address VARCHAR(50)
Member Remarks TEXT

Then this membership number, as the primary key, uses PRIMARY
Member name If you want to build an index, then it is a normal INDEX
Member ID number If you want to build an index, you can choose UNIQUE (unique, no repetition is allowed)
Member remark information, if you need to build an index, you can choose FULLTEXT, full-text search.

However, FULLTEXT works best when searching for very long articles.
It is used for relatively short text, if it is only one or two lines of words, ordinary INDEX can also be used.

 Create index: CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))

 Syntax to drop an index: DROP INDEX index_name ON tableName

Second, the index is divided into single-column index and combined index

   Single-column index: that is, an index contains only a single column, and a table can have multiple single-column indexes, but this is not a composite index.
   Composite index: that is, an index contains multiple columns.

In order to visually compare the two, create another table:

CREATE TABLE myIndex (

i_testID INT NOT NULL AUTO_INCREMENT,

vc_Name VARCHAR(50) NOT NULL,

vc_City VARCHAR(50) NOT NULL,

i_Age INT NOT NULL,

i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID)

);

In these 10,000 records, there are 5 records with vc_Name="erquan", but the combination of city, age and school is different.
Take a look at this T-SQL:

The code is as follows:
SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='Zhengzhou ' AND i_Age=25;

First consider building a single-column index:

    Indexed on the vc_Name column.When executing T-SQL, the target is quickly locked on the 5 records with vc_Name=erquan, and they are taken out and placed in an intermediate result set.In this result set, first exclude records whose vc_City is not equal to "Zhengzhou", then exclude records whose i_Age is not equal to 25, and finally filter out the only eligible records.

    Although an index is established on vc_Name, MYSQL does not need to scan the entire table when querying, which improves the efficiency, but there is still a certain distance from our requirements.Similarly, the efficiency of single-column indexes established in vc_City and i_Age respectively is similar.

    In order to further squeeze the efficiency of MySQL, it is necessary to consider building a composite index.It is to build vc_Name, vc_City, i_Age into an index:
    ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);--note that when creating a table, the length of vc_Name is 50, why use 10 here? Because in general, the length of the name will not exceed 10, which will speed up the index query speed, reduce the size of the index file, and improve the update speed of INSERT.

    When executing T-SQL, MySQL does not need to scan any records until it finds a unique record! !

    Someone must ask, if a single-column index is established on vc_Name, vc_City, i_Age, and the table has three single-column indexes, the query efficiency is the same as the above combined index, right? Hey, it is very different, far lower than our combined index~~ Although there are three indexes at this time, MySQL can only use the single-column index that it thinks seems to be the most efficient.

    Establishing such a composite index is actually equivalent to establishing

 vc_Name,vc_City,i_Age
    vc_Name,vc_City
    vc_Name

    Three combined indexes like this! Why is there no composite index such as vc_City, i_Age, etc.? This is because of the result of mysql combined index "leftmost prefix".The simple understanding is to only start the combination from the leftmost.Not only the query that contains these three columns will use the composite index, but the following T-SQL will use it:

 SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="Zhengzhou"
  SELECT * FROM myIndex WHREE vc_Name="erquan"

The following will not be used:

 SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="Zhengzhou"
  SELECT * FROM myIndex WHREE vc_City="Zhengzhou"

Three, use the index

    By now you should be able to create and use indexes, right? But under what circumstances do you need to build an index? In general, columns appearing in WHERE and JOIN need to be indexed, but this is not always the case, because MySQL only indexes <, <=,=, >, >=, BETWEEN, IN, and sometimes LIKE (described later) will use the index.
    SELECT t.vc_Name FROM testIndex t LEFT JOIN myIndex m ON t.vc_Name=m.vc_Name WHERE m.i_Age=20 AND m.vc_City='Zhengzhou'  The need for i_Age to build an index, because the vc_Name of the testIndex table appears in the JOIN clause, it is also necessary to build an index on it.

    As mentioned earlier, only certain LIKEs need to be indexed? Yes.Because MySQL will not use indexes when querying with wildcard characters % and _, such as

 SELECT * FROM myIndex WHERE vc_Name like 'erquan%'

will use the index, while

 SELECT * FROM myIndex WHEREt vc_Name like '%erquan'

    Indexes will not be used.

Fourth, the inadequacy of the index

    So many good things about the index have been said above, is it really as good as the legend? Of course there will be disadvantages.

    1.Although the index greatly improves the query speed, it will reduce the speed of updating the table, such as INSERT, UPDATE and DELETE on the table.Because when updating the table, MySQL not only saves the data, but also saves the index file

    2.Create index files that will occupy disk space.In general, this problem is not serious, but if you create multiple composite indexes on a large table, the index file will swell very quickly.

End:

    Having said so much, I just want to use the index to improve the execution efficiency of the database.But indexes are only one factor that increases efficiency.If your MySQL table has large data, you need to spend time researching to establish the best index or optimize the query statement.

The above is the whole content of this article, I hope you like it.

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

lipitor 20mg sale & lt;a href="https://lipiws.top/"& gt;lipitor 10mg oral& lt;/a& gt; atorvastatin 80mg oral

Crfhrd

2024-03-07

Leave a Reply

+