A component of a database

The name of the meaning
table table
Views view
Stored Procs The stored procedure
Functions Custom storage functions
Triggers The trigger
Events Time trigger

Query optimization

  1. Store a lot of data
- create a table
CREATE TABLE `user`(
	id INT,
	username VARCHAR(32),
	`password` VARCHAR(32),
	sex VARCHAR(6),
	email VARCHAR(50));-- 2. Create a stored procedure to insert records in batches
DELIMITER $$ Declare the end symbol of the stored procedure to be $$
CREATE PROCEDURE auto_insert()  
BEGIN
    DECLARE i INT DEFAULT 1; 
	START TRANSACTION; -- Start transaction
    WHILE(i< =4000000)DO
        INSERT INTO `user` VALUES(i,CONCAT('Jody',i),MD5(i),'male',CONCAT('Jody',i,'@gmail.com'));
        SET i=i+1;
    END WHILE;
	COMMIT; - submit
END$$ -- End of statement
DELIMITER ; -- Redeclare the semicolon as the closing symbol

-- View the stored procedure
SHOW CREATE PROCEDURE auto_insert;
Call the stored procedure
CALL auto_insert();
Copy the code
  1. Slow Query logs

Slow query logs record THE SQL statements that take a long time to perform operations

- Enable slow log query
set global slow_query_log=on;
- If the query duration of session Settings exceeds 2 seconds, the session is logged
set long_query_time=1;
-- View the slow query time configuration
show variables like '%slow_query_log%';
-- Check whether slow query logs are enabled
show variables like '%long_query_time%';
-- Specify id query
SELECT * FROM USER WHERE id = 3999999;
-- Specify name exact match
SELECT * FROM USER WHERE `username` = Jody '3888888';
-- Specify email fuzzy matching
SELECT * FROM USER WHERE email LIKE 'Jody 3777777%';
Copy the code

  1. Add indexes to optimize query speed

    Indexing is a sorting process

Index (BTree,B+Tree)

  • The system reads data from disk to memory in the basic unit of disk blocks.
  • Data in the same disk block is read all at once, rather than fetching what is needed.
  • INNODB storage engine has the concept of pages, the smallest unit of disk management. The default page size in the INNODB storage engine is 16KB.
  • The INNODB engine connects several addresses to disk blocks to achieve a page size of 16KB. If each piece of data on a page helps locate data records, this will reduce disk I/O times and improve query efficiency.

BTree

  • BTREE data structure:

    Each node contains not only a KEY value, but also data. The NUMBER of DISK I/OS for data query increases.Copy the code

  • B+Tree data structure

    Non-leaf nodes store only KEY values. All data is stored in leaf nodes. All leaf nodes have connection Pointers between them.Copy the code

  • B + Tree

    Improve query speed. The disk I/O count is reduced. The tree structure is small.Copy the code

Index creation principles

  • The legibility of data in a field cannot be less than 70%
  • Build indexes on columns that need to be searched frequently, which can greatly speed up the search
  • You can speed up joins by building indexes (foreign key fields) on columns that often need to be joined.
  • Build indexes on columns that often need to be sorted because the indexes are already sorted, so you can take advantage of index sorting to speed up sort queries.
  • Note: The establishment and maintenance of indexes are time-consuming. When creating a table, the database needs to maintain the indexes. When adding records, updating, and modifying indexes, the indexes also need to be updated, which indirectly affects the efficiency of the database

Advantages of indexes

  • Improve the efficiency of data retrieval and reduce the IO cost of database.
  • Sorting data through indexed columns reduces the cost of sorting data and CPU consumption.

The index disadvantage

  • The index is actually a table that holds primary keys and index fields and points to records in the entity table, so index columns also take up space
  • While indexes greatly speed up queries, they slow down the speed of updating tables, such as INSERTS, UPDATES, and DELETES. MySQL will update the index file every time it updates a column that has been added to the index.

Modify the table specified index

(1) Add a primary key, which means the index value must be unique and cannot be NULL

ALTER TABLE table_name ADD PRIMARY KEY; -- index name: primary

ALTER TABLE user ADD PRIMARY KEY(id);

Add unique index (NULL may occur multiple times)

ALTER TABLE table_name ADD UNIQUE; -- Index name: field name

ALTER TABLE USER ADD UNIQUE (username);

3. Add a common index. The index value can appear multiple times.

ALTER TABLE table_name ADD INDEX; -- Index name: field name

ALTER TABLE USER ADD INDEX (email);

Create indexes

Create a normal index
CREATEThe INDEX INDEX nameONTable name (column name);CREATE INDEX index_name ON `user`(`password`);
Create a unique index
CREATE UNIQUEThe INDEX INDEX nameONTable name (column name);CREATE UNIQUE INDEX index_name ON `user`(`password`);
Create a normal composite index
CREATEThe INDEX INDEX numberONTable name (column name1And the column name2);
CREATE INDEX index_name ON `user`(id,username);
Create a unique composite index
CREATE UNIQUEThe INDEX INDEX numberONTable name (column name1And the column name2);
CREATE UNIQUE INDEX index_name ON `user`(id,username);
Copy the code

Specify the index when creating the table

CREATE TABLE `user`(
      id INT,
      username VARCHAR(20),
      email VARCHAR(20),
	
      PRIMARY KEY(id),
      UNIQUE(email),
      INDEX(username)
);

Copy the code

Index invalidation

  • Select * from username WHERE username LIKE ‘jack123%’ WHERE username LIKE ‘jack123%’ WHERE username LIKE ‘jack123%

    WHERE username LIKE '%jack123%

  • Using OR queries where there is no index on one of the criteria can also reduce query efficiency

    SELECT * FROM USER WHERE id = 123456 OR sex = 'female';

  • Index invalid if evaluated on index column

    SELECT * FROM USER WHERE id + 1 = 1234567;

  • Use! =, <>, IS NOT NULL, NOT, etc

  • Composite indexes must pay attention to left-most matching (they must appear in order), otherwise they will fail

    • For example, add composite index (ID,NAME,email) to USER table. That’s like adding three indexes

      • id
      • id,NAME
      • id,NAME,email

    • If we make inquiries

      SELECT * FROM USER WHERE id = 1; -- - -- - effectively

      SELECT * FROM USER WHERE id = 1 AND NAME LIKE 'jack%'; -- - -- - effectively

      SELECT * FROM USER WHERE id = 1 AND NAME LIKE 'jack%' AND email LIKE '%jack'; -- - -- - effectively

      SELECT * FROM USER WHERE NAME LIKE 'jack%'; - invalid

      SELECT * FROM USER WHERE id = 1 OR email LIKE 'jack%'; - invalid

      SELECT * FROM USER WHERE NAME LIKE 'jack%' AND email LIKE 'jack%'; - invalid

    • Automatic promotion of types causes index invalidation: try to keep the types consistent (e.g. Int to varcher)