Index type for MySQL

MySQL index is very important for the efficient operation of MySQL. Indexing can greatly improve the speed of MySQL retrieval.

For example, if a reasonably designed and indexed MySQL is a Lamborgini, then a human tricycle is a MySQL that does not design and use an index.

Using the catalog page (index) of a Chinese dictionary as an example, we can quickly find the words we need by the catalog (index) sorted first by phonetic, stroke, and side.

When you create an index, you need to ensure that it is a condition that is applied to the SQL query statement (typically as a condition of the WHERE clause).
Index files that take up disk space for indexing.
Indexes supported by each search engine are different, as follows:

MyISAM, InnoDB supports btree indexing
Memory supports btree and hash indexing

The advantages of indexing are as follows:

  • Speed up queries
  • Create a unique index to ensure the uniqueness of data in a data table
  • Implementing data integrity and accelerating table-to-table connections
  • Reduce time for grouping and sorting

There are also many disadvantages to increasing the index, mainly in the following areas:

  • Creating and maintaining indexes takes time, and as the amount of data increases, so does the time it takes.
  • Indexes need to take up disk space. In addition to data tables, each index also takes up a certain amount of physical space. If there are a large number of indexes, the index file may reach the maximum file size faster than the data file.
  • Indexes are also maintained dynamically when data in tables is added, deleted, and modified, which reduces the speed of data maintenance.

I. Classification of Indexes

1. Unique Index and General Index
Normal index: is the basic index type in MySQL, allowing duplicate and null values to be inserted in the column defining the index.
Unique index: The values of index columns must be unique, but null values are allowed.If it is a composite index, the combination of column values must be unique.
Primary key index: is a special unique index that does not allow null values.

2. Single-column and combined indexes
Single column index: that is, an index contains only a single column, and a table can have multiple single column indexes;
Composite index: An index created on a combination of fields in a table.Indexes are only used if left-hand fields of these fields are used in query conditions.The leftmost prefix set is followed when using a combined index.

3. Full Text Index (fulltext)
Full-text index type is FULLTEXT and supports full-text lookup on columns that define indexes, allowing them to
Insert duplicate and null values into the.Full-text indexes can be created on columns of type CHAR, VARCHAR, or TEXT.Prior to MySQL 5.7.xx, only the MyISAM storage engine supported full-text indexing.

4. Spatial Index
A spatial index is an index of a field of spatial data type. There are four types of spatial data in MySQL:
geometry, point, linstring, and polygon.MySQL extends using the SPATIAL keyword so that columns that can be used to create spatial indexes must be declared NOT NULL, similarly, spatial indexes can only be created in tables where the storage engine is MyISAM until MySQL 5.7.xx.
5. Rules for creating indexes

  • Creating indexes is not as good as more. If there are a large number of indexes in a table, it will not only take up disk space, but also affect
  • Performance of statements such as insert, delete, update, and so on.Because the index also adjusts and updates when the data in the table changes;
  • Tables with small amounts of data are best not to create an index, since less data may take longer to query than to traverse the index.
  • Avoid creating indexes on frequently updated data.Indexes should be created for fields that are frequently used for queries.
  • Columns with many different values that are often used in conditional expressions are indexed;
  • When uniqueness is a characteristic of some data itself, we create a uniqueness index.
  • Index columns that are frequently sorted or grouped, or create a composite index if there are more than one sorted column;

2. Creating indexes while creating tables
1. Create a normal index

mysql> create table book
    -> (
    -> bookid int,
    -> bookname varchar(255),
    -> authors varchar(255),
    -> info varchar(255),
    -> comment varchar(255),
    -> year_publication year,                  
    -> index(year_publication)              # Create year_publication column as index column
    -> ); 
mysql> show create table book\G              # View Index
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) DEFAULT NULL,
  `bookname` varchar(255) DEFAULT NULL,
  `authors` varchar(255) DEFAULT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) DEFAULT NULL,
  KEY `year_publication` (`year_publication`)             .
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

Use explain to determine if the index is being used:

#Add the "explain" keyword before the query command
mysql> explain select * from book where year_publication=1999\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication               # possible_keys: The index name used is indicated later, and if no name is defined, the field name is used as the index name
          key: year_publication
      key_len: 2
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

2. Unique Index
The primary reason for unique indexes is to reduce the execution time of query index column operations.Especially when comparing large data tables.Similar to normal indexes, the difference is that the values of index columns must be unique, but null values are allowed.If it is a composite index, the combination of column values must be unique

#Creating tables with unique indexes
mysql> create table t1(
    -> id int not null,
    -> name char(30),
    -> unique index Uniqidx(id)
    -> );
#See
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) DEFAULT NULL,
  UNIQUE KEY `Uniqidx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

3. Single column index
Single-column index: An index created on a field in a data table where multiple single-column indexes can be created

#Create a single-column index
mysql> create table t2(
    -> id int not null,
    -> name char(50) null,
    -> index singleidx(name)
    -> );
#See
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) DEFAULT NULL,
  KEY `singleidx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

