1.索引注意事项
- 索引的建立应该结合具体的业务，不论表多大多复杂，如果业务仅仅对一个字段作为限制条件查询，则单列索引就可以。如果查询涉及多个字段且经常需要联表，则常是在各个查询条件字段的交集上建立复合索引。细节结合业务；
- like左匹配模式可以走到索引，但是同><和between一样是range类型的索引，复合索引走到这里就会停止。对于走到范围索引的字段如果锁定数据范围过大(超过一定百分比),此时会锁定前边字段筛选出的所有数据，即此相当于此范围索引没走到；
- 对于text和很长的varchar必须使用前缀索引，因为不限制长度的索引大且慢甚至不符合规则而无法建立。前缀索引的选择性应该尽可能接近完整列的选择性别（对于文本字段建立索引还有一个策略是模拟哈希索引，求每个字段内容的哈希值，对哈希值所在的字段加索引）:
-- 选择性计算
select count(distinct left(city,3))/count(*) as sel3,
count(distinct left(city,4))/count(*) as sel4,
count(distinct left(city,5))/count(*) as sel5,
count(distinct city)/count(*) as sel
from word_map_table;
-- 前缀索引语法：单位是字节
alter table word_map_table add key (city(4));
in (a,b,c)走的不是rang类型索引，而是“多值相等”，复合索引走到这里不会停止；但是要注意不要连续使用多个“多字相等”，诸如in (a,b,...10个) in (1,2,3,4...10)排列组合会有一百种；- 覆盖索引，即索引可以覆盖到所查询的字段，此时直接可以在索引中取值。“非覆盖索引”即使查询的字段数量一样，但也会比前者慢，因为需要回表取值，此时可以使用延迟关联；
- 延迟关联，即先根据查询条件查询数据主键id集合，然后根据id获取结果集；
explain
select oute.username,oute.sync_bbs
from XXX oute
inner join
(
select id
from XXX inne
where inne.username in('女士','李娜','先生') and sex in('FEMALE','MALE')
)tem
on oute.id=tem.id;
- 索引应该单独放在比较符号的一侧，如果是表达式的一部分，则走不到索引：
where a=1; -- 可以走到索引
where a-1=0; -- 走不到索引；
- 选择性低的字段，如果每次查询都用到，则应该作为符合索引的第一项，比如性别、通讯工具是否在线等等；
- InnoDB 引擎使用
delete from table_name时一行一行清空表，因此对于大量数据的表使用truncate table table_name;
- 等值传播
explain extended
select *
from xxxlows_rfnd fr
inner join xxxetails od
on fr.ord_id=od.ord_id
where od.ord_id=2819460;
show warnings;
-- 重构的查询
/* select#1 */
...
where ((`od`.`ord_id` = 2819460) and (`ent_portal`.`fr`.`ord_id` = 2819460))
2.相关操作
- 使用
explain可以查看查询计划；
show status like 'last_query_cost'可以获取上次执行sql语句的开销；show (full) processlist可以看到当前那些 用户/主机 运行了哪些sql语句，这些sql语句运行了多久并且处于什么状态；- 使用
explain extended+show warnings可以查看重构后的查询；
- drap table table_name,delete from tab_name where con_XXX,truncate table tab_name的区别
1.drop table tab_name:删除表，后两者为删除表数据；
2.delete from tab_name where con_XXX:根据条件删除表中记录；auto-incrementing keys不会重置；执行后会计算影响的记录数，也因此比truncate table tab_name 慢；可以使用事务回滚操作；仅仅是行锁
3.truncate table tab_name:清空表，重置auto-incrementing key字段为1，保留索引；
