1, Index overview
1. Introduction
An index is a structure that sorts the values of one or more columns in a database table. Using an index can quickly access specific information in a database table.
For example, index: if a certain page in the database is regarded as a book, the index is like a book directory. You can quickly find the location of the specified content in the book through the directory. For database tables, you can quickly find the data in the table through the index.
2. Principle of index
Indexes are generally stored on disk (or in memory) in the form of files. The principle of stored indexes is roughly summarized as space for time. When the database does not add an index, the default is to conduct full search, that is, conduct global scanning, and query as many times as there are data, Then find the matching data and put it into the result set until the whole table is scanned. After the index is established, the key value of the index will be placed on an n-ary tree (BTree). Because the characteristic of B tree is that it is suitable for organizing dynamic lookup tables on disk and other direct storage devices. Each time you query by index, you will search directly on the tree according to the key value.
3. Advantages of index
① Indexed columns can ensure the uniqueness of rows and generate unique rowids
② Indexing can effectively shorten the retrieval time of data
③ Indexing can speed up the connection between tables
④ Adding an index to the fields used for sorting or grouping can speed up the grouping and sorting order
4. Disadvantages of index
① Creating and maintaining indexes requires time cost, which increases with the increase of data volume
② Creating and maintaining indexes requires space costs. Each index occupies the physical storage space of the database. The larger the amount of data, the larger the occupied space (the data table occupies the data space of the database)
③ It will reduce the efficiency of adding, deleting and modifying the table, because each addition, deletion and modification of the index requires dynamic maintenance, resulting in a longer time
2, Commands such as index classification, creation, modification and deletion
1. Basic index type
① Normal index (single column index)
② Composite index (composite index)
③ Unique index
④ Primary key index
⑤ Full text index
2. Index creation
1. Normal index (single column index)
Ordinary index (single column index): single column index is the most basic index, and it has no restrictions.
(1) Create index directly
CREATE INDEX index_name ON table_name(col_name);
(2) Adding indexes by modifying the table structure
ALTER TABLE table_name ADD INDEX index_name(col_name);
(3) Indexes are created when tables are created
CREATE TABLE `news` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` varchar(255) NOT NULL ,
`content` varchar(255) NULL ,
`time` varchar(20) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (title(255))
)(4) Delete index
DROP INDEX index_name ON table_name; perhaps alter table `Table name` drop index Index name;
2. Composite index (composite index)
A composite index is an index created on multiple fields. The composite index follows the "leftmost prefix" principle, that is, only when the first field of the composite index is used in the query criteria can the index be used. Therefore, the order of index columns in composite index is very important.
(1) Create a composite index
create index index_name on table_name(col_name1,col_name2,...);
(2) Adding indexes by modifying the table structure
alter table table_name add index index_name(col_name,col_name2,...);
3. Unique index
Unique index: a unique index is similar to an ordinary index. The main difference is that the value of the unique index limit column must be unique, but null values are allowed (only one null value is allowed).
If you add a unique index to a table that already has data:
- If the value of the column to which the index is added has two or more null values, the unique index cannot be created and will fail. (generally, when creating a table, you need to add not null to the field to set the unique index automatically.)
- If the value of the column to which the index is added has two or more null values, a unique index can still be created, but the data created later cannot be inserted with null values, and strictly speaking, this column is not unique because there are multiple null values.
Creating a unique index for multiple fields specifies that the combination of column values must be unique.
For example, if you create a unique index of orderId field and productId field in the order table, the combined values of these two columns must be unique!
"Null and NULL"Concept of:
1: Null values do not take up space .
2: MySQL Medium NULL It actually takes up space.
Length verification: note that there is no space between null values.
> select length(''),length(null),length(' ');
+------------+--------------+-------------+
| length('') | length(null) | length(' ') |
+------------+--------------+-------------+
| 0 | NULL | 1 |
+------------+--------------+-------------+
(1) Create unique index
# Create a single index CREATE UNIQUE INDEX index_name ON table_name(col_name); # Create multiple indexes CREATE UNIQUE INDEX index_name on table_name(col_name,...);
(2) Modify table structure
# single ALTER TABLE table_name ADD UNIQUE index index_name(col_name); # Multiple ALTER TABLE table_name ADD UNIQUE index index_name(col_name,...);
(3) Specify the index directly when creating the table
CREATE TABLE `news` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` varchar(255) NOT NULL ,
`content` varchar(255) NULL ,
`time` varchar(20) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
UNIQUE index_name_unique(title)
)4. Primary key index
Primary key index is a special unique index. A table can only have one primary key, and no null value is allowed. Generally, the primary key index is created when creating a table:
(1) Primary key index (added when creating a table)
CREATE TABLE `news` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` varchar(255) NOT NULL ,
`content` varchar(255) NULL ,
`time` varchar(20) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
(2) Primary key index (added after table creation)
CREATE TABLE `order` (
`orderId` varchar(36) NOT NULL,
`productId` varchar(36) NOT NULL ,
`time` varchar(20) NULL DEFAULT NULL
)
alter table `order` add primary key(`orderId`);5. Full text index
In general, fuzzy queries are queried through like. However, for massive data, this is not a good way. Indexes can be used in like "value%", but for like "% value%", full table query is performed. There is no performance problem in tables with small amount of data. However, for massive data, full table scanning is a terrible thing, so the performance of like fuzzy matching is very poor.
In this case, we need to consider using full-text search for optimization. Full text search is a FULLTEXT index in MySQL. FULLTEXT index supports InnoDB after MySQL version 5.6, while the previous version only supports MyISAM table.
Full text index is mainly used to find keywords in text, rather than directly compare with the values in the index. Fulltext index is very different from other indexes. It is more like a search engine than a simple parameter matching of where statement. The fulltext index is used in conjunction with the match against operation, rather than the general where statement plus like. At present, only char, varchar and text columns can create full-text indexes.
Tips:
When there is a large amount of data, first put the data into a table without a global index, and then use CREATE index to create a fulltext index, which is much faster than first creating fulltext for a table and then writing the data.
(1) Create a table that is suitable for adding a full-text index
CREATE TABLE `news` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` varchar(255) NOT NULL ,
`content` text NOT NULL ,
`time` varchar(20) NULL DEFAULT NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
)(2) Modify table structure and add full-text index
ALTER TABLE table_name ADD FULLTEXT index_fulltext_content(col_name)
(3) Create index directly
CREATE FULLTEXT INDEX index_fulltext_content ON table_name(col_name)
Note: the default MySQL does not support Chinese full-text retrieval!
MySQL full-text search is only a temporary solution. For full-text search scenarios, a more professional approach is to use full-text search engines, such as ElasticSearch or Solr.
3. Query and deletion of indexes
#see: show indexes from `Table name`; #or show keys from `Table name`; #delete alter table `Table name` drop index Index name;