Mysql优化相关总结

时间:2022-08-22 14:05:27

Mysql优化相关总结

2016-05-31 数据库*

优化顺序:

  • 选择适当的引擎和表结构和数据类型

  • 建立索引,优化sql。

  • 增加缓存,redis、memcache。

  • 主从、主主,读写分离。

  • mysql自带分区表

  • 根据业务耦合垂直拆分,分布式数据库

  • 水平拆分,选择合理的sharding key。

引擎区别与选择:

  • Innodb采用聚簇索引,聚簇索引包含data。辅助索引(复合索引、前缀索引、唯一索引)存储的是主索引的值,所以查找时需要两次B-Tree搜索,读的效率稍低。但是覆盖索引和自适应的哈希索引可以一定程度上缓解这个问题(自适应哈希索引不需要用户指定,Innodb在运行时动态的根据距离的访问频率和模式为一部分页建立哈希索引,但只存在内存中,停库会丢失,重启后慢慢重新维护)。Innodb使用的是行锁,粒度更小,并发更强。并且读写之间是可以并发的,读不需要加锁,根据隔离级别不同,遇到锁时读取快照。所以读写并发是很好的。另外,Innodb相比MyIsam提供事务和外键功能。

  • MyIsam数据和索引是分开存储的,索引缓存在内存中,索引存储的是数据的磁盘地址。所以不需要多次查询B-Tree,读的性能好。但是MyIsam是表锁,只有读读之间是并发的,因此写的效率差。并且写优先级高,大量写可能导致操作饿死。

  • 综上所述:如果应用的读写操作比例非常大,或需要全文索引,那么可以使用MyIsam(5.6开始Innodb也支持全文索引)。其他情况,除了特殊情况特殊分析,推荐Innodb。

数据类型的选择:

  • char、varchar:char是固定长度的,varchar是可变长度的,所以char的处理速度要快得多。char适合长度变化不大的数据列,或要求查询速度很严苛的场景。Myisam建议用char,Innodb建议用varchar。

  • text、blob:blob可以存二进制,text只能存字符数据。可以通过合成索引来提高大文本字段的检索性能(建立一个额外的列存储大文本列数据的散列值),但这种方法只适用于精确完整的匹配查询。前缀索引也有助于查询,但查询条件不能以%开头。

  • 浮点数、定点数:float、double是浮点数,精度超出时会四舍五入。decimal、numberic是定点数,实际使用字符串存储的,所以精度更高,超出精度会警告或直接报错。所以:货币等精度敏感的数据要用定点数,因为浮点数存在误差问题。

  • 日期类型:DATETIME能存储年月日时分秒,比TIMESTAMP能表示的年份更久远。如果涉及时区问题,用TIMESTAMP。

字符集:

  • 不同的字符集可能涉及隐式的转换,而导致索引失效。

表结构:

  • 选择合适的主键(选择性,长短),不要宽表,不要太多关联,不要大字段。

  • 关于范式:

    • 第一,一个字段只存一个意义的值。{学号,性别+年龄} 。解决办法:{学号,性别,年龄}

    • 第二,如果主键是复合主键,非主键字段不能依赖主键的一部分,必须依赖全部。{学号,学科,分数,总分},分数依赖复合主键,但是总分只依赖学科一个字段,所以不符合第二范式。解决办法:{学号,学科,分数} {学科,总分}

    • 第三,不能有冗余。{学号,班级,班主任}班主任字段就会有大量冗余。解决办法:({学号,班级} {班级,班主任})

    • 范式不一定要严格遵守,要根据具体情况抉择,适当的违反有时候会带来好处更多一些。例如,

  • qq的用户表存储qq图标的点亮情况,如果每个图标用一个字段存储,那么几十个图标会使表很宽、很浪费空间。如果用位图存储所有的点亮情况,一个或者两个字段就可以。虽然违反了第一范式,但是依然是个好办法。

  • 第二、第三范式虽然保证了表的严谨,但是可能带来更多的连接。适当违反可以减少连接,特别是当分布式部署时,会省去很多麻烦。

