indexes, views, SQL optimizations, and database stored procedures

I. Index

Indexing is the most effective and common technique for query optimization

An index is a separate, physical database structure that refers to a list of pointers to a column or columns in a table.

In MySQL, the physical storage of a table consists of two parts, one for storing the table's data, the other for the index, and when searching for data, MySQL will first search the index, find a pointer to the starting position of the desired data, and then locate the target data directly through the pointer.

1. Create an index :

CREATE index name on table name (column name to add index)

You can add indexes to multiple columns in a table

by querying the SQL add a sentence before the statement Explain index efficiency can be analyzed,

There is such a table:

Execute EXPLAIN select*from t_product WHERE productName = ' TV '

same Article SQL query statement, you can see the scan when the index is not indexed 9 Line

CREATE INDEX index_pname on t_product (productName)

Once the index is established, the same query is executed again, and it is discovered that only the 1 And then the results are done.

Effects such as

2. Delete the index :

Alter table Name Drop INDEX index name

How to select an indexed column:

Columns commonly seen in 1.Where clauses

Select ID from t_student where sName= ' Zhang San '

2. Columns that appear frequently in the join clause

Select S.*,g.grade from T_student s joins T_grade G on S.id=g.studentid

3. Columns that are frequently sorted or grouped

Select S.*,g.grade from T_student s joins T_grade G on S.id=g.studentid Group by S.name ORDER by G.grade

like using indexed columns, like more special, MySQL The index is: the operand does not begin with a wildcard.

Select*from t_student where sName like ' j% ' will use the index

Select*from t_student where sName like '%j% ' will not use the index and will still query the whole table

Disadvantages of the index:

1. Slow down and change the speed of data
2. Disk space consumption
3. Increase the burden on the query optimizer

It is not easy to think that "the more indexes, the higher the performance", the more you do not have to index each data column. If you rarely use or never use an index, we recommend that you delete the index.

Two View

A view is a view in a computer database, a virtual table whose contents are defined by a query .

1. Create a view

CREATE VIEW V_find as SELECT id,productname,price,imgpath from T_product;

2. Working with views

Querying directly from the view

Select*from V_find;

For complex, even-table queries, you can use the CREATE view to make SQL statements simple.

CREATE VIEW V_join as SELECT r.address,r.price,m.manname from Test.t_man m,test.t_room R WHERE r.manid=m.id;

Select*from v_join where Manname= ' Zhang San

3. Delete a view

DROP VIEW V_find

Note : The view is only for queries, it's just a virtual table! If you need to add data, it must be added to the real table.

three . SQL Statement optimization policy

1. Add an index to the appropriate column.

2. to avoid full-table scanning, first consider establishing an index on the columns involved in the Where and order by .

3. Avoid Select *

The more data you read from the database, the slower the query becomes

4. Always set an ID for each table

Using the varchar type to make the performance drop when the primary key

5. Use emum instead of varchar

