Mysql优化

2022-04-28 11:32:23   8  举报





雀食蟀

mysql索引

模版推荐

作者其他创作

大纲/内容

存储引擎与索引

mysql 流程图

查看mysql支持的存储引擎 show engines;

二叉树

如果我们需要查找id=12的用户信息，利用我们创建的二叉查找树索引，查找流程如下：  1. 将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点>的右子节点作为当前节点。  2. 继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。  3. 把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即id=12,name=xm。 利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要6次才能找到。

平衡二叉树

在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。

b树

从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。  如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。  如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。 说明每个磁盘块仅仅存储一个键值和数据！ 可以想象到二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！ B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。 假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下：  1. 先找到根节点也就是页1，判断28在键值17和35之间，我们那么我们根据页1中的指针p2找到页3。  2. 将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8。  3. 将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)。

b+树

1.B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。另外，B+树的阶数是等于键值的数量的，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。&nbsp; 2.因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的。

B+树索引真正的实现方式聚簇索引和非聚簇索引

1. 聚集索引（聚簇索引）：以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。  2. 非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。   明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。

利用聚集索引查找数据

1. 一般根节点都是常驻内存的，也就是说页1已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。 从内存中读取到页1，要查找这个id>=18 and id <40或者范围值，我们首先需要找到id=18的键值。 从页1中我们可以找到键值18，此时我们需要根据指针p2，定位到页3。 2. 要从页3中查找数据，我们就需要拿着p2指针去磁盘中进行读取页3。 从磁盘中读取页3后将页3放入内存中，然后进行查找，我们可以找到键值18，然后再拿到页3中的指针p1，定位到页8。 3. 同样的页8页不在内存中，我们需要再去磁盘中将页8读取到内存中。 将页8读取到内存中后。 因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值18。 此时因为已经到数据页了，此时我们已经找到一条满足条件的数据了，就是键值18对应的数据。 因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页8中的键值依次进行遍历查找并匹配满足条件的数据。 我们可以一直找到键值为22的数据，然后页8中就没有数据了，此时我们需要拿着页8中的p指针去读取页9中的数据。 4. 因为页9不在内存中，就又会加载页9到内存中，并通过和页8中一样的方式进行数据的查找，直到将页12加载到内存中，发现41大于40，此时不满足条件。 那么查找到此终止。

利用非聚集索引查找数据

在叶子节点中，不在存储所有的数据了，存储的是键值和主键。 对于叶子节点中的x-y，比如1-1。左边的1表示的是索引的键值，右边的1表示的是主键值。如果我们要找到幸运数字为33的用户信息，对应的sql语句为select * from user where luckNum=33。 查找的流程跟聚集索引一样，这里就不详细介绍了。我们最终会找到主键值47，找到主键后我们需要再到聚集索引中查找具体对应的数据信息，此时又回到了聚集索引的查找流程。   ———————————————— 版权声明：本文为CSDN博主「卌卌y」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/qq_35493807/article/details/122827319

架构优化

使用缓存

读写分离(集群、主从复制)

用户写数据只往master节点写，而读的请求分摊到各个slave节点上

binlog是实现MySQL主从复制功能的核心组件。master节点会将所有的写操作记录到binlog中，slave节点会有专门的I/O线程读取master节点的binlog，将写操作同步到当前所在的slave节点。

查看  开启 binlog  show variables like 'log_bin'; 查看binlog日志存放的位置 show global variables like '%log%'; 查看binlog 日志内容 show binlog events in 'binlog.000016' ;

主库配置 #主服务器唯一Id[必填] server-id=1 #启用二进制日志[必填] #log-bin=mysql-bin  log-bin=D:\work\application\mysql-8.0.27-winx64\data\mysql-bin     #开启mysql的binlog日志功能 #主机，读写都可以 read-only=0 #设置不要复制的数据库[可选] #binlog-ignore-db=mysql #设置需要复制的数据库[可选]（输数据库名字）  #binlog-do-db=dlj sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全 binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed expire_logs_days = 7                           #binlog过期清理时间 max_binlog_size = 100m                    #binlog每个日志文件大小 binlog_cache_size = 4m                        #binlog缓存大小 max_binlog_cache_size= 512m              #最大binlog缓存大 binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行 auto-increment-offset = 1     # 自增值的偏移量 auto-increment-increment = 1  # 自增值的自增量

从库配置 #主从复制配置  #从服务器唯一Id  server-id=2  #只读，对拥有super权限的账号是不生效的  read_only = 1 log-bin=D:\mysql-8.0.27-winx64\data\mysql-bin

创建并授权用来做复制的用户 create user 'liushuai'@'10.133.46.114' IDENTIFIED by 'liushuai'; ALTER USER 'liushuai'@'10.133.46.114' IDENTIFIED WITH mysql_native_password BY 'liushuai'; show grants for 'liushuai'@'10.133.46.114'; GRANT REPLICATION SLAVE ON *.* TO 'liushuai'@'10.133.46.114'; select * from  user   where user='liushuai' update user set host='%' where user='root'; select * from user root; flush privileges; show master status; create table user33(     id   VARCHAR(2),     name VARCHAR(2),     age VARCHAR(2) ) CREATE DATABASE demo5;

从库执行sql MASTER_HOST ：master主机名(或IP地址) MASTER_PORT ：mysql实例端口号 MASTER_USER：用户名 MASTER_PASSWORD：密码 MASTER_AUTO_POSITION：如果进行change master to时使用MASTER_AUTO_POSITION = 1，slave连接master将使用基于GTID的复制协议 MASTER_RETRY_COUNT：重连次数 MASTER_HEARTBEAT_PERIOD：复制心跳的周期 CHANGE MASTER TO MASTER_HOST='10.133.47.103', MASTER_PORT=3306, MASTER_USER='liushuai', MASTER_PASSWORD='liushuai', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=1176 ,MASTER_RETRY_COUNT = 60,MASTER_HEARTBEAT_PERIOD = 10000; start slave; stop slave; reset master; show slave status; set global sql_slave_skip_counter =1;

连接配置优化

服务端配置

服务端的最大连接数 show variables like 'max_connections'; set GLOBAL max_connections=200 查询ip  连接数状态 SELECT substring_index(host, ':',1) AS host_name, state, count(*) FROM information_schema.processlist GROUP BY state, host_name; 过一段时间释放不活动的连接 show variables like 'wait_timeout';

服务端配置

应用侧与mysql底层的连接，是基于TCP协议的长链接，而TCP协议，需要经过三次握手和四次挥手来实现建连和释放。如果我每次执行sql都重新建立一个新的连接的话，那就要不断握手和挥手，这很耗时。所以一般会建立一个长连接池，连接用完之后，塞到连接池里，下次要执行sql的时候，再从里面捞一条连接出来用，非常环保。

SQL语句的优化

1.查询语句应该尽量避免全表扫描，首先应该考虑在Where子句以及OrderBy子句上建立索引，但是每一条SQL语句最多只会走一条索引，而建立过多的索引会带来插入和更新时的开销，同时对于区分度不大的字段，应该尽量避免建立索引;

2.可以在查询语句前使用explain关键字，查看SQL语句的执行计划，判断该查询语句是否使用了索引;

3.应尽量使用EXIST和NOT EXIST代替 IN和NOT IN，因为后者很有可能导致全表扫描放弃使用索引；

4.应尽量避免在Where子句中使用or作为连接条件，因为同样会导致全表扫描；