十六、性能优化

　　MySQL优化一方面是找出系统的瓶颈，提高MySQL数据库整体的性能；另一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷非服务。

　　MySQL常见的瓶颈：

    • CPU：CUP饱和的时候一般发生在数据装入内存或从磁盘读取数据的时候。

    • IO：磁盘I/O瓶颈发生在装入数据大于内存容量的时候。

    • 服务器硬件的性能瓶颈：top free iostat 和 vmstat 来查看系统的性能状态。 

一、优化查询

1.1、分析查询语句

　　使用EXLPLAIN关键字可以模拟优化器执行SQL语句，从而直到MySQL是如何处理的SQL语句，分析查询语句或者表结构的性能瓶颈。

　　explain的作用：

    • 表的读取顺序

    • 数据读取操作的操作类型

    • 哪些索引可以使用

    • 哪些索引被使用

    • 表之间的引用

    • 每张表有多少行被优化器查询

explain [extended] select select_options 

　　expalian查询结果字段解释

(1)、id

　　select标识符，select的查询序列号（查询语句的顺序）

  • id型同：执行顺序由上至下

  • id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

  • id相同/不同：id相同不同，同时存在

　　id如果相同可以看成是一组，组内从上往下顺序执行；不同组之间，id值越大，优先级越高，越先执行。

(2)、select_type

　　查询的类型，主要区别普通查询，联合查询，子查询等的复杂查询。

    • SIMPLE：简单查询，不包括子查询和连接查询

    • PRIMARY：主查询，或者是最外层的查询

    • UNION：连接查询的第2个，或者后面的查询语句

    • DEPENDENT UNION：连接查询中的第2个或者后面的SELECE语句，取决于外面的查询

    • UNION RESULT：连接查询的结果

    • SUBQUERY：子查询中的第一个SELECT语句

    • DEPENDENT SUBQUERY：子查询中的第一个SELECT,取决于外面的查询

    • DERIVED：导出表的SELECT

(3)、table

　　查询的表

(4)、type

　　连接的类型，下面是从最佳到最差,一般来说达到range级别，最好达到ref级别

system > const > eq_rf > ref > range > index > ALL

  • system：表是仅有一行记录的表

  • const：数据表最多只有一个匹配行，它将在查询开始被读取，表示通过索引一次就找到了，const用于比较primary key 或者unique 索引。因为只匹配一行数据，所以很快如将主键置于where列表中，mysql就能将该查询转换为一个常量 。

  • eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一性扫描

  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质也是一种索引访问，它返回所有匹配某个值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

  • Range: 只检索给定返回的行，使用一个索引来选择行，key列显示使用了那个索引，一般就是你的where语句中出现 between、<、> 、in等查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

  • Index: FUll Index Scan 与ALL区别为index类型只遍历索引树，这通常比All快，因为索引文件通常比数据文件小(也就是说虽然all和index都是读全表，但是index是从索引中读取的，而all是从硬盘中读的)

  • All: full Table scan 将遍历全表以找到匹配的行。

(4)、possible_keys:

　　显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则索引被列出，但不一定被查询实际使用。

(5)、key

　　查询实际使用到的索引。查询中如果使用了覆盖索引，则该索引和查询的select字段重叠。

(6)、key_len

　　表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。

　　key_len计算：影响索引长度因素：索引列为字符串类型的情况

    • 列长度：
    • 列是否为空： NULL(+1)，NOT NULL(+0)
    • 字符集： 如 utf8mb4=4,utf8=3,gbk=2,latin1=1
    • 列类型为字符： 如 varchar(+2), char(+0)
    • 计算公式：key_len=(表字符集长度) * 列长度 + 1(null) + 2(变长列)
# charact_set=utf8, char(50), null
key_len=(3*50+1+0)=151
 
# charact_set=utf8, char(50), not null
key_len=(3*50+0+0)=150
 
# charact_set=utf8, varchar(50), null
key_len=(3*50+1+2)=153

 

 

(7)、ref

