mysql优化 学习笔记

前言

优化mysq 学习笔记 视频位置
慕课–性能优化mysql

一 SQL 改写优化

慢查询日志用于记录 那些查询比较慢的查询记录

show variables like ‘slow_query_log’
上面的查看慢查询日志是否开启

下面的这个可以查询慢查询日志相关的其他信息
show variables like ‘slow%’

explain + sql语句用于分析这条sql的执行过程

explain select max(payment_date) from payment;

执行结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	payment	NULL	ALL	NULL	NULL	NULL	NULL	16086	100	NULL

向payment_date上添加一个索引 会大大的缩短搜索的时间

create index idx_paydate on payment(payment_date);

执行结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away

下面的Extra中表示 查询这个表中的最大数据不用再遍历整个表，直接取出最大的数据，提高查询速度

count/group优化

count(*)和count(file)是不一样的 后面的要忽略null 前面的不会忽略null

count的优化也可以通过添加索引来提高性能

子查询最好用连接来代替

group()查询优化

 select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor USING(actor_id) group by film_actor.actor_id;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	actor	NULL	ALL	PRIMARY	NULL	NULL	NULL	200	100	Using temporary; Using filesort
1	SIMPLE	film_actor	NULL	ref	PRIMARY,idx_fk_film_id	PRIMARY	2	sakila.actor.actor_id	27	100	Using index

优化后的SQL

SELECT actor.first_name, actor.last_name,c.cnt FROM sakila.actor INNER JOIN 
(SELECT actor_id, COUNT(*) AS cnt FROM sakila.film_actor GROUP BY actor_id) AS c USING(actor_id);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	actor	NULL	ALL	PRIMARY	NULL	NULL	NULL	200	100	NULL
1	PRIMARY		NULL	ref			2	sakila.actor.actor_id	27	100	NULL
2	DERIVED	film_actor	NULL	index	PRIMARY,idx_fk_film_id	PRIMARY	4	NULL	5462	100	Using index

额外关于USING 用于简化join

SELECT f.color, c.is_primary, c.is_dark, c.is_rainbow    
FROM flags f  
INNER JOIN color c ON f.color = c.color    
WHERE f.country = 'China'; 

上面的等价于下面的

SELECT f.color, c.is_primary, c.is_dark, c.is_rainbow  
FROM flags f  
INNER JOIN color c USING(color)  
WHERE f.country = 'China'

limit 查询优化

explain select film_id,description from sakila.film order by title LIMIT 50,5;

分析结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film	NULL	ALL	NULL	NULL	NULL	NULL	1000	100	Using filesort

优化后的

explain select film_id,description from sakila.film order by film_id LIMIT 50,5;

执行结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	film	NULL	index	NULL	PRIMARY	2	NULL	55	100	NULL

效果很明显，开始是要扫描1000条数据，下面要扫描55条数据，所以当排序效果一致时 应该率先按照主键来排序。

优化的基本思想就是减少IO

二 索引优化

选取那一列做索引

一般是按照自己的业务需要 但是在选取联合索引的时候一般是选取离散程度更高的列放在离散程度更低的列前面（在做比较的时候，一般也是选取离散程度高的做索引列）

离散程度 一般这列中唯一值多离散程度就高

select count(distinct customer_id),count(distinct staff_id) from payment;

count(distinct customer_id)	count(distinct staff_id)
599	2

customer_id的离散值就比staff_id的离散值高

冗余、重复索引

索引多了 不但会影响插入（更新 插入 删除）语句，同时会影响查询语句

一般分为重复、冗余 。

重复：例：一般主键上就会有唯一索引，但再在主键上添加一个索引就叫重复
冗余：联合索引中已经包含了 改列，但他又有单独的索引，就叫冗余缩影