MySQL优化

show status

执行频率查看。

语句：show [global|session] status

Com_select:执行select次数。

Com_insert:执行insert操作的次数。

Com_update:执行update操作的次数。

Com_delete：执行delete的次数。

InnoDB引擎统计：

Innodb_rows_read: SELECT查询返回行数。

Innodb_rows_inserted:INSERT插入行数。

Innodb_rows_deleted:DELETE删除行数。

Com_commit：事务提交次数

Com_rollback：事务回滚的次数

Connections：试图连接MySQL服务器的次数。

Uptime：服务工作时间。

Slow_queries：慢查询次数。

 

定位执行效率低的SQL

方式一：通过--log-slow-queries指定文件位置，long_query_time指定超时时间。

方式二：show processlist查看当前MySQL在进行的线程，包括线程的状态，是否锁表等。对方式一的弥补。

方式三：通过explain分析低效率SQL

explain

https://www.cnblogs.com/clphp/p/5403215.html

https://www.jianshu.com/p/ea3fc71fdc45

https://blog.csdn.net/z69183787/article/details/53393153

explain select * from person where id=1 \G;

select_type:表示select类型，常见的类型有SIMPLE（简单表）、PRIMARY(主查询，即外层查询)、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（自查询的第一个SELECT语句）等。

table：表示输出结果集的表。

type：访问类型(MySQL在表中找到所需行的方式)

ALL：全表扫描，MySQL遍历全表来找到匹配的行。

index：索引全扫描，MySQL便利整个索引来查询匹配的行。

range：索引范围扫描，常见<、<=、>、>=、between等操作。

ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独的记录 行。

eq_ref：类似ref，区别在于使用的索引时唯一索引，对于每个索引键值，表 中只有一条记录匹配，就是多表连接使用primary key或者unique index作为 关联条件。

const/system：单表中最多有一个匹配行，查询起来非常快速，所以这个匹 配行中的其它列的值可以被优化器在当前查询中当作常量来处理。

NULL：MySQL不用访问表或者索引，直接就能够得到结果。

ref_or_null：与ref类似，区别在于条件中包含对NULL的查询。

Index_merge：索引合并化。

unique_subquery：in的后面时查询非唯一索引字段的子查询。

index_subquery：与unique_subquery类似，区别在与in的后面时查询非唯 一索引字段的子查询。

possible_keys：表示查询时可能使用的索引。

key：表示实际使用过的索引。

key_len：使用到索引字段的长度。

rows：扫描行的数量。

Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

 

explain结合show warnings

可以查看是否有警告信息等。

explain  select count(*) from person where id>1 \G;

show warnings\G

show profile

查看当前MySQL是否支持profile

select @@have_profiling;

查看profile打开状态

select @@profiling;

打开关闭profile

打开set @@profiling=1;

关闭set @@profiling=0;

通过show profiles查看指定SQL的Query ID

show profiles;

show profile for query  id;

例如：

show profiles;

show profile for query 5;

Sending data状态表示访问数据行并把结果返回给客服端（两个步骤）。

因为在Sending data状态下，MySQL需要做大量的磁盘读取操作，所以经常是整个查询最耗时的状态。

 

MySQL支持选择all、cpu、block io、context switch、page faults等明细来查看耗时情况。

例如：

Cpu：

show profile cpu for query 5;

trace分析优化器

打开trace，设置格式为json

SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;

设置trace最大使用内存

SET OPTIMIZER_TRACE_MAX_SIZE=1000000;

查看跟踪文件

select * from information_schema.optimizer_trace;

索引

索引分类

B-Tree索引：最常见索引，B树索引。

HASH索引：只有Memory引擎支持。

R-Tree索引：空间索引，是MyISAM特殊索引，主要用于地理空间数据类型。

FULL-text索引:全文索引，也是MyISAM特殊索引，主要用于全文索引，InnoDB从5.6开始提供支持。

 

优势分析：HASH索引适用于key-value查询，通过HASH索引要比通过B-Tree索引查询更迅速；HASH索引不适用范围查询，比如<、>、<=、>=等。