Sex ENUM (' male, ' female ')

If a column contains only a specific value for a limited tree, such as gender, state, etc., try to enumerate all possible values as data types using an enum, and the value of the enum column is identified by the identity value, and MySQL is processed faster.

6. Try to avoid using or in the WHERE clause to join the condition, if a field has an index and a field is not indexed, it will cause the engine to discard using the index for a full table scan

7. Fuzzy queries do not start with wildcards, or cause indexes to fail with a full table scan

Select *from t_student where SName like ' a% '

8. try to avoid the where clause to perform an expression on a field , which causes the engine to fail to discard the full table scan using the index.

Select ID from t where num/2=100

Should be changed to select ID from t where num=100*2

9. In and not to be used with caution, otherwise it can cause full table scan, can use exists instead of in.

Select num from a where num in (select num from B)

Replace with the following statement

Select num from a where exists (select 1 from b where num=a.num)

10. The index of a table should not be more than 6, if too many should consider deleting some infrequently used indexes

11. Try to avoid large transaction operation and improve the system concurrency capability

12. Not all indexes are valid for queries, and when there is a large amount of data duplication in the index columns, SQL queries may not take advantage of the index, and if there are almost half of the fields Sex,male,female in a table, the query efficiency does not work even if the index page is built on sex.

Iv. Database Stored Procedures

L What is a stored procedure?

A stored procedure is a well-written SQL code that is in the directory where the database exists, and the external program can directly invoke the stored procedures defined in the database.

L Advantages of stored procedures

1. Performance increases, making the database self-executing stored procedures more efficient and faster than sending SQL statements to the database through an application.
2. Reduces the frequency with which the application interacts with database information. In some businesses, applications send multiple SQL instructions to the server. Using a stored procedure requires only one statement that calls the stored procedure, and then gets the data you want.
3. Stored procedures are highly reusable and are stored in the database so that they can be used for any reason.
4. Stored procedures are a safe way for a database administrator to obtain data services for applications that do not have permission to access tables in the database and to use stored procedures.

L Disadvantages of stored procedures

1. Stored procedures increase the amount of system resources consumed by the database.
2. Because the stored procedure is still SQL, there is no way to write a stored procedure that corresponds to a complex business logic like a programming language.
3. Stored procedures are not easy to debug
4. The stored procedure is very difficult to write and maintain.

L Stored Procedure syntax

#创建存储过程

delimiter//

CREATE PROCEDURE P_find ()

BEGIN

select* from Test.t_man;

END;

//

#使用存储过程

Call P_find ();

#删除存储过程

DROP PROCEDURE P_find;

L Stored Procedure Parameters

#IN表示输入参数, out represents the output parameter

delimiter//

CREATE PROCEDURE Pro (in Mname varchar), out jname varchar (20))

BEGIN

SELECT *from Test.t_man where manname=mname;

END;

//

Call Pro (' Zhang San ', @j);

SELECT @j;

L Stored Procedure definition variables

DECLARE t INT DEFAULT 0;

SET t=10;

SET t=t+1;

Note: Stored procedures can only be defined at the beginning of a stored procedure

Instance:

#在exercise数据库中有t_man表, contains the field employee name, and the employee position name. Enter a citizen's name, according to the job title, to determine the level of ownership (there is no level in the table)

delimiter//

CREATE PROCEDURE p_get (in Mname varchar (a), out info VARCHAR (20))

BEGIN

#定义变量

DECLARE mjob VARCHAR (+) DEFAULT ';

#查询指定姓名的员工的职务 and assign the job title to the Mjob variable

SELECT job into Mjob from Exercise.t_man WHERE manname=mname;

IF mjob= ' manager ' OR mjob= ' assistant ' then

SET info= ' executives ';

ELSEIF mjob= ' accountant ' OR mjob= ' clerk ' then

SET info = ' administrative staff ';

ELSE

SET info= ' staff ';

END IF;

END

//

#两句可以一起执行, but the middle must use ";" Separated

Call P_get (' Zhang Sanfeng ', @m);

SELECT @m;

#动态条件查询

delimiter//

CREATE PROCEDURE P_dyna (in Mname VARCHAR (a), in StartDate date,in endDate DATE)

BEGIN

DECLARE msql VARCHAR ($) DEFAULT ' select *from exercise.t_man where 1=1 ';

IF Mname is not NULL and mname! = "Then

SET Msql=concat (mSQL, "and manname like '", mname, "% '");

END IF;

IF StartDate is isn't NULL then

SET Msql=concat (mSQL, "and birthday>= '", StartDate, "'");

END IF;

IF EndDate is isn't NULL then

SET Msql=concat (mSQL, "and birthday<= '", EndDate, "'");

END IF;

#执行SQL语句, first build a global variable

SET @ms =msql;

PREPARE St from @ms;

EXECUTE St;

deallocate PREPARE St;

END;

//

Call P_dyna (' Zhang ', ' 1980-01-01 ', ' 2016-01-01 ')

indexes, views, SQL optimizations, and database stored procedures