MySQL数据库学习笔记-SQL优化

写在前面：不管未来的职业方向是DBA还是架构师，数据库是特别重要的一部分，这篇文章重在将自己学到的有关MySQL数据优化方面的知识，做一次总结。

一、MySQL数据库性能影响因素

（一）商业需求对性能的影响

1. 不合理的需求造成资源投入产出比过低
2. 无用功能堆积使系统过度复杂，影响整体性能

（二）系统架构及实现对性能的影响

1. 不适合在数据库存放的数据
   
   • 二进制多媒体数据，比如图片，音频，视频等。
   • 流水队列数据
   • 超大文本数据
2. 不合理的利用应用层的Cache缓存机制
   适合用于缓存的数据
   
   • 系统各种配置及规则数据
   • 活跃用户的基本信息数据
   • 活跃用户的个性化定制信息数据
   • 准实时的统计信息数据
   • 其它一些访问频繁但变更少的数据
3. 不是最简的数据库实现
4. 过度依赖数据库Query语句的功能造成数据库操作性能下降
5. 重复执行相同的Query语句

总结：

1.Cache系统的不合理利用导致Cache命中率下降，并造成数据库访问量的增加，同时也浪费了Cache系统的硬件资源
2.过渡依赖面向对象思想，对系统架构的要求过于完美，造成实现逻辑复杂化
3.对可扩展性的过渡完美要求，促使设计系统的时候将对象拆的过于离散，造成系统中有大量的Join语句
4.对数据库的过渡依赖，将大量更适合放入文件系统的数据存入数据库，如各种日志
5.过渡理想化系统的用户体验

（三）SQL语句对系统性能的影响

（四）Schema设计对系统性能的影响

1.OLTP系统
2.OLAP系统

二、查询条件的优化

• 在进行聚合查询时，利用group by进行操作时，有筛选条件having和where。其中，having和where的区别在于having是对聚合后的结果进行筛选，而where是在聚合前就对记录进行筛选，如果逻辑允许，应该尽可能利用where先过滤记录，这也结果集减小，将对聚合的效率有显著的提高，最后再根据逻辑看是否需要having进行再过滤。
  -

（一）SQL查询技巧

• 巧用正则表达式代替like的模糊查询，这个功能在匹配查找时非常方便
• 巧用ORDER BY RAND()提取随机行，实现随机抽取样本的功能，在进行数据统计时非常方便
• 利用group by 和with rollup子句做统计可以满足用户想要得到的任意一个分组以及分组组合的聚合信息值，但是，rollup不能和order by一起使用，limit用在rollup后面
• 利用bit group functions做统计，大大降低存储量，提高查询效率
  使用group by和bit_and、bit_or完成统计工作
  BitMap原型，例子select customer_id, bit_or(product) form shop group by customer_id
• 使用外键需要注意的问题，仅对InnoDB有作用。InnoDB存储引擎支持对外部关键字约束条件的检查
• 多使用Profile
• 永远用小结果集驱动大的结果集
• 尽可能在索引中完成排序，通过对索引进行检索中，已经得到了有序的数据访问顺序，不需要再进行排序操作
• 只取自己需要的Columns
• 仅仅使用最有效的过滤条件
• 尽可能避免复杂的Join和子查询，越复杂的Join语句，锁定的资源也就越多，所阻塞其它线程也就越多。

（二）Query优化

1、如何快速定位

• 通过show status命令了解各种SQL的执行效率like Com_XXX
• 定位执行效率低的SQL语句，可以通过慢查询日志定位
• 通过explain分析低效的SQL的执行计划

2、合理设计和利用索引（重点内容）

2.1 索引的存储分类

MyISAM的表的数据和索引是自动分开存储的，各自是独立的一个文件夹
InnoDB的表的数据和索引是存储在一个表空间里面的，但可以有多个文件组成。
存储类型：主要有Hash和B-Tree两种，MyISAM和InnoDB支持B-Tree，memory和heap都支持，还有Fulltext索引以及R-Tree索引

2.2 B-Tree索引

概念：
B-Tree索引：MySQL中的物理文件大多数是以Balance Tree的结构来存储的，也就是所有实际需要的数据都存放于Tree的Leaf Node节点上，而且到每一个Leaf Node的最短路径的长度是完全相同的。
B+Tree索引：InnoDB使用改进后的B-Tree称之为B+Tree，即在每一个Leaf Node上除了存放索引键的相关信息外，还存储了指向与该Leaf Node相邻的下一个Leaf Node的指针信息，加快索引多个Leaf Node的效率。

