Advantages, disadvantages and rules of MySQL advanced learning index

Time:2022-1-14

1、 Advantages and disadvantages of index

Advantages: it can quickly retrieve and speed up grouping and sorting

Disadvantages: occupy storage space and reduce the modification operation of data table

2、 Classification of indexes

primary key That is, the primary index, according to PK_ Clolum (length) is used to build an index. Duplicate and null values are not allowed
unique index The value of the column used to build the index must be unique, and null values are allowed
General index There are no restrictions on indexes built with ordinary columns in the table
Full text index Index built with columns of large text objects
Full text index An index built with columns of large text objects;
Composite index An index built with a combination of multiple columns. Values in these columns are not allowed to have null values.

3、 Rules for using indexes

1. Suitable for indexing

  • The primary key automatically creates a unique index;
  • Index the columns that often appear in where or order by statements as query criteria;
  • Index the sorted column;
  • The fields associated with other tables in the query are indexed by foreign key relationships;
  • Tendency combination index under high concurrency;
  • The columns used for aggregation functions can be indexed, such as the column when Max (column_1) or count (column_1) is used_ 1 requires indexing.

2. Not suitable for indexing

  • Do not create an index for frequently added, deleted or modified columns;
  • A large number of duplicate columns are not indexed;
  • There are too few table records. Do not build an index.

3. Index failure

  • In the composite index, the value of a column cannot be null. If there is, this column is invalid for the composite index;
  • In the like operation, ‘% AAA%’ will not use the index, that is, the index will become invalid, but ‘AAA%’ can use the index;
  • Using expressions or functions on indexed columns will invalidate the index;
  • Use not equal to in query criteria, including < symbol, > symbol and= Will cause index invalidation;
  • Using is null or is not null in query criteria will lead to index invalidation;
  • If the string is not quoted, the index will be invalid;
  • Using or to connect multiple conditions in query conditions will lead to index invalidation, unless each condition linked by or is added with an index;
  • If the sorted field uses an index, the selected field is also an index field, otherwise the index will be invalid;
  • Try not to include multi column sorting. If necessary, it is best to build a composite index for this queue.

4、 SQL about indexes

1. Add indexes when creating tables

--Add indexes when creating tables
--Index keyword
--The myindex index has its own name
--(username (16)) to which field
CREATE TABLE mytable(
 ID INT NOT NULL,
 username VARCHAR(16) NOT NULL,
 INDEX myindex (username(16))
);

2. Add index after creating table

--Add index
--Name of myindex index (self defined)
--The name of the mytable table
CREATE INDEX myindex ON mytable(username(16));
perhaps
ALTER TABLE mytable ADD INDEX myindex(username);

3 view index

--The name of the mytable table
 show index FROM mytable;

4. Delete index

--Name of myindex index (self defined)
--The name of the mytable table
DROP INDEX myindex ON mytable;
perhaps
ALTER TABLE mytable DROP INDEX myindex;

summary

This is the end of this article about the advantages, disadvantages and rules of MySQL advanced learning index. For more information about MySQL advanced index, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!