Home Database Mysql Tutorial mysql5.1和mysql5.5创建索引速度对比

mysql5.1和mysql5.5创建索引速度对比

Jun 07, 2016 pm 05:40 PM
index

在MySQL5.1.X版本里,聚集索引创建和删除的过程:1、创建一个和原表结构一样的空表,然后创建聚集索引;2、拷贝原表的数据到新表,这时会对原表加一个排他锁,其

3、 复制完毕后删除掉原表,并把新表改名为原表。 

3、复制完毕后删除掉原表,并把新表改名为原表。

在MySQL5.5.X版本开始, 创建和删除非聚集索引不用复制整个表的内容,

只需更新索引页,和之前相比,速度会更快。但创建聚集索引(主键)时,网站空间,或者是外键时,香港空间,还是需要复制整个表的内容,因为聚集索引是把 primary key 以及 row data 保存在一起的,免备案空间,而 secondary index 则是单独存放,然后有个指针指向 primary key。

下面是创建非聚集索引的速度对比:

参见MySQL5.5手册:

 

本文出自 “贺春旸的技术专栏” 博客,请务必保留此出处

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

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

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

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to fix 100% disk usage on Windows 11 How to fix 100% disk usage on Windows 11 Apr 20, 2023 pm 12:58 PM

How to Fix 100% Disk Usage on Windows 11 The straightforward way to find the problematic application or service causing 100% disk usage is to use Task Manager. To open Task Manager, right-click on the Start menu and select Task Manager. Click the Disk column header to see what's using the most resources. From there, you'll have a good idea of ​​where to start. However, the problem may be more serious than simply closing an application or disabling a service. Read on to find more potential causes of problems and how to fix them. Disabling SuperfetchSuperfetch feature (also known as SysMain in Windows 11) helps reduce startup time by accessing prefetch files

How to hide files and folders and remove them from search in Windows 11? How to hide files and folders and remove them from search in Windows 11? Apr 26, 2023 pm 11:07 PM

<h2>How to Hide Files and Folders from Search on Windows 11</h2><p>The first thing we need to look at is customizing the location of Windows Search files. By skipping these specific locations, you should be able to see results faster while also hiding any files you want to protect. </p><p>If you want to exclude files and folders from searches on Windows 11, use the following steps: </p><ol&

Here are 6 ways to fix Windows 11 search bar not available. Here are 6 ways to fix Windows 11 search bar not available. May 08, 2023 pm 10:25 PM

If your search bar isn't working in Windows 11, there are a few quick ways to get it up and running in no time! Any Microsoft operating system can experience glitches from time to time, and the latest operating systems are not exempt from this rule. Additionally, as pointed out by user u/zebra_head1 on Reddit, the same error appears on Windows 11 with 22H2Build22621.1413. Users complained that the option to toggle the taskbar search box randomly disappeared. Therefore, you must be prepared for any situation. Why can't I type in the search bar on my computer? The inability to type on the computer can be attributed to different factors and processes. Here are some things you should be aware of: Ctfmon.

What are the Oracle index types? What are the Oracle index types? Nov 16, 2023 am 09:59 AM

Oracle index types include: 1. B-Tree index; 2. Bitmap index; 3. Function index; 4. Hash index; 5. Reverse key index; 6. Local index; 7. Global index; 8. Domain index ; 9. Bitmap connection index; 10. Composite index. Detailed introduction: 1. B-Tree index is a self-balancing tree data structure that can efficiently support concurrent operations. In Oracle database, B-Tree index is the most commonly used index type; 2. Bit Graph index is an index type based on bitmap algorithm and so on.

Windows 11 Outlook Search Not Working: 6 Fixes Windows 11 Outlook Search Not Working: 6 Fixes Apr 22, 2023 pm 09:46 PM

Run the Search and Indexing Troubleshooter in Outlook One of the more straightforward fixes you can start is to run the Search and Indexing Troubleshooter. To run the troubleshooter on Windows 11: Click the Start button or press the Windows key and select Settings from the menu. When Settings opens, select System > Troubleshooting > Additional Troubleshooting. Scroll down on the right side, find SearchandIndexing and click the Run button. Select Outlook Search to return no results and continue with the on-screen instructions. When you run it, the troubleshooter will automatically identify and fix the problem. After running the troubleshooter, open Outlook and see if the search is working properly. like

PHP returns the string from the start position to the end position of a string in another string PHP returns the string from the start position to the end position of a string in another string Mar 21, 2024 am 10:31 AM

This article will explain in detail how PHP returns the string from the start position to the end position of a string in another string. The editor thinks it is quite practical, so I share it with you as a reference. I hope you will finish reading this article. You can gain something from this article. Use the substr() function in PHP to extract substrings from a string. The substr() function can extract characters within a specified range from a string. The syntax is as follows: substr(string,start,length) where: string: the original string from which the substring is to be extracted. start: The index of the starting position of the substring (starting from 0). length (optional): The length of the substring. If not specified, then

How to solve the problem that the index exceeds the array limit How to solve the problem that the index exceeds the array limit Nov 15, 2023 pm 05:22 PM

The solutions are: 1. Check whether the index value is correct: first confirm whether your index value exceeds the length range of the array. The index of the array starts from 0, so the maximum index value should be the array length minus 1; 2. Check the loop boundary conditions: If you use the index for array access in a loop, make sure the loop boundary conditions are correct; 3. Initialize the array: Before using an array, make sure that the array has been initialized correctly; 4. Use exception handling: You can use the exception handling mechanism in the program to catch errors where the index exceeds the bounds of the array, and handle it accordingly.

How to improve the efficiency of data grouping and data aggregation in PHP and MySQL through indexes? How to improve the efficiency of data grouping and data aggregation in PHP and MySQL through indexes? Oct 15, 2023 am 11:39 AM

How to improve the efficiency of data grouping and data aggregation in PHP and MySQL through indexes? Introduction: PHP and MySQL are currently the most widely used programming languages ​​and database management systems, and are often used to build web applications and process large amounts of data. Data grouping and data aggregation are common operations when processing large amounts of data, but if indexes are not designed and used appropriately, these operations can become very inefficient. This article will introduce how to use indexes to improve the efficiency of data grouping and data aggregation in PHP and MySQL, and improve

See all articles