Thực đơn
trang chủHướng dẫn

Sử dụng mysql. Sử dụng tối ưu MySQL. Chọn cơ sở dữ liệu

Sử dụng tối ưu MySQL

Giới thiệu

Để bắt đầu làm việc với MySQL, bạn cần tải xuống cơ sở dữ liệu này, cài đặt, định cấu hình và nghiên cứu tài liệu.

Trong quá trình cung cấp dịch vụ hosting, chúng tôi chú ý đến những lỗi phổ biến nhất mà người dùng mắc phải khi phát triển máy chủ ảo của mình. Một trong những công việc “khó khăn” đối với quản trị viên web thông thường là làm việc với máy chủ MySQL. Thông thường, việc nghiên cứu các nguyên tắc hoạt động của SQL và phương pháp làm việc với cơ sở dữ liệu được thực hiện từ tài liệu, từ đó chỉ những thứ có liên quan tại thời điểm đọc mới được chọn - cách kết nối với cơ sở dữ liệu, cách đưa ra yêu cầu, cách để cập nhật thông tin hoặc thêm lối đi mới vào cơ sở dữ liệu và vân vân.

Tất nhiên, cách tiếp cận này mang lại kết quả mong muốn - giao diện trang web của người dùng cuối cùng sẽ được tích hợp với cơ sở dữ liệu. Tuy nhiên, người dùng không phải lúc nào cũng nghĩ về cách cơ sở dữ liệu của họ hoạt động tối ưu, các quy trình xảy ra khi làm việc với MySQL có thể được tối ưu hóa như thế nào và chức năng sẽ như thế nào. máy chủ ảo với lượng tải tăng lên, "dòng" người dùng sẽ dẫn đến "quảng cáo" trang web.

Bài viết này sẽ giúp bạn tối ưu hóa công việc của mình với MySQL DBMS. Tài liệu được trình bày không giả vờ là một mô tả chi tiết về tối ưu hóa MySQL nói chung mà chỉ thu hút sự chú ý đến những lỗi phổ biến nhất mà người dùng mắc phải và nói về cách tránh chúng. Bạn có thể tìm hiểu thêm về sự phức tạp của việc thiết lập MySQL trên các trang chuyên biệt, các liên kết đến các trang này được cung cấp ở cuối bài viết này.

Dữ liệu nào cần được lưu trữ trong MySQL

Đừng cố gắng đưa tất cả thông tin bạn có vào cơ sở dữ liệu. Ví dụ: không cần lưu trữ ảnh ở đó, mặc dù MySQL cho phép điều này. Bằng cách đặt hình ảnh nhị phân của tệp đồ họa vào cơ sở dữ liệu, bạn sẽ chỉ làm chậm máy chủ của mình. Việc đọc một tệp có hình ảnh từ đĩa đơn giản hơn nhiều và, từ quan điểm tiêu thụ tài nguyên, tiết kiệm hơn so với việc kết nối từ tập lệnh với SQL, đưa ra yêu cầu, nhận hình ảnh, xử lý nó và đưa ra các tiêu đề http cần thiết, hiển thị nó cho khách truy cập vào máy chủ web. Trong trường hợp thứ hai, hoạt động phát hành ảnh sẽ yêu cầu tài nguyên bộ xử lý, bộ nhớ và đĩa nhiều hơn gấp nhiều lần. Cũng cần nhớ rằng có các cơ chế lưu vào bộ nhớ đệm các tài liệu web cho phép người dùng tiết kiệm lưu lượng truy cập và với việc tạo nội dung động, bạn thực sự đã tước đi cơ hội thuận tiện này của khách truy cập.

Thay vì hình ảnh, tốt hơn là lưu trữ thông tin trong MySQL, trên cơ sở đó bạn có thể tạo liên kết đến hình ảnh tĩnh trong các tài liệu được tạo động bởi các tập lệnh.

Tối ưu hóa truy vấn

Trong trường hợp bạn thực sự chỉ cần lấy một phần dữ liệu nhất định từ MySQL, bạn có thể sử dụng phím LIMIT cho hàm SELECT. Điều này hữu ích chẳng hạn khi bạn cần hiển thị kết quả tìm kiếm thứ gì đó trong cơ sở dữ liệu. Giả sử cơ sở dữ liệu chứa danh sách các sản phẩm mà cửa hàng trực tuyến của bạn cung cấp. Việc cung cấp toàn bộ danh sách sản phẩm trong danh mục mong muốn có phần vô nhân đạo đối với người dùng - các kênh liên lạc với Internet không nhanh đối với tất cả mọi người và việc cung cấp thêm hàng trăm kilobyte thông tin thường buộc người dùng phải mất hơn một phút để chờ kết quả tải trang . Trong những tình huống như vậy, thông tin được đưa ra theo từng phần, chẳng hạn như 10 vị trí. Việc chọn tất cả thông tin từ cơ sở dữ liệu và lọc đầu ra bằng một tập lệnh là sai. Sẽ tốt hơn nhiều nếu đưa ra một yêu cầu như

chọn tốt, giá sách giới hạn 20.10

Kết quả MySQL sẽ “cung cấp” cho bạn 10 bản ghi từ cơ sở dữ liệu bắt đầu từ vị trí thứ 20. Sau khi cung cấp kết quả cho người dùng, hãy tạo liên kết “10 sản phẩm tiếp theo”, chuyển dưới dạng tham số cho tập lệnh vị trí tiếp theo mà danh sách sản phẩm sẽ được hiển thị và sử dụng số này khi tạo truy vấn tới MySQL.

Bạn cũng nên nhớ rằng khi viết truy vấn cơ sở dữ liệu (truy vấn SQL), bạn chỉ nên yêu cầu những thông tin mà bạn thực sự cần. Ví dụ: nếu có 10 trường trong cơ sở dữ liệu và hiện tại bạn thực sự chỉ cần lấy hai trường trong số đó, thay vì yêu cầu

chọn * từ tên_bảng

sử dụng một công trình như

chọn trường1, trường2 từ tên_bảng

Bằng cách này, bạn sẽ không tạo gánh nặng cho MySQL với những công việc không cần thiết, hãy đảm nhận bộ nhớ bổ sung và thực hiện các hoạt động đĩa bổ sung.

Bạn cũng nên sử dụng phím WHERE nơi bạn cần lấy thông tin phù hợp với một mẫu cụ thể. Ví dụ: nếu bạn cần lấy từ các trường cơ sở dữ liệu có tên sách của tác giả Ivanov, bạn nên sử dụng cấu trúc như

chọn tiêu đề từ những cuốn sách có tác giả="Ivanov"

Ngoài ra còn có phím THÍCH cho phép bạn tìm kiếm các trường có giá trị “tương tự” với một mẫu nhất định:

chọn tiêu đề từ những cuốn sách có tác giả như "Ivanov%"

TRONG trong trường hợp này MySQL sẽ trả về tên của những cuốn sách có giá trị trường tác giả bắt đầu bằng "Ivanov"

.

Hoạt động sử dụng nhiều tài nguyên

Tuy nhiên, cần nhớ rằng có những hoạt động mà bản thân việc thực hiện chúng đòi hỏi nhiều tài nguyên hơn so với các truy vấn thông thường. Ví dụ: sử dụng thao tác DISTINCT trên hàm SELECT sẽ tốn nhiều thời gian hơn. hơn Thời gian CPU hơn bình thường SELECT. DISTINCT cố gắng tìm kiếm các giá trị duy nhất, thường thực hiện nhiều phép so sánh, thay thế và tính toán. Hơn nữa, khối lượng dữ liệu mà DISTINCT được áp dụng càng lớn (xét cho cùng, cơ sở dữ liệu của bạn sẽ phát triển theo thời gian), yêu cầu đó sẽ được thực thi càng chậm và sự tăng trưởng của các tài nguyên cần thiết để thực hiện chức năng đó sẽ không xảy ra tỷ lệ thuận với khối lượng dữ liệu được lưu trữ và xử lý nhưng nhanh hơn nhiều.

Chỉ mục

Chỉ mục được sử dụng nhiều hơn tìm kiếm nhanh theo giá trị của một trong các trường. Nếu chỉ mục không được tạo thì MySQL sẽ quét tuần tự tất cả các trường từ bản ghi đầu tiên đến bản ghi gần đây nhất, so sánh giá trị đã chọn với giá trị ban đầu. Làm sao thêm bàn và càng chứa nhiều trường thì việc lấy mẫu càng mất nhiều thời gian. Nếu một bảng nhất định có chỉ mục trên cột được đề cập thì MySQL có thể định vị nhanh đến vị trí vật lý của dữ liệu mà không cần phải thực hiện quét toàn bộ bảng. Ví dụ: nếu bảng bao gồm 1000 hàng thì tốc độ tìm kiếm sẽ nhanh hơn ít nhất 100 lần. Tốc độ này sẽ còn cao hơn nếu có nhu cầu truy cập tất cả 1000 cột cùng một lúc, bởi vì trong trường hợp này, không có thời gian dành cho việc định vị ổ cứng.