（1）对于InnoDB存储引擎来说：
采用两种不同的索引：Cluster形式的主键索引（Primary Key）和B+Tree索引（Secondary Index），两者的Root Node和Branch Nodes完全一样，在Leaf Nodes会有差异。
其中，在Primary Key中，Leaf Nodes存放的时表的实际数据，包括主键字段的数据还有其他字段的数据，整个数据以主键值有序的排列，通过主键访问数据效率非常高。而在B+Tree中则在Leaf Nodes中存放了索引键的相关信息以及主键信息。而没有相关的数据行。
（2）对于MyISAM存储引擎来说：
和InnoDBd的Secondary Index基本相同，只是在Leaf Nodes上除了索引键信息外，还存放了直接定位到MyISAM数据文件中相应数据行的信息Row Number，但不会存放主键信息。

2.3 Hash索引

主要用于memory存储引擎，还支持非唯一的Hash索引，如果遇到非唯一的Hash索引，存储引擎会将它们链接到同一个Hash键值下，并以一个链表的形式存在，然后取得实际键值时过滤不存在的键。查询效率高
缺点：
1. Hash索引仅仅满足“=”“IN””<=>”查询，不满足使用范围查询
2. 无法被用来避免数据的排序索引
3. 不能利用部分索引键查询
4. 在任何时候都不能避免表扫描
5. 如果遇到大量的hashcode重复，效率不一定比B-Tree高

2.4 Full-Text索引

全文索引，尽在MyISAM中使用，char,varchar和text支持
与B-Tree的区别，不是通过字段内容的完整匹配，通过特定的算法，将字段进行分割后再进行索引，按照最小的四个字节来分割。

2.5 索引小结

缺点：

1. 需要消耗存储空间
2. 如果更新涉及到索引字段，会增加IO消耗和调整索引导致的数据量

优点：

1. 提高索引效率，减少检索过程中需要读取的数据量
2. 降低排序成本，改善排序数组的操作性能

2.5 使用时注意的事项

• 使用索引关键字，或者多列索引最左边的前缀
• like查询，后面如果是常量，并且%号不在第一个字符
• 大的文本索引，使用全文搜索
• 如果列名是索引，使用column_name is null
• 存在索引但是不使用的情况
  
  • 如果or前的条件中列有索引，而后面的列中没有索引，那么所涉及的索引都不会用到
  • like是以%开头
  • 不是索引的第一部分
  • 如果列类型是字符串，则需要在where中用引号引起来

实用优化方法

• 定期优化表和检查表
• 定期优化表

3、其它SQL优化

大批量插入数据

• MyISAM: 使用disable keys和enable keys

alter table tb1_name disable keys;loading the data;alter table tb1_name ensable keys;

• InnoDB:
  (1) 导入顺序按照主键的顺序排列
  (2) 在导入数据前执行set unique_checks=0，关闭唯一性校验，导入结束后再回 复。
  (3) 如果使用自动提交方式，导入前关闭自动提交，导入后再打开

优化INSERT

• 同时从一个客户插入很多行，建议使用多个值表的insert语句，不要分开写
• 从不同用户插入很多行，通过使用insert delayed 进行优化，将数据放入内存队列中，没有写进磁盘，而low_priority则相反
• 将索引和数据分在不同的磁盘上存放
• 当从一个文本文件装载一个表时，使用load data infile

优化group by：

在group by 后面指定order by null，可以避免MySQL的默认排序

1. 利用松散索引扫描实现，条件字段处在同一个索引中最前面的位置
2. 使用临时表实现

优化order by：

where条件和order by使用相同的条件，order by顺序和索引顺序相同
order by字段都是升序或者都是降序

• 通过有序的索引获得有序的数据，避免因为排序计算带来的资源损耗
• 利用相应的排序算法进行排序
  
  • 取出满足过滤条件的字段和直接定位到行数据的指针信息，在sort buffer中对数据进行排序，排好的数据根据指针返回表中取得客户端请求的其它信息
  • 在sort buffer中对数据和行指针进行排序，减少第二次访问，但是增加了内存消耗

优化嵌套查询：

