Mysql性能优化

人们常说Mysql性能优化有三驾马车：慢查询日志、explain执行计划（包括索引优化）、show profile（与硬件相关）本文主要从慢查询日志、explain执行计划两方面讲述如何进行Mysql的性能优化

1. 性能分析思路

1. 使用【慢查询日志】功能，获取所有查询时间比较长的sql语句

2. 其次【查看执行计划】查看有问题的sql的执行计划

3. 最后可以使用【show profile[s] 查看有问题的sql的性能使用情况】

    E.g.
    Id是主键、name是非唯一索引
    Mysql在执行查询时，一般情况下，只会用到一个索引（优化器去进行选择）
    Select * from user where id=1 and 			name=’zhangsan’;
   

2. Mysql架构介绍

• Sql层（语句、规范、优化器）
• 存储引擎层（InnoDB）
• 磁盘

2.1 Mysql存储引擎

Mysql存储引擎有许多种，常用的包括MyISAM（插入查询快，不支持事务）、InnoDB、Memory（临时表）

再磁盘中存储数据库信息有两种形式，顺序IO和随机IO

1. 顺序IO：追加操作 花费时间包括（传输时间）
2. 随机IO：随机操作 花费时间包括（寻道时间、旋转时间、传输时间）

3. 索引介绍

3.1 索引是什么

1.帮助mysql高效获取数据的数据结构，加快数据库查询速度
2.（聚集索引、覆盖所有、组合索引、前缀索引、唯一索引等）都是使用b+树等结构组织等索引
3.索引往往是存储在磁盘上文件夹中

3.2 索引的优势和劣势

3.2.1 优势

1.可以提高数据库检索效率，降低数据库IO成本
2.可通过索引进行排序

3.2.2 劣势

1.索引会占据磁盘空间
2.索引虽然会提高查询效率，但是会降低更新表的效率（插入数据索引也要更新）

3.3 常用索引分类

3.3.1 单列索引

1.普通索引
2.唯一索引 （可为null）
3.主键索引

3.3.2 组合索引（用的几率大，更好）

1.再多个字段组合上创建的索引
2.组合索引的使用，需要遵循最左前缀原则（最左匹配原则）
3.建议使用组合索引代替单列索引

3.4 索引的存储结构

索引在存储引擎中实现，不同存储引擎有不同的存储结构
MyISAM和InnoDB只支持btree索引
Memory和HEAP支持hash和btree索引

3.4.1 B树和B+树的区别

B树非叶子结点存储数据
B+树只有叶子结点存储数据

3.4.2 聚集索引（InnoDB）

即索引组织表 表数据和索引在一起 索引存的是数据

3.4.3主键索引

3.4.4 辅助索引

存储的是主键索引中的主键值，不是地址值
如果是非主键查询，则需要搜索两次索引树、一次是辅助索引树、一次是主键索引树，最终取出来数据。

若没有主键索引，找唯一非空列，若还是找不到，则创建隐藏列

3.4.5 非聚集索引（MyISAM）

即堆组织表 索引存的是物理地址值

3.5 组合索引使用（性能优化的门槛）

以下语句会创建一棵B+树，但是他相当于三棵索引树的功效
ALTER TABLE ‘table_name’ADD INDEX index_name(‘col1’,’col2’,’col3’)

3.5.1 创建组合索引

1.常出现在where中的列、建议用来创建组合索引，组合索引中的顺序是很重要的(mysql从左到右，oracle从右到左)
2.常出现在order by和group by语句中的列，最后按照顺序去创建组合索引
3.常出现在select中的列，也建议按照顺序，创建组合索引

3.5.2 最左前缀原则：针对组合索引和前缀索引

