MySQL：数据库优化

Mysql性能优化概述

在应用开发的过程中，由于前期数据量少，开发人员编写的SQL语句或者数据库整体解决方案都更重视在功能上的实现，但是当应用系统正式上线后，随着生成数据量的急剧增长，很多SQL语句和数据库整体方案开始逐渐显露出了性能问题，对生成的影响也越来越大，此时Mysql数据库的性能问题成为系统应用的瓶颈，因此需要进行Mysql数据库的性能优化。
性能下降的表现

• 执行时间长
• 等待时间长

性能下降的原因

• 查询语句写的不好，各种连接，各种子查询导致用不上索引或者没有建立索引
• 建立的索引失效，建立了索引,在真正执行时,没有用上建立的索引
• 关联查询太多join
• 服务器调优及配置参数导致，如果设置的不合理,比例不恰当,也会导致性能下降,sql变慢
• 系统架构的问题

数据库优化的目的

• 避免出现页面访问错误。
• 增加数据库的稳定性：很多数据库的问题都是由于查询低效引起的。
• 优化用户体验，流畅页面访问速度，支撑业务正常开张

掌握

• 如果写出高质量的sql
• 如果保证索引不失效
• 搭建高并发，高可靠的系统架构

MySQL数据库的优化技术

对mysql优化时一个综合性的技术，主要包括

