Why is MyISAM faster than InnoDB query in MySQL

chingho 2021-06-10 16:39:18
myisam faster innodb query mysql


We all know that MySQL in ,MyISAM Than InnoDB Quick query , But a lot of people don't know how it works .

Today, let's talk about the principle , Also verify whether MyISAM Than InnoDB It's really fast .

Before we explore the principle , Let's verify the query speed first .


verification

Let's test two problems together :

 

1、 Query by primary key ,MyISAM Is it better than InnoDB Quick query ?

2、 Query through non primary key fields ,MyISAM Is it better than InnoDB Quick query ?


Preparation before verification

Order form structure :

CREATE TABLE `trade` (
`id` INT(11) NULL DEFAULT NULL,
`trade_no` INT(11) NULL DEFAULT NULL,
UNIQUE INDEX `id` (`id`),
INDEX `trade_no` (`trade_no`)
)
COMMENT=' Order '
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

Create tables for different engines trade_myisam、trade_innodb, And insert 10 Ten thousand test data , as follows :

 


Here we all use Int type , It's for our convenience , Use interval queries , To verify the query speed .


Validate ideas

1、 Pass respectively id、trade_no Inquire about 10000~20000 The record of the interval , And get the query time .

2、 Circular query 100 Time , And get the query cost Time average .

3、 By comparing the query cost Time average , To judge performance .


Now let's verify , Two questions at the beginning of the article :

1、 Primary key query

C# Realize primary key query Id stay 10000~20000 Section , The code for the average query time is as follows :