索引的设计和使用:

  • Myisam和Innodb的索引都是BTree索引,B代表平衡树。都支持前缀索引,前缀索引有个缺点:order by和group by不能使用该索引。Myisam支持全文索引(5.6开始innodb也支持全文索引)。

  • 最适合做索引的列是出现在where子句和链接子句中的列,而不是select中的。

  • 索引列要使用短列,如果长字符列做索引,尽量用前缀索引,在最短的长度内,满足足够的选择性。

  • 不要过度索引,给插入和更新带来很大负担。

  • Innodb尽量用自增列做主索引,如果不能,也不要用大字段。

  • 最左前缀原则是BTree索引使用的首要原则,即索引条件中列的顺序,按复合索引中列的顺序,从左到右尽量覆盖,中间不能间断,且尽量以精确的“=”为条件。从左到右第一个使用范围比较的条件可以使用索引,但后面的条件列不再能使用索引。

  • 常见不能使用索引的场景:

    • 以%开头的Like查询。可以考虑用全文索引。或利用Innodb的聚簇索引,扫索引比扫表快得多。例如:一个表有主键id,辅助索引name。现在想根据name模糊搜索 name like %end%,直接select * from table where name like %end%,会引起表的全扫描,效率低下。因为Innodb每个辅助索引中存的都是主键的值,所以可以改为select * from (select id from table where name like %end%) a, table b where a.id = b.id;  这样子查询中因为id和name在辅助索引中满足了覆盖索引,只扫索引就可以拿到所有满足条件的id,然后根据id再去查询最终结果。

    • 出现类型转换、函数、运算、转码时。

    • 不包含复合索引左侧列。

    • mysql猜测扫表比使用索引快的时候。

    • or连接的条件中,每个字段必须都能用到索引,否则将全不使用索引。

事务和锁:

  • Innodb提供事务功能,Myisam不提供。

  • Innodb采用行级锁,也支持表级锁。Myisam提供表级锁。Innodb的读写并发性更好,但行级锁有出现死锁的可能。

  • Innodb的行级锁是对索引项加锁实现的,意味着不通过索引检索就会锁定所有记录,与表锁相同。行锁分为三种:

    • record lock:索引项加锁

    • gap lock:间隙锁

    • Next-key lock:前面两个的组合

  • 事务:

    • 未提交读 read uncommit

    • 已提交读 read commit : 解决脏读

    • 可重复读 repeatable read : 解决不可重复读 (默认级别)

    • 可序列化 serializable : 解决幻读

    • 更新丢失

    • 脏读

    • 不可重复读

    • 幻读

    • 原子性、一致性、持久性、隔离性

    • 并发事务处理能提高效率和资源利用率,但是也带来了问题:

    • 隔离级别:脏读、不可重复读、幻读都是一致性问题,需要隔离机制来解决。隔离机制有两种:加锁、生成一致性快照。事务隔离越严格,副作用越小,代价越大。现有4个隔离级别,可以根据业务不同进行选择:

    • 默认的隔离界别可重复读,会出现幻读的问题。select...for update可以解决幻读问题。eg:select * from data where id < 100 fro update;  会锁定id小于100的所有记录以及不存在的记录”间隙“也加锁,也级是next-key lock,所以就避免了幻读。但容易造成严重的锁等待,尽量不用,而用精确等于的条件访问更新数据。值得说明的是,当用等于条件来请求一个不存在的数据时,也会加next-key lock。

  • 死锁预防:

    • 以相同顺序访问表

    • 事务中,如果需要更新记录,应该直接申请足够级别的锁:排它锁。

  • 分布式事务:5.0.3开始支持,且只有Innodb。两段式提交,但是效率不理想。

表的拆分:

  • 垂直拆分:主码和一部分列放在一个表, 主码和另一部分放在另外一个表。好处是行更小,数据页能放更多缓存,坏处是需要管理冗余,获取全部数据需要join。

  • 水平拆分:当表很大或表中数据本来就具有独立性时。优点:降低索引层数,缺点:给应用带来复杂性。

分区表:

  • 把一个表分成多个小的部分,对应用来说是透明的。可以存储更多的数据,提高查询吞吐量。where字句包含分区条件时,可以只扫描部分分区,提高效率。sum、count等操作,可以在分区上并行进行再汇总。

  • 同一个表的所有分区必须相同引擎。

  • 分区类型:RANGE 范围,LIST 根据枚举,HASH 散列,KEY 类似hash。不能使用主键、唯一键以外的字段做分区字段。