　　显示索引的哪一列被使用，如果可能，是一个常数，哪些列或常量被用于查找索引上的值。

(8)、rows

　　显示MySQL在表中进行查询时必须检查的行数。根据表统计信息及索引选用的情况，大致估算出找到所需的记录所需读取的行数。

(9)、Extra

　　表示MySQL在处理查询时的详细信息

    • Using filesort :mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。(文件内排序)

    • Using temporary：使用临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序order by和分组查询 group by和分组查询

    • using index:表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where 表明索引被用来执行索引键值的查找如果没有同时出现using where 表明索引用来读取数据而非执行查找动作

    • using where:表示使用where过滤

    • using join buffer ：使用连接缓存

    • impossible where ：where子句的值总是false，不能用来获取任何元组

    • select tables optized away:在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作或者对象MyISAM存储引擎优化COUNT(*)操作。不必等到执行阶段在运行计算查询执行计划生成的阶段即可完成优化

    • distinct:优化distinct操作。在找到第一个匹配的元组后即停止找同样值的操作

　　覆盖索引：

　　理解方式：就是select的数据列只用从索引中就能取得，不比读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所创建的索引覆盖。

DESCRIBE SELECT select_options和EXPLAIN用法一样

 

1.2、简单case

  1. 第一行(执行顺序4)：id列为1，表示是union里的第一个select,select_type列的primary表示该查询为外层查询，table列被标记<derived3>,表示查询结果来自一张衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。[select d1.name]

  2. 第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分，因查询包含在from中，所以为derived[select id,name from t1 where other_column=“”]

  3. 第三行（执行顺序3）：select列表中的子查询select_type为subquery,为整个查询中的第二select[select id from t3]

  4. 第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select,最先执行[select name，id from t2]

  5. 第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1.4>表示用第一个和第四个select的结果进行union操作。[两个union操作]

二、索引使用原则

1.全值匹配
2.最佳左前缀法则：最左边的索引字段不能丢失
3.不在索引列上左任何操作，
4.存储引擎不能使用索引中范围条件右边的列（返回之后全失效）
5.尽量使用覆盖索引，减少select *
6.mysql在使用不等于（!=Z）的时候无法使用索引，导致全表扫描
7.is null,is not null 也无法使用索引
8.like以统配符开头（'%abc'）索引失效，全表扫描
9.字符串不加单引号，索引失效
10.少用or，用它连接时索引失效。

总结：假设index(a,b,c)

Where语句索引是否被使用
Where a=3 Y,使用到a
Where a=3 and b =5 Y,使用到a,b
Where a=3 and b =5 and c=4 Y,使用到a,b,c
Where b =3 or b =3 and c=4 N
Where a=3 and c =5 使用到a,但是c不能使用，b中间中断
Where a=3 and b >5 and c=4 使用到a和b， c不能使用在范围之后，b中断了
Where a=3 and b like ‘kk%’ and c=4 使用了 a,b ,c
Where a=3 and b like ‘%kk’ and c=4 Y.只用到a
Where a=3 and b like ‘%kk%’ and c=4 Y.只用到a
Where a=3 and b like ‘k%kk%’ and c=4 Y.只用到abc

2.1、使用索引查询

　　索引并不起作用场景。

    1. 使用LIKE关键字的查询语句 如果匹配字符串的第一个字符为“%”,索引不会起作用

    2. 使用多列索引的查询语句 MySQL可以创建多字段的索引。一个索引可以包括16个字段，对于多列索引，只有查询条件中使用这些字段中第一个字段时，索引才会被使用。

    3. 适用OR关键字的查询语句 OR前后的两个条件中的列都是索引时，查询中才使用索引。否则使用不到索引

2.2、优化子查询

　　子查询虽然可以使查询语句灵活，但是执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，因此，子查询的速度会受到一定的影响。 连接查询不需要建立临时表，其速度比子查询更快，如果查询中使用索引的话，性能会更好。

三、索引优化

3.1、索引分析

(1)、单表

