mysql 数据量大 加了索引 聚类查询还是慢
作者&投稿:霍咽 (若有异议请与网页底部的电邮联系)
你用的是mysql什么版本,看的是很久前的资料。 你遇到的问题,我之前遇到过,以前也认为myisam读更快 在大数据量情况下innodb更快,但是必须加内存。 你可以复制个表,改过来试试。网上搜个配innodb内存的方法
具体问题具体分析,你最好简单说一下表结构,以及你索引加在哪个字段上,你的数据量多大,慢是指多长时间。
可以根据条件去添加索引,一、
所有mysql索引列类型都可以被索引,对来相关类使用索引可以提高select查询性能,根据mysql索引数,可以是最大索引与最小索引,每种存储引擎对每个表的至少支持16的索引。总索引长度为256字节。
mysim和innodb存储引擎的表默认创建索引都是btree索引,目前mysql还不支持函数索引,但支持前缘索引,对字段前N个字符创建索引
二、mysql创建索引语法
Create [unioun|fulltext|spatial] index indexname[using indextype] on tablename( tablenamecol)
index_col_name:
col_name[ (length)][asc |desc]
如果你创建索引时搞错了,需要修改mysql索引我们可以用alert来修改索引,语法与create index创建索引差不多,我们就不说了,可以查看相关手册。
下面我们来看一个关于mysql创建索引实例教程。
mysql>create index cityname on city(city(2));
Query Ok,600 rows affected (0.26 sec)
Records :600 Duplicates:0 Warings 0:
我们现在来以city为条件进行查询,如下面。
->explain select * from city where city ='www.111cn.net' G
id:1
......
possible_keys:cityname
key:cityname
好了,现在我们来看看mysql删除索引等实例
Drop indexname on tablename
实例,我现在要删除刚才创建city索引
>drop index cityname on city;
Query ok, .....
不过通常对百万级数据的查询或者其他操作,都改换其他的大型的数据库了
1. 执行计划中明明有使用到索引,为什么执行还是这么慢?
2. 执行计划中显示扫描行数为 644,为什么 slow log 中显示 100 多万行?
a. 我们先看执行计划,选择的索引 “INDX_BIOM_ELOCK_TASK3(TASK_ID)”。结合 sql 来看,因为有 "ORDER BY TASK_ID DESC" 子句,排序通常很慢,如果使用了文件排序性能会更差,优化器选择这个索引避免了排序。
那为什么不选 possible_keys:INDX_BIOM_ELOCK_TASK 呢?原因也很简单,TASK_DATE 字段区分度太低了,走这个索引需要扫描的行数很大,而且还要进行额外的排序,优化器综合判断代价更大,所以就不选这个索引了。不过如果我们强制选择这个索引(用 force index 语法),会看到 SQL 执行速度更快少于 10s,那是因为优化器基于代价的原则并不等价于执行速度的快慢;
b. 再看执行计划中的 type:index,"index" 代表 “全索引扫描”,其实和全表扫描差不多,只是扫描的时候是按照索引次序进行而不是行,主要优点就是避免了排序,但是开销仍然非常大。
Extra:Using where 也意味着扫描完索引后还需要回表进行筛选。一般来说,得保证 type 至少达到 range 级别,最好能达到 ref。
在第 2 点中提到的“慢日志记录Rows_examined: 1161559,看起来是全表扫描”,这里更正为“全索引扫描”,扫描行数确实等于表的行数;
c. 关于执行计划中:“rows:644”,其实这个只是估算值,并不准确,我们分析慢 SQL 时判断准确的扫描行数应该以 slow log 中的 Rows_examined 为准。
4. 优化建议:添加组合索引 IDX_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID)
优化过程:
TASK_DATE 字段存在索引,但是选择度很低,优化器不会走这个索引,建议后续可以删除这个索引:
select count(*),count(distinct TASK_DATE) from T_BIOMA_ELOCK_TASK;+------------+---------------------------+| count(*) | count(distinct TASK_DATE) |+------------+---------------------------+| 1161559 | 223 |+------------+---------------------------+
在这个 sql 中 REL_DEVID 字段从命名上看选择度较高,通过下面 sql 来检验确实如此:
select count(*),count(distinct REL_DEVID) from T_BIOMA_ELOCK_TASK;+----------+---------------------------+| count(*) | count(distinct REL_DEVID) |+----------+---------------------------+| 1161559 | 62235 |+----------+---------------------------+
由于有排序,所以得把 task_id 也加入到新建的索引中,REL_DEVID,task_id 组合选择度 100%:
select count(*),count(distinct REL_DEVID,task_id) from T_BIOMA_ELOCK_TASK;+----------+-----------------------------------+| count(*) | count(distinct REL_DEVID,task_id) |+----------+-----------------------------------+| 1161559 | 1161559 |+----------+-----------------------------------+
在测试环境添加 REL_DEVID,TASK_ID 组合索引,测试 sql 性能:alter table T_BIOMA_ELOCK_TASK add index idx_REL_DEVID_TASK_ID(REL_DEVID,TASK_ID);
添加索引后执行计划:
这里还要注意一点“隐式转换”:REL_DEVID 字段数据类型为 varchar,需要在 sql 中加引号:AND T.REL_DEVID = 000000025xxx >> AND T.REL_DEVID = '000000025xxx'
执行时间从 10s+ 降到 毫秒级别:
1 row in set (0.00 sec)
结论
一个典型的 order by 查询的优化,添加更合适的索引可以避免性能问题:执行计划使用索引并不意味着就能执行快。
惠货脉络: 可以根据条件去添加索引, 一、 所有mysql索引列类型都可以被索引,对来相关类使用索引可以提高select查询性能,根据mysql索引数,可以是最大索引与最小索引,每种存储引擎对每个表的至少支持16的索引.总索引长度为256字节. ...
惠州市19462553392: mysql 数据库,表每天会插入30W条数据,该表数据千万级,查询效率很慢,建立索引是否利大于弊? - ?
惠货脉络: 呵呵,我认为可以建立索引,但必须要合理分配IO 为什么: 1:索引是建立在表的一列或多个列上的辅助对象,目的是加快访问表中的数据(加快查询); 2:通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的...
惠州市19462553392: mysql 多大数据量适合建索引? - ?
惠货脉络: mysql 1、表的主键、外键必须有索引; 2、数据量超过300的表应该有索引; 3、经常与其他表进行连接的表,在连接字段上应该建立索引; 4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引; 5、索引应该建在选择性高的字段上; 6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引; 7、频繁进行数据操作的表,不要建立太多的索引; 8、删除无用的索引,避免对执行计划造成负面影响;
惠州市19462553392: mysql索引建多了有什么坏处?
惠货脉络: 建立索引常用的规则如下: 1、表的主键、外键必须有索引; 2、数据量超过300的表应该有索引; 3、经常与其他表进行连接的表,在连接字段上应该建立索引; 4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引; 5、索...
惠州市19462553392: mysql数据库有100万+数据,查询起来很慢了,如何优化 - ?
惠货脉络: 你好,你可以根据条件去添加索引,例如:<br> <br>一、<br>所有mysql索引列类型都可以被索引,对来相关类使用索引可以提高select查询性能,根据mysql索引数,可以是最大索引与最小索引,每种存储引擎对每个表的至少支持16的索引.总...
惠州市19462553392: sql 数据库中,为什么大数目不同值不适合创建聚集索引??? - ?
惠货脉络: 这个说法不是很成立.主要看你的应用是插入/修改为主,还是查询为主.》若是查询为主,就要创建INDEX;而Clusted Index比普通索引更快速.》但是,Clusted Index是数据页本身,插入时会导致数据重整 -- 频繁的Data Page分裂、合并.因此,若是插入/修改为主,在多用户并发状态下,容易死锁(Page分裂、合并时会加lock的;量大会导致锁的级别--即范围上升).
惠州市19462553392: mysql 查询的时候加了索引 查询还是很慢怎么办 - ?
惠货脉络: MySQL的最佳是单表百万级,一旦上到千万级就慢了,只能分表,分表不行就集群或者换数据库吧.
惠州市19462553392: MYSQL数据量非常大的表,查询的非常慢,该怎么办?冗余?加索引??
惠货脉络: 拆分数据库表,将表按照一定的规则水平分到不同的服务器上 对表进行分区,按一定的条件将表进行水平或则垂直分区 规划好表结构,在那些字段使用频繁且字段唯一值较差的字段建立索引(经常变化的字段勿建立索引) 还有就是服务器的架构问题了,什么负载均衡啥的,自己去网上找资料
惠州市19462553392: MySQL数据库,一天一万条以上的增量,怎么优化? - ?
惠货脉络: bulk_insert_buffer_size参数相对增大------用于存放insert语句的缓存空间,增大可以提高insert的速度对于insert频率较大的表,可以适当删除不常用的索引,可以减少对表索引维护的开销在业务允许的情况下,也就是说不需要事物机制,建议用myisam引擎,相比较而言,myisam比innodb的批量插入要快很多,当然还有archive引擎,不过这个引擎很少用,所以建议还是用myisam
惠州市19462553392: mysql数据量大时怎么建立索引提高查询效率 - ?
惠货脉络: mysql的索引方法btree和hash的区别 Hash索引:Hash 索引结构的特殊性,其检索效率非常高,但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端,主要有以下这些:(1)Hash 索引仅仅能满足”=”,”IN”和”<=>”查询,不能使用范...