“This is the 11th day of my participation in the Gwen Challenge in November. See details: The Last Gwen Challenge in 2021”

The nature of indexes

== An Index is a data structure that helps MySQL obtain data efficiently ==. == An index is a data structure ==.

Indexes are not very useful for small data, but they are very useful for big data

Classification of indexes

  • PRIMARY KEY index
    • A unique identifier that the primary key cannot be repeated and only one column can be used 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, not written. You can also use the index or key keyword
  • FullText index
    • Only under a specific data engine, such as MYISAM
    • Rapid location data

Use of indexes

How indexes are created


Format 1: Index type Index Name Specifies the name of the column to be indexed
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)

Add an index to format 2
-- CREATE INDEX /FULLTEXT INDEX name ON table (field) ID_ Table name _ field name INDEX name
CREATE INDEX id_app_user_name ON`app_user`(`name`) Creating indexes can greatly speed up queries

Method 3 add an index to a table while building it
CREATE TABLE test1 (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    content TEXT NOT NULL.PRIMARY KEY(id),  Create primary key index
    FULLTEXT KEY content_index(content)A full-text index is created
) ENGINE=MYISAM DEFAULT CHARSET=utf8;
Copy the code

The way to drop an index

One - way
DROPThe INDEX INDEX nameONThe name of the tableTwo - way
ALTER TABLEThe name of the tableDROPThe INDEX INDEX nameCopy the code
Analyze SQL execution status
EXPLAIN SELECT * FROM student 
Copy the code

The index principles

  • More indexes are not always better
  • Do not index data that changes frequently
  • Tables with small data volumes do not need indexes
  • If a data column contains a lot of duplicate content, there is not much practical value in indexing it
  • Indexes are used for frequently queried fields
  • The primary key automatically creates a unique index
  • Query fields associated with other tables are indexed by foreign key relationships
  • A sorted field in a query that can be accessed through an index to speed sorting
  • Statistics or group fields in the query

The index test

- = = = = = = = = = = = = = = = = = = = = = = = = = index = = = = = = = = = = = = = = = = = = = = = = = = = =
-- Use of indexes
-- 1. Add index to column when create table
-- 2. Add the index
ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE
DROP INDEX; DROP INDEX


-- Displays all index information
SHOW INDEX FROM student

Format 1: Index type Index Name Specifies the name of the column to be indexed
ALTER TABLE `student` ADD FULLTEXT INDEX `studentname`(`studentname`)


Analyze SQL execution status
EXPLAIN SELECT * FROM student  -- Non-full-text index

EXPLAIN SELECT * FROM student WHERE MATCH(`studentname`) AGAINST('Joe')-- Full-text index
Note: The column specified in the match() function must be identical to the column specified in the full-text index, otherwise an error will be reported and the full-text index cannot be used, because the full-text index does not record which column the keyword came from. If you want to use a full-text index for a column, create a separate full-text index for that column. * /

SELECT  VERSION()

-- Full-text index test
CREATE TABLE test1 (
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    content TEXT NOT NULL.PRIMARY KEY(id),
    FULLTEXT KEY content_index(content)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

INSERT INTO test1 (content) VALUES ('a'), ('b'), ('c');
INSERT INTO test1 (content) VALUES ('aa'), ('bb'), ('cc');
INSERT INTO test1(content) VALUES ('aaa'), ('bbb'), ('ccc');
INSERT INTO test1 (content) VALUES ('aaaa'), ('bbbb'), ('cccc');


SELECT * FROM test1 WHERE MATCH(content) AGAINST('a');
SELECT * FROM test1 WHERE MATCH(content) AGAINST('aa');
SELECT * FROM test1 WHERE MATCH(content) AGAINST('aaa');
SELECT * FROM test1 WHERE MATCH(content) AGAINST('aaaa');

SHOW VARIABLES LIKE '%ft%';

- = = = = = = = = = = = = = = = = = = = = = index test = = = = = = = = = = = = = = = = = = = = = = = =
CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT ' ',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT ' ',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT ' ',
`age` TINYINT(4) DEFAULT NULL,
`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=utf8


-- Million data inserts
DROP FUNCTION IF EXISTS mock_data;
Before writing a function, you must write the flag $$
DELIMITER $$
CREATE FUNCTION mock_data()Create a function
RETURNS INT -- Returns a value
-- Pay attention to;
BEGIN     - the body of the function
DECLARE num INT DEFAULT 1000000;-- num as a cutoff number, defined as millions,
DECLARE i INT DEFAULT 0; Define the start variable
WHILE i < num DO
   Insert statement
   INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('users', i), CONCAT('100',i,'[email protected]'), CONCAT('18'.FLOOR(RAND()*(999999999- 100000000.)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
   SET i = i + 1;
END WHILE; -- End of loop
RETURN i;
END;
SELECT  mock_data();-- Start execution


SELECT * FROM `app_user` WHERE `name`=Users' 1988 '

Analyze SQL execution status
EXPLAIN SELECT * FROM `app_user` WHERE `name`=Users' 1988 '-- shows that 991770 rows of data were queried before 'user 1988' was found

Add an index to format 2
-- CREATE INDEX /FULLTEXT INDEX name ON table (field) ID_ Table name _ field name INDEX name
CREATE INDEX id_app_user_name ON`app_user`(`name`) Creating indexes can greatly speed up queries

Analyze SQL execution status
EXPLAIN SELECT * FROM `app_user` WHERE `name`=Users' 1988 'Mysql > query user 1988; mysql > query user 1988
Copy the code