MySQL双写缓冲实现原理及性能优化实战 MySQL双写缓冲实现原理及性能优化实战在MySQL数据库中，当执行写操作（例如插入、更新或删除）时，会将数据先写入到内存缓冲区中，然后再异步地将数据写入到磁盘中，这就是MySQL的写操作延迟特性。然而，这种延迟特性可能会导致数据丢失的风险，而MySQL的双写缓冲机制可以有效地避免这种风险。双写缓冲的原理双写缓冲是MySQL引入的一种机制，用来保证数据库的数据一致性。该机制的原理是在每次写操作时，将数据先写入到一个内存缓冲区的重做日志（redo log）中，然后再异步地将数据写入到磁盘中的数据文件。当发生宕机等故障情况时，MySQL可以通过重做日志来恢复数据的一致性。双写缓冲机制可以避免直接在磁盘上进行写操作造成的数据丢失风险，因为即使在写操作过程中发生故障，数据依然可以从重做日志中恢复。不过需要注意的是，双写缓冲机制并不能完全杜绝数据丢失的可能性，因为如果出现内存故障或重做日志丢失等情况，仍然会发生数据丢失。启用双写缓冲在MySQL中，启用双写缓冲是非常简单的，只需要在配置文件中添加以下参数：[mysqld] innodb_doublewrite=1这样，MySQL就会自动开启双写缓冲机制。性能优化实战尽管双写缓冲可以提高数据的一致性，但同时也会带来一定的性能开销。因此，在实际应用中，需要根据具体情况进行性能优化。下面介绍一些常见的性能优化策略。调整重做日志大小 重做日志的大小对性能有直接影响。如果重做日志的大小设置过小，可能会导致频繁的写入磁盘操作，降低性能；如果重做日志的大小设置过大，可能会占用过多的内存资源。因此，需要根据实际情况调整重做日志的大小。可以通过设置innodb_log_file_size参数来调整重做日志的大小。分离重做日志文件 在MySQL中，重做日志是以文件（默认为ib_logfile0和ib_logfile1）的形式存在的。如果将重做日志文件分离到不同的磁盘上，可以提高写入性能。可以通过设置innodb_log_group_home_dir参数来将重做日志文件存放在不同的目录中。数据库磁盘和内存的协调 对于大规模的写入操作，可以通过调整innodb_flush_log_at_trx_commit参数的值来优化性能。将其设置为0可以提高写入性能，但可能会增加数据丢失风险；将其设置为1可以保证数据的一致性，但会降低写入性能；将其设置为2则可以在每秒刷新重做日志，从而较好地平衡性能和数据一致性。总结：MySQL的双写缓冲机制可以有效地保证数据库的数据一致性。通过启用双写缓冲，可以避免直接在磁盘上进行写操作造成的数据丢失风险。不过，在实际应用中，需要根据具体情况进行性能优化。通过调整重做日志大小、分离重做日志文件以及协调数据库磁盘和内存的使用，可以提高MySQL的写入性能。代码示例：-- 创建测试表 CREATE TABLE test ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) ) ENGINE=InnoDB; -- 开启双写缓冲 SET GLOBAL innodb_doublewrite = 1; -- 插入数据 INSERT INTO test (name) VALUES ('John');以上是关于MySQL双写缓冲实现原理及性能优化实战的介绍。通过了解双写缓冲的原理和启用方法，并结合适当的性能优化策略，可以提高MySQL的写入性能和数据一致性。

