## 避免使用select * (外包项目 select * 很好用) ### 缺点 1. 会导致覆盖索引失效 2. 当表字段数量多时, 会查询出很多无效字段, 如果我们只需要其中 3、5个字段时，会浪费数据库资源，增加数据传输时间 ## 优点 简单方便 对于缺点一， 确定本表或者本条sql用不到覆盖索引就无所谓了， 特别是外包项目，很多表都不带加索引的。 对于缺点二， 如果像分页查询， 这点数据的资源消耗可忽略了 最重要的是，某些外包项目，需求更改频繁，极其频繁，select*的开发效率远远大于上面俩缺点 ## 最左前缀原则 最左前列开始并且不跳过索引中的列， 如：你家是三层楼，你得按照 1、2、3层的方式去第三层，不能跳过1去2、3，也不能跳过2直接从1到3 联合索引在底层 B+树 上是先从左列开始排序，比如 a、b、c 三列， 先排序a，当a相同时排序b，依次排序。索引查询时，跳过a列去查询，这时候b实际是相当于无序的，没法去使用此联合索引查询。 ## like 左边加% 不走索引 like "%wang"， 只要左边有% 就不会走索引 字符串当索引列时，会比较字符串的每个字符进行排序，第一个相同再比较第二个，依此类推。也就是字符串的索引列都是根据每个字符排好序的，跳过前边的索引字符时，索引列就没法排序，无法使用索引。 like "%wang" 这种就相当于违反联合索引中最左前缀原则。 ## 小表驱动大表 小表驱动大表，也就是说用小表的数据集驱动大表的数据集。 假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。 这时如果想查一下，所有有效的用户下过的订单列表。 可以使用in关键字实现： select * from order where user_id in (select id from user where status=1) 也可以使用 exists 关键字实现： select * from order where exists (select 1 from user where order.user_id = user.id and status=1) 前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。 为什么呢？ 因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。 而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。 这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。 总结一下： * in 适用于左边大表，右边小表。（oracle in 限制不能超过1000个， MySQL 是4M） * exists 适用于左边小表，右边大表。 不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。 ## limit 如查询用户首单时间时，可以增加 limit 1 表示只返回一条 如果是深度分页, 可以分页条件查询的sql写在子查询中 只返回id, 在外边根据id在手动查询数据，这样可以大量减少回表，提高些性能。 mysql分页是：把当前分页前的数据也查询出来，然后得到当前分页的数据，前边的再抛弃掉。如：查询第100页的数据，那么mysql会把前边的99页和当前100页的数据都查询出来，然后再放弃前边的99页，只留下需要的第100页面数据。 ``` 请使用5.7以上版本, 5.5 会非常慢 120万数据情况下 使用子查询, 先查询到数据的id, 再查询其他字段, 在都有索引的情况下快了 0.359 秒 SELECT * FROM sys_user WHERE id IN ( SELECT t.id FROM ( SELECT id FROM sys_user WHERE `status` = 0 ORDER BY create_time DESC, id DESC LIMIT 826163, 10 ) AS t ) ORDER BY create_time DESC, id DESC; 直接分页 2.009秒 SELECT * FROM sys_user WHERE `status` = 0 ORDER BY create_time DESC, id DESC LIMIT 826163, 10 ``` ## union all 和 union **Union**：对两个结果集进行并集操作，**不包括****重复行**，同时进行默认规则的排序； **Union All**：对两个结果集进行并集操作，**包括****重复行**，不进行排序； 因为没有去重和排序，union all 效率比union高， 如果确定并集中不包含重复数据可用 union all ## 覆盖索引 如果返回的字段不在索引中, 会进行回表(去主键索引中获取其他要返回的字段) 覆盖索引就是所有需要的字段在索引里(联合索引需要注意字段顺序)都包含了, 则不需要回表了 extra 有 Using index 表示使用了覆盖索引 ## group by 先使用 where 把数据缩小范围在分组，能在where用的条件不要放在having中 Where是在结果返回**之前**起作用的，Where中**不能使用聚合函数。** Having是在结果返回**之后**对查询结果进行的过滤操作，在Having中**可以使用聚合函数** 注意当有联合索引时, group by 的顺序要和 联合索引一至, 否则也用不上联合索引, 而且像这样的条件 date_format(create_time,'%Y-%m-%d') 也可能会导致查询速度慢。都用到了联合索引, 但是 date_format extra 是 ``` Using where; Using index; Using temporary; Using filesort ``` 不加 date_format extra 是 ``` Using where; Using index ``` 速度影响也很大, 导致出现了临时表和排序.(索引是有序的了, 不应该再而外排序) ## 尽量避免在索引列使用 or 、 in、!= 并不是用了 or in 等就不用索引了，而是**MySQL会预估查询结果数量，判断全表查询和使用索引那种方式更加高效**，当 or in 等查询的结果集在总数据中占比过大，就有可能使用全表查询了。 ## Join 表的数量限制 阿里的Java规范手册里强制要求 join 不超过三张表 ## 设置合理的字段类型 像账号状态，审核状态，性别，这种枚举类可以使用 tinyint（） 长度固定的字符串字段，用char类型 长度可变的字符串字段，用varchar类型。 金额字段用decimal，避免精度丢失问题。 ## 索引创建规则 * 经常与其他表进行连接的表，在连接字段上应该建立索引 * 不要对有大量重复的值的字段建立索引（像性别这种字段） * 索引应该建在小字段上，对于大的文本字段，text类型字段，不要建索引 * 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引 * 如果复合索引中的字段经常单独出现在Where子句中，则分解为多个单字段索引 * 频繁进行数据操作的表，不要建立太多的索引 * 不要在索引上进行任何操作，例如对索引进行（计算、函数、类型转换），索引失效 * SQL传参类型要和表字段类型一致, 防止MySQL隐式转换导致索引失效 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。 注：如果是做外包，那种小企业后台管理，工期紧，任务重，上面的内容可以当做不知道，**干完要紧**。干完了才有时间学习或者玩耍。