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

Below to bring you the specific functions of the MySQL index related to the content, I believe you must have read similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I believe you will gain something after reading the specific functions of the MySQL index.

MySQL indexing function

1. The function of index greatly accelerates the speed of data retrieval and increases the efficiency of query, reduces the cost of disk Imax O, ensures the uniqueness of data record, and makes the execution of SQL statements applied to tables faster. Index description in a relational database, an index is a separate, physical storage structure that sorts the values of one or more columns in a database table. it is a list of logical pointers to a collection of one or more column values in a table and the corresponding data pages that physically identify these values in the table. The function of the index is equivalent to the catalogue of the book, and the content you need can be found quickly according to the page number in the catalog. Indexes are special files (indexes on InnoDB data tables are part of the table space) that contain reference pointers to all records in the data table. Indexing is not omnipotent, indexing can speed up data retrieval operations, but it will slow down data modification operations. Each time the data record is modified, the index must be refreshed. To make up for this to some extent, many SQL commands have a DELAY_KEY_WRITE entry. The purpose of this option is to temporarily prevent MySQL from refreshing the index every time the command inserts a new record and modifies an existing one, and the refresh of the index will wait until all records have been inserted / modified. In situations where many new records need to be inserted into a data table, the role of the DELAY_KEY_WRITE option will be obvious. In addition, the index takes up a considerable amount of space on the hard disk. Therefore, you should index only the most frequently queried and sorted data columns. Note that if a data column contains a lot of duplicate content, indexing it does not have much practical effect. In theory, it is possible to create an index for each field in a data table, but MySQL limits the total number of indexes in the same data table to 16.

Index of InnoDB data table

On InnoDB data tables, indexes are much more important to InnoDB data tables. On InnoDB data tables, indexes not only play a role in searching for data records, but also serve as the basis for row-level locking mechanisms. "data row-level locking" means that individual records being processed are locked during the execution of a transaction operation, preventing other users from accessing them. This locking affects, but is not limited to, the SELECT, LOCKINSHAREMODE, SELECT, FORUPDATE commands, as well as the INSERT, UPDATE, and DELETE commands. For the sake of efficiency, row-level locking of InnoDB data tables actually occurs on their indexes, not on the table itself. Obviously, the data row-level locking mechanism works only if the relevant data table has an appropriate index for locking. 3. Restrictions on indexes if there is an unequal sign (WHERE coloum! =) in the query condition of the WHERE clause, MySQL will not be able to use the index. Similarly, if the function (WHERE DAY (column) =) is used in the query condition of the WHERE clause, MySQL will not be able to use the index. In JOIN operations (when you need to extract data from multiple data tables), MySQL can use indexes only if the primary and foreign keys have the same data type. If you use the comparison operators LIKE and REGEXP,MySQL in the query condition of the WHERE clause, you can use the index only if the first character of the search template is not a wildcard. For example, the index will be used if the query condition is LIKE 'abc%',MySQL; if the query condition is LIKE'% abc',MySQL, the index will not be used. In the ORDER BY operation, MySQL uses the index only if the sort condition is not a query condition expression. Even so, in queries involving multiple data tables, even if indexes are available, those indexes don't do much to speed up ORDER BY. If a data column contains many duplicate values, it will not work well even if it is indexed. For example, there is no need to create an index for a data column if it contains only values such as "0swap 1" or "YUnip N". 4. Classification of indexes (1) the most basic index type of a general index, there are no restrictions such as uniqueness. (2) unique index

A unique index is an index that does not allow any two rows to have the same index value.

When there are duplicate key values in existing data, most databases do not allow the newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the employee table, no two employees can have the same last name.

After establishing a UNIQUE index on a column, when a new record is inserted, the database management system automatically checks whether the new record takes a duplicate value on the column, and the UNIQE constraint in the CREATE TABLE command will implicitly create a UNIQUE index. (3) Primary key index

Referred to as the primary index, the value of a column or combination of columns (fields) in a database table uniquely identifies each row in the table. This column is called the primary key of the table.

Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a specific type of unique index. The index requires that each value in the primary key be unique. It also allows quick access to data when a primary key index is used in a query.

It is suggested that although a unique index helps to locate information, it is recommended to use a primary key index instead for best performance results. (4) the candidate index, like the primary index, requires the uniqueness of field values and determines the order in which records are processed. In databases and free tables, you can create multiple candidate indexes for each table. (5) A composite index an index on two or more columns is called a composite index. With additional columns in the index, you can narrow your search, but using an index with two columns is different from using two separate indexes. Duplicate values and null values are not allowed in data records. (6) full-text index

Full-text indexing is the key technology to realize big data search at present. We can use a variety of algorithms such as word segmentation technology to intelligently analyze the frequency and importance of keywords in the text, and then intelligently filter out the search results we want according to certain algorithm rules. Used for multiple column values, allowing duplicate and null values in data records

MySQL's own full-text indexing can only be used for database engine MyISAM data tables (7) Spatial indexing in MySQL 5.7.4 laboratory version, InnoDB storage engine added support for geometric data spatial indexing. Prior to this, InnoDB stored geometric data as BLOB (binary large objects) data, and could only create prefix indexes on spatial data, which was very inefficient when it came to spatial search, especially when complex geometric data were involved. In most cases, the only way to get results is to scan the table. In the new version of MySQL, InnoDB supports spatial index, which is realized by R-tree, which makes spatial search more efficient. 5. Use of index (1) create index create [unique | fulltext | spatial] index index name on table name (field)

Detailed explanation

Unique: unique index

Fulltext: full-text index. InnoDB does not support indexes of type FULLTEXT.

Spatial: spatial index

Case

Mysql > create index hehe_1 on hehe.user (user_name); / / create a new normal index for the user_name field in the user table

Mysql > create unique index hehe_2 on hehe.user (user_passwd); / / create a unique index for the user_passwd field in the user table

Mysql > create index hehe_3 on hehe.user (user_name,user_passwd)

/ / the user_name and user_passwd fields in the user table are combined into a composite index

(2) View the index show index from database name. Table name; (3) Delete index

Drop index index name on database name. Table name

What are the specific functions of the MySQL index mentioned above, do you think it is what you want? If you want to know more about it, you can continue to follow our industry information section.

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.