MySQL索引总结

发布时间:2020-05-15 18:51:31 | 来源: | 编辑整理:超管


如何判定是否需要创建索引?

1、较频繁地作为查询条件的字段
这个都知道。什么是较频繁呢?分析你执行的所有SQL语句。最好将他们一个个都列出来。然后分析,发现其中有些字段在大部分的SQL语句查询时候都会用到,那么就果断为他建立索引。

2、唯一性太差的字段不适合建立索引
什么是唯一性太差的字段。如状态字段、类型字段。那些只存储固定几个值的字段,例如用户登录状态、消息的status等。这个涉及到了索引扫描的特性。例如:通过索引查找键值为A和B的某些数据,通过A找到某条相符合的数据,这条数据在X页上面,然后继续扫描,又发现符合A的数据出现在了Y页上面,那么存储引擎就会丢弃X页面的数据,然后存储Y页面上的数据,一直到查找完所有对应A的数据,然后查找B字段,发现X页面上面又有对应B字段的数据,那么他就会再次扫描X页面,等于X页面就会被扫描2次甚至多次。以此类推,所以同一个数据页可能会被多次重复的读取,丢弃,在读取,这无疑给存储引擎极大地增加了IO的负担。

3、更新太频繁地字段不适合创建索引
当你为这个字段创建索引时候,当你再次更新这个字段数据时,数据库会自动更新他的索引,所以当这个字段更新太频繁地时候那么就是不断的更新索引,性能的影响可想而知。大概被检索几十次会更新一次的字段才比较符合建立索引的规范。而如果一个字段同一个时间段内被更新多次,那么果断不能为他建立索引。

4、不会出现在where条件中的字段不该建立索引


下面分享Mysql是否需要建立索引的九大场景:
1、表的主键、外键必须有索引;
2、数据量超过300的表应该有索引;
3、经常与其他表进行连接的表,在连接字段上应该建立索引;
4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
5、索引应该建在选择性高的字段上;
6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替:
a、正确选择复合索引中的主列字段,一般是选择性较好的字段;
b、复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
c、如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
d、如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
e、如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引;
8、频繁进行数据操作的表,不要建立太多的索引;
9、删除无用的索引,避免对执行计划造成负面影响;


MySQL优化:
现在知道的有三个方面:
(1) 选择的数据引擎上。如果不需要事务的支持,强烈建议用MyISAM数据引擎,查询、插入、删除都快些。
(2) 另一方面是从SQL语句上提高。建立索引后,尽量少用子查询in、exist、is NULL、like、!=、不要对where语句后面的字段使用函数等,这些操作可能会导致索引不可用而全表扫描;不要用select *返回没用的字段、先where查询再join等。
(3) 数据缓存memcached,没怎么接触过。

再写点个人经验吧:
1.查询时,能不用* 就不用,尽量写全字段名。
2.索引不是越多越好,每个表控制在6个索引以内。范围where条件的情况下,索引不起作用,比如where value<100
3.大部分情况连接效率远大于子查询,但是有例外。当你对连接查询的效率都感到不能接受的时候可以试试用子查询,虽然大部分情况下你会更失望,但总有碰到惊喜的时候不是么...
4.多用explain 和 profile分析查询语句
5.有时候可以1条大的SQL可以分成几个小SQL顺序执行,分了吧,速度会快很多。
6.每隔一段时间用alter table table_name engine=innodb;优化表
7.连接时注意:小表 jion 大表的原则
8.学会用explain 和 profile判断是什么原因使你的SQL慢。
9.查看慢查询日志,找出执行时间长的SQL试着优化去吧~~

MySQL 对于大表,要怎么优化
分区表,加合适的索引,数据拆分,可以把数据按某种规则拆分(比如时间,比如按数值),也可以是把表的字段拆分,比如表的字段过多,就可以分成多个表,然后做关联

我给的顺序是:
1. 优化shema、sql语句+索引
Why: 再好的MySQL架构也扛不住一个频繁的垃圾查询。不合理的schema设计也会导致数据存取慢。索引的作用不必多说,但如innodb下,错的索引带来的可能不只是查询变慢而已。
How: 设计阶段就需要预计QPS及数据规模,参考业务场景对数据的要求,合理设计表结构(参考mysql在线DDL问题),甚至违反设计范式做到适当冗余。生产环境分析慢日志,优化语句。索引的设计需要知道索引是怎么用的,比如innodb的加锁机制。
When: 这个不仅仅是第一个要考虑的,而应该是需要持续去优化的。特别是要参考业务。但实际环境中如果是这个的问题,那一般比较幸运了,因为一般已经优化过很多了。实际中遇到的一般是更深的问题。
2. 缓存
缓存没有那么简单。
缓存对于应用不是完全透明的,除非你用Django这种成熟框架,而且缓存粒度很大,但实际。。。像python,最少也得加几个装饰器。
如何保证缓存里面的数据是始终正确的?写数据前失效缓存还是写数据后?
缓存挂了或者过冷,流量压到后端mysql了怎么办?
缓存也不是万能的。写多读少,命中率会很低。
How: memcache用做缓存,redis用于需要持久化的场景。(redis能不能完全取代memcache?呵呵。。)
还可以使用mysql自带的query cache,对应用基本完全透明。但会受限于本机。而且只缓存查询结果,mc和redis可以缓存一些加工后的数据。
而且数据量大、QPS大的情况下,也需要考虑分片及HA的问题。如果有一个数据过热,把一个节点压垮了怎么办?
When: 基本上大多数读多写少的场景都能用,写多的情况下可能需要考虑考虑。
3. 复制及读写分离
Why: 这个其实是大多数场景下都必须的。因为复制可以实现备份、高可用、负载均衡。就算嫌麻烦不做负载均衡,那备份下总是要的吧?既然已经备份了,何不加个LVS+HAProxy做下HA?顺便稍微修改下应用,读写分离也就成了。
How: 节点少的情况下,主备。前面加Keepalived+HAProxy等组件,失效自动切换。读写分离可能需要修改下应用。
节点多的情况下,一是考虑多级备份,减轻主的压力。其次可以引入第三方组件,接管主节点的备份工作。
主主不是很推荐。一是需要考虑数据冲突的情况,比如错开id,同时操作数据后冲突解决。其次如果强一致会导致延迟增加,如果有节点挂了,需要等到超时才返回。
When: 主备几乎大多数场景。甚至不论数据大小。高可用对应用透明,为啥不用?主主麻烦,建议先用切分。
4. 切分
包括垂直切分和水平切分,实现方式上又包括分库、分表。
虽然有些难度,但还是推荐常用的。
Why: 垂直切分保证业务的独立性,防止不同业务争抢资源,毕竟业务是有优先级的。
水平切分主要用于突破单机瓶颈。除了主主外,只有切分能真正做到将负载分配下去。
切分后也可对不同片数据进行不同优化。如按时间切分,超过一定时间数据不允许修改,就可以引入压缩了,数据传输及读取减少很多。
How: 根据业务垂直切分。业务内部分库、分表。一般都需要修改应用。除分表外,其余实现不是很复杂。有第三方组件可用,但通用高效又灵活的方式,还是自己写client。
When: 垂直切分一般都要做,只不过业务粒度大小而已。
分库有是经常用的,就算当前压力小,也尽量分出几个逻辑库出来。等规模上去了,很方便就迁移扩展。
水平拆分有一定难度,但如果将来一定会到这个规模,又可能用到,建议越早做越好。因为对应用的改动较大,而且迁移成本高。