MySQL Index usage tutorial

1. What index

An index is a special kind of file (an index on a InnoDB data table is an integral part of a table space), and they contain location information for all records in the datasheet. More generally, the database index is like a directory in front of a book, which can speed up the database query.

2. How indexing Works

In addition to dictionaries, there are examples of indexes in life, such as train station schedules, book catalogs, and so on. They all work the same way, by shrinking the range of data they want to filter out the results they want, and by turning random events into sequential events, that is, we always lock data by the same search method.

The database is the same, but obviously much more complex, because not only is it facing the equivalent query, but also the scope query (>, <, between, in), Fuzzy query (like), the set query (or), and so on. How should the database choose the way to deal with all the problems? We recall the example of the dictionary, can we divide the data into segments and then query it in segments? The simplest if 1000 data, 1 to 100 is divided into the first paragraph, 101 to 200 is divided into the second paragraph, 201 to 300 is divided into the third paragraph ... This check No. 250 data, as long as the third paragraph can be, all of a sudden to remove 90% of invalid data.

3. See if an index has been created in a table

Show index from table name;

4. Create an index (if the specified field is a string, you need to specify the length, the recommended length is the same length as when the field is defined, the field type is not a string, you can not fill in the length section)

Create index name on data table (field name length)

5. Deleting an index

Drop index index name on data table

6. Test the data sheet to speed up the query 6.1 create a data table

CREATE TABLE My_index (title varchar (10));

6.2 Inserting data

From Pymysql import connectdef main ():    # Create connection connection    conn = connect (host= ' localhost ', port=3306,database= ' Python_test_1 ', user= ' root ', password= ' xu666666 ', charset= ' UTF8 ')    # get Cursor object    cursor = Conn.cursor ()    # Insert 100,000 times data for    I in range (100000):        cursor.execute ("INSERT into my_index values (' ha-%d ')"% i)    # submit Data    Conn.commit () if __name__ = = "__main__":    Main ()

6.3 Start Query and start time detection

• Turn on Run time monitoring:

Set profiling=1;

• Find 10,000th Data ha-99999

SELECT * from My_index where title= ' ha-99999 ';

• To view the time of execution:

Show Profiles;

• To create an index for the title column of table Title_index:

Create INDEX Test_index on My_index (title (10));

• To execute a query statement:

SELECT * from My_index where title= ' ha-99999 ';

• See the time of execution again

Show Profiles;

6.4 Test Results

Attention

1. Indexes can significantly improve the efficiency of query for some fields, but not the efficiency of inserting, updating, and deleting data, as the result of the change in data table data will cause the index to be rearranged, reducing efficiency

2. It is important to note that too many indexes will affect the speed of updates and insertions because it requires the same update for each index file. For a table that often needs to be updated and inserted, there is no need to index a rarely used where clause, and for smaller tables, the cost of sorting is not significant and there is no need to create additional indexes. Indexing takes up disk space.

3. Build an index to create on a common query field, and build on your own needs by the number of

　

MySQL Index usage tutorial