锐客网

  1. 首页 > 生活百科 > it技术 > >

mysql索引怎么修复 mysql索引失效怎么办

IT技术索引

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
我们知道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 避免索引失效where条件==order by 条件==group by 条件 按顺序遵守 最佳左前缀法则
假设创建了复合索引：a,b,c
不在索引列上做任何的操作（计算、函数、显式或隐式的类型转换），否则会导致索引失效而转向全表扫描
【mysql索引怎么修复 mysql索引失效怎么办】 1、字符不加单引号会导致索引失效
name字段为varchar类型
这条sql发生了隐式的类型转换：数值==字符串 。所以导致了全表扫描，索引失效
应尽量避免在 where 子句中对字段进行表达式操作 ， 这将导致引擎放弃使用索引而进行全表扫描 。如：
mysql中的范围条件有：in/not in、 like、、BETWEEN AND ；
后面的索引失效
in会导致索引全部失效?。。?
BETWEEN AND 范围条件不会导致索引失效?。。?
尽量让索引列和查询列一致；减少select * 的使用
1、查询表结构
2、查询表的索引结构
联合索引：name,age,post；说明add_time字段没有添加索引
3、查看select * 的执行计划
4、查看 select name,age,pos的执行计划
5、如果select只用一部分索引
like以通配符开头（’玞…’）mysql索引失效会变成全表扫描的操作 。
解决：可以使用 覆盖索引 来解决这个问题！
1、先查看表上的索引
id、name、age、pos 四个字段上都有索引； 注意：name是联合索引中的第一个，带头大哥！
2、查看表结构
有个add_time字段没有用到索引
3、查看执行计划
使用UNION ALL
假设创建了联合索引 x(a,b,c)
ps：like虽然也是范围查询但是区别于、，%用在最前面就只用到索引a了；%用在最后面可以用到a b c！
下面的sql几乎违背了上面的所有原则，索引依然全部生效 。因为select是索引覆盖的，select里不包含没有建立索引的字段 。因此总是用到索引的 。可以看出来索引覆盖在sql优化中的作用性
mysql数据表 唯一索引需要修改成普通索引 怎么改？解决方案：先删除唯一索引，再在当前字段创建普通索引，参考下列说明以及SQL：
普通索引
普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度 。因此 ， 应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引 。只要有可能 ， 就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引 。
唯一索引
普通索引允许被索引的数据列包含重复的值 。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次 。
如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引 。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录 。也就是说，唯一索引可以保证数据记录的唯一性 。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复 。
删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引 。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下 。
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
创建索引
在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引 。
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)
数据库损坏了怎么办有的时候因为掉电或者其mysql索引怎么修复他原因导致数据库损坏,我们可以使用mysql自带的mysqlcheck命令来快速修复所有的数据库或者特定的数据库;例如
检查优化并修复所有的数据库用:
# mysqlcheck -A -o -r -p
Enter password:
database1 OK
database2 OK
----------
修复指定的数据库用
# mysqlcheck -A -o -r Database_NAME -p
即可
另外如果只是对某个表进行修复可以用：myisamchk或isamchk
其中myisamchk适用于MYISAM类型的数据表，而isamchk适用于ISAM类型的数据表 。这两条命令的主要参数相同，一般新的系统都使用MYISAM作为缺省的数据表类型，这里以myisamchk为例子进行说明 。当发现某个数据表出现问题时可以使用：
myisamchk tablename.MYI
进行检测，如果需要修复的话，可以使用：
myisamchk -of tablename.MYI
关于myisamchk的详细参数说明，可以参见它的使用帮助 。需要注意的时在进行修改时必须确保MySQL服务器没有访问这个数据表，保险的情况下是最好在进行检测时把MySQL服务器Shutdown掉 。
另外可以把下面的命令放在mysql索引怎么修复你的rc.local里面启动MySQL服务器前：
[ -x /tmp/mysql.sock ]/pathtochk/myisamchk -of /DATA_DIR/*/*.MYI
其中的/tmp/mysql.sock是MySQL监听的Sock文件位置，对于使用RPM安装的用户应该是 /var/lib/mysql/mysql.sock，对于使用源码安装则是/tmp/mysql.sock可以根据自己的实际情况进行变更，而 pathtochk则是myisamchk所在的位置，DATA_DIR是你的MySQL数据库存放的位置 。
1,简单的修复模式
myisamchk -r -q path/数据库/坏表.MYI
注:-r ----恢复模式 -q ----快速修复
2,使用安全修复模式
myisamchk --safe-recover path/数据库/坏表.MYI
3,困难的修复模式
如果在索引文件的第一个16K块被破坏，或包含不正确的信息，或如果索引文件丢失 ， 你只应该到这个阶段。在这种情况下，创建一个新的索引文件是必要的 。按如下这样做：
把数据文件移更安全的地方 。
使用表描述文件创建新的(空)数据和索引文件：
shell mysql db_name
mysql Delete FROM tbl_name;
mysql quit
将老的数据文件拷贝到新创建的数据文件之中 。（不要只是将老文件移回新文件之中；你要保留一个副本以防某些东西出错 。）
回到阶段2 。现在myisamchk -r -q应该工作了 。（这不应该是一个无限循环) 。
4,非常困难的修复模式
只有描述文件也破坏了 ， 你才应该到达这个阶段 。这应该从未发生过，因为在表被创建以后，描述文件就不再改变了 。
从一个备份恢复描述文件并且回到阶段3 。你也可以恢复索引文件并且回到阶段2 。对后者，你应该用myisamchk -r启动 。
如果你没有一个备份但是确切地知道表是怎样被创建的，在另一个数据库中创建表的一个拷贝 。删除新的数据文件，然后从其他数据库将描述和索引文件移到破坏的数据库中 。这给了你新的描述和索引文件，但是让数据文件独自留下来了 。回到阶段2并且尝试重建索引文件 。
5,优化表结构
myisamchk -r 表
也可以使用sql语句来优化OPTIMIZE TABLE
本方法参考自mouse博客
mysql索引怎么修复的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于mysql索引失效怎么办、mysql索引怎么修复的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：步步高x5怎么连接u盘，步步高手机怎么连接u盘

    下一篇：包含棋牌棋牌游戏平台的词条