Indexes

MySQL's official definition of Index is: Index is a data structure that helps MySQL efficiently obtain data. Extract the sentence trunk to get the essence of Index: Index is a data structure. For details, you can check this blog post (the original blog post has expired, and this blog post is reprinted) https://blog.csdn.net/weichi7549/article/details/88315157

Classification of indexes

• Primary key index (primary key)
  • Unique identifier. It cannot be repeated. There can only be one column as the primary key
• UNIQUE KEY
  • Avoid duplicate columns. Unique indexes can be repeated, and multiple columns can be identified as unique indexes
• General index (KEY/INDEX)
  • default
• Full text index (FULLTEXT)
  • It can only be used under a specific data engine (MyISAM)
  • Fast positioning data

Use of index:

1. Add an index to the field when it is created

2. After creation, increase the index

-- Show all indexes
SHOW INDEX FROM `student`
-- Add a full-text index
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname`(`studentname`) -- Index name(Field name)
-- explain analysis SQL Implementation
EXPLAIN SELECT * FROM `student` -- rows = 12
EXPLAIN SELECT * FROM `student` WHERE MATCH(`studentname`) AGAINST('king') -- rows = 1

One million data test index

We can test the effect of index on data processing through a large amount of data!

-- Test a million pieces of data
-- Create user table
CREATE TABLE `app_user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT 'User nickname',
`email` VARCHAR(50) NOT NULL COMMENT 'User mailbox',
`phone` VARCHAR(20) DEFAULT '' COMMENT 'cell-phone number',
`gender` TINYINT UNSIGNED DEFAULT '0' COMMENT 'Gender',
`password` VARCHAR(100) NOT NULL COMMENT 'password',
`age` TINYINT DEFAULT '0' COMMENT 'Age',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app User table'
TRUNCATE `app_user`

-- Custom function
DELIMITER $$ -- You must write (flag) before writing a function
CREATE FUNCTION mock_data() -- Create function
RETURNS INT -- Return value
DETERMINISTIC -- certainty
BEGIN
 DECLARE num INT DEFAULT 1000000;
 DECLARE i INT DEFAULT 0;
 WHILE i < num DO -- while loop
  -- Insert statement
  INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
  VALUES
  (CONCAT('user',i),
  '24736743@qq.com',
  CONCAT('18',FLOOR(RAND()*(999999999-100000000)+100000000)),
  FLOOR(RAND()*2),
  UUID(),
  FLOOR(RAND()*100));
  SET i = i + 1;
 END WHILE;
 RETURN i;
END $$
-- Execution function
SELECT mock_data();

After the above codes are executed, we can try to query "user 9999". It can be found that the query time takes a total of 1.098sec (there may be a certain time gap between computers with different configurations and multiple execution of query statements).

SELECT * FROM `app_user` WHERE `name`='User 9999' -- 1.098sec

Through the explain statement, you can also find that the query statement queries nearly 900000 pieces of data (the results may vary under different circumstances)~

EXPLAIN SELECT * FROM `app_user` WHERE `name`='User 9999'

This is only one million data. In the era of big data, the measurement unit of data may be calculated in hundreds of billions and trillions. If you use this method to query, it will increase a lot of useless time cost. Therefore, the usefulness of index is reflected here~

-- Create index
-- Syntax: create index Index name on Table name(Field name)
-- Naming conventions for Index Names: id_Table name_Field name
CREATE INDEX id_app_user_name ON `app_user`(`name`)

At this time, when we run the query statement and explain statement again, we can find that the query time is shortened to 0.01sec, and explain also shows that the query statement only queries 1 row of data~

After the above tests, we can draw a simple conclusion that setting the index can effectively improve the query efficiency of data! It may not feel obvious under small data, but under huge data, the gap becomes very obvious!

Principles of indexing

• The more indexes, the better
• Do not index process change data
• Tables with small amounts of data do not need to be indexed
• Indexes are usually added to fields commonly used for queries

Tips: the data structure of the index is Hash type index, and the default data structure of InnoDB is BTree~

User management and authority management

SQLyog visual management

SQL command management

First, let's specify the location of the user table: MySQL User, SQL command management essentially reads this table and performs addition, deletion, modification and query~

-- Create user create user user name identified by 'password'
CREATE USER Ayin IDENTIFIED BY '12345'
-- Modify current user password
SET PASSWORD = PASSWORD('123456')
-- Modify the specified user password
SET PASSWORD FOR Ayin = '123456'
-- Note: in MySQL8.0 In previous versions, you need to add a password before the password password(),that is
-- SET PASSWORD FOR Ayin = PASSWORD('123456')

-- rename
RENAME USER Ayin TO ayin

-- User authorization(all privileges: Full permissions)
-- *.* All libraries and tables(library.surface)
GRANT ALL PRIVILEGES ON *.* TO ayin
-- Note: Although we have given all permissions to ayin User, but the user still does not have permission root large
-- As you can see from the user management, ayin User's grand Permission is not checked, indicating that the user does not have permission to authorize other users
-- privileges: Unlock all permissions except those granted to other users

-- Query authority
SHOW GRANTS FOR ayin
-- GRANT USAGE ON *.* TO `ayin`@`%`
SHOW GRANTS FOR root@localhost
-- GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

-- Revoke permissions
REMOVE ALL PRIVILEGES ON *.* TO ayin
-- delete user
DROP USER ayin

Database backup

Role of database backup:

• Ensure that important data is not lost
• Facilitate data transfer

MySQL database backup method:

• Copy physical files directly

• Manual backup using visualization tools

• Using the command line (mysqldump)

# Export database
# mysqldump -h hostname - u username - p password database table name 1 table name 2... > Physical disk location / file name
C:\WINDOWS\system32>mysqldump -hlocalhost -uroot -p12345 school student >D:/a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
# Import database
# In the case of login, switch to the specified database
source D:/a.sql