MySQL设计优化

最新推荐文章于 2021-10-18 17:27:33 发布

额滴神--JH

最新推荐文章于 2021-10-18 17:27:33 发布

阅读量478

点赞数 1

分类专栏： MySQL 文章标签： MySql 优化

本文链接：https://blog.csdn.net/weixin_40395050/article/details/96971264

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL优化

索引

为什么要用索引？

提高查询效率，索引（Index）是帮助 MySQL 高效获取数据的数据结构

为什么加了索引能够提高查询效率？

首先明白为什么索引会增加速度，DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。

索引的分类：

主键索引------primary key

唯一索引------unique

组合索引

全文索引------fulltext

普通索引------index

不要使用like 使用全文索引

全文索引的缺点：不支持事务

会用第三方搜索引擎代替全文索引

执行计划–EXPLAIN

慢查询

分析MySQL语句查询性能的方法除了使用 EXPLAIN 输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为“慢查询”。

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

参数说明

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

设置步骤

1.查看慢查询相关参数

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /mysql/data/localhost-slow.log   |
+---------------------------+----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

2.设置方法

方法一：全局变量设置

将 slow_query_log 全局变量设置为“ON”状态

mysql> set global slow_query_log='ON';

设置慢查询日志存放的位置

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

查询超过1秒就记录

mysql> set global long_query_time=1;

方法二：配置文件设置

修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

3.重启MySQL服务

service mysqld restart

4.查看设置后的参数

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

索引优缺点：

优点：

1.通过创建唯一索引，可以保证数据库每一行数据的唯一性

2.可以大大提高查询速度

3.可以加速表与表的连接

4.可以显著的减少查询中分组和排序的时间。

缺点：

增加、删除慢，需要修改索引文件

1.创建索引和维护索引需要时间，而且数据量越大时间越长2.创建索引需要占据磁盘的空间，如果有大量的索引，可能比数据文件更快达到最大文件尺寸3.当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

什么字段适合加索引？–索引的设计原则

查询次数比较多，不经常更新的字段，有非常多不同的值

建立索引 where条件需要查询，并且值非常多的不同

1.不是越多越好

2.常更新的表越少越好

3.数据量小的表最好不要建立索引

4.不同的值比较多的列才需要建立索引

5.某种数据本身具备唯一性的时候，建立唯一性索引，可以保证定义的列的数据完整性，以提高查询熟度

6.频繁进行排序或分组的列(group by或者是order by)可以建立索引，提高搜索速度

7.经常用于查询条件的字段应该建立索引

索引的注意事项:

1.如果使用组合索引第一个可以不用第二个一起作为条件查找----会使用组合索引

第二个条件，不使用第一个条件-----不会使用组合索引

第一个条件和第二个条件一起使用-----会使用组合索引

2.条件中加like的效果

使用索引的时候，不要like‘%%’，否则会全表扫描

使用like的时候开头不要%

3.使用or，条件都必须加上索引，只要有一个条件不佳索引，不会使用索引查找

4.判断是否为空的时候 ‘ ’和is null会使用索引 =null不会使用索引

5.group by分组不会使用索引，会进行全表扫描

6.分组需要高效率，禁止排序order by null ，group by会默认分组完成之后进行排序

7.select * from userId>=101 select *from userId>100 不要使用>= 会进行两次全表扫描

8.in not in 加了索引都会失效

9.查询量比较大，使用缓存，分表，分页

索引实现原理折半查找 B-Tree

二分法（折半）查找

在这里插入图片描述

//二分查找法(折半查找法)
    public static int halfSearch(int[] arr,int number){
        int min =0;  //最小下标
        int max =arr.length-1;   //最大下标
        int mid = 0;  //中间下标
        while (min<max){
            //没找到,更新范围继续找
            mid = (min+max)/2;
            if (arr[mid]>number){   //number在mid的左边
                max = mid-1;  //改变最大下标
            }else if(arr[mid]<number){  //number在mid的右边
                min = mid+1;  //改变最小下标
            }else{
                return  mid;
            }
        }
        return -1;
    }

B-Tree

https://blog.csdn.net/v_JULY_v/article/details/6530142/

mysql数据引擎

InnoDB、Memory、Myisam

innodb----主流使用innodb，事务机制

innodb与myisam区别是什么？

批量添加–myisam效率高

innodb----事务安全

锁机制 myisam表锁 innodb行锁

数据结构 myisam支持全文检索 innodb不支持