using (var conn = new MySqlConnection(connsql))
{
conn.Open();
// Query times
var queryTime = 1000;
​
//InnoDB Data engine :
Primary key query
// Starting time
var startTime = DateTime.Now;
for (var i = 0; i < queryTime; i++)
{
// Insert
var sql = "select * from trade_innodb where id>=10000 and id<20000";
var sqlComm = new MySqlCommand();
sqlComm.Connection = conn;
sqlComm.CommandText = sql;
sqlComm.ExecuteScalar();
sqlComm.Dispose();
}
// Completion time
var endTime = DateTime.Now;
// Time consuming
var spanTime = (endTime - startTime) * 1.0 / queryTime;
Console.WriteLine("InnoDB Primary key query 1000 The average time taken per time :
" + spanTime.Minutes + " branch " + spanTime.Seconds + " second " + spanTime.Milliseconds + " millisecond ");
​
//MyISAM Data engine :
Primary key query
// Starting time
startTime = DateTime.Now;
for (var i = 0; i < queryTime; i++)
{
// Insert
var sql = "select * from trade_myisam where id>=10000 and id<20000";
var sqlComm = new MySqlCommand();
sqlComm.Connection = conn;
sqlComm.CommandText = sql;
sqlComm.ExecuteScalar();
sqlComm.Dispose();
}
// Completion time
endTime = DateTime.Now;
// Time consuming
spanTime = (endTime - startTime) * 1.0 / queryTime;
Console.WriteLine("MyISAM Primary key query 1000 The average time taken per time :
" + spanTime.Minutes + " branch " + spanTime.Seconds + " second " + spanTime.Milliseconds + " millisecond ");
​
conn.Close();
}

Running results :

Through the picture above , You can see the result : MyISAM Query performance is really good Innodb fast .


2、 Non primary key query

C# Realize fee primary key query trade_no stay 10000~20000 Section , The code for the average query time is as follows :

using (var conn = new MySqlConnection(connsql))
{
conn.Open();
// Query times
var queryTime = 1000;
​
//InnoDB Data engine :
Non primary key query
// Starting time
var startTime = DateTime.Now;
for (var i = 0; i < queryTime; i++)
{
// Insert
var sql = "select * from trade_innodb where trade_no>=10000 and trade_no<20000";
var sqlComm = new MySqlCommand();
sqlComm.Connection = conn;
sqlComm.CommandText = sql;
sqlComm.ExecuteScalar();
sqlComm.Dispose();
}
// Completion time
var endTime = DateTime.Now;
// Time consuming
var spanTime = (endTime - startTime) * 1.0 / queryTime;
Console.WriteLine("InnoDB Non primary key query 1000 The average time taken per time :
" + spanTime.Minutes + " branch " + spanTime.Seconds + " second " + spanTime.Milliseconds + " millisecond ");
​
//MyISAM Data engine :
Non primary key query
// Starting time
startTime = DateTime.Now;
for (var i = 0; i < queryTime; i++)
{
// Insert
var sql = "select * from trade_myisam where trade_no>=10000 and trade_no<20000";
var sqlComm = new MySqlCommand();
sqlComm.Connection = conn;
sqlComm.CommandText = sql;
sqlComm.ExecuteScalar();
sqlComm.Dispose();
}
// Completion time
endTime = DateTime.Now;
// Time consuming
spanTime = (endTime - startTime) * 1.0 / queryTime;
Console.WriteLine("MyISAM Non primary key query 1000 The average time taken per time :
" + spanTime.Minutes + " branch " + spanTime.Seconds + " second " + spanTime.Milliseconds + " millisecond ");
​
conn.Close();
}

Running results :

Through the picture above , You can see the result : MyISAM Query performance is really good Innodb fast .


Comparing the results

Through our verification, we can see that , Whether it's a primary key or a non primary key query ,MyISAM Query performance is really good Innodb fast . The query speed is different , Must be MyISAM And InnoDB What's the difference? , Now let's take a look at the difference between them .


MyISAM And InnoDB difference

 

Compare with the table above , InnoDB Doing it SELECT When , There's more to maintain than MYISAM There are a lot more engines , The factors that affect the query speed are :

 

1) Data blocks ,InnoDB To cache ,MyISAM Cache only index blocks ,   There is also a reduction in the number of in and out ;

2)InnoDB Addressing is mapped to blocks , And then to the line ,MyISAM The record is directly from the document OFFSET, Positioning is better than InnoDB Be quick

3)InnoDB It needs maintenance MVCC Agreement ; Although your scene doesn't have , But he still needs to check and maintain

MVCC (Multi-Version Concurrency Control) Multi version concurrency control  .


Index difference :

 

MyISAM And InnoDB Index storage is different ( For the index, see 《 Elaborate MySql Index principle 》).

 

InnoDB The table is expanded based on the primary key B+tree Clustered index of . MyISAM Then nonclustered index ,MyISAM The storage will have two files , One is the index file , The other is data files , The index in the index file points to the table data in the data file .

 

Clustered index is not a separate index type , It's a way of storing ,InnoDB Clustered indexes are actually stored in the same structure B+tree Index and data rows . When there is a clustered index , Its index is actually in the leaf page .

 

So let's go through 2 Pictures , We can see these two engines intuitively , The difference in index structure :

 

 

maintain MVCC

InnoDB The engine still needs to be implemented MVCC, There is also a certain loss of performance . Innodb It's through each line of record that there is 2 An extra hidden value to achieve , These two values record when this row of data is created , Another record when this row of data expires ( Or deleted ).

 

however InnoDB It doesn't store the actual time when these events happened , Instead, it only stores the system version number at the time of these events . This is a growing number as transactions are created . Each transaction records its own system version number at the beginning of the transaction . Each query must check whether the version number of each row of data is the same as that of the transaction .


Last

Through our verification, we can see that , Whether it's a primary key or a non primary key query ,MyISAM Query performance is really good Innodb fast . however MyISAM Unsupported transaction , There's a lot of trouble in implementing the business .

 

About us developing , The question of which data engine to choose . Usually in our business system ,InnoDB Data engine can meet our needs , The query performance difference between the two engines is relatively small , There's no need at all for this performance , Bring us more work . MyISAM The engine is more used to store data warehouse , Only a few queries are inserted into the business scenario , For example, system logs .

 

Welcome to your attention official account 【 Programming Fun 】, Learn more about MySQL knowledge .

 

版权声明
本文为[chingho]所创,转载请带上原文链接,感谢
https://javamana.com/2021/05/20210522120126345u.html

  1. Java back end learning route
  2. Regular automatic backup of MySQL database
  3. JQuery pictures loaded with scroll bar
  4. What should we know when learning MySQL?
  5. Java -- Analysis of container class library framework
  6. Spring type conversion (converter)
  7. Android Java abstract class
  8. The Maven project specifies the JDK version
  9. Wu Yuxiong's Python Oracle operation database (4)
  10. Oracle database exception handling and view
  11. Java reflection mechanism (2)
  12. be vigilant! MySQL becomes a new target for data blackmail
  13. Introduction to common SDK for API interface of open platforms
  14. Installing MySQL 5.7 under Linux
  15. Installation configuration and jreid test of redis3.2 under centos7
  16. Initialize the new HashMap & lt; String, String&gt;{ { put('str1':'abc');} }( (transfer)
  17. Hibernate learning notes 3.2 (hibernate build mapping)
  18. How to enable Arthas for spring boot project deployed in docker container
  19. Talking about equals and Java==
  20. Proxy mode of Java
  21. Mybatis framework (2)
  22. Basic usage of Linux nginx
  23. JQuery to add table rows, delete rows, call the date control
  24. About the workflow of mybatis and the comparison with JDBC and Hibernate
  25. JMeter HTTP get login
  26. Download and Install Apache Zookeeper on Ubuntu
  27. Java IO stream character stream FileReader, filewriter, copy of custom small array, BufferedReader, bufferedwriter, readline() and newline() methods, linenumberreader, use the specified code table to read and write characters
  28. Debugging errors encountered in Hadoop running
  29. The command of releasing memory in Linux
  30. Mybatis 3 source code analysis (3)
  31. Introduction of four switch types of rabbitmq
  32. JavaScript string (1) common functions
  33. Object oriented [day07]: introduction to class features (4)
  34. Linux DNS toolkit -- bind utils
  35. Oracle gets the first and last days of this week, this month, this quarter, and this year
  36. Install MySQL in Ubuntu
  37. Java implementation of sensitive word filtering (DFA algorithm)
  38. Image upload with spring MVC multipartfile
  39. Making Linux system installation disk under Ubuntu
  40. Introduction of Linux screen command
  41. On the problem of using ant script and maven pom.xml file in the project
  42. Environment installation of Kafka and strom clusters
  43. Zookeeper C API guide 1 (turn)
  44. Linux view system information command summary
  45. Making clock with native JavaScript
  46. Linux command line bash batch rename files
  47. Java video transcoding blog
  48. Log of MySQL 5.7 intimate parameters_ timestamps
  49. Solution to access denied when MySQL changes root password
  50. Simple dynamic string SDS of redis data structure
  51. Introduction to JavaScript classics-day02
  52. Notes on Linux learning: about environment variables
  53. Big data self taught 3-windows client dbvisualizer / squirrel configuration connection hive
  54. IOS learning - KVO design pattern
  55. Linux driver learning
  56. Main new features of Java 8
  57. Source code analysis of hashtable in Java
  58. Hadoop learning notes (2): simple operation
  59. Linux thread implementation model
  60. MySQL creates users and assigns user permissions