DCL & Function & Index

review

Multi table relation:
	One to many:
		Add multiple foreign key columns to a general table,The value is the primary key of a table
		To ensure the validity and integrity of data,Generally, after the table is under construction,Adding foreign key constraints by modifying table relationships
			alter table Multi table name add foreign key (Foreign key field name) references Main table(Primary key field name);
    Many to many:
    	By creating an intermediate table,Keep the primary keys of the other two tables as foreign keys in the intermediate table,It will split many to many into two one to many
    	To ensure the validity and integrity of data,You can add foreign key constraints to the two fields of the intermediate table
    one-on-one:
		When there are few fields, they can be combined into one
		Unique foreign key correspondence
		Primary key correspondence
 multi-table query:
	Inner connection(Intersection of two tables)
		Implicit
    		select Fields from a ,b where Connection conditions and query conditions
		Explicit
    		select Fields from a join b on Connection conditions where query criteria
		The results of implicit and explicit queries are the same,Just master one of them
	External connection
		Left outer
    		select Fields from a left join b on Connection conditions where query criteria
    		query a All data,Query according to connection conditions b,Show when you are satisfied
		Right outer
		Both, just remember the left side.Can be equivalent conversion

Subquery:
	One query needs to rely on the results of another query,Let's call another query a subquery
	The results of the subquery are:
		Query criteria are generally used for single line and single column
		Multiple rows and single columns are generally used as query criteria
		Multiple rows and columns are generally used as temporary tables
 Self correlation
	Special multi table query,The associated table is itself.
TCL:
	begin; Open transaction
	commit; Commit transaction
	rollback; Rollback transaction

	mysql The default transaction is automatically committed
	Transaction concept:
	Transaction characteristics:ACID
		Atomicity
		uniformity
		Isolation
		persistence
	Regardless of isolation, there will be dirty reads,Non repeatable reading,Unreal reading
	This can be solved by setting the isolation level of the database(Generally not set,Just use the default database)

Isolation level:
	read uncommitted The above three problems will backfire, and the safety is too poor
	read committed Avoid dirty reading   oracle default
	repeatable read Avoid dirty reading and non repeatable reading mysql default
	serialiazable All avoid inefficiency

Primary key constraint: used to determine the uniqueness of each record in a table

Foreign key constraint: used to indicate that a record in multiple tables belongs to a record in that table

Today's content introduction

DCL: used to add users and set permissions

Some common functions in mysql:

• Date function
• Mathematical function
• String function
• case when is equivalent to if else if multi branch judgment
• if

sql exercise

mysql advanced course: (for application or interview)

• Indexing: improving query performance
• Index structure

I. DCL (understanding)

Function: create or destroy users, authorize or revoke permissions for users

Create a user with root privileges:

• create user 'user name' @ 'domain name or ip' identified by 'password';

  CREATE USER 'tom'@'localhost' IDENTIFIED BY '1234';-- Use on this machine tom Cooperate with 1234 login
  CREATE USER 'tom'@'%' IDENTIFIED BY '1234';-- Use on any computer tom Cooperate with 1234 login
  

Use the root user to authorize other users:

• grant permission list (separated by ',') or all on which database Which table to 'username' @ 'hostname'
• For example: grant all on dayxx* TO ‘tom’@‘localhost’;

Which permissions do you want to view

• SHOW GRANTS FOR 'tom'@'localhost';

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-xmud5iwy-1620640286261) (03. DCL & Function & index. assets/image-20210116085520870.png)]

Revoke permissions:

• revoke permission list (separated by ',') or all on which database Which table from 'username' @ 'hostname'
• For example: revoke delete on dayxx* FROM ‘tom’@‘localhost’;

Modify user password:

• Do not log in and change password

  -- No login mysql,Directly in cmd Medium input 
  mysqladmin -uroot -p password New password
  -- Enter the original password according to the prompt
  

• Login and change user password

  -- Sign in mysql
  set password for 'user'@'Domain name or ip' = 'New password'
  

Delete user:

• drop user 'user name' @ 'domain name or ip'
• For example: DROP USER 'tom' @ 'localhost';

II. MySQL function

• In order to simplify the operation, mysql provides a large number of functions for programmers to use (for example, if you want to input the current time, you can call the now() function)
• Where functions can appear: in the values() of the insert statement, in the update statement, in the delete statement, in the query statement and its clauses.

