大访问量的情况下,可添加节点或改变架构可有效的缓解数据库压力,不过一切的原点,都是从单台mysql开始的。下面总结一些使用过或者研究过的经验,从配置以及调节索引的方面入手,对mysql进行一些优化。
第一步应该做的就是排查问题,找出瓶颈,所以,先从日志入手
开启慢查询日志
mysql>show variables like "%slow%";
查看慢查询配置,没有则在my.cnf中添加,如下
log-slow-queries = /data/mysqldata/slowquery.log #日志目录
long_query_time = 1 #记录下查询时间查过1秒
log-queries-not-using-indexes #表示记录下没有使用索引的查询
分析日志 – mysqldumpslow
分析日志,可用mysql提供的mysqldumpslow,使用很简单,参数可–help查看
# -s:排序方式。c , t , l , r 表示记录次数、时间、查询时间的多少、返回的记录数排序;
# ac , at , al , ar 表示相应的倒叙;
# -t:返回前面多少条的数据;
# -g:包含什么,大小写不敏感的;
使用实例:
mysqldumpslow -s r -t 10 /slowquery.log #slow记录最多的10个语句
mysqldumpslow -s t -t 10 -g "left join" /slowquery.log #按照时间排序前10中含有"left join"的
mysql日志分析工具–mysqlsla
mysqlsla下载安装:
wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
tar zvxf mysqlsla-2.03.tar.gz
cd mysqlsla-2.03
perl Makefile.PL
make
make install
mysqlsla /data/mysqldata/slow.log
使用实例:
mysqlsla /data/mysqldata/slow.log
# mysqlsla会自动判断日志类型,为了方便可以建立一个配置文件”~/.mysqlsla”
# 在文件里写上:top=100,这样会打印出前100条结果。
mysqlsla命令参数说明:
hackmysql.com推出的一款MySQL的日志分析工具
mysql
有一个功能就是可以log下来运行的比较慢的sql语句,默认是没有这个log的,为了开启这个功能,要修改my.cnf或者在mysql 启动的时候加入一些参数。
如果在my.cnf里面修改,需增加如下几行
long_query_time = 10
log-slow-queries =
long_query_time 是指执行超过多久的sql会被log下来,这里是10秒。
log-slow-queries 设置把日志 写在那里,为空的时候,系统会给慢查询 日志赋予主机名,并被附加slow.log 如果设置了参数log-long-format,那么所有没有使用索引的查询也将被记录。在文件my.cnf或my.ini中加入下面这一行可以记录这些查询 这是一个有用的日志。它对于性能的影响不大(假设所有查询都很快),并且强调了那些最需要注意的查询(丢失了索引或索引没有得到最佳应用)
# Time: 070927 8:08:52 # User@Host: root[root] @ [192.168.0.20] # Query_time: 372 Lock_time: 136 Rows_sent: 152 Rows_examined: 263630
select id, name from manager where id in (66,10135);
这是慢查询 日志中的一条,用了372秒,锁了136秒,返回152行,一共查了263630行
如果日志内容很多,用眼睛一条一条去看会累死,mysql 自带了分析的工具,使用方法如下:
命令行下,进入mysql /bin 目录,输入mysqldumpslow –help 或--help 可以看到这个工具的参数,主要有
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql .server startup scrīpt)
-l don't subtract lock time from total time
-s ,是order 的顺序,说明写的不够详细,俺用下来,包括看了代码,主要有
c,t,l,r 和ac,at,al,ar ,分别是按照query 次数,时间,lock 的时间和返回的记录数来排序,前面加了a 的时倒叙
-t ,是top n 的意思,即为返回前面多少条的数据
-g ,后边可以写一个正则匹配模式,大小写不敏感的
mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log
上述命令可以看出访问次数最多的20 个sql 语句和返回记录集最多的20 个sql 。
mysqldumpslow -t 10 -s t -g “left join” host-slow.log
这个是按照时间返回前10 条里面含有左连接的sql 语句
整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等.
格式说明如下:
总查询次数 (queries total), 去重后的sql数量 (unique)
输出报表的内容排序(sorted by)
最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数.
Count, sql的执行次数及占总的slow log数量的百分比.
Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.
95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.
Lock Time, 等待锁的时间.
95% of Lock , 95%的慢sql等待锁时间.
Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.
Rows examined, 扫描的行数量.
Database, 属于哪个数据库
Users, 哪个用户,IP, 占到所有用户执行的sql百分比
Query abstract, 抽象后的sql语句
Query sample, sql语句
除了以上的输出, 官方还提供了很多定制化参数, 是一款不可多得的好工具.

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


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

Atom editor mac version download
The most popular open source editor

SublimeText3 Linux new version
SublimeText3 Linux latest version

SublimeText3 Mac version
God-level code editing software (SublimeText3)

SublimeText3 English version
Recommended: Win version, supports code prompts!

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.