坚信细节决定成败,不要小看这些小优化哦!
一:只要是查询一行数据时尽量使用limit 1
下面的优化条件要根据实际开发场景来使用
1:[ 使用union all 代替 or]
[between代替in ,not in]
对字段进行表达式操作会导致索引失效
[这里的like 模糊查询要注意 %必须在查询条件的 右边 在左边索引会失效 (%XX%,%XX)]
但是如果业务需要 必须使用like %% 这时候防止索引失效,就得使用 覆盖索引;
覆盖索引在查询的时候 查询字段必须跟索引字段顺序一样 (必须匹配最左原则) ,查询字段不能超出建立的索引字段 否则会失效
二、建索引的几大原则
1、最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a 1="" and="" b=“2” c=""> 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
2、=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
3、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录
4、索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5、尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可
三、选择表合适存储引擎:
myisam:应用时以读和插入操作为主,只有少量的更新和删除,并且对事务的完整性,并发性要求不是很高的。
InnoDB:事务处理,以及并发条件下要求数据的一致性。除了插入和查询外,包括很多的更新和删除。(InnoDB 有效地降低删除和更新导致的锁定)。 对于支持事务的 InnoDB类 型的表来说,影响速度的主要原因是 AUTOCOMMIT 默认设置是打开的,而且程序没有显式调用 BEGIN 开始事务,导致每插入一条都自动提交,严重影响了速度。可以在执行 SQL 前调用 begin,多条 SQL 形成一个事物(即使 autocommit 打开也可以),将大大提高性能。