利用join来取代子查询

优化or条件：

or之间的两个条件必须用到索引，如果没有索引，则考虑增加索引

三、选择合适的数据类型

text和blob

• 在执行大量的删除操作后，使用optimize table 进行碎片的整理
• 使用合成的（Synthetic）索引来提高大文本text和blob的查询性能，即通过大文本字段的内容简历一个散列值，并把这个值存储在单独的数据列中，通过散列值就可以检索文本行了。计算hashCode可以采用mod5()等算法，如果生成的字符串带有尾部空格，则不要存在char和varchar中。
• 尽量避免检索大文本
• 把text和blob列分散到单独的表中

四、存储引擎

最常用的五种引擎：MyISAM，InnoDB，MEMORY，MERGE，NDB

特点 MyISAM InnoDB MEMORY MERGE NDB 存储限制 有 64TB 有 没有 有 事务安全 支持 锁机制 表锁 行锁 表锁 表锁 行锁 B树索引 支持 支持 支持 支持 支持 哈希索引 支持 支持 全文索引 支持 集群索引 支持 数据缓存 支持 支持 支持 索引缓存 支持 支持 支持 支持 支持 数据可压缩 支持 空间使用 低 高 N/A 低 低 内存使用 低 高 中等 低 高 批量插入的速度 高 低 高 高 高 支持外键 支持

（一）MyISAM

存储三种文件名

1. .frm(存储表定义)
2. MYD(MYData，存储数据)
3. MYI(MYIndex，存储索引)
   创建表时通过DATA DIRECTORY 指定，文件路径需要绝对路径

三种存储格式

1. 静态表（固定长度）：存储速度快，容易缓存，出现故障容易恢复，占用空间大
2. 动态表：占用空间少，容易产生碎片，定期执行OPTIMIZE TABLE。
3. 压缩表
   采用固定长度的数据列代替可变长度的数据列，字符串采用char

表文件损坏

1. 当MySQL正在做写操作时被终止（kill）或其他情况造成异常终止
2. 主机崩溃（crash）
3. 磁盘硬件故障
4. Bug

（二）InnoDB

介绍

InnoDB提供了具有提交、回滚和崩溃恢复能力的事务安全，但是写的效率会差点而且会占用更多的磁盘空间

特点

1.支持事务安全
实现了数据隔离的四个级别：read uncommitted、read committed、repeatable、serializable.

2.数据多版本的读取

3.自动增长列
自动增长列可以手工插入，如果是0或者null，则是自动增长后的值，该自动增长表必须是索引，如果是组合索引也必须是索引的第一列

4.锁定机制的改进
InnoDB实现了行锁，通过索引来完成。

5.外键约束
1. 在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。在创建索引时，可以指定在删除、更新父表时，对子表进行相应的操作。
2. 当某个表被其它表创建了外键参考，那么该表的对应索引或者主键禁止被删除
3. 在导入多个表的数据时，如果需要忽略表之前导入的顺序，可以通过暂时关闭外键约束来加快处理的速度。

物理结构

1.数据文件
存放在数据表中的数据和所有的索引数据，包括主键和其它普通索引；
表空间分为两种：
- 共享表空间：所有表数据，索引数据，各种元数据及事务的undo数据都存在同一个表空间中（一个文件或者多个数据文件），通过innodb_data_file_path来指定，增加数据文件需要停机重启。（增加数据文件，只需要在innodb_data_file_path参数后面按照标准格式设置好文件路径和相关属性即可）
- 独享表空间：每个表的数据和索引被存放于一个单独的.ibd中，该文件包含表数据，索引数据，各种元数据及事务的undo数据。
2.日志文件
采用轮循策略写入，当出现系统崩溃时，由于REDO日志的存在，且有checkpoint机制来保护，InnoDB通过REDO日志将数据库崩溃时已经完成但还没有来得及将内存中已经修改的但未完全写入磁盘的数据进行重做操作写入数据文件，也能够将所有已部分完成并写入磁盘的未完成事务进行回滚操作，保证数据的一致性。
采用可变长度的数据列，字符串采用varchar

参考文献

《深入浅出MySQL 数据库开发、优化与管理维护》 唐汉明 翟振兴 兰丽华 关宝军 申宝柱 编著
《MySQL 性能调优与架构设计》 简朝阳 著

（未完待续）