0 prepare data

In yesterday's data

[the external chain picture transfer fails, and the source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-jsdfh6h5-1620640286271) (03. DCL & Function & index. assets/image-20210116090530685.png)]

1 date function

1.  Function: NOW() | CURDATE() | CURTIME()
	Description: get the current date, time, date and time of the system
	example: SELECT NOW();
	SELECT NOW() FROM DUAL;-- from dual Grammatical
	
2.  Function: YEAR(DATE) | MONTH(DATE) | DAY(DATE)
	Description: select year, month and day from date
	example: SELECT YEAR(NOW()); 
	
3.  Function: LAST_DAY(DATE)
	Description: returns the last day of the month
	example: SELECT LAST_DAY(NOW());
	
4.  Function: ADDDATE(DATE,n) | SUBDATE(DATE,n)
	Description: calculation start date DATE Plus (minus) n Date of day
	example: SELECT ADDDATE(NOW(),10);
	
5.  Function: QUARTER(DATE)
	Description: return date DATE What season is it, back to 1 to 4
	example: SELECT QUARTER(NOW());
	
6.  Function: DATEDIFF(d1,d2)
	Description: calculation date d1->d2 Days between
	example: SELECT DATEDIFF('2019-08-01','2019-07-01');
	
7.  Function: DATE_FORMAT(d,f)
	Description: by expression f Required display date d
	example: SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
	https://www.runoob.com/sql/func-date-format.html

practice

-- 1.Count the number of days each employee is employed

-- 2.Count the length of service of each employee

-- 3.Query employees recruited in 2011

-- 4.Statistics of employees who have been employed for more than 10 years

-- 1.Count the number of days each employee is employed
SELECT ename,joindate,DATEDIFF(NOW(),joindate) FROM emp;

-- 2.Count the length of service of each employee
SELECT ename,joindate,DATEDIFF(NOW(),joindate)/365 FROM emp;

-- 3.Query employees recruited in 2011
SELECT ename,joindate FROM emp WHERE joindate LIKE '2011-%';
SELECT ename,joindate FROM emp WHERE YEAR(joindate) = '2011';

-- 4.Statistics of employees who have been employed for more than 10 years
SELECT ename,joindate FROM emp WHERE DATEDIFF(NOW(),joindate)>=3650;

2 mathematical function

1.  Function: ABS(x)
	Description: Return x Absolute value of　　
	example: SELECT ABS(-1);
	
2.  Function: CEIL(x) | FLOOR(x)
	Description: round up (down)
	example: SELECT CEIL(1.5);
	
3.  Function: MOD(x,y)
	Description: Return x mod y Result of
	example: SELECT MOD(5,4);
	
4.  Function: RAND()
	Description: returns a random number from 0 to 1 [0,1)
	example: SELECT RAND();
	
5.  Function: ROUND(x)  round(x,y)  retain x of y One decimal place and rounded
	Description: rounding
	example: SELECT ROUND(1.23456);
	
6.  Function: TRUNCATE(x,y) Tailing method
	Description: returns a numeric value x Keep to the decimal point y Value of bit
	example: SELECT TRUNCATE(1.23456,3);

practice

-- 1.Count the length of service of each employee. If it exceeds half a year, it will be counted as one year

-- 2.Count the average salary of each department,Keep 2 decimal places

-- 3.Count the average salary of each department,Decimal rounded up

-- 4.Count the average salary of each department,Decimal down rounding

-- 1.Count the length of service of each employee. If it exceeds half a year, it will be counted as one year
SELECT ename,ROUND(DATEDIFF(NOW(),joindate)/365) FROM emp;

-- 2.Count the average salary of each department,Keep 2 decimal places
SELECT dept_id,TRUNCATE(AVG(salary),2) FROM emp GROUP BY dept_id;

-- 3.Count the average salary of each department,Decimal rounded up
SELECT dept_id,CEIL(AVG(salary)) FROM emp GROUP BY dept_id;

-- 4.Count the average salary of each department,Decimal down rounding
SELECT dept_id,FLOOR(AVG(salary)) FROM emp GROUP BY dept_id;

3 string function

1.  Function: CONCAT(s1,s2...sn)
	Description: String s1,s2 Combine multiple strings into one string
	stay mysql There is no limit to the number of parameters in,stay oracle There can only be two of them
	example: SELECT CONCAT("pass", "wisdom", "Broadcast", "passenger");
	