4. Combined Index
Composite index: An index is created on multiple fields.Follow the leftmost prefix principle.Leftmost prefix index leftmost column to match rows

#Create Composite Index
mysql> create table t3(
    -> id int not null,
    -> name char(30) not null,
    -> age int not null,
    -> info varchar(255),
    -> index multiidx(id,name,age)
    -> );
#View Composite Index
mysql> show create table t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `multiidx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Note: Combinatorial indexes can function as several indexes, but they are not used to query any field arbitrarily.Instead, it follows the leftmost prefix: matching rows using the leftmost column set in the index.Such a set of columns is called the leftmost prefix.
5. Full-text Index
Full-text index: FULLTEXT, which can be used for full-text search, supports CHAR\VARCHAR and TEXT columns.Indexing is always done on the entire column, does not support local indexing, and is suitable for table creation with large data.

#Create a table with a full-text index
mysql> create table t4
    -> (
    -> id int not null,
    -> name char(30) not null,
    -> age int not null,
    -> info varchar(255),
    -> FULLTEXT INDEX FullIdx(info(100))
    -> );          
#If it is a pre-5.7 version, you need to add ENGINE=MyISAM when you create the table 
mysql> show create table t4\G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `FullIdx` (`info`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
#Full-text indexes are used differently from other indexes in the following ways (insert test data first)
mysql>create table test8 ( id int not null, title varchar(255), body varchar(255),fulltext index(body) );
mysql> insert into test8 values(1,'shujuku','In MySQL 8.0.17,  we made an observation in the well-known TPC-H benchmark for  one particular query');
mysql> insert into test8 values(2,'shujuku','WORDS OF WISDOM: Like they say in Asia, nobody should use a fork.  Tradition evven dictates to "chop" all your forks and "stick" to the origiinal. ');
#Test if an index is used and data can be searched
mysql> explain select * from test8 where match(body) against('MySQL')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test8
   partitions: NULL
         type: fulltext
possible_keys: body
          key: body
      key_len: 0
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Ft_hints: sorted
1 row in set, 1 warning (0.00 sec)

The data you see is as follows:

Note: In the above way of creating a full-text index, you can achieve a full-text index of English, with each word matched separated by spaces. If you want to achieve a full-text index of Chinese, you need to add "with parser ngram" to the table with a Chinese parser.
6. Spatial Index
Spatial index: Must be created in a table of MyISAM type, and the field of spatial type must be non-empty.

#Creating a spatial index
mysql> create table tb5(
    -> g geometry not null,
    -> spatial index spaidx(g)
    -> )engine=myisam;
#See
mysql> show create table tb5\G
*************************** 1. row ***************************
       Table: tb5
Create Table: CREATE TABLE `tb5` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spaidx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

3. Create an index on an existing table

1. Add Unique Index

mysql> alter table book add unique index uniqidx(bookid);                     # uniqidx is the index name and bookid is the column name

2. Add Single Column Index

mysql> alter table book add index bkidex(comment(50));

3. Add Full-Text Index

mysql> alter table t6 add fulltext index infofulidx(info);

4. Add Composite Index

mysql> alter table book add index abc(authors(20),info);

5. Add Spatial Index

mysql> alter table t5 add spatial index spatidx(g);

4. Create an index on an existing table

#Create a table for testing
mysql> CREATE TABLE book1 ( bookid INT NOT NULL, bookname VARCHAR(255) NOT NULL,
    -> authors VARCHAR(255) NOT NULL, info VARCHAR(255) NULL, comment VARCHAR(255) NULL,
    -> year_publication YEAR NOT NULL );

1. Create a normal index

Mysql> create index bknameidex on Book1 (bookname); Book1 indicates that bookname is the column name

2. Create a single-column index

mysql> create index bkcmtidex on book1(comment(50));

3. Create Composite Index

mysql> create index bkauthandinfoidex on book1(authors,info);

4. Create Full-Text Index

mysql> create fulltext index fullidx on book(info);

5. Create Unique Index

mysql> create unique index uniqidx on book1(bookid);

6. Creating a spatial index

mysql> create table t7 (g geometry not null);
mysql> create spatial index spaidx on t7(g);

5. Delete Index
When you delete a column from a table, it is also deleted from the index if the column you want to delete is part of the index.If all the columns that make up the index are deleted, the entire index is deleted.

1. See what indexes are on some tables

mysql> show index from  book\G

2. Delete index with alter table

mysql> alter table book drop index uniqidx;

Note: The unique index of the constraint field added to AUTO_INCREMENT cannot be deleted

3. Delete with drop index

mysql> drop index spaidx on t7;

Tags: MySQL SQL less

Posted on Thu, 07 May 2020 18:22:54 -0400 by dave007