Memory引擎只有“=”条件下才会使用索引。

索引管理

创建索引

CREATE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD INDEX index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY
KEY(id) , INDEX index_name (title(5)))

查看索引
SHOW INDEX FROM [table_name]
SHOW KEYS FROM [table_name] # 只在MySQL 中可以使用keys 关键字。

删除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

场景分析

1 全值匹配

explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343

explain输出结果中字段type的值为const，表示常量；字段key为idx_rental_date，表示优化器选择索引idx_rental_date选择扫描

2.范围匹配

explain select * from rental where customer_id>=373 and customer_id<400\G

类型type为range说明优化器选择范围查询，索引key为idx_fk_customer_id说明优化器选择索引idx_fk_customer_id来加速访问，Extra为Using index condition

https://www.cnblogs.com/wy123/p/7366486.html

3.最左前缀匹配

仅仅使用最左边列进行查找。

例如：col1+col2+col3字段的联合索引能够被包含col1、(col1+col2)、(col1+col2+col3)的等值查询利用到，可是不能够col2、(col2+col3)的等值查询利用到;

SQL例子：

创建索引：

alter table payment add index idx_payment_date(payment_date,amount,last_update);

 

情况一（当使用第一列和第三列时）

explain select * from payment where payment_date='2006-02-14 15:16:03' and last_update='2006-02-15 22:22:32'\G

这时会使用索引idx_payment_date进行查询优化。

情况二（当使用第二列和第三列时）

explain select * from payment where amount=3.98 and last_update='2006-02-15 22:22:32'\G

这时不会使用索引idx_payment_date进行查询。

1.仅仅对索引进行查询

查询的列都在索引中，查询的效率更高。

例如：

explain select last_update from payment where payment_date='2006-02-14 15:16:03' and amount=3.98\G

Extra变成了Using index，也就意味着，现在直接访问索引就足够获取到所需要的数据，不需要通过索引回表，Using index也就是平常说的覆盖索引扫描。之访问必须访问的数据。只访问必须访问的数据，在一般情况下，减少不必要的数据访问能够提升效率。

2.匹配列前缀

仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。

例如：

create index idx_title_desc_part on film_text(title(10),description(20));

explain select title from film_text where title like 'AFRICAN%'\G

Extra值为Using where表示优化器需要索引回表查询数据。

3.能够实现索引匹配部分精确而其他部分进行范围匹配

例：

explain select inventory_id from rental where rental_date='2016-02-14 15:15:15' and customer_id>=300 and customer_id<=400\G

show index from rental\G

因为rental_date和customer_id涉及到的索引有idx_fk_customer_id,idx_rental_date，所以possible_keys: idx_fk_customer_id,idx_rental_date

Key为idx_rental_date表示优化器选择索引idx_rental_date帮助加速查询，因为只查询索引字段inventory_id，所以 Extra部分Using index，表示查询使用了覆盖索引扫描。

（7）用or分隔开的条件，如果or前面中的列有索引，后面没有，那么设计的索引都不会被调用。

查看索引使用情况

show status like 'Handler_read%';

Handler_read_key：如果索引正在工作，Handler_read_key值很高。这个值代表一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

Handler_read_rnd_next：值高意味查询运行低效，此时应该建立索引补救。这个值表示数据文件中读下一行的请求书。如果正进行大量的表扫描，Handler_read_rnd_next的值较高，则通常说明表索引不正确或写入的查询没有利用索引。

分析表

analyze table payment

检查表

 check table payment;

定期优化表

optimize table payment;

Optimize table 命令可以将表的空间碎片进行合并，可以消除由于删除或者更新造成的空间浪费，但optimize table命令只对MyISAM、BDB和InnoDB表起作用。

对于InnoDB引擎表来说，通过设置innodb_file_per_table参数，设置InnoDB为独立表空间模式，这样每个数据库的每个表都会生成独立的ibd文件，用于存储表的数据和索引，这样可以一定程度减轻InnoDB表的空间回收问题。另外再删除大量数据后，InnoDB表可以通过alter table 但是不修改引擎的方式来回收不用的空间，alter table payment engine=innodb;

 

