MySQL experience 5-1 -- INDEX _ MySQL

MySQL 5-1 -- index bitsCN.com 1. MySQL has multiple rows that access tables. The most commonly used is sequential access and index access. Sequential access refers to browsing a table in one row, that is, full table scan. Disadvantage: it is very time-consuming and inefficient. Index access: read only the rows that show the required features, including at least one index, similar to the directory in the book. Index: a list that contains the value of the index field and the corresponding page number. The index keywords are sorted. You can use indexes to provide query speed. for each index, MySQL internally stores a pointer to the location of the actual record in a data file. Index advantages: provides query speed. Disadvantage: it occupies disk space and reduces the write speed. (Insert, update, and delete) when to create a key index: If a table mainly provides queries, index the fields that are frequently queried. Index disadvantages: 1) first, indexes are stored as files, and index files occupy disk space. If there are a large number of indexes, the index file may reach the maximum file size faster than the data file. 2) when updating the data on the index column in the table, the index also needs to be updated, which may require re-organizing an index. if there are many indexes in the table, this is a waste of time. That is to say, this reduces the efficiency of adding, deleting, modifying, and other write operations. The more indexes the table has, the longer the table will be updated. However, these drawbacks do not affect the application of indexes, because the benefits brought by indexes have basically covered up their defects. when there are many rows of data in a table, indexes are usually indispensable. Index creation in databases has the following benefits: 1 ). fast data reading; 2 ). ensure the uniqueness of data records; 3 ). implement the integrity of the reference between the table and the table; 4) when using the group by and order by statements for data retrieval, using the index can reduce the time for sorting and grouping. 2. index classification currently, most MySQL indexes are stored in B-tree mode. The B-tree method is used to construct a tree that contains multiple nodes. The top node constitutes the index start point, called the root. Each node contains several Index column values. each value in the node points to another node or to a row in the table. the values in a node must be sorted in an orderly manner. A node pointing to a row is called a leaf page. The leaf page itself is also interconnected. a leaf page has a pointer pointing to the next group. In this way, each row in the table will have a corresponding value in the index. When querying, you can directly find the row based on the index value. The nodes in the index are stored in files, so the index also occupies physical space. MySQL stores the indexes of a table in the same index file. If you update a table value or add or delete a row to the table, MySQL automatically updates the index. Therefore, the index tree is always consistent with the table content. An index is an object. drop is used to delete objects, and delete is used to delete records. 3. the main index types stored in the form of BTREE are described as follows: 1). a common index is the most basic index type and has no restrictions such as uniqueness. The keyword used to create a common INDEX is INDEX. Create index name on table name (field name ,...); Example: create indexindex_name on xs (name); 2 ). the unique index is basically the same as the previous normal index, but there is a difference: all values of the index column can only appear once, that is, they must be unique. The keyword used to create a UNIQUE index is UNIQUE. You can create multiple indexes in a table, and the index values cannot be repeated. a null value of create unique index name on table name (field name,...) is allowed ,...) 3). The primary key (primary key) is a unique index, which must be specified as "primary key ". A primary key is generally specified when a table is created. you can also modify the table to add a primary key. However, each table can only have one primary key. Only one primary key can be created for a table. the primary key field cannot contain duplicate values and cannot be empty. 4). Full-text indexing (fulltext) MySQL supports full-text retrieval and full-text indexing. In MySQL, the full-text index type is FULLTEXT. Full-TEXT indexes can only be created in VARCHAR or TEXT columns and can only be created in MyISAM tables. It can be created using the create table command or the alter table or create index command. For large-scale datasets, using the alter table (or create index) command to CREATE a full-text INDEX is faster than inserting a record into an empty TABLE with a full-text INDEX. In addition to B-tree indexes, MySQL also supports HASH indexes when the table type is memory or heap ). You do not need to create a tree structure when using hash indexes, but all values are saved in a list. this list points to the relevant pages and rows. When a specific row is obtained based on a value, the hash index is very fast. Create fulltext index name on table name (field name ,...); 4. you can use the create index statement to CREATE indexes on an existing table. you can create multiple indexes on one table. Syntax format: create [unique | fulltext | spatial] index index_name [using index_type] on tbl_name (index_col_name ,...) the format of index_col_name is col_name [(length)] [ASC | DESC] description: ● index_name: name of the index. the name of the index must be unique in a table. ● USING index_type: some storage engines allow you to specify the index type when creating an index. Index_type is the name of the index type supported by the storage engine. The index types supported by MySQL include BTREE and HASH. If the USING clause is not specified, MySQL automatically creates a BTREE index. ● Index_col_name: col_name indicates the column name of the created index. Length indicates that the index is created using the first length of the column. Using a part of a column to create an index can greatly reduce the index file and save disk space. In some cases, you can only index the column prefix. For example, the length of an index column has a maximum limit. Therefore, if the length of an index column exceeds this limit, you may need to use the prefix for indexing. BLOB or TEXT columns must be indexed with a prefix. The prefix is up to 255 bytes, but for MyISAM and InnoDB tables, the prefix is up to 1000 bytes. You can also specify whether the index is sorted in ascending or descending order (DESC). The default value is ASC. If a column in a SELECT statement is sorted in descending order, defining a descending index on the column can speed up processing. ● UNIQUE | FULLTEXT | SPATIAL: UNIQUE indicates that a UNIQUE index is created. FULLTEXT indicates that a full-text index is created. SPATIAL indicates a SPATIAL index and can be used to index columns of the geometric data type. This book does not discuss SPATIAL indexes. It can be seen that the CREATEINDEX statement cannot create a primary key. For example, create an ascending index xh­ _ XS based on the first five characters in the student ID column of the XS table. Create index XH_XS on xs (student ID (5) ASC); multiple columns can be included in the definition of an INDEX separated by commas, but they must belong to the same table. Such an index is called a composite index. For example, create a composite index XSKC _ IN the student ID column and course number column of the XS_KC table. Create index XSKC_IN ON XS_KC (student ID, course number); 5. use the alter table statement to modify a TABLE, which also includes adding an INDEX to the TABLE. Syntax format: ALTER [IGNORE] TABLEtbl_name add index [index name] [index type] (index column ,...) /* add index */| add [constraint [symbol] primary key [index type] (index column ,...) /* add a primary key */| add [CONSTRAINT [symbol] UNIQUE [index_name] [index_type] (index_col_name ,...) /* add a unique index */| add [FULLTEXT | SPATIAL] [index name] (index column ,...) /* add full-text index */| add [CONSTRAINT [symbol] foreign key [index name] (index column ,...) [reference_definition]/* Add a foreign key */| disable keys | enabl E keys description: ● index_type: the syntax format is USING {BTREE | HASH }. When an index is defined by default, the index of a PRIMARY key is called "PRIMARY". other indexes use the first column name of the index as the index name. If the names of multiple indexes start with the names of a column, an ordered number is placed after the column name. ● Constraint [symbol]: defines a name for the primary key, UNIQUE key, and foreign key. The following are also useful. This section describes naming integrity constraints. ● Disable keys | enable keys: used only in the MyISAM TABLE. use alter table... disable keys allows MySQL to stop updating non-unique indexes in the MyISAM TABLE when updating the TABLE, and then use alter table... enable keys re-create the lost index, which can greatly speed up the query. For example, create a non-unique index in the name column of the XS table. Alter table xs add index XS_XM using btree (name); for example, if the xs table is used (assuming that the primary key of the xs table is not fixed), create such an INDEX to accelerate the TABLE search speed: alter table xs add primary key (student ID), add index mark (date of birth, gender); in this example, both primary key and composite INDEX are included, mySQL can create multiple indexes at the same time. Remember: A column using the primary key must be a column with the not null attribute. If you want to view the index created in the table, you can use the show INDEX from index name statement, for example, show index from xs; 6. create an index when creating a table in the first two cases, the index is created after the table is created. You can also create indexes when creating tables. The create table statement can contain the index definition. Syntax format: Create [temporary] table [if not exists] table name [([field name],... | [index_definition])] [table_option] [select_statement]; where index_definition is an index item: [constraint [symbol] primary key [index type] (index column ,...) /* primary KEY */| {INDEX | KEY} [INDEX name] [INDEX type] (INDEX column ,...) /* INDEX */| [constraint [symbol] UNIQUE [INDEX] [INDEX name] [INDEX type] (INDEX column ,...) /* unique INDEX */| [FULLTEXT | SPATIAL] [INDEX] [INDEX type] (INDEX column ,...) /* Full-text index */| [constraint [symbol]] Foreign KEY [INDEX type] (INDEX column,...) [reference_definition]/* foreign KEY */description: KEY is usually a synonym of INDEX. When defining column options, you can also define a column as PRIMARYKEY. However, when a primary key is a multi-column index composed of multiple columns, you cannot define this primary key when defining columns, you must add a primary key (col_name,…) at the end of the statement ,...) Clause. For example, the statement for creating an XS_KC table is as follows. the XS_KC strap has a joint primary key of the student ID and course number, and creates an index on the score column. Create table XS_KC (student id char (6) not null, course number CHAR (3) not null, score TINYINT (1), credits TINYINT (1), primary key (student ID, course No.), index cj (Score); 7. 1 ). use the drop index statement to delete the INDEX syntax format: drop index index_name ON tbl_name this statement syntax is very simple, index_name is the name of the INDEX to be deleted, tb1_name is the INDEX of the table. For example, delete the XS_XH index on the XS table. Drop index XS_XH on xs; 2 ). use the alter table statement to delete the index syntax format: ALTER [ignore] TABLE tbl_name | drop primary key/* delete primary key */| drop index index_name/* delete INDEX */| drop foreign key fk_symbol/* delete foreign key */where, the drop index clause can delete various types of indexes. You do not need to provide the index name when using the drop primary key clause, because a table has only one primary key. For example, delete the primary key and mark indexes of the XS table. Alter table xs drop primary key, drop index mark; if a column is deleted from the TABLE, the INDEX may be affected. If the column to be deleted is an index component, the column is also deleted from the index. If all the columns that make up the index are deleted, the entire index is deleted. When performing join queries involving multiple tables, the index will be more valuable. 8. summary of three index creation methods: 1. the table has been created: 1) create index name on table name (field name ,..); 2) alter table name add index name (field name ,...); 2. create an index when creating a table. 1) specify the primary key: create table abc (id int primary key, name varchar (10), index ind_name (name); 2 ). the primary key is: create table abc1 (id int, name varchar (10), primary key (id), index ind_name (name ));
Author tianyazaiheruanbitsCN.com