MySQL技术大全冰河

转载

mob6454cc7ccdfc 2024-07-16 23:11:29

文章标签 MySQL技术大全冰河字段数据 MySQL 文章分类 MySQL 数据库

1.EXPLAIN命令

如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。
借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。

2.php代替复杂处理

Sql要做一些很复杂的处理的话，可以分出来交给后端语言去完成。特别是一些case when 什么的。

3.字段设计为非空

非null字段的处理要比null字段的处理高效些！且不需要判断是否为null。MySQL中每条记录都需要额外的存储空间，表示每个字段是否为null。因此通常使用特殊的数据进行占位，比如int not null default 0、string not null default ‘’。就是每个字段都给他默认值，给个0 好，‘’也好。因为判断数据是否Null比前者慢。

4. order by字段建立索引

当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。

但是如果我们对该字段建立索引alter table 表名 add index(字段名)，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）

5. join表的On字段建立索引

对join语句匹配关系（on）涉及的字段建立索引能够提高效率

6. 索引覆盖，对常查询的字段建立索引

什么是索引覆盖，就是一条sql里面用到的字段，比如select后面的，order by 的，group by的等等用到的字段，只要有一个没建索引的，就会进行全表扫描。
所以我们进可能对所有常用的数据建立索引，但是不要想着为每个字段建立索引，那跟没建也没啥区别，还多了索引记录的更慢了。
注：状态值（例如性别、支付状态等状态值字段）不容易触发到索引，就可以不建了

7. like查询，不能以通配符开头

例如，在title字段建立了索引的前提下。
select * from article where title like ‘mysql%’;会触发索引。
select * from article where title like ‘%mysql%’;不会触发索引。

8. 在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键

InnoDB使用聚集索引，各条数据记录按主键顺序存放。
因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置。
如果使用非自增主键（如果身份证号或学号等），插入数据时，MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片。

9. 最左前缀匹配原则

mysql会一直从左向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
所以，写语句时，能命中索引的绝对条件写在前面，范围查询写在后面。