注意：ANALYZE、CHECK、OPTIMIZE、ALTER TABLE执行期间将对表进行锁定，因此一定要注意要在数据不繁忙的时候执行相关的操作。

 

常用SQL优化

避免全表扫描

对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引。

避免判断null 值

应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。

select id from t where num is null
可以在num 上设置默认值0，确保表中num 列没有null 值：
select id from t where num=0

避免不等值判断

应尽量避免在where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

大数据量添加

MyISAM存储引擎：

ALTER TABLE t DISABLE KEYS;

load data infile file into table t;

ALTER TABLE t ENABLE KEYS;

ENABLE KEYS和DISABLE KEYS 用来打开和关闭MyISAM表非唯一索引的更新。

 

InnoDB存储引擎：

1. 因为InnoDB类型的表按照主键顺序保存，所以将导入数据按照主键顺序排列，可以有效地提高导入数据的效率。
2. 导入数据之前执行SET UNIQUE_CHECKS=0,关闭唯一性体检，结束后SET UNIQUE_CHECKS=1,恢复唯一性体验，可以提高导入效率。
3. 如果应用自动提交，导入之前SET AUTOCOMMIT=0关闭自动提交,导入之后SET AUTOCOMMIT=1打开自动提交。

Insert语句优化

单客户端插入多行尽量使用

INSERT INTO table_name values(1,2),(1,3),(1,4)...

多客服端插入使用 INSERT DELAYED ，DELAYED 让INSERT 马上执行，但数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快的多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完成后才进行插入。

如果进行批量插入，MyISAM可以通过增加bulk_insert_buffer_size 变量值的方法来提高速度。

当从一个文本文件装载一个表时，使用LOAD DATA INFILE. 这通常比使用很多的INSERT 语句快20 倍。

Order by 优化

1 通过有序索引顺序扫描直接返回有序数据，explain分析查询时，显示Using Index，不需额外排序，操作效率高。

explain select customer_id from customer order by store_id\G

2 通过对返回数据进行排序，通常说Filesort排序，因此不是通过索引直接返回排序结果的排序都叫Filesort排序。Filesort并不代表通过磁盘文件进行排序，而只是说名进行了一个排序操作，至于排序操作是否使用了磁盘文件或零食表等，取决MySQL服务器对排序参数的设置和需要排序数据的大小。

 explain select * from customer order by store_id\G  

3 返回的时复合索引内容，并且排序的时复合索引的列

alter table customer add index idx_storeid_email(store_id,email);

explain select store_id,email,customer_id from customer order by email\G

Filesort是通过相应的排序算法，将取得数据在sort_buffer_size系统变量设置的内存排序区中进行排序，若内存装载不下，它会将磁盘的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。sort_buffer_size设置的排序区是每个线程独占的，所以同一个时刻，MySQL中存在多个sort buffer排序区。

 

优化排序：尽量减少额外的排序，通过索引直接返回有序数据。

 

WHERE条件和ORDER BY 使用相同的索引，并且ORDER BY 的排序和索引顺序相同，并且ORDER BY 的字段都是升序或者都是降序。否则肯定需要额外的排序操作，这样就会出现Filesort。

例如：

explain select store_id,email,customer_id from customer where store_id=1 order by email desc\G

只在idx_storeid_email完成过滤和排序操作.

Group by 优化

默认情况下，MySQL对所有GROUP BY col1,col2,...的字段进行排序。这与查询中指定ORDER BY col1,col2,类实。因此，如果显示包括一个包含相同列的ORDER BY子句，则对MySQL的实际执行性能没有什么影响。

如果查包括GROUP BY但想要避免排序结果的消耗，则可以指定ORDER BY NULL 进制排序。

select p.payment_date,sum(p.amount) from (select * from payment limit 101,10) p group by p.payment_date;

select p.payment_date,sum(p.amount) from (select * from payment limit 101,10) p group by p.payment_date order by null;  

嵌套查询优化

MySQL 4.1开始支持SQL的子查询，例如上面group例子。

