目录

  • 一、建表语句
  • 二、索引三表关联优化分析
  • 三、join语句优化总结


一、建表语句

1、书籍的类别表建表语句

CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分类id' ,
`card` INT(10) UNSIGNED NOT NULL COMMENT '类别',
PRIMARY KEY (`id`) 
)COMMENT='书籍的类别表';

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

MYSQL三级等保加固_建表


2、书籍表的建表语句

CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '书籍id',
`card` INT(10) UNSIGNED NOT NULL COMMENT '类别',
PRIMARY KEY (`bookid`) 
)COMMENT='书籍表';

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

MYSQL三级等保加固_MYSQL三级等保加固_02


3、手机表的建表语句

CREATE TABLE IF NOT EXISTS `phone` (
`phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '手机id' ,
`card` INT(10) UNSIGNED NOT NULL COMMENT '类别',
PRIMARY KEY (`phoneid`) 
)COMMENT='手机表';

INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));

MYSQL三级等保加固_MYSQL三级等保加固_03

二、索引三表关联优化分析

1、执行3张表的索引查询sql如下：

SHOW INDEX FROM book;

MYSQL三级等保加固_MYSQL三级等保加固_04

SHOW INDEX FROM class;


MYSQL三级等保加固_结果集_05

SHOW INDEX FROM phone;


MYSQL三级等保加固_MYSQL三级等保加固_06

  • 结论：只有主键索引

2、直接执行explain查看情况

EXPLAIN SELECT * FROM class
LEFT JOIN book ON class.card=book.card
LEFT JOIN phone ON book.card=phone.card

MYSQL三级等保加固_MYSQL三级等保加固_07

  • 结论：type 都为All,需要进行优化。因为type的常用访问类型结果值从最好到最坏依次是：system>const>eq_ref>ref>range>index>ALL，得保证查询至少达到range级别，最好能达到ref。

3、第一次优化
（1）、在book 表中建立索引,并查看所建立的索引。

ALTER TABLE book ADD INDEX index_bookcar (card);
SHOW INDEX FROM book ;

MYSQL三级等保加固_字段_08

（2）、在phone 表中建立索引,并查看所建立的索引。

ALTER TABLE phone ADD INDEX index_phonecar (card);
SHOW INDEX FROM phone ;

MYSQL三级等保加固_建表_09

（3）、执行EXPLAIN

EXPLAIN SELECT * FROM class
LEFT JOIN book ON class.card=book.card
LEFT JOIN phone ON book.card=phone.card

MYSQL三级等保加固_字段_10

  • 结论：后2行的tyepe都是ref且总rows行数优化很好，效果不错，因此索引最好设置在需要经常查询的字段中。（即保证Join语句中被驱动表上Join条件字段已经被索引）

三、join语句优化总结

  • 尽可能减少Join语句中的NestedLoop（嵌套循环）的循环总次数；“永远用小结果驱动大的结果集”。
    class表是书的类别表，数据较少；
    book表是书籍表，数据较多；
    因为多种书可以对应一种书籍类别；
    所以class表数据为小结果集，book表数据为大结果集
  • 优先优化NestedLoop（嵌套循环）的内层循环；
    多层嵌套循环优先优化最内层的循环
  • 保证Join语句中被驱动表上Join条件字段已经被索引；
  • 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置（即mysql配置文件my.ini文件中JoinBuffer的设置）；