mysql count 优化索引_MySQL学习之SQL优化(索引优化)

在where从句，group by从句，order by从句，on从句中出现的列

索引字段越小越好

离散度大的列放到联合索引的前面

explain select * from payment where staff_id=2 and customer_id=584;--思考: index(staff_id,customer_id)好?还是index(customer_id,staff_id)好?

select count(distinct customer_id),count(distinct staff_id) frompayment;+-----------------------------+--------------------------+

| count(distinct customer_id) | count(distinct staff_id) |

+-----------------------------+--------------------------+

| 599 | 2 |

+-----------------------------+--------------------------+--由于customer_id的离散度更大(重复率小,可选择性更大)，所以应该使用 index(customer_id,staff_id)

二、索引的维护

1、重复及冗余索引

--冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引。如下:key(name,id)就是一 个冗余索引--可以删除冗余索引，达到优化效果。

create tabletest(

idint not null primary key,

namevarchar(10) not null,key(name,id)

)engine=innodb;

2、检查重复及冗余索引

使用pt-duplicate-key-checker工具检查重复及冗余索引，安装如下：

wget http://www.percona.com/downloads/percona-toolkit/2.2.4/percona-toolkit-2.2.4.tar.gz

tar -xzvf percona-toolkit-2.2.4.tar.gz

cd percona-toolkit-2.2.4

perlMakefile.PLmake && make install#如果报错(Can't locate Time/HiRes.pm in @INC (@INC contains....)

yum -y install perl-Time-HiRes

#如果报错: Cannot connect to MySQL because the Perl DBD::mysql module is not installed or not found.yum -y install perl-DBD-mysql

使用：

pt-duplicate-key-checker -h127.0.0.1 -uroot -proot

#指定数据库

pt-duplicate-key-checker -h127.0.0.1 -uroot -proot -dsakila

3、删除不用索引

在mysql中可以通过慢查日志配合pt-index-usage工具来进行索引使用情况分析。

pt-index-usage -h127.0.0.1 -uroot -proot /data/mysql/hive-slow.log

三、SQL优化(慢查询)

1、慢查询

如何发现有问题的SQL?我们可以 使用Mysql慢查询日志对有效率问题的SQL进行监控。

--查看包含log的参数

show variables like '%log%';--查看慢查询日志是否开启

show variables like 'slow_query_log';--查看慢查询日志存储位置

show variables like 'slow_query_log_file';--开启慢查询日志

set global slow_query_log=on;--指定慢查询日志存储位置

set global show_query_log_file='/data/mysql/hive-slow.log';--记录没有使用索引的sql 开启慢查询日志

set global log_queries_not_using_indexes=on;--查看慢查询设置的时间 超过此时间记录到慢查询日志中 show variables like 'long_query_time'; #记录查询超过1s的sql

set global long_query_time=1;

如：

测试：

--执行sql

select sleep(3);

#查看日志[root@hive ~]# tail -f /data/mysql/hive-slow.log

/root/mysql/bin/mysqld, Version: 5.6.38 (MySQL Community Server (GPL)). started with:

Tcp port:3306 Unix socket: /tmp/mysql.sock

Time Id Command Argument

# Time:190708 9:38:47#User@Host: root[root] @ [192.168.3.36] Id: 40 //执行sql的主机信息

# Query_time:3.000991 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 //sql的执行信息SET timestamp=1562593127; //sql执行时间select sleep(3); //sql的内容

2、慢查询日志分析工具 mysqldumpslow

安装完MySQL后，默认就带了mysqldumpslow，mysql官方提供的一个常用工具。

--查看参数列表

mysqldumpslow -h--分析慢查询日志中前三条比较慢的sql

mysqldumpslow -t 3 /data/mysql/hive-slow.log | more

输出效果：

[root@hive ~]# mysqldumpslow -t 3 /data/mysql/hive-slow.log | moreReading mysql slow query log from/data/mysql/hive-slow.log

Died at/root/mysql/bin/mysqldumpslow line 161, <> chunk 1.

Count:1 Time=3.00s (3s) Lock=0.00s (0s) Rows=1.0 (1),

root[root]@[192.168.3.36]select sleep(N)

3、慢查询日志分析工具pt-query-digest

分析结果比mysqldumpslow更详细全面，它可以分析binlog、General log、slowlog， 安装 ：

wget percona.com/get/pt-query-digestchmod u+x pt-query-digestmv /root/pt-query-digest /usr/bin/#如果出现Can't locate Time/HiRes.pm in @INC 错误

yum -y install perl-Time-HiRes

#查看参数列表

pt-query-digest --help

[root@hive bin]# pt-query-digest /data/mysql/hive-slow.log | more# 50ms usertime, 50ms system time, 20.09M rss, 165.33Mvsz

# Currentdate: Mon Jul 8 10:35:26 2019# Hostname: hive

# Files:/data/mysql/hive-slow.log

# Overall:1 total, 1 unique, 0QPS, 0x concurrency ______________________

# Time range: all events occurred at2019-07-08 09:38:47# Attribute

