The source code for this blog is available on GitHub.
Note
Top

Every core concept I need to know as a developer - Computer science

Cover Image for Every core concept I need to know as a developer - Computer science
Han Chen
Han Chen

SQL

請舉出三種在硬體資源足夠的資料庫伺服器環境中,針對關聯式資料庫 (Relational Database)查詢時,發生效能緩慢問題的調校方式。

關聯式資料庫的效能緩慢可能由多種因素引起,以下是三種調校方式:

  1. 索引優化:索引是提高SQL查詢效能的重要方式。若資料庫查詢效能不佳,可以審視現有的索引是否合適,或是否需要添加新的索引。在频繁查询的列上添加索引可以大大提高查询速度。但同時要注意,过多的索引可能会在插入,更新和删除记录时降低性能,因为索引需要在这些操作中进行维护。因此,需要找到正确的平衡。

  2. 查詢優化:確保SQL查詢語句的效能最佳化也是提升資料庫效能的重要方式。這可能涉及重新編寫某些查詢以利用索引,或使用EXPLAIN等工具來分析查詢的執行計劃。透過理解查詢如何被資料庫執行,您可以找到可能的效能瓶頸並進行改善。

  3. 硬體優化:雖然問題指出硬體資源足夠,但是,您仍然需要確保這些資源被正確地使用。這可能涉及調整資料庫的配置設定,例如增加緩存大小(如果內存允許),或調整其他相關的性能參數。對於大型資料庫,可能需要考慮使用更快的硬碟,例如SSD,或者進行分區以改善效能。

除了以上的方法,還需要根據特定的工作負載和使用模式來進行優化,並定期進行性能監控和調整以確保資料庫的效能。

Do there any ways to adjust rather than 索引優化, 查詢優化 and 硬體優化

Yes, there are several other strategies that can be applied to improve the performance of a relational database, including:

  1. Normalization and Denormalization: Database normalization can minimize data redundancy and improve data integrity, but at the expense of query performance because of increased number of joins. Denormalization, on the other hand, can improve read performance by reducing the number of joins in a query.

  2. Database Partitioning: This involves splitting a large table into smaller, more manageable pieces called partitions. This can help improve performance in cases where you're often querying a subset of data.

  3. Connection Pooling: Opening and closing database connections can be costly in terms of resources and time. Connection pooling allows you to reuse existing connections, thereby saving the overhead of establishing a new connection each time a user wants to read/write to the database.

  4. Caching: Frequently accessed data can be stored in memory (cache) for faster retrieval. While this doesn't directly speed up the database, it can significantly reduce the load on it and improve overall application performance.

  5. Concurrency and Transaction Management: Efficient handling of transactions and concurrency can significantly improve the performance of a relational database. This includes using appropriate isolation levels, efficient locking mechanisms, and optimizing long-running queries that could lock resources for extended periods.

  6. Use of Stored Procedures: Stored procedures are SQL statements that are stored in the database and can be called by the application. Since stored procedures are compiled and optimized by the database, they can be more efficient than SQL queries from the application.

Remember, though, every database and application is unique, so these strategies should be considered general guidance rather than hard-and-fast rules. Always test changes in a development or staging environment before deploying them to production.

Nosql

NoSQL (often interpreted as 'Not only SQL') is a type of database that is designed to handle data management requirements different from the traditional relational databases. NoSQL databases are particularly useful for working with large sets of distributed data.

Here are the four main types of NoSQL databases:

  1. Document-oriented databases: These store data in a document-like format (usually JSON). Each document is self-describing, and the schema can be different for each document. Examples include MongoDB and CouchDB.
  2. Key-Value stores: These are the simplest NoSQL databases. Every single item in the database is stored as an attribute name (or 'key'), together with its value. Examples of key-value stores include Redis, Voldemort, and DynamoDB.
  3. Wide-column stores: These are a type of NoSQL database that uses tables, rows, and columns, but names and format of the columns can vary from row to row in the same table. They are designed to store columns of data instead of rows and are highly scalable. Examples: Cassandra, HBase.
  4. Graph databases: These are designed to handle data whose relations are best represented as a graph and have elements which are interconnected, with an undetermined number of relations between them. Examples include Neo4J and InfiniteGraph.

Each type has its own specific use cases, and the type chosen typically depends on the data structure and the specific use case, rather than being a "one size fits all" solution.

Memory

Question 001:

於資料儲存階層設有“快取記憶體(cache memory)”與“主記憶體(main memory)” 模組,假設前者之資料存取時間為 12 ms,而後者為 60 ms,請問於快取命中率 為 70%時,總體平均之資料存取時間為何?

Answer 001:

資料存取時間可以用命中率(Hit rate)與未命中率(Miss rate)以及各自對應的存取時間來計算。

在這個例子中,快取命中率為 70%,這意味著有 70% 的時間資料可以在快取記憶體中找到,其存取時間為 12ms。另有 30%(即 100% - 70%)的時間,資料不在快取中,需要從主記憶體取得,其存取時間為 60ms。

因此,我們可以使用以下的公式來計算總體平均的資料存取時間:

平均存取時間 = 命中時間 * 命中率 + 未命中時間 * 未命中率

代入數值得到:

平均存取時間 = 12ms * 0.7 + 60ms * 0.3

經過計算後,得到的總體平均之資料存取時間大約為 26.4 ms。

Network

WIFI

不同Wi-Fi等級傳輸的速度不同,以最新的Wi-Fi 6為例,除了提供更快的傳輸速度之外,還能增加訊號穩定度、減少訊號干擾、一次連線多個存取點,讓你享受不延遲的8K影片,快速下載與上傳大檔案,大幅提升網路使用體驗。以下提供無線網路標準的比較表作為參考:

Wi-Fi等級 無線網路標準 頻段 最高傳輸速率 傳輸技術 最大頻段
第一代 IEEE802.11 2.4GHz 2M bps DSSS / CCK 20MHz
第二代 IEEE802.11b 2.4GHz 11M bps DSSS 20MHz
IEEE802.11a 5GHz 54M bps OFDM 20MHz
第三代 IEEE802.11g 2.4GHz 54M bps DSSS / OFDM 20MHz
Wi-Fi 4 IEEE802.11n 2.4GHz / 5GHz 600M bps OFDM 40MHz
Wi-Fi 5 IEEE802.11ac wave1 5GHz 3466M bps OFDM 80MHz
IEEE802.11ac wave2 6933M bps OFDM 160MHz
Wi-Fi 6 IEEE802.11ax 2.4GHz / 5GHz 9.6G bps OFDMA 160MHz
Li-Fi 802.11bb

What is Li-Fi: Link

Algorithm

Recursive Problem - GCD and LCM

Let's break this down:

  1. GCD (Greatest Common Divisor)

    We'll use the Euclidean algorithm to compute the GCD, as it's a classic example of recursion. Given two integers a and b, where a > b, subtract b from a. The difference replaces a. The process is repeated until a equals b. That common value then becomes the GCD of the original a and b.

    A recursive approach to the Euclidean algorithm involves using the modulo % operation:

    function gcd(a, b) {
        if (b === 0) {
            return a;
        }
        return gcd(b, a % b);
    }
    
    console.log(gcd(48, 18)); // 6
    
  2. LCM (Least Common Multiple)

    The least common multiple of two integers a and b can be computed using the formula:

    lcm(a, b) = |a * b| / gcd(a, b)

    Since we've already got a recursive function to find the GCD, finding the LCM becomes straightforward:

    function lcm(a, b) {
        return Math.abs(a * b) / gcd(a, b);
    }
    
    console.log(lcm(15, 10)); // 30
    

Given the recursive nature of the GCD function, the LCM function also indirectly becomes recursive.

Recursive Problem - Some simple examples