史上最全的MySQL高性能优化实战总结！ 史上最全的MySQL高性能优化实战总结！1.1 前言MySQL对于很多Linux从业者而言，是一个非常棘手的问题，多数情况都是因为对数据库出现问题的情况和处理思路不清晰。在进行MySQL的优化之前必须要了解的就是MySQL的查询过程，很多的查询优化工作实际上就是遵循一些原则让MySQL的优化器能够按照预想的合理方式运行而已。今天给大家体验MySQL的优化实战，助你高薪之路顺畅。图 - MySQL查询过程1.2 优化的哲学优化有风险，涉足需谨慎1.2.1 优化可能带来的问题优化不总是对一个单纯的环境进行，还很可能是一个复杂的已投产的系统。优化手段本来就有很大的风险，只不过你没能力意识到和预见到！任何的技术可以解决一个问题，但必然存在带来一个问题的风险！对于优化来说解决问题而带来的问题,控制在可接受的范围内才是有成果。保持现状或出现更差的情况都是失败！1.2.2 优化的需求稳定性和业务可持续性,通常比性能更重要！优化不可避免涉及到变更，变更就有风险！优化使性能变好，维持和变差是等概率事件！切记优化,应该是各部门协同，共同参与的工作，任何单一部门都不能对数据库进行优化！所以优化工作,是由业务需要驱使的！！！1.2.3 优化由谁参与 在进行数据库优化时，应由数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等，业务相关人员共同参与。1.3 优化思路1.3.1 优化什么 在数据库优化上有两个主要方面：即安全与性能。安全 ---\> 数据可持续性性能 ---\> 数据的高性能访问1.3.2 优化的范围有哪些存储、主机和操作系统方面:主机架构稳定性I/O规划及配置Swap交换分区OS内核参数和网络问题应用程序方面:应用程序稳定性SQL语句性能串行访问资源性能欠佳会话管理这个应用适不适合用MySQL数据库优化方面:内存数据库结构(物理&逻辑)实例配置说明：不管是在，设计系统，定位问题还是优化，都可以按照这个顺序执行。1.3.3 优化维度数据库优化维度有四个:硬件、系统配置、数据库表结构、SQL及索引优化选择优化成本:硬件>系统配置>数据库表结构>SQL及索引优化效果:硬件<系统配置<数据库表结构<SQL及索引1.4 优化工具有啥？1.4.1 数据库层面检查问题常用工具msyqladmin       #mysql客户端，可进行管理操作 mysqlshow        #功能强大的查看shell命令 show [SESSION | GLOBAL] variables  #查看数据库参数信息 SHOW [SESSION | GLOBAL] STATUS     #查看数据库的状态信息 information_schema                 #获取元数据的方法S SHOW ENGINE INNODB STATUS Innodb   #引擎的所有状态 SHOW PROCESSLIST    #查看当前所有连接session状态 explain             #获取查询语句的执行计划s how index           #查看表的索引信息 slow-log            #记录慢查询语句 mysqldumpslow       #分析slowlog文件的 不常用但好用的工具zabbix            #监控主机、系统、数据库（部署zabbix监控平台） pt-query-digest   #分析慢日志 mysqlslap         #分析慢日志 sysbench          #压力测试工具 mysql profiling             #统计数据库整体状态工具  Performance Schema mysql    #性能状态统计的数据 workbench      #管理、备份、监控、分析、优化工具（比较费资源） 1.4.2 数据库层面问题解决思路一般应急调优的思路：针对突然的业务办理卡顿，无法进行正常的业务处理！需要立马解决的场景！1、show processlist2、explain select id ,name from stu where name='clsn'; # ALL id name age sex select id,name from stu where id=2-1 函数 结果集>30; show index from table;3、通过执行计划判断，索引问题（有没有、合不合理）或者语句本身问题4、show status like '%lock%'; # 查询锁状态 kill SESSION_ID; # 杀掉有问题的session常规调优思路：针对业务周期性的卡顿，例如在每天10-11点业务特别慢，但是还能够使用，过了这段时间就好了。1、查看slowlog，分析slowlog，分析出查询慢的语句。2、按照一定优先级，进行一个一个的排查所有慢语句。3、分析top sql，进行explain调试，查看语句执行时间。4、调整索引或语句本身。1.4.3 系统层面cpu方面vmstat、sar top、htop、nmon、mpstat 内存free 、ps -aux 、 IO设备（磁盘、网络）iostat 、 ss 、 netstat 、 iptraf、iftop、lsof、 vmstat 命令说明：Procs：r显示有多少进程正在等待CPU时间。b显示处于不可中断的休眠的进程数量。在等待I/OMemory：swpd显示被交换到磁盘的数据块的数量。未被使用的数据块，用户缓冲数据块，用于操作系统的数据块的数量Swap：操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。s1和s0最好是0Io：每秒从设备中读入b1的写入到设备b0的数据块的数量。反映了磁盘I/OSystem：显示了每秒发生中断的数量(in)和上下文交换(cs)的数量Cpu：显示用于运行用户代码，系统代码，空闲，等待I/O的CPU时间iostat命令说明实例命令： iostat -dk 1 5iostat -d -k -x 5 （查看设备使用率（%util）和响应时间（await））tps：该设备每秒的传输次数。“一次传输”意思是“一次I/O请求”。多个逻辑请求可能会被合并为“一次I/O请求”。iops ：硬件出厂的时候，厂家定义的一个每秒最大的IO次数,"一次传输"请求的大小是未知的。kB_read/s：每秒从设备（drive expressed）读取的数据量；KB_wrtn/s：每秒向设备（drive expressed）写入的数据量；kB_read：读取的总数据量；kB_wrtn：写入的总数量数据量；这些单位都为Kilobytes。1.4.4 系统层面问题解决办法你认为到底负载高好，还是低好呢？在实际的生产中，一般认为 cpu只要不超过90%都没什么问题 。当然不排除下面这些特殊情况：问题一：cpu负载高，IO负载低内存不够磁盘性能差SQL问题 ------>去数据库层，进一步排查sql问题IO出问题了（磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高）tps过高: 大量的小数据IO、大量的全表扫描问题二：IO负载高，cpu负载低大量小的IO 写操作：autocommit ，产生大量小IOIO/PS,磁盘的一个定值，硬件出厂的时候，厂家定义的一个每秒最大的IO次数。大量大的IO 写操作SQL问题的几率比较大问题三：IO和cpu负载都很高硬件不够了或sql存在问题1.5 基础优化1.5.1 优化思路定位问题点:硬件 --> 系统 --> 应用 --> 数据库 --\> 架构（高可用、读写分离、分库分表）处理方向明确优化目标、性能和安全的折中、防患未然1.5.2 硬件优化主机方面：根据数据库类型，主机CPU选择、内存容量选择、磁盘选择平衡内存和磁盘资源随机的I/O和顺序的I/O主机 RAID卡的BBU(Battery Backup Unit)关闭cpu的选择：cpu的两个关键因素：核数、主频根据不同的业务类型进行选择：cpu密集型：计算比较多，OLTP 主频很高的cpu、核数还要多IO密集型：查询比较，OLAP 核数要多，主频不一定高的内存的选择：OLAP类型数据库，需要更多内存，和数据获取量级有关。OLTP类型数据一般内存是cpu核心数量的2倍到4倍，没有最佳实践。存储方面：根据存储数据种类的不同，选择不同的存储设备配置合理的RAID级别(raid5、raid10、热备盘)对与操作系统来讲，不需要太特殊的选择，最好做好冗余（raid1）（ssd、sas 、sata）raid卡：主机raid卡选择：实现操作系统磁盘的冗余（raid1）平衡内存和磁盘资源随机的I/O和顺序的I/O主机 RAID卡的BBU(Battery Backup Unit)要关闭。网络设备方面：使用流量支持更高的网络设备（交换机、路由器、网线、网卡、HBA卡）注意：以上这些规划应该在初始设计系统时就应该考虑好。1.5.3 服务器硬件优化1、物理状态灯：2、自带管理设备：远程控制卡（FENCE设备：ipmi ilo idarc），开关机、硬件监控。3、第三方的监控软件、设备（snmp、agent）对物理设施进行监控4、存储设备：自带的监控平台。EMC2（hp收购了）， 日立（hds），IBM低端OEM hds，高端存储是自己技术，华为存储1.5.4 系统优化Cpu：基本不需要调整，在硬件选择方面下功夫即可。内存：基本不需要调整，在硬件选择方面下功夫即可。SWAP：MySQL尽量避免使用swap。阿里云的服务器中默认swap为0IO ：raid、no lvm、 ext4或xfs、ssd、IO调度策略Swap调整(不使用swap分区)/proc/sys/vm/swappiness的内容改成0（临时） /etc/sysctl.conf上添加vm.swappiness=0（永久） 这个参数决定了Linux是倾向于使用swap，还是倾向于释放文件系统cache。在内存紧张的情况下，数值越低越倾向于释放文件系统cache。当然，这个参数只能减少使用swap的概率，并不能避免Linux使用swap。 修改MySQL的配置参数innodb\_flush\_method，开启O_DIRECT模式。这种情况下，InnoDB的buffer pool会直接绕过文件系统cache来访问磁盘，但是redo log依旧会使用文件系统cache。值得注意的是，Redo log是覆写模式的，即使使用了文件系统的cache，也不会占用太多IO调度策略#echo deadline>/sys/block/sda/queue/scheduler #临时修改为deadline 永久修改如下： vi /boot/grub/grub.conf 更改到如下内容: kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet 1.5.5 系统参数调整Linux系统内核参数优化vim /etc/sysctl.conf   net.ipv4.ip_local_port_range = 1024 65535 # 用户端口范围   net.ipv4.tcp_max_syn_backlog = 4096   net.ipv4.tcp_fin_timeout = 30   fs.file-max=65535 # 系统最大文件句柄，控制的是能打开文件最大数量 用户限制参数（mysql可以不设置以下配置）vim /etc/security/limits.conf   * soft nproc 65535   * hard nproc 65535   * soft nofile 65535   * hard nofile 65535 1.5.6 应用优化业务应用和数据库应用独立,防火墙：iptables、selinux等其他无用服务(关闭)：chkconfig --level 23456 acpid off  chkconfig --level 23456 anacron off  chkconfig --level 23456 autofs off  chkconfig --level 23456 avahi-daemon off  chkconfig --level 23456 bluetooth off  chkconfig --level 23456 cups off  chkconfig --level 23456 firstboot off  chkconfig --level 23456 haldaemon off  chkconfig --level 23456 hplip off  chkconfig --level 23456 ip6tables off  chkconfig --level 23456 iptables off  chkconfig --level 23456 isdn off  chkconfig --level 23456 pcscd off  chkconfig --level 23456 sendmail off  chkconfig --level 23456 yum-updatesd off 安装图形界面的服务器不要启动图形界面 runlevel 3,另外，思考将来我们的业务是否真的需要MySQL，还是使用其他种类的数据库。用数据库的最高境界就是不用数据库。1.6 数据库优化SQL优化方向：执行计划、索引、SQL改写架构优化方向：高可用架构、高性能架构、分库分表1.6.1 数据库参数优化调整：实例整体（高级优化，扩展）thread_concurrency   #并发线程数量个数  sort_buffer_size     #排序缓存  read_buffer_size     #顺序读取缓存  read_rnd_buffer_size #随机读取缓存  key_buffer_size      #索引缓存  thread_cache_size    #线程缓存(1G—>8, 2G—>16, 3G—>32, >3G—>64) 连接层（基础优化）设置合理的连接客户和连接方式max_connections        #最大连接数，看交易笔数设置  max_connect_errors     #最大错误连接数，能大则大  connect_timeout        #连接超时  max_user_connections   #最大用户连接数  skip-name-resolve      #跳过域名解析  wait_timeout           #等待超时  back_log               #可以在堆栈中的连接数量 SQL层（基础优化）query\_cache\_size： 查询缓存OLAP类型数据库,需要重点加大此内存缓存.但是一般不会超过GB.对于经常被修改的数据，缓存会立马失效。我们可以实用内存数据库（redis、memecache），替代他的功能。1.6.2 存储引擎层（innodb基础优化参数）default-storage-engine  innodb_buffer_pool_size # 没有固定大小，50%测试值，看看情况再微调。但是尽量设置不要超过物理内存70% innodb_file_per_table=(1,0)  innodb_flush_log_at_trx_commit=(0,1,2) #1是最安全的，0是性能最高，2折中  binlog_sync  Innodb_flush_method=(O_DIRECT, fdatasync)  innodb_log_buffer_size      #100M以下  innodb_log_file_size        #100M 以下  innodb_log_files_in_group   #5个成员以下,一般2-3个够用（iblogfile0-N）  innodb_max_dirty_pages_pct  #达到百分之75的时候刷写 内存脏页到磁盘。 log_bin  max_binlog_cache_size       #可以不设置  max_binlog_size             #可以不设置  innodb_additional_mem_pool_size     #小于2G内存的机器，推荐值是20M。32G内存以上100M

