MySql index design

Database index is a sorted data structure in the database management system to assist in quickly querying and updating data in database tables. Index implementation usually uses B-tree and its variant B+-tree.

In addition to data, the database system also maintains data structures that satisfy specific search algorithms. These data structures reference (point to) the data in some way, so that advanced search algorithms can be implemented on these data structures. This data structure is an index.

There are costs to be paid for setting indexes on tables: First, the storage space of the database is increased, and second, it takes more time to insert and modify data (because the index will also change accordingly).

The figure shows a possible indexing method. On the left is a data table with a total of two columns and seven records. The leftmost one is the physical address of the data record (note that logically adjacent records are not necessarily physically adjacent on the disk). In order to speed up the search of Col2, you can maintain a binary search tree as shown on the right. Each node contains the index key value and a pointer to the physical address of the corresponding data record. In this way, you can use binary search in O(log2n) The corresponding data is obtained within the complexity.

Creating indexes can greatly improve system performance.

First, by creating a unique index, the uniqueness of each row of data in the database table can be guaranteed.

Second, it can greatly speed up data retrieval, which is also the main reason for creating an index.

Third, it can speed up the connection between tables, which is particularly meaningful in achieving referential integrity of data.

Fourth, when using grouping and sorting clauses for data retrieval, the time for grouping and sorting in queries can also be significantly reduced.

5. By using indexes, you can use optimization hiders during the query process to improve system performance.

#There are so many advantages to adding an index, why not create an index for every column in the table? Because, adding indexes also has many disadvantages.

# First, creating and maintaining indexes takes time, and this time increases as the amount of data increases.

Second, the index needs to occupy physical space. In addition to the data space occupied by the data table, each index also occupies a certain amount of physical space. If a clustered index is to be established, the space required will be larger.

Third, when adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the data maintenance speed.

Indexes are built on certain columns in the database table. When creating an index, you should consider which columns can be indexed on and which columns cannot be indexed on.

Generally speaking, indexes should be created on these columns:

1. On columns that are often searched, the search speed can be accelerated;

2. As On the primary key column, enforce the uniqueness of the column and the arrangement structure of the data in the organization table;

3. On the columns that are often used in connections, these columns are mainly foreign keys, which can speed up the connection. ;

4. Create an index on columns that often need to be searched based on range, because the index has been sorted, and its specified range is continuous;

5. Create an index on columns that often need to be sorted Create an index, because the index has been sorted, so the query can use the sorting of the index to speed up the sorting query time;

6. Create an index on the columns that are often used in the WHERE clause to speed up the judgment of conditions.

Similarly, indexes should not be created for some columns. Generally speaking, these columns that should not be indexed have the following characteristics:

1. Indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, indexing or not indexing does not improve query speed. On the contrary, due to the addition of indexes, the maintenance speed of the system is reduced and the space requirements are increased.

Second, indexes should not be added to columns with few data values. This is because, since these columns have very few values, such as the gender column of the personnel table, in the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the data that needs to be searched in the table. The proportion of rows is huge. Increasing the index does not significantly speed up retrieval.

Three, indexes should not be added to columns defined as text, image and bit data types. This is because the data volume of these columns is either quite large or has very few values.

Fourth, when the modification performance is far greater than the retrieval performance, the index should not be created. This is because modification performance and retrieval performance are contradictory to each other. When adding indexes, retrieval performance will be improved, but modification performance will be reduced. When reducing indexes, modification performance will increase and retrieval performance will decrease. Therefore, when modification performance is much greater than retrieval performance, indexes should not be created.

According to the functions of the database, three types of indexes can be created in the database designer: unique index, primary key index and clustered index.

Unique Index

A unique index is one that does not allow any two rows to have the same index value. Most databases do not allow newly created unique indexes to be saved with the table when there are duplicate key values in the existing data. The database may also prevent adding new data that would 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.

Primary Key Index

Database tables often have a column or combination of columns whose value 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 each value in the primary key to be unique. It also allows fast access to data when a primary key index is used in queries.

Clustered Index

In a clustered index, the physical order of the rows in the table is the same as the logical (index) order of the key values. A table can contain only one clustered index. If an index is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered indexes generally provide faster data access than nonclustered indexes.

The above is the content of MySql index design. For more related content, please pay attention to the PHP Chinese website (m.sbmmt.com)!