Recursion can sometimes be applied in scenarios where it's not the most intuitive approach. Here are a few more examples of functions that can be implemented recursively:

  1. Factorial: Factorial of a non-negative integer n is the product of all positive integers less than or equal to n.

    function factorial(n) {
        if (n <= 1) return 1;
        return n * factorial(n - 1);
    }
    
    console.log(factorial(5));  // Outputs: 120
    
  2. Towers of Hanoi: A classic problem involving moving discs between three pegs.

    function towersOfHanoi(n, source, auxiliary, target) {
        if (n === 1) {
            console.log(`Move disc 1 from ${source} to ${target}`);
            return;
        }
        towersOfHanoi(n - 1, source, target, auxiliary);
        console.log(`Move disc ${n} from ${source} to ${target}`);
        towersOfHanoi(n - 1, auxiliary, source, target);
    }
    
    towersOfHanoi(3, 'A', 'B', 'C');
    
  3. Sum of Array Elements: Find the sum of all elements in an array.

    function sumArray(arr, n) {
        if (n <= 0) return 0;
        return sumArray(arr, n - 1) + arr[n - 1];
    }
    
    console.log(sumArray([1, 2, 3, 4, 5], 5));  // Outputs: 15
    
  4. Binary Search: Recursively find a target value within a sorted array.

    function binarySearch(arr, x, low = 0, high = arr.length - 1) {
        if (low > high) return -1;
    
        let mid = Math.floor((low + high) / 2);
    
        if (arr[mid] === x) return mid;
    
        if (arr[mid] < x) {
            return binarySearch(arr, x, mid + 1, high);
        }
    
        return binarySearch(arr, x, low, mid - 1);
    }
    
    console.log(binarySearch([1, 2, 3, 4, 5], 4));  // Outputs: 3
    
  5. Reverse a String: Recursively reverse a given string.

    function reverseString(str) {
        if (str === "") return str;
        return reverseString(str.substr(1)) + str[0];
    }
    
    console.log(reverseString("hello"));  // Outputs: "olleh"
    
  6. Flattening Arrays

    Flattening arrays might not seem like a natural fit for recursion, but it works quite well when dealing with arrays that might contain other arrays.

    function flatten(arr) {
        if (arr.length === 0) return [];
        if (Array.isArray(arr[0])) {
            return flatten(arr[0]).concat(flatten(arr.slice(1)));
        } else {
            return [arr[0]].concat(flatten(arr.slice(1)));
        }
    }
    
    console.log(flatten([1, [2, 3], 4, [5, [6, 7]]])); // [1, 2, 3, 4, 5, 6, 7]
    
  7. Counting ways to climb stairs

    Imagine you're climbing a staircase and at each step you can either take a single step or jump two steps. The problem of counting the number of unique ways to reach the top can be tackled recursively, even though it may not be the first solution that comes to mind.

    function countWays(n) {
        if (n <= 1) return 1;
        return countWays(n - 1) + countWays(n - 2);
    }
    
    console.log(countWays(5));  // 8
    
  8. Towers of Hanoi

    The Towers of Hanoi is a classic recursive problem, but for those unfamiliar with it, the recursive solution can seem non-intuitive.

    function hanoi(n, source, target, auxiliary) {
        if (n > 0) {
            hanoi(n - 1, source, auxiliary, target);
            console.log(`Move disk ${n} from ${source} to ${target}`);
            hanoi(n - 1, auxiliary, target, source);
        }
    }
    
    hanoi(3, 'A', 'C', 'B');
    
  9. String Permutations

    Generating all permutations of a string can be tackled recursively. For every character in the string, we can swap it with the first character and recursively generate permutations for the remainder of the string.

    生成字串的所有排列都可以遞迴處理。 對於字串中的每個字元,我們可以將其與第一個字元交換,併為字串的其餘部分遞迴生成排列。

    This function, permute, is designed to generate all possible permutations of a given string str.

    Let's break down the function step by step:

    1. Base Parameters:

      • str: The string of which we want to find the permutations.
      • prefix: An optional parameter that helps the function keep track of the characters chosen so far. Initially, it's an empty string.
    2. Base Case:

      • If the length of str is 0, this means we've formed a permutation stored in the prefix and we can print or push it to our result.
    3. Recursive Case:

      • For every character in the string str, the function does the following:
        • Takes the character out from str to form a new string rem. This is achieved by concatenating the slice of the string before the character and the slice of the string after the character.
        • Calls itself (recursively) with the new string rem and updates the prefix with the character that's taken out.

    To better understand, consider the string "ABC":

    • For i = 0: the function chooses "A", and rem becomes "BC". The function is then called with "BC" and "A" as the prefix.

      • Within this call, the function chooses "B" from "BC", and rem becomes "C". The function is then called with "C" and "AB" as the prefix.
        • This ultimately leads to the permutation "ABC".
    • For i = 1: the function chooses "B", and rem becomes "AC". The process continues like the previous step, leading to the permutation "BAC".

    • For i = 2: the function chooses "C", and rem becomes "AB". This leads to the permutation "CAB".

    The function repeats these steps for every character in the string until all permutations are found.

    In the code you provided, instead of collecting the results in the result array, the permutations are being printed out directly using console.log(prefix). If you uncomment the lines related to result, you can collect the permutations in an array and return them.

    let r = [];
    function permute(str, prefix = "") {
      if (str.length === 0) {
        r.push(prefix)
        console.log(prefix)
      } else {
        for (let i = 0; i < str.length; i++) {
          let rem = str.slice(0, i) + str.slice(i + 1);
          permute(rem, prefix + str[i]);
        }
      }
    }
    
    permute("abc");
    console.log(r) // [ 'abc', 'acb', 'bac', 'bca', 'cab', 'cba' ]
    

