search
HomeDatabaseMysql Tutorial新手必看:一步到位之InnoDB_MySQL

bitsCN.com

新手必看:一步到位之InnoDB

 

   前言:MySQL发展到今天,InnoDB引擎已经作为绝对的主力,除了像大数据量分析等比较特殊领域需求外,它适用于众多场景。然而,仍有不少开发者还在“执迷不悟”的使用MyISAM引擎,觉得对InnoDB无法把握好,还是MyISAM简单省事,还能支持快速COUNT(*)。本文是由于最近几天帮忙处理discuz论坛有感而发,希望能对广大开发者有帮助。    

 

   1. 快速认识InnoDB

   InnoDB是MySQL下使用最广泛的引擎,它是基于MySQL的高可扩展性和高性能存储引擎,从5.5版本开始,它已经成为了默认引擎。

   InnODB引擎支持众多特性:

a) 支持ACID,简单地说就是支持事务完整性、一致性; 

b) 支持行锁,以及类似ORACLE的一致性读,多用户并发;

c) 独有的聚集索引主键设计方式,可大幅提升并发读写性能;

d) 支持外键;    

e) 支持崩溃数据自修复;

   InnoDB有这么多特性,比MyISAM来的优秀多了,还犹豫什么,果断的切换到InnoDB引擎吧 

   2. 修改InnoDB配置选项

   可以选择官方版本,或者Percona的分支,如果不知道在哪下载,就google吧。

   安装完MySQL后,需要适当修改下my.cnf配置文件,针对InnoDB相关的选项做一些调整,才能较好的运行InnoDB。

   相关的选项有:

#InnoDB存储数据字典、内部数据结构的缓冲池,16MB 已经足够大了。

innodb_additional_mem_pool_size = 16M

 

#InnoDB用于缓存数据、索引、锁、插入缓冲、数据字典等

#如果是专用的DB服务器,且以InnoDB引擎为主的场景,通常可设置物理内存的50%

#如果是非专用DB服务器,可以先尝试设置成内存的1/4,如果有问题再调整

#默认值是8M,非常坑X,这也是导致很多人觉得InnoDB不如MyISAM好用的缘故

innodb_buffer_pool_size = 4G

#InnoDB共享表空间初始化大小,默认是 10MB,也非常坑X,改成 1GB,并且自动扩展

innodb_data_file_path = ibdata1:1G:autoextend

 

#如果不了解本选项,建议设置为1,能较好保护数据可靠性,对性能有一定影响,但可控

innodb_flush_log_at_trx_commit = 1

 

#InnoDB的log buffer,通常设置为 64MB 就足够了

innodb_log_buffer_size = 64M

 

#InnoDB redo log大小,通常设置256MB 就足够了

innodb_log_file_size = 256M

 

#InnoDB redo log文件组,通常设置为 2 就足够了

innodb_log_files_in_group = 2

 

#启用InnoDB的独立表空间模式,便于管理

innodb_file_per_table = 1

 

#启用InnoDB的status file,便于管理员查看以及监控等

innodb_status_file = 1

 

#设置事务隔离级别为 READ-COMMITED,提高事务效率,通常都满足事务一致性要求

transaction_isolation = READ-COMMITTED 

   在这里,其他配置选项也需要注意:

#设置最大并发连接数,如果前端程序是PHP,可适当加大,但不可过大

#如果前端程序采用连接池,可适当调小,避免连接数过大

max_connections = 60

#最大连接错误次数,可适当加大,防止频繁连接错误后,前端host被mysql拒绝掉

max_connect_errors = 100000

 

#设置慢查询阀值,建议设置最小的 1 秒

long_query_time = 1

 

#设置临时表最大值,这是每次连接都会分配,不宜设置过大 max_heap_table_size 和 tmp_table_size 要设置一样大

max_heap_table_size = 96M

tmp_table_size = 96M

 

#每个连接都会分配的一些排序、连接等缓冲,一般设置为 2MB 就足够了

sort_buffer_size = 2M

join_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 2M

 

#建议关闭query cache,有些时候对性能反而是一种损害

query_cache_size = 0

 

#如果是以InnoDB引擎为主的DB,专用于MyISAM引擎的 key_buffer_size 可以设置较小,8MB 已足够