Trong trường hợp nào thì nên tạo chỉ mục:

  1. Tìm chuỗi nhanh khi sử dụng mệnh đề WHERE
  2. Tìm hàng từ các bảng khác khi thực hiện nối
  3. Tìm giá trị MIN() hoặc MAX() cho trường được lập chỉ mục
  4. Sắp xếp hoặc nhóm bảng nếu sử dụng trường được lập chỉ mục
  5. Trong một số trường hợp, nhu cầu truy cập file dữ liệu bị mất hoàn toàn. Nếu tất cả các trường được sử dụng cho một số bảng đều là số và tạo thành chỉ mục thuận tay trái trên một số khóa thì các giá trị có thể được trả về hoàn toàn từ cây chỉ mục với tốc độ nhanh hơn nhiều.
  6. Nếu truy vấn như
    CHỌN * TỪ tbl_name Ở ĐÂU col1=val1 VÀ col2=val2;
    và có chỉ mục hỗn hợp trên trường col1 và col2 thì dữ liệu sẽ được trả về trực tiếp. Nếu bạn tạo các chỉ mục riêng cho col1 và col2, trình tối ưu hóa sẽ cố gắng tìm chỉ mục bị ràng buộc nhất bằng cách xác định chỉ mục nào có thể tìm thấy ít hàng hơn và sử dụng chỉ mục đó để truy xuất dữ liệu.
    Nếu bảng có chỉ mục hỗn hợp thì mọi kết quả khớp trái với chỉ mục hiện có sẽ được sử dụng. Ví dụ: nếu có chỉ mục hỗn hợp gồm 3 trường (col1, col2, col3), thì việc tìm kiếm chỉ mục có thể được thực hiện trên các trường (col1), (col1, col2) và (col1, col2, col3).

Tìm hiểu thêm về lập chỉ mục

Hỗ trợ kết nối

Như bạn có thể biết, để làm việc với máy chủ MySQL, trước tiên bạn phải thiết lập kết nối với nó bằng cách cung cấp thông tin đăng nhập và mật khẩu của mình. Quá trình thiết lập kết nối có thể mất nhiều thời gian hơn so với việc xử lý trực tiếp yêu cầu tới cơ sở dữ liệu sau khi thiết lập kết nối. Theo logic, bạn cần tránh các kết nối không cần thiết đến cơ sở dữ liệu, không ngắt kết nối khỏi cơ sở dữ liệu ở những nơi có thể thực hiện việc này, nếu bạn dự định tiếp tục làm việc với máy chủ SQL trong tương lai. Ví dụ: nếu tập lệnh của bạn đã thiết lập kết nối tới cơ sở dữ liệu và thực hiện lựa chọn dữ liệu để phân tích, bạn không cần phải đóng kết nối với cơ sở dữ liệu nếu, trong khi tập lệnh đó đang chạy, bạn dự định đặt kết quả phân tích vào kho dữ liệu.

Cũng có thể duy trì cái gọi là kết nối liên tục với cơ sở dữ liệu, nhưng điều này hoàn toàn có thể thực hiện được khi sử dụng các môi trường lập trình phức tạp hơn php hoặc Perl ở chế độ CGI thông thường, khi trình thông dịch của ngôn ngữ tương ứng được khởi chạy một lần bởi máy chủ web để thực hiện yêu cầu đến.

Andrey Klochkov

MySQL là gì

Trước khi đưa ra kết luận, nó có đáng sử dụng không? gói MySQL Là một máy chủ cơ sở dữ liệu, trước tiên bạn cần tìm hiểu nó là gì. MySQL là một DBMS quan hệ.

MySQL hỗ trợ SQL (Ngôn ngữ truy vấn có cấu trúc) và có thể được sử dụng làm máy chủ SQL. Điều này có nghĩa là bạn có thể giao tiếp với máy chủ bằng SQL: máy khách gửi yêu cầu đến máy chủ, máy chủ xử lý yêu cầu đó và chỉ cung cấp cho máy khách dữ liệu nhận được do yêu cầu này. Do đó, khách hàng không cần tải xuống dữ liệu và thực hiện các phép tính, chẳng hạn như trong Microsoft truy cập.

