mysql优化 explain index
本文章属于转载,尊重原创:http://www.2cto.com/database/201501/369135.html
实验环境:
1、sql工具:Navicat 2、sql数据库,使用openstack数据库作为示例 |
一、mysql索引查询
show index from instances |
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100332254.png)
Table:数据库表名 Non_unique:索引不能包括重复词,则为0。可以,则为1。 Key_name:索引的名称。 索引中的列序列号,从1开始。 列名称 列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机 会就越大。 如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。 指示关键字如何被压缩。如果没有被压缩,则为NULL。 如果列含有NULL,则为YES。如果没有,则该列为NO。 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。 Comment:注释。 |
二、验证Mysql的主键会自动创建索引? 创建一个没有主键的ttx_index数据库表:
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100336257.png)
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100339260.png)
改变ttx_index数据库表字段id,将之设为主键,再次查询索引:
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100340262.png)
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100348264.png)
得出结论,在Mysql中,数据库主键会自动建立索引。
三、Mysql性能优化利器:explain 1、首先查看instances数据库表的索引:
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100349265.png)
2、EXPLAIN 用法详解:
EXPLAIN SELECT * FROM instances |
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100351267.png)
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100352268.png)
explain字段详解:
table:显示这一行的数据是关于哪张表的 type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 key: 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引 key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好 ref:显示索引的哪一列被使用了,如果可能的话,是一个常数 rows:MYSQL认为必须检查的用来返回请求数据的行数 Extra:关于MYSQL如何解析查询的额外信息。将在下表中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢 extra列返回的描述的意义:
|
那么如何才能让sql走索引查询呢?
EXPLAIN SELECT * FROM instances WHERE id=1 |
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100354270.png)
接下来实验,当不走索引还是查询id=1这条数据时候,会是如何?
SELECT id, display_name FROM instances WHERE id=1 |
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100355271.png)
EXPLAIN SELECT * FROM instances WHERE display_name = "vm1" |
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100356274.png)
结论:在查询时候,如果where条件中的字段有索引(走不走索引,取决于where条件中的字段),在执行sql语句时,mysql会自动走索引。
但是有个问题是,在走不走索引,查询花费时间都是0.001ms,似乎没有得到性能提高?
SELECT COUNT(*) FROM instances |
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100357275.png)
在数据库表中instance数据总条数才74条,因此索引没法发挥它的性能优势,接下来人为制造上w条数据:
insert instances(display_name) select display_name from instances |
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100358277.png)
再次查询总条数:
SELECT COUNT(*) FROM instances |
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100358278.png)
这次数据已经有接近500w了。 再次验证上述索引性能问题:
1、为了对比的真实性,将id=1的数据记录的display_name修改为唯一名字test_index_dispaly_name
SELECT id, display_name FROM instances WHERE id=1 |
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100359280.png)
2、不走索引查询:
SELECT * FROM instances WHERE display_name = 'test_index_dispaly_name' |
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100400281.png)
3、通过id走索引查询:
SELECT * FROM instances WHERE id=1 |
![\](http://www.2cto.com/uploadfile/Collfiles/20150113/20150113100400283.png)
结论:对于百万上亿级数据,走不走索引效率影响相当明显(效率差别都到万了)。
4、哪些情况sql不会走索引?
时间关系,此处暂且未总结,后续有时间补上。若有需要请自行网上查找。 |
相关文章
- mysql优化杂记
- mysql中影响myisam引擎写入性能的三项设置
- 【MySQL从入门到精通】【高级篇】(二十一)数据库优化步骤_查看系统性能参数
- mysql 索引与优化like查询
- MySQL的基本概念与操作
- mysql的binlog增量日志解析,提供增量数据订阅和消费
- 安装mysql-workbench
- <转>MySQL性能调优的10个方法
- linux下安装mysql初始化报错:bin/mysqld: error while loading shared libraries: libnuma.so.1
- mysql 5.7 表空间优化alter table xxxx
- 004.MySQL主库手动复制至从库
- MySQL数据库性能优化思路与解决方法(二转)
- SQL学习笔记三(补充-1)之MySQL存储引擎
- MySQL 优化Limit分页
- MySQL批量执行SQL修改视图属主的办法
- 一个最不可思议的MySQL死锁分析
- Mysql优化之优化工具profiling
- mysql use index () 优化查询的例子
- MySQL分页查询优化
- 玩转Mysql系列 - 第1天:mysql基础知识
- Mysql优化的方法
- MySQL(三) 数据库表的查询操作【重要】
- MySQL STR_TO_DATE函数
- 【mysql】查询数组类型的数据