search
HomeDatabaseMysql TutorialMysql优化与使用集锦_MySQL

MyISAM的性能是比Innodb强

MyISAM的索引和数据是分开的,并且索引是有压缩的  

Innodb是索引和数据是紧密捆绑的,没有使用压缩从而会造成Innodb比MyISAM体积庞大不小

MyISAM不支持外键  Innodb支持

MyISAM不支持事务  Innodb支持

MyISAM只支持表所  Innodb支持行锁

对数据信息的存储方式不同,MyISAM创建一张表对应3个文件,Innodb则只有一个文件.frm,数据存储在ibdata1

复制自己insert into tt select * from tt

--------------------------

恢复

不小心update一个表where写的范围不对,导致这个表没法正常用了,这个时候MyISAM的优越性就体现出来了,随便从当天拷贝的压缩包取出对应表的文件,随便放到一个数据库目录下,然后dump成sql再导回到主库,并把对应的binlog补上。如果是Innodb,恐怕不可能有这么快速度

--------------------------

锁表

select count(*) 和order by 是最频繁的,大概能占了整个sql总语句的60%以上的操作,而这种操作Innodb其实也是会锁表的,很多人以为Innodb是行级锁,那个只是where对它主键是有效,非主键的都会锁全表的。

-------------------------

大小

保证数据库单个实例尽量不要超过150G。

受文件系统操作限制,文件数过大需要更多文件句柄,且大目录  操作造成复制、压缩、备份效率低。

     - 打开表占用数据库资源(table_cache)

   √ 建议一个库不应超过300-400个表

   √ 建议一般带char字段的表不应超过500万rows.基于数字的字段为主的表不要超过1000万rows.


切分尽量多的小实例,一个机器跑7-8个实例,平常load avg不超过1-2,峰值不超过6-7为合理。

-------------------------

主从

通过分多个主库,便于未来可扩展

通过使用replicate_do_db(table)来解决从库追主库延迟时间较长的问题由于mysql的从库只能单进程追,而通过上述方式,就能形成多进程追不同库来减少延迟,缺点是管理成本会很大。

---------------------------

多IDC

通过多IDC提升数据库平台99.999%稳定性

---------------------------
分表
 按时间(财经)
 按ID号hash分(统一通行证)
 按业务项目(通用投票)

merge引擎

提升代码开发速度
1.
比如有些项目,需要定期存用户离线消息,可以采取程序只访问对应的merge表,然后merge表对应7个子表(比如周一到周日)。
2.
比如统计项目,可能分表策略是每个月一个表,然后要做如一季度,二季度的统计,为
了方便开发,可以采取程序只访问对应merge表,然后自由结合1234,N表作为merge表的子表。

---------------------------

索引

正确使用索引,避免全表搜索使用定长表,且定期做OPTIMIZE TABLE命令(注意这个命令会锁表,请在数据库访问小的时候做)在对大表进行添加索引,一定要选择访问小的时间段做,否则会导致严重问题。

注:一般临晨2-3点时候是大部分项目访问的低谷。

索引优化,选择实验
稳妥地改进
将需要优化的相关表复制到测试环境
在测试环境启动一个测试daemon,关闭query cache或是使用select SQL_NO_CACHE 方式。
未优化时测试若干次查询时间,以及explain检查扫描集。
选择合适的索引试验建立。可以通过use index(xx)来强制使用。检查是否有效。
测试查询时间变化,反复试验得到最优结果

保持关注,根据情况随时改变索引设置

采取从库不同索引的模式来提升性能
比如有些项目,有很多不同的排序需求,需
要建立很多索引,但是如果都加必然导致性
能下降,所以采取不同功能使用对应索引的
从库来解决。

-------------------------------------

排序

