\
1, build table
2. Write a stored procedure to insert data
CREATE DEFINER=root@% PROCEDURE yushenghuan(num int)# start declare I int default 0; WHILE I < num do INSERT INTO yusheng (id, name,info,type) VALUES (I, I +’@yusheng’, ‘I bring you the stars, I still think the stars are not as good as you ‘, 1); SET i = i + 1; END while; end
We insert 10000 first, too few query time will make no difference
3. SQL optimization
Add index to field first: blog.51cto.com/13764714/24…
1. Optimize the query to avoid full table scan. First consider creating indexes on the columns involved in WHERE and Order BY.
Select info from yusheng where order by info
2. Avoid using! In the WHERE clause. = or <> operators, otherwise the engine will abandon the index and perform a full table scan.
Select info from yusheng where id! = 1
3. Avoid null values in the WHERE clause. Otherwise, the engine will give up using indexes and perform a full table scan. Select info from yusheng where info is null select info from yusheng where info is null select info from yusheng where info is null select info from yusheng where info =0
Select id from yusheng where id=10 or id=20 select id from yusheng where id=10 or id=20 select id from yusheng where id=20 select id from yusheng where id=10 union all select id from yusheng where id=20
5. The following query will also cause a full table scan:
select id from yusheng where id like ‘%11%’
To be more efficient, consider full-text retrieval.
Select id from yusheng where id like ’11 ‘select id from yusheng where id like ’11
Select id from yusheng where id in(1,2,3) select id from yusheng where id in(1,2,3) select id from yusheng where id in(1,2,3) select id from yusheng where id between 1 and 3
7. If an argument is used in the WHERE clause, a full table scan will also occur.
Because SQL parses local variables only at run time, the optimizer cannot defer access plan selection until run time; It must be selected at compile time. However, if the access plan is established at compile time, the value of the variable is unknown and therefore cannot be used as an input for index selection. Select id from yusheng where id=@num where id=@num select id from yusheng where id=@num where id=@num
8. Try to avoid expression operations on fields in the WHERE clause. This will cause the engine to abandon the use of indexes and perform a full table scan. Select id from yusheng where num/2=100 select id from yusheng where num=100*2 9 You should try to avoid performing functional operations on fields in the WHERE clause, which will cause the engine to abandon the index for a full table scan. Such as: Select id from yusheng where substring(name,1,3)=’ ABC ‘select id from yusheng where substring(name,1,3)=’ ABC’ select id from yusheng where substring(name,1,3)=’ ABC Datediff (day,createdate,’2005-11-30′)=0–‘2005-11-30’ select id from yusheng where name like ‘abc%’ select id from yusheng where createdate>=’2005-11-30′ and createdate<‘2005-12-1’ 10. Do not perform functions, arithmetic operations, or other expressions to the left of the “=” in the WHERE clause, or the system may fail to use the index correctly.
11. When using index fields as a condition, if the index is a compound index, the first field in the index must be used as a condition to ensure that the index is used by the system. Otherwise, the index will not be used. Select col1,col2 into #t from yusheng where 1=0 select col1,col2 into #t from yusheng where 1=0 create table #t(…) Select num from a where num in(select num from b) select num from a where num in(select num from b) select num from a where exists(select 1 from b where num=a.num) 14. Not all indexes are valid for queries. SQL queries are optimized according to the data in the table. If there is a large amount of duplicate data in the index column, SQL queries may not use the index.
15. The more indexes, the better. Indexes can improve the efficiency of select, but also reduce the efficiency of INSERT and UPDATE, because insert or UPDATE may cause index reconstruction. The number of indexes in a table should not exceed 6. If there are too many, consider whether indexes on infrequently used columns are necessary. 16. Updates to clustered index data columns should be avoided as much as possible, because the clustered index data column order is the physical storage order of table records, and changing the values of the clustered index data column will cause the entire table record order to be adjusted, which is costly. If applications require frequent updates of clustered index data columns, consider whether the index should be clustered index.
17. Use numeric fields as far as possible. If the fields containing only numeric information are not designed as characters, it will reduce query and join performance and increase storage overhead. This is because the engine processes queries and joins by comparing each character in the string, whereas for numeric types you only need to compare once.
18. Use varchar/ NVARCHar instead of char/nchar as much as possible, because first of all, the storage space of variable length fields is small, which can save storage space. Secondly, for queries, the search efficiency in a relatively small field is obviously higher. Select * from t where select * from t where select * from t
Use table variables instead of temporary tables whenever possible. If the table variables contain large amounts of data, be aware that the indexes are very limited (only primary key indexes).
21. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.
22. Temporary tables are not unusable, and their appropriate use can make some routines more efficient, for example, when repeated references to a data set in a large table or a commonly used table are needed. However, for one-time events, it is best to use export tables.
23. When creating a temporary table, if a large amount of data is inserted at one time, you can use select into instead of create table to avoid causing a large number of logs and improve the speed. If the data volume is small, create the table first, then insert, in order to ease system table resources.
24. If temporary tables are used, delete all temporary tables explicitly at the end of the stored procedure, first truncate table, and then drop table. In this way, the system table can be locked for a long time. 25. Avoid cursors as they are inefficient, and consider overwriting if cursors operate on more than 10,000 rows.
26. Look for a set-based solution to the problem before using a cursor based approach or temporary table approach. Set-based approaches are usually more effective.
27. Like temporary tables, cursors are not unavailable. Using the FAST_FORWARD cursor for small data sets is often preferable to other row-by-row processing methods, especially if you have to reference several tables to get the data you want. Routines that include “totals” in the result set are generally executed faster than using cursors. If development time permits, try both the cursor – and set-based approaches to see which one works better.
28. SET SET NOCOUNT ON at the beginning of all stored procedures and triggers and SET SET NOCOUNT OFF at the end. There is no need to send the DONE_IN_PROC message to the client after executing each statement of the stored procedure and trigger. 29. Try not to return a large amount of data to the client. If the amount of data is too large, consider whether the corresponding requirements are reasonable.
30. Avoid large transaction operations to improve system concurrency.
31. If two tables are joined, it is much more efficient to include all join conditions as possible
32. The system will automatically remove the duplicate tuple. If the duplicate tuple is kept, the system will use the UNION ALL
Group BY SELECT ID from a where a>10 group by ID; Select DISTINCT ID from a where A >10