MySQL 性能优化的 9 种姿势 MySQL 性能优化的 9 种姿势1、选择最合适的字段属性Mysql是一种关系型数据库，可以很好地支持大数据量的存储，但是一般来说，数据库中的表越小，在它上面执行的查询也就越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度舍得尽可能小。例如：在定义邮政编码这个字段时，如果将其设置为char(255)，显然给数据库增加了不必要的空间，甚至使用varchar这种类型也是多余的，因为char(6)就可以很好地完成了任务。同样的如果可以的话，我们应该是用MEDIUMINT而不是BIGINT来定义整形字段。2、尽量把字段设置为NOT NULL在可能的情况下，尽量把字段设置为NOT NULL,这样在将来执行查询的时候，数据库不用去比较NULL值。对于某些文本字段来说，例如“省份”或者“性别”，我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中，ENUM类型被当做数值型数据来处理，而数值型数据被处理起来的速度要比文本类型要快得多。这样我们又可以提高数据库的性能。3、使用连接(JOIN)来代替子查询(Sub-Queries)MySQL从4.1开始支持SQL的子查询。这个技术可以使用select语句来创建一个单例的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如：我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户id取出来，然后将结果传递给主查询，如下图所示：连接(JOIN)之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上 需要两个步骤的查询工作。另外，如果你的应用程序有很多JOIN查询，你应该确认两个表中JOIN的字段是被建立过索引的。这样MySQL内部 会启动为你优化JOIN的SQL语句的机制。而且这些被用来JOIN的字段，应该是相同的类型的。例如：如果你要把DECIMAL字段和一个INT字段JOIN在一起，MySQL就无法使用他们的索引。对于那些STRING类型，还需要有相同的字符集才行。(两个表的字符集可能不相同)。inner join内连接也叫做等值连接，left/right join是外链接。SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id=B.id; SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id; SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id; 经过多方面的证实inner join性能比较快，因为inner join是等值连接，或许返回的行数比较少。但是我们要记得有些语句隐形的用到了等值连接，如：SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id; sql中的连接查询有inner join(内连接）、left join(左连接)、right join（右连接）、full join（全连接）四种方式，它们之间其实并没有太大区别，仅仅是查询出来的结果有所不同。例如我们有两张表：Orders表通过外键Id_P和Persons表进行关联。inner join（内连接），在两张表进行连接查询时，只保留两张表中完全匹配的结果集。我们使用inner join对两张表进行连接查询，sql如下：SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p INNER JOIN Orders o ON p.Id_P=o.Id_P and 1=1  --用and连接多个条件 ORDER BY p.LastName 查询结果集：此种连接方式Orders表中Id_P字段在Persons表中找不到匹配的，则不会列出来。注意：单纯的select * from a,b是笛卡尔乘积。比如a表有5条数据，b表有3条数据，那么最后的结果有5*3=15条数据。但是如果对两个表进行关联:select * from a,b where a.id = b.id 意思就变了，此时就等价于：select * from a inner join b on a.id = b.id。 -- 即就是内连接。 但是这种写法并不符合规范，可能只对某些数据库管用，如sqlserver。推荐最好不要这样写。最好写成inner join的写法。内连接查询 (select * from a join b on a.id = b.id) 与 关联查询 (select * from a , b where a.id = b.id)的区别left join,在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。我们使用left join对两张表进行连接查询，sql如下：SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p LEFT JOIN Orders o ON p.Id_P=o.Id_P ORDER BY p.LastName 查询结果如下：可以看到，左表（Persons表）中LastName为Bush的行的Id_P字段在右表（Orders表）中没有匹配，但查询结果仍然保留该行。right join,在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。我们使用right join对两张表进行连接查询，sql如下：SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p RIGHT JOIN Orders o ON p.Id_P=o.Id_P ORDER BY p.LastName 查询结果如下：Orders表中最后一条记录Id_P字段值为65，在左表中没有记录与之匹配，但依然保留。full join,在两张表进行连接查询时，返回左表和右表中所有没有匹配的行。我们使用full join对两张表进行连接查询，sql如下：SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p FULL JOIN Orders o ON p.Id_P=o.Id_P ORDER BY p.LastName 查询结果如下：查询结果是left join和right join的并集。4、使用联合(UNION)来代替手动创建的临时表MySQL从4.0版本开始支持union查询，他可以把需要使用临时表的两条或更多的select查询合在一个查询中。在客户端查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用union作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要相同。下面一个例子就演示了一个使用union额查询。当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候尽量使用union all而不是union，因为union和union all的差异主要是前者需要将两个或者多个结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，增大资源消耗及延迟。5、事务尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询，但不是所有的数据库操作，都可以只用一条或少数几条就可以完成的。更多的时候是需要用一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中的数据的一致性和完整性。事务以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL语句操作失败，那么Rollback命令就可以把数据库恢复到begin开始之前的状态。BEGIN;  INSERTINTOsalesinfoSETCustomerID=14; UPDATEinventorySETQuantity=11WHEREitem='book'; COMMIT; 事务的另一个作用是当多个用户同时使用相同的数据源时，他可以使用锁定数据库的方式来为用户提供一种安全的访问机制，这样可以保证用户的操作不被其它的用户所干扰。一般来说，事务必须满足四个条件(ACID)：原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability).原子性：一个事物(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚(Rollback)到事务开始的状态，就像这个事务从来没有执行过一样。一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。隔离性：数据库允许多个事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同的级别，包括读未提交(Read uncommitted)、读已提交(Read committed)、可重复读(repeateable read)和串行化(Serializable).持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。事务的并发问题：1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据就是脏数据2、不可重复读：事务A多次读取同一事物，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。3、幻读：系统管理员A将数据库中的所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表MySQL事务隔离级别事务控制语句：BEGIN或START TRANSACTION：显式的开启一个事物。COMMIT：也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的。Rollback：也可以使用Rollback work，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。SAVEPOINT identifier：SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有很多个SAVEPOINT；RELEASE SAVEPOINT identifier：删除一个事物的保存点,当没有指定的保存点时，执行该语句会抛出一个异常。ROLLBACK TO inditifier：把事务回滚到标记点。SET TRANSACTION:用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERLALIZABLE。6、使用外键锁定表的方法可以维护数据的完整性，但是他却不能保证数据的关联性。这个时候我们可以使用外键。例如：外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的customerid映射到salesinfo表中customerid，任何一条没有办法合法customerid的记录都不会被跟新或插入到salesinfo中.CREATE TABLE customerinfo(customerid int primary key) engine = innodb; CREATE  TABLE   salesinfo( salesid int not null,customerid  int not null, primary key(customerid,salesid),foreign key(customerid)  references  customerinfo(customerid) on delete cascade)engine = innodb; 注意例子中的参数“on delete cascade”.该参数保证当customerinfo表中的一条客户记录也会被自动删除。如果要在mysql中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是mysql表的默认类型。定义的方法是在CREATE TABLE语句中加上engine=innoDB。你还在到处找面试题，点击这个面试库进行刷题，各类面试题太齐全了。7、锁定表尽管事务是维护数据库完整性的一个非常好的方法，但却因为他的独占性，有时会影响数据库的性能，尤其是很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其他的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。其实，有些情况下我们可以通过锁定表的方式来获得更好的性能。下面的例子就是锁定表的方法来完成前面一个例子中事务的功能。这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其他访问来对inventory进行插入、更新或者删除的操作。8、使用索引索引是提高数据库性能的常用方法，他可以令数据库服务器比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。那该对那些字段进行索引呢？一般来说，索引应该建立在那些将用于join，where判断和orderby排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引，对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况。例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。9、优化de的查询语句不使用子查询例：SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’); 子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。在MariaDB10/MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id; 但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，固生产环境应避免使用子查询避免函数索引例：SELECT * FROM t WHERE YEAR(d) >= 2016; 由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。应改为—–>SELECT * FROM t WHERE d >= ‘2016-01-01’; 用IN来替换OR低效查询SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30; —–\> 高效查询SELECT * FROM t WHERE LOC_IN IN (10,20,30); LIKE双百分号无法使用到索引SELECT * FROM t WHERE name LIKE ‘%de%’; —–>SELECT * FROM t WHERE name LIKE ‘de%’; 目前只有MySQL5.7支持全文索引（支持中文）读取适当的记录LIMIT M,NSELECT * FROM t WHERE 1; —–>SELECT * FROM t WHERE 1 LIMIT 10; 避免数据类型不一致SELECT * FROM t WHERE id = ’19’; —–>SELECT * FROM t WHERE id = 19; 分组统计可以禁止排序SELECT goods_id,count(*) FROM t GROUP BY goods_id; 默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。另外，MySQL 系列面试题和答案全部整理好了，微信搜索民工哥技术之路，可以查看MySQL企业面试题，在线阅读。—–>SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL; 避免随机取记录SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4; MySQL不支持函数索引，会导致全表扫描 —–>SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4; 禁止不必要的ORDER BY排序SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC; —–>SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id; 批量INSERT插入INSERT INTO t (id, name) VALUES(1,’Bea’); INSERT INTO t (id, name) VALUES(2,’Belle’); INSERT INTO t (id, name) VALUES(3,’Bernice’); —–>INSERT INTO t (id, name) VALUES(1,’Bea’), (2,’Belle’),(3,’Bernice’); 链接：blog.csdn.net/weixin_42047611/article/details/81772149

