锐客网

  1. 首页 > 睿知 > it技术 > >

mysql索引太大怎么办 mysql索引坏处( 二 )

IT技术创建


当给新表加完索引后，最上面那条查询直接就是0.0002s
场景：在给一张有几万条记录的表添加索引时，进度非常慢，导致其它查询无法进行
处理方式：
使用Navicat的命令行模式，执行以下命令：
show processlist;
【mysql索引太大怎么办 mysql索引坏处】这时会看到有哪些线程正在执行 ， 也可以查看锁表的线程 。你会发现alter table * add key ****那个线程状态是Waiting for table metadata lock，后面有个这个表的所有操作都是这个状态 ， 很明显是这条加索引的语句把表给锁了 。
查看线程ID，执行
kill 线程ID
这样被锁住的表就能立即被使用了 。
由此得出一个结论 ， 当一张表数据量很大时，不要轻易添加索引，会导致表被锁死！如果非要添加，那么应该先把数据表进行备份，然后进行空表添加索引 。
只能通过ALTER TABLE不能create index
参数说明：
MySQL InnoDB引擎索引长度受限怎么办mysql CREATE TABLE `tb` (
-`a` varchar(255) DEFAULT NULL,
-`b` varchar(255) DEFAULT NULL,
-`c` varchar(255) DEFAULT NULL,
-`d` varchar(255) DEFAULT NULL,
-`e` varchar(255) DEFAULT NULL,
-KEY `a` (`a`,`b`,`c`,`d`,`e`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
可以看到 ， 由于每个字段占用255*3, 因此这个索引的大小是38253072，报错 。
为什么3072
mysql索引太大怎么办我们知道InnoDB一个page的默认大小是16k 。由于是Btree组织 ， 要求叶子节点上一个page至少要包含两条记录（否则就退化链表了） 。
所以一个记录最多不能超过8k 。
又由于InnoDB的聚簇索引结构，一个二级索引要包含主键索引，因此每个单个索引不能超过4k （极端情况，pk和某个二级索引都达到这个限制） 。
由于需要预留和辅助空间，扣掉后不能超过3500，取个“整数”就是(1024*3) 。
单列索引限制
上面有提到单列索引限制767，起因是256×3-1 。这个3是字符最大占用空间（utf8） 。但是在5.5以后 ， 开始支持4个字节的uutf8 。255×4767, 于是增加了一个参数叫做 innodb_large_prefix 。
这个参数默认值是OFF 。当改为ON时，允许列索引最大达到3072 。
可以看到默认行为是建表成功，报一个warning，并且将长度阶段为255 。
注意要生效需要加row_format=compressed或者dynamic。
如果确实需要在单个很大的列上创建索引，或者需要在多个很大的列上创建联合索引，而又超过了索引的长度限制，解决办法是在建索引时限制索引prefix的大?。?
例如：create index yarn_app_result_i4 on yarn_app_result (flow_exec_id(100), another_column(50));
这样，在创建索引时就会限制使用的每个列的最大长度 。如上的例子中，在创建联合索引时 ， 最多使用列flow_exec_id中前100个字符创建索引，最多使用another_column中前
50个字符创建索引 。这样子，就可以避免索引长度过大的问题 。
最后，mysql索引太大怎么办我想说一句 。我们在设计数据库时，最好不要在一个可能包含很长字符串的列上创建索引，尤其是当这个列中的字符串都很长时 。如果在这类列上创建了索引 ， 那么在创建索引时以及根据索引查询时 ， 都会浪费很多时间在计算和存储上 。有经验的设计人员应该不会这样设计数据库 。
索引如何处理数据量大的问题一、什么是索引？
索引用来快速地寻找那些具有特定值的记录 ， 所有MySQL索引都以B-树的形式保存 。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录 。表里面的记录数量越多，这个操作的代价就越高 。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置 。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍 。

推荐阅读


上一篇：htmljquery的实现的简单介绍

下一篇：代理服务器java，代理服务器可能有问题或地址不正确是什么啥意思