-- 建表SQL
CREATE TABLE IF NOT EXISTS article (
  id INT (10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  author_id INT (10) UNSIGNED NOT NULL,
  category_id INT (10) UNSIGNED NOT NULL,
  views INT (10) UNSIGNED NOT NULL,
  comments INT (10) UNSIGNED NOT NULL,
  title VARBINARY (225) NOT NULL,
  contene TEXT NOT NULL
)

INSERT INTO article(author_id ,category_id ,views ,comments ,title,contene  ) 
VALUES (1,1,1,1,'1','1'),(2,2,2,2,'2','2'),(3,3,3,3,'3','3');
SELECT * FROM article
-- 查询category_id等于1 且comments大于1的情况下，views最多的author_id
EXPLAIN SELECT id,author_id FROM article a WHERE a.category_id =1 AND a.comments >1 ORDER BY views DESC LIMIT 1;

　　结论：type是All，进行了去全表扫描，Extra还是Using filesort,进行是文件内排序，影响性能。所以需要优化

开始优化

-- 开始优化
-- 1.1、新建索引+删除索引
-- 建立索引的方式一：
ALTER TABLE article ADD INDEX idx_article_cvv(category_id,comments,views);
-- 建立索引的方式二：
CREATE INDEX idx_article_cvv ON article(category_id,comments,views);
-- 删除索引
DROP INDEX idx_article_cvv ON article

-- 1.2、第2次EXPLAIN
EXPLAIN SELECT id,author_id FROM article WHERE category_id =1 AND comments>1 ORDER BY views DESC LIMIT 1;
EXPLAIN SELECT id,author_id FROM article WHERE category_id =1 AND comments=1 ORDER BY views DESC LIMIT 1;
-- 结论：type编程range,这个是可以忍受的，但是extra里使用Using filesort仍是无法接受的。
-- 但是我们建立索引，为啥索引没有用呢？
-- 这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,
-- 如果遇到相同的comments则再排序views，当comments字段处于联合索引的中间位置时
-- 因为comments>1条件是一个范围值(range),MySQL无法利用索引再对后面的vies部分进行检索，即range类型查询字段后面的索引失效。

--  1.3、删除第一次建立的索引
DROP INDEX idx_article_cvv ON article;

-- 1.4、第二次新建索引
CREATE INDEX idx_article_cv ON article(category_id,views);

-- 1.5、第3次EXPLAIN
EXPLAIN SELECT id,author_id FROM article WHERE category_id =1 AND comments>1 ORDER BY views DESC LIMIT 1;
-- 结论：可以看到type编程了ref,Extra中的Using filesort也消失了，结果非常理想。
DROP INDEX idx_article_cv ON article; 

(2)、两表

-- 建表
CREATE TABLE IF NOT EXISTS class(
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS book(
bookid INT (10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(bookid)
)

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 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));
-- 下面开始explain分析
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card
-- 结论：type 有ALL

-- 添加索引优化
ALTER TABLE book ADD INDEX idx_book_c(card);

-- 第二次explain
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.`card`;
-- 可以看到第二行的type变为了ref,rows优化比较明显。这是由左连接特性决定的。
-- left join 条件用于确认如何从右表搜索行，左表一定都有，所以右边是我们的关键点，一定需要建立索引。

-- 删除就索引，新建索引，第3次explain
DROP INDEX idx_book_c ON book;
ALTER TABLE class ADD INDEX idx_class_c(card);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.`card`;

-- 然后来看一个右查询
-- 优化比较明显，这是因为RIGHT JOIN 条件用于确定如何从左表搜索行，右边一定都有，所以左边是我们的关键点，一定要建立索引，
EXPLAIN    SELECT * FROM class RIGHT JOIN book ON class.`card`=book.`card`
DROP INDEX idx_class_c(card);
ALTER TABLE book ADD INDEX idx_book_c(card);
-- 右连接，基本没有变化
EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.`card` = book.`card`;

(3)、三表

-- 建表SQL
CREATE TABLE IF NOT EXISTS phone(
phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (phoneid)
)ENGINE=INNODB;

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));
ALTER TABLE phone ADD INDEX idx_p_c(card);
ALTER TABLE book ADD INDEX idx_b_c(card);

