mysql高级操作(深度优化，缓存，主从，集群)

一.分库分表

1.垂直拆分

(1)垂直拆表：拆字段

a.大表拆分成小表，大字符串内容字段，不常用字段信息，拆分为另外一张表。

b.好处是避免磁盘因为字段过大导致 索引跨页。提高查询效率

(2)垂直拆库：同业务类型分为一个库

a.在垂直拆表的基础上，将一个系统的不同业务类型进行拆分

b.好处是 降低单数据库服务的压力（物理存储、内存、IO等）、降低单机故障的影响面

2.水平拆分

(1)水平拆表：一般按日期

a.将数据按照某种维度拆分为多张表，但是由于多张表还是从属于一个库，其降低锁粒度，一定程度提升查询性能，还是有io瓶颈。

(2)水平拆库：一个表按维度拆分到多库

a.将数据按照某种维度分拆到多个库中，降低单机单库的压力，提升读写性能

水平分表实操：

1. 确定要水平拆分的表：主表user
2. 创建拆分的表 user1,user2,user3,user4它们和主表user一样的字段。

3. DROP table IF EXISTS user1;
   create table user1( id int(32),name varchar(255)
   
   )ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
   Create table user2 like user1;
   Create table user3 like user1;
   Create table user4 like user1;

    4.创建分表管理表users,里面装着所有分表(user1,user2,user3,user4)，本身没有数据。

DROP table IF EXISTS users;
create table users( id int(32),name varchar(255)

)ENGINE=MERGE UNION=(user1,user2,user3,user4)
 INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1 ;

5.按照某一个维度，将数据插入分到各个分表中。

insert into user1(字段名) select 字段名 from member where 日期='2023-01'

insert into user2(字段名) select 字段名 from member where 日期='2023-02'

insert into user3(字段名) select 字段名 from member where 日期='2023-03'

6.查询数据，按管理表查询

select * from users where .....

二.存储引擎设置优化

1.存储引擎内存配置

1. 选择合适的存储引擎 大量读数据选择（myIsam），如果是需要事务选择默认引擎innodb,虽然查询效率低下，但是将innodb设置属性：innodb_flush_log_at_trx_commit = 2 可以获得接近的读取性能 (相差百倍) 。
2. 内存大小 innodb_buffer_pool_size 配置。
3. 展示数据库内存大小mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%'

2.数据预热

a.默认情况，只有某条数据被读取一次，才会缓存在 innodb_buffer_pool。所以，数据库刚刚启动，需要进行数据预热，将磁盘上的所有数据缓存到内存中。数据预热可以提高读取速度。
 

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb
;

b.执行上面sql

mysql -uroot -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql

三.集群

复制：MySQL主/从-主/主集群安装部署_mysql集群安装_Aidon-东哥博客的博客-CSDN博客