#============# Exectime#Locktime0000000 #Rowssent 1111101 #Rowsexamine 0000000 # Query size 15 15 15 15 15 0 15total min max avg95%stddev median======= ======= ======= ======= ======= ======= =======3s 3s 3s 3s 3s03s

# Profile

# Rank Query ID Responsetime Calls R/Call V/M

#==== ================================== ============= ===== ====== =====#1 0x59A74D08D407B5EDF9A57DD5A41825CA 3.0010 100.0% 1 3.0010 0.00SELECT

# Query1: 0 QPS, 0x concurrency, ID 0x59A74D08D407B5EDF9A57DD5A41825CA at byte

0# This item is includedin the report because it matches --limit.

# Scores: V/M = 0.00# Time range: all events occurred at2019-07-08 09:38:47# Attribute pct total min max avg95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 100 1# Exectime 1003s 3s

#Locktime0 0 0#Rowssent100 1 1#Rowsexamine00 0# Query size

# String:

# Hosts192.168.3.36# Users root

# Query_time distribution

# 1us

# 10us

# 100us

# 1ms

# 10ms

# 100ms

# 1s ################################################################

# 10s+# EXPLAIN/*!50100 PARTITIONS*/

select sleep(3)\G

输出分为三部分：

显示除了日志的时间范围，以及总的sql数量和不同的sql数量

Response Time:响应时间占比 Calls:sql执行次数

sql的具体日志

如何通过慢查询日志发现有问题的SQL?

查询次数多且每次查询占用时间长的SQL：通常为pt-query-digest分析的前几个查询

IO大的SQL(数据库主要瓶颈出现在IO层次)：注意pt-query-digest分析中的Rows examine项

未命中索引的SQL：注意pt-query-digest分析中的Rows examine和Rows Sent的对比

四、常见优化实例

1、Count()和Max()的优化

--查询最后支付时间--优化max()函数

explain select max(payment_date) from payment;

--给payment_date建立索引(覆盖索引)

create index idx_paydate on payment(payment_date);

--删除索引

drop index idx_release_year onpayment;--显示索引

show index frompayment;--在一条SQL中同时查出2006年和2007年电影的数量--优化Count()函数--count('任意内容')都会统计出所有记录数，因为count只有在遇见null时不计数，即 count(null)==0

explain select count(release_year='2006' or null) as '2006年电影数 量',count(release_year='2007' or null) as '2007年电影数量' fromfilm;--优化，为release_year列设置索引

create index idx_release_year on film(release_year);

2、子查询优化

通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据。

--查询sandra出演的所有影片

explain select title,release_year,length from film where film_id in(select film_id from film_actor where actor_id in ( select actor_id from actor where first_name='sandra'));--优化之后

explain select title,release_year,length from film f join film_actor fa on fa.film_id=f.film_idjoin actor a on fa.actor_id =a.actor_idwhere a.first_name='sandra';

继续优化，将first_name设为索引：

create index idx_first_name on actor(first_name);

3、group by的优化

优化策略: 先给分组字段建索引；再对该表分组、分组后再和其他表关联查询

--每个演员参与影片的数量

explain select a.first_name,a.last_name,count(*) from film_actor fa inner joinactor a using(actor_id)group byfa.actor_id;--优化后 子查询 索引

explain select a.first_name,a.last_name,c.cnt fromactor ainner join(select actor_id,count(*) as cnt from film_actor group by actor_id) as c USING(actor_id)

4、limit优化

limit常用于分页处理，时常会伴随order by 从句使用，因此大多时候会使用Filesorts这样会造成大量的IO问题。

避免数据量大时扫描过多的记录：

--分页查询影片描述信息

explain select film_id,description from film order by title limit 50,5;--优化1:使用有索引的列或主键进行order by操作(order by film_id)--页数越大，rows越大

explain select film_id,description from film order by film_id limit 50,5;--优化2:记录上次返回的主键，在下次查询的时候用主键过滤，避免了数据量大时扫描过多的记录--注意要求有序主键 或者建立有序辅助索引列

explain select film_id,description from film where film_id>55 and film_id<=60 order by film_id limit 1,5;

5、in和exsits优化

原则:小表驱动大表，即小的数据集驱动大的数据集

in:当B表的数据集必须小于A表的数据集时，in优于exists：select  *fromAwhereidin(selectidfromB);

explain select * from film where id in(select film_id from film_actor);

exists:当A表的数据集小于B表的数据集时，exists优于in。将主查询A的数据，放到子查询B中做条件验证，根据验证结果(true或false)来决定主查询的数据是否保留：

select * fromAwhere exists (select 1 from B where B.id = A.id) --A表与B表的ID字段应建立索引

explain select * from film where exists (select 1 from film_actor where film_actor.film_id = film.id)

注意：

EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以是SELECT 1或select X,官方说法是实际执行时会忽略SELECT清单,因此没有区别；

EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比；

EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析。

6、join

对连接属性进行排序时，应当选择驱动表的属性作为排序表中的条件

explain select * from film join film_actor on film_actor.film_id=film.id order by film.id;

explain select * from film join film_actor on film_actor.film_id=film.id order by film_actor.film_id;

explain select name from film join film_actor on film_actor.film_id=film.id order by film_actor.film_id;