java 如何分表查询字段 java mysql分表

转载

mob64ca13feda16 2024-05-30 08:25:09

文章标签 java 如何分表查询字段 mysql 隔离级别数据库 文章分类 Java 后端开发

oracle和mysql分页的区别；oracle分页的原理；

数据库隔离级别有哪些，各自的含义是什么，MYSQL默认的隔离级别是是什么

数据库查询执行流程

数据库怎么优化；索引的原因；为什么要使用B+树存储索引；

什么情况要加索引,哪些字段上适合加索引；

使用索引的优缺点？

MySQL如何定位慢sql

Mycat原理

mycat会用吗；平时会自己弄分库分表吗；什么时候需要分库什么时候需要分表；

你们现在的数据量大吗；是怎么进行拆分存储的；数据库是分库的对吧；他的分库的规则是什么样的；

读锁和写锁的实现方式，加了读锁，其他资源能不能读，加了写锁其他资源能不能读；

oracle和mysql分页的区别；oracle分页的原理；

Mysql使用limit分页

select * from stu limit m, n; //m = (startPage-1)*pageSize,n = pageSize

Oracle使用rownum分页

select * from (
select rownum rn,a.* from table_name a where rownum <= x
//结束行，x = startPage*pageSize
)
where rn >= y; //起始行，y = (startPage-1)*pageSize+1

（1）>= y，<= x表示从第y行（起始行）~x行（结束行）。

（2）rownum只能比较小于，不能比较大于，因为rownum是先查询后排序的，例如你的条件为rownum>1，当查询到第一条数据，rownum为1，则不符合条件。第2、3...类似，一直不符合条件，所以一直没有返回结果。所以查询的时候需要设置别名，然后查询完成之后再通过调用别名进行大于的判断。

数据库隔离级别有哪些，各自的含义是什么，MYSQL默认的隔离级别是是什么

【1】Read Uncommitted（读取未提交内容）：出现脏读，也就是可能读取到其他会话中未提交事务修改的数据。
【2】Read Committed（读取已提交内容）：不可重复读，只能读取到已经提交的数据。Oracle 等数据库默认的隔离级别。
【3】Repeatable Read（可重复读）：出现幻读。在同一个事务内的查询都和事务开始时刻一致。InnoDB默认级别。
【4】Serializable（串行读）：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

MySQL 默认的隔离级别：Repeatable Read（可重读）级别。
Oracle 默认的隔离级别：Read Committed（读取已提交的）级别。

【可重复读实现原理】：使用MVCC（多版本并发控制）。InnoDB为每行记录添加了一个版本号（系统版本号），每当修改数据时，版本号加一。在读取事务开始时，系统会给事务一个当前版本号，事务会读取版本号<=当前版本号的数据，这时就算另一个事务插入一个数据，并立马提交，新插入这条数据的版本号会比读取事务的版本号高，因此读取事务读的数据还是不会变。

如果数据库并发控制引擎是单纯的封锁协议机制，则应该在读取数据的时候，判断数据项是不是其他事务更新过的。可是InnoDB没有这么做，而是通过如下方式，在RR隔离级别下为事务设置了一个“一致性读视图（即快照）”，之后读取数据，就是根据这个快照来获取，这样，就不能看到他晚于本事务的事务对已有记录的更新（更新生成新版本，必然不在旧的快照所限定的范围内）。

static my_bool snapshot_handlerton(THD *thd, plugin_ref plugin, void *arg){
    handlerton *hton= plugin_data<handlerton*>(plugin);
    if (hton->state == SHOW_OPTION_YES && hton->start_consistent_snapshot){ //隔离级别是RR时start_consistent_snapshot才被赋值
        hton->start_consistent_snapshot(hton, thd); //对于InnoDB，实际执行innobase_start_trx_and_assign_read_view()函数
        *((bool *)arg)= false;
    }
    return FALSE;
}

在事务开始的时候 trans_begin() 会调用 snapshot_handlerton() 函数指针即使用 innobase_start_trx_and_assign_read_view() 函数在可重复读隔离级别下创建一个快照，其他隔离级别则不创建快照。如下图：

innobase_start_trx_and_assign_read_view(  //在可重复读隔离级别下创建一个快照，其他隔离级别则不创建快照
    handlerton*    hton,    /*!< in: InnoDB handlerton */
    THD*           thd)     /*!< in: MySQL thread handle of the user for whom the transaction should be committed */
{...
    if(trx->isolation_level == TRX_ISO_REPEATABLE_READ) { //如果是RR隔离级别，则给read view赋值，即构建一致性视图
        trx_assign_read_view(trx);  //为读一致性视图（快照）赋一个值，注意在store_lock()中应隔离级别小于RR才关闭快照
    }else{
        push_warning_printf(thd, Sql_condition::SL_WARNING,
                    HA_ERR_UNSUPPORTED,
                    "InnoDB: WITH CONSISTENT SNAPSHOT"
                    " was ignored because this phrase"
                    " can only be used with"
                    " REPEATABLE READ isolation level.");
    }
...
}