2.  Function: CHAR_LENGTH(str)
	Description: returns a string str Number of characters
	example: SELECT CHAR_LENGTH("Intelligence Podcast");
	
3.  Function: LENGTH(str)
	Description: returns a string s Number of bytes
	Chinese characters in utf8 Encoding the next Chinese character takes up 3 bytes;gbk Encoding a Chinese character takes up 2 bytes
	example: SELECT LENGTH("Intelligence Podcast") ;
	
4.  Function: UCASE(s) | UPPER(s)
	Description: converts a string to uppercase
	example: SELECT UCASE("itcast");
	
5.  Function: LCASE(s) | LOWER(s)
	Description: converts a string to lowercase
	example: SELECT LCASE("ITCAST");
	
6.  Function: LOCATE(s1,s)
	Description: from string s Get in s1 Start position of(Start with 1)
	example: SELECT LOCATE('he','itheima');
	
7.  Function: TRIM(str) | LTRIM(str) | RTRIM(str)
	Description: string to space
	example: SELECT TRIM("  Chuan Zhiren");
	
8.  Function: REPLACE(s,s1,s2)
	Description: String s2 Substitution string s String in s1
	example: SELECT REPLACE('abc','a','x');
	
9.  Function: SUBSTR(s, start, length)
	Description: from string s of start Position intercept length is length Substring of;length If you don't write, it is intercepted to the end
	example: SELECT SUBSTR("itcast", 2, 3);
	
10. Function: STRCMP(str1,str2)
	Description: compares string sizes,Returns 1 when left is greater than right, 0 when left is equal to right, and returns when left is less than right-1，
	example: SELECT STRCMP("a","b");

practice

-- 1.Change all employee nicknames to uppercase


-- 2.Display the last names of all employees and intercept them


-- 3.Displays the character length of all employee names


-- 4.Displays the byte length of all employee names


-- 5.Replace all employees surnamed Li with li


-- 6.Splice the names and nicknames of all employees together

-- 1.Change all employee nicknames to uppercase
SELECT nickname,UPPER(nickname) FROM emp;

-- 2.Display the last names of all employees and intercept them
SELECT ename,SUBSTR(ename,1,1) FROM emp;

-- 3.Displays the character length of all employee names
SELECT ename,CHAR_LENGTH(ename) FROM emp;

-- 4.Display length of all employee names in bytes
SELECT ename,LENGTH(ename) FROM emp;

-- 5.Replace all employees surnamed Li with li
SELECT ename,REPLACE(ename,'Lee','li') FROM emp;

-- 6.Splice the names and nicknames of all employees together
SELECT ename,nickname,CONCAT(ename,nickname) FROM emp;

4 Summary

date

• Year (date)
• now()
• date_ Format (date, format)
• DateDiff (large date, small date)

mathematics

• Round (number)
• Truncate (number, digits)
• ceil and floor
• rand()
  • SELECT * FROM emp ORDER BY RAND() LIMIT 1; – Get one at random

character string

• Substr (string, start position, intercept length)
• Replace (string, string to replace, new string)
• char_length()

III. advanced MySQL functions

1 case expression

In the process of querying the code, we may need to judge the query results.

-- grammar 
	SELECT 
		CASE [field,value] 
			WHEN Judgment condition 1 
				THEN Expected value 1
			WHEN Judgment condition 2 
				THEN Expected value 2
			ELSE The value obtained when none of the previous conditions are met 
		END
	FROM
		table_name;

practice

-- Query and sort the salary grade of each employee
-- The salary grade is displayed as 1 'Make money with great effort'
-- The salary grade is displayed as 2 'Well off life'
-- The salary grade is displayed as 3 'You can marry a daughter-in-law'
-- The salary grade is displayed as 4 'You can buy a car'
-- The salary grade is displayed as 5 'You can buy a house'
-- Salary grade is not displayed in the above list as  'vulgar tycoon'

SELECT ename,s.grade,
	CASE s.grade
		WHEN 1 THEN 'Make money with great effort'
		WHEN 2 THEN 'Well off life'
		WHEN 3 THEN 'You can marry a daughter-in-law'
		WHEN 4 THEN 'You can buy a car'
		WHEN 5 THEN 'You can buy a house'
		ELSE 'Trench'
	END 'desc'