优化手段:

  • 通过慢查询日志查看已经执行的慢语句记录。show processlist查看当前mysql正在运行的线程。

  • EXPLAIN查看慢sql的执行计划。

    • ALL:全表扫描

    • index:索引全扫描

    • range:索引范围扫描

    • ref:使用非唯一索引或唯一索引的前缀索引

    • eq-ref:使用唯一索引

    • const   system : 单表只有最多一个匹配行,可以非常迅速的找到。

    • NULL:不需要访问表或者索引。

    • select_type:select的类型。SIMPLE 简单表、PRIMARY 主查询、UNION 联合中第二个或后面的、SUBQUERY子查询。

    • table:表名

    • type:在表中找到所需行的方式。效率从低到高:

    • possible_keys:可能使用的索引

    • keys:实际使用的索引

    • key_len:使用到的索引字段的长度

    • rows:扫描行的数量

    • Extra:额外的信息

  • explain extended 加上show warnings能够看到sql真正执行前,优化器做了哪些修改。

  • show profiles可以查看当前线程每个查询。show profile for query + id(show profiles得到的),可以看每一步的耗时。还可以进一步在cpu io block等级别查看在使用什么资源时,耗时高。例如:show profile cpu for query + id。

  • 5.6提供了trace对sql进行跟踪,进一步了解优化器选择最终执行计划的原因。

    • 首先打开trace:set optimizer_trace="enabled=on",END_MARKERS_IN_JSON=on;

    • 缓存大小:set  optimizer_trace_max_mem_size=1000000;

    • 执行sql

    • select * from information_schema.optimizer_trace; 查看跟踪文件。

sql语句优化:

  • insert:如果单个客户端插入多条,尽量insert into test values(1,2),(3,4),(5,6)如此同时插入,减少交互

  • order by:btree索引是有序存储的,可以利用。所以尽量减少额外的filesort,通过索引直接返回有序数据。做法:order by与where使用相同的索引、复合索引。并且order by的字段都是升序或都是降序。 如果做不到,排序操作很多,数据较多时,适当开大sort_buffer_size让排序尽量在内存中完成,这个值是每个线程独占的,多个线程就多个buffer,注意!

  • group by:默认情况下group by c1, c2会对c1,c2...的所有字段排序,如果不需要刻意通过显示的加一个order by null禁止排序,提高效率。

  • 嵌套查询:有些情况可以使用连接代替。

  • or:保证每个列都能用到索引,会发现mysql处理时,将每个字段分别查询后进行了UNION操作。

  • 分页查询:limit1000,10 会排序出前1010行,最后只去10行。效率低。

    • 利用覆盖索引:子查询先利用覆盖索引查询到满足条件的主键,再利用主键回表查找记录。eg:select name, value from data order by name limit 1000, 10; 改为 select name, value from data a inner join (select id from data order by name limit 1000, 10) b on a.id = b.id;

    • 纪录上一次结果的最后一个排序列的值,然后:where name > lastvalue order by name limit 10; 这种方法不适合排序字段有重复值的情况,会丢纪录。

  • SQL提示:

    • USE INDEX : 让mysql参考提供的索引。eg:select * from data use index (idx_id);

    • IGNORE INDEX:忽略某索引

    • FORCE INDEX:强制使用某索引

  • 对大表的统计操作:新建临时表,讲所需数据导入临时表,再统计。好处:隔离,可以临时加字段、索引。

  • in not in  exist  not exist:

  • 技巧:

    • order by rand() limit 5;随机选出5行。

应用优化:

  • 连接建立的代价较大,应用连接池。

  • 如果表更新操作不频繁,可以利用查询缓存。一旦表发生一点更新,整个表的缓存都将失效。

  • 增加cache层。

  • 主从来分摊读写压力,但是存在延迟,需考虑。

  • 分布式数据库CLUSTER。

其他优化:

  • 定期分析表:analyze table test; 分析表可以使系统得到准确的统计信息,sql能生成更正确的执行计划。

  • 优化表:optimize table test;合并空间碎片。

  • 上述两个操作会锁表!!!

集群:

  • Mysql Cluster :

    • 节点类型:管理节点(1个)、SQL节点(应用和数据节点间的桥梁)、数据节点(存放数据,有多个镜像节点应对宕机)

  • MMM架构:

    • 双主复制架构,只有一个主提供写,另一个提供一部分读。

  • MHA架构:

    • 两部分组成:MHA Manager管理节点、MHA

