索引失效以及MYSQL建表注意事项

最新推荐文章于 2023-11-29 13:54:17 发布

talent210

最新推荐文章于 2023-11-29 13:54:17 发布

阅读量217

点赞数

分类专栏： JAVA-性能

本文链接：https://blog.csdn.net/talent210/article/details/95763015

版权

JAVA-性能专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1，必须要有主键字段

如果不设主键，INNODB会生成一个隐藏列，作为自增主键。

2，主键用什么类型？

用数值型且顺序自增。不要用UUID。因为主键索引使用B+TREE，每次插入新的就，记录会顺序天井到当前索引节点的后续位置，当一页写满，自动开辟一个新的页。如果不自增，可能会中间插入，引发页的分裂，产生表水平。而且数值型比较效率也比字符型快。

3，主键不推荐有业务含义

因为字段的业务含义随业务变化可能有变更。有业务含义的时候不一定就是自增的。

4，表示枚举的字段不要使用ENUM类型

用TINYINT 。

ENUM 的ORDER BY效率低。

如果枚举值是数值，有陷阱。例如 CREATE TABLE TEST(COL ENUM('0','1'));

INSERT INTO TEST VALUE (1);

查询总数：0

下面的写法才对INSERT INTO TEST VALUE ('1');

5,货币字段用什么类型

如果是整数 INT 如果可以是小数用DECIMAL 不要用FLOAT DOUBLE，因为FLOAT DOUBLE 用二进制存储，有一定的误差

6，时间字段的类型

1，VARCHAR。如果存储'YYYY-MM-DD HH:MM:SS‘ 需要自己格式化。而且做时间比较需要函数转化，无法使用索引

2，TIMESTAMP。该类型是4字节的整数。时间范围1970-2038. 2038年以后的无法用TIMESTAMP类型存储。有个优势就是自带时区信息。一旦系统中的时区发生改变。例如SET TIME_ZONE='america/new_york',会发现该字段的值自己会发生变更。

3，DATETIME 8个字节。时间范围1000-9999.存储的是时间绝对值，不带时区信息

4，BIGINT 8个字节。自己维护了。

7，不存储图片、音视频等大内容

实际使用中用文件系统存储。如HDFS。表中存放路径。

MYSQL内存临时表不正常TEXT BLOB这样的大数据类型。如果查询中包含这样的数据，在排序等操作时，不能使用临时表，必须使用磁盘临时表，导致查询效率慢。

BINLOG内容太多，会导致主从同步效率慢。而且网络资源也消耗多

8，字段要定义成NOT NULL

1，索引性能不好。MYSQL 难以优化引用可空列查询，它会使索引、索引统计、和值更加复杂。可空列需要更多的存储空间。还需要MYSQL 内存进行特殊处理。可空列被索引后，每条记录都需要一个额外的自己。还能导致MYISAM中固定大小的索引变成可变大小的索引

2，查询会出现不可预料的结果

例如：一张USER表有字段属性 name,age 其中name为索引
下面列举几个索引失效的情况
1. select * from USER where name=‘xzz’ or age=16；
例如这种情况：当语句中带有or的时候即使有索引也会失效。
2.select * from USER where name like‘%xzz’ ；
例如这种情况：当语句索引 like 带%的时候索引失效（注意：如果上句为 like‘xzz’此时索引是生效的）
如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP，MYSQL只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。最左原则
3. 值字段类型错误
select * from USER where name=123;（此处只是简单做个例子，实际场景中一般name不会为数字的）
例如这种情况：如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
4.如果mysql估计使用全表扫描要比使用索引快,则不使用索引（这个不知道咋举例子了）
5.假如上述将name和age设置为联合索引，一定要注意顺序，mysql联合所以有最左原则，下面以name，age的顺序讲下
（1）select * from USER where name=‘xzz’ and age =11；
（2）select * from USER where age=11 and name=‘xzz’；
例如上诉两种情况：以name，age顺序为联合索引，（1）索引是生效的，（2）索引是失效的
6. 字段不能使用函数
比如age为索引：select * from USER where age-1>11;
例如这种情况：索引失效，不要在索引上进行操作，否则索引会失效（是有类似时间转换的问题和上诉问题一样）
或例如： where date(create_at) = '2019-01-01'
7.WHERE字句的查询条件里有不等于号（WHERE column!=…），MYSQL将无法使用索引
8.在JOIN操作中（需要从多个数据表提取数据时），MYSQL只有在主键和外键的数据类型相同时才能使用索引，否则即使建立了索引也不会使用
9.在ORDER BY操作中，MYSQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。尽管如此，在涉及多个数据表的查询里，即使有索引可用，那些索引在加快ORDER BY操作方面也没什么作用。
6.如果某个数据列里包含着许多重复的值，就算为它建立了索引也不会有很好的效果。比如说，如果某个数据列里包含了净是些诸如“0/1”或“Y/N”等值，就没有必要为它创建一个索引。
7.如果条件中有or(并且其中有or的条件是不带索引的)，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
8.如果mysql估计使用全表扫描要比使用索引快,则不使用索引. 这是一个坑，实际如果遇到了，可以强制使用INDEX 测试下。select * from user force index(id) where id=123 and name='x' and age=12
force index影响的是type和rows
没加强制索引之前，走的是联合索引，加了force index之后单纯的使用id索引，区间变小，key_len远远小于没加force index之前的，扫描的rows远远小于之前的。
force index()指令可以指定本次查询使用哪个索引！一条sql只会用到一个索引，mysql优化器会计算出一个合适的索引，但是这个索引不一定是最好的。force index()指令可以避免MySql优化器用到了一个低效的索引。
9, 字符集不一致导致索引失效
SELCT A.* FROM A, B WHERE A.NAME=B.NAME
发现两个表的关联字段a.NAME和b.NAME并没有走索引,导致b表走全表扫描.
查询发现两张表的name字段其实都有索引,但是不走索引其实蛮奇怪,这样导致b表全表扫描,代价很大,再仔细检查发现
A表字符集为utf8mb4
B表字符集为utf8
alter table B modify column NAME varchar(320) character set utf8mb4 DEFAULT NULL