How to optimize MySQL?

osc_kvlhvh2u 2020-11-09 13:38:56
optimize mysql


source :my.oschina.net/liughDevelop/blog/1788148

  • One 、 Lead to SQL Reasons for slow implementation :

  • Two 、 When analyzing the cause , We have to find a starting point :

  • 3、 ... and 、 What is index ?

  • Four 、Explain analysis

  • 5、 ... and 、 Optimization case

  • 6、 ... and 、 Need to create index ?


The index is similar to the bibliographic index of a university library , Can improve the efficiency of data retrieval , Reduce the IO cost .MySQL stay 300 The performance of about 10000 records began to decline gradually , Although official documents say 500~800w Record , Therefore, it is very necessary to index large amount of data .MySQL Provides Explain, Used for display SQL Details of execution , Index can be optimized .

One 、 Lead to SQL Reasons for slow implementation :

1. Hardware problem . Such as slow network speed , Out of memory ,I/O Small throughput , Disk full wait .

2. No index or index failure .( Generally in Internet companies ,DBA I'll lock my watch in the middle of the night , Index again , Because when you delete a data , The tree structure of the index is not complete . So what Internet companies do is fake delete . One is for data analysis , The second is not to destroy the index )

3. Excessive data ( Sub database and sub table )

4. Server tuning and parameter settings ( adjustment my.cnf)

Two 、 When analyzing the cause , We have to find a starting point :

1. First observe , Open slow query log , Set the corresponding threshold ( For example, over 3 Seconds are slow. SQL), After a day in the production environment , Look at what SQL It's slow .

2.Explain And slow SQL analysis . such as SQL Bad sentences , No or invalid index , Too many associated queries ( Sometimes it's a design defect or an inescapable need ) wait .

3.Show Profile It's better than Explain Further implementation details , Each execution can be queried SQL What did you do , How many seconds did it take .

4. look for DBA Or O & m right MySQL Parameter tuning of the server .

3、 ... and 、 What is index ?

MySQL The official definition of index is : Indexes (Index) Help MySQL Data structure for efficient data acquisition . We can simply understand it as :** A data structure for fast searching and arranging .**Mysql There are two main structures of index :B+Tree Index and Hash Indexes . What we usually call index , If not specified , Generally speaking, it means B Index of tree structure organization (B+Tree Indexes ). Index as shown in the figure :

Outermost light blue disk block 1 There are data in it. 17、35( Navy Blue ) And a pointer P1、P2、P3( yellow ).P1 Pointer indicates less than 17 The disk blocks ,P2 Is in 17-35 Between ,P3 Pointing greater than 35 The disk blocks . Real data exists in the cotyledon node, the bottom layer 3、5、9、10、13...... Non-leaf nodes do not store real data , Only data items that guide the search direction are stored , Such as 17、35.

Lookup process : Search for example 28 Data item , Load disk block first 1 Into memory , Happen once I/O, Use binary search to determine P2 The pointer . Then found 28 stay 26 and 30 Between , adopt P2 Address of pointer load disk block 3 To the memory , Happen a second time I/O. Find the disk block in the same way 8, The third time I/O.

Here's the real story , above 3 Layer of B+Tree Can represent millions of data , Millions of data only happened three times I/O Not millions of times I/O, Time improvement is huge .

Four 、Explain analysis

Foreshadowing completed , Enter the practical part , Insert the data needed for the test first :

CREATE TABLE `user_info` (
  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50NOT NULL DEFAULT '',
  `age`  INT(11)              DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO user_info (name, age) VALUES ('xys'20);
INSERT INTO user_info (name, age) VALUES ('a'21);
INSERT INTO user_info (name, age) VALUES ('b'23);
INSERT INTO user_info (name, age) VALUES ('c'50);
INSERT INTO user_info (name, age) VALUES ('d'15);
INSERT INTO user_info (name, age) VALUES ('e'20);
INSERT INTO user_info (name, age) VALUES ('f'21);
INSERT INTO user_info (name, age) VALUES ('g'23);
INSERT INTO user_info (name, age) VALUES ('h'50);
INSERT INTO user_info (name, age) VALUES ('i'15);
CREATE TABLE `order_info` (
  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT(20)           DEFAULT NULL,
  `product_name` VARCHAR(50NOT NULL DEFAULT '',
  `productor`    VARCHAR(30)          DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_product_detail_index` (`user_id``product_name``productor`)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
INSERT INTO order_info (user_id, product_name, productor) VALUES (1'p1''WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1'p2''WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1'p1''DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2'p1''WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2'p5''WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3'p3''MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4'p1''WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6'p1''WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9'p8''TE');

First experience , perform Explain The effect of :

Index usage in possible_keys、key and key_len The three column , Next, let's talk about it from left to right .

1.id

--id identical , Execution order from top to bottom 
explain select u.*,o.* from user_info u,order_info o where u.id=o.user_id;
--id Different , The higher the value, the earlier it is executed 
explain select * from  user_info  where id=(select user_id from order_info where  product_name ='p8');

2.select_type

You can see id Execution instance of , There are several types in total :

  • SIMPLE: Indicates that this query does not contain UNION Query or subquery

  • PRIMARY: Indicates that this query is the outermost query

  • SUBQUERY: First in subquery SELECT

  • UNION: Indicates that this query is UNION Second or subsequent queries for

  • DEPENDENT UNION:UNION The second or subsequent query statement in , Depends on external queries

  • UNION RESULT, UNION Result

  • DEPENDENT SUBQUERY: First in subquery SELECT, Depends on external queries . That is, the subquery depends on the results of the outer query .

  • DERIVED: derivative , For exported tables SELECT(FROM A subquery of a clause )

3.table

table Represents the table or derived table involved in the query :

explain select tt.* from (select u.* from user_info u,order_info o where u.id=o.user_id and u.id=1) tt

id by 1 The expression of id by 2 Of u and o Table derived .

4.type

type Fields are important , It provides an important basis to judge whether the query is efficient . adopt type Field , We judge that this query is Full table scan still Index scanning, etc .

type Common values include :

  • system: There is only one data in the table , This type is special const type .

  • const: Equivalent query scan for primary key or unique index , Return at most one row of data .const Very fast query speed , Because it only reads once . For example, the following query , It uses a primary key index , therefore type Namely const Type of :explain select * from user_info where id = 2;

  • eq_ref: This type usually occurs in multi table join Inquire about , For each result in the previous table , Can only match to one row of results in the following table . And the comparison operation of query is usually =, High query efficiency . for example :explain select * from user_info, order_info where user_info.id = order_info.user_id;

  • ref: This type usually occurs in multi table join Inquire about , For non unique or non primary key indexes , Or use it Left most prefix Query of rule index . For example, in the following example , That's it ref Type of query :explain select * from user_info, order_info where user_info.id = order_info.user_id AND order_info.user_id = 5

  • range: Indicates using index range queries , Get some data records in the table by index field range . This type usually appears in =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() In operation . For example, the following example is a range query :explain select * from user_info  where id between 2 and 8;

  • index: Represents a full index scan (full index scan), and ALL Similar type , It's just ALL Type is full table scan , and index Type only scans all indexes , Without scanning data .index Types usually appear in : The data to be queried can be obtained directly in the index tree , Without scanning data . When this is the case ,Extra Field Will be displayed Using index.

  • ALL: Indicates full table scan , This type of query is one of the worst performing queries . Generally speaking , Our query should not appear ALL Type of query , Because such a query has a large amount of data , It's a huge disaster for database performance . If a query is ALL Types of queries , In general, you can add indexes to the corresponding fields to avoid .

Generally speaking , Different type The performance relationship for the type is as follows :    ALL < index < range ~ index_merge < ref < eq_ref < const < system    ALL Type because it is a full table scan , So under the same query conditions , It's the slowest . and index Type of query is not a full table scan , But it scans all the indexes , Therefore ratio ALL Slightly faster type . The latter types use indexes to query data , So part or most of the data can be filtered , Therefore, the query efficiency is relatively high .

5.possible_keys

It said mysql In the query , Possible indexes . Be careful , Even if some indexes possible_keys It appears that , But it doesn't mean that the index will actually be mysql Use to .mysql Which indexes are used in query , from key Field decision .

6.key

This field is mysql Index actually used in the current query . Like dinner ,possible_keys How many people should be there ,key How many people are there . When we don't index :

explain select o.* from order_info o where  o.product_name= 'p1' and  o.productor='whh';
create index idx_name_productor on order_info(productor);
drop index idx_name_productor on order_info;

Query after establishing composite index :

7.key_len

Indicates the number of bytes indexed by the query optimizer , This field evaluates whether the composite index is fully used .

8.ref

This indicates which column of the index is used , If possible , Is a constant . Preceding type There are also attributes ref, Pay attention to differences .

9.rows

rows It's also an important field ,mysql Query optimizer based on statistics , Estimate sql Number of data rows to scan to find the result set , This value is very intuitive sql Efficiency or quality , In principle, rows The less, the better. . You can contrast key Examples in , One of them doesn't have index money ,rows yes 9, After indexing ,rows yes 4.

10.extra

explain A lot of additional information in extra Field shows , Common contents are as follows :

  • using filesort : Express mysql Additional sort operations required , Cannot sort by index order . Generally speaking, there are using filesort It's recommended to remove them through optimization , Because of such queries cpu High resource consumption .

  • using index: Overwrite index scan , Indicates that the query can find the required data in the index tree , Do not scan table data files , It often indicates that the performance is good .

  • using temporary: Query using temporary table , Generally appears in sorting , Grouping and multiple tables join The situation of , Low query efficiency , Suggested optimization .

  • using where : Table name used where Filter .

5、 ... and 、 Optimization case

explain select u.*,o.* from user_info u LEFT JOIN  order_info o on u.id=o.user_id;

Execution results ,type Yes ALL, And there's no index :

img

Start optimizing , Create index on associated column , Obviously see type Column ALL become ref, And the index is used ,rows Also from scanning 9 The line has changed. 1 That's ok : There is a general rule in this : Left link index above right table , Right link index above left table .

6、 ... and 、 Need to create index ?

Index can improve query speed very efficiently , At the same time, it will reduce the speed of updating the table . The index is actually a table , The table holds the primary key and index fields , And points to the record of the entity table , So index columns also need to occupy space .

I'm an ordinary procedural ape , Level co., LTD. , There are inevitably mistakes in the article , Welcome readers who sacrifice their precious time , Let me express my opinions on the content of this article , My purpose is only to help readers .

 Recommended reading 
 Code comparison tool , I'll use this 6 individual 
 Share my favorite 5 A free online  SQL Database environment , It's so convenient !
Spring Boot A combination of three moves , Hand in hand to teach you to play elegant back-end interface 
MySQL 5.7 vs 8.0, You choose the one ? Net friend : I'm going to stay where I am ~

Last , I recommend you an interesting and interesting official account : The guy who wrote the code ,7 Old programmers teach you to write bug, reply interview | resources Send you a complete set of Development Notes There's a surprise
Sweep yards attention
版权声明
本文为[osc_kvlhvh2u]所创,转载请带上原文链接,感谢

  1. 【计算机网络 12(1),尚学堂马士兵Java视频教程
  2. 【程序猿历程,史上最全的Java面试题集锦在这里
  3. 【程序猿历程(1),Javaweb视频教程百度云
  4. Notes on MySQL 45 lectures (1-7)
  5. [computer network 12 (1), Shang Xuetang Ma soldier java video tutorial
  6. The most complete collection of Java interview questions in history is here
  7. [process of program ape (1), JavaWeb video tutorial, baidu cloud
  8. Notes on MySQL 45 lectures (1-7)
  9. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  10. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  11. 精进 Spring Boot 03:Spring Boot 的配置文件和配置管理,以及用三种方式读取配置文件
  12. Refined spring boot 03: spring boot configuration files and configuration management, and reading configuration files in three ways
  13. 【递归,Java传智播客笔记
  14. [recursion, Java intelligence podcast notes
  15. [adhere to painting for 386 days] the beginning of spring of 24 solar terms
  16. K8S系列第八篇(Service、EndPoints以及高可用kubeadm部署)
  17. K8s Series Part 8 (service, endpoints and high availability kubeadm deployment)
  18. 【重识 HTML (3),350道Java面试真题分享
  19. 【重识 HTML (2),Java并发编程必会的多线程你竟然还不会
  20. 【重识 HTML (1),二本Java小菜鸟4面字节跳动被秒成渣渣
  21. [re recognize HTML (3) and share 350 real Java interview questions
  22. [re recognize HTML (2). Multithreading is a must for Java Concurrent Programming. How dare you not
  23. [re recognize HTML (1), two Java rookies' 4-sided bytes beat and become slag in seconds
  24. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  25. RPC 1: how to develop RPC framework from scratch
  26. 造轮子系列之RPC 1:如何从零开始开发RPC框架
  27. RPC 1: how to develop RPC framework from scratch
  28. 一次性捋清楚吧,对乱糟糟的,Spring事务扩展机制
  29. 一文彻底弄懂如何选择抽象类还是接口,连续四年百度Java岗必问面试题
  30. Redis常用命令
  31. 一双拖鞋引发的血案,狂神说Java系列笔记
  32. 一、mysql基础安装
  33. 一位程序员的独白:尽管我一生坎坷,Java框架面试基础
  34. Clear it all at once. For the messy, spring transaction extension mechanism
  35. A thorough understanding of how to choose abstract classes or interfaces, baidu Java post must ask interview questions for four consecutive years
  36. Redis common commands
  37. A pair of slippers triggered the murder, crazy God said java series notes
  38. 1、 MySQL basic installation
  39. Monologue of a programmer: despite my ups and downs in my life, Java framework is the foundation of interview
  40. 【大厂面试】三面三问Spring循环依赖,请一定要把这篇看完(建议收藏)
  41. 一线互联网企业中,springboot入门项目
  42. 一篇文带你入门SSM框架Spring开发,帮你快速拿Offer
  43. 【面试资料】Java全集、微服务、大数据、数据结构与算法、机器学习知识最全总结,283页pdf
  44. 【leetcode刷题】24.数组中重复的数字——Java版
  45. 【leetcode刷题】23.对称二叉树——Java版
  46. 【leetcode刷题】22.二叉树的中序遍历——Java版
  47. 【leetcode刷题】21.三数之和——Java版
  48. 【leetcode刷题】20.最长回文子串——Java版
  49. 【leetcode刷题】19.回文链表——Java版
  50. 【leetcode刷题】18.反转链表——Java版
  51. 【leetcode刷题】17.相交链表——Java&python版
  52. 【leetcode刷题】16.环形链表——Java版
  53. 【leetcode刷题】15.汉明距离——Java版
  54. 【leetcode刷题】14.找到所有数组中消失的数字——Java版
  55. 【leetcode刷题】13.比特位计数——Java版
  56. oracle控制用户权限命令
  57. 三年Java开发,继阿里,鲁班二期Java架构师
  58. Oracle必须要启动的服务
  59. 万字长文!深入剖析HashMap,Java基础笔试题大全带答案
  60. 一问Kafka就心慌?我却凭着这份,图灵学院vip课程百度云