FROM emp e
JOIN salarygrade s
	ON e.salary BETWEEN s.losalary AND s.hisalary
ORDER BY s.grade ASC;

2 if expression

-- grammar
SELECT IF(1 > 0,'true','false') from table_name;

practice

-- wages+Employees with bonuses greater than 20000 show that they have a beautiful wife at home, otherwise they show a single dog
SELECT ename,IF(salary+IFNULL(bonus,0)>20000,'There is a fairy wife at home','keep trying')FROM emp;

IV. MySQL comprehensive exercise

-- 1.Calculate employee's daily salary(By 30 days)，Keep two decimal places

-- 2.Calculate the annual salary of employees and sort it in descending order

-- 3.Find out the information of employees with less than 5000 bonus or without bonus

-- 4.Returns the employee's job name and the minimum wage for this job

-- 5.Return the information of employees who have worked for more than 10 years and joined in February

-- 6.Return the employees who joined Lin Chong in the same year

-- 7.Return the employee name (employee table) and department name (department table) whose salary is grade 2 (salary grade table)

-- 8.pay off,Add on the original basis: 2000 chairman, 1500 manager and 800 others

-- 1.Calculate employee's daily salary(By 30 days)，Keep two decimal places
SELECT ename,TRUNCATE(salary/30,2) FROM emp;

-- 2.Calculate the annual salary of employees and sort it in descending order
SELECT ename,salary*12 yearsalary FROM emp ORDER BY yearsalary DESC;

-- 3.Find out the information of employees with less than 5000 bonus or without bonus
SELECT ename,bonus FROM emp WHERE bonus < 5000 OR bonus IS NULL;
SELECT ename,bonus FROM emp WHERE IFNULL(bonus,0) < 5000;

-- 4.Returns the employee's job name and the minimum wage for this job
-- Mode 1:multi-table query
SELECT j.jname,MIN(salary)
FROM emp e
JOIN job j
	ON e.job_id = j.id
GROUP BY j.jname;

-- Mode 2:Subquery
-- a.First query the minimum salary of each job
SELECT job_id,MIN(salary) FROM emp GROUP BY job_id;
-- b.relation job Table query data
SELECT j.jname,tmp.minsalary
FROM job j
JOIN (SELECT job_id,MIN(salary) minsalary FROM emp GROUP BY job_id) tmp
	ON j.id = tmp.job_id;


-- 5.Return the information of employees who have worked for more than 10 years and joined in February
SELECT ename,joindate FROM emp WHERE DATEDIFF(NOW(),joindate)>3650 AND MONTH(joindate) = 2;

-- 6.Return the employees who joined Lin Chong in the same year
-- a.Query Lin Chong's year of employment first
SELECT YEAR(joindate) FROM emp WHERE ename = 'Lin Chong';-- 2011
-- b.Query other employees by year
SELECT * FROM emp WHERE YEAR(joindate) = 2011;
-- become
SELECT * FROM emp WHERE YEAR(joindate) = (SELECT YEAR(joindate) FROM emp WHERE ename = 'Lin Chong');


-- 7.Return the employee name (employee table) and department name (department table) whose salary is grade 2 (salary grade table)
SELECT ename,dname
FROM emp e
LEFT JOIN dept d
	ON e.dept_id = d.id
LEFT JOIN salarygrade s
	ON e.salary BETWEEN s.losalary AND s.hisalary
WHERE s.grade = 2;


-- 8.pay off,Add on the original basis: 2000 chairman, 1500 manager and 800 others

SELECT ename,salary,jname,
	CASE jname
		WHEN 'chairman' THEN salary+2000
		WHEN 'manager' THEN salary+1500
		ELSE salary+800
	END net salary
FROM emp e
LEFT JOIN job j
	ON e.job_id = j.id;

[understanding MySQL performance v]

1 Analysis - inefficient database query

Our enterprise development often focuses on the business requirements and the realization of functions, but the system online time is longer and longer, and the data increases with it, which will affect the overall performance of the database

Database table optimization

Hardware optimization: improve the hardware level of the server. After the software optimization, the expected effect has not been achieved, and then consider the hardware

Software Optimization:

