SQL Data Types, Index & Authorization

วิดีโอที่อาจารย์ให้ดูก่อนเรียนบทนี้:

10.1ทำไมชนิดข้อมูลถึงสำคัญ

• ควบคุมว่าข้อมูลแบบไหนเก็บได้ในแต่ละคอลัมน์
• ประหยัดพื้นที่จัดเก็บและหน่วยความจำ
• ให้ฐานข้อมูลตรวจสอบความถูกต้องของข้อมูลได้อัตโนมัติ
• เพิ่มประสิทธิภาพของ query ผ่าน operation ที่มีประสิทธิภาพ

ชนิดข้อมูลกับประสิทธิภาพ: ชนิดที่เล็กกว่า = query เร็วกว่า · ชนิดที่เหมาะสมทำให้ index ทำงานได้ดี · ชนิดที่ไม่ตรงกันทำให้เกิด implicit conversion · ชนิดที่ผิดทำให้ตรรกะของแอปพัง

SELECT * FROM customers WHERE customer_id = 12345;   -- ใส่เป็นตัวเลข ไม่ใช่ '12345'

ชนิดข้อมูลที่ต้องรู้

DECLARE @price FLOAT = 19.95;
SELECT @price;   -- อาจแสดง: 19.950000000000003

แบบฝึกหัดออกแบบตาราง — พร้อมเฉลย

CREATE TABLE books (
    book_id          INT AUTO_INCREMENT PRIMARY KEY,
    title            VARCHAR(255) NOT NULL,
    author           VARCHAR(100) NOT NULL,
    isbn             CHAR(13) UNIQUE,
    publication_date DATE,
    price            DECIMAL(6,2),
    page_count       SMALLINT UNSIGNED,
    description      TEXT
);

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50)  NOT NULL,
    last_name   VARCHAR(50)  NOT NULL,
    email       VARCHAR(100) UNIQUE,
    phone       VARCHAR(20),
    hire_date   DATE NOT NULL,
    salary      DECIMAL(10,2),
    department  VARCHAR(50),
    job_title   VARCHAR(100)
);

10.2Index

Index คือโครงสร้างข้อมูลในฐานข้อมูลที่เพิ่มความเร็วในการดึงข้อมูล เปรียบเหมือนดัชนีท้ายเล่มหนังสือที่ช่วยให้หาหน้าได้เร็ว · สร้างบนคอลัมน์หนึ่งคอลัมน์หรือหลายคอลัมน์ และถูกเก็บแยกจากตัวข้อมูลในตาราง

Index ทำงานอย่างไร

• เก็บรายการค่าที่เรียงลำดับแล้วพร้อมตำแหน่งของแถวที่ตรงกัน
• ทำให้ฐานข้อมูลหาข้อมูลได้โดยไม่ต้องสแกนทั้งตาราง
• แลกกันระหว่างการอ่านที่เร็วขึ้น กับการเขียนที่ช้าลง
• ฐานข้อมูลจะใช้ index ให้เองอัตโนมัติเมื่อมันช่วยให้ query เร็วขึ้น

SELECT * FROM customers
WHERE email = 'john@example.com';

CREATE INDEX idx_email ON customers(email);
SELECT * FROM customers
WHERE email = 'john@example.com';

B-tree (Balanced tree) คือโครงสร้างข้อมูลที่ปรับสมดุลตัวเอง เก็บข้อมูลเรียงลำดับไว้เสมอ และคงเวลาการค้นหาแบบลอการิทึม · คิดง่าย ๆ ว่าเป็นระบบแฟ้มเอกสารที่จัดระเบียบตัวเองอัตโนมัติเพื่อให้ค้นได้เร็วตลอด

คำสั่งสร้าง index และควรสร้างเมื่อไหร่

CREATE INDEX <name> ON <relation-name> (attribute);

CREATE INDEX studentID_index ON student(ID);
CREATE INDEX idx_email       ON customers(email);
CREATE INDEX idx_city        ON customers(city);
CREATE INDEX idx_order_date  ON orders(order_date);
CREATE INDEX idx_customer_id ON orders(customer_id);

เหตุผลเชิงทฤษฎี: query จำนวนมากอ้างถึงเรคอร์ดเพียงส่วนน้อยของตาราง การให้ระบบอ่านทุกเรคอร์ดเพื่อหาเรคอร์ดที่มีค่าเฉพาะเจาะจงจึงไม่มีประสิทธิภาพ · index บน attribute ของ relation คือโครงสร้างข้อมูลที่ช่วยให้ระบบหา tuple ที่มีค่าตามที่ระบุได้อย่างมีประสิทธิภาพโดยไม่ต้องไล่ดูทุก tuple

ตัวอย่างสถานการณ์จากสไลด์

SELECT * FROM customers WHERE email = 'user@example.com';
SELECT city, COUNT(*) FROM customers GROUP BY city;
SELECT * FROM customers WHERE city='New York' AND state='NY';

CREATE INDEX idx_order_date  ON orders(order_date);
CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_order_id    ON order_items(order_id);

10.3Authorization

Authorization คือกลไกควบคุมการเข้าถึงและการดำเนินการกับฐานข้อมูล กำหนดว่าผู้ใช้หรือ role ใดทำอะไรกับ object ในฐานข้อมูลได้บ้าง · รับประกันความปลอดภัยและ integrity ของข้อมูล และนำหลัก least privilege มาใช้

คำสั่ง GRANT และ REVOKE

