MySQL database index Overview

MySQL database index Overview
I. Meanings and features of indexes are independent and stored on disks.Database StructureThey includeReference pointer of all records in the data table. The index is used to quickly find rows with a special value in one or more columns. The MySQL column type can be indexed.Using indexes for related columns increases the query speed.. Indexes are implemented in the storage engine. Therefore, the indexes of each storage engine are not necessarily the same, and each storage engine does not necessarily support all index types. Define the maximum number of indexes and the maximum index length for each table based on the storage engine. All storage services support at least 16 indexes for each table, with a total index length of at least 256 bytes. Most storage engines have higher limits. There are two storage types of indexes in MySQL: BTREE and HASH, which are related to the storage engine of the Table: MyISAM and InnoDB Storage are diligent and support only BTREE indexes; the MEMORY/HEAP Storage engine supports HASH and BTREE indexes. Index has the following advantages: (1) You can create a unique index to ensure the uniqueness of each row of data in the database table. (2) It can greatly speed up data query. (3) Accelerate the connection between tables in terms of data reference integrity. (4) When you use grouping and sorting clauses to query data, you can also significantly reduce the time for grouping and sorting in the query. The main disadvantages of indexes are: (1) it takes time to create and maintain indexes, and the time consumed increases as the amount of data increases. (2) indexes occupy disk space. In addition to data tables, each index occupies a certain amount of physical space. If a large number of indexes exist, the index file may reach the maximum file size faster than the data file. (3) When adding, deleting, and modifying data in a table, the index must also be dynamically maintained, reducing the Data Maintenance speed. Ii. Index classification MySQL indexes can be divided into the following categories: 1,Common Index and unique indexA common index is a basic index type in MySQL. Duplicate and null values can be inserted in the columns that define the index. Unique index. The value of the index column must be unique. A null value is allowed for a single index. If it is a composite index, the combination of column values must be unique. A primary key index is a special unique index that does not allow null values. 2,Single Column index and combined indexA single index contains only one column. A table can have multiple single indexes. Composite index refers to the index created on the combination of multiple fields in the table. The index is used only when the left fields of these fields are used in the query conditions. The leftmost prefix set is followed when composite indexes are used. 3,Full-text indexThe full-text index type is FULLTEXT, which supports full-text search for the columns that define the index. duplicate values and null values can be inserted in these index columns. Full-TEXT indexes can be created on CHAR, VARCHAR, or TEXT columns. In MySQL, only the MyISAM storage engine supports full-text indexing. 4,Spatial IndexA spatial index is an index created for fields of the spatial data type. MySQL has four spatial data types: GEOMETRY, POINT, LINESTRING, and POLYGON. MySQL expands with the SPATIAL keyword and creates a SPATIAL index using syntaxes similar to regular indexes. The column that creates the spatial index must be declared as not null. The spatial index can only be created in a table where the storage engine is MyISAM. Iii. Design Principles of indexes unreasonable index design or missing indexes will cause obstacles to the performance of databases and applications. Efficient indexing is very important for achieving good performance. When designing indexes, we should consider some principles: (1) the more indexes, the better. A large number of indexes in a table not only occupy disk space, it also affects the performance of INSERT, DELETE, UPDATE and other statements, because the indexes are also adjusted and updated when the data in the table is changed. (2) Avoid making too many indexes on frequently updated tables, and minimize the number of columns in the indexes. You should create indexes for fields that are frequently used for queries. avoid adding unnecessary fields. (3) it is best not to use indexes for tables with a small amount of data. Because the data is small, the query time may be shorter than the time used to traverse the index, and the index may not produce optimization results. (4) Create an index for columns with different values that are frequently used in condition expressions. Do not create an index for columns with fewer values. For example, in the "gender" Field of the student table, there are only two different values: "male" and "female". Therefore, no index is required. If an index is created, the query efficiency is not improved, but the update speed is significantly lower. (5) When uniqueness is a feature of a data, specify a unique index. To use a unique index, you must ensure the data integrity of the defined columns to increase the query speed. (6) Create an index on the columns that are frequently sorted or grouped (that is, group by or order by operations). If there are multiple columns to be sorted, you can create a composite index on these columns. 4. CREATE an index 1. CREATE an index when creating a TABLE use create table to CREATE a TABLE. In addition to defining the data type of a column, you can also define primary key constraints, foreign key constraints, or unique constraints, regardless of the constraint, the defined constraint is equivalent to creating an index on the specified column.(1) create a common indexThe most basic index type has no restrictions such as uniqueness. Its function is to speed up data access. Eg: create table book (bookid int not null, bookname VARCHAR (255) not null, authors VARCHAR (255) not null, info VARCHAR (255), comment VARCHAR (255 ), year_publication year not null, INDEX (year_publication ));(2) create a unique indexThe primary reason for creating an index is to reduce the time required to query an index column, especially for a large data table. It is similar to the previous normal index. The difference is that the value of the index column must be unique, but null values are allowed. If it is a composite index, the column must be unique. Eg: create table t1 (id int not null, name CHAR (30) not null, unique index UniqIdx (id ));(3) create a single column IndexA single-column index is an index created on a field in a data table. A table can use multiple single-column indexes. In the above two examples, the index is created as a single column. Eg: create tbale t2 (id int not null, name CHAR (50), INDEX SingleIdx (name (20) ---- Single Column INDEX named SingleIdx, INDEX length is 20 );(4) create a composite indexA composite index is used to create an index on multiple fields. Eg: create table t3 (id int not null, name CHAR (30) not null, age int not null, info VARCHAR (255), INDEX MultiIdx (id, name, age (100 )));A composite index can be used for several indexes. However, when used, the index is not available for any field, but follows the "leftmost prefix ": use the leftmost column set class in the index to match rows. Such a column set is called the leftmost prefix. For example, the Index consists of the id, name, and age3 fields. The index rows are stored in the order of id, name, and age. The index can search for the following field combinations: (id, name, age), (id, name) or id. If the column does not constitute the leftmost prefix of the index, MySQL cannot use a local index, such as (age) or (name, age) the combination cannot use index query.(5) create a full-text indexFULLTEXT full-text index can be used for full-text search. Only the MyISAM storage engine supports FULLTEXT indexes and only CHAR, VARCHAR, and TEXT columns. The index always applies to the entire column and does not support partial (prefix) indexes. Eg: create table t4 (id int not null, name CHAR (30) not null, age int not null, info VARCHAR (255), fulltext index FullTxtIdx (info )) ENGINE = MyISAM;(6) create a spatial indexThe spatial index must be created in the MyISAM table and the spatial field must be non-empty. Eg: create table t5 (g geometry not null, spatial index spatIdx (g) ENGINE = MyISAM; 2. CREATE an INDEX on an existing TABLE and CREATE an INDEX on an existing TABLE, you can use the alter table or create index statement. Use the alter table statement: alter table table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length],...) [ASC | DESC] use the create index statement: CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name [length ,...) [ASC | DESC] 3. Use the alter table statement to delete an INDEX: alter table table_name drop index index_name; Use the drop index statement to delete an INDEX: drop index index_name ON table_name; 4. view the index show index from table_name; show keys from table_name;