Target audience: Database architects, database administrators, infrastructure engineers, or application developers.
As an open source distributed database, in most cases, TiDB can serve as a scale-out MySQL database without manual sharding. However, because of its distributed nature, there are some differences between traditional relational databases like MySQL and TiDB. For detailed information, see TiDB Compatibility with MySQL.
This article shows you how to efficiently develop high-quality applications with TiDB. You'll get an in-depth look at several TiDB features that will save you hours of work and prevent some common coding errors. You'll also learn important best practices from the many TiDB users that have come before you.
This article is also useful if you're migrating your current applications to TiDB.
This section is an in-depth look at transaction-related issues in TiDB and provides solutions to prevent or resolve them.
TiDB supports snapshot isolation (SI) as is shown in TiDB Passes Jepsen Test for Snapshot Isolation and Single-Key Linearizability. For more information about the TiDB transaction model and translation level, see Transaction Model and TiDB Transaction Isolation Levels.
TiDB uses the optimistic locking mechanism. This means that TiDB automatically retries the transactions that meet conflicts when being committed and backs off when TiDB handles concurrent transactions. However, if you specify the
SELECT FOR UPDATE statement or disable the
tidb_disable_txn_auto_retry variable, this backoff mechanism becomes invalid, and the transactions committed later are rolled back.
SELECT FOR UPDATE is applied in hot record scenarios including the following:
Generally, traditional standalone DBMSs use pessimistic locking to implement
SELECT FOR UPDATE. After a transaction starts, they check locks. If the lock required by the transaction and the current lock on the data are incompatible, a lock wait occurs, and the transaction can be executed after the current lock is released. TiDB executes
SELECT FOR UPDATE just like setting the lock wait time to 0 in a pessimistic locking system, and a transaction encountering lock conflicts fails to commit.
To sum up, TiDB doesn't apply to scenarios where the same row of data is modified concurrently. Using
SELECT FOR UPDATE in a transaction can guarantee data consistency, but only the transaction committed earliest among the concurrently-executed transactions can be executed successfully. TiDB rolls back the remaining requests.
The best practice to handle a hot record scenario is to transfer and implement the counter feature in the cache (like Redis and Codis).
In a database with pessimistic locking applied, the concurrent
SELECT FOR UPDATE transactions queue up and are executed serially. Therefore, the performance is not good. However, handling the counter with the cache improves performance.
According to the ACID (Atomicity, Consistency, Isolation, Durability) theory, concurrent transactions should be isolated from each other to avoid mutual interference. This means that transactions cannot be “nested.”
At the read committed (RC) isolation level, if multiple reads exist in the same transaction, the data is read each time the data is committed. When multiple transactions execute concurrently, multiple read results in a transaction may be very different. These are called “non-repeatable reads.”
Most RDBMS products use RC as the default isolation level. However, sometimes database application developers don't pay attention to the isolation level setting. They even treat non-repeatable reads as a feature, and develop applications based on “nested transactions.”
This section gives an example to explain why the nested transaction model does not apply to TiDB.
220.127.116.11 Example for a set of nested transactions
The following diagram shows the implementation logic for a set of nested transactions. At the top, session 1 and session 2 are two sessions initiated by the application. Down the left side, T1- T8 constitute a timeline. The logic is as follows:
The application opens session 1 at T1, and then performs a query. (Note that in the MySQL protocol, the first statement that follows
begin and accesses the table data is the start of a transaction.)
From T3 to T5, the application opens session 2, writes a row of data, and then commits the data.
The application continues to manipulate session 1.
valvalue of the corresponding row for
k=1that is written by session 2 at T4.
At the RC isolation level, the returned result for the query at T8 is
102, which seems to meet the feature requirement for a nested transaction. But this process does not conform to reality. The diagram above only uses a single thread to simulate a nested transaction. But in the concurrent requests in practical application scenarios, multiple transactions are alternately executed and committed in the timeline. In this case, we can't predict the execution result of the nested transactions.
At the snapshot isolation (SI) or repeatable read (RR) isolation levels, the returned result of any reads before being committed or rolled back corresponds to the consistency status when the transaction starts. The data accessible to the transaction in session 1 at T2 is determined. This is just like taking a snapshot of the database at T2. Even though session 2 is open from T3 to T5, and some data is written in and committed, this does not affect the data read by session 1 at T6. At T6, the row with
k=1 has not been read, so Row 0 is updated. At T8, the returned value for the query is 2. At the SI or RR isolation levels, the degree of isolation is higher, and for concurrent requests, the results are predictable.
18.104.22.168 Solution to the example
For these cases of nested transactions, if you only require that session 1 update the table after session 2 writes data into the table, you only need to control the application logic by adding a commit step after querying the statement at T2. This commits the query transaction in a timely manner. We then perform the rest of the steps on the timeline after T2.
PROPAGATION_NESTEDin the Java Spring Framework (relying on the savepoint mechanism)
The Java Spring Framework supports
PROPAGATION_NESTED propagation, and it starts a nested transaction, which is a subtransaction started independently of the existing transaction. When a nested transaction starts, the database records a savepoint. If the nested transaction fails to be executed, the database rolls back the transaction to the savepoint status. The nested transaction is part of the outer transaction, and it is committed with the outer transaction. The following commands show a savepoint mechanism:
mysql> BEGIN; mysql> INSERT INTO T2 VALUES(100); mysql> SAVEPOINT svp1; mysql> INSERT INTO T2 VALUES(200); mysql> ROLLBACK TO SAVEPOINT svp1; mysql> RELEASE SAVEPOINT svp1; mysql> COMMIT; mysql> SELECT * FROM T2; +------+ | ID | +------+ | 100 | +------+
TiDB does not support the savepoint mechanism, and therefore it does not support the
PROPAGATION_NESTED propagation behavior. If a
PROPAGATION_NESTED propagation behavior is applied to an application based on the Java Spring Framework, you need to adjust the client by removing the nested transaction logic.
TiKV, the storage engine of TiDB is based on RocksDB which adopts the log-structured merge-tree (LSM-tree). For large transactions in a log-based database, you must manually set the available log capacity to a larger value to prevent a single transaction from filling the log.
TiDB sets a hard limit for the number of transactions. Due to the two-phase commit in TiDB, modifying data in large transactions might cause some problems. Therefore, to reduce this impact, TiDB sets a limit for the transaction size.
TiKV stores data in key-value pairs, and the transaction limit is based on the size and number of these pairs. One row of data a table in TiDB (or in the concept of other traditional relational databases) is mapped into a key-value pair, and so is an index. When a table has only two indexes, three key-value pairs are written to the database each time a row of data is inserted. Based on these assumptions, the transaction limit is as follows:
To stay within these limits, you must reduce the number of transactions on certain operations. These include transactions with
Update operations on large amounts of data. The best practice is to rewrite large transactions as paged SQL statements and to commit these statements in phases. TiDB uses
Order by along with the offset of
limit to implement the paging feature. For example:
update tab set value='new_value' where id in (select id from tab order by id limit 0,10000); commit; update tab set value='new_value' where id in (select id from tab order by id limit 10000,10000); commit; update tab set value='new_value' where id in (select id from tab order by id limit 20000,10000); commit; ...
This section introduces allocating principles for auto-increment IDs in TiDB, the best practice for designing auto-increment IDs, and how to use auto-increment IDs in TiDB.
TiDB's auto-increment ID feature is only guaranteed to be automatically monotonic(incremental and unique), but it is not guaranteed to be allocated sequentially. Currently, TiDB allocates IDs in batches. If data is inserted into multiple TiDB servers simultaneously, the allocated IDs are not sequential. When multiple threads concurrently insert data into multiple tidb-server instances, the data inserted later may have a smaller auto-increment ID. TiDB lets you specify
AUTO_INCREMENT for the integer field, but allows only one
AUTO_INCREMENT field in a single table.
Generally, the purpose for setting auto-increment IDs is to use them as the unique restriction for data in a table. Thus, auto-increment IDs are designed as the primary key or unique index, and the property of these columns should be
The type for auto-increment ID columns should be integer. Among multiple integer types, it is recommended to use
bigint. The reason is that auto-increment IDs of the
int type are usually used up even in a standalone database. TiDB handles much more data than a standalone database, and it allocates auto-increment IDs in a multi-threading framework. As a result, the
int type can't meet the demand. Generally, auto-increment IDs don't store negative numbers, and adding the “unsigned” property for a column can double the ID storage capacity. The range for unsigned
ints is from 0 to 4294967295, while the range for unsigned
bigints is from 0 to 18446744073709551615.
In conclusion, the best practice for auto-increment ID design is:
`auto_inc_id` bigint unsigned not null primary key auto_increment comment 'auto-increment ID'
As mentioned previously, auto-increment IDs are mostly designed as primary keys or unique indexes. You shouldn't manually assign values for auto-increment IDs. This may cause frequent update requests for maximum values of many global auto-increment IDs, thereby affecting write performance.
While writing data, you don't have to specify the auto-increment ID column in TiDB. TiDB automatically assigns values for it. Of course, you can specify an incremental ID column for writing. Just set the value to write as
NULL, and TiDB automatically assigns values for it.
mysql> create table autoid(`auto_inc_id` bigint unsigned not null primary key auto_increment comment 'auto-increment ID', b int); Query OK, 0 rows affected (0.25 sec) mysql> insert into autoid(b) values(100); Query OK, 1 row affected (0.19 sec) mysql> insert into autoid(b) values(200); Query OK, 1 row affected (0.03 sec) mysql> insert into autoid(b) values(300); Query OK, 1 row affected (0.06 sec) mysql> insert into autoid values(null,1000); Query OK, 1 row affected (0.04 sec) mysql> insert into autoid values(null,2000); Query OK, 1 row affected (0.03 sec) mysql> insert into autoid values(null,3000); Query OK, 1 row affected (0.03 sec) mysql> select * from autoid; +-------------+------+ | auto_inc_id | b | +-------------+------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | | 4 | 1000 | | 5 | 2000 | | 6 | 3000 | +-------------+------+ 6 rows in set (0.01 sec)
This section describes constraints used in TiDB and digs deep into a constraint check issue in TiDB.
TiDB uses primary keys and unique indexes as UNIQUE constraints for table data. This approach is similar to other database management systems. However, be aware of the following differences:
Drop Columnoperation doesn't let you delete primary key columns.
TiDB doesn't support foreign keys. However as a reference, the parser imports them and shows the relations in the
information_schema. You can only perform cascade operations on multiple tables with foreign keys within an application.
INSERTonly performs UNIQUE constraint check upon committing
TiDB adopts an optimistic transaction model. During the commit phase, TiDB tries to land all write operations into the storage engine, which makes a heavier commit load compared to other databases that use pessimistic transaction models.
The following DML statements involve read operations on table data:
MERGE/UPSERT(not supported by TiDB)
INSERT ON DUPLICATE KEY UPDATE,
INSERT IGNORE ON DUPLICATE KEY UPDATE)
When the table contains a primary key or a unique index, SQL semantics imply a UNIQUE constraint check of the table. This requires the verification to be performed once the DML statements above read the data. TiDB implements this requirement.
In DML, only
INSERT statements are pure write operations. There are exceptions, however. The following special
INSERT statements aren't pure writes:
INSERT ON DUPLICATE KEY UPDATE
INSERT IGNORE ON DUPLICATE KEY UPDATE
A primary key or a unique index on the table implies the SQL semantics in the INSERT statement to read the corresponding record in the table. If nothing is returned for the read. This line of the record is available for the write; if any data is returned, it means this line doesn't satisfy the UNIQUE constraint of the table.
To improve execution efficiency, TiDB doesn't compare the records in the table when it executes
INSERT statements. Instead, it verifies that records are unique when it commits the transaction. This approach saves some read operations. The performance advantages are especially prominent when there are many records involved in the
INSERT statement, such as a batch insert.
However, this implementation is not flawless. When there are too many write records in a single transaction, if there is any conflict of primary key or unique index between the data for write and existing data in the table, TiDB waits until the commit to report the error and rolls back the whole transaction. In the meantime, some applications may capture the information returned by the
INSERT statement. Based on the captured information, TiDB determines subsequent execution logics for the application. By default, TiDB does not return errors for duplicate primary keys or unique indexes during the execution of the
INSERT statement. This type of information is only returned upon
To conclude, TiDB by default doesn't behave as shown in the screenshot below. The first error occurs because the transaction contains two records with the same primary key, which indicates that TiDB performs uniqueness verification on all records involved in the transaction. The second error happens when the transaction is committed, which indicates that TiDB by default only compares the
INSERT records involved in the transaction with the records in the table when it commits the transaction.
mysql> create table t1 (a int key); Query OK, 0 rows affected (0.23 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.15 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(1); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(1); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> commit; ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
TiDB provides a switch –
tidb_constraint_check_in_place – to control this behavior. The default value,
0, means that uniqueness verification is only performed when the transaction is committed instead of while an
INSERT statement is executed. If you're sure about uniqueness verification during the
INSERT execution, you can set this variable to
1. It's recommended that you set it at the session level for every application so that the write performance of batch insert operations won't be affected, as shown below:
So far, we have only noticed that Oracle GoldenGate (OGG) users need to set this system variable, because OGG does the constraint check in place. For OGG users, update the Java Database Connectivity (JDBC) connection string as shown below:
This section describes how TiDB uses indexes and their current restrictions. It also describes how composite indexes are designed in TiDB.
Indexes are also data that take up storage. Like the data in a table, indexes in TiDB are also stored as key-value (KV) pairs in the storage engine. An index row is a key-value pair. If we have a table with 10 indexes, 11 KV pairs are written each time we insert a row of data.
TiDB supports primary key indexes, unique indexes, and secondary indexes. These indexes can be composed of either a single column or multiple columns (which is called a composite index).
The current version of TiDB (V2.1.0) doesn't support bi-directional indexes, full-text indexes, or global indexes for partitioned tables.
TiDB lets you use indexes when the query predicate is among the following:
=, >, <, >=, <=, like ‘...%', not like ‘...%', in, not in, <>, !=, is null, is not null
The optimizer determines whether to use indexes.
You can't use an index when the query predicate is among the following:
like ‘%...'，like ‘%...%'，not like ‘%...'，not like ‘%...%'，<=>
- The current version of TiDB (V2.1.0) hasn't implemented
<=>so it can't use indexes as “is null.”
- The current version of TiDB doesn't support using two indexes simultaneously in one table for the query against the same table. The related optimizations are still under development.
A composite index in TiDB is structured as
key tablekeyname (a,b,c). Like other databases, the ground rule for designing composite indexes is to place data with a high degree of discrimination in front as much as possible. This approach facilitates SQL execution by filtering out fewer rows within a shorter time. It's worth noting that in the current version of TiDB (V.2.1.0 or earlier), the range query for a front column in the composite index terminates the use of subsequent index columns. You can get a better idea about this feature by reviewing the following sample query:
select a,b,c from tablename where a<predicate>'<value1>' and b<predicate>'<value2>' and c<predicate>'<value3>';
If the predicate of condition a is
in, composite index
(a,b,c) can be used with query condition b, for example:
select a,b,c from tablename where a=1 and b<5 and c='abc'
If the predicate is
in for both condition a and condition b, composite index (a,b,c)` can be used for condition c, for example:
select a,b,c from tablename where a in (1,2,3) and b=5 and c='abc'
If the predicate of condition a is neither
in , composite index
(a,b,c) is not available for condition b, in which case an indexless data scan per condition b is performed against the filtered data of condition a. For example:
select a,b,c from tablename where a>1 and b<5 and c='abc'
This is because in TiDB, if the front column in the composite index is used in a range query, queries in subsequent columns proceed as indexless scans within the filtered data of the previous column.
To conclude, when you design composite indexes in TiDB, you should place columns with a high degree of discrimination in front as much as possible, and columns for frequent range queries in the back.
Also, composite index
(a,b,c) is available for the query structured as
select c, count(*) from tablename where a=1 and b=2 group by c, and the
where clause complies with the above principle.
Write-back in batch job scenarios is a general cause of write hotspots. TiKV is a range-based key-value system, where the key determines which Region is written. The value of a key depends on the following:
bigint…), the key is the primary key.
_tidb_rowid) for the table, the key is the hidden column.
SHARD_ROW_ID_BITSto scatter the write hotspot
You can use
SHARD_ROW_ID_BITS to set the number of bits of the shards in the hidden column.
For the tables with non-integer primary keys or without primary keys, TiDB uses an implicit auto-increment ROW ID. When a large number of
INSERT operations occur, the data is written into a single Region, causing a write hotspot.
To mitigate the hotspot issue, you can configure
SHARD_ROW_ID_BITS. The ROW ID is scattered, and the data is written into multiple different Regions. However, setting an overly large value might lead to an excessively large number of remote procedure call (RPC) requests, which increase the CPU and network overheads.
SHARD_ROW_ID_BITS = 4indicates 16 shards
SHARD_ROW_ID_BITS = 6indicates 64 shards
SHARD_ROW_ID_BITS = 0indicates the default, 1 shard
CREATE TABLEsample statement:
CREATE TABLE t (c int) SHARD_ROW_ID_BITS = 4
ALTER TABLEsample statement:
ALTER TABLE t SHARD_ROW_ID_BITS = 4
You can use a partitioned table to scatter the data from one table into multiple physical tables. With properly-designed partition rules, you can use the partitioned table to further avoid write hotspot issues.
This section describes some SQL syntax best practices in TiDB.
create table as selectis not supported
The current version of TiDB does not support the
create table as select … statement. To achieve the same result, you must modify two statements in combination:
create table like …and
insert into select ….
create table like… supports replicating the schema of the corresponding table.
GROUP BYto guarantee stable result sets
For convenience, if you disable
ONLY_FULL_GROUP_BY, MySQL lets the
SELECT substatement reference the nonaggregated fields that are not stated in the
GROUP BY substatements. This makes a non-full
GROUP BY syntax. In other databases, this is deemed a syntax error that may cause unstable result sets.
In the following three SQL statements, the first one uses the full
GROUP BY syntax, with all the fields referenced in the
SELECT substatement stated in the
GROUP BY substaments. It has the most stable result sets with three combinations of
The second and third SQL statements are identical, but they yield different results. The statement only states a
class field in the
GROUP BY statement, so the result sets are only aggregated for
class. Since there are two unique values for
class, the result sets only contains two rows of data, while there are three combinations of
stuname fields. Class 2018_CS_03 has two students, and there is no semantic restriction as to which one is returned for each execution. Either one is semantically expected.
mysql> select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class, a.stuname order by a.class, a.stuname; +------------+--------------+------------------+ | class | stuname | max(b.courscore) | +------------+--------------+------------------+ | 2018_CS_01 | MonkeyDLuffy | 95.5 | | 2018_CS_03 | PatrickStar | 99.0 | | 2018_CS_03 | SpongeBob | 95.0 | +------------+--------------+------------------+ 3 rows in set (0.00 sec) mysql> select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname; +------------+--------------+------------------+ | class | stuname | max(b.courscore) | +------------+--------------+------------------+ | 2018_CS_01 | MonkeyDLuffy | 95.5 | | 2018_CS_03 | SpongeBob | 99.0 | +------------+--------------+------------------+ 2 rows in set (0.01 sec) mysql> select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname; +------------+--------------+------------------+ | class | stuname | max(b.courscore) | +------------+--------------+------------------+ | 2018_CS_01 | MonkeyDLuffy | 95.5 | | 2018_CS_03 | PatrickStar | 99.0 | +------------+--------------+------------------+ 2 rows in set (0.01 sec)
Therefore, to guarantee stable result sets, use the full
GROUP BY syntax.
MySQL provides a
ONLY_FULL_GROUP_BY SQL mode to control whether to check the full
GROUP BY syntax, which is also supported in TiDB.
mysql> select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname; +------------+--------------+------------------+ | class | stuname | max(b.courscore) | +------------+--------------+------------------+ | 2018_CS_01 | MonkeyDLuffy | 95.5 | | 2018_CS_03 | PatrickStar | 99.0 | +------------+--------------+------------------+ 2 rows in set (0.01 sec) mysql> set @@sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.01 sec) mysql> select a.class, a.stuname, max(b.courscore) from stu_info a join stu_score b on a.stuno=b.stuno group by a.class order by a.class, a.stuname; ERROR 1055 (42000): Expression #2 of ORDER BY is not in GROUP BY clause and contains nonaggregated column '' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
ORDER BYto guarantee the output sequence of result sets
According to SQL semantics, you must use the
ORDER BY syntax to output result sets in a specified sequence. For standalone databases, because the data are all stored on one server, results are stably returned when data is not reorganized. Some databases can still output result sets in the order of primary keys or indexes. TiDB is a distributed database where data is stored in multiple servers, and the TiDB server layer does not cache data. Therefore, the display order of the result sets for SQL statements without
ORDER BY may be unpredictable. To output the result sets in sequence, explicitly add the fields for sorting in the
ORDER BY clause, as defined by SQL semantics.
In the following example, the user only adds one field in the
ORDER BY clause. Therefore, TiDB sorts the results by this field only.
mysql> select a.class, a.stuname, b.course, b.courscore from stu_info a join stu_score b on a.stuno=b.stuno order by a.class; +------------+--------------+-------------------------+-----------+ | class | stuname | course | courscore | +------------+--------------+-------------------------+-----------+ | 2018_CS_01 | MonkeyDLuffy | PrinciplesofDatabase | 60.5 | | 2018_CS_01 | MonkeyDLuffy | English | 43.0 | | 2018_CS_01 | MonkeyDLuffy | OpSwimming | 67.0 | | 2018_CS_01 | MonkeyDLuffy | OpFencing | 76.0 | | 2018_CS_01 | MonkeyDLuffy | FundamentalsofCompiling | 88.0 | | 2018_CS_01 | MonkeyDLuffy | OperatingSystem | 90.5 | | 2018_CS_01 | MonkeyDLuffy | PrincipleofStatistics | 69.0 | | 2018_CS_01 | MonkeyDLuffy | ProbabilityTheory | 76.0 | | 2018_CS_01 | MonkeyDLuffy | Physics | 63.5 | | 2018_CS_01 | MonkeyDLuffy | AdvancedMathematics | 95.5 | | 2018_CS_01 | MonkeyDLuffy | LinearAlgebra | 92.5 | | 2018_CS_01 | MonkeyDLuffy | DiscreteMathematics | 89.0 | | 2018_CS_03 | SpongeBob | PrinciplesofDatabase | 88.0 | | 2018_CS_03 | SpongeBob | English | 79.0 | | 2018_CS_03 | SpongeBob | OpBasketball | 92.0 | | 2018_CS_03 | SpongeBob | OpTennis | 94.0 | | 2018_CS_03 | PatrickStar | LinearAlgebra | 6.5 | | 2018_CS_03 | PatrickStar | AdvancedMathematics | 5.0 | | 2018_CS_03 | SpongeBob | DiscreteMathematics | 72.0 | | 2018_CS_03 | PatrickStar | ProbabilityTheory | 12.0 | | 2018_CS_03 | PatrickStar | PrincipleofStatistics | 20.0 | | 2018_CS_03 | PatrickStar | OperatingSystem | 36.0 | | 2018_CS_03 | PatrickStar | FundamentalsofCompiling | 2.0 | | 2018_CS_03 | PatrickStar | DiscreteMathematics | 14.0 | | 2018_CS_03 | PatrickStar | PrinciplesofDatabase | 9.0 | | 2018_CS_03 | PatrickStar | English | 60.0 | | 2018_CS_03 | PatrickStar | OpTableTennis | 12.0 | | 2018_CS_03 | PatrickStar | OpPiano | 99.0 | | 2018_CS_03 | SpongeBob | FundamentalsofCompiling | 43.0 | | 2018_CS_03 | SpongeBob | OperatingSystem | 95.0 | | 2018_CS_03 | SpongeBob | PrincipleofStatistics | 90.0 | | 2018_CS_03 | SpongeBob | ProbabilityTheory | 87.0 | | 2018_CS_03 | SpongeBob | Physics | 65.0 | | 2018_CS_03 | SpongeBob | AdvancedMathematics | 55.0 | | 2018_CS_03 | SpongeBob | LinearAlgebra | 60.5 | | 2018_CS_03 | PatrickStar | Physics | 6.0 | +------------+--------------+-------------------------+-----------+ 36 rows in set (0.01 sec)