EXPLAIN    SELECT * FROM class LEFT JOIN  book ON class.`card` = book.`card` LEFT JOIN phone ON book.`card` = phone.`card`;
-- 后2行的type都是ref 且总是rows优化很好，效果不错，因此索引最好设置在需要经常查询的字段中。

结论：

  •  join 语句优化，尽可能减少join语句总的NestedLoop（嵌套循环）的循环总次数：“永远用小结果驱动大的结果集”。优先优化NestedLoop的内层循环：
  • 保证Join语句中被驱动表上Join条件字段已经被索引
  • 当无法保证被驱动表的Join条件字段被索引且资源充足的条件下，不要太吝啬JoinBuffer的设置。

3.2、索引失效

-- 建表SQL
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR(24) NOT NULL DEFAULT '' COMMENT '职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表';
-- 插入数据
INSERT INTO staffs(NAME,age,pos,add_time) VALUES ('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES ('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES ('2000',23,'dev',NOW());

SELECT * FROM staffs;
-- 创建索引
ALTER TABLE staffs ADD INDEX idx_staffs_nameagepos(NAME,age,pos);

①、全值匹配（索引列包含了where后跟的条件列）

EXPLAIN SELECT * FROM staffs WHERE NAME ='july';

EXPLAIN SELECT * FROM staffs WHERE NAME ='july' AND age = 25;

EXPLAIN SELECT * FROM staffs WHERE NAME ='july' AND age = 25 AND pos ='dev';

②、最佳左前缀法则

　　如过索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT * FROM staffs WHERE  age = 25 AND pos ='dev';
EXPLAIN SELECT * FROM staffs WHERE  pos ='dev';

③、不在索引列上做任何操

　　不在索引列上做任何操作（计算、函数、自动or手动类型转换），会导致索引失效，进而进行全表扫描。

④、存储引擎不能使用索引中范围条件右边的列（范围之后全失效）

EXPLAIN SELECT * FROM staffs WHERE NAME ='z4';
EXPLAIN SELECT * FROM staffs WHERE NAME ='z4' AND age = 22;
EXPLAIN SELECT * FROM staffs WHERE NAME ='z4' AND age = 22 AND pos ='manager';
EXPLAIN SELECT * FROM staffs WHERE NAME ='z4' AND age >11 AND pos ='manager';

⑤、尽量使用覆盖索引

　　尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

EXPLAIN SELECT * FROM staffs WHERE NAME ='july' AND age = 25 AND pos ='dev';
EXPLAIN SELECT NAME,age,pos FROM staffs WHERE NAME ='july' AND age > 25 AND pos ='dev';
EXPLAIN SELECT NAME,age,pos FROM staffs WHERE NAME ='july' AND age = 25;
EXPLAIN SELECT NAME FROM staffs WHERE NAME ='july' AND age = 25;

⑥、mysql在使用不等于（!= ）的时候无法使用索引，导致全表扫描

EXPLAIN SELECT * FROM staffs WHERE NAME ='july';
EXPLAIN SELECT * FROM staffs WHERE NAME !='july';
EXPLAIN SELECT * FROM staffs WHERE NAME <>'july';

　　　　is null,is not null 也无法使用索引、字符串不加单引号，索引失效

⑦、like已统配符开头（’%abc’）所有失效，全表扫描

EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%july%';
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%july';
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE 'july%';　　

　　解决like '%字符串%' 索引失效？

CREATE TABLE tbl_user(
id INT(11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT NULL,
age INT(11) DEFAULT NULL,
email VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(NAME,age,email) VALUES('1aa1',21,'b@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('2aa2',222,'a@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('3aa3',256,'c@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('4aa4',21,'d@163.com');

　　使用覆盖索引，索引列大于等于select后的查询列

⑧、少用or，用它连接时索引失效。

EXPLAIN SELECT * FROM staffs WHERE NAME LIKE 'july' OR NAME ='z3';
SELECT * FROM staffs WHERE NAME LIKE 'july' OR NAME ='z3';

　　建议：

    • 对于单列索引，尽量选择针对当前query过滤性更好的索引。

    • 在选择组合索引的时候，当前Query中的过滤性最好的字段在索引字段顺序中，位置越靠前越好。

    • 在选择组合索引的时候，尽可能选择可以能够包含query中的where子句中会更多字段的索引。

    • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

四、查询截取分析

4.1、查询优化

(1)、小表驱动大表

　　优化原则：小表驱动大表，即小的数据集驱动打的数据集。

SELECT * FROM A WHERE id IN (SELECT id FROM B);
-- 等价于
FOR SELECT id FROM B;
FOR SELECT * FROM A WHERE A.id = B.id;

　　当B表的数据集小于A表的数据集时，用in 由于exists

SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id)
-- 等价于
FOR SELECT * FROM A;
FOR SELECT * FROM B WHERE B.id = A.id;

　　当A表的数据集小于B表的数据集时，用exists优于in。

　　注意：A B表的Id应该建立索引。

  • EXISTS
SELECT  .....FROM   table    WHRER  EXISTS  (subquery)

　　该语法可以理解为：将主查询的数据，放到子查询做条件验证，根据验证结果（TRUR|FALSE）来决定主查询的数据结果是否得以保留。　　

  • 提示
    • EXISTS(subquery)只返回TRUE或者FALSE，因此子查询中的SELECT * 也可以是SELECT 1或者其他，官方说明是实际执行时会忽略SELECT 清单，因此没有区别。
    • EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验确定是否有效率问题。
    • EXISTS子查往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析。

(2)、order by 关键字优化

　　order by字句，尽可能使用index方式排序，避免使用FileSort方法是排序。

-- 建表SQL
CREATE TABLE tblA(
-- id int primary key not null auto_increment,
age INT,
birth TIMESTAMP NOT NULL
);

INSERT INTO tblA(age ,birth) VALUES(22,NOW());
INSERT INTO tblA(age ,birth) VALUES(23,NOW());
INSERT INTO tblA(age ,birth) VALUES(24,NOW());
CREATE INDEX idx_A_ageBirth ON tblA(age,birth);
SELECT * FROM tblA;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age,birth;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth,age;

EXPLAIN SELECT * FROM tblA  ORDER BY birth;
EXPLAIN SELECT * FROM tblA WHERE birth > '2020-12-16 00:00:00' ORDER BY birth;
EXPLAIN SELECT * FROM tblA WHERE birth > '2020-12-16 00:00:00' ORDER BY age;
EXPLAIN SELECT * FROM tblA  ORDER BY age ASC,birth DESC;

 

 

　①、MySQL支持两种方式的排序，FileSort和index

    • index效率高，它指mysql扫描索引本身完成排序

    • FileSort方式效率较低。

　②、Order By满足两种情况，会使用Index方式排序

    • Order By 语句使用索引最左前列
    • 使用where 字句与Order By字句条件列组合满足索引最左前列

　③、尽可能在索引列上完成排序操作，遵照索引建在最佳左前缀，如果不在索引上，filesort有两种算法：mysql要启动双路排序和单路排序。

    • 双路排序：mysql 4.1之前是使用双路排序，字面就是两次扫描磁盘，最终得到数据,读取行指针和order by列，对他们进行排序，然后扫描已经拍好须的列表，按照列表中的值重新从列表中读取对应的数据输出

      从磁盘取排序字段，在buffer进行排序，在从磁盘取其他字段

      取一批数据，要对磁盘进行两次扫描，众所周知，I/O是很耗时的，所以在mysql4.1之后出现了第二种改进的算法——单路排序

    • 单路排序：从磁盘读取查询需要的所有列，按照order by 列在buffer 对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免二次读取数据，并且把随机IO变成顺序I/O,但是它会使用更多的空间，因为它把每一行都存在内存中。

　④、结论及延申出问题：单行优于多行，但是单行也有问题

    • 在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B 是把所有字段都取出，所以有能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排位再取sort_buffer容量大小，再排……..从而多次IO。

　　优化策略：增大sort_buffer_size 参数的设置 增大 max_length_sort_data参数的设置

　⑤、提高Order By的速度：

    1. Order By时select * 是一大忌，只Query需要的字段，这点非常重要，在这里的影响是：
      • 当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法--单路排序，否则用老算法--多路排序。
      • 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。
    2. 尝试提高sort_buffer_size
      • 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每一个进程的。　　
    3. 尝试提高max_length_for_sort_data
      • 提高这个参数，会增加用改进算法的概率，但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。　

　⑥、Order By优化总结

　　　为排序使用索引

　　　　　　MySql两种排序方式：文件排序或扫描有序索引排序。

　　　　　　MySql能为排序与查询使用相同的索引。

KEY a_b_c(a,b,c)
order by 能使用索引最左前缀
-ORDER BY a
-ORDER BY a,b
-ORDER BY a,b,c
-ORDER BY a DESC,b DESC,c DESC

如果WHERE使用索引的最左前缀定义为常量，则order by能使用索引
-WHERE a = const ORDER BY b,c
-WHERE a = const AND b = const ORDER BY c-WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
-ORDER BY a ASC,b DESC c DESC  /*排序不一致*/
-WHERE g = const ORDER BY b,c  /*丢失a索引*/
-WHERE a = const ORDER BY c    /*丢失b索引*/
-WHERE a = const ORDER BY a,d  /*d不是索引的一部分*/
-WHERE a in（...）ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

(3)、Group by 关键字优化

  • group by 实质是先排序后进行分组，要遵照索引建的最佳左前缀

  • 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。

  • where 高于having，能写在where限定的条件就不要去having限定了。

4.2、慢查询日志

(1)、简介

  • MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阙值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

  • 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒，我们就算慢SQL，希望能收集超过5秒的SQL，结合之前的explain进行全面分析。

(2)、具体操作

-- 查看是否开启
SHOW VARIABLES LIKE '%slow_query_log%'
# 开启
set global slow_query_log=1;
-- 使用上面命令开启慢查询日志只对当前数据库生效，如果MySQL重启就会失效。

(3)、案例

-- 查看当前多少秒算慢
show  variables like '%long_query_time%'；
-- 设置慢的阈值时间
set global long_query_time =3；
--为什么设置后看不出变化
-- 需要重新连接或者新开一个会话才能看到修改的值，
show variables like '%long_query_time%'
show global variables like '%long_query_time%'
--记录慢SQL并分析
[root@s1 ~]# cat /data/mysqldata/s1-slow.log 
/usr/local/jdy/mysql/bin/mysqld, Version: 5.7.22 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
/usr/local/jdy/mysql/bin/mysqld, Version: 5.7.22 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
-- 查询当前系统中有多少条慢查询记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

　　以上操作通过配置完成

# 指定慢查询超时时长(默认10秒)，超出此时长的属于慢查询
long_query_time=3
# 定义一般查询日志和慢查询日志的输出格式，默认为file
log_output=FILE  
# 也是是否启用慢查询日志，此变量和log_slow_queries修改一个另一个同时变化
slow_query_log=1
# 默认路径为库文件目录下主机名加上-slow.log
slow_query_log_file=/data/mysqldata/s1-slow.log

(4)、日志分析工具mysqldumpslow

　　查看mysqldumpslow的帮助信息

    • s:表示按何种方式排序

    • C:访问次数

    • l:锁定时间

    • r:返回记录

    • t:查询时间

    • al:平均锁定时间

    • ar:平均返回记录数

    • t:返回前面多少条数据

    • g:搭配正则表达式

# 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /data/mysqldata/s1-slow.log

# 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10/data/mysqldata/s1-slow.log

# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /data/mysqldata/s1-slow.log

# 另外建议在使用这些命令的时候结合|和more使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /data/mysqldata/s1-slow.log | more

4.3、show profile

　　是mysql 提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于mysql 的调优测试。官网：http://dec.mysql/com/doc/refman/5.5/en/show-profile.html。默认情况下关闭，并报存最近15此的运行结果

(1)、看看当前的MySQL版本是否支持

# 默认是关闭，使用前需要开启
SHOW VARIABLES LIKE 'profiling'

(2)、开启功能，默认关闭

# 开启
SET profiling=ON;

(3)、运行SQL

select * from emp,group by id%10 limit 150000;
select * from emp,group by id%20 limit 5;

(4)、查看结果，show profiles;

(5)、诊断SQL,

　　show profile cpu,block io for query 上一步前面的问题SQL数字号码

(6)、日常开发需要注意的结论

  •  converting HEAP to MyISAM： 查询结果太大，内存都不够用了
  •  Greating tmp table ：创建临时表

拷贝数据到临时表

　　用完再删除Coping to tmp table on disk ：把内存中的实例表复制到磁盘（危险！！！）locked

4.4、全局查询日志

  • 配置启用：在mysql的my.cnf中，设置如下
# 是否启用一般查询日志，为全局变量，必须在global上修改。
general_log=1
# 输出格式
log_output=FILE
# 默认是库文件路径下主机名加上.log
general_log_file=/data/mysqldata/hostname.log
  • 编码启用
-- 命令
SET GLOBAL general_log = 1;
SET GLOBAL log_output ='TABLE';

　　此后，你所编写的sql语句，将会记录到mysql库里的general_log 表，可以使用以下命令查看

SELECT * FROM mysql.`general_log`;　

　　永远不要在生产环境开启这个功能

五、优化数据库结构

5.1、将字段很多的表分解成多个表

　　对于字段多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新的表

5.2、增加中间表、增加冗余字段

　　对于需要经常联合查询的表，可以建立中间表以提高查询效率，通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询 。

5.3、优化插入记录的速度

　　插入数据时，影响插入速度的主要是索引、唯一性效验、一次插入记录条数等。

　　禁用索引：对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会影响插入记录的速度。我们可以在插入记录之前禁用索引， 数据插入完毕之后开启索引。

--  禁用索引的语法：
ALTER TABLE table_name DISABLE KEYS

--  重新开启索引的语法
ALTER TABLE table_name ENABLE KEYS;

　　对于空表批量导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后建立索引 。

　　禁用唯一性检查：插入数据时，MySQL会对插入的记录进行唯一性效验。这种唯一性校验也会降低插入记录的速度。

-- 禁用语法：
SET UNIQUE_CHECKS=0

-- 开启语法：
SET UNIQUE_CHECKS=1;

　　使用LOAD DATE INFILE 批量导入：

-- 对于InnoDB引擎的表：
-- 1.禁止唯一性检查
-- 2.禁止外键检查
        SET foreign_key_checks=0;
        SET foreign_key_checks=1;
-- 3.禁止自动提交
        SET autocommit =0;
        set autocommit=1;

5.4、分析表、检查表和优化表

  • 分析表主要是分析关键字的分布

  • 查检表主要是检查表是否存在错误

  • 优化表主要是消除删除或者更新造成的空间浪费

(1)、分析表

ANALYZE  [local|No_WRITE_TO_BINLOG]  TABLE  tab_name [,tab_name] ...

  • local:是关键字，No_WRITE_TO_BINLOG：关键字别名，二者都是执行过程中不写入二进制日志ANALYZE TABLE分析表的过程中，数据库系统会自动对表加一个只读锁。不能插入数据。

  • ANALYZE TABLE语句能够分析InnoDB、DBD和MyISAM类型的表 。

(2)、检查表

　　MySQL中使用CHECK TABLE语句来检查表。CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误，对于MyISAM类型的表，CHECK TABLE语句还有更新关键字统计数据。而且，CHECK TABLE也可以检查视图是否有错误。

-- 语法
CHECK TABLE tbl_name [,tab_name] ....[option]...

option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}

  • QUICK:不扫描行，不检查错误的连接。

  • FAST：只检查没有被正确关闭的表。

  • CHANGED：只检查上次检查后被更改的表和没有别正确关闭的表。

  • MEDIUM：扫描行，已验证被删除的连接是有效的。也可以计算各行的关键字效验和，并使用计算出的校验和验证这一点。

  • EXTENDED:对每行的所有关键字践行一个全面的关键字查找，这可以确保表示100%一致的。耗时长。

(3)、优化表

　　optimize TABLE语句来优化表，但是只能优化表中VARCHAR、BLOB或者TEXT类型的字段。

-- 语法：
OPTIMIZE  [LOCAL|No_WRITE_TO_BINLOG] TABLE tab_name [,tab_name] ...

六、优化MySQL服务器

　　一方面是对硬件的优化，另一方面是对MySQL服务器参数进行优化。

6.1、优化服务器硬件

  • 配置较大的内存。通过增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘I\O

  • 配置高速磁盘系统，以减少读盘的等待时间，提高响应速度

  • 合理分布磁盘I/O，把磁盘分散在多个设备上，以减少资源竞争，提高并行操作能力

  • 配置多处理器，MySQL是多线程的数据库。多处理器可同时执行多个线程

6.2、优化参数

  • key_buffer_size:表示索引缓冲区的大小，索引缓冲区所有的线程共享。增加索引缓冲区可以得到更好处理的索引

  • table_cache:表示同时打开表的个数

  • query_cache_size:表示查询缓冲区的大小，该参数需要query_cache_type配合使用当query_cache_type 值是0时，所有的查询都不使用查询缓冲区。但是query_cache_type=0并不会导致MySQL释放query_cache_size所配置的缓冲区内存。当query_cache_type=1时，所有的查询都将使用查询缓冲区，除非在查询语句中指定 SOL_NO_CACHE，如SELECT NO_SQL_CACHE * from table.当query_cache_type=2时，只有在查询语句中使用SQL_CACHE关键字，查询才会使用查询缓冲区，使用查询缓冲区可以提高查询速度，这种方式只适应于修改操作少且经常执行相同的查询操作的情况

  • sort_buffer_size：表示排序缓冲区的大小，这个值越大，进行拍新的速度越快

  • read_buffer_size：表示线程连续扫描时为扫描每个表分配的缓冲区大小

  • read_rnd_buffer_size：表示为每个线程保留的缓冲区大小。主要用于存储安特定存出顺序读取出来的记录

  • innodb_buffer_pool_size：表示innodb类型的表和索引的最大缓存。这个值越大，查询的速度就会越快。

  • max_connections：数据库最大连接数

  • InnoDB_flush_log_at_trx_commit:表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘，该参数有3个值：0、1、2。0：表示每隔一秒将数据写入日志文件并将日志文件写入磁盘。值为1时，表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘。值为2时，表示每次提交事务时将数据写入日志文件，每个1秒将日志文件写入磁盘，默认值为1

  • back_log:表示对到来的TCP/IP连接的侦听队列的大小

  • interactive_timeout:表示服务器在关闭连接前等待的秒数

  • thread_cache_size：表示可以复用的线程的数量。如果有很多新的线程，为了提高性能可以增大该参数的值。

  • `sort_buffer_size``：表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或者GROUP BY 操作的速度，默认值为2M。

怎么SQL调优

  • 观察，至少跑一天，看看生产的慢SQL情况

  • 开启慢查询日志，设置阙值，比如查过3秒的就是慢SQL,并将其抓取出来。

  • explain+慢SQL分析

  • show profile

  • 运维经理 或者DBA进行SQL数据库服务器的参数调优

总结

1.慢查询日志的开启并捕获

2.explain+慢SQL分析

3.show profile 查询SQL 在MYSQL服务器里面的执行细节和生命周期情况

4.SQL数据库服务器的参数调优

general_log
posted @ 2020-12-16 17:07  jingdy  阅读(243)  评论(0)  编辑  收藏  举报