之后，在每条SQL语句执行的时候，根据隔离级别判断是不是要使用一个新的快照，如果是可重复读，则不使用新快照，沿用老的快照，这样就能保证所有的读操作看到的是同一个数据状态；同时也确保了读已提交隔离级别下一个事务块内的不同语句的读操作看到的不是同一个数据状态。

ha_innobase::store_lock(...)
{...
    if (lock_type != TL_IGNORE && trx->n_mysql_tables_in_use == 0) {
        trx->isolation_level = innobase_map_isolation_level((enum_tx_isolation) thd_tx_isolation(thd));
        if (trx->isolation_level <= TRX_ISO_READ_COMMITTED  //隔离级别小于等于读已提交，关闭老的快照。可重复读不关闭老快照所以可以沿用
            && MVCC::is_view_active(trx->read_view)) {
            /* At low transaction isolation levels we let each consistent read set its own snapshot */
            mutex_enter(&trx_sys->mutex);
            trx_sys->mvcc->view_close(trx->read_view, true);  //隔离级别小，关闭快照，这样下一条SQL执行时，将获取新快照
            mutex_exit(&trx_sys->mutex);
        }
    }
...
}

从上面的分析可以看出，InnoDB的可重复读的实现，利用了实现 MVCC技术的快照技术。这是 MVCC 和基于封锁技术这两个并非控制技术的结合之处。

数据库查询执行流程

java 如何分表查询字段 java mysql分表_数据库

数据库怎么优化；索引的原因；为什么要使用B+树存储索引；

为什么要使用B+树存储索引；
B+树是在B树的基础上改造，它的数据都在叶子节点，同时，叶子节点之间还加了指针形成链表

这是一个4路B+树，它的数据都在叶子节点，并且有链路相连。

java 如何分表查询字段 java mysql分表_mysql_02

为什么要这样设计呢？

B+树在数据库的索引中用到最多，数据库的select操作，有时会查找多条，B树查询需要做局部的中序遍历，可能要跨层访问，而B+树所有的数据都在叶子节点，不用跨层，同时有链表结构，只需要找到首尾，通过链表就能把所有数据查询出来

比如，找7~9，只需要在叶子节点中就能找到。

数据库优化的几个方面

1. SQL以及索引的优化

什么情况要加索引,哪些字段上适合加索引；

表的主键、外键必须有索引；

经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

经常与其他表进行连接的表，在连接字段上应该建立索引；

索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

使用索引的优缺点？

索引就像书的目录一样可以非常快速的定位到书的页面

优点：提高查询效率，没有索引的话查询数据库表会进行全表扫描

缺点：插入慢，占用硬盘空间

MySQL如何定位慢sql

步骤1：查询是否开启了慢查询

mysql> show variables like '%slow%';
+---------------------------+--------------------------------+
| Variable_name             | Value                          |
+---------------------------+--------------------------------+
| log_slow_admin_statements | OFF                            |
| log_slow_slave_statements | OFF                            |
| slow_launch_time          | 2                              |
| slow_query_log            | ON                             |
| slow_query_log_file       | /data/mysql/localhost-slow.log |
+---------------------------+--------------------------------+
5 rows in set (0.01 sec)

mysql>

我这里是开启了，没有开启的，直接set global slow_query_log=on;就ok了。
步骤2：设置慢查询的时间限制
mysql默认的慢查询时间是10秒，可以设置成其它的时间。

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.03 sec)

mysql> set long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql>

set global 只是全局session生效，重启后失效,如果需要以上配置永久生效，需要在mysql.ini（linux my.cnf）中配置

步骤3：查看慢查询
show status like ‘slow_queries’;
它会显示慢查询sql的数目，具体的sql就在上面的Log file日志中可以看到。

mysql> show status like 'slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set (0.01 sec)

mysql>

其它命令

show processlist：查看哪些线程在运行；

show open tables：查看哪些表在使用。

慢查询分析日志
改一下慢查询配置

mysql> set long_query_time=0.1;
Query OK, 0 rows affected (0.05 sec)

mysql>

执行几条慢的SQL

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|   100005 |
+----------+
1 row in set (0.28 sec)

mysql> select * from users;
...
...
100005 rows in set (1.41 sec)

mysql>

mysql> select count(*) from user_address_copy;
+----------+
| count(*) |
+----------+
|    30006 |
+----------+
1 row in set (0.08 sec)

mysql> select * from user_address_copy;
...
...
30006 rows in set (0.39 sec)

mysql>

vim 打开慢查询记录的文件slow_query_log_file | /data/mysql/localhost-slow.log

vim /data/mysql/localhost-slow.log

localhost-slow.log 内容如下：

/software/mysql/bin/mysqld, Version: 5.7.24 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /software/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2018-12-08T03:08:23.877322Z
# User@Host: root[root] @ localhost []  Id:    24
# Query_time: 0.551358  Lock_time: 0.000514 Rows_sent: 1  Rows_examined: 100005
use test;
SET timestamp=1544238503;
select count(*) from users;
# Time: 2018-12-08T03:09:06.038256Z
# User@Host: root[root] @ localhost []  Id:    24
# Query_time: 1.401716  Lock_time: 0.000220 Rows_sent: 100005  Rows_examined: 100005
SET timestamp=1544238546;
select * from users;
# Time: 2018-12-08T03:12:03.207302Z
# User@Host: root[root] @ localhost []  Id:    24
# Query_time: 0.395499  Lock_time: 0.000378 Rows_sent: 30006  Rows_examined: 30006
SET timestamp=1544238723;
select * from user_address_copy;

Time ：日志记录的时间

User@Host：执行的用户及主机

Query_time：查询耗费时间 Lock_time 锁表时间 Rows_sent 发送给请求方的记录条数 Rows_examined 语句扫描的记录条数

SET timestamp 语句执行的时间点

select .... 执行的具体语句

慢查询日志分析工具

这里以MySQL为例，最常见的方式是，由自带的慢查询日志或者开源的慢查询系统定位到具体的出问题的SQL，然后使用explain、profile等工具来逐步调优，最后经过测试达到效果后上线。

2. 合理的数据库是设计

根据数据库三范式来进行表结构的设计。

数据库三范式：
第一范式：数据表中每个字段都必须是不可拆分的最小单元，也就是确保每一列的原子性；
第二范式：满足一范式后，表中每一列必须有唯一性，都必须依赖于主键；
第三范式：满足二范式后，表中的每一列只与主键直接相关而不是间接相关(外键也是直接相关)，字段没有冗余。

分表

分表方式
水平分割（按行）、垂直分割(按列)

分表场景
A：根据经验，mysql表数据一般达到百万级别，查询效率就会很低。
B：一张表的某些字段值比较大并且很少使用。可以将这些字段隔离成单独一张表，通过外键关联，例如考试成绩，我们通常关注分数，不关注考试详情。

水平分表策略
按时间分表：当数据有很强的实效性，例如微博的数据，可以按月分割。
按区间分表：例如用户表 1到一百万用一张表，一百万到两百万用一张表。
hash分表：通过一个原始目标id或者是名称按照一定的hash算法计算出数据存储的表名。

3. 系统配置的优化

4. 硬件优化

缓存

搜索引擎

例如：solr，elasticsearch

Mycat原理

MyCAT是一款由阿里Cobar演变而来的用于支持数据库读写分离、分片的分布式中间件。MyCAT可不但支持Oracle、MSSQL、MYSQL、PG、DB2关系型数据库，同时也支持MongoDB等非关系型数据库。

MyCAT主要是通过对SQL的拦截，然后经过一定规则的分片解析、路由分析、读写分离分析、缓存分析等，然后将SQL发给后端真实的数据块，并将返回的结果做适当处理返回给客户端。

java 如何分表查询字段 java mysql分表_java 如何分表查询字段_03

mycat会用吗；平时会自己弄分库分表吗；什么时候需要分库什么时候需要分表；

你们现在的数据量大吗；是怎么进行拆分存储的；数据库是分库的对吧；他的分库的规则是什么样的；

读锁和写锁的实现方式，加了读锁，其他资源能不能读，加了写锁其他资源能不能读；

数据库的并发操作会带来许多问题，比如丢失更新、不可重复读、读脏数据（幽灵数据）等等，为避免该类问题的产生，我们采用了封锁机制，一般DBMS进行并发控制的方法是封锁机制和事务机制。

nnoDB的标准行级锁有哪2种：排它锁（Exclusive Locks，X锁）和共享锁（Share Locks，S锁）：

排它锁也称独占锁、写锁或X锁，若sessionA获得某数据表的排他锁权限，那么sessionA只能对该表进行读取或修改，其他session既不能读取也不能修改该表，更不能对该表加任何类型的锁，直到sessionA释放排它锁权限。加锁方式：lock tables tablename write；
共享锁也称读锁或S锁，若sessionA获得某数据表的共享锁权限，那么任何session（包括sessionA）只能对该表进行读取，不能修改该表，其他session可以对该数据表继续加S锁但不能加X锁，直到sessionA释放共享锁权限。加锁方式：set tables tablename read；

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。