尽量使用带主键的字段做order by 的排序尽量不要多提供页面的查找(最好只提供100页内),避免机器爬虫抓取数据,导致数据库压力负载过高。因为做order by field1 limit xxxxxx,20是非常消耗数据库资源。 -------------------------------------批量通过使用insert批量的方式来提升主库的写速度,通过批量values模式都能提升主库写性能。 -------------------------------------- key-value通过简单的key-value模式数据库来处理简单逻辑业务,如berkeley DB, LightCloud, Tokyo Tyrant------------------------------------ NoSQL通过Memcache来缓冲频繁update的数据库
比如通过设定阀值500次才往数据库做一次写操作,或是间隔30分钟往数据库写一次。------------------------------------- 补丁通过使用如ebay公司开发的heap补丁来解决一些如session业务比如跑一些数据总大小不会很大,但是update特别频繁的,比如用户状态值,补丁的好处是省内存。------------------------------------ 监控进程列表mysql>show processlist; profiling该方式默认是关闭的。 可以通过以下语句查看 
mysql>select @@profiling; 
mysql>set profiling=1; //打开
执行需要测试的sql 语句:mysql> show profiles; 
通过指定的Query_ID 来查询指定的sql语句的执行信息: 
mysql> show profile for query 1; 
测试完毕以后 ,关闭参数: mysql> set profiling=0
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
How does MySQL's licensing compare to other database systems?How does MySQL's licensing compare to other database systems?Apr 25, 2025 am 12:26 AM

MySQL uses a GPL license. 1) The GPL license allows the free use, modification and distribution of MySQL, but the modified distribution must comply with GPL. 2) Commercial licenses can avoid public modifications and are suitable for commercial applications that require confidentiality.

When would you choose InnoDB over MyISAM, and vice versa?When would you choose InnoDB over MyISAM, and vice versa?Apr 25, 2025 am 12:22 AM

The situations when choosing InnoDB instead of MyISAM include: 1) transaction support, 2) high concurrency environment, 3) high data consistency; conversely, the situation when choosing MyISAM includes: 1) mainly read operations, 2) no transaction support is required. InnoDB is suitable for applications that require high data consistency and transaction processing, such as e-commerce platforms, while MyISAM is suitable for read-intensive and transaction-free applications such as blog systems.

Explain the purpose of foreign keys in MySQL.Explain the purpose of foreign keys in MySQL.Apr 25, 2025 am 12:17 AM

In MySQL, the function of foreign keys is to establish the relationship between tables and ensure the consistency and integrity of the data. Foreign keys maintain the effectiveness of data through reference integrity checks and cascading operations. Pay attention to performance optimization and avoid common errors when using them.

What are the different types of indexes in MySQL?What are the different types of indexes in MySQL?Apr 25, 2025 am 12:12 AM

There are four main index types in MySQL: B-Tree index, hash index, full-text index and spatial index. 1.B-Tree index is suitable for range query, sorting and grouping, and is suitable for creation on the name column of the employees table. 2. Hash index is suitable for equivalent queries and is suitable for creation on the id column of the hash_table table of the MEMORY storage engine. 3. Full text index is used for text search, suitable for creation on the content column of the articles table. 4. Spatial index is used for geospatial query, suitable for creation on geom columns of locations table.

How do you create an index in MySQL?How do you create an index in MySQL?Apr 25, 2025 am 12:06 AM

TocreateanindexinMySQL,usetheCREATEINDEXstatement.1)Forasinglecolumn,use"CREATEINDEXidx_lastnameONemployees(lastname);"2)Foracompositeindex,use"CREATEINDEXidx_nameONemployees(lastname,firstname);"3)Forauniqueindex,use"CREATEU

How does MySQL differ from SQLite?How does MySQL differ from SQLite?Apr 24, 2025 am 12:12 AM

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

What are indexes in MySQL, and how do they improve performance?What are indexes in MySQL, and how do they improve performance?Apr 24, 2025 am 12:09 AM

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Explain how to use transactions in MySQL to ensure data consistency.Explain how to use transactions in MySQL to ensure data consistency.Apr 24, 2025 am 12:09 AM

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

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

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools