MySQL数据库优化

一、MySQL会遇到的问题：

　　1、高并发的读写效率低问题----解决方案：集群，分布式。

　　2、海量数据的读写效率低问题-----解决方案：分表，分库。

　　3、高可用和高扩展----解决方案：动态扩展服务器、防止单点故障、容灾。

 

 

二、关系型数据库优化：（原则：先单机，后多机。）

　　2.1 单机优化方案：

　　　　1.慢SQL的定义-->分析慢SQL--解决慢SQL。　　　

　　　　2. 表的设计、索引、引擎的优化。
       　　3. 分表(垂直分表、水平分表)、分区、分库 的优化。
      　　 4. 缓存做集群。

　　　　5.SQL语句优化

 

　　

　　2.2 多机优化方案（分为多个数据库）：

 

　　　　1. 读写分离（要保证主从同步）：

 

　　　　　　    28原则：如果有10个数据库，则2个专门做增删改的数据库，8个专门做查询的数据库。

 

　　　　2. 缓存做集群

 

三、定位慢SQL　

 

　　3.1  查看数据库状态：

 

　　　　3.1.1  查看运行时间：show status like ‘uptime’;

 

　　　　3.1.2  CRUD执行次数：

 

　　　　　　Show status like ‘Com_%’;

 

　　　　　　Show status like ‘Com_update%’

 

　　　　　　Show status like ‘Com_delete%’

 

　　　　　　Show status like ‘Com_insert%’

 

　　　　　　Show status like ‘Com_select%’

 

1. create drop truncate（重置表） delete

 

　　　　GLOBAL（全局）、SESSION（当前会话）

 

　　　　show  global  status  like  ‘Com_select%’;

 

　　　　show  session  status  like  ‘Com_select%’;

 

　　　　3.1.3 查看所有连接数：

 

　　　　　　Show status like ‘connections’;

 

　　　　　　Show status like ‘Max_used_connections’;

 

 

 

　　3.2  查看慢查询

 

　　　　3.2.1  查看慢查询的条数

 

　　　　　　Show status like ‘slow_queries’;

 

　　　　　　Select * from dept;

 

　　　　　　Select * from emp where ....;

 

　　　　3.2.2  告诉MySQL什么是慢，设置慢查询阀值。

 

　　　　　　Show variables like ‘long_query_time’;

 

　　　　　　Set long_query_time=0.5

 

　　　　　　Show VARIABLES like ‘%slow%’;

 

四、分析慢SQL

　　(1) Explain select * from emp where empno=459 ：分析这条查询语句

 

 

 

五、单机优化---表设计和引擎

　　5.1 表结构设计---3NF和反3NF

　　　　1NF：表的列：具有原子性，不可再分解。

　　　　2NF:主键唯一性。

　　　　3NF:表中无冗余数据。

　　　　反3NF：适当增加字段，允许冗余-----可提高查询效率。

　　5.2 MySQL存储引擎

　　　　注意：在建表的时候可以指定存储引擎，不指定则为默认，该默认是在my.ini文件里配置。

　　　　MySQL引擎分类：myisam、innodb、memory。

　　　　5.2.1 MyISAM 和 INNODB的区别（主要区别）　　　

　　　　　　1. 事务安全：MyISAM不支持事务，INNODB支持。

 

　　　　　　2. 查询和添加速度：MyISAM速度快，INNODB速度慢。

 

　　　　　　3. 支持全文索引：MyIsam支持，innodb不支持。

 

　　　　　　4. 锁机制： MyIsam表锁 innodb行锁。

 

　　　　　　5. 外键：MyISAM 不支持外键， INNODB支持外键. (通常不设置外键，通常是在程序中保证数据的一致)。

　　　　5.2.2 引擎使用场景　　　

 　　　　　　MyISAM存储引擎：

　　　　　　　　如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. 比如 bbs 中的 发帖表，回复表.

　　　　　　INNODB存储引擎:

　　　　　　　　对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.

　　　5.2.3 添加、修改引擎

　　　　　　1、创建表时指定存储引擎：

　　　　　　　　Create table 表名(字段列表) engine 存储引擎名称;

　　　　　　2、修改存储引擎：

　　　　　　　　 alter table table_name engine=innodb;

六、单机优化---索引

　　 概念：只是一个数据结构，用于提升查询性能的，但是删除，添加，修改就相对慢了。是以空间换时间的策略。
   分类：
     普通索引：允许重复的值出现，可以在任何字段上面添加。

　　　　唯一索引：不能有重复，可以在任何字段上面添加，可以为null,并且可以有多个null。

　　　　主键索引：唯一且没有null值。

 　　　全文索引：es操作代替了数据的查询。

　 6.1 Mysql常用引擎允许的索引类型：

 

 

 　　　hash：就像Map,通过一个key直接就能找到value。

　　　 B-tree：使用二叉树保存索引。

 

 

 　　6.2 索引的使用　　

　　　　添加索引：

　　　　　　方法一：create index index_emp_empno on emp(empno);

 

　　　　　　方法二：alter table emp add index index_emp_empno(empno);

 

　　　　查询索引：show index from emp;

 

　　　　删除索引：alter table emp drop index index_emp_empno;

 

　　　　修改索引：删了再加。

　　6.3 适合创建索引的条件：

 

　　　　1. 肯定在where条件经常使用，或经常排序，order by后面的字段。

 

　　　　2. 该字段的内容不能平频繁变化。

　　　　3. 该字段的内容不是唯一的几个值(如：sex)。

 

　　6.4 创建索引的小技巧：

　　　　根据索引列的多少区分：　

　　　　　　普通索引（单列索引）：该索引只在一个列上面创建。

 

　　　　　　复合索引（多列索引）：该索引只在多个列上面创建。

 

　　　　6.4.1 对于创建的复合索引，如果第一个索引没有用到(dname)，这个索引就没有用。　　

　　　　　　alter table dept add index my_indx (dname,loc); 　　// dname 是左边的列,loc就是右边的列

 

　　　　　　explain select * from dept where dname='aaa'　　　// 索引有效

　　　　　　explain select * from dept where loc='aaa'　　// 索引无效（没有用到dname）

　　　　6.4.2 对于使用like的查询，'%aaa’:使用索引无效,‘aaa%’:使用索引有效，‘%aaa%’：使用索引无效。

　　　　　　explain select * from dept where dname like '%aaa'  //索引无效

　　　　　　explain select * from dept where dname like 'aaa%'　　//索引有效

　　　　　　explain select * from dept where dname like '%aaa%'  //索引无效

　　　　6.4.3 在有or的Sql语句中，只要有一个条件不能使用索引，则这条语句的索引无效；换言之，就是要求使用的所有字段,都必须单独使用时能使用索引。　　　　　

　　　　　　　explain select * from dept where dname = 'aaa';　　//索引有效

 

   　　　　　　explain select * from dept where loc = 'aaa';　　　　//索引无效

 

    　　　　　　select * from dept where dname=’xxx’ or loc=’xx’;　　//因此索引无效

　　　　4.4.4 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则使用索引无效。

　　　　　　　　expain select * from dept where dname=‘111’; //索引有效

　　　　　　　　expain select * from dept where dname=qqq;　　//会报错

　　　　4.4.5 如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

 

   　　　　　　如：表里面只有一条记录

 

 

原文链接:https://www.cnblogs.com/wanghj-15/p/11470347.html
如有疑问请与原作者联系

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有