mysql optimization (2) index optimization strategy
1: Index type
Index: used for quick query;
Node level 1, 2 raised to the 0th power
Node layer 1, 2 to the 1st power
Node layer 3, 2 to the 2nd power
Node layer 4, 2 to the 3rd power
Node layer 5 layers, 2 to the 4th power
. . .
. . .
. . .
Node layer 31, 2 to the power of 32
adds up to 4.2 billion
That is to say, 4.2 billion numbers can be checked up to 32 times
Ordinary queries require 2.1 billion times
This is-----》B-tree index
Note: It is called btree index, from a broad perspective , both use balanced trees, but in terms of specific implementation, each engine is slightly different,
For example, strictly speaking, the NDB engine uses T-tree
Myisam, innodb , the B-tree index is used by default
But abstractly---the B-tree system can be understood as a "sorted fast search structure".
1.2 hash index spring hahahaha. . . Nima Nima. . .
In the memory table, the default is hash index,
The theoretical query time complexity of hash is O(1)
Question: Since hash search is like this Efficient, why not use hash index?
Answer:
1: The result calculated by the hash function is random. If the data is placed on the disk,
use the algorithm. . . . .
For example, if the primary key is id, then as the id increases, the rows corresponding to
id are randomly placed on the disk. They are scattered irregularly! !
Hash algorithm There is no rule in allocating disk space! ! !
2: The range query cannot be optimized. 3: The prefix index cannot be used.
For example, in btree, the value of the field column is "hellopworld" and the index is added
To query xx=helloword, you can naturally use the index, xx=hello, or you can also use the index.
(left prefix index)
Because hash('helloword') and hash('hello') are both The relationship between the two is still random
4: The sorting cannot be optimized.
5: The row must be returned. That is to say, the data location is obtained through the index, and the data must be returned to the table
------》If you go back and search, it means that the directory is just a dictionary and you must actually turn the page again
2: Common misunderstandings of btree indexes
2.1 Add indexes to the columns commonly used in where conditions
Example: where cat_id=3 and price>100; //Query the third column, products above 100 yuan
Error : Indexes are added to cat_id, and, and price.
Error: Only cat_id or Price index can be used, because they are independent indexes, and only one can be used at the same time.
alter table add index(cat_id)
alter table add index(price)
alter table add index(goods_id) ----------------- ----------Only one can be used at the same time. . . . The joint index treats multiple columns as the overall value
index (cat_id, goods_name, price) ------------------------- -- Treat multiple columns as the overall value
2.2 After creating an index on multiple columns, the index will work no matter which column is queried
Error: Multiple On the column index, for the index to work, it needs to meet the left prefix requirement.
///Make prefix requirements
Take index(a,b,c) as an example, (note that it depends on the order)
Statement
Does the index work?
Where a=3
Yes, only column a is used
Where a=3 and b=5
Yes, columns a and b are used
Where a=3 and b=5 and c=4
Yes, abc
is used
Where b=3 / where c=4
No
Where a=3 and c=4
Column a can function as an index, but column c cannot
Where a=3 and b>10 and c=7
A can be used, b can be used, C cannot be used
Same as above, where a=3 and b like 'xxxx%' and c=7
A can be used, B can be used, C cannot be used
For ease of understanding, assume that ABC is a 10-meter-long plank each, and the river is 30 meters wide.
Exact match, then the board is 10 meters long,
Like, left prefix and range query, then the board is 5 meters long,
Splice it yourself, you can If you cross the other side of the river, you will know whether the index can be used.
As in the above example, where a=3 and b>10, and c=7,
A board is 10 meters long, A The column index works
Board A is connected to board B normally, and the index of board B is working
Board B is short and cannot be connected to board C,
The index of column C does not work.
The above is the content of mysql optimization (2) index optimization strategy. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1662
14
1419
52
1313
25
1262
29
1235
24
How to optimize the performance of MySQL database?
Sep 11, 2023 pm 06:10 PM
How to optimize the performance of MySQL database? In the modern information age, data has become an important asset for businesses and organizations. As one of the most commonly used relational database management systems, MySQL is widely used in all walks of life. However, as the amount of data increases and the load increases, the performance problems of the MySQL database gradually become apparent. In order to improve the stability and response speed of the system, it is crucial to optimize the performance of the MySQL database. This article will introduce some common MySQL database performance optimization methods to help readers
How to optimize cross-table queries and cross-database queries in PHP and MySQL through indexes?
Oct 15, 2023 am 09:57 AM
How to optimize cross-table queries and cross-database queries in PHP and MySQL through indexes? Introduction: In the development of applications that need to process large amounts of data, cross-table queries and cross-database queries are inevitable requirements. However, these operations are very resource intensive for database performance and can cause applications to slow down or even crash. This article will introduce how to optimize cross-table queries and cross-database queries in PHP and MySQL through indexes, thereby improving application performance. 1. Using indexes Index is a data structure in the database
How to optimize AVG function through MySQL to improve performance
May 11, 2023 am 08:00 AM
How to improve performance by optimizing the AVG function in MySQL MySQL is a popular relational database management system that contains many powerful functions and functions. The AVG function is widely used in calculating averages, but because this function needs to traverse the entire data set, it will cause performance problems in the case of large-scale data. This article will introduce in detail how to optimize the AVG function through MySQL to improve performance. 1. Using indexes Indexes are the most important part of MySQL optimization.
How to optimize the efficiency of data sorting and data grouping in PHP and MySQL through indexes?
Oct 15, 2023 pm 04:00 PM
How to optimize the efficiency of data sorting and data grouping in PHP and MySQL through indexes? In the process of developing web applications, it is often necessary to sort and group data. For data sorting and data grouping operations between PHP and MySQL, we can optimize its efficiency through indexes. An index is a data structure used to speed up the retrieval of data. It speeds up sorting, grouping, and lookup operations on data. Below we will introduce how to optimize data sorting and data grouping of PHP and MySQL through indexes.
How to improve the cache hit rate and database query efficiency of PHP and MySQL through indexes?
Oct 15, 2023 pm 01:15 PM
How to improve the cache hit rate and database query efficiency of PHP and MySQL through indexes? Introduction: PHP and MySQL are a commonly used combination when developing websites and applications. However, in order to optimize performance and improve user experience, we need to focus on the efficiency of database queries and cache hit rates. Among them, indexing is the key to improving query speed and cache efficiency. This article will introduce how to improve the cache hit rate and database query efficiency of PHP and MySQL through indexing, and give specific code examples. 1. Why use
MySQL optimization based on TokuDB engine: improving writing and compression performance
Jul 25, 2023 pm 11:45 PM
MySQL optimization based on TokuDB engine: improving writing and compression performance Introduction: As a commonly used relational database management system, MySQL is facing increasing writing pressure and storage requirements in the context of the big data era. To meet this challenge, TokuDB engine came into being. This article will introduce how to use the TokuDB engine to improve MySQL's writing performance and compression performance. 1. What is TokuDB engine? TokuDB engine is a big data-oriented engine designed to handle high write
How to achieve low-level optimization of MySQL: tips and best practices for advanced optimization of SQL statements
Nov 08, 2023 pm 04:32 PM
MySQL is a widely used relational database management system commonly used for web application development and data storage. In practical applications, the underlying optimization of MySQL is particularly important, among which the advanced optimization of SQL statements is the key to improving database performance. This article will introduce some tips and best practices for implementing MySQL's underlying optimization, as well as specific code examples. Determine the query conditions When writing SQL statements, you must first clearly define the query conditions and avoid using unlimited wildcard queries, that is, avoid using "%" to open the query.
Java skills experience sharing and summary for database search effect optimization
Sep 18, 2023 am 09:25 AM
Java skills experience sharing and summary for database search effect optimization Summary: Database search is one of the common operations in most applications. However, when the amount of data is large, search operations can become slow, affecting application performance and response time. This article will share some Java tips to help optimize database search results and provide specific code examples. Using Indexes Indexing is an important part of improving search efficiency in a database. Before performing a search operation, make sure that you create appropriate indexes on the columns that need to be searched. For example
