MySQL索引

MySQL性能优化

一 影响性能的相关因素

1. 商业需求对性能的影响

系统中每一个功能都是为用户提供某种服务，满足用户需求。但不是所有功能都是有用的，会造成资源的浪费

1. 系统架构及实现对性能的影响

2-1 不适合在数据库中存放的数据

二进制多媒体数据：视频

流水队列数据

超大文本数据

2-2 是否合理利用了应用层Cache机制？

2.2.1 系统各配置及规则数据

2.2.2 活跃用户的基本信息数据

2.2.3 准实时的统计信息数据

2.2.4 其他一些访问频繁但变更较少的数据

1. 设计对系统性能的影响

不合理的表设计可能会增加数据库的压力，从而影响性能

类型越精确越优秀 类型越小越好

1. 硬件环境对系统性能的影响
2. Query语句对系统性能的影响
3. 综合考虑

在整个系统的性能优化中，如果按照百分比来划分上面几个层面的优化带来的性能收益，可以得出如下数据：

需求和架构及业务实现优化：55%

Query语句的优化：30%

数据库自身的优化：15%

二 SQL优化

1 MySQL系统架构

1-1 逻辑模块组成

1.1.1 初始化模块

MySQLServer启动时，对整个系统进行初始化操作

1.1.2 核心API

提供一些需要非常高效的底层操作功能的优化实现，包括底层数据结构实现，页数算法实现，字符串处理，数字处理等，小文件I/O ，格式化输出，内存管理

1.1.3 网络交互模块

抽象出底层网络交互所使用的接口API，实现底层网络数据的接受与发送，方便其他模块调用

1.1.4 Client&Server交互协议模块

交互协议

1.1.5 用户模块

登录连接权限控制以及用户管理

1.1.6 访问控制模块

根据用户信息，管理用户能进行的操作

1.1.7 连接管理、连接线程和线程管理

管理客户端与SQL之间的连接线程

1.1.8 Query解析和转发模块

用户发送给数据库的命令称之为query。此模块主要功能为将query语句进行分析，按照不同操作类型进行分类，做出针对性的转发

1.1.9 QueryCache模块

将query请求的返回结果集cache到内存中，与该query的hash值做应对。

1.1.10 Query优化器模块

优化query

1.1.11 表变更管理模块

完成DML和DDL的query

1.1.12 表维护模块

表的状态检查，错误修复，优化和分析等工作

1.1.13 系统状态管理模块

客户端请求系统状态时，将状态返回给客户端

1.1.14表管理器

维护.frm文件以及一个catch 该catch存放各个表的结构信息。还能维护table级别的锁管理

1.1.15 日志记录模块

记录整个系统级别的逻辑层的日志

1.1.16 复制模块

1.1.17 存储引擎接口模块

1-2 各模块工作配合

2 常用引擎介绍

2-1 MyISAM

MyISAM存储引擎的表在数据库中，每一个表存放为3各以表名命名的物理文件

.frm：存放表结构定义信息

.MYD：数据

.MYI：索引数据

2-2 Innodb

.frm：存放表结构定义信息

.IBD：存放数据和索引数据

2-3 对比

   	InnoDB               	MyISAM                        

存储文件 .frm 表定义文件 .ibd 数据文件 .frm 表定义文件 .myd 数据文件 .myi 索引文件锁 表锁、行锁 表锁

事物 ACID 不支持

CRUD 读、写 读多

count 扫表 专门存储的地方

索引结构 B+Tree B+Tree

3 理解索引

索引就是高效存储数据的数据结构 一般基于B+Tree结构

3-1 索引的好处

提高表数据的检索效率：where

降低排序成本：order by

提高分组效率：group by

3-2 索引缺点：

需要额外的维护成本

3-3 索引的创建

create index 索引名 on 表名（加索引的字段）

频繁作为查询条件的字段应该创建索引：where having

唯一性太差的字段不适合单独创建索引

更新非常频繁的字段不适合创建索引

不会出现在where字句中的字段不该创建索引

3-4 单值索引和组合索引

单值索引：给一个字段设置索引

组合索引：给多个字段组合在一起设置索引

尽量使用组合索引

4 性能分析指令

4-1 explain 查看执行计划

id ：SELECT识别符。这是SELECT的查询序列号

select_type：查询类型

table： 哪张表

type：联接类型

possible_keys：可能用到的索引

key：实际用到的索引

key_len：索引长度

ref：从哪个字段取数据

rows：检查的行数

filtered：过滤几条信息

Extra：解决查询的详细信息

5 索引使用策略及优化

5-1 最左前缀原理与相关优化

5-2 索引选择性与前缀索引

5-3 InnoDB主键选择与插入优化

6 优化JOIN

7 其他优化

8 SQL优化原则

8-1 选择需要优化的SQL

8-2 Explain 和Profile

8-3 永远用小结果集驱动大的结果集

8-4 在索引中完成排序

8-5 使用最小的Colums

8-6 使用最有效的过滤条件