GRANT  <privilege list> ON <relation or view> TO   <user list>;
REVOKE <privilege list> ON <relation or view> FROM <user list>;

-- ให้สิทธิ์อ่านทีมการตลาด
GRANT SELECT ON customers TO marketing_team;

-- ให้สิทธิ์เต็มกับทีม HR บนตาราง employees
GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO hr_team;

-- จำกัดเฉพาะบางคอลัมน์
GRANT UPDATE (email, phone) ON employees TO intern_role;

-- ถอนสิทธิ์ทั้งหมดของพนักงานที่ลาออก (MySQL: ทั้งฐานข้อมูลเขียนเป็น db.*)
REVOKE ALL PRIVILEGES ON company_db.* FROM john_doe;
-- (PostgreSQL เขียนว่า REVOKE ALL PRIVILEGES ON DATABASE company_db FROM john_doe;)

GRANT SELECT ON instructor TO U1, U2, U3;
REVOKE SELECT ON student FROM U1, U2, U3;

ตารางที่ตัวอย่างสองบรรทัดท้ายอ้างถึง คือฐานข้อมูลมหาวิทยาลัย — instructor(ID, name, dept_name, salary) · student(ID, name, dept_name, tot_cred) · takes(ID, course_id, sec_id, semester, year, grade) (โครงสร้างเต็มอยู่ในบทที่ 5)

• <user list> เป็นได้ทั้ง user-id · public (ให้สิทธิ์ผู้ใช้ที่ถูกต้องทุกคน) · หรือ role
• ผู้ที่จะ grant ต้องถือสิทธิ์นั้นอยู่แล้ว (หรือเป็น DBA)
• <privilege-list> ใช้ all เพื่อถอนสิทธิ์ทั้งหมดที่ผู้ถูกถอนมีอยู่
• ถ้ารายชื่อผู้ถูกถอนมี public ผู้ใช้ทุกคนจะเสียสิทธิ์ยกเว้นคนที่ได้รับสิทธิ์แบบระบุตัวโดยตรง
• WITH GRANT OPTION — เขียนต่อท้าย GRANT … TO u WITH GRANT OPTION แปลว่า ให้ u ส่งต่อสิทธิ์นี้ให้คนอื่นได้อีกทอด → เกิดเป็น “โซ่การให้สิทธิ์”
  เช่น DBA → คณบดี → หัวหน้าภาค → อาจารย์ · แต่ละขั้นต่อสิทธิ์กันไปเรื่อย ๆ
• ถ้าสิทธิ์เดียวกันถูก grant ให้ผู้ใช้คนเดิมสองครั้งโดยผู้ให้ต่างคนกัน ผู้ใช้อาจยังคงสิทธิ์ไว้หลังการ revoke ครั้งหนึ่ง
  เพราะยังมีอีกโซ่หนึ่งค้ำอยู่ — ถอนโซ่เดียวไม่พอ ต้องถอนทุกโซ่ที่ให้สิทธิ์นั้น
• สิทธิ์ทั้งหมดที่ขึ้นอยู่กับสิทธิ์ที่ถูกถอน ก็จะถูกถอนไปด้วย (cascading revoke)
  พอถอนต้นโซ่ สิทธิ์ที่แตกออกไปจากมันทั้งหมดถูกถอนตามอัตโนมัติ — ถอนสิทธิ์คณบดี แล้วหัวหน้าภาคกับอาจารย์ที่ได้ต่อมาจากเขาก็เสียสิทธิ์ด้วย

Roles

Role คือวิธีแยกแยะผู้ใช้ตามสิ่งที่พวกเขาเข้าถึงหรืออัปเดตได้ในฐานข้อมูล

CREATE ROLE instructor;
GRANT instructor TO Amit;                 -- มอบ role ให้ผู้ใช้
GRANT SELECT ON takes TO instructor;      -- มอบสิทธิ์ให้ role

-- role มอบให้ role อื่นได้ → เกิดการสืบทอดสิทธิ์
CREATE ROLE teaching_assistant;
GRANT teaching_assistant TO instructor;   -- instructor ได้สิทธิ์ทุกอย่างของ TA

-- ลูกโซ่ของ role
CREATE ROLE dean;
GRANT instructor TO dean;
GRANT dean TO Satoshi;

Authorization บน View

CREATE VIEW geo_instructor AS
    (SELECT * FROM instructor WHERE dept_name = 'Geology');

GRANT SELECT ON geo_instructor TO geo_staff;

-- แล้วถ้า geo_staff สั่ง...
SELECT * FROM geo_instructor;

10.4สรุปบทนี้

• ชนิดข้อมูลสำคัญต่อ integrity และการใช้พื้นที่ — เลือกตามลักษณะข้อมูลและความต้องการในอนาคต
• Index เพิ่มประสิทธิภาพ query อย่างมีนัยสำคัญ — สร้างบนคอลัมน์ที่ query บ่อยและคอลัมน์ที่ใช้ join
• ต้องชั่งน้ำหนักระหว่างความเร็วในการอ่าน กับภาระในการเขียน
• Authorization จำเป็นต่อความปลอดภัย — ใช้ GRANT และ REVOKE ควบคุมการเข้าถึง และใช้หลัก least privilege
• การใช้ทั้งสามอย่างให้ถูกต้องนำไปสู่: ประสิทธิภาพที่ดีขึ้น · integrity ที่แข็งแรงขึ้น · ความปลอดภัยและการควบคุมการเข้าถึงที่ดีขึ้น