• 表的设计合理化(符合3NF，有时候要进行反三范式操作)
• 添加适当索引(index）(重点)
• 分表技术(水平分割、垂直分割)
• 主从复制，读写分离
• 存储过程 [模块化编程，可以提高速度]
• 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
• 系统应用优化等
• 服务器的硬件优化

一般优化步骤和慢查询定位

通过show status命令了解SQL的执行频率

MySQL客户端连接成功后，通过使用show [session|global] status 命令可以提供服务器状态信息。其中的session 来表示当前的连接的统计结果，global来表示自数据库上次启动至今的统计结果。默认是session级别的。
下面的例子：

show status like 'Com_%'; 
-- 试图连接MySQL服务器的次数 
show status like 'connections'; 
-- 显示慢查询次数 
show status like 'slow_queries';

其中Com_XXX表示XXX语句所执行的次数。
重点注意：Com_select, Com_insert, Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的 应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。 还有几个常用的参数便于用 户了解数据库的基本情况。

• Connections：试图连接MySQL服务器的次数
• Uptime：服务器工作的时间（单位秒）
• Slow_queries：慢查询的次数 (默认是慢查询时间10s)

慢查询日志

慢查询日志：记录具体执行效率较低的SQL语句的日志信息。

在默认情况下mysql慢查询日志记录是关闭的，同时慢查询日志默认不记录：管理语句和不使用索引进行查询的语句。

MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句 具体指运行时 间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10S，意思是运行10S 以上的语句。就会被认作是慢查询,默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数， 如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

通过慢查询日志定位执行效率较低的SQL语句。慢查询日志记录了所有执行时间超过long_query_time所设置的SQL 语句。

• 查看是否开启慢查询日志

show variables like '%slow_query_log%' 
-- 开启 
set global slow_query_log=1; 
-- 只对当前数据库生效, 如果重启后, 则会失效 
-- 如果想永久生效,必须要修改配置文件 
slow_query_log = 1 
slow_query_log_file=地址

• 那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？

show variables like 'long_query_time' 
set global long_query_time=4;

• 要断开连接后, 才能生效

show global variables like 'long_query_time'; 
select sleep(4) 
show global status like '%slow_queries%';

慢查询日志分析工具mysqldumpslow

慢查询日志中可能会出现很多的日志记录，我们可以通过慢查询日志工具进行分析，MySQL默认安装了 mysqldumpslow工具实现对慢查询日志信息的分析。
示例

-- 得到返回记录集最多的10个SQL。 
mysqldumpslow.pl -s r -t 10  C:\soft\DESKTOP-8GVEK4U-slow.log 
-- 得到访问次数最多的10个SQL 
mysqldumpslow.pl -s c -t 10  C:\soft\DESKTOP-8GVEK4U-slow.log 
-- 得到按照时间排序的前10条里面含有左连接的查询语句。 
mysqldumpslow.pl -s t -t 10 -g “left join”  C:\soft\DESKTOP-8GVEK4U-slow.log 
-- 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。 
mysqldumpslow.pl -s r -t 20  C:\soft\DESKTOP-8GVEK4U-slow.log

Count: 4（执行了多少次）  Time=375.01s（每次执行的时间） (1500s)（一共执行了多少时间）  Lock=0.00s (0s)（等待锁的时间）  Rows=10200.3（每次返回的记录数） (40801)（总共返回的记录 数）, username[password]@[10.194.172.41]

参数

第三方的慢查询日志分析工具：mysqlsla，myprofi，pt-query-diges

Show Profile分析

概述 Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测 量。默认情况下处于关闭状态，并保存最近15次的运行结果。把一条sql在mysql当中每一个环节耗费的时候都记录 下来。 使用

-- 1.查看当前版本是否支持 
Show variables like 'profiling'; 
-- 2.打开profile 
set profiling = on 
-- 3.查看结果 
show profiles 
-- 4.诊断sql 
show profile cpu,block io for query 88;

当出现以下选项时, 要进行优化

• Creating tmp table 创建临时表copy数据到临时表,用完再进行删除
• Copying to tmp table on disk 把内存中临时表复制到磁盘
• Locked 被锁

通过EXPLAIN分析低效SQL的执行计划

查询执行计划，使用explain关键字,可以模拟优化器执行的SQL语句，从而知道MYSQL是如何处理sql语句的 通过 Explain可以分析查询语句或表结构的性能瓶颈。 使用方法explain sql语句

分析包含信息

• id
  • select查询的序列号包含一组数字,表示查询中执行select子句或操作表的顺序
  • 相同,顺序走
  • 不同,看谁大,大的先执行
• type 访问类型排列,从上到下是最好的方式到最差的方式
  • system
    • 表中有一行记录(系统表) 这是const类型的特例 , 平时不会出现
  • const
    • 表示通过索引一次就找到了
    • const用于比较primary 或者 unique索引. 直接查询主键或者唯一索引
    • 因为只匹配一行数据,所以很快
  • eq_ref
    • 唯一性索引扫描
    • 对于每个索引键,表中只有一条记录与之匹配
    • 常见于主键或唯一索引扫描
  • ref
    • 非唯一性索引扫描,返回匹配某个单独值的所有行
    • 本质上也是一种索引访问
    • 它返回所有匹配某个单独值的行
    • 可能会找到多个符合条件的行,
    • 所以它应该属于查找和扫描的混合体
  • range
    • 只检索给定范围的行,使用一个索引来选择行
    • key列显示使用了哪个索引
    • 一般就是在你的where语句中出现between\ in等查询
    • 这种范围扫描索引比全表扫描要好
    • 因为它只需要开始于索引的某一点.而结束语另一点
    • 不用扫描全部索引
  • index
    • Full Index Scan
    • index与All区别为index类型只遍历索引树,通常比All要快,因为索引文件通常比数据文件要小
    • all和index都是读全表,但index是从索引中读取,all是从硬盘当中读取
  • ALL
    • 将全表进行扫描,从硬盘当中读取数据如果出现了All 数据量非常大, 一定要去做优化
  • 要求
    • 一般来说,保证查询至少达到range级别，最好能达到ref
• key
  • 实际使用的索引,如果为NULL,则没有使用索引
  • 查询中若使用了覆盖索引 ,则该索引仅出现在key列表中
  • possible_keys与key关系 理论应该用到哪些索引 实际用到了哪些索引覆盖索引
  • 查询的字段和建立的字段刚好吻合,这种我们称为覆盖索引
• select_type:查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询
  • SIMPLE 简单select查询,查询中不包含子查询或者UNION
  • PRIMARY查询中若包含任何复杂的子查询,最外层查询则被标记为primary
  • SUBQUERY：在select或where中包含了子查询
  • DERIVED：在from列表中包含的子查询被标记为derived(衍生)把结果放在临时表当中
  • UNION 若第二个select出现的union之后,则被标记为union 若union包含在from子句的子查询中,外层select将被标记为deriver
  • UNION RESULT从union表获取结果select，两个UNION合并的结果集在最后
• table 显示这一行的数据是关于哪张表的
• partitions 如果查询是基于分区表的话, 会显示查询访问的分区
• possible_keys key与keys主要作用,是查看是否使用了建立的索引, 也即判断索引失效 在建立多个索引 的情况下,
  mysql最终用到了哪一个索引 possible_keys 显示可能应用在这张表中的索引,一个或者多个 查询涉及到的字段
  上若存在索引,则该索引将被列出,但不一定被查询实际使用 可能自己创建了4个索引,在执行的时候,可能根据内部 的自动判断,只使用了3个
• key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引长度 .
• ref 索引是否被引入到, 到底引用到了哪几个索引
• rows根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
• filtered:满足查询的记录数量的比例，注意是百分比，不是具体记录数, 值越大越好，filtered列的值依赖统计信 息，并不十分准确
• Extra:产生的值
  • Using filesort说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行Mysql中无法
    利用索引完成排序操作称为"文件排序"
  • Using temporary使用了临时表保存中间结果,Mysql在对查询结果排序时, 使用了临时表,常见于排序 orderby
    和分组查询group by
  • use index表示相应的select中使用了覆盖索引,避免访问了表的数据行, 效率很好如果同时出现using where
    表明索引被用来执行索引键值的查找如果没有同时出现using where 表明索引 用来读取数据而非执 行查找动作
  • using where: 表明使用了wher过滤
  • using join buffer: 使用了连接缓存
  • impossible where: where 子句的值总是false 不能用来获取任何元组

常用的SQL优化-索引优化

什么是索引
索引是解决SQL性能问题的重要手段之一，使用索引可以帮助用户解决大多数的SQL性能问题。索引就是数据结构， 索引是Mysql高效获取数据的数据结构,类似新华字典的索引目录,可以通过索引目录快速查到你想要的字.排好序的快 速查找数据
为什么要建立索引
提高查询效率,没有排序之前一个一个往后找,通过索引进行排序之后,可以直接定义到想要的位置,排好序的快速查找数据结构–>就是索引
建立索引的优劣势
优势

• 索引类似大学图书馆建立的书目索引提高数据检索的效率
• 降低数据库的IO成本
• 通过索引对数据项进行排序,降低数据排序成本,降低了CPU的消耗

劣势

• 一般来说, 索引本身也很大, 索引往往以文件的形式存储到磁盘上
• 索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录.所以索引也是要占磁盘空间的
• 虽然索引提高了查询速度,但是会降低更新表的速度.
• 因为更新表时, MYSQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段, 会调整因为更新所 带来的键值变化后索引的信息

索引分类

• 单值索引
  • 一个索引只包含单个列,一个表可以有多个单值索引,一般来说, 一个表建立索引不要超过5个
• 唯一索引
  • 索引列的值必须唯一,但允许有空值
• 复合索引
  • 一个索引包含多个列
• 全文索引
  • MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的 速度。

索引快速查找数据的原因和索引类型

为了加快数据的查找,可以维护二叉查找树, 每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针,这 样就可以运用二叉查找在一定的复杂度内获取相应的数据,从而快速的检索出符合条件 的记录
二叉查找树

• 左子树的键值小于根的键值
• 右子树的键值大于根的键值
  
  除了二叉树还有B-tree索引，我平时所说的索引,如果没有特别指定, 都是指B树结构组织的索引,其中聚焦索引,次 要索引,复合索引,前缀索引,唯一默认都是B+树索引,B+树索引之外, 还有哈希索引(Hash index)等
  
  

B-Tree

平衡多路查找树特性
m阶B-Tree满足以下条件：

• 0.根节点至少包括两个孩子
• 1.树中每个节点最多有m个孩子(m>=2)
• 2.除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
• 3.所有叶子节点都在同一层
• 4.ki(1=1…n)为关键字,且关键字按顺序升序排列k(i-1) < k
• 5.关键字的个数n满足：ceil(m/2)-1 <= n <= m-1 (非叶子节点关键字个数比指向孩子的指针少1个)
• 6.非叶子结点的指针p[1],p[2],…p[m] 其中p1指向关键字小于k[1]的子树 p[m]指针关键字大于k[m-1]的子树 15 > 12 p[i]指向关键字属于(k[i-1],k[i])的子树 9,10 是位于8 和 12之间
  

B+Tree

B+树是B树的变体,基本与B-Tree相同

不同点

• 非叶子节点的子树指针与关键字个数相同
• 非叶子节点的子树指针,指向关键字值[k[i],k[i+1]]的子树(10 < 18 < 20)
• 非叶子节点仅用来做索引,数据都保存在叶子节点中
• 所有叶子节点均有一个链指针指向下一个 叶子节点
  • 链接起来, 能够方便我们直接在叶子节点做范围统计
  • 而不是再回到子节点中
  • 一旦定位到某个叶子节点, 便可以从该叶子节点横向的去跨子树去做统计

采用B+Tree做为主流索引数据结构的原因

• 更适合用来做存储索引
• B+树的磁盘读写代价更低
  • 内部的结构并没有指向关键字的具体指针
  • 不存放数据,只存放索引信息
  • 内部节点相对B树更小
• B+树的查询效率更加稳定
  • 内部节点并不是最终指向文件内容的节点,只是叶子节点中关键字的索引
  • 所以它任何关键字的查找,必须走一条从根节点到叶子节点的路
  • 所有关键字查询的长度相同,导致每一个数据查询的效率也几乎是相同
• B+树更有利于对数据库的扫描
• B树在提高IO性能同时,并没有解决元素遍历效率低下问题
• B+树只需要遍历叶子节点,就可以解决对全部关键字信息的扫描
• 对数据库中, 频繁使用的范围查询,性能更高

索引的基本操作

创建索引

create [UNIQUE] index 索引名称 ON 表名(字段(长度))

查看索引

show index from 表名

删除索引

drop index[索引名称] on 表名

更改索引

alter 表名 add [unque] index[索引名称] on(字段(长度)) 
alter table tab_name add primary key(column_list) 
添加一个主键,索引必须是唯一索引,不能为NULL 
alter table tab_name add unque index_name(column_list) 
创建的索引是唯一索引,可以为NULL 
alter table tab_name add index index_name(column_list) 
普通索引,索引值可出现多次 
alter table tab_name add fulltext index_name(column_list) 
-- 全文索引

索引建立选择

适合建立索引 1.主键自动建立唯一索引 primary 2.频繁作为查询条件的字段应该创建索引 比如银行系统银行 帐号,电信系统的手机号 3.查询中与其它表关联的字段,外键关系建立索引 比如员工,部门外键 4.频繁更新的字 段不适合建立索引 每次更新不单单更新数据,还要更新索引 5.where条件里用不到的字段不建立索引 6.查询中 排序的字段,排序的字段若通过索引去访问将大大提升排序速度 索引能够提高检索的速度和排序的速度 7.查询 中统计或分组的字段 分组的前提是必排序

不适合建立索引 记录比较少 经常增删改的表 索引提高了查询的速度 同时却会降低更新表的速度,如果对表的 INSERT,UPDATE和DELETE 因为建立索引后, 更新表时, MYSQL不仅要保存数据,还要保存一下索引文件 数据重 复的表字段 如果某个数据列包含了许多重复的内容,为它建立索引 就没有太大在的实际效果 比如表中的某一个 字段为国籍,性别 数据的差异率和重复率不高,这种建立索引就没有太多意义

MySQL如何正确使用索引

避免索引失效索引失效

全值匹配(最好)

最佳左前缀法则

如果索引的多列,要遵守最左前缀法则,指的就是从索引的最左列开始 并且不跳过索引中的列

不在索引列上做任何操作

计算,函数,类型转换会导致索引失效而转向全表扫描
示例 正常状态

范围条件右边的索引失效

!=或者<>

is not null 无法使用索引

or引起索引失效

like引起索引失效

字符串不加引号索引失效

尽量使用覆盖索引