• Indexing – today
• Put common data into non relational database (cache, working in memory) – years ago
• Sub database and sub table – mysql advanced
• Read write separation - mysql advanced

2. Check the execution efficiency of - sql statement

Let's insert tens of millions of data first

-- 0.Create database
create database day03_1;
use  day03_1;

-- 1. Preparation 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 that the end symbol of the stored procedure is $$
CREATE PROCEDURE auto_insert()
BEGIN
    DECLARE i INT DEFAULT 1;
	START TRANSACTION; -- Open transaction
    WHILE(i<=10000000)DO
        INSERT INTO `user` VALUES(i,CONCAT('jack',i),MD5(i),'male',CONCAT('jack',i,'@itcast.cn'));
        SET i=i+1;
    END WHILE;
	COMMIT; -- Submit
END$$ -- End of statement
DELIMITER ; -- Redeclare semicolon as closing symbol

-- 3. Viewing stored procedures
-- SHOW CREATE PROCEDURE auto_insert;

-- 4. Call stored procedure
CALL auto_insert();

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-fxyohk7n-1620640286280) (03. DCL & Function & index. assets/image-20200827113217406.png)]

Slow query log

-- View slow query related configurations
show variables like '%query%'

[the external chain picture transfer fails, and the source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-ourmtl7h-1620640286284) (03. DCL & Function & index. assets/image-20200827113714354.png)]

Enable slow query log

set global  slow_query_log = on;

Set slow query log time node (threshold)

-- Global settings
set global long_query_time=3;

-- Session settings
set long_query_time=3;

Slow query log file analysis

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-vcwmils0-1620640286286) (03. DCL & Function & index. assets/image-20200827114437219.png)]

Vi. MySQL index

1 what is an index

In real life, we often go to the library to look up books.

Now we put all the books together in disorder, so finding a book is like looking for a needle in a haystack, which is very inefficient.

If we sort by category and find the corresponding books according to the category, the efficiency will be very high. In fact, the sorting process is index.

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-rcqwcf14-1620640286287) (03. DCL & Function & index. assets/1566306728298.png)]

The establishment of MySQL index is very important for the efficient operation of MySQL. Index can greatly improve the retrieval speed of MySQL.

If MySQL with reasonable design and index is a Lamborghini, MySQL without design and index is a human tricycle.

[the external chain picture transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-hfng9m0c-1620640286288) (03. DCL & Function & index. assets/1566306864253.png)]

2 advantages and disadvantages of index

advantage

• Similar to the construction of bibliographic index in university library, it can improve the efficiency of data retrieval and reduce the IO cost of database.
• Sort the data through the index column to reduce the cost of data sorting and CPU consumption.

inferiority

• In fact, the index is also a table, which saves the primary key and index fields and points to the records of the entity table, so the index column also takes up space
• Although the index greatly improves the query speed, it will reduce the speed of updating the table, such as INSERT, UPDATE and DELETE. When updating the table, MySQL should not only save the data, but also save the index file. Every time the field with index column is updated, the index information after the key value changes caused by the UPDATE will be adjusted.

3 index classification and syntax

Single column index (add index to a column of fields) id

Combined index (add index to multi column field) id +name

classification

• Normal index: only accelerate query
• Unique index: accelerated query + unique column value (can be null)
• Primary key index: accelerated query + unique column value (can not be null) + there is only one in the table
• Combined (combined) index: multiple column values form an index. Note: the leftmost matching principle

Create index

Direct creation (common or unique understanding)

-- Create normal index
create index Index name on Table name(Listing);

-- Create unique index
create unique index  Index name on Table name(Listing);

-- Create common composite index
create index Index number on Table name(Column name 1,Column name 2);

-- Create unique composite index
create unique index  Index number on Table name(Column name 1,Column name 2);

For example:

create table i1(
	id int,
    name varchar(8),
    email varchar(8)
);

-- to name Add normal index
CREATE INDEX nameindex ON i1(NAME);

-- to email Add unique index
CREATE UNIQUE INDEX emailindex ON i1(email);

[the external chain picture transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-2ba26qbm-1620640286289) (03. DCL & Function & index. assets/image-20210116144013415.png)]

Specify (understand) when modifying the table

-- Add a primary key, which means that the index value must be unique and cannot be null NULL
alter table Table name add primary key(Column name 1,Column name 2)