#如果是以MyISAM引擎为主,可设置较大,但不能超过4G

#在这里,强烈建议不使用MyISAM引擎,默认都是用InnoDB引擎

key_buffer_size = 8M

#设置连接超时阀值,如果前端程序采用短连接,建议缩短这2个值

#如果前端程序采用长连接,可直接注释掉这两个选项,是用默认配置(8小时)

interactive_timeout = 120

wait_timeout = 120

   3. 开始使用InnoDB引擎

   修改完配置文件,即可启动MySQL。启动完毕后,在MySQL的datadir目录下,若产生以下几个文件,则表示应该可以使用InnoDB引擎了。

-rw-rw---- 1 mysql mysql 1.0G Sep 21 17:25 ibdata1

-rw-rw---- 1 mysql mysql 256M Sep 21 17:25 ib_logfile0

-rw-rw---- 1 mysql mysql 256M Sep 21 10:50 ib_logfile1

   登录MySQL后,执行命令,确认已启用InnoDB引擎:

(root:imysql.cn:Thu Oct 15 09:16:22 2009)[mysql]> show engines;

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |

+------------+---------+----------------------------------------------------------------+--------------+------+------------+

| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |

   接下来创建一个InnoDB表:

(root:imysql.cn:Thu Oct 15 09:16:22 2009)[mysql]> 

CREATE TABLE my_innodb_talbe(

id INT UNSIGNED NOT NULL AUTO_INCREMENT,

name VARCHAR(20) NOT NULL DEFAULT /'/',

passwd VARCHAR(32) NOT NULL DEFAULT /'/',

PRIMARY KEY(id),

UNIQUE KEY `idx_name`(name)

) ENGINE = InnoDB;

   有几个和MySQL(尤其是InnoDB引擎)数据表设计相关的建议,希望开发者朋友能遵循:

a) 所有InnoDB数据表都创建一个和业务无关的自增数字型作为主键,对保证性能很有帮助;

b) 杜绝使用text/blob,确实需要使用的,尽可能拆分出去成一个独立的表;

c) 时间戳建议使用 TIMESTAMP 类型存储;

d) IPV4 地址建议用 INT UNSIGNED 类型存储;

e) 性别等非是即非的逻辑,建议采用 TINYINT 存储,而不是 CHAR(1);

f) 存储较长文本内容时,建议采用JSON/BSON格式存储

 

bitsCN.com
Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
What are the differences in syntax between MySQL and other SQL dialects?What are the differences in syntax between MySQL and other SQL dialects?Apr 27, 2025 am 12:26 AM

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

What is MySQL partitioning?What is MySQL partitioning?Apr 27, 2025 am 12:23 AM

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How do you grant and revoke privileges in MySQL?How do you grant and revoke privileges in MySQL?Apr 27, 2025 am 12:21 AM

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

Explain the differences between InnoDB and MyISAM storage engines.Explain the differences between InnoDB and MyISAM storage engines.Apr 27, 2025 am 12:20 AM

InnoDB is suitable for applications that require transaction support and high concurrency, while MyISAM is suitable for applications that require more reads and less writes. 1.InnoDB supports transaction and bank-level locks, suitable for e-commerce and banking systems. 2.MyISAM provides fast read and indexing, suitable for blogging and content management systems.

What are the different types of JOINs in MySQL?What are the different types of JOINs in MySQL?Apr 27, 2025 am 12:13 AM

There are four main JOIN types in MySQL: INNERJOIN, LEFTJOIN, RIGHTJOIN and FULLOUTERJOIN. 1.INNERJOIN returns all rows in the two tables that meet the JOIN conditions. 2.LEFTJOIN returns all rows in the left table, even if there are no matching rows in the right table. 3. RIGHTJOIN is contrary to LEFTJOIN and returns all rows in the right table. 4.FULLOUTERJOIN returns all rows in the two tables that meet or do not meet JOIN conditions.

What are the different storage engines available in MySQL?What are the different storage engines available in MySQL?Apr 26, 2025 am 12:27 AM

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

What are some common security vulnerabilities in MySQL?What are some common security vulnerabilities in MySQL?Apr 26, 2025 am 12:27 AM

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

How can you identify slow queries in MySQL?How can you identify slow queries in MySQL?Apr 26, 2025 am 12:15 AM

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!