Today's article still records the process of solving the problem . When testing the code yesterday , It takes about ten seconds to find a function . I'll look at my code logic , After reading the code completely , According to the feeling, there are two reasons for the long time : One is mysql Writing problems of , Another problem may be the circular query of the database . Code dream will not upload , It may involve the privacy of the company

         The bottom is glued out sql part , You can see this sql all 7 More than seconds , The second picture is using explain Results after actuator , You can see that only two of the five tables are indexed , Other tables do not have indexes , Can see type The level of this column is also ALL.
         The result value is from good to bad :system > const > eq_ref > ref > fulltext > ref_or_null >
index_merge > unique_subquery > index_subquery > range > index >
ALL. generally speaking , We must ensure that the inquiry reaches at least range level , It's best to achieve ref, Otherwise, performance problems may occur . So this sql It's time to optimize .

         Optimization method :
        1, Combine first type+key These two columns are optimized , That is, optimize by adding indexes . Usually I will give it to where and order by
Add index to fields used in Clause . So I'm here d Four indexes were added to the table (deviceId,name,del_flag,project_id),c
Table to prdfession Index is also added .

        2, Then according to Extra Optimize this column , You can see that the two columns above appear “Using temporary”,“Using
filesort” These two cases .
        “Using temporary”: refer to sql Medium if order by perhaps group
by The fields in are from other tables rather than the first table in the join order , A temporary table will be created . Solution : You can try to create a federated index .
        “Using filesort”: refer to order by The amount of field data queried exceeds mysql Default value of , Caused order
by The fields in do not go through the index , File query used , It is also the cause of performance bottlenecks . Solution : You can try to put the sorting function into the code ,sql Do not sort in .
         When a normal index is created , Joint index (project_id + name) after , Take another look sql Execution speed of .

         You can see that the execution speed was immediately raised ,explain When the order is executed , You will also find type All trains have reached ref level ,extra There's no train left “Using
temporary”,“Using filesort” These two signs that affect performance .
         Now? sql The optimization of is completed , If the speed is still slow , Consider optimizing the logic of the code .

©2019-2020 Toolsou All rights reserved,
Solve in servlet The Chinese output in is a question mark C String function and character function in language MySQL management 35 A small coup optimization Java performance —— Concise article Seven sorting algorithms (java code ) use Ansible Batch deployment SSH Password free login to remote host according to excel generate create Build table SQL sentence Spring Source code series ( sixteen )Spring merge BeanDefinition Principle of Virtual machine installation Linux course What are the common exception classes ?