MySQL性能优化方案

欢迎访问我的个人博客:www.ifueen.com

MySql性能优化策略

关系型数据库的优化方案

关于MySQL的优化，大体方案可以分为以下步骤

1. 找出执行效率低的Sql
2. 分析慢的Sql
3. 进行优化

定位慢查询

找出执行效率慢的SQL(定位慢SQL)

首先要明白原理，实际上我们在数据库上面所做的操作都会被日志记录下来，我们在定位慢查询就需要去通过日志记录来找到到底是哪条SQL效率慢

查询数据库状态的命令

运行了多久 show status like 'uptime';

CRUD的执行次数 
show status like '%Com_select%'
show status like '%Com_insert%'
show status like '%Com_update%'
show status like '%Com_delete%'

查询所有的连接数
show status like 'connections'

查询慢查询次数(数据库默认是10S，可以通过命令设置)
show status like 'slow_queries' 

查看和修改慢查询时间阈值
show variables like ‘long_query_time’ ; //可以显示当前慢查询时间
set long_query_time=1 ;//可以修改慢查询时间

开启慢查询记录日志

因为我们的SQL服务默认是没有开启满记录日志的，所以我们并不能查看慢查询相关的记录，需要我们手动开启

进入到mysql文件夹下的bin目录，通过cmd运行以下命令：mysqld.exe --safe-mode --slow-query-log

就可以开启慢查询记录的日志

慢记录日志的默认输出地址在my.ini文件中，通常默认是在C盘

慢记录一般都会在开发和测试阶段开启，不要等到上线后再去检查

分析Sql语句

通过 explain 语句可以分析Sql语句

单机优化

通过表结构设计

数据库设计三范式

这是数据库在设计之初我们默认都要遵守的规则

第一范式：数据表中的列是不可分割的一个整体

第二范式：表中的记录都需要一个唯一标识(通常是主键)

第三范式：表中不要有冗余数据，大体意思就是一张表中不能出现另一张表除主键之外的字段

反三范式设计

有时候为了提高效率，有时候就必须要通过反三范式来进行设计，适当的保留冗余数据

对于存储设计，我们通常有两种方式来进行设置

定长：会根据数据的类型来进行空间的分配，是固定的

边长：根据存储数据的大小来，用多少就分配多少

存储引擎

数据库的存储引擎分类：Innodb,MyISAM

Innodb和MyISAM的区别

• Innodb支持事务，MyISAM不支持事务
• MyISAM查询和添加较块，Innodb查询比较慢
• Innodb支持外键，MyISAM不支持外键
• 锁机制MyISAM用的是表锁，Innodb用的是行锁
• MyISAM支持全文索引，Innodb不支持全文索引

索引

首先我们要弄清楚一个概念：什么是索引，什么时候用索引？

索引是数据库优化的时候最物美价廉的东西，官方概念称是帮助MySQL提高查找效率的一种数据结构，能够帮我们快速的定位查找到相应的位置

我们一般在数据量过多的表中或者需要查询频率比较高的字段上加上索引

注意：对于增删改操作比较多的字段，不建议使用索引，即使查询频率较高

MyISAM和Innodb都只支持Btree索引算法，而MEMORY/HEAP同时支持Btree和HASH算法

索引分类

• 普通索引：允许重复的出现，可以在任何字段上面添加
• 唯一索引：要求添加索引的字段里面的内容必须是唯一的(如：身份证、电话号码、游戏ID、QQ号)，允许内容为空
• 主键索引：只能在主键上面添加的索引，要求非空且唯一
• 全文索引：用来堆表中的文本域(bigtext,char,text)进行索引，全文索引针对MyISAM有用

此外，我们害可以创建复合索引(多列索引，就是在多个列上面创建一样的索引

复合索引使用事项：

• 没有使用第一部分就不会使用索引
• 对于使用了like的查询，只有前置匹配才可以使用到复合索引
• 如果条件中存在or，则索引失效
• 如果条件中存在<>，则索引失效
• 如果列类型是字符串，那么一定要用引号引起来，否则也会导致索引失效

分表

分表的方式有垂直分表和水平分表两种

垂直分表

垂直分表是根据字段进行分表，我们通常会遇到这种需求，有些表记录的数据并不是很多，但是字段却非常的多，每次查询起来会执行大量的I/O，影响性能，这时候我们就可以进行垂直分表

比如：现在一个用户有许多字段信息，但是有些又不太常用，就可以通过id，把姓名，电话，地址等分一个表，把签名，收藏等通过id进行垂直分表，分为另一张表，两张表的id是同一个

水平分表

水平分表是根据记录来进行分表，如果一个表的记录太多的话，不用想，自然会影响到查询的效率，比如上千万条，而且需要经常检索，那么我们就有必要进行水平分表了

水平分表其实很好理解，比如一个一千万记录的表，我们可以根据记录数分成十张一百万的表，第一张就是一到一百万，一次往后…

有时候会遇到这种需求，加入需要查询第五百万条到第八百三十二万条怎么办呢，这时候我们可以用UNION和UNION ALL来把结果集进行联合

水平分表中的id我们可以通过主键自增、UUID、雪花算法等来进行求出

最简单的一种hash算法：t_user+id%100(分表数)+1

SQL语句优化

DDL优化

• 通过禁用索引来提供导入数据性能
• 关闭唯一校验
• 修改事务提交方式

DML优化

将多条结构相关的sql语句合并味一条

DQL优化

• order by优化，多用索引进行排序
• group by优化，在group by是使用order by null,取消默认排序
• subQuery嵌套优化，利用嵌套进行优化
• limit优化(重要) 在分页条件那里尽量使用一个带索引的字段进行排序或者过滤比如：select * from test id>4000 limit 5