MySQL性能优化技巧分享

MySQL性能优化

在互联网公司MySQL的使用非常广泛，大家经常会有MySQL性能优化方面的需求。整理了一些在MySQL优化方面的实用技巧。

Schema与数据类型优化

  1. 整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT
  2. 完全“随机”的字符串（如：MD5()、SHA1()或者UUID()等产生的字符串）会任意分布在很大的空间内，会导致INSERT以及一些SELECT语句变的很慢
  3. 如果希望查询执行得快速且并发性好，单个查询最好不要做太多的关联查询（互联网公司非常忌讳关联查询），利用程序来完成关联操作
  4. 如果需要对一张比较大的表做表结构变更（ALTER TABLE操作增加一列），建议先拷贝一张与原表结构一样的表，再将数据复制进去，最后通过重命名将新表的表名称修改为原表的表名称。因为在变更表结构的时候很有可能会锁住整个表，并且可能会有长时间的不可用
  5. 避免多表关联的时候可以适当考虑一些反范式的建表方案，增加一些冗余字段

InnoDB索引优化

  1. 如果不是按照索引的最左列开始查找，则无法使用索引
  2. 所有的非聚簇索引都需要先通过索引定位到对应的主键，然后在到聚簇索引查找数据，所以在定义主键索引的时候一定要谨慎
  3. 只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或者正序）都一样时，MySQL才能够使用索引来对结果做排序。有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。
  4. 在使用like来匹配字符串类型的字段的值时，尽可能的使用前缀匹配like ‘XX%'，避免使用 like ‘%XX'
  5. 哈希索引是基于哈希表实现的，只有精确匹配索引所有列的查询才有效，也不遵循索引的最左匹配原则
  6. 当服务器需要对多个索引做联合操作时（通常有多个OR条件），建议修改成UNION的方式，这样方便命中索引
  7. 对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列
  8. 尽可能多的使用覆盖索引（如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引），通过EXPLAIN的Extra列可以看到“Using index”信息
  9. 当ID为主键时，创建索引(A)，相当于创建了(A)和(A, ID)两个索引
  10. 表中的索引越多对SELECT、UPDATE和DELETE操作速度变慢，同时占用的内存也会比较多
  11. InnoDB在二级索引上使用共享锁，但是访问主键索引需要排他锁
  12. 尽可能的使用WHERE IN和WHERE BETWEEN AND的方式来进行范围查询
  13. LIMIT的偏移量越大性能越慢
  14. 编写查询语句时应该避免单行查找、尽可能的使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询

查询性能优化

1.对于低效的查询，通常从两个方面来分析：

  1. 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候可能是访问了太多的列
  2. 确认MySQL服务器层是否在分析大量超过需要的数据行

2.一般MySQL能够使用以下三种方式应用WHERE条件，从好到坏依次为：

  • 在索引中使用WHERE条件俩过滤不匹配的记录
  • 使用索引覆盖扫描来返回记录
  • 从数据表中返回数据，然后过滤不满足条件的记录

3.MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。在一个通用服务器上，也能够运行每秒超过10万的查询，一个千兆网卡也能轻松满足每秒超过2000次的查询，MySQL内部每秒能够扫描内存中上百万行数据

4.在删除大量数据时，建议每次删除一小批量数据后，暂停一会儿再做下一次的删除

5.无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序

6.COUNT()函数有两种不同的作用：它可以统计某个列值的数量，也可以统计行数。最简单的就是通过COUNT(*)来统计行数

7.关联查询的时候要确保关联的字段上有索引

8.在数据量很大并且历史数据需要定期删除的情况下，可以考虑使用分区表

9.如果定了的索引列和分区列不匹配，会导致查询无法进行分区过滤

10.外键约束尽可能避免，通常通过程序来实现，心中要有外键

11.触发器、存储过程、自定义函数等最好不要使用

12.尽可能的利用查询缓存，如果在写查询语句的时候有一些不确定的数据（NOW()或者CURRENT_DATE()等）时，则不会被缓存

13.用多个小表代替一个大表对查询缓存有好处

14.批量写入时只需要做一次缓存失效，所以相比单条写入（每写入一次，缓存就失效）效率更好，对于写密集型的应用，直接禁用查询缓存

15.如果缓存的空间太大，在过期操作的时候可能会导致服务器僵死

以上是个人在工作中的经验总结，如果有描述错误的地方希望大家可以帮忙指出，一起交流学习！