-- Add unique index (except NULL Outside, NULL May occur multiple times)
alter table Table name add unique(Column name 1,Listing 2)

-- Add a common index, and the index value can appear multiple times.
alter table Table name add index(Column name 1,Column name 2)

For example:

create table i2(
	id int,
    name varchar(8),
    email varchar(8)
);
-- to id Add primary key index
ALTER TABLE i2 ADD PRIMARY KEY(id);

-- to name Add normal index
ALTER TABLE i2 ADD INDEX(NAME);

-- to email Add unique index
ALTER TABLE i2 ADD UNIQUE(email);

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-fxp112xf-1620640286291) (03. DCL & Function & index. assets/image-20210116144407438.png)]

Specify when creating tables (recommended)

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

Delete index

-- Delete directly
drop index Index name on Table name;

-- Delete master when modifying the table
alter table Table name drop index Index name;

-- Delete primary key
alter table Table name drop primary key;

Index creation principles

1. The recognition degree of data in the field shall not be less than 70%
2. Building indexes on columns that often need to be searched will greatly speed up the search
3. Building indexes on columns that often need to be connected can speed up the connection.
4. Build an index on the columns that often need to be sorted, because the index is already sorted. In this way, you can use the sorting of the index to speed up the sorting query.

Note: if the index is so good, do you want to build as many indexes as possible in the database table fields?

Definitely not.

1. Because the establishment and maintenance of the index are time-consuming, the index also needs to be updated when adding records, updating and deleting, which will indirectly affect the efficiency of the database
2. The index also needs to be saved, which takes up a lot of disk space

4 effect demonstration

Query without index

-- according to id 
select * from user where id = 1234567;
-- According to user name
select * from user where username = 'jack1234567';
-- Fuzzy query according to mailbox
select * from user where email like 'jack12345%';

When there is no index on my computer, it takes almost five seconds to query the data

Create index and re query

-- id Set as primary key
alter table user add primary key (id);

-- Set mailbox to unique
alter table user add unique(email);

-- username Set to normal
alter table user add index(username);

[the external chain picture transfer fails, and the source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-go0r4rsv-1620640286292) (03. DCL & Function & index. assets/image-20210116150649450.png)]

After adding the index, the above query operation can be performed again, and the time is almost negligible

5. Common index failures

• During fuzzy query, if "%" is added to the left of the index column, the index will become invalid

  • Where username likes' jack123% '– the index is valid
  • where username like '% jack123%' – index invalid

• Using or query, the query efficiency will also be reduced if there is no index on one of the conditions

  • select * from user where id = 123456 or sex = 'female';

• If the calculation is performed on the index column, the index becomes invalid

  • select * from user where id + 1 = 1234567;

• Use! =, < > is not null, not, etc. will also fail

• Composite index should pay attention to the leftmost matching principle, otherwise it will also fail

  • For example, a combined index (id,name,email) is added to the user table It is equivalent to adding three indexes

    alter table user add index(id,name,email);

    • id
    • id,name
    • id,name,email

  • If we inquire

    • select * from user where id = 1;-- Effective
    • select * from user where id = 1 and name like ‘jack%’;-- Effective
    • select * from user where id = 1 and name like ‘jack%’ and email like ‘jack%’;-- Effective
    • select * from user where name like ‘jack%’;-- invalid
    • select * from user where id = 1 and email like ‘jack%’;-- invalid
    • select * from user where name like ‘jack%’ and email like ‘jack%’;-- invalid

6 index data structure

introduce

We know that index is a data structure that helps MySQL to efficiently obtain and arrange data.

Index = sorted data structure

Why does the query efficiency improve a lot after using the index? Next, let's understand.

[the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-7obarhtk-1620640286293) (03. DCL & Function & index. assets/1566372154562.png)]

When there is no index, we execute an sql statement, which is global traversal of the table and disk addressing (note that logically adjacent records are not necessarily physically adjacent on the disk).

In order to speed up the search efficiency, a binary search tree shown on the right can be maintained. Each node contains an index key value and a pointer to the physical address of the corresponding data record, so that the binary search can be used to quickly obtain the corresponding data.

Index data structure

1. Binary tree
2. Red black tree (balanced binary tree)
3. Hash
4. B-Tree (multiple search tree)
5. B+Tree [MySQL usage]

https://www.cs.usfca.edu/~galles/visualization/

