9 MySQL数据库性能优化ppt课件数据库原理与应用 .pptx
《9 MySQL数据库性能优化ppt课件数据库原理与应用 .pptx》由会员分享,可在线阅读,更多相关《9 MySQL数据库性能优化ppt课件数据库原理与应用 .pptx(46页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、9 MySQL 数据库性能优化教学课件数据库原理与应用 数据库原理与应用9.1优化简介数据库原理与应用3数据库原理与应用 MySQL 数据库性能优化是数据库管理员和数据库开发人员的必备技能 性能优化的目的是为了使MySQL 数据库运行速度更快、占用的磁盘空间更小。例如,通过优化文件系统提高磁盘I/O 的读写速度,通过优化操作系统调度策略提高MySQL 在高负荷情况下的负载能力,优化表结构、索引、查询语句等使查询响应更快。数据库原理与应用SHOW STATUS LIKE value;查询MySQL 数据库的性能参数【说明】value 是要查询的参数值 Connections:连接MySQL 服务
2、器的次数。Uptime:MySQL 服务器的上线时间。Slow_queries:慢查询的次数。Com_select:查询操作的次数。Com_insert:插入操作的次数。Com_update:更新操作的次数。Com_delete:删除操作的次数。数据库原理与应用【例9-1】查询MySQL 服务器的慢查询次数。SHOW STATUS LIKE slow_queries;慢查询次数参数可以结合慢查询日志找出慢查询语句,然后针对慢查询语句进行表结构优化或者查询语句优化。9.2优化查询数据库原理与应用7数据库原理与应用9.2.1 分析查询语句的执行计划 执行计划是SQL 语句调优的一个重要依据。查看S
3、QL 语句的查询执行计划(QEP)使用EXPLAIN 语句和DESCRIBE 语句,通过此语句的输出结果能够了解到MySQL 优化器是如何执行SQL 语句的,提供重要的信息来帮助做出调优决策。MySQL5.6.3 及之后的版本对SELECT、DELETE、INSERT和UPDATE 语句都可以生成执行计划。数据库原理与应用EXPLAIN SELECT 语句;【例9-2】使用EXPLAIN 语句分析简单的查询语句。1.EXPLAIN语句USE fruitsales;EXPLAIN SELECT*FROM suppliers WHERE s_id IN(101,102,103);【说明】(1)id
4、:SELECT 识别符,是SELECT 查询序列号。数据库原理与应用(2)select_type:表示SELECT 语句的类型,它的常用取值:SIMPLE 表示简单查询,其中不包括连接查询和子查询。PRIMARY 表示主查询,或者是最外层的查询语句。UNION 表示连接查询的第二个或后面的查询语句。UNION RESULT 表示一系列定义在UNION 语句中表的返回结果,其对应的table 列的值为,表示匹配的id 行是这个集合的一部分。【例9-3】使用EXPLAIN 语句分析带有UNION 的查询语句。EXPLAIN SELECT*FROM suppliers WHERE s_call=11
5、111 UNION SELECT*FROM suppliers WHERE s_call=22222;数据库原理与应用(3)table:表示查询的表。(4)partitions:表示分区表的分区情况,非分区表该列值为NULL(5)type:表示MySQL 在表中找到所需行的方式,下面按照性能由最差到最好的顺序给出常见类型。ALL:全表扫描,MySQL 将进行全表扫描。index:索引全扫描,MySQL 将遍历整个索引来查询匹配的行,index 与ALL 的区别为index 类型只遍历索引树。range:只检索给定范围的行,使用一个索引来选择行。数据库原理与应用 index_subquery:表
6、示子查询中使用了普通索引。unique_subquery:表示子查询中使用了UNIQUE 或者PRIMARY KEY。ref:表示多表查询时,后面的表使用了普通索引。eq_ref:表示多表连接时,后面的表使用了UNION 或者PRIMARY KEY。const:表示表中有多条记录,但只从表中查询一条记录。system:该表是仅有一行的系统表。数据库原理与应用(6)possible_keys:表示查询中可能使用的索引。(7)key:表示查询使用到的索引。(8)key_len:表示索引字段的长度。(9)ref:表示使用哪个列或常数与索引一起来查询记录。(10)rows:表示查询的行数。(11)fi
7、ltered:表示针对表里符合条件的记录数的百分比。(12)Extra:表示MySQL 在处理查询时的详细信息。数据库原理与应用【例9-4】使用EXPLAIN 语句分析带有子查询的查询语句。CREATE UNIQUE INDEX name_idx ON suppliers(s_name);EXPLAIN SELECT*FROM suppliers WHERE s_id=(SELECT s_id FROM suppliers WHERE s_name=ACME);数据库原理与应用DESCRIBE|DESC SELECT 语句;【例9-5】使用DESCIBE 语句分析一个查询语句。2.DESCRI
8、BE语句DESCRIBE SELECT*FROM fruits WHERE f_name=apple;DESCRIBE 语句的使用方法与EXPLAIN 语句是一样的,分析结果也是一样。数据库原理与应用9.2.2 优化查询基本原则 尽可能对每一条运行在数据库中的SQL 语句进行EXPLAIN。尽量少使用JOIN。MySQL 的优势在于简单,但是在某些方面其实也是劣势。对于复杂的多表JOIN,一方面由于优化器受限,另一方面JOIN的性能表现距离其它的关系数据库(例如Oracle)有一定的差距。尽量少排序。排序操作会消耗较多的CPU 资源。尽量避免使用SELECT*查询。大多数情况下,SELECT
9、子句中的字段的多少并不会影响到读取的数据。但是当存在ORDER BY 操作时,SELECT 子句中的字段多少在很大的程度上就影响到了排序效率。尽量用JOIN 代替子查询。虽然JOIN 的性能并不是特别好,但是和MySQL 的子查询相比,它还是具有非常大的优势的。数据库原理与应用 尽量少使用OR关键字。当WHERE 子句中存在多个条件以“或”并存时,MySQL 的优化器并没有很好地解决其执行计划优化问题,这时使用UNION ALL 或UNION 的方式来代替OR会得到更好的效果。尽量使用UNION ALL 代替UNION。UNION 和UNION ALL 的差异主要是前者需要将两个或多个查询结果
10、集合并后再进行唯一性过滤操作,这会涉及排序增加大量的CPU 运算,加大资源消耗和延迟。所以当确认结果集中不可能出现重复结果或不在乎重复结果时,应尽量使用UNION ALL 而不是UNION。尽量早过滤。使用这一原则优化一些JOIN 的SQL 语句,这样可以尽可能多地减少不必要的I/O 操作,大大节省I/O 操作所消耗的时间。避免“类型转换”。这里的“类型转换”是指WHERE 子句中出现的字段类型和所给的值类型不一致时发生的类型转换。数据库原理与应用 优先优化高并发的SQL 语句。从破坏性的角度来说,高并发的SQL语句总会比使用频率低的SQL 语句破坏性大,因为高并发的SQL 语句一旦出现问题,
11、甚至不给用户任何喘息的机会就会将系统压跨。而对于一些需要消耗大量I/O 且响应很慢的SQL 语句,由于频率低,即使遇到大多情况下就是使整个系统响应慢一点,但至少会给用户喘息的机会。从全局出发优化,而不是片面调整。SQL 优化不能是单独针对某一个进行,而是应该充分考虑系统中所有的SQL 语句,尤其是在通过调整索引优化SQL 的执行计划时,千万不能顾此失彼,因小失大。数据库原理与应用9.2.3 索引对查询速度的影响 MySQL 中提高性能的一个最有效的方式就是对数据表设计合理的索引。【例9-6】查询语句中不使用索引和使用索引的对比EXPLAIN SELECT*from fruits WHERE f
12、_name=apple;CREATE INDEX fname_idx ON fruits(f_name);EXPLAIN SELECT*from fruits WHERE f_name=apple;数据库原理与应用1.使用LIKE 关键字的查询语句【例9-7】查询语句中使用LIKE 关键字,并且匹配的字符串中包含有“%”的两种查询情况比较。EXPLAIN SELECT*from fruits WHERE f_name LIKE%e;EXPLAIN SELECT*from fruits WHERE f_name LIKE a%;如果匹配字符串的第一个字符为“%”,索引不会起作用。数据库原理与应用
13、2.使用多列索引的查询语句【例9-8】下面在fruits 表的s_id 和f_name 两个字段上创建多列索引,然后验证多列索引的使用情况。DROP INDEX fname_idx ON FRUITS;#删除例9-6 创建的索引 CREATE INDEX sid_fname_idx ON fruits(s_id,f_name);EXPLAIN SELECT*FROM fruits WHERE s_id=107;只有查询条件中使用了索引字段中的第一个字段时,索引才会被使用。EXPLAIN SELECT*from fruits WHERE f_name=apple;数据库原理与应用3.使用OR关键
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL数据库性能优化ppt课件数据库原理与应用 MySQL 数据库 性能 优化 ppt 课件 原理 应用
限制150内