1. SQL statement performance causes

Performance degradation THE SQL is slow and takes a long time to execute. 1. The query statement is poorly written. 2. 4. Unreasonable server tuning parameters (thread, buffer, etc.)

Index of 2.

What is an index

  • An index is a data structure that helps mysql query efficiently.
  • function
    • The sorting
    • Quickly find
  • Data structure B+ tree
    • Data is not actually deleted but updated, and deletion invalidates the index
    • The index itself is also large, with disk DF -h
    • Compound indexes are used a lot (like finding things on Taobao)

The index and

  • advantage
    • Improve search efficiency and reduce IO costs
    • Indexes sort data, reducing sorting costs and CPU consumption
  • disadvantage
    • An index occupies disk space and is also an index table
    • Reduce the update table speed, change the data and change the index.
    • It takes time to optimize the index and build the optimal index

The index classification

  • Single-value index: An index contains a single column, which is more complex. Generally, a table does not have more than five indexes
  • Unique index: The value of the indexed column must be unique, but empty values are allowed
  • Composite index: An index contains multiple columns.

The basic grammar

Improve search efficiency, similar to dictionary table user, field name email

select * from user where name = '' and email = '';
Copy the code
Create index Index name on Table name (field) create index idx_user_name on user(name) // Single value create index idx_user_nameEmail on user(name, Email) / / compositeCopy the code

3. SQL execution sequence

  1. handwritten
  2. Machine readable

Machine reading sequence (emphasis From From)Summarize the SQL parsing order