1. Binary tree

   • Each node can have up to two child nodes
   • The data of the left node is smaller than that of the root node, and the data of the right node is larger than that of the root node
   • When the data is inserted in sequence, the binary tree will become a linked list
   • Even if it does not become a linked list, how deep does 10 million pieces of data need to be in the binary tree (at least 24 layers)

2. Red black tree (balanced binary tree)

   • It is also a binary tree, but it achieves a balance through left rotation and right rotation and will not generate a linked list

3. Hash

   • To save or query, hash the data first. The value obtained after the operation is the value of the hash bucket into which the data is to be put.
   • When using this data to query the results, it is also more efficient
   • However, if you do range query, you need to query one value by one, which is not as fast as the linked list query of leaf nodes of b + tree
   • jdk1. hashmap bottom layer of 8: the linked list storage used when storing in each bucket by default. If the length of the linked list is > 8, the linked list will become a tree When data is removed, if the number of data in the tree is less than 6, it will become a linked list

4. B-Tree (multiple search tree)

   • A node can store more data, and a node can have more child nodes

   • The data saved on each node includes data content, index value and pointer value

   • The default size of each index node in mysql is 16k. For example, the index value is fixed (6Byte), the pointer value is fixed (8Byte), and the size of the data content is (1010B). A data adds up to exactly 1kb. A node can store up to 16 data, and a node can have 17 child points. It only needs 6 layers to store 1kw data

     [the external chain picture transfer fails, and the source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-6pwhu8hm-1620640286294) (03. DCL & Function & index. Assets / image-2021011602391231. PNG)]

5. B+Tree [MySQL usage]

   • For the improved version of B Tree, only index values and pointer values are saved on each non leaf node, and only leaf nodes save data; All leaf nodes are linked using a linked list
   • The default size of each index node in mysql is 16k. 1170 elements can be stored on one node. Only three layers are needed to store 1kw data And when doing range query, you can get it directly through the form of linked list on the leaf node
   • mysql also preloads the index value of the root node into memory for faster query If it was originally 3 layers, now it only needs 2 io to find the specified data

More data is stored on a node (because non leaf nodes do not store the data of the current node), the number of child nodes of each node is more, and the tree is more flat. The linked list structure used by leaves is convenient for range query

B+Tree in MySQL

-- see mysql Inode size
show global status like 'innodb_page_size';

Schematic diagram of B+Tree index structure in MySQL:

[the external chain picture transfer fails. The source station may have anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-8a6ggelo-1620640286295) (03. DCL & Function & index. assets/1566372947007.png)]

7 database storage engine

Different MySQL storage engines have different ways to save index files. There are two common storage engines, MyISAM and InnoDB.

myisam:msyql5. Transactions are not supported for use before version 5

[the transfer of external chain pictures fails, and the source station may have anti-theft chain mechanism. It is recommended to save the pictures and upload them directly (img-sen7qj5r-1620640286296) (03. DCL & Function & index. assets/image-20210116164347405.png)]

innodb:mysql5. Default use after 5 (inclusive), supporting transactions

[the external link image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-4vnlimog-1620640286297) (03. DCL & Function & index. assets/image-20210116164431523.png)]

• Clustered index

  • Index used in innodb
  • There can only be one clustered index in a table
    • Encyclopedia says: logical address and physical address are consistent
    • Indexes and data stored in leaf nodes in b+tree
    • A clustered index is generally a primary key index; If there is no primary key, find the first unique index in the table; If there is no unique index, mysql will add a column by default, which we can't see
    • Clustered indexes are generally primary key indexes, and the leaf nodes of primary key indexes store data Other indexes in the table no longer store data. The primary key stored in the leaf node of other indexes If you are searching for other indexes, find the primary key first and find the specific value through the primary key index

  [the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-lqoojb2f-1620640286298) (03. DCL & Function & index. assets/1574059330054.png)]

  [the external chain picture transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-kpougwyo-1620640286299) (03. DCL & Function & index. assets/1568705854433.png)]

• Nonclustered index

  • Index used in myisam
  • Index files and data files are separate
  • All indexes are nonclustered, and the address of data is stored in each index When querying, first find the address of the data, and then locate each data through the address

[the external chain picture transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-hr5blbf0-1620640286299) (03. DCL & Function & index. assets/1574059144094.png)]