到此这篇关于MySQL性能优化技巧分享的文章就介绍到这了,更多相关MySQL性能优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL中聚合函数count的使用和性能优化技巧

    本文的环境是Windows 10,MySQL版本是5.7.12-log 一. 基本使用 count的基本作用是有两个: 统计某个列的数据的数量: 统计结果集的行数: 用来获取满足条件的数据的数量.但是其中有一些与使用中印象不同的情况,比如当count作用一列.多列.以及使用*来表达整行产生的效果是不同的. 示例表如下: CREATE TABLE `NewTable` ( `id` int(11) NULL DEFAULT NULL , `name` varchar(30) NULL DEFAUL

  • MYSQL开发性能研究之批量插入数据的优化方法

    MYSQL开发性能研究之批量插入数据的优化方法

    一.我们遇到了什么问题 在标准SQL里面,我们通常会写下如下的SQL insert语句. INSERT INTO TBL_TEST (id) VALUES(1); 很显然,在MYSQL中,这样的方式也是可行的.但是当我们需要批量插入数据的时候,这样的语句却会出现性能问题.例如说,如果有需要插入100000条数据,那么就需要有100000条insert语句,每一句都需要提交到关系引擎那里去解析,优化,然后才能够到达存储引擎做真的插入工作. 正是由于性能的瓶颈问题,MYSQL官方文档也就提到了使用批

  • mysql查询时offset过大影响性能的原因和优化详解

    mysql查询时offset过大影响性能的原因和优化详解

    前言 mysql查询使用select命令,配合limit,offset参数可以读取指定范围的记录.本文将介绍mysql查询时,offset过大影响性能的原因及优化方法. 准备测试数据表及数据 1.创建表 CREATE TABLE `member` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL COMMENT '姓名', `gender` tinyint(3) unsigned NOT NU

  • MySQL性能优化之如何高效正确的使用索引

    实践是检验真理的唯一途径,本篇只是站在索引使用的全局来定位的,你只需要通读全篇并结合具体的例子,或回忆以往使用过的地方,对整体有个全面认识,并理解索引是如何工作的,就可以了.在后续使用索引,或者优化索引时,可以从这些方面出发,进一步来加深对索引正确高效的使用. 一.索引失效 索引失效,是一个老生常谈的话题了.只要提到数据库优化.使用索引,都能一口气说出一大堆索引失效的场景,什么不能用.什么不该用这类的话,在此,我就不再一一罗列啰嗦了. 索引失效,是指表中有字段创建了索引,由于sql语句书写不当导

  • MySQL性能优化神器Explain的基本使用分析

    简介 MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化. EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 Explain 就可以了, 例如: EXPLAIN SELECT * from user_info WHERE id < 300; 准备 为了接下来方便演示 EXPLAIN 的使用, 首先我们需要建立两个测试用的表, 并添加相应的数据: CREATE TABLE `user

  • mysql千万级数据分页查询性能优化

    mysql数据量大时使用limit分页,随着页码的增大,查询效率越低下. 实验 1.直接使用用limit start, count分页语句: select * from order limit start, count 当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下: select * from order limit 10, 20 0.016秒 select * from order limit 100, 20

  • MySQL数据库查询性能优化策略

    优化查询 使用Explain语句分析查询语句 Explain 用来分析 SELECT 查询语句,开发人员可以通过分析 Explain 结果来优化查询语句. 通过对查询语句的分析,可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询语句. 使用索引查询 MySql中提高性能的一个最有效的方式就是对数据表设计合理的索引. 索引提供了高效访问数据的方法,并且加快查询速度. 如果查询时没有使用索引,那么查询语句将扫描表中所有的记录.在数据量大的时候,这样查询速度会很慢. 使用索引进行查询,查

  • MySQL Limit性能优化及分页数据性能优化详解

    MySQL Limit可以分段查询数据库数据,主要应用在分页上.虽然现在写的网站数据都是千条级别,一些小的的优化起的作用不大,但是开发就要做到极致,追求完美性能.下面记录一些limit性能优化方法. Limit语法: SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数.LIMIT接受一个或两个数字参数.参数必须是一个整数常量. 如果给定两个参数,第一个参数指定

  • Mysql高性能优化技能总结

    数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来) 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀,备份表必须以bak_为前缀并以日期(时间戳)为后缀 所有存储相同数据的列名和列类型必须一致(一般作为关联列,如果查询时关联列类型不一致会自动进行数据类型隐式转换,会造成列上的索引失效,导致查询效率降低) 数据库基本设计规范 1

  • 通过MySQL慢查询优化MySQL性能的方法讲解

    通过MySQL慢查询优化MySQL性能的方法讲解

    随着访问量的上升,MySQL数据库的压力就越大,几乎大部分使用MySQL架构的web应用在数据库上都会出现性能问题,通过mysql慢查询日志跟踪有问题的查询非常有用,可以分析出当前程序里有很耗费资源的sql语句. 慢查询日志我们可以通过my.cnf文件设置开启,下面先来看一下相关参数的意义 log-slow-queries <slow_query_log_file> 存放slow query日志的文件.你必须保证mysql server进程mysqld_safe进程用户对该文件有w权限. lo

随机推荐