1. mysql会一直向右匹配直到遇到范围查询(>、 <、 between、 like)就停止匹配，比如a = 1 and b = 2 and c>3 and d=4如果建立(a,b,c, d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a, b, d的顺序可以任意调整。
2. =和in可以乱序，比如a = 1 and b= 2 and c = 3建立(a,b,c)索引可以任意顺序。mysq1的查询优化器会帮你优化成索引可以识别的形式

3.5.3 哪些情况需要创建索引

1.主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引(业务)
3.多表关联查询中，关联字段应该创建索引
4.查询中统计或者分组字段，应该创建索引
5.查询中排序的字段，应该创建索引

3.5.4 哪些情况不需要创建索引

1.表记录太少
2. 经常进行增删改操作的表
3.频繁更新的字段
4. where条件里使用频率不高的字段

4. Explain查看执行计划

Explain命令用法非常简单，在select语句前加上explain就可以了

4.1 输出的字段（只选取了重要的）

4.1.1 type：JOIN类型

显示的是单位查询的连接类型或者理解为访问类型，访问性能依次从好到差:

system（表中只有一行或空表）
const（使用唯一索引或主键）
eq_ ref（多表查询，一般针对唯一索引）
ref（多表查询，一般针对非唯一索引，=查询或者前缀索引查询）
fulltext
ref or null
unique subquery
index subquery
range（常见于< > in like）
index merge
index（没有回表，在索引树上就获取了数据）
ALL（全表扫描）

注意事项:

1. 除了all之外，其他的type都可以使用到索引
2. 除了index merge之外，其他的type只可以用到一 个索引
3. 最少要使用到range级别，以上都使用了索引

4.1.2 Extra：额外的信息

Using index：表示使用了索引覆盖，不需要回表查询，通过索引就可以获取查询的结果数据
Using where：使用了mysql server中的过滤信息

4.1.3 分页类型

物理分页：在数据库层面只返回分页后的数据
逻辑分页：在数据库层面返回所有数据，在JVM内存中再对所有数据进行逻辑的分页

4.1.4 ICP索引下推

ICP索引下推即（5.6版本后）某些筛选在innoDB层就进行筛选

5. 索引失效

以下情况会导致索引失效

1.全值匹配
2.最左前缀法则
3.不在索引上做任何操作（计算、函数、类型转换），这些操作会导致索引失效转向全表扫描
4.存储引擎不能使用索引范围条件右边的列
5.尽量使用覆盖索引，减少select
6.Mysql在使用不等于（！= < >）无法使用索引会导致全表扫描
7.Is null, is not null 也无法使用索引
8.Like以通配符%开头，会使索引失效
9.字符串不加单引号索引失效
10.少用or，用它连接时索引失效

6. Mysql性能优化

6.1 服务器层面优化

将数据保存在内存中，保证从内存读取数据
建议innodb_buffer_pool_size设置为总内存大小的3/4或者4/5。

6.2 内存预热

磁盘数据在mysql server启动的时候，读取到内存中
降低磁盘写入次数
推荐innodb_log_file_size设置为0.35*innodb_buffer_pool_size

6.3 提高磁盘读写

6. Sql设计层面优化

设计中间表，一般针对于统计分析功能，或者实时性不高的需求(OLTP、OLAP)

为减少关联查询，创建合理的冗余字段(考虑数据库的三范式和查询性能的取舍.创建冗余字段还需要注意数据一致性问题)

对于字段太多的大表，考虑垂直拆表(比如一个表有100多个字段)

对于表中经常不被使用的字段或者存储数据比较多的字段，考虑拆表(比如商品表中会存储商品介绍，此时可以将商品介绍字段单独拆解到另一个表中，使用商品ID关联)

每张表建议都要有一个主键 (主键索引)， 而且主键类型最好是int类型.建议自增主键(不考虑分布式系统

7. Sql语句优化（开发人员）

7.1 索引优化

●为搜索字段( where中的条件)、 排序字段、select查询列，创建合适的素引， 不过要考虑数据的业务场景:查询多还是增删多?

●尽量建立组合索引并注意组合索引的创建顺序，按照顺序组织查询条件、 尽量将筛选粒度大的查询条件放到是左边。

●尽量使用覆盖索引，SELECT语句中尽量不要使用”。

●order by、 group by语句要尽量使用到索引

7.2 其他优化

●尽量不使用count（*），使用count（主键）

●JION两张表的关联字段最好都建立索引，而且最好字段类型是一样的。

●WHERE条件中尽量不要使用1=1、not in语句（建议使用not exists）

●不用MYSQL内置的函数，因为内置函数不会建立查询缓存（比如 now（））