9 posts tagged with "SQL review"

OceanBase database has been widely adopted in finance, e-commerce, and government sectors due to its high availability, strong consistency, and high performance. As business scale expands and data volume surges, optimizing OceanBase database query performance becomes increasingly important. PawSQL provides comprehensive performance optimization support for OceanBase database, helping users fully unleash OceanBase's performance potential.

I. Core Optimization Technologies​

PawSQL introduces two specialized capabilities for OceanBase database:

1. Deep OceanBase SQL Syntax Support

• Dual-mode compatibility: Full support for MySQL/Oracle syntax systems
• Complete parsing of OceanBase-specific DDL syntax:

  CREATE TABLE nation_d (
      n_nationkey INTEGER NOT NULL PRIMARY KEY,
      n_name CHAR(25) NOT NULL,
      n_regionkey INTEGER NOT NULL,
      n_comment VARCHAR(152)
  ) duplicate_scope = cluster/none;
  
  CREATE TABLE part (
      p_partkey int NOT NULL PRIMARY KEY,
      p_name VARCHAR(55) NOT NULL,
      p_mfgr CHAR(25) NOT NULL,
      p_brand CHAR(10) NOT NULL,
      p_type VARCHAR(25) NOT NULL,
      p_size NUMBER NOT NULL,
      p_container CHAR(10) NOT NULL,
      p_retailprice NUMBER(15,2) NOT NULL,
      p_comment VARCHAR(23)
  ) PARTITION BY HASH(p_partkey) PARTITIONS 16;
  

2. Rule System Upgrade

• Three new distributed design standards:
  • Avoid table join fields that are not distribution keys
  • DML in distributed databases should avoid table joins
  • DML operations in distributed databases lack sharding field equality conditions
• Three new distribution key design standards:
  • Local tables are not recommended in distributed databases
  • Multiple fields are not recommended for distribution keys
  • Distribution keys should use highly distinctive fields
• Two new distribution strategy design standards:
  • Replicated distribution is not recommended for large tables
  • Hash distribution is recommended for distribution method

II. Product Matrix of SQL Lifecycle​

2.1 Development and Testing Phase: Intelligent SQL Optimization​

PawSQL Optimization Platform is a one-stop online SQL optimization tool for application developers and testers. It integrates industry best practices for relational database query optimization, helping application developers and database administrators solve SQL performance issues through query rewriting optimization and intelligent index recommendations. PawSQL Optimization Platform has completed integration with common IDEs, allowing developers to perform SQL optimization without leaving their development environment.

The PawSQL Optimization Platform is an online SQL optimization tool for developers and DBAs, incorporating industry-leading query optimization technologies, including:

• Intelligent Query Rewriting: Automatically optimizes inefficient SQL statements
• Index Recommendation Engine: Precisely recommends optimal index combinations
• Distributed Optimization Strategies: Provides specialized optimization suggestions for OceanBase's distributed characteristics

2.2 Integration Phase: Intelligent SQL Review​

PawSQL Review Platform, with its leading core technologies such as self-developed SQL parser, syntax tree-based rule matching, and context information updates, provides comprehensive and precise (accuracy over 95%) intelligent SQL review capabilities for SQL quality management teams. It conducts comprehensive checks from multiple dimensions including syntax specifications, performance efficiency, and security, providing targeted optimization suggestions to help enterprises improve SQL performance and application efficiency.

For OceanBase database's distributed characteristics, PawSQL provides specialized distributed query optimization suggestions, with applicable rules exceeding 190.

2.3 Operating Phase: Performance Patroller​

PawSQL Database Performance Patroller automatically captures slow queries generated in the database periodically and provides SQL optimization suggestions, including automatic SQL rewriting, intelligent index recommendations, and existing redundant index analysis. It automatically inspects database objects periodically, identifying potential performance, security, and maintainability issues, and provides optimization suggestions.

III. Summary​

PawSQL for OceanBase database provides you with a one-stop performance optimization solution. From daily query optimization to complex distributed scenario handling, PawSQL can help you tackle challenges with ease. 🚀 Experience PawSQL now and unlock OceanBase database's performance potential!

Problem​

Functionrand(MySQL) or random(PostgreSQL) returns a random floating-point value in the range 0 to 1.0. It is useful to get a random sample of dataset using following query.

select * from orders order by rand() limit 1; 

This works fine if customer table is less than 10,000 rows. But when you have 1,000,000 rows, the overhead for sorting the rows becomes unacceptable. The reason is quiet clear: we sort all rows, but only keep one.

Copyright © 2023 PawSQL

Problem Definition​

Using the LIMIT clause in UPDATE/DELETE operations can lead to master-slave data inconsistency or interruption of slave synchronization.

PawSQL Auditing​

PawSQL provides a corresponding SQL Audit warning for this case.

We often see articles warning that using SELECT * is a bad habit and columns should be explicitly specified in queries, such as in the latest Alibaba Java Coding Guidelines (Taishan Edition) where it is a mandatory rule. However, people only have a vague understanding of the reasons behind avoiding SELECT *. This article explains 9 reasons to avoid using SELECT * from two perspectives, and also discusses two reasonable exceptional cases to use SELECT *.

Copyright © 2023 PawSQL

Problem Definition​

STRAIGHT_JOIN is a table join method in MySQL that forces joining tables in the order defined, equivalent to an inner join in result. It gives developers some control over how the database executes the SQL query. However, it also loses out on join order optimizations by the optimizer, so should be used carefully based on the scenario. PawSQL provides a risk warning for statements using STRAIGHT_JOIN to avoid potential performance issues caused by it.

Taking the lineitem and orders tables in the tpch database as an example, the following query will join the lineitem table first and orders table second directly, indicating the database should take lineitem as the driving table and orders as the driven table for the join operation, without optimizing the table order.

SELECT *
FROM lineitem 
STRAIGHT_JOIN orders ON lineitem.l_orderkey = orders.o_orderkey;