MySQL millions of data quickly create indexes

Test data Native One table the users have 100 million records. There are no fields to add indexes before this table is created, so you need to add an index to the table at this point. But because of the large amount of data, the index may not be successful, think of a lot of ways to finally dig a hole success.

Start getting ready for work,

User table structure:

CREATE TABLE' users ' (' ID 'int( One) unsigned not NULLauto_increment, ' name 'varchar(255)CHARACTER SETUtf8mb4DEFAULT NULL, ' Add_time 'int( One)DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=MyISAM auto_increment=1000001 DEFAULTCHARSET=Latin1;

An error occurred while trying to export data using into outfile in Navicat:

Select *  from  into ' Users.txt ' //users.txt files in MySQL dat

Such as:

I checked the Internet. mysql set permissions, you can use the

Show variables like '%secure% '; see what is the current value of Secure-file-priv?

You can see that the Secure-file-priv parameter is used to limit the load data, SELECT ... OUTFILE, and Load_file () to which the specified directory is passed. When the value of Secure_file_priv is null, the limit mysqld is not allowed to import | Export when the value of Secure_file_priv is/tmp/, the import for restricted mysqld | Export can only occur in/tmp/directory when secure_file The _priv value does not have a specific value, which means that there is no restriction on MYSQLD import | export.

The exported data must be the specified path for this value to be exported, and by default it may be null (I am looking at null here) to represent the forbidden export, so you need to set

Since I am using the My.ini file under the Phpstudy,mysql installation path, set the path:

Note: The path name is double-quoted ""
Re-execute error after reboot still .....

This parameter is not set in mysql.cnf, which means that this parameter defaults to NULL so try to modify [mysqld] in mysql.cnf to join Secure_file_priv  =  The end result is as follows:

Restart MySQL again

You can see the users.txt generated in the sibling test database directory ...
Then execute:

truncate  Users

To create an index:

Create Index  on users (name);

Indexed by: BTREE, RTREE, HASH, Fulltext, SPATIAL, here no longer repeat their differences, there are many online about this piece of introduction

Import the file to the appropriate table users.

Load ' Users.txt '  into table users;

This method is used when the write is locked or when the external service is paused, and it is not possible to play it directly on the line.

MySQL millions of data quickly create indexes