Mysql FAQ
- 2020-06-01 11:09:05
- OfStack
1, the difference between utf8_bin and utf8_general_ci
ci is case insensitive, "case insensitive ", a and A will be treated as 1 in character judgment;
bin is in base 2. a and A are treated differently.
For example, if you run:
SELECT * FROM table WHERE txt = 'a'
In utf8_bin you will not find the 1 line where txt = 'A', but utf8_general_ci will.
2, MyISAM and InnoDB
InnoDB and MyISAM are the two table types most commonly used by many people when using MySQL, and each has its advantages and disadvantages, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. Tables of type MyISAM emphasize performance and are faster to execute than those of type InnoDB, but do not provide transaction support, whereas InnoDB provides transaction support and advanced database functions such as external keys.
Here are some details and implementation differences:
◆1.InnoDB does not support indexes of type FULLTEXT.
When select count(*) from table is executed, InnoDB scans the entire table once to calculate the number of rows, but MyISAM simply reads out the number of saved rows. Note that when the count(*) statement contains the where condition, the operations for both tables are identical.
◆3. For fields of type AUTO_INCREMENT, InnoDB must contain an index for only this field, but in the MyISAM table, it is possible to set up a joint index with other fields 1.
When DELETE FROM table, InnoDB will not re-establish the table, but delete 1 row and 1 row.
The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to MyISAM table and then to InnoDB table after importing the data, but it does not work for tables that use additional InnoDB features (such as foreign keys).
In addition, the row lock of InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing an SQL statement, InnoDB table will also lock the full table, such as update table set num=1 where name like "%aaa%".
The main difference between the two types is that Innodb supports transaction processing and foreign key and row-level locking, while MyISAM does not, so it is easy to assume that MyISAM is only suitable for small projects.
As a user of MySQL, I prefer Innodb and MyISAM, but MyISAM is definitely my first choice for my current operation and maintenance database platform to meet the requirements of 99.9% stability, convenient scalability and high availability.
Here's why:
1. First of all, most of the projects I currently carry on the platform are those that read more and write less, while the reading performance of MyISAM is much better than that of Innodb.
2. The index of MyISAM is separated from the data, and the index is compressed, so the memory utilization rate is improved a lot. While it can load more indexes, Innodb is tightly bound with data. Without compression, Innodb is larger than MyISAM.
3, from the perspective of platform, often occurs between 1, 2 months application developers accidentally update1 table where write wrong, lead to this table can't normal use, MyISAM superiority will show up at this time, just copy from the package to take out the corresponding table files, literally in a database directory, and then dump into sql lead back to the main library, and the corresponding binlog. If it is Innodb, I'm afraid it is impossible to have such a fast speed. Don't tell me to ask Innodb to regularly use the export xxx.sql mechanism for backup, because the data volume of the smallest database instance on my platform is basically several 10G.
4. According to the application logic I have contacted, select count(*) and order by are the most frequent operations, accounting for more than 60% of the total sql statements. In fact, Innodb can lock the table, and many people think that Innodb is a row-level lock.
5, and there are often many application department I need to give them some tables of data on a regular basis, MyISAM words is very convenient, as long as you send them correspond to the table of frm. MYD, MYI files, let them in the corresponding version of the database is started, and Innodb requires export xxx. sql, because light to others files, influenced by dictionary data file, the other party is unable to use.
6. If MyISAM is better than insert in write operation, Innodb is not as good as MyISAM in write operation. If update is used for index-based update operation, MyISAM may be worse than Innodb in write operation.
7. If MyISAM is used, merge engine can greatly speed up the development of application departments. They only need to do some select count(*) operations on this merge table, which is very suitable for a certain type of rows (such as log, survey statistics) business table with a total amount of hundreds of millions of large projects.
Of course, Innodb is not absolutely unnecessary. I use Innodb for transaction projects such as stock trading project simulation. When there are more than 200,000 active users, it is also very easy to deal with.
In addition, one could argue you MyISAM cannot too much resistance to write operation, but I can through the architecture to make up for, say that I use existing database platform capacity: master-slave data amount in more than a few hundred T, every day more than 10 pv dynamic pages, there are several big projects data interface, by calling is not counted in pv, total (including a large project for early memcached deployment, leading to a single database query processing 90 million) every day. The average load of my overall database server is around 0.5-1.
3. What is the difference between Utf8_general_ci and utf8_unicode_ci
utf8_unicode_ci proofreading rules only partially support the Unicode proofreading rules algorithm. Some characters are still not supported.
utf8_unicode_ci does not fully support combined tokens.
utf8_general_ci is a legacy proofreading rule that does not support extensions and can only be compared character by character. This means that the comparison of the utf8_general_ci proofreading rule is fast, but less accurate than the proofreading rule using utf8_unicode_ci.
Differences in application
1. For a language, the utf8 character set correction rules related to the specific language are only implemented if the utf8_unicode_ci sorting is not good. For example, for German and French, utf8_unicode_ci works very well, so there is no need to create special utf8 proofreading rules for both languages.
2. utf8_general_ci is also applicable to German and French, except for '? 'is equal to' s', not outside of 'ss'. If your application accepts this, you should use utf8_general_ci because it is fast. Otherwise, use utf8_unicode_ci, as it is more accurate.
utf8_unicode_ci is more accurate, utf8_general_ci is faster. Generally, the accuracy of utf8_general_ci is enough for us to use, after I have looked at a lot of program source code, I found that most of them are also used is utf8_general_ci, so when creating a new database 1 generally choose utf8_general_ci can be
Reference:
http://www.diybl.com/course/7_databases/mysql/myxl/20100721/474574.html
English: http: / / hi. baidu. com/my_labs/blog/item/ea1a578360dc82ab0cf4d2c0 html
English: http: / / stackoverflow com/questions / 766809 / whats - the - difference - between - utf8 - general - ci - and - utf8 unicode -- ci
4, the table name contains special characters
It's better not to use the special notation book_id so you don't have to use the quotes
ci is case insensitive, "case insensitive ", a and A will be treated as 1 in character judgment;
bin is in base 2. a and A are treated differently.
For example, if you run:
SELECT * FROM table WHERE txt = 'a'
In utf8_bin you will not find the 1 line where txt = 'A', but utf8_general_ci will.
2, MyISAM and InnoDB
InnoDB and MyISAM are the two table types most commonly used by many people when using MySQL, and each has its advantages and disadvantages, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does. Tables of type MyISAM emphasize performance and are faster to execute than those of type InnoDB, but do not provide transaction support, whereas InnoDB provides transaction support and advanced database functions such as external keys.
Here are some details and implementation differences:
◆1.InnoDB does not support indexes of type FULLTEXT.
When select count(*) from table is executed, InnoDB scans the entire table once to calculate the number of rows, but MyISAM simply reads out the number of saved rows. Note that when the count(*) statement contains the where condition, the operations for both tables are identical.
◆3. For fields of type AUTO_INCREMENT, InnoDB must contain an index for only this field, but in the MyISAM table, it is possible to set up a joint index with other fields 1.
When DELETE FROM table, InnoDB will not re-establish the table, but delete 1 row and 1 row.
The LOAD TABLE FROM MASTER operation does not work for InnoDB. The solution is to first change the InnoDB table to MyISAM table and then to InnoDB table after importing the data, but it does not work for tables that use additional InnoDB features (such as foreign keys).
In addition, the row lock of InnoDB table is not absolute. If MySQL cannot determine the range to be scanned when executing an SQL statement, InnoDB table will also lock the full table, such as update table set num=1 where name like "%aaa%".
The main difference between the two types is that Innodb supports transaction processing and foreign key and row-level locking, while MyISAM does not, so it is easy to assume that MyISAM is only suitable for small projects.
As a user of MySQL, I prefer Innodb and MyISAM, but MyISAM is definitely my first choice for my current operation and maintenance database platform to meet the requirements of 99.9% stability, convenient scalability and high availability.
Here's why:
1. First of all, most of the projects I currently carry on the platform are those that read more and write less, while the reading performance of MyISAM is much better than that of Innodb.
2. The index of MyISAM is separated from the data, and the index is compressed, so the memory utilization rate is improved a lot. While it can load more indexes, Innodb is tightly bound with data. Without compression, Innodb is larger than MyISAM.
3, from the perspective of platform, often occurs between 1, 2 months application developers accidentally update1 table where write wrong, lead to this table can't normal use, MyISAM superiority will show up at this time, just copy from the package to take out the corresponding table files, literally in a database directory, and then dump into sql lead back to the main library, and the corresponding binlog. If it is Innodb, I'm afraid it is impossible to have such a fast speed. Don't tell me to ask Innodb to regularly use the export xxx.sql mechanism for backup, because the data volume of the smallest database instance on my platform is basically several 10G.
4. According to the application logic I have contacted, select count(*) and order by are the most frequent operations, accounting for more than 60% of the total sql statements. In fact, Innodb can lock the table, and many people think that Innodb is a row-level lock.
5, and there are often many application department I need to give them some tables of data on a regular basis, MyISAM words is very convenient, as long as you send them correspond to the table of frm. MYD, MYI files, let them in the corresponding version of the database is started, and Innodb requires export xxx. sql, because light to others files, influenced by dictionary data file, the other party is unable to use.
6. If MyISAM is better than insert in write operation, Innodb is not as good as MyISAM in write operation. If update is used for index-based update operation, MyISAM may be worse than Innodb in write operation.
7. If MyISAM is used, merge engine can greatly speed up the development of application departments. They only need to do some select count(*) operations on this merge table, which is very suitable for a certain type of rows (such as log, survey statistics) business table with a total amount of hundreds of millions of large projects.
Of course, Innodb is not absolutely unnecessary. I use Innodb for transaction projects such as stock trading project simulation. When there are more than 200,000 active users, it is also very easy to deal with.
In addition, one could argue you MyISAM cannot too much resistance to write operation, but I can through the architecture to make up for, say that I use existing database platform capacity: master-slave data amount in more than a few hundred T, every day more than 10 pv dynamic pages, there are several big projects data interface, by calling is not counted in pv, total (including a large project for early memcached deployment, leading to a single database query processing 90 million) every day. The average load of my overall database server is around 0.5-1.
3. What is the difference between Utf8_general_ci and utf8_unicode_ci
utf8_unicode_ci proofreading rules only partially support the Unicode proofreading rules algorithm. Some characters are still not supported.
utf8_unicode_ci does not fully support combined tokens.
utf8_general_ci is a legacy proofreading rule that does not support extensions and can only be compared character by character. This means that the comparison of the utf8_general_ci proofreading rule is fast, but less accurate than the proofreading rule using utf8_unicode_ci.
Differences in application
1. For a language, the utf8 character set correction rules related to the specific language are only implemented if the utf8_unicode_ci sorting is not good. For example, for German and French, utf8_unicode_ci works very well, so there is no need to create special utf8 proofreading rules for both languages.
2. utf8_general_ci is also applicable to German and French, except for '? 'is equal to' s', not outside of 'ss'. If your application accepts this, you should use utf8_general_ci because it is fast. Otherwise, use utf8_unicode_ci, as it is more accurate.
utf8_unicode_ci is more accurate, utf8_general_ci is faster. Generally, the accuracy of utf8_general_ci is enough for us to use, after I have looked at a lot of program source code, I found that most of them are also used is utf8_general_ci, so when creating a new database 1 generally choose utf8_general_ci can be
Reference:
http://www.diybl.com/course/7_databases/mysql/myxl/20100721/474574.html
English: http: / / hi. baidu. com/my_labs/blog/item/ea1a578360dc82ab0cf4d2c0 html
English: http: / / stackoverflow com/questions / 766809 / whats - the - difference - between - utf8 - general - ci - and - utf8 unicode -- ci
4, the table name contains special characters
select * from books where `book-id` = 'b001' Notice it's on the number pad 1 On the side of the ` Not single quotation marks
select * from `book-cate`
It's better not to use the special notation book_id so you don't have to use the quotes