explain select * from customer where customer_id not in (select customer_id from payment)\G

 explain select * from customer a left join payment b on a.customer_id=b.customer_id where b.customer_id is null\G

从执行计划中可以看出查询关联类型从index_subquery调整位ref。

连接（JOIN）之所以更有效率一些，是因为MySQL不需要在内存中创建临时表是创建完成这个逻辑上需要两个步骤的查询工作。

MySQL OR优化

对于含有OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引；

复合索引列用不到索引。

两个独立索引做OR查询：

explain select * from traderecored where payment_id=2 or customer_id=3 \G

但是在建有复合键的索引上做or操作却用不到索引。

慎用in 和not in 逻辑

in 和not in 也要慎用，否则会导致全表扫描，如：
select id from t1 where num in(select id from t2 where id > 10)
此时外层查询会全表扫描，不使用索引。可以修改为：
select id from t1,(select id from t1 where id > 10) t2 where t1.id = t2.id
此时索引被使用，可以明显提升查询效率。

注意模糊查询

下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
模糊查询如果是必要条件时，可以使用select id from t where name like 'abc%'来实现模
糊查询，此时索引将被使用。如果头匹配是必要逻辑，建议使用全文搜索引擎（Elastic search、
Lucene、Solr 等）。

避免查询条件中字段计算

应尽量避免在where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进
行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2

避免查询条件中对字段进行函数操作

应尽量避免在where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行
全表扫描。如：
select id from t where substring(name,1,3)='abc'--name 以abc 开头的id
应改为:
select id from t where name like 'abc%'

WHERE 子句“=”左边注意点

不要在where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

不要定义无异议的查询

不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)

exists

很多时候用exists 代替in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)

分页优化

1 在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他内容。

 show index from film\G

 explain select film_id,description from film order by title limit 50,5\G

通过让MySQL扫描尽可能少的页面来提高分页效率优化后

 explain select a.film_id,a.description from film a inner join(select film_id from film order by title limit 50,5) b on a.film_id =b.film_id\G

 

2 把LIMIT查询转换成某个位置的查询。

下面这种方式会出现丢失部分记录，不适用这种方式进行优化

explain select * from payment order by rental_id desc limit 410,10\G

 explain select * from payment where rental_id<15640 order by rental_id desc limit 10\G

查询索引选择

1 USE INDEX

添加USE INDEX来提供希望MySQL去参考的索引列表

select count(*) from rental use index(idx_rental_date);

2 IGNORE INDEX

让MySQL忽略一个或者多个索引。

select count(*) from rental ignore index(idx_rental_date);

3 FORCE INDEX

强制MySQL使用一个特定的索引。

select count(*) from rental force index(idx_rental_date);

正则表达式运用

数据类型优化

表需要使用合众数据类型是需要根据应用来判断的。虽然应用设计之初需要考虑字段的长度留有一定的冗余，但是不允许让很多字段都留下大量的冗余，这样即浪费磁盘存储空间，同时在应用程序操作时也浪费物理内存。

PROCEDURE ANALYSE()对当前应用的表进行分析，该函数可以对数据库中的列的数据类型提出优化建议，可以根据应用的实际情况考虑是否实施优化。

select * from traderecored procedure analyse()\G

输出的每一列信息都会对数据表中的列的数据类型提出建议。

select * from traderecored procedure analyse(16,256)；

表示不对包含的值多于16个项或者256个字节的ENUM类型提出建议。

索引优化

1 索引不会包含有NULL 值的列

只要列中包含有NULL 值都将不会被包含在索引中，组合索引中只要有一列含有NULL值，那么这一列对于此组合索引就是无效的。

2 使用短索引

短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O 操作。
CREATE INDEX index_name ON table_name (column(length))

3 索引列排序

MySQL 查询只使用一个索引，因此如果where 子句中已经使用了索引的话，那么order by 中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4 like 语句操作

like “%aaa%”不会使用索引，而like “aaa%”可以使用索引。

5 不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描， 因此我们可以改成： select * from users whereadddate<’2007-01-01′