Ngoài ra, MySQL là phần mềm nguồn mở, tức là. nó có thể được tự do nghiên cứu và sửa đổi. Gói này được phân phối theo các điều khoản của GPL (Giấy phép Công cộng Chung) và có thể tải xuống miễn phí từ Internet (http://www.mysql.com) cho mục đích sử dụng phi thương mại.

Với sự ra đời của các công nghệ Internet cho phép tạo ra các trang Web động, nhu cầu về các DBMS phù hợp nhất cho việc này về tốc độ, độ tin cậy và độ ổn định đã tăng lên rất nhiều. Và ở đây, gói MySQL hoạt động tốt, hóa ra là nhanh, đơn giản và đáng tin cậy, tuy nhiên, phải trả giá bằng việc suy giảm chức năng (hãy đặt trước ngay rằng các nhà phát triển MySQL hứa sẽ bổ sung các chức năng còn thiếu trong các phiên bản tiếp theo của chương trình).

Qua nhìn chung, việc thiếu một số chức năng đã bị hy sinh vì tốc độ và độ tin cậy không gây ra nhiều rắc rối cho người dùng (mặc dù đôi khi vẫn xảy ra một số khó chịu). MySQL không đáp ứng được nhiệm vụ chạy cơ sở dữ liệu công ty chính thức, nhưng MySQL xử lý khá tốt các công việc hàng ngày.

sai sót

Dưới đây là danh sách ngắn các tính năng chính mà MySQL đang thiếu.

Giao dịch- cho phép bạn kết hợp một số truy vấn SQL thành một đơn vị công việc và nếu bất kỳ truy vấn nào trong đơn vị này không thành công, hãy quay lại để trả dữ liệu về trạng thái ban đầu. Hãy giải thích bằng một ví dụ.

Bạn cần rút tiền từ một tài khoản và chuyển vào tài khoản khác. Để làm điều này, bạn cần thực hiện hai truy vấn SQL: đầu tiên là rút tiền từ một tài khoản, thứ hai là ghi có vào tài khoản khác. Nếu bạn không áp dụng giao dịch thì nếu yêu cầu thứ hai không thành công, tiền sẽ bị rút khỏi tài khoản nhưng không được ghi có vào tài khoản khác. Việc sử dụng các giao dịch cho phép bạn quay trở lại như thể tiền chưa hề được rút khỏi tài khoản.

Lưu ý rằng bằng cách sử dụng lệnh LOCK TABLES trong MySQL, bạn có thể mô phỏng một giao dịch. Lệnh này khóa bảng trong khi các truy vấn được thực thi, qua đó đảm bảo tính toàn vẹn của dữ liệu nhưng vẫn không thể khôi phục được.

Gây nên- phục vụ để tự động hóa việc kiểm soát trạng thái và hoạt động của cơ sở dữ liệu. Trình kích hoạt được lưu trữ trong cơ sở dữ liệu và kích hoạt khi một sự kiện nhất định xảy ra. Hãy lấy ví dụ tương tự với việc chuyển tiền: nếu yêu cầu thứ hai không thành công, trình kích hoạt sẽ kích hoạt, quá trình này sẽ quay trở lại hoặc gửi tin nhắn đến quản trị viên cơ sở dữ liệu.

Thủ tục lưu trữ- đây là một số lệnh SQL được lưu trữ trong cơ sở dữ liệu dưới một tên nhất định và cùng nhau thực hiện một chức năng nhất định. Sử dụng các thủ tục được lưu trữ, bạn có thể mở rộng Cú pháp SQL sao cho nó sẽ giống với ngôn ngữ lập trình thông thường (ví dụ: Oracle PL/SQL). Trong ví dụ chuyển tiền của chúng tôi, chúng tôi có thể lưu hai truy vấn SQL dưới cùng một tên, sau đó gọi thủ tục này, chuyển cho nó hai số tài khoản và số tiền làm tham số. Sau đó, cả hai truy vấn sẽ được thực hiện trong một giao dịch.

Truy vấn lồng nhau- cho phép bạn thay thế các giá trị vào các điều kiện lựa chọn một cách linh hoạt, dựa trên kết quả thực hiện một truy vấn khác. Theo tác giả, nếu bằng cách nào đó bạn có thể quản lý mà không cần tất cả những điều trên, thì việc thiếu các truy vấn lồng nhau đôi khi thực sự làm hỏng cuộc sống. Ví dụ: để tìm ra phương tiện nào vận chuyển nhiều hàng hóa hơn mức trung bình của đội xe, bạn cần thực hiện truy vấn SQL sau:

CHỌN auto FROM autopark WHERE massa > !Hơn cả cái gì? Tôi không biết mức trung bình là bao nhiêu!

Để làm điều này, cần tính giá trị trung bình trong trường massa:

CHỌN AVG (massa) TỪ autopark

Nếu các truy vấn lồng nhau được hỗ trợ thì hai truy vấn này có thể được lồng vào nhau:

SELECT auto FROM autopark WHERE massa >(SELECT AVG(massa) FROM autopark)

Nhưng trong trường hợp của MySQL, giá trị trung bình phải được tìm riêng và thay thế vào một truy vấn khác trực tiếp trong tập lệnh CGI, điều này chắc chắn ảnh hưởng đến hiệu suất.

Hướng dẫn UNION- nói một cách đơn giản, nó kết hợp đầu ra của một số truy vấn thành một, với khả năng loại bỏ các hàng trùng lặp.

Cập nhật dữ liệu theo tầng- Cho phép bạn xóa và cập nhật dữ liệu liên quan. Ví dụ: khi bạn xóa một bản ghi khách hàng khỏi cơ sở dữ liệu, tất cả các bản ghi đơn hàng cho khách hàng đó sẽ tự động bị xóa khỏi các bảng liên quan.

Thuận lợi

Bây giờ hãy liệt kê những ưu điểm của MySQL.

Hiệu suất. Nhờ cơ chế đa luồng bên trong nên MySQL rất nhanh.

Sự an toàn. Mức độ bảo mật khá cao được đảm bảo nhờ cơ sở dữ liệu mysql, được tạo trong quá trình cài đặt gói và chứa năm bảng. Bằng cách sử dụng các bảng này, bạn có thể mô tả người dùng nào từ miền nào có thể làm việc với bảng nào và anh ta có thể sử dụng những lệnh nào. Mật khẩu được lưu trữ trong cơ sở dữ liệu có thể được mã hóa bằng hàm pass() tích hợp của MySQL.

Giấy phép. Việc cấp phép MySQL từng hơi khó hiểu; Chương trình này hiện được phân phối miễn phí cho mục đích phi thương mại.

Tính mở của mã. Nhờ đó, bạn có thể tự mình thêm các chức năng cần thiết vào gói, mở rộng chức năng của nó theo yêu cầu. Nhân tiện, chính các tác giả MySQL có thể làm điều này cho bạn với một khoản phí. Để đặt mua tiện ích mở rộng MySQL từ người tạo gói, chỉ cần truy cập http://www.mysql.com và điền vào biểu mẫu thích hợp.

Độ tin cậy. Những người tạo ra MySQL đã làm rất tốt: theo như tôi biết, gói này khá ổn định và khó bị phá vỡ. Tôi không theo dõi cụ thể các bản tóm tắt kết quả. tin tặc tấn công trên MySQL, nhưng tôi chưa bao giờ thấy (không giống như các máy chủ Web tương tự) thông báo rằng MySQL đã bị hỏng do mục đích xấu của ai đó.

Tài nguyên.Điều này có thể phụ thuộc vào nhiều yếu tố khác nhau, nhưng trong mọi trường hợp, bạn sẽ không cần siêu máy tính.

Cộng đồng. Do tính mở của mã, tính chất tự do của chương trình cũng như hoạt động ổn định và đáng tin cậy của nó, một cộng đồng gồm những người không chỉ trung thành với MySQL mà còn tham gia bằng mọi cách có thể vào việc phát triển MySQL đã được hình thành. chính gói đó và đào tạo những người ít kinh nghiệm hơn để làm việc với nó. tồn tại số lượng lớn danh sách gửi thư và hội nghị nơi bạn có thể nhận được trợ giúp miễn phí bất cứ lúc nào trong ngày.

Tính di động. Hiện tại, chương trình đã có phiên bản dành cho hầu hết các nền tảng máy tính phổ biến. Điều này gợi ý rằng một số hệ điều hành. Bạn có thể chọn những gì sẽ làm việc, chẳng hạn như Linux hoặc Windows, nhưng ngay cả khi bạn thay đổi hệ điều hành, bạn sẽ không mất dữ liệu và thậm chí bạn sẽ không cần đến nó công cụ bổ sungđể chuyển chúng.

Tôi không biết nên coi việc MySQL không có giao diện người dùng đồ họa (GUI) là một bất lợi hay một lợi thế. Ví dụ: sẽ thuận tiện hơn cho tôi khi viết một truy vấn SQL theo cách thủ công (nhân tiện, kết quả thực thi của nó có thể được chuyển hướng đến một tệp) hơn là sử dụng trình hướng dẫn truy vấn, như trong Microsoft SQL Server.

Có một số chương trình máy khách dành cho MySQL có GUI, nhưng chúng chưa hoàn hảo và phần lớn chỉ làm chậm công việc. Nếu bạn thích GUI, tôi khuyên bạn nên tải xuống và dùng thử các chương trình này để khuyến khích người tạo cải tiến hơn nữa sản phẩm của họ. Đây là danh sách đầy đủ các chương trình có GUI:

  • Winmysqladmin - có trong bản phân phối MySQL Windows, có giao diện đồ họa tiêu chuẩn và cho phép bạn quản trị MySQL;
  • MySqlManager - được bao gồm trong bản phân phối Windows của MySQL, nó chứa các chức năng máy khách (nhưng tác giả không thể làm bất cứ điều gì nghiêm trọng với sự trợ giúp của nó);
  • Quản trị viên MySQL cho cửa sổ- một tiện ích của bên thứ ba “tiên tiến” hơn. Cho phép bạn đăng ký và kết nối đồng thời với nhiều máy chủ MySQL, tạo, xóa và thay đổi cấu trúc cơ sở dữ liệu và bảng, tạo khóa trong bảng, viết truy vấn SQL và lưu chúng vào một tệp:
  • XMySQL là máy khách MySQL dành cho các hệ thống giống X Window. Cung cấp quyền truy cập đầy đủ vào các bảng, cho phép chèn nhóm và loại bỏ, có chức năng thiết kế truy vấn và quản lý gói. Chương trình có thể được tìm thấy tại http://web.wt.net/~dblhack/.

Danh sách đầy đủ hơn về tất cả các loại tiện ích dành cho MySQL (và nó rất lớn) có tại http://www.mysql.com/downloads/. Ở đó bạn sẽ tìm thấy rất nhiều điều thú vị và hữu ích: xuất dữ liệu từ MySQL sang Microsoft Access và ngược lại, trình điều khiển ODBC, v.v.

Mặc dù thiếu giao diện đồ họa nhưng gói MySQL bao gồm các công cụ quản trị khá mạnh mẽ với giao diện dòng lệnh. Dưới đây là danh sách của họ với mô tả ngắn gọn tiện ích

  • MySQLAdmin là công cụ quản trị MySQL chính. Với nó, bạn có thể tạo, hủy, sửa đổi cơ sở dữ liệu và có toàn quyền kiểm soát máy chủ của mình.
  • MySQLDump là một tiện ích sao lưu dữ liệu.
  • MySQLAccess - cho phép bạn thay đổi bảng quyền truy cập và hiển thị nội dung của chúng ở dạng dễ đọc.
  • MySQLBug - trong trường hợp xảy ra lỗi trong MySQL, tiện ích này sẽ tạo một báo cáo lỗi cho các nhà phát triển chương trình, đồng thời gửi nó đến danh sách gửi thư của MySQL để các chuyên gia có thể giúp giải quyết vấn đề của bạn.
  • MySQLImport - Nhập dữ liệu từ một tệp được phân tách vào cơ sở dữ liệu.
  • MySQLShow - hiển thị cấu trúc của cơ sở dữ liệu và các bảng chứa trong đó.

Tôi muốn thu hút sự chú ý của người đọc đến điều này: hiện nay có những chương trình hoạt động thông qua giao diện CGI cung cấp một cách thực tế gói đầy đủ dịch vụ quản trị cơ sở dữ liệu. Các chương trình này được đặt trên các máy chủ Web và là các tập lệnh CGI thông thường. Rất thường xuyên những tập lệnh này được đặt trong các thư mục có thể truy cập công khai. Điều nguy hiểm là việc sử dụng công cụ tìm kiếm Bất kỳ ai cũng có thể tìm thấy những chương trình như vậy theo tên tệp và sau đó làm bất cứ điều gì họ mong muốn với cơ sở dữ liệu của bạn. Vấn đề này có thể dễ dàng tránh được bằng cách đặt các tập lệnh này vào các thư mục được bảo vệ bằng mật khẩu trên máy chủ. Nhưng Quyết định tốt nhất- thường từ chối sử dụng các chương trình như vậy trên máy chủ.

MySQL cũng có phần mở rộng riêng ngôn ngữ SQL. Các hàm này có thể được sử dụng trong truy vấn theo hai cách. Đầu tiên, dưới dạng giá trị cần truy xuất: hàm được đưa vào danh sách các trường cần truy xuất. Giá trị trả về của hàm sẽ được tính cho từng bản ghi bảng và hiển thị như thể đó là một trường bảng. Ví dụ: hãy hiển thị tiêu đề của bài viết và độ dài của nó:

CHỌN tiêu đề, LENGTH(tiêu đề) TỪ bảng

Kết quả là, chúng ta nhận được hai cột, trong đó một cột được lấy từ bảng và cột thứ hai được tính toán.

Thứ hai, hàm này có thể được sử dụng như một phần của mệnh đề WHERE - trong trường hợp này, nó sẽ thay thế một hằng số tại thời điểm truy vấn được thực thi. Hãy để tôi giải thích bằng một ví dụ: bạn cần tìm các sự kiện đã xảy ra hơn một ngày trước.

CHỌN sự kiện TỪ bảng WHERE thời gian>(Unix_TIMESTAMP()-(60*60*24))

Ở đây hàm Unix_TIMESTAMP() tính toán thời điểm hiện tại, từ đó chúng ta trừ đi một ngày.

Ứng dụng

Theo tác giả, lĩnh vực ứng dụng phù hợp nhất cho MySQL là Internet, nhờ hệ thống bảo mật tốt của gói này, hoạt động ổn định và hiệu suất cao. Nếu dự án Internet của bạn thiếu giao dịch, bạn có thể sử dụng Postgres. Postgres về nhiều mặt tương tự như MySQL, gần như tốt về hiệu năng nhưng có nhiều chức năng hơn. Tuy nhiên, theo kinh nghiệm cho thấy, khả năng của MySQL khá đủ cho các dự án Internet đơn giản.

Đối với sử dụng MySQL với tư cách là cơ sở dữ liệu của công ty, tình hình ở đây không mấy thuận lợi. Hãy xây dựng các yêu cầu đối với máy chủ SQL của cơ sở dữ liệu công ty, dựa trên đặc thù công việc của nhân viên và đánh giá MySQL theo quan điểm của các yêu cầu này.

Khả năng làm việc với nhiều người dùng. Yêu cầu hiển nhiên này cần được bổ sung bởi thực tế là cường độ sử dụng cơ sở dữ liệu trong trường hợp này sẽ cao hơn nhiều so với trên máy chủ Web. Trên thực tế, đối với một trang web, 20 khách truy cập cùng lúc được coi là thành công lớn, và trong trường hợp cơ sở công ty, ngay cả một công ty nhỏ cũng có thể tự hào về chỉ số này. Cần đặc biệt chú ý đến thực tế là cơ sở dữ liệu của công ty sử dụng phức tạp hơn giao diện người dùng hơn các trang trên trang web; nói cách khác, nó gửi yêu cầu đến máy chủ mạnh mẽ hơn. TRONG về mặt kỹ thuậtđiều này có nghĩa là cần phải khóa ở cấp độ bản ghi đang được sửa đổi. Ở đây MySQL hiển thị không Cách tốt nhất có thể: Việc khóa nó được thực hiện ở cấp độ bàn. Đặc biệt, điều này có nghĩa là nếu ai đó nhập đơn hàng thì tất cả các truy vấn (phân tích số liệu thống kê, chọn bản ghi cho báo cáo, v.v.) sẽ phải đợi cho đến khi đơn hàng được nhập. Trong trường hợp cơ sở dữ liệu của công ty, điều này thậm chí còn phủ nhận lợi thế về tốc độ của MySQL.

Kiểm soát tính toàn vẹn dữ liệu ở cấp độ máy chủ SQL. Cơ sở dữ liệu của công ty là khác nhau mạch phức tạp dữ liệu và việc duy trì tính toàn vẹn của dữ liệu bằng chương trình máy khách là rất khó: một mối quan hệ có thể kết nối năm đến bảy bảng và số lượng bảng có thể lên tới 30-40. Và trong trường hợp này, khả năng cập nhật và xóa các bản ghi trong các bảng liên quan, điều mà MySQL thiếu, trở nên quan trọng.

Ngoài ra, chúng tôi đã đề cập rằng cơ sở dữ liệu công ty sử dụng giao diện phức tạp hơn và trường hợp này làm phát sinh thêm hai yêu cầu: hỗ trợ cho tất cả các câu lệnh SQL tiêu chuẩn (và tiện ích mở rộng hữu ích), cũng như việc sử dụng các thủ tục và trình kích hoạt được lưu trữ. Than ôi, MySQL cũng không hài lòng ở đây.

Tóm tắt tất cả những gì đã nói, chúng ta có thể kết luận rằng đối với hầu hết các dự án Internet, khả năng của MySQL DBMS là khá đủ. Chúng sẽ đủ để lưu trữ sổ địa chỉ trong mạng nội bộ của doanh nghiệp.

MySQL là một loại cơ sở dữ liệu quan hệ. MySQL là một máy chủ có thể được kết nối bởi nhiều người dùng khác nhau.

Khi kết nối Internet, bạn có nhập tên người dùng và mật khẩu cũng như tên của máy chủ mà bạn đang kết nối không? Khi làm việc với MySQL, hệ thống tương tự được sử dụng.

Một điều nữa: cơ sở dữ liệu quan hệ là gì? Phương tiện quan hệ dựa trên bảng. Biên tập viên điện tử nổi tiếng bảng excel từ Microsoft thực sự là một trình soạn thảo cơ sở dữ liệu quan hệ.

Kết nối với máy chủ MySQL

Để kết nối với máy chủ MySQL bằng PHP, hãy sử dụng hàm mysqli_connect(). Chức năng này có ba đối số: tên máy chủ, tên người dùng và mật khẩu.

Hàm mysqli_connect() trả về mã định danh kết nối, nó được lưu trữ trong một biến và sau đó được sử dụng để làm việc với cơ sở dữ liệu.

Mã kết nối máy chủ MySQL:

$link = mysqli_connect("localhost", "root", "");

Trong trường hợp này, tôi đang làm việc trên một máy cục bộ trên Denwere nên tên máy chủ là localhost, tên người dùng là root và không có mật khẩu.

Kết nối cũng cần được đóng sau khi làm việc xong với MySQL. Hàm mysqli_close() được sử dụng để đóng kết nối. Hãy mở rộng ví dụ:

$link = mysqli_connect("localhost", "root", ""); if (!$link) die("Error"); mysqli_close ($ liên kết);

Ở đây chúng tôi đã kiểm tra sự thật của mã định danh kết nối; nếu có lỗi xảy ra với kết nối của chúng tôi thì chương trình sẽ không được thực thi, hàm die() sẽ dừng thực thi và hiển thị thông báo lỗi trong trình duyệt.

Lỗi kết nối

Các chức năng sau đây được sử dụng để kiểm tra kết nối:

  • mysqli_connect_errno() - trả về mã lỗi của lần thử kết nối cuối cùng. Nếu không có lỗi, trả về 0.
  • mysqli_connect_error() - trả về mô tả về lỗi kết nối cuối cùng tới máy chủ MySQL.
định nghĩa("HOST", "localhost"); định nghĩa("DB_USER", "root"); định nghĩa("DB_PASSWORD", ""); định nghĩa ("DB", "người kiểm tra"); $link = mysqli_connect(HOST, DB_USER, DB_PASSWORD, DB); /* kiểm tra kết nối */ if (mysqli_connect_errno()) ( printf("Không thể kết nối: %s\n", mysqli_connect_error()); exit(); ) else ( printf("Kết nối thành công: %s\n" , mysqli_get_host_info($link));

Hàm mysqli_get_host_info() trả về một chuỗi chứa loại kết nối đang được sử dụng.

Cũng lưu ý rằng bằng cách sử dụng lệnh xác định, tôi đã lưu tất cả tham số kết nối dưới dạng hằng số. Khi bạn viết những dự án lớn và sẽ có rất nhiều file kết nối tới MySQL server, sẽ rất thuận tiện khi lưu trữ các thông số kết nối trong tập tin riêng biệt và dán nó bằng cách sử dụng bao gồm các chức năng hoặc yêu cầu.

Chọn cơ sở dữ liệu

Một máy chủ MySQL có thể có nhiều cơ sở dữ liệu. Trước hết, chúng ta cần chọn cơ sở mà chúng ta cần làm việc. Trong PHP, có một tham số khác cho hàm này trong hàm mysqli_connect() - tên cơ sở dữ liệu.

Tôi đã tạo nó trên máy tính của mình thông qua phpMyAdmin bằng trình kiểm tra tên. Hãy kết nối với nó:

$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); mysql_close ($ liên kết);

Vì vậy, chúng tôi đã chọn một cơ sở dữ liệu để làm việc. Nhưng như chúng ta biết, cơ sở dữ liệu quan hệ bao gồm các bảng và cơ sở dữ liệu của chúng ta chưa có bảng. Cơ sở dữ liệu được tạo trống, không có bảng. Các bảng phải được thêm vào nó một cách riêng biệt. Bây giờ hãy thêm một bảng vào nó bằng PHP.

Tạo một bảng

Trong tên của cơ sở dữ liệu MySQL, phần SQL là viết tắt của Ngôn ngữ truy vấn có cấu trúc, dịch là ngôn ngữ truy vấn có cấu trúc. Trong ngôn ngữ SQL, chúng ta sẽ viết các truy vấn từ chương trình PHP gửi chúng đến máy chủ MySQL.

Để tạo bảng chúng ta chỉ cần đưa ra lệnh CREATE TABLE. Hãy tạo một bảng có tên là người dùng trong các cột trong đó thông tin đăng nhập (cột đăng nhập) và mật khẩu (cột mật khẩu) của người dùng sẽ được lưu trữ.

$query = "TẠO người dùng BẢNG (đăng nhập VARCHAR(20), mật khẩu VARCHAR(20))";

Trong đoạn mã này, chúng ta đã gán cho biến $query một chuỗi văn bản đại diện cho truy vấn SQL. Chúng tôi tạo một bảng có tên là người dùng chứa hai cột thông tin đăng nhập và mật khẩu, cả hai đều có kiểu dữ liệu VARCHAR(20). Chúng ta sẽ nói về các kiểu dữ liệu sau, bây giờ tôi chỉ lưu ý rằng VARCHAR(20) là một chuỗi chiều dài tối đa 20 ký tự.

Để gửi yêu cầu của chúng tôi tới máy chủ MySQL chúng tôi sử dụng hàm PHP mysqli_query() . Hàm này trả về số dương nếu thao tác thành công và trả về sai nếu xảy ra lỗi (cú pháp yêu cầu không chính xác hoặc chương trình không có quyền thực thi yêu cầu).

$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "TẠO người dùng BẢNG (đăng nhập VARCHAR(20), mật khẩu VARCHAR(20))"; mysqli_query ($ truy vấn); mysqli_close ($ liên kết);

Truy vấn SQL không cần phải được ghi vào một biến; nó có thể được viết trực tiếp dưới dạng đối số cho hàm mysql_query(). Nó chỉ làm cho mã dễ đọc hơn.

Tập lệnh này có một nhược điểm - nó không xuất ra bất cứ thứ gì cho trình duyệt. Hãy thêm một tin nhắn:

$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "TẠO người dùng BẢNG (đăng nhập VARCHAR(20), mật khẩu VARCHAR(20))"; if (mysqli_query($query)) echo "Bảng đã được tạo."; else echo "Bảng chưa được tạo."; mysqli_close ($ liên kết);

Nếu chạy lại tập lệnh này, chúng ta sẽ thấy thông báo trên trình duyệt: “Bảng chưa được tạo”. Thực tế là bảng đã được tạo trong lần khởi chạy đầu tiên và không thể tạo lại bảng có cùng tên. Chúng ta đang gặp phải một tình huống lỗi, vì vậy đã đến lúc nói về việc xử lý lỗi khi làm việc với MySQL.

Xử lý lỗi

Khi gỡ lỗi một chương trình, chúng ta có thể cần thông tin chính xác về lỗi. Khi xảy ra lỗi trong MySQL, máy chủ cơ sở dữ liệu sẽ đặt số lỗi và một dòng mô tả lỗi đó. PHP có các chức năng đặc biệt để truy cập dữ liệu này.

  • mysqli_errno() - trả về số lỗi.
  • mysqli_error() - trả về một chuỗi mô tả lỗi.

Bây giờ hãy thêm hàm mysql_error() vào tập lệnh của chúng ta:

$link = mysql_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "TẠO người dùng BẢNG (đăng nhập VARCHAR(20), mật khẩu VARCHAR(20))"; if (mysqli_query($query)) echo "Bảng đã được tạo."; else echo "Bảng chưa được tạo: ".mysqli_error(); mysqli_close ($ liên kết);

Bây giờ tập lệnh của chúng ta sẽ trả về dòng này cho trình duyệt: “Bảng chưa được tạo: Bảng “người dùng” đã tồn tại.”

Xóa một bảng

Vì vậy, bây giờ chúng ta có một cái bàn mà chúng ta không cần. Đã đến lúc tìm hiểu cách loại bỏ bảng khỏi cơ sở dữ liệu.

Để xóa một bảng, sử dụng lệnh DROP TABLE theo sau là tên bảng.

$link = mysqli_connect("localhost", "root", "", "tester"); if (!$link) die("Error"); $query = "Người dùng DROP TABLE"; if (!mysqli_query($query)) echo "Lỗi khi xóa bảng: ".mysqli_error(); else echo "Bảng đã bị xóa."; mysqli_close ($ liên kết);

Kết quả

Vậy là chúng ta đã làm chủ được Khái niệm cơ bản về MySQL. Những gì chúng tôi đã học để làm:

  • Kết nối với cơ sở dữ liệu MySQL bằng hàm mysqli_connect().
  • Đóng kết nối với máy chủ MySQL bằng hàm mysqli_close().
  • Gửi truy vấn SQL đến máy chủ MySQL bằng hàm mysqli_query().
  • Chúng ta đã học truy vấn SQL để tạo bảng: tạo bảng.
  • Chúng ta đã học truy vấn SQL để xóa một bảng: thả bảng.
  • Chúng ta đã học cách xử lý lỗi bằng cách sử dụng hàm mysqli_errno() và mysqli_error().

Sau đó chúng ta sẽ xem xét kỹ hơn các kiểu dữ liệu MySQL.

Đọc bài tiếp theo:

MySQL là một trong những cơ sở dữ liệu dễ quản lý nhất trên tất cả các nền tảng. Ngoài ra, cơ sở dữ liệu này không đòi hỏi nhiều tài nguyên nên nó có thể được sử dụng ngay cả trên những máy tính cá nhân với khối lượng nhỏ bộ nhớ truy cập tạm thời và ổ cứng. Về vấn đề này, các nhà phát triển sử dụng Công cụ PHP, đã sử dụng MySQL từ lâu để tạo môi trường phát triển cục bộ hoàn chỉnh cho web trên tất cả các loại máy tính khách, thậm chí cả máy tính xách tay.

Bất kỳ nhà phát triển nào làm việc trong môi trường PHP đều có thể quản lý cơ sở dữ liệu MySQL một cách độc lập, không giống như một số cơ sở dữ liệu khác. Để đơn giản hóa hơn nữa nhiệm vụ quản trị, bạn có thể tận dụng nhiều công cụ không chỉ được cung cấp trong cơ sở dữ liệu MySQL mà còn có thể được lấy từ các nhà phát triển bên thứ ba.

Các khái niệm cơ bản cần làm quen ở giai đoạn này, là:

  • cơ sở dữ liệu- vùng chứa toàn bộ bộ sưu tập dữ liệu MySQL;
  • bàn- một thùng chứa được lồng trong cơ sở dữ liệu nơi dữ liệu được lưu trữ;
  • đường kẻ- một bản ghi riêng biệt có thể chứa nhiều trường;
  • cột- tên trường bên trong dòng.

Cần lưu ý rằng tôi không cố gắng tái tạo thuật ngữ chính xác được sử dụng trong tài liệu giáo dục trên cơ sở dữ liệu quan hệ mà chỉ cung cấp các định nghĩa đơn giản, phổ biến để giúp bạn nhanh chóng hiểu các khái niệm cơ bản và bắt đầu làm việc với cơ sở dữ liệu.

Truy cập MySQL từ dòng lệnh

Có ba cách chính để làm việc với MySQL: sử dụng dòng lệnh, sử dụng giao diện web như phpMyAdmin và sử dụng ngôn ngữ lập trình như PHP. Phương pháp thứ ba sẽ được thảo luận trong các bài viết tiếp theo, nhưng bây giờ chúng ta hãy xem xét hai phương pháp đầu tiên.

Nếu bạn đã cài đặt OpenServer WAMP server theo đúng hướng dẫn trong bài thì truy cập vào chương trình thực thi MySQL có thể được lấy từ thư mục sau:

Khóa học MySQL từ đầu.

Thay vì MySQL-5.7-x64, bạn cần thay thế phiên bản được chỉ định trong cài đặt OpenServer trong tab "Mô-đun".

Bạn cần mở chương trình Command Promt và vào thư mục này. Việc này được thực hiện bằng lệnh cd PATH_TO_NEEDED_FOLDER:

Mã này chỉ khả dụng sau khi mua khóa học MySQL từ đầu.

Sau này, bạn cần chạy chương trình mysql.exe trong thư mục này, truyền tham số đặc biệt của nó. Để làm điều này trong dòng lệnh bây giờ bạn cần chạy lệnh:

Mã này chỉ khả dụng sau khi mua khóa học MySQL từ đầu.

Kết quả là máy khách MySQL sẽ khởi động. Nó được kết nối với máy chủ MySQL, được khởi chạy khi OpenServer khởi động.

Nếu điều này không dẫn đến kết quả mong muốn và hiển thị thông báo lỗi về việc kết nối với máy chủ MySQL “Không thể kết nối với máy chủ MySQL trên "localhost"", hãy đảm bảo rằng OpenServer đang chạy và MySQL được chỉ định trong mô-đun.

Tham số -u là viết tắt của người dùng. Nghĩa là, đây là hộp kiểm để chỉ định người dùng mà bạn cần kết nối với máy chủ. gốc là nhất người dùng chính trong MySQL. Nó được tạo khi máy chủ được cài đặt và theo mặc định nó không có mật khẩu.

Nhưng hãy quay trở lại thiết bị đầu cuối của chúng tôi. Thông qua ứng dụng khách bảng điều khiển này, chúng tôi có thể gửi nhiều lệnh khác nhau đến máy chủ. Hãy chạy lệnh liệt kê tất cả các cơ sở dữ liệu được tạo trên máy chủ này.

Mã này chỉ khả dụng sau khi mua khóa học MySQL từ đầu.

Đáp lại, chúng ta sẽ nhận được một danh sách cơ sở dữ liệu được thiết kế đẹp mắt. Bạn sẽ có ít chúng hơn tôi vì tôi đã thêm chúng rồi.

Làm việc với MySQL thông qua phpMyAdmin

Để quản lý cơ sở dữ liệu và bảng, việc sử dụng chương trình phpMyAdmin sẽ dễ dàng và nhanh hơn nhiều. Trước khi vào thanh địa chỉ trình duyệt hàng tiếp theo, bạn cần đảm bảo rằng chương trình OpenServer đang chạy và do đó, cơ sở dữ liệu MySQL đã sẵn sàng để sử dụng:

Ở đây chúng ta sẽ được chào đón bởi một cửa sổ đẹp mắt để đăng nhập vào hệ thống.

Cũng giống như trường hợp với ứng dụng giao diện điều khiển chỉ định người dùng root và để lại mật khẩu trống. Nhấp vào "đăng nhập".

Sau đó, bạn sẽ được đưa tới bảng điều khiển cơ sở dữ liệu với giao diện khá thân thiện. Ở bên trái, bạn sẽ thấy danh sách cơ sở dữ liệu giống như bạn đã nhận được trong phiên bản bảng điều khiển. Bạn có thể chọc chúng và xem những gì bên trong.

Bây giờ, hãy nhấp vào tab SQL và chuyển đến cửa sổ nơi bạn có thể trực tiếp viết truy vấn vào MySQL DBMS, giống như trong bảng điều khiển:

Trong cửa sổ mở ra, nhập cùng một truy vấn:

Mã này chỉ khả dụng sau khi mua khóa học MySQL từ đầu.

Chúng tôi nhấn nút “chuyển tiếp” và thấy kết quả tương tự như trong trường hợp ứng dụng bảng điều khiển.

Các khái niệm SQL cơ bản

Theo Andrew Taylor, người đã phát triển ngôn ngữ SQL, tên của ngôn ngữ này không phải là viết tắt của Ngôn ngữ truy vấn có cấu trúc (hoặc bất kỳ thứ gì tương tự), mặc dù nhiều người tin rằng đúng như vậy. Ngôn ngữ SQL làm nền tảng cho một quy trình chặt chẽ hơn và nhiều hơn nữa. phương pháp chung lưu trữ dữ liệu so với tiêu chuẩn tổ chức cơ sở dữ liệu kiểu DBM trước đây, dựa trên việc sử dụng các tệp phẳng.

Ngôn ngữ SQL được xác định theo tiêu chuẩn ANSI(Viện Tiêu chuẩn Quốc gia Hoa Kỳ) và ECMA(Hiệp hội các nhà sản xuất máy tính châu Âu); cả hai tổ chức tiêu chuẩn hóa này đều được quốc tế công nhận. khuyến nghị chung Có sự khác biệt đáng chú ý về tiêu chuẩn SQL giữa một bên là các sản phẩm phần mềm thương mại và bên kia là các tổ chức cơ sở dữ liệu nguồn mở. Ví dụ, vài năm qua đã chứng kiến ​​sự phát triển nhanh chóng của cái gọi là cơ sở dữ liệu quan hệ đối tượng, cũng như các sản phẩm phần mềm SQL được thiết kế đặc biệt cho thị trường Web. Danh sách cơ sở dữ liệu có thể được sử dụng kết hợp với Hệ thống PHP, cực kỳ lớn nên phải tuân theo một số nguyên tắc nhất định khi chọn cơ sở dữ liệu phù hợp nhất.

Nguyên tắc cơ bản là trước tiên bạn phải xem xét nhu cầu phát triển của bản thân, hoặc ít nhất là biết chính xác mục tiêu bạn muốn đạt được. Nếu bạn cố đọc các đề xuất khác, bạn sẽ gặp rất nhiều thông báo rất thuyết phục với những lập luận như vậy mà theo đúng nghĩa đen là bạn “không thể làm được” nếu không có một số công cụ cơ sở dữ liệu nâng cao (chẳng hạn như trình kích hoạt hoặc thủ tục được lưu trữ) và bất kỳ sản phẩm phần mềm hỗ trợ SQL nào có chức năng này. không có những công cụ này khó có thể xứng đáng được gọi là sản phẩm SQL. Hãy coi những lời phán xét này như muối bỏ bể. Sẽ tốt hơn nhiều nếu bạn tự mình lập danh sách các đặc điểm cần thiết theo thứ tự quan trọng và sau đó bắt đầu tìm kiếm sản phẩm phần mềm phù hợp nhất với yêu cầu của bạn.

Nhưng bất chấp những điều trên, phần lớn hỗ trợ ngôn ngữ SQL vẫn tuân theo tiêu chuẩn. Vì vậy, trong các chương trình, theo quy luật, chỉ một số ít được sử dụng nhiều lần. câu lệnh SQL, bất kể sản phẩm phần mềm cụ thể nào được chọn để triển khai.

Về cơ bản, cơ sở dữ liệu SQL có cấu trúc logic rất đơn giản. Bất kỳ cài đặt phần mềm SQL cụ thể nào thường có thể bao gồm nhiều cơ sở dữ liệu. Ví dụ: một cơ sở dữ liệu có thể được sử dụng để lưu trữ dữ liệu về khách hàng và cơ sở dữ liệu khác có thể chứa dữ liệu về sản phẩm. (Một khó khăn nhất định phát sinh do thực tế là máy chủ SQL và tập hợp các bảng được máy chủ này hỗ trợ thường được biểu thị thuật ngữ chung cơ sở dữ liệu.) Mỗi ​​cơ sở dữ liệu chứa một số bảng, mỗi bảng bao gồm các cột được xác định cẩn thận và mỗi vị trí trong bảng có thể được coi là một bản ghi hoặc hàng được nhập vào bảng.

Bất kỳ máy chủ SQL nào cũng hỗ trợ bốn toán tử thao tác dữ liệu và nói chung các toán tử này làm nền tảng cho phần lớn các hoạt động được thực hiện với cơ sở quan hệ dữ liệu. Bốn câu lệnh cơ sở dữ liệu cơ bản này là CHỌN, CHÈN, CẬP NHẬT và XÓA. Câu lệnh SQL, còn được gọi là lệnh, rất tiện lợi và cho phép bạn thực hiện hầu hết mọi hành động cần thiết trong cơ sở dữ liệu.

Một đặc điểm quan trọng của bốn câu lệnh SQL này là chúng chỉ thao tác với các giá trị được lưu trữ trong cơ sở dữ liệu chứ không ảnh hưởng đến cấu trúc của chính cơ sở dữ liệu đó. Nói cách khác, các lệnh dựa trên các toán tử này có thể được sử dụng, chẳng hạn như để nhập dữ liệu thay vì tạo cơ sở dữ liệu; Sử dụng các lệnh như vậy, bạn có thể xóa bất kỳ phần dữ liệu nào khỏi cơ sở dữ liệu, nhưng bản thân “vỏ” sẽ vẫn còn nguyên, do đó, đặc biệt, bạn không thể gán tên của cơ sở dữ liệu hiện có cho cơ sở dữ liệu khác chạy trên cùng một máy chủ. Để thêm hoặc xóa cột, hủy toàn bộ cơ sở dữ liệu mà không để lại dấu vết hoặc tạo cơ sở dữ liệu mới, bạn phải sử dụng các lệnh khác như DROP, ALTER và CREATE.

Chúng ta sẽ xem xét chi tiết tất cả các toán tử này trong bài viết tiếp theo về các lệnh MySQL.

Hoạt động cơ sở dữ liệu rất thường xuyên trở thành nút thắt cổ chai khi triển khai một dự án web. Các vấn đề tối ưu hóa trong những trường hợp như vậy không chỉ liên quan đến người quản trị cơ sở dữ liệu. Lập trình viên cần cấu trúc bảng hợp lý, viết truy vấn tối ưu và mã hiệu quả hơn. Bài viết này cung cấp một danh sách nhỏ các kỹ thuật để tối ưu hóa công việc với MySQL dành cho các lập trình viên.

1. Tối ưu hóa truy vấn của bạn cho bộ đệm truy vấn.

Hầu hết các máy chủ MySQL đều sử dụng bộ nhớ đệm truy vấn. Đây là một trong phương pháp hiệu quả cải tiến hiệu suất được thực hiện bởi công cụ cơ sở dữ liệu trong lý lịch. Nếu một truy vấn được thực hiện nhiều lần, bộ đệm sẽ bắt đầu được sử dụng để lấy kết quả và thao tác được hoàn thành nhanh hơn nhiều.

Vấn đề là nó quá đơn giản, đồng thời bị ẩn khỏi nhà phát triển, và hầu hết các lập trình viên đều bỏ qua cơ hội tuyệt vời như vậy để cải thiện hiệu suất của dự án. Một số hành động thực sự có thể ngăn việc sử dụng bộ nhớ đệm truy vấn trong quá trình thực thi.

// Bộ đệm truy vấn KHÔNG HOẠT ĐỘNG $r = mysql_query("CHỌN tên người dùng TỪ người dùng WHERE signup_date >= CURDATE()"); // Bộ đệm truy vấn đang HOẠT ĐỘNG! $hôm nay = ngày("Y-m-d"); $r = mysql_query("CHỌN tên người dùng TỪ người dùng WHERE signup_date >= "$today"");

Nguyên nhân bộ đệm truy vấn không hoạt động trong trường hợp đầu tiên là do sử dụng hàm CURDATE(). Cách tiếp cận này được sử dụng cho tất cả các hàm không xác định, ví dụ: NOW(), RAND(), v.v. Vì kết quả trả về của hàm có thể thay đổi nên MySQL quyết định không đăng yêu cầu này trong bộ đệm. Tất cả những gì cần thiết để khắc phục tình trạng này là thêm một dòng mã PHP bổ sung trước yêu cầu.

2. Sử dụng EXPLAIN cho truy vấn SELECT của bạn

Sử dụng từ khóa EXPLAIN có thể giúp vẽ ra bức tranh về những gì MySQL đang làm để hoàn thành truy vấn của bạn. Hình ảnh này giúp bạn dễ dàng xác định các điểm nghẽn và các vấn đề khác trong truy vấn hoặc cấu trúc bảng.

Kết quả của truy vấn EXPLAIN cho biết chỉ mục nào được sử dụng, cách quét và sắp xếp bảng, v.v.

Chúng ta hãy thực hiện một truy vấn CHỌN (tốt nhất là một truy vấn phức tạp, có THAM GIA) và thêm từ khóa EXPLAIN trước nó. Bạn có thể sử dụng PhpMyAdmin cho việc này. Một truy vấn như vậy sẽ đưa ra kết quả trong một bảng đẹp. Giả sử chúng ta quên thêm chỉ mục vào một cột được sử dụng cho THAM GIA:

Sau khi thêm chỉ mục cho trường group_id:

Bây giờ thay vì quét 7883 hàng, chỉ có 9 và 16 hàng từ hai bảng sẽ được quét. Một cách hay để đánh giá hiệu suất là nhân tất cả các số trong cột “hàng”. Kết quả gần đúng tỷ lệ thuận với lượng dữ liệu được xử lý.

3. Sử dụng LIMIT 1 nếu bạn cần lấy một chuỗi duy nhất

Đôi khi, khi sử dụng một truy vấn, bạn đã biết rằng bạn chỉ đang tìm kiếm một hàng. Bạn có thể lấy một bản ghi duy nhất hoặc đơn giản kiểm tra sự tồn tại của bất kỳ số lượng bản ghi nào thỏa mãn mệnh đề WHERE.

Trong trường hợp như vậy, việc thêm LIMIT 1 vào truy vấn của bạn có thể cải thiện hiệu suất. Trong điều kiện này, công cụ cơ sở dữ liệu sẽ ngừng quét các bản ghi ngay khi tìm thấy và không quét toàn bộ bảng hoặc chỉ mục.

// Có người dùng nào từ Alabama không? // Đừng làm điều này: $r = mysql_query("SELECT * FROM user WHERE state = "Alabama""); if (mysql_num_rows($r) > 0) ( // ... ) // Điều này sẽ tốt hơn nhiều: $r = mysql_query("SELECT 1 FROM user WHERE state = "Alabama" LIMIT 1"); if (mysql_num_rows($r) > 0) ( // ... )

4. Lập chỉ mục các trường tìm kiếm của bạn

Lập chỉ mục không chỉ là khóa chính và khóa duy nhất. Nếu bất kỳ cột nào trong bảng của bạn được sử dụng cho truy vấn tìm kiếm, thì chúng cần được lập chỉ mục.

Như bạn có thể thấy, quy tắc này cũng áp dụng cho việc tìm kiếm theo một phần của chuỗi, ví dụ: “last_name THÍCH ‘a%’”. Khi bắt đầu chuỗi được sử dụng để tìm kiếm, MySQL có thể sử dụng chỉ mục của cột đang được tìm kiếm.

Bạn cũng nên hiểu những loại tìm kiếm nào bạn không thể sử dụng tính năng lập chỉ mục thông thường. Ví dụ: khi tìm kiếm từ (“WHERE post_content THÍCH ‘%apple%’”), lợi ích của việc lập chỉ mục sẽ không có. Trong những trường hợp như vậy, tốt hơn nên sử dụng tìm kiếm hoặc xây dựng toàn văn bản mysql Quyết định của riêng dựa trên việc lập chỉ mục.

5. Đánh chỉ mục và sử dụng cùng kiểu cho các cột liên kết

Nếu ứng dụng của bạn chứa nhiều truy vấn THAM GIA, bạn cần lập chỉ mục các cột được liên kết trong cả hai bảng. Điều này có tác dụng tối ưu hóa nội bộ các hoạt động liên kết trong MySQL.

Ngoài ra, các cột được liên kết phải cùng loại. Ví dụ: nếu bạn liên kết cột DECIMAL với cột INT từ một bảng khác, MySQL sẽ không thể sử dụng chỉ mục trên ít nhất một trong hai bảng. Ngay cả việc mã hóa ký tự cũng phải giống nhau đối với cột giống hệt nhau loại đường.

// Tìm kiếm một công ty từ một trạng thái cụ thể $r = mysql_query("CHỌN tên công ty TỪ người dùng TRÁI THAM GIA công ty TRÊN (users.state = Companies.state) WHERE user.id = $user_id"); // cả hai cột cho tên trạng thái phải được lập chỉ mục // và cả hai phải cùng loại và mã hóa ký tự // nếu không MySQL sẽ thất bại quét toàn bộ những cái bàn

6. Không sử dụng ORDER BY RAND()

Đây là một trong những thủ thuật trông rất ngầu và nhiều lập trình viên mới đã rơi vào bẫy của nó. Họ thậm chí không thể tưởng tượng được vấn đề khủng khiếp mà họ đang tạo ra cho chính mình khi bắt đầu sử dụng biểu thức này trong các truy vấn của mình.

Nếu bạn thực sự cần ngẫu nhiên hóa các hàng trong kết quả truy vấn của mình thì có nhiều cách tốt hơn để thực hiện việc này. Tất nhiên, điều này sẽ được triển khai bằng mã bổ sung, nhưng bạn sẽ được cứu khỏi sự cố tăng theo cấp số nhân với khối lượng dữ liệu. Vấn đề là, MySQL thực hiện thao tác RAND() (làm tốn thời gian của CPU) trên mỗi hàng trong bảng trước khi sắp xếp nó và chỉ cung cấp cho bạn một hàng.

// KHÔNG làm điều này: $r = mysql_query("CHỌN tên người dùng TỪ người dùng ORDER BY RAND() LIMIT 1"); // Điều này sẽ hoạt động tốt hơn: $r = mysql_query("SELECT count(*) FROM user"); $d = mysql_fetch_row($r); $rand = mt_rand(0,$d - 1); $r = mysql_query("CHỌN tên người dùng TỪ GIỚI HẠN người dùng $rand, 1");

Đây là cách bạn có được số ngẫu nhiên, nhỏ hơn số hàng trong kết quả truy vấn và sử dụng nó làm phần bù trong mệnh đề LIMIT.

7. Tránh sử dụng CHỌN *

Càng nhiều dữ liệu được đọc từ bảng, truy vấn sẽ càng chậm. Những hoạt động như vậy cũng mất thời gian để hoàn thành các hoạt động trên đĩa. Và nếu máy chủ cơ sở dữ liệu tách biệt với máy chủ web thì sự chậm trễ cũng sẽ do truyền dữ liệu qua mạng giữa các máy chủ.

Đó là một thói quen tốt để chỉ định một cột khi thực hiện CHỌN.

// Xấu: $r = mysql_query("CHỌN * TỪ người dùng Ở ĐÂU user_id = 1"); $d = mysql_fetch_assoc($r); echo "Chào mừng ($d["tên người dùng"])"; // Điều này tốt hơn: $r = mysql_query("CHỌN tên người dùng TỪ người dùng WHERE user_id = 1"); $d = mysql_fetch_assoc($r); echo "Chào mừng ($d["tên người dùng"])"; // Sự khác biệt trở nên đáng kể với lượng dữ liệu lớn

8. Cố gắng sử dụng trường id ở mọi nơi

Một cách thực hành tốt là sử dụng trường id trong mỗi bảng có tập thuộc tính PRIMARY KEY, AUTO_INCREMENT và thuộc loại INT. Tốt nhất là - UNSIGNED, vì trong trường hợp này giá trị không thể âm.

Ngay cả khi bảng của bạn có một trường có tên người dùng duy nhất, đừng đặt nó làm khóa chính. Các trường VARCHAR hoạt động chậm như khóa chính. Ngoài ra, cấu trúc cơ sở dữ liệu của bạn sẽ tốt hơn nếu nó sử dụng nội bộ các liên kết đến các bản ghi dựa trên id.

Ngoài ra, công cụ MySQL sử dụng các khóa chính cho mục đích nội bộ của nó và việc sử dụng trường id sẽ tạo ra điều kiện tối ưuđể giải quyết chúng.

Một ngoại lệ có thể xảy ra đối với quy tắc này là “bảng kết hợp”, được sử dụng cho mối quan hệ nhiều-nhiều giữa hai bảng khác. Ví dụ: bảng “posts_tags” chứa 2 cột: post_id, tag_id. Chúng được sử dụng để mô tả mối quan hệ giữa hai bảng “post” và “tags”. Bảng được mô tả có thể có khóa chính chứa cả hai trường id.

9. Sử dụng ENUM thay vì VARCHAR

// Tạo một biểu thức đã chuẩn bị sẵn if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) ( // Liên kết các tham số $stmt->bind_param("s", $state); / / Thực thi $stmt->execute(); // Liên kết các biến kết quả $stmt->bind_result($username); // Lấy các giá trị $stmt->fetch(); \n", $tên người dùng , $state); $stmt->close();

13. Yêu cầu không được lưu vào bộ đệm

Thông thường, khi bạn thực thi một yêu cầu từ một tập lệnh, tập lệnh đó sẽ bị gián đoạn cho đến khi yêu cầu được hoàn thành. Hành vi này có thể được thay đổi bằng cách sử dụng các truy vấn không có bộ đệm.

Lời giải thích tuyệt vời về hàm mysql_unbuffered_query() từ tài liệu PHP:

“mysql_unbuffered_query() gửi một truy vấn SQL đến máy chủ MySQL mà không tự động truy xuất và đệm các hàng kết quả như hàm mysql_query() thực hiện. Do đó, một lượng bộ nhớ nhất định được lưu bởi các truy vấn SQL phát hành bộ lớn kết quả và bạn có thể bắt đầu làm việc với tập kết quả ngay sau khi nhận được hàng đầu tiên mà không cần đợi truy vấn SQL được thực thi đầy đủ.”

Tuy nhiên, có một số hạn chế. Bạn phải đọc tất cả các hàng hoặc gọi mysql_free_result() trước khi thực hiện truy vấn tiếp theo. Bạn cũng không thể sử dụng mysql_num_rows() hoặc mysql_data_seek() để đặt kết quả.

14. Lưu trữ địa chỉ IP dưới dạng UNSIGNED INT

Nhiều lập trình viên tạo trường VARCHAR(15) để lưu trữ địa chỉ IP mà không hề nghĩ đến thực tế là họ sẽ lưu trữ một giá trị số nguyên trong trường này. Nếu bạn sử dụng INT, kích thước trường sẽ giảm xuống còn 4 byte và nó sẽ có độ dài cố định.

Bạn phải sử dụng loại UNSIGNED INT vì địa chỉ IP sử dụng tất cả 32 bit của số nguyên không dấu.

$r = "CẬP NHẬT người dùng SET ip = INET_ATON("($_SERVER["REMOTE_ADDR"])") WHERE user_id = $user_id";

15. Các bảng có độ dài bản ghi cố định (Static) nhanh hơn

Khi mỗi cột riêng lẻ trong một bảng có độ dài cố định thì toàn bộ bảng được coi là “tĩnh” hoặc “độ dài bản ghi cố định”. Ví dụ về các loại cột không có độ dài cố định: VARCHAR, TEXT, BLOB. Nếu bạn bao gồm ít nhất một cột thuộc loại này, bảng sẽ không còn được coi là "tĩnh" nữa và sẽ được công cụ MySQL xử lý khác nhau.

Các bảng "tĩnh" được công cụ MySQL xử lý nhanh hơn khi tìm kiếm bản ghi. Khi bạn cần đọc một bản ghi cụ thể trong bảng, vị trí của nó sẽ được tính toán nhanh chóng. Nếu kích thước hàng không cố định thì việc xác định vị trí của bản ghi cần có thời gian tìm kiếm và so sánh với chỉ số của khóa chính.

Các bảng như vậy cũng dễ lưu vào bộ nhớ đệm hơn và dễ phục hồi hơn sau các lỗi. Nhưng chúng có thể chiếm nhiều không gian hơn. Ví dụ: nếu bạn chuyển đổi trường VARCHAR(20) thành trường CHAR(20) thì 20 byte sẽ luôn bị chiếm dụng, bất kể chúng có được sử dụng hay không.

Việc sử dụng kỹ thuật Tách dọc giúp có thể tách các cột có độ dài thay đổi thành một bảng riêng biệt.

16. Tách dọc

Phân vùng theo chiều dọc là hành động phân chia cấu trúc bảng theo chiều dọc nhằm mục đích tối ưu hóa.

ví dụ 1: Bạn có một bảng chứa các địa chỉ nhà hiếm khi được sử dụng trong ứng dụng. Bạn có thể phân vùng bảng của mình và lưu trữ địa chỉ trong bàn riêng. Điều này sẽ làm giảm kích thước của bảng người dùng chính. Và như bạn đã biết, một bảng nhỏ hơn sẽ được xử lý nhanh hơn.

Ví dụ 2: Bạn có trường “last_login” trong bảng của mình. Nó được cập nhật mỗi khi người dùng đăng ký trên trang web. Tuy nhiên, mỗi lần cập nhật bảng đều khiến truy vấn được lưu vào bộ đệm, điều này có thể khiến hệ thống bị quá tải. Bạn có thể phân bổ trường này cho một bảng khác để ít cập nhật bảng người dùng hơn.

Nhưng bạn cần chắc chắn rằng bạn không cần liên kết vĩnh viễn hai bảng bạn vừa tách, vì điều này có thể dẫn đến hiệu suất kém.

17. Tách các truy vấn DELETE hoặc INSERT lớn

Nếu bạn cần thực hiện yêu cầu lớn XÓA hoặc CHÈN trên một trang web trực tiếp, bạn cần cẩn thận để không làm gián đoạn lưu lượng truy cập. Khi một truy vấn lớn đang chạy, nó có thể khóa các bảng của bạn và khiến ứng dụng của bạn dừng lại.

Apache chạy nhiều tiến trình/luồng song song. vì lý do này, nó hoạt động hiệu quả hơn khi tập lệnh hoàn thành việc thực thi càng nhanh càng tốt, do đó máy chủ không sử dụng quá nhiều kết nối mở và các tiến trình tiêu tốn tài nguyên, đặc biệt là bộ nhớ.

Nếu bạn khóa các bảng trong một khoảng thời gian dài (ví dụ: 30 giây trở lên) trên máy chủ web được tải nặng, bạn có thể gây ra tình trạng tồn đọng các quy trình và yêu cầu sẽ cần thời gian dọn dẹp đáng kể hoặc thậm chí khiến máy chủ web của bạn dừng lại.

Nếu bạn có một tập lệnh loại bỏ một số lượng lớn bản ghi, chỉ cần sử dụng mệnh đề LIMIT để chia nó thành các lô nhỏ nhằm tránh tình huống được mô tả.

Trong khi (1) ( mysql_query("DELETE FROM log WHERE log_date<= "2009-10-01" LIMIT 10000"); if (mysql_affected_rows() == 0) { // выполняем удаление break; } // вы можете сделать небольшую паузу usleep(50000); }

18. Các cột nhỏ được xử lý nhanh hơn

Đối với một công cụ cơ sở dữ liệu, đĩa là nút thắt cổ chai quan trọng nhất. Mong muốn làm cho mọi thứ ngày càng nhỏ hơn thường có tác động tích cực đến năng suất bằng cách giảm lượng dữ liệu được di chuyển.

Tài liệu MySQL chứa danh sách các tiêu chuẩn lưu trữ dữ liệu cho tất cả các loại.

Nếu bảng chỉ chứa một vài hàng thì không có lý do gì để tạo khóa chính kiểu INT thay vì MEDIUMINT, SMALLINT hoặc thậm chí TINYINT. nếu bạn chỉ cần ngày, hãy sử dụng DATE thay vì DATETIME.

Bạn chỉ cần nhớ về những cơ hội phát triển.

19. Chọn cơ chế lưu trữ phù hợp

Có hai công cụ lưu trữ dữ liệu chính cho MySQL: MyISAM và InnoDB. Mỗi thứ có lợi thế và bất lợi riêng của nó.

MyISAM rất phù hợp cho các ứng dụng đọc nhiều, nhưng nó không có khả năng mở rộng tốt khi có nhiều bản ghi. Ngay cả khi bạn cập nhật một trường trong một hàng, toàn bộ bảng sẽ bị khóa và không có tiến trình nào có thể đọc bất cứ thứ gì cho đến khi truy vấn hoàn tất. MyISAM thực hiện tính toán nhanh chóng cho các truy vấn như SELECT COUNT(*).

InnoDB là một công cụ lưu trữ phức tạp hơn và có thể chậm hơn MyISAM đối với hầu hết các ứng dụng nhỏ. Nhưng nó hỗ trợ khóa hàng, điều này tốt hơn cho việc chia tỷ lệ bảng. Nó cũng hỗ trợ một số tính năng bổ sung như giao dịch.

20. Sử dụng ánh xạ quan hệ đối tượng

Việc sử dụng ánh xạ quan hệ đối tượng (ORM - Object Relational Mapper) mang lại một số lợi thế. Mọi thứ có thể được thực hiện trong ORM đều có thể được thực hiện thủ công, nhưng cần nhiều nỗ lực hơn và yêu cầu cấp độ nhà phát triển cao hơn.

ORM rất tốt cho việc lười tải. Điều này có nghĩa là các giá trị có thể được truy xuất khi cần thiết. Nhưng bạn cần phải cẩn thận vì bạn có thể tạo nhiều truy vấn nhỏ sẽ làm giảm hiệu suất.

ORM cũng có thể kết hợp các truy vấn của bạn thành các giao dịch, nhanh hơn đáng kể so với các truy vấn cơ sở dữ liệu riêng lẻ.

Đối với PHP, bạn có thể sử dụng Học thuyết ORM.

21. Hãy cẩn thận với các kết nối liên tục

Kết nối liên tục được thiết kế để giảm chi phí khôi phục kết nối tới MySQL. Khi một kết nối liên tục được tạo, nó vẫn mở ngay cả sau khi tập lệnh hoàn tất. Vì Apache sử dụng lại các tiến trình con nên tiến trình này được thực thi cho tập lệnh mới và nó cũng sử dụng kết nối MySQL.

Điều này nghe có vẻ tuyệt vời về mặt lý thuyết. Nhưng trên thực tế, tính năng này không đáng một xu do có nhiều vấn đề. Nó có thể gây ra các vấn đề nghiêm trọng về giới hạn kết nối, tràn bộ nhớ, v.v.

Apache hoạt động theo nguyên tắc tương tranh và tạo ra nhiều tiến trình con. Đây là lý do khiến các kết nối liên tục không hoạt động như mong đợi trên hệ thống này. Trước khi sử dụng hàm mysql_pconnect(), hãy tham khảo ý kiến ​​của quản trị viên hệ thống của bạn.