能看懂的Mysql优化方案-更新ing

た入场券 2022-05-15 10:39 210阅读 0赞

### 一、数据库设计要合理(符合3F三范式) ###

什么是三大范式：[原文-张龙豪][-]

*  第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
 *  第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。
 *  第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.

表结构:

*  合适的数据类型
 *  尽可能使用not null和default
 *  少用text,使用text考虑分表

### 二、☆添加索引       [参考链接][Link 1] ###

索引实现原理 b-tree(折半查找,提高查询效率)

b-tree请参考[参考链接][Link 2]

[折半查找参考链接][Link 3]          java实现折半查找两种:[参考链接][Link 4]

索引优点:        提高程序效率

索引缺点:         添加删除效率低(索引文件重新生成)、增加内存

索引分类

*        普通索引
 *        主键索引\[常见\]
 *        唯一索引
 *       全文索引(InnoDB不支持全文索引)(一般都用第三方搜索引擎框架)
 *       联合索引

-- 创建索引语句
    create index [索引名] index on [表名] (列名1,列名2)
    -- 创建联合索引语句(第一个索引可以不用第二个一起作为条件查找,还会使用索引,但只用第二个不会用索引)
    alter table dept add index my_index (列名1,列名2)

### 索引优化方案: ###

> 1.查询次数多,不常更新的字段适合建索引

> 2.order by与group by,on后应直接使用字段，而且字段应该是索引字段。

> 3.索引字段长度应较短而长度固定。

> 4.索引字段重复不能过多。

> 5.对于like语句,以%或者‘-’开头的会全表扫描不会使用索引，以%结尾会使用索引

> 6.尽量不要使用in 和not in和<>和<>操作,索引失效,全表扫描

> 7.使用or时条件需要都加索引,否则有一个不用索引,整句就不用索引

### 三、☆sql语句优化 ###

> (1)、where子句中使用like关键字时，前置百分号会导致索引失效（起始字符不确定都会失效）。如：select id from test where name like "%吉坤"。

> (2)、where子句中使用is null或is not null时，因为null值会被自动从索引中排除，索引一般不会建立在有空值的列上。

> (3)、where子句中使用or关键字时，or左右字段如果存在一个没有索引，有索引字段也会失效；而且即使都有索引，因为二者的索引存储顺序并不一致，效率还不如顺序全表扫描，这时引擎有可能放弃使用索引，所以要慎用or。

> (4)、where子句中使用in或not in关键字时，会导致全表扫描，能使用exists或between and替代就不使用in。

> (5)、where子句中使用!=操作符时，将放弃使用索引，因为范围不确定，使用索引效率不高，会被引擎自动改为全表扫描；

> (6)、where子句中应尽量避免对索引字段操作（表达式操作或函数操作），比如select id from test where num/2 = 100应改为num = 200。

> (7)、在使用复合索引时，查询时必须使用到索引的第一个字段，否则索引失效；并且应尽量让字段顺序与索引顺序一致。

> (8)、查询时必须使用正确的数据类型。数据库包含了自动了类型转换，比如纯数字赋值给字符串字段时可以被自动转换，但如果查询时不加引号查询，会导致引擎忽略索引。

> (9)、不使用Select \*，只查询需要的字段。

> (10)、不使用>=，用>

> (11)、(阿里)超过三张表禁止join

### 四、☆分表分库技术(取模分表、水平分割垂直分割) ###

分库:   -----垂直分割

电商项目(将一个项目进行拆分,拆分成多个小项目,每个项目都有自己的数据库,互不影响)(会员数据库|订单数据库|支付数据库)

分表 ---------水平分割

日志表(数据量巨大,根据年份分表,)、大企账号()、手机号(根据前三位分表)

更好的处理方案:水平分割,取模分表

### 五、☆读写分离(Redis也要读写分离,减小io) ###

mycat

### 六、存储过程(比传统sql语句效率高) ###

### 七、配置mysql最大连接数my.ini  my.cnf ###

### 八、mysql服务升级 ###

### 九、随时清理碎片化 ###

### 十、随时清理碎片化 ###

### 十一、(最先)慢查询开启 ###

如何从一个大项目中，迅速的定位执行速度慢的语句. (定位慢查询)

**show status**使用show status查看MySQL服务器状态信息

常用命令

<table> 
 <tbody> 
 <tr> 
 <td style="vertical-align:top;"> --mysql数据库启动了多少时间 show status like 'uptime'; </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> show&nbsp; stauts like 'com_select'&nbsp; show stauts like 'com_insert' ...类推 update&nbsp; delete（显示数据库的查询，更新，添加，删除的次数） </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> show [session|global] status like .... 如果你不写&nbsp; [session|global] 默认是session 会话，指取出当前窗口的执行，如果你想看所有(从mysql 启动到现在，则应该 global) </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> //显示到mysql数据库的连接数 show status like&nbsp; 'connections ';&nbsp; </td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"> //显示慢查询次数 show status like 'slow_queries'; </td> 
 </tr> 
 </tbody> 
</table>

开启慢查询日志

windows下修改my.ini,Linux下修改my.cnf文件

在\[mysqld\]最后增加如下命令

slow\_query\_log= 1  
long\_query\_time= 1  
slow\_query\_log\_file=c:/slow.log  
解释:第一行开启慢查询日志,第二行查询时间为1s(默认为10s)以上,日志存放位置

参数

> slow\_query\_log 慢查询开启状态  
> slow\_query\_log\_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）  
> long\_query\_time 查询超过多少秒才记录

### SQL执行计划 ###

定位执行效率差的SQL，那么当我们定位到一个SQL以后还不算完事，我们还需要知道该SQL的执行计划，比如是全表扫描，还是索引扫描，这些都需要通过EXPLAIN去完成

explain + sql语句(查询结果参数解释)

![70][]

![70 1][]

[-]: https://www.cnblogs.com/knowledgesea/p/3667395.html
[Link 1]: https://blog.csdn.net/zhangliangzi/article/details/52329355
[Link 2]: https://www.cnblogs.com/dongguacai/p/7239599.html
[Link 3]: https://blog.csdn.net/a591243801/article/details/72823916
[Link 4]: https://blog.csdn.net/troubleshooter/article/details/78393476
[70]: /images/20220515/57b46ecb9e2a4373bdd3b0ed3e39bb54.png
[70 1]: /images/20220515/c7f8311b65cc4ceb89c3660c64d00848.png