Mysql优化相关总结的更多相关文章

  1. MySQL优化相关参数--先做个记录,以后可能用得到

    innodb_io_capacity:可设置的磁盘IO性能参数,越高代表当前mysql的IO性能更好,可用做决策刷脏页速度的参数: innodb_flush_neighbors:刷脏页是否开启连坐机制 ...

  2. 单表60亿记录等大数据场景的MySQL优化和运维之道

    此文是根据杨尚刚在[QCON高可用架构群]中,针对MySQL在单表海量记录等场景下,业界广泛关注的MySQL问题的经验分享整理而成,转发请注明出处. 杨尚刚,美图公司数据库高级DBA,负责美图后端数据 ...

  3. 【转】单表60亿记录等大数据场景的MySQL优化和运维之道 &vert; 高可用架构

    此文是根据杨尚刚在[QCON高可用架构群]中,针对MySQL在单表海量记录等场景下,业界广泛关注的MySQL问题的经验分享整理而成,转发请注明出处. 杨尚刚,美图公司数据库高级DBA,负责美图后端数据 ...

  4. &lbrack;转载&rsqb; 单表60亿记录等大数据场景的MySQL优化和运维之道 &vert; 高可用架构

    原文: http://mp.weixin.qq.com/s?__biz=MzAwMDU1MTE1OQ==&mid=209406532&idx=1&sn=2e9b0cc02bdd ...

  5. 单表60亿记录等大数据场景的MySQL优化和运维之道 &vert; 高可用架构

    015-08-09 杨尚刚 高可用架构 此文是根据杨尚刚在[QCON高可用架构群]中,针对MySQL在单表海量记录等场景下,业界广泛关注的MySQL问题的经验分享整理而成,转发请注明出处. 杨尚刚,美 ...

  6. MySql性能优化相关

    原来使用MySql处理的数据量比较少,小打小闹的,没有关注过性能的问题.最近要处理的数据量飙升,每天至少20W行的新增数据,导致MySql在性能方面已经是差到不可用的地步了,必须要重视MySql的优化 ...

  7. MySQL优化聊两句

    原文地址:http://www.cnblogs.com/verrion/p/mysql_optimised.html MySQL优化聊两句 MySQL不多介绍,今天聊两句该如何优化以及从哪些方面入手, ...

  8. 0104探究MySQL优化器对索引和JOIN顺序的选择

    转自http://www.jb51.net/article/67007.htm,感谢博主 本文通过一个案例来看看MySQL优化器如何选择索引和JOIN顺序.表结构和数据准备参考本文最后部分" ...

  9. MySQL优化概述

    一. MySQL优化要点 MySQL优化是一门复杂的综合性技术,主要包括: 1 表的设计合理化(符合 3NF,必要时允许数据冗余) 2.1 SQL语句优化(以查询为主) 2.2 适当添加索引(主键索引 ...

随机推荐

  1. 网页中多个图标在一张图片上,使用css将各图标显示

    现在的网页中显示很多图标算是常态,发现项目中页面上用到的图标都是单个图标单个文件,用的时候直接往页面上挂,这确实很常态. 如果,网站是挂在外网上,或者网速过低,又大量使用图标的情况下,由于浏览器和服务 ...

  2. 使用SFTP工具下载文件

    1. 打开SFTP会话 File->Connect SFTP Session  2. cd 到文件目录下 3. get 文件名称 sftp> get catalina.out 4. lpw ...

  3. 【转】随机函数 rand&lpar;&rpar; srand&lpar;&rpar; 以及seed的原理

    from:http://blog.csdn.net/feige2008/article/details/6943885   标准库<cstdlib>(被包含于<iostream&gt ...

  4. ok6410 android driver&lpar;8&rpar;

    In the past, we know how to create and run a simple character device driver on pc, goldfish and ok64 ...

  5. MIT-线性代数笔记(1-6)

    学习目录 第 01 讲 行图像和列图像 第 02 讲 矩阵消元 第 03 讲 矩阵的乘法和逆矩阵 第 04 讲 矩阵的LU 分解 第 05 讲 转置.置换和空间 第 06 讲 列空间和零空间 第 07 ...

  6. &lbrack;HAOI2010&rsqb;软件安装

    简单的tarjan+(本蒟蒻刚刚接触不久)恶心的树形DP 题面 题目描述 现在我们的手头有N个软件,对于一个软件i,它要占用Wi的磁盘空间,它的价值为Vi.我们希望从中选择一些软件安装到一台磁盘容量为 ...

  7. Android反编译获取源码-android学习之旅&lpar;70&rpar;

    上一讲我们介绍了如何获取资源文件,这一节讲解如何获取源码,其实获取源码真的很简单 首先还是要有工具,Dex2jar,这个工具用于将apk解压之后的dex文件转化为jar文件还有jd-gui的这个工具能 ...

  8. HTML学习笔记:2&period;基础语法

    HTML基本结构 HTML标签 HTML元素 HTML属性 注释 ①基本结构 <html> html:指明是个html文件 <head> <title>标题< ...

  9. gulp给文件加版本号

    版本号用文件MD5生成 默认根据文件MD5生成,因此文件未发生改变,此版本号将不会变 所以当没有改变文件的时候,我们就不能用gulp来改变版本号了 需要安装的插件 npm install --save ...

  10. 【Hadoop学习之七】Hadoop YARN

    环境 虚拟机:VMware 10 Linux版本:CentOS-6.5-x86_64 客户端:Xshell4 FTP:Xftp4       jdk8       hadoop-3.1.1 YARN: ...