All these examples showcase that even if recursion may not seem like the most obvious solution, it can provide elegant solutions to problems. However, it's essential to note that recursion can have its downsides, such as possible stack overflows and sometimes inefficiencies, especially when not combined with techniques like memoization.

These are just a few of the countless problems that can be solved using recursion. Some are more intuitive than others, but recursion provides a powerful tool for simplifying complex problems.

Recursive Problem - Towers of Hanoi

The "Towers of Hanoi" (often simply referred to as "Hanoi") is a classic mathematical game or puzzle. Here's a more detailed breakdown:

You have three rods and n discs of different sizes. The discs are initially stacked on one rod in order of size, with the largest disc at the bottom and the smallest at the top. The objective of the puzzle is to move the entire stack of discs to another rod, obeying the following rules:

  1. Only one disc can be moved at a time.
  2. A disc can only be placed on top of a larger disc or an empty rod.
  3. A disc can be moved from the top of a stack on one rod to the top of a stack on another rod (or to an empty rod).

The recursive approach to solving the Towers of Hanoi problem can be broken down as follows:

  1. Move the top n-1 discs from the source rod to an auxiliary rod, using the target rod as a temporary holding place.
  2. Move the nth (largest) disc from the source rod to the target rod.
  3. Move the n-1 discs from the auxiliary rod to the target rod, using the source rod as a temporary holding place.

Here's the algorithm for the above:

function towersOfHanoi(n, source, auxiliary, target) {
    // Base case: if only one disc, move it from source to target
    if (n === 1) {
        console.log(`Move disc 1 from ${source} to ${target}`);
        return;
    }
    // Recursively move the top n-1 discs from source to auxiliary
    towersOfHanoi(n - 1, source, target, auxiliary);
    // Move the nth disc from source to target
    console.log(`Move disc ${n} from ${source} to ${target}`);
    // Recursively move the n-1 discs from auxiliary to target
    towersOfHanoi(n - 1, auxiliary, source, target);
}

The Towers of Hanoi puzzle has been widely studied in mathematics, particularly in algorithms and recursion. It's a popular problem because:

  • It's a clear example of how a seemingly complex problem can be broken down into simpler sub-problems using recursion.
  • It showcases the power and elegance of recursive solutions.
  • The puzzle has a deep mathematical significance, with relations to binary numbers and Sierpinski triangles.

There's a legend associated with the Towers of Hanoi puzzle. The legend says that in an ancient temple, monks are attempting to solve a 64-disc version of this puzzle. According to the legend, once they complete the puzzle, the world will end. Fortunately for us, even at a rate of one move per second, it would take more than 500 billion years to solve the 64-disc version!

Recursive Problem - fibonacci

Here's a simple Fibonacci sequence generator using TypeScript:

function fibonacci(n: number): number {
    if (n <= 1) return n;
  
    return fibonacci(n - 1) + fibonacci(n - 2);
}

console.log(fibonacci(10));  // Outputs: 55

This is a basic recursive implementation. However, this isn't the most efficient way to compute the Fibonacci sequence, especially for larger numbers. A more efficient approach would be to use iteration or memoization.

Here's a version using iteration:

function fibonacciIterative(n: number): number {
    if (n <= 1) return n;
    
    let previous = 0;
    let current = 1;
    
    for (let i = 2; i <= n; i++) {
        const next = previous + current;
        previous = current;
        current = next;
    }
    
    return current;
}

console.log(fibonacciIterative(10));  // Outputs: 55

And here's a version using memoization:

function fibonacciMemoized(n: number, memo: Map<number, number> = new Map()): number {
    if (n <= 1) return n;
    
    if (memo.has(n)) return memo.get(n)!;

    const value = fibonacciMemoized(n - 1, memo) + fibonacciMemoized(n - 2, memo);
    memo.set(n, value);
    
    return value;
}

console.log(fibonacciMemoized(10));  // Outputs: 55

With TypeScript, we can use its type-checking capabilities to ensure the input to the function is a number, and that the function always returns a number.

© 2024 WOOTHINK. All Rights Reserved.
Site MapTerms and ConditionsPrivacy PolicyCookie Policy