mysql查询优化相关技巧

页面导航:首页 > 数据库 > Mysql > mysql查询优化相关技巧

mysql查询优化相关技巧

来源: 作者: 时间:2016-01-30 09:02 【

本文参考自 《Mysql技术内幕(第4版)》mysql查询优化的相关操作使用EXPLAIN语句检查优化器操作 43;---- 43;------------- 43;---------- 43;------- 43;--------------- 43;------ 43;--------
 使用EXPLAIN语句检查优化器操作 +----+-------------+----------+-------+---------------+------+---------+------+------+----------------- | id | select_type | table | type | possible_keys | key | key_len| ref | rows | Extra +----+-------------+----------+-------+---------------+------+---------+------+------+----------------- | 1 |SIMPLE | car_info | range | name | name | 768 | NULL | 9 | Using where; Using index | +----+-------------+----------+-------+---------------+------+---------+------+------+----------------
EXPLAIN输出解释
select_type 有如下几种类型: SIMPLE:未使用连接查询或者子查询的简单select语句 explain select * from car_info;
PRIMARY:最外层的select语句 explain select * from (select name from car_info where name like '凯迪拉克%') as a;
+----+-------------+------------+-------+---------------+------+---------+------+------+-------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+------+---------+------+------+---------------
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | |
| 2 | DERIVED | car_info | range | name | name | 768 | NULL | 9 | Using where; Using index |
+----+-------------+------------+-------+---------------+------+---------+------+------+---------------
UNION:union中的第二个,或后面的select语句 explain select name from car_info where id =100 union select name from web_car_brands where id =5; +------+--------------+----------------+-------+---------------+---------+---------+-------+------+-- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+----------------+-------+---------------+---------+---------+-------+------+--- | 1 | PRIMARY | car_info | const | PRIMARY | PRIMARY | 8 | const | 1 | | | 2 | UNION | web_car_brands | const | PRIMARY,id | PRIMARY | 4 | const | 1 | | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------+----------------+-------+---------------+---------+---------+-------+------+---

DEPENDENT UNION:union中的第二个或后面的色了传统语句,取决于外面的查询
> explain select * from t3 where id in (select id from t3 where id=3952602 union all select id from t3) ;
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where
| 2 | DEPENDENT SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | Using index |
| 3 | DEPENDENT UNION | t3 | eq_ref | PRIMARY,idx_t3_id | PRIMARY | 4 | func | 1 | Using where; Using index |
|NULL | UNION RESULT | <union2,3> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+-

UNION RESULT:union的结果 explain select name from car_info where id =100 union select name from web_car_brands where id =5; +------+--------------+----------------+-------+---------------+---------+---------+-------+------+----- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------+----------------+-------+---------------+---------+---------+-------+------+-- | 1 | PRIMARY | car_info | const | PRIMARY | PRIMARY | 8 | const | 1 | | | 2 | UNION | web_car_brands | const | PRIMARY,id | PRIMARY | 4 | const | 1 | | | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | | +------+--------------+----------------+-------+---------------+---------+---------+-------+------+----
SUBQUERY:子查询中的第一个SELECT语句 explain select name from car_info where id = (select id from web_car_series where id = 5); +----+-------------+----------------+-------+---------------+---------+---------+-------+------+----- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+-------+---------------+---------+---------+-------+------+------ | 1 | PRIMARY | car_info | const | PRIMARY | PRIMARY | 8 | const | 1 | | | 2 | SUBQUERY | web_car_series | const | PRIMARY | PRIMARY | 4 | | 1 | Using index | +----+-------------+----------------+-------+---------------+---------+---------+-------+------+-----

DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
explain select name from car_info where id in (select id from web_car_series where id = 5); +----+--------------------+----------------+-------+---------------+---------+---------+-------+------+- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+----------------+-------+---------------+---------+---------+-------+------+ | 1 | PRIMARY | car_info | index | NULL | name | 768 | NULL | 145 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | web_car_series | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | +----+--------------------+----------------+-------+---------------+---------+---------+-------+-----

DERIVED:在from列表中包含子查询,mysql会递归的执行该子查询,并把结果放在临时表中
explain select * from (select name from car_info where id = 100) a; +----+-------------+------------+--------+---------------+---------+---------+------+------+- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+------+------+- | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | | | 2 | DERIVED | car_info | const | PRIMARY | PRIMARY | 8 | | 1 | | +----+-------------+------------+--------+---------------+---------+---------+------+------+-
type列: MySQL 在表里找到所需行的方式包括如下几张(由左至右,由最差到最好): All-->index-->range-->ref -->eq_ref-->const,system -->null
ALL:进行全数据表扫描 index:按照索引的次序扫描表,先读索引,然后读取具体的数据行,其实还是全表扫描,好处在于不用排序,按照索引的顺序 range:按照某个范围读取数据行 ref:非唯一性索引访问 eq_ref:使用唯一性索引访问(主键或者唯一性索引) const:最多只有一个匹配行,const常用于数值比较如 primary key
null:在优化过程中已经得到结果,不需要访问表或者索引 如:explain select min(id) from car_info;
possible_keys列: possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样,创造一个适当的索引并且再次用EXPLAIN检查查询
key列 key列显示MySQL实际决定使用的键(索引)。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len列 key_len列显示MySQL决定使用的键长度。使用的索引的长度,在不损失精确性的情况下,长度越短越好
rows列 rows列显示MySQL认为它执行查询时必须检查的行数
 mysql相关优化技巧
尽量使用数据类型相同的数据列进行比较
使带索引的数据列在比较表达式中单独出现
不要在like模式的开始位置使用通配符,此时索引无效
尽量使用数值操作,少使用字符串操作
数据类型合理选用,尽量"小",选择适用于存储引擎的数据格式
尽量将数据列声明为NOT NULL ,因为MYSQL不需要在查询处理期间检查数据列值是否为NULL
考虑使用ENUM数据列,ENUM在MYSQL内部被表示为一系列数值,处理速度快
利用Procedure analyse()语句 该语句可以将数据列中可以采用ENUM方式字段列出,procedure analyse(16,256)语句表示数据列中不同取值超过16个的或者长度超过256个字节的,不提出ENUM类型的建议
对容易产生碎片化的数据表进行整理,对于可变长度的数据列,随着数据的大量修改或者删除极易产生碎片,因此需要定期optimize table
尽量避免对BLOB或TEXT值进行索引
Tags:

相关文章

    文章评论

    最 近 更 新
    热 点 排 行
    Js与CSS工具
    代码转换工具
    
    <