都支持b-tree数据结构存储

清理碎片化

只存在myisam数据引擎

在MySQL中，如果我们删除了表中的大量数据，或者我们对含有可变长度文本数据类型(VARCHAR，TEXT或BLOB)的表进行了很多更改，不过被删除的数据记录仍然被保持在MySQL的链接清单中，因此文件的大小并不会随着数据的删除而减小。

当我们确定数据需要被清除掉时，那么这些数据就已经成了无用的数据，但是按照MySQL的处理方式，这些数据仍然会占用我们的磁盘空间，从而造成了极大的资源浪费。不仅如此，过大的数据文件还会导致MySQL执行相关数据操作时需要耗费更多的性能和时间。因此，对MySQL的某些进行碎片整理是非常有必要的。

清理碎片化语法

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table_name1 [, table_name2] ...

备注：
1.MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。
3.在OPTIMIZE TABLE运行过程中，MySQL会锁定表。
4.默认情况下，直接对InnoDB引擎的使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者d --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。

MySQL读写分离—mycat==nginx

1.what读写分离？

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

2.why那么为什么要读写分离？

因为数据库的“写”（写10000条数据到oracle可能要3分钟）操作是比较耗时的。
但是数据库的“读”（从oracle读10000条数据可能只要5秒钟）。
所以读写分离，解决的是，数据库的写入，影响了查询的效率。

3.when什么时候要读写分离？

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用，利用数据库主从同步。可以减少数据库压力，提高性能。当然，数据库也有其它优化方案。memcache 或是表折分，或是搜索引擎。都是解决方法。

4.主从复制与读写分离

https://img-blog.csdn.net/20171213163138791?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMzQyMTYyOQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast

4.1、mysql支持的复制类型

1）基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。

2）基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。

3）混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

4.2、复制的工作过程

1）在每个事务更新数据完成之前，master在二进制日志记录这些改变。写入二进制日志完成后，master通知存储引擎提交事务。

2） Slave将master的binary log复制到其中继日志。首先slave开始一个工作线程（I/O），I/O线程在master上打开一个普通的连接，然后开始binlog dump process。binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件，I/O线程将这些事件写入中继日志。

3） Sql slave thread（sql从线程）处理该过程的最后一步，sql线程从中继日志读取事件，并重放其中的事件而更新slave数据，使其与master中的数据一致，只要该线程与I/O线程保持一致，中继日志通常会位于os缓存中，所以中继日志的开销很小。

5、 mysql读写分离原理

读写分离就是在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全。

6、前较为常见的Mysql读写分离分为以下两种：

基于程序代码内部实现

在代码中根据select 、insert进行路由分类，这类方法也是目前生产环境下应用最广泛的。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手。

基于中间代理层实现

代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到，后端数据库，有以下代表性的程序。

常见的mysql代理开源项目

1）mysql_proxy。mysql_proxy是Mysql的一个开源项目，通过其自带的lua脚本进行sql判断。

（2）Atlas。是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。

（3）Amoeba。由阿里巴巴集团在职员工陈思儒使用序java语言进行开发，阿里巴巴集团将其用户生产环境下，但是他并不支持事物以及存储过程。

经过上述简单的比较，不是所有的应用都能够在基于程序代码中实现读写分离，像一些大型的java应用，如果在程序代码中实现读写分离对代码的改动就较大，所以，像这种应用一般会考虑使用代理层来实现，那么今天就使用Amoeba为例，完成主从复制和读写分离。

MySQLProxy介绍

下面使用MySQL官方提供的数据库代理层产品MySQLProxy搭建读写分离。

MySQLProxy实际上是在客户端请求与MySQLServer之间建立了一个连接池。所有客户端请求都是发向MySQLProxy，然后经由MySQLProxy进行相应的分析，判断出是读操作还是写操作，分发至对应的MySQLServer上。对于多节点Slave集群，也可以起做到负载均衡的效果。

https://img-blog.csdn.net/20171213165947064?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMzQyMTYyOQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast

https://img-blog.csdn.net/20171213170006192?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdTAxMzQyMTYyOQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast

--作者：额滴神

额滴神--JH

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL设计优化

MySQL优化索引为什么要用索引？提高查询效率，索引（Index）是帮助 MySQL 高效获取数据的数据结构为什么加了索引能够提高查询效率？首先明白为什么索引会增加速度，DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查...
复制链接

扫一扫