Introduction to methods of viewing, creating and deleting indexes in MySQL-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

Introduction to methods of viewing, creating and deleting indexes in MySQL

黄舟

Dec 06, 2017 am 10:33 AM

mysql create

I believe that everyone will deal with mysql in development. This article mainly introduces the methods of viewing, creating and deleting indexes in MySQL, and analyzes MySQL in detail in the form of examples. The role of indexes, as well as related implementation skills for viewing, creating and deleting indexes!

The examples in this article describe the methods of viewing, creating and deleting indexes in MySQL. Share it with everyone for your reference. The details are as follows:

1. The index function

On the index column, in addition to the ordered search mentioned above, the database uses various rapid positioning technologies to greatly improve the

queryefficiency. Especially when the amount of data is very large and the query involves multiple tables, using indexes can often speed up the query thousands of times.

For example, there are three unindexed tables t1, t2, and t3, which contain only columns c1, c2, and c3 respectively. Each table contains 1000 rows of data, which refers to the value from 1 to 1000. Search The query for rows with equal values is as follows.

SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3

Copy after login

The result of this query should be 1000 rows, each row containing 3 equal values. To process this query without an index, you must look for all combinations of the 3 tables to get those rows that match the

WHERE clause. The number of possible combinations is 1000×1000×1000 (billions), so obviously the query will be very slow.

If each table is indexed, the query process can be greatly accelerated. Query processing using indexes is as follows.

(1) Select the first row from table t1 to view the data contained in this row.

(2) Use the index on table t2 to directly locate the row in t2 that matches the value of t1. Similarly, use the index on table t3 to directly locate the row in t3 that matches the value from t1.
(3) Scan the next row of table t1 and repeat the previous process until all rows in t1 are traversed.

In this case, a full scan is still performed on table t1, but index lookups on tables t2 and t3 can be performed to directly retrieve the rows in these tables, which is one million faster than without using indexes. times.

Using indexes, MySQL accelerates the
search for rows whose WHERE clause satisfies the condition, and when performing multi-table connection queries, it speeds up matching rows in other tables when performing the connection.

2. Create index

You can create an index when executing the CREATE TABLE statement, or you can use CREATE INDEX or ALTER TABLE alone to add an index to the table.

1. ALTER TABLE

ALTER TABLE is used to create a normal index, UNIQUE index or PRIMARY KEY index.

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

Copy after login

The table_name is the name of the table to be indexed, column_list indicates which columns to index, and when there are multiple columns, separate them with commas. The index name index_name is optional. By default, MySQL will assign a name based on the first index column. Additionally, ALTER TABLE allows multiple tables to be altered in a single statement, so multiple indexes can be created at the same time.

2. CREATE INDEX

CREATE INDEX can add ordinary indexes or UNIQUE indexes to the table.

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

Copy after login

table_name, index_name and column_list have the same meaning as in the ALTER TABLE statement, and the index name is not optional. In addition, you cannot use the CREATE INDEX statement to create a PRIMARY KEY index.

3. Index type

When creating an index, you can specify whether the index can contain duplicate values. If not included, the index should be created as a PRIMARY KEY or UNIQUE index. For a single-column unique index, this guarantees that the single column does not contain duplicate values. For multi-column unique indexes, it is guaranteed that the combination of multiple values is not repeated.

PRIMARY KEY index and UNIQUE index are very similar. In fact, a PRIMARY KEY index is just a UNIQUE index with the name PRIMARY. This means that a table can only contain one PRIMARY KEY, because it is impossible to have two indexes with the same name in a table.

The following SQL statement adds a PRIMARY KEY index on sid to the students table.

The code is as follows:

ALTER TABLE students ADD PRIMARY KEY (sid)

Copy after login

4. Delete the index

You can use the ALTER TABLE or DROP INDEX statement to delete the index. Similar to the CREATE INDEX statement, DROP INDEX can be processed as a statement inside ALTER TABLE. The syntax is as follows.

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

Copy after login

Among them, the first two statements are equivalent, delete the index index_name in table_name.

The third statement is only used when deleting the PRIMARY KEY index, because a table can only have one PRIMARY KEY index, so there is no need to specify the index name. If no PRIMARY KEY index is created, but the table has one or more UNIQUE indexes, MySQL drops the first UNIQUE index.
If a column is deleted from the table, the index will be affected. For a multi-column index, if one of the columns is deleted, the column will also be deleted from the index. If you delete all the columns that make up the index, the entire index will be deleted.

5． View index

mysql> show index from tblname;
mysql> show keys from tblname;

Copy after login

· Table
表的名称。
· Non_unique
如果索引不能包括重复词，则为0。如果可以，则为1。
· Key_name
索引的名称。
· Seq_in_index
索引中的列序列号，从1开始。
· Column_name
列名称。
· Collation
列以什么方式存储在索引中。在MySQL中，有值‘A'（升序）或NULL（无分类）。
· Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
· Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
· Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。
· Null
如果列含有NULL，则含有YES。如果没有，则该列含有NO。
· Index_type
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
· Comment

总结：

通过本文的详细学习，相信有很多小伙伴对MySQL实现查看与创建以及删除索引的方法有了进一步的了解，希望对你有所帮助！

相关推荐：

MySQL如何创建和删除索引？

MySQL创建索引、重建索引、查询索引、删除索引

mysql建立索引删除索引很慢的解决

The above is the detailed content of Introduction to methods of viewing, creating and deleting indexes in MySQL. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Assassin's Creed Shadows: Seashell Riddle Solution

1 months ago By DDD

What's New in Windows 11 KB5054979 & How to Fix Update Issues

3 weeks ago By DDD

Where to find the Crane Control Keycard in Atomfall

1 months ago By DDD

How to fix KB5055523 fails to install in Windows 11?

2 weeks ago By DDD

InZoi: How To Apply To School And University

3 weeks ago By DDD

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Where is the login entrance for gmail email?

7749

Java Tutorial

1643

CakePHP Tutorial

1397

Laravel Tutorial

1293

PHP Tutorial

1234

Related knowledge

MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

How to connect to the database of apache Apr 13, 2025 pm 01:03 PM

Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.

Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

How to start mysql by docker Apr 15, 2025 pm 12:09 PM

The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database

MySQL's Role: Databases in Web Applications Apr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

Laravel Introduction Example Apr 18, 2025 pm 12:45 PM

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

How to install mysql in centos7 Apr 14, 2025 pm 08:30 PM

The key to installing MySQL elegantly is to add the official MySQL repository. The specific steps are as follows: Download the MySQL official GPG key to prevent phishing attacks. Add MySQL repository file: rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm Update yum repository cache: yum update installation MySQL: yum install mysql-server startup MySQL service: systemctl start mysqld set up booting

See all articles