mysqlinlist优化_MySQL优化整理
⼀、SQL优化
1、show status查看各种sql的执⾏频率
SHOW STATUS 可以根据需要显⽰ session 级别的统计结果和 global级别的统计结果。
显⽰当前session：show status like "Com_%"；全局级别：show global status；
⽐如：show status like "Com_select"
2、定位低效的SQL语句
(1)、showprocesslist
进⼊mysql时可⽤show processlist；不在mysql提⽰符下使⽤时⽤mysql -uroot  -e 'Show  processlist'  或者  mysqladmin processlist
其中主要的参数是state，下⾯表列出了state常见的问题以及排查思路：
状态⼀：sending data
Sending data：表⽰从引擎层读取数据返回给Server端的状态
长时间存在原因
(1)    没适当的索引，查询效率低
(2)    读取⼤量数据，读取缓慢
(3)    系统负载⾼，读取缓慢
解决⽅法：
(1)    加上合适的索引
(2)    或者改写SQL，提⾼效率
(3)    增加LIMIT限制每次读取数据量
(4)    检查&升级I/O设备性能
状态⼆、Waiting for table metadata lock
Waiting for table metadata lock：长时间等待MDL锁原因：
(1)    DDL被阻塞，进⽽阻塞他后续SQL
(2)    DDL之前的SQL长时间未结束
解决⽅法：
(1)    提⾼每个SQL的效率
(2)    ⼲掉长时间运⾏的SQL
(3)    把DDL放在半夜等低⾕时段
(4)    采⽤pt-osc执⾏DDL
状态三、Sleep
危害：
(1)    占⽤连接数
(2)    消耗内存未释放
(3)    可能有⾏锁(甚⾄是表锁)未释放
解决⽅法：
(1)    适当调低timeout
(2)    主动Kill超时不活跃连接
(3)    定期检查锁、锁等待
shell批量注释
(4)    可以利⽤pt-kill⼯具
状态四：Copy to tmp table
原因：
(1)　执⾏alter table 修改表结构，需要⽣成临时表
(2)    建议放在夜间低⾕执⾏，或者⽤pt-osc
状态五：Creating tmp table
常见于group by 没有索引的情况，需要拷贝数据到临时表[内存/磁盘上]，执⾏计划中会出现Using temporary关键字。建议创建合适的索引，消除临时表
状态六、Creating sort index
常见于order by 没有索引的情况，需要进⾏filesort排序，执⾏计划中会出现Using filesort关键字。建议创建排序索引。
(2)、通过慢查询⽇志定位
启动慢查询⽇志：set global slow_query_log=1；设置超时时间：set global long_query_time=4
3、⽤EXPLAIN分析低效SQL
mysql> explain select * from (select * from ( select * from t1 where id=2602) a) b;接口类型不能用于实例化对象也不能用于声明对象引用
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |环境变量可不可以是一个具体变量
| 2 | DERIVED | | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | t1 | const | PRIMARY,idx_t1_id | PRIMARY | 4 | | 1 | |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
主要有两个重要的参数：type和Extra
(1)type：表⽰MySQL在表中到所需⾏的⽅式，⼜称“访问类型”。
常⽤的类型有： ALL, index, range, ref, eq_ref, const, system, NULL(从左到右，性能从差到好)
ALL：Full Table Scan， MySQL将遍历全表以到匹配的⾏
index:  Full Index Scan，index与ALL区别为index类型只遍历索引树。这种类型表⽰是mysql会对整个该索引进⾏扫描。要想⽤到这种类型的索引，对这个索引并⽆特别要求，只要是索引，或者某个复合索引的⼀部分，mysql都可能会采⽤index类型的⽅式扫描。但是呢，缺点是效率不⾼，mysql会从索引中的第⼀个数据⼀个个的查到最后⼀个数据，直到到符合判断条件的某个索引。
range: 只检索给定范围的⾏，使⽤⼀个索引来选择⾏。当使⽤=、<>、>、>=、、BETWEEN或者IN操作符，⽤常量⽐较关键字列时，可以使⽤range：
SELECT * FROM tbl_name WHERE key_column = 10;
SELECT* FROM tbl_name WHERE key_column BETWEEN 10 and 20;
SELECT* FROM tbl_name WHERE key_column IN (10,20,30);
android系统提供了三种设置宽度和高度的属性分别是
SELECT* FROM tbl_name WHERE key_part1= 10 AND key_part2 IN (10,20,30);
ref:  对于每个来⾃于前⾯的表的⾏组合，所有有匹配索引值的⾏将从这张表中读取。如果联接只使⽤键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY(换句话说，如果联接不能基于关键字选择单个⾏的话)，则使⽤ref。如果使⽤的键仅仅匹配少量⾏，该联接类型是不错的。 ref可以⽤于使⽤=或<=>操作符的带索引的列。
SELECT * FROM ref_table WHERE key_column=expr;
SELECT* FROM ref_table,other_table WHERE ref_table.key_column=lumn;
SELECT* FROM ref_table,other_table WHERE ref_table.key_column_part1=lumn
AND ref_table.key_column_part2=1;
个人博客网页下载eq_ref: 使⽤的索引是唯⼀索引，对于每个索引键值，表中只有⼀条记录匹配，即多表连接中使⽤primary key或者unique key作为关联条件
const:  表最多有⼀个匹配⾏，它将在查询开始时被读取。因为仅有⼀⾏，在这⾏的列值可被优化器剩余部分认为是常数。const⽤于⽤常数值⽐较PRIMARY KEY或UNIQUE索引的所有部分时。
⽐如查询：SELECT * from tbl_name WHERE primary_key=1；tbl_name可以⽤const表。
system: 表仅有⼀⾏(=系统表)。这是const联接类型的⼀个特例。
NULL: MySQL在优化过程中分解语句，执⾏时甚⾄不⽤访问表或索引，例如从⼀个索引列⾥选取最⼩值可以通过单独索引查完成。
(2)Extra: 该列包含MySQL解决查询的详细信息，有以下⼏种情况：
Using where: 列数据是从仅仅使⽤了索引中的信息⽽没有读取实际的⾏动的表返回的，这发⽣在对表的全部的请求列都是同⼀个索引的部分的时候，表⽰mysql服务器将在存储引擎检索⾏后再进⾏过滤
Using temporary：表⽰MySQL需要使⽤临时表来存储结果集，常见于排序和分组查询
Using filesort：MySQL中⽆法利⽤索引完成的排序操作称为“⽂件排序”
Using join buffer：改值强调了在获取连接条件时没有使⽤索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的⾏。
Select tables optimized away：这个值意味着仅通过使⽤索引，优化器可能仅从聚合函数结果中返回⼀⾏
⼆、索引优化
对哪些字段建⽴索引呢？⼀般说来，索引应建⽴在那些将⽤于JOIN, WHERE判断和ORDER BY排序的字段上。尽量不要对数据库中某个含有⼤量重复的值的字段建⽴索引。
1、查看索引使⽤情况
如果索引正在⼯作， Handler_read_key 的值将很⾼，这个值代表了⼀个⾏被索引值读的次数，很低的值表明增加索引得到的性能改善不⾼，因为索引并不经常使⽤。
Handler_read_rnd_next 的值⾼则意味着查询运⾏低效，并且应该建⽴索引补救。这个值的含义是在数据⽂件中读下⼀⾏的请求数。如果你正进⾏⼤量的表扫描，如果该值较⾼。通常说明表索引不正确或写⼊的查询没有利⽤索引。
语法：
mysql> show status like 'Handler_read%';
2、order by优化
基于索引的排序
MySQL的弱点之⼀是它的排序。虽然MySQL可以在1秒中查询⼤约15,000条记录，但由于MySQL在查询时最多只能使⽤⼀个索引。因此，如果WHERE条件已经占⽤了索引，那么在排序中就不使⽤索引了，这将⼤⼤降低查询的速度。我们可以看看如下的SQL语句:
SELECT * FROM SALES WHERE NAME = “name” ORDER BY SALE_DATE DESC;
在以上的SQL的WHERE⼦句中已经使⽤了NAME字段上的索引，因此，在对SALE_DATE进⾏排序时将不再使⽤索引。为了解决这个问题，我们可以对SALES表建⽴复合索引:
ALTER TABLE SALES DROP INDEX NAME, ADD INDEX (NAME,SALE_DATE)
这样再使⽤上述的SELECT语句进⾏查询时速度就会⼤副提升。但要注意，在使⽤这个⽅法时，要确保WHERE⼦句中没有排序字段，在上例中就是不能⽤SALE_DATE进⾏查询，否则虽然排序快了，但是SALE_DATE字段上没有单独的索引，因此查询⼜会慢下来。
在某些情况中， MySQL可以使⽤⼀个索引来满⾜ ORDER BY⼦句，⽽不需要额外的排序。 where条件和order by使⽤相同的索引，并且order by 的顺序和索引顺序相 同，并且order by的字段都是升序或者都是降序。
下列sql可以使⽤索引。
SELECT *FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT* FROM t1 WHERE key_part1=1ORDER BY key_part1 DESC, key_part2 DESC;
SELECT* FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
mysql语句的执行顺序但是以下情况不使⽤索引：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC ；  　--order by 的字段混合 ASC 和 DESC
SELECT* FROM t1 WHERE key2=constant ORDER BY key1 ；  --⽤于查询⾏的关键字与 ORDER BY 中所使⽤的不相同
SELECT* FROM t1 ORDER BY key1, key2 ；          -- 对不同的关键字使⽤ ORDER BY ：
3、Group by优化
默认情况下， MySQL 排序所有 GROUP BY col1 ， col2 ， .... 。查询的⽅法如同在查询中指定 ORDER BY col1 ， col2 ， ... 。如果显式包括⼀个包含相同的列的 ORDER BY⼦句， MySQL 可以毫不减速地对它进⾏优化，尽管仍然进⾏排序。如果查询包括 GROUP BY 但你想要避免排序结果的消耗，你可以指定 ORDER BY NULL禁⽌排序。
例如 ：
INSERT INTO foo  SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。