mysql查询调优

Posted on by jam

mysql查询调优

0x01分区

数据量大的表，可以进行分区，目前MySQL支持范围分区（RANGE），列表分区（LIST），哈希分区（HASH）以及KEY分区四种。

分区的优势：

1、冷热分离：表非常大且只在表的最后部分有热点数据，冷数据根据分区规则自动归档。
2、定期淘汰历史数据：按时间写入，历史数据可淘汰，可快速删除，空间可快速回收。
3、优化查询：在where字句中包含分区列时，分区可以大大提高查询效率，减少缓存开销、减少IO开销。
4、统计性能提升：在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果。
 
MySQL的分区规则：
  • 范围 ：PARTITIONED BY RANGE COLUMNS
  • 列表 ：PARTITION BY LIST COLUMNS
  • HASH：PARTITION BY HASH
  • KEY ：PARTITION BY KEY
  • 子分区：SUBPARTITION BY XXX
 
分区语句
CREATE TABLE IF NOT EXISTS `jam` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `report_dt` date NOT NULL DEFAULT '0000-00-00' COMMENT '日期',
  PRIMARY KEY (`id`,`report_dt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Jam'
PARTITION BY RANGE COLUMNS(report_dt)
(   
PARTITION pmin VALUES LESS THAN (('2017-01-01')),
PARTITION p20170101 VALUES LESS THAN (('2017-01-16')),
PARTITION p20211202 VALUES LESS THAN (('2022-01-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
查看分区情况
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE  TABLE_NAME = 'jam';
 
其他分区可以自己查询
 

0x02索引

索引对于改善查询非常有用，但是也有很多需要注意的地方。
索引是在MYSQL的存储引擎层中实现的，而不是在服务层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。MYSQL目前提供了一下4种索引。
  • B-Tree 索引：最常见的索引类型，大部分引擎都支持B树索引。
  • HASH 索引：只有Memory引擎支持，使用场景简单。
  • R-Tree 索引(空间索引)：空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。
  • Full-text (全文索引)：全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。
 
MyISAM、InnoDB引擎、Memory三个常用引擎类型比较
索引
MyISAM引擎
InnoDB引擎
Memory引擎
B-Tree 索引
支持
支持
支持
HASH 索引
不支持
不支持
支持
R-Tree 索引
支持
不支持
不支持
Full-text 索引
不支持
暂不支持
不支持
 
设置索引的方法
1．ALTER TABLE - ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
  • ALTER TABLE table_name ADD INDEX index_name (column_list)
  • ALTER TABLE table_name ADD UNIQUE (column_list)
  • ALTER TABLE table_name ADD PRIMARY KEY (column_list)

     

2．CREATE INDEX - CREATE INDEX可对表增加普通索引或UNIQUE索引。
  • CREATE INDEX index_name ON table_name (column_list)
  • CREATE UNIQUE INDEX index_name ON table_name (column_list)

     

查看索引
show index from tblname;
show keys from tblname;

 

索引选择原则

  1.  较频繁的作为查询条件的字段应该创建索引
  2.  唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
  3.  更新非常频繁的字段不适合创建索引
  4.  不会出现在 WHERE 子句中的字段不该创建索引
性能优化过程中，选择在哪个列上创建索引是最非常重要的。
索引列的基数越大，索引的效果越好。
使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度；
利用最左前缀
 
 

0x03优化命令：explain

EXPLAIN列的解释
table                    显示这一行的数据是关于哪张表的
type                     这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys     显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key                      实际使用的索引。如果为NULL，则没有使用索引。
key_len               使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref                       显示索引的哪一列被使用了，如果可能的话，是一个常数
rows                    MYSQL认为必须检查的用来返回请求数据的行数
Extra                   关于MYSQL如何解析查询的额外信息。
 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Extra字段值含义：
Distinct                   一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
Not exists               MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
Range checked for each Record（index map:#）      没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort          看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index            列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary    看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
Where used           使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）
 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
type字段值含义：
const       表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
eq_ref     连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
ref           这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
range      这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
index       这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
ALL         这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免
 
例如：
explain partitions select * from table1 where id <100

 

0x04 OPTIMIZE TABLE

 
当对MySQL进行大量的增删改操作的时候，很容易产生一些碎片，这些碎片占据着空间，所以可能会出现删除很多数据后，数据文件大小变化不大的现象。当然新插入的数据仍然会利用这些碎片。但过多的碎片，对数据的插入操作是有一定影响的，此时，我们可以通过optimize来对表的优化。Optimize 语句目前支持MyIASM和BDB表。
用法
为了更加直观的看到数据碎片，Mysql可以使用如下命令查看 
show table status [like table_name] 
 
data_free 选项代表数据碎片。 
InnoDB 引擎的表分为独享表空间和同享表空间的表，我们可以通过
show variables like ‘innodb_file_per_table’;
来查看是否开启独享表空间。
如果开启了独享表空间的。此时是无法对表进行optimize操作的，如果操作，会返回如图信息，最后的一条Table does not support optimize, doing recreate + analyze instead。因为该结构下删除了大量的行，此时索引会重组并且会释放相应的空间因此不必优化。
当然optimize在对表进行操作的时候，会加锁，所以不宜经常在程序中调用。 
 
 
 
 
ref.
 
 
 

发表回复

您的电子邮箱地址不会被公开。

粤ICP备17041560号-2