MySQL太慢？试试这些诊断思路和工具 MySQL太慢？试试这些诊断思路和工具作者 | 黄炎原文｜http://www.sohu.com/a/249994941_683048如果遇到 MySQL 慢的话，你的第一印象是什么，如果MySQL 数据库性能不行，你是如何处理的？MySQL 慢怎么办如果遇到 MySQL 慢的话，你的第一印象是什么，MySQL 数据库如果性能不行，你是如何处理的？我咨询了一些同行, 得到了以下反馈：第一反应是再试一次第二个反应是优化一下 SQL第三个反应是调大 buffer pool，然后开始换硬件了，换一下 SSD最后实在不行了找个搜索引擎搜索一下“MySQL 慢怎么办”。如果大家用的是国内的搜索引擎的话，搜索引擎会推荐某某知道或者某某乎, 推荐一些 MySQL 调优经验, 调大参数 A, 调低参数 B, 诸如此类，类似的网站能告诉你 MySQL 慢怎么办。我们来分析一下这些现象背后隐藏的意义：如果再试一次能够成功的话, 意味着你可能碰到了不可复现的外界因素的影响，导致 MySQL 会慢。如果优化 SQL 能解决，就意味着 SQL 的执行复杂度远远大于它的需求复杂度。如果调大 buffer pool 能解决，就意味着 MySQL 碰到了自身的某些限制。如果换 SSD 能解决，那么意味着服务器资源受到了一定的限制。如果需要搜索引擎，意味着调优这事已经变成了玄学。本文向大家分享我对 MySQL 慢的诊断思路，以及向大家介绍系统观测工具。MySQL 慢的诊断思路MySQL 慢的诊断思路，一般会从三个方向来做：MySQL 内部的观测外部资源的观测外部需求的改造下面依次看一下这几个思路。MySQL 内部观测常用的 MySQL 内部观测手段是这样的：第一步是 Processlist，看一下哪个 SQL 压力不太正常；第二步是 explain，解释一下它的执行计划；第三步要做 Profilling，如果这个 SQL 能再执行一次的话, 就做一个 Profilling；高级的 DBA 会直接动用 performance\_schema ，MySQL 5.7 以后直接动用 sys\_schema，sys_schema 是一个视图，里面有便捷的各类信息，帮助大家来诊断性能；再高级一点，会动用 innodb_metrics 进行一个对引擎的诊断。除了这些手段以外，还有一些乱七八糟的手段就不列在这了，这些是常规的 MySQL 内部状态观测的思路。外部资源观测这里引用国外一个大神写的文章，标题是《60 秒的快速巡检》（参考链接在文末）。我们来看一下它在 60 秒之内对服务器到底做了一个什么样的巡检。一共十条命令，下面一条一条来看一下。uptime，uptime 告诉我们这个机器活了多久，以及它的平均负载是多少。dmesg -T | tail，告诉我们系统日志里边有没有什么报错。vmstat 1，告诉我们虚拟内存的状态，页的换进换出有没有问题，swap 有没有使用。mpstat -P ALL 1，告诉我们 CPU 压力在各个核上是不是均匀的。pidstat 1，告诉我们各个进程的对资源的占用大概是什么样子。iostat-xz 1，查看 IO 的问题。free-m 内存使用率；sar-n DVE 1，sar-n TCP, ETCP 1，8 和 9 两条按设备网卡设备的维度，看一下网络的消耗状态，以及总体看 TCP 的使用率和错误率是多少。top，看一下大概的进程和线程的问题。这个就是对于外部资源的诊断，这十条命令揭示了应该去诊断哪些外部资源。外部需求改造第三个诊断思路是外部的需求改造，在这里引用了 MySQL 官方文档中的一章，《Examples of Common Queries 》（ https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.5/en/examples.html），文档中介绍了常规的 SQL 怎么写, 给出了一些例子。下面看一下它其中提到的一个例子。这张表有三列，article、dealer、price。它做的事情是从这个表里选取每个作者最贵的商品列在结果集中，这是它最原始的 SQL，非常符合业务的写法，但是它是个关联子查询。关联子查询成本是很贵的，所以上面的文档会教你快速地把它转成一个非关联子查询，大家可以看到中间的子查询和外边的查询之间是没有关联性的。第三步，会教大家直接把子查询拿掉，然后转成这样一个 SQL，这个就叫业务改造，前后三个 SQL 的成本都不一样，把关联子查询拆掉的成本，拆掉以后 SQL 会跑得非常好，但这个 SQL 已经不能良好表义了，只有在诊断到 SQL 成本比较高的情况下才建议大家使用这种方式。为什么它能够把一个关联子查询拆掉？这背后的原理是关系代数，所有的 SQL 都可以被表达成等价的关系代数式，关系代数式之间有等价关系，这个等价关系通过变换可以把关联子查询拆掉。总结一下，对于 MySQL 慢的诊断思路如下：第一，MySQL 本身提供了很多命令来观察 MySQL 自身的各类状态，从上往下检一般能检到 SQL 的问题或者服务器的问题。第二，从服务器的角度，我们从巡检的脚本角度入手，服务器的资源就这几种，观测手法也就那么几种，把服务器的资源全部都观察一圈就可以了。第三，如果实在搞不定，需求方一定要按照数据库容易接受的方式去写 SQL，这个成本会下降的非常快，这个是常规的 MySQL 慢的诊断思路。下面重点介绍为大家介绍系统观测工具。系统观测工具介绍先从诊断思路的讨论切换到系统的观测工具，首先了解什么叫系统观测工具并且看一下它的举例，然后再回到诊断思路上，看看新的工具的引入能为我们的思路到底带来怎样的改变。什么叫系统观测工具这里也参考了一篇外国人写的文档：https://jvns.ca/blog/2017/07/05/linux-tracing-systems/把这个文档拆开，中间描述了三件事情：系统观测工具的数据源来自于哪里；数据采集过程，因为采集的是系统的运行状况，所以到底如何采集这是一个难点；应该怎么看数据，是用图来看，还是用表来看，它就叫数据处理前端。第一步，我们来看一下数据源，Linux 给我们提供的数据源包括操作系统内核态提供的观测点和用户态提供的观测点，MySQL 很早之前就提供了用户态的观测点。第二步，如何把数据抽出来。以下这些工具中大家最熟悉的应该是 perf 和 ftrace，sysdig 也有人在用，其它的可能有所耳闻，这是从操作系统里抽取数据的方法。第三步，数据处理前端，前端常用的也是 perf 和 ftrace。如果大家对 perf 很熟悉的话会知道 perf 出来的数据是一个树形的数据，并可以跟这棵树进行交互，比如说: 查看某个函数运行了多久，哪一个函数的时间最长，这个是数据处理前端。我们来对比一下常规的四类系统观测工具：ftrace, perf_events，eBPF 和 Systemtap，这四个工具到底有什么不同，看看 Linux 为什么提供这么多观测工具。ftrace：ftrace 是 sysfs 中的一个桩，通过这个桩内核提供了一种观测的形式——把想观测的函数签名打到这个桩里，然后操作系统就会提供这个函数运行的状况。ftrace 的结构如左图, 数据处理前端和采集端是 ftrace, 数据源是下面这一堆。perf：常用的 perf 的原理是操作系统提供了一个系统调用可以将数据写到一个缓存中, 然后客户端把这些数据端抽取出来然后呈现在显示器上。eBPF：这是本文想重点推荐的。以上两种方案一种是操作系统提供的文件系统上的桩，一种是操作系统提供的系统调用，而 eBPF 是将一段代码直接插到操作系统内核某一个位置上的机制。Systemtap：它的原理是将一段 C 的代码编译成一个内核模块，然后将这个模块嵌到内核里边去，它不是由内核提供的一个机制，而是由内核的模块机制提供的一种功能。介绍了这四种观测工具的不同，大家在选取观测工具的时候就知道应该怎么选。这四种观测工具对系统伤害最轻的是谁？对系统伤害最轻的是系统调用，这是系统承诺出来的服务。然后是 ftrace，这是系统在文件系统层面提供的一个口，告诉你可以通过这个口跟系统交互。对系统侵入性最强的是谁？对系统侵入性最强的应该是 eBPF，因为它直接将一根代码嵌入到系统里边去做，最不稳定的应该是 System Tap，因为它是系统的一个模块, 又提供了非常复杂的功能。上图是 eBPF 的架构图，eBPF 先将一段程序编译成二进制代码，然后插入到操作系统里，操作系统运行这段代码的时候，将采集到的数据吐到操作系统本身的空间里，然后再做统一返回。eBPF 结构最核心的部分在于把代码插入到操作系统中运行，它需要做各种安全保护才能完成这一点，所以这也是这个机制复杂的地方。下面引用一个开源的 eBPF 脚本集 bcc, 快速看一下 eBPF 能做什么, 这些功能都是开箱即用的。bcc (eBPF 脚本集) 使用举例MySQL 的请求延迟分析：一个 MySQL 承担了很多业务，上千个并发˙中，哪一个 SQL 最慢，到底有哪些 SQL 在一秒以上，除了 slow log 以外，还可以用这种方法来看。这个命令的结果分为三列，它的第一列是请求的延迟，指数级递增，单位是微秒，中间一列是它的命中数，如果有一个请求命中了 64-127 微秒这个区间，命中数会加一，最后一列是它的分布图，它在同一个报告里提供了数值的方式和图的方式，可以很容易看到结果。对于这台服务器来说，我下了一个 select 的性能压力，它大部分的请求集中于 64 到 127 微秒之间。这个数据库的性能可能还不错。再来看另外一种压力，我下了一个 select+insert 的混合压力在一个数据库里，它的图又变了，它呈现了一个非常好的双峰图，我将两个峰值用另外一种颜色标明，这两个峰值的意思是很有可能有混合压力在一个数据库里，或者是上面的这部分压力是命中了某些缓存，而下面的某些压力是由于没有命中缓存，导致这部分请求更慢一些, 形成另一个峰值，所以通过这种峰值分析可以看到数据库大概的一个运行状态。如果能做得更好，你可以抽检自己的数据库然后做环比图，比如今天和昨天同样的时间，同样的业务压力下对数据库的延迟进行分析，如果数据库的延迟峰一直在往后延，就意味着数据库的状态在变得更糟糕一些。这是 bcc 第一个能做的事情，需要再次强调的是它开箱即用直接下载过来就可以使用。MySQL 的慢查询：MySQL 本身提供很好的慢查询，为什么还要用另外一个机制来获取 MySQL 的慢查询呢？MySQL 的慢查询可能很难做，与 MySQL 的慢日志相比, 它可以低成本地完成:获取少量慢查询获取某种模式的慢查询获取某个用户的慢查询比如说获取少量的慢查询，为什么是少量呢？因为不确定现在的线上延迟是多少，慢查询只开一秒可能日志瞬间就被堆上去，性能就会下来，但是如果慢查询开个十秒左右，没有请求在这个区间命中，所以要一点一点的去调这个值，比如说线上 1% 的最慢的查询能够命中，但是在这个脚本里面，可以取一定区间的最大的几个查询把它拎出来。通过脚本还可以命中某种模式的慢查询, 比如说我们只关心 update 的慢查询, 那么获取 select 的结果就没有太大的意义，或者是我一定要获取某一些特定表的相关的查询，我都可以通过脚本来做。第三种情况，想获取某个用户的慢查询，这个一般对于多租户系统，因为多租户系统只想针对某一个用户进行慢查询分析的时候，这种脚本就比较好用。VFS 延迟分析：对 VFS 做延迟分析，这是对数据库进行了一个写压力，可以明显看到一个双峰图，这是写的两个峰，是数据库对于内核的写压力的反馈。这个意味着什么呢？这个可能意味着因为这部分的写是命中了操作系统文件系统的缓存，而下面这部分写是真正的写穿到设备的，所以他们俩的延迟不一样，这是一个典型的双峰图，大家需要把两个峰拆开来去行这样的分析。换一个说法，如果写压力都集中在这里，而没有第二个峰的情况下，需不需要去更换物理设备？有可能不需要，因为所有的东西都命中了操作系统的缓存。短生命周期的临时文件检测：这个不一定常见，MySQL 会在某些情况下动用临时表, 如果 SQL 没写好就会创建临时表，这些临时表的生命周期很短，但是量很大，所以一定要写文件而不能内存里。在这种情况下会对操作系统造成一些压力，而这个压力又不太好诊断，因为临时文件的生存周期短，所以这个脚本可以帮大家提供一个方案，这个方案的结果是这样子。我做了一个临时表，这个临时表活了 5.3 秒左右，于是它展现在了脚本的结果里。如果扫描自己的线上 MySQL 发现这里有大量的东西说明在大量的使用临时表，如果 IO 压力在此时比较大, 就可能受了临时表的影响。短连接分析：好一点的应用都会用连接池，但是我们很多的时候没有那么好的运气，老碰到那么好的应用，所以经常业务会扔过来大量的短连接。这个例子中, sysbench 上了一个大并发，但是只活了 300 多毫秒，这些连接都只活了 300 多毫秒，反复运行这个 sysbench 就可以将数据库打死，建立一千个连接，300 毫秒以后也会销毁，再建立一千个连接，你的业务就会忽上忽下，通过这个脚本就可以抓到这个压力从哪个服务器来的，哪个端口来的，然后把它搞定就可以了。长连接分析：除了短连接分析，还有长连接分析，哪一个业务端老在搞我的数据，老在往里写，总在往里读，搞的网络特别慢。可以帮大家提供这样一个视角，它有读有写。以上几个 bcc 相关的例子都是现成的脚本。bcc 可以观测操作系统的各个方面，比如说如果有东西被 OOM kill 掉了，内存有泄露的也可以看，它基本上是我们这几年发现的一个宝库，大家直接调用这些脚本就可以完成很多的别人完成不了的分析，它的技术用的是 eBPF，直接在 github 上直接搜就行了。eBPF 使用方法 / 限制如果这里边脚本满足不了要求, 那可以自己写。这里介绍一下脚本的写法以及 eBPF 的限制。拿上面提到过的 MySQL 延迟分析举例，一个 MySQL 上面有一千个 query，这些 query 大概都落在哪个延迟时间里面那张图，为了完成这个需求, 我需要写两段程序，其中第一段程序是运行在内核里边的程序。这段程序的逻辑是这样的：在 query 开始的时候截获一下，让它记录一个时间戳；请求结束的时候再截获一下记录一个时间戳；把两个时间戳相减获得一个延迟；把这个延迟扔到结果集里边去，程序就完成了。我用结束时间减开始时间，减一下得到一个延迟，然后把延迟扔到一个统计容器里面，这个事就结束了。这是我要写的第一个程序，是嵌到内核里的程序，但是需要一个外壳的程序负责嵌入。这个外壳程序的逻辑也非常简单，把刚才那段内核的程序嵌到 MySQL 的观测点上，嵌到内核里面去，然后把结果集拿出来，打印出来就结束了，这是如何写一个 eBPF 的脚本，大家唯一需要做的事情就是这两个程序，然后运行一下。这个程序的核心只有 45 行，中间忽略了负责差错处理的一部分。只需要把现在的脚本拿下来抄一抄，改一改就可以完成很多的功能了。这么好的方法为什么很多人不知道呢？操作系统内核的限制，这个功能是 Linux 4.4 引进来的，但是在 Linux 4.4 上存在统计的 bug，我们推荐的是 Linux 4.9+，部分好用的功能是在 4.13+ 上才开放，这个是 eBPF 最大的限制。怎么办呢？只能祝大家长寿吧！活到 Linux 4.x 内核能在生产环境上使用的那一天。它的第二个最大的限制是 MySQL 的编译参数，MySQL 虽然在很早的时候已经提供了 dtrace 的观测点，这些观测点是公用的，但是它在默认的编译出来的官方发布的包里边是不带观测点编译的，所以在直接官方发布的二进制的包里边是用不了这个功能的，大家需要自己编译一下。编译的时候需要带这个参数，这个可能也是属于一个比较大的限制。所以如果大家受到限制，再推荐换一个工具：systemtap。Linux 2.6 就已经有了，但是它的机制是写一个内核模块，这种机制其实不是特别稳定，它为了解决不是特别稳定的问题增加了若干限制，比如说能在内核中使用的内存大小有限制，采集频率也有限制，对整个内核性能的影响百分比也有限制，在这些限制参数都开起来的情况下，它还是比较安全的。但是很多观测功能就必须要把这些限制关掉，一旦关掉内核就不是很稳定，所以这个工具，我没有敢把它的缺点写在上面因为确实是个好的工具，我们也很难说它的这个缺点是个致命的缺陷，但是不太推荐在生产环境上使用，但是在测试环境上确实是非常好玩的一个工具，如果大家用不了 eBPF 的话可以用 systemtap 来做一些诊断。还有很多其他的工具：至于如何选择，大家直接谷歌一下有专门的文章教大家怎么选择这些观测工具。但是总的来说没有一个科学的思路，只有尝试，不停的尝试。参考链接：60 秒的快速巡检：https://medium.com/netflix-techblog/linux-performance-analysis-in-60-000-milliseconds-accc10403c55本文 PPT 下载链接：github.com/actiontech/slides

MySQL 常用30种SQL查询语句优化方法 MySQL 常用30种SQL查询语句优化方法1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=04、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10union allselect id from t where num=205、下面的查询也将导致全表扫描：(不能前置百分号)select id from t where name like ‘%c%’下面走索引select id from t where name like ‘c%’若要提高效率，可以考虑全文检索。6、in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 37、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：select id from t where num=@num可以改为强制查询使用索引：select id from t with(index(索引名)) where num=@num8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*29、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=’abc’ –name以abc开头的idselect id from t where datediff(day,createdate,’2005-11-30′)=0 –’2005-11-30′生成的id应改为:select id from t where name like ‘abc%’select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引顺序相一致。12、不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：create table #t(…)13、很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数较好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。18、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。19、任何地方都不要使用 select from t ，用具体的字段列表代替“”，不要返回用不到的任何字段。20、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。21、避免频繁创建和删除临时表，以减少系统表资源的消耗。22、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，较好使 用导出表。23、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。25、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。26、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。27、与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONEINPROC 消息。29、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。30、尽量避免大事务操作，提高系统并发能力。