数据库索引

数据库索引

索引结构

B树，B+树

InnoDB和MyISAM

聚簇索引和辅助索引

联合索引

问题：那么当查询条件为2个及以上时，我们是创建多个单列索引还是创建一个联合索引好呢？他们之间的区别是什么？哪个效率高呢？

如何查看一个SQL语句是否使用索引呢？使用EXPLAIN sql语句,eg：EXPLAIN SELECT * FROM table t where tid = 1;（这里面很多内容，但是最起码需要知道这个能看该SQL语句是否使用索引，以及使用了哪个索引）。通常用来回答：SQL语句调优（通过EXPLAIN查看SQL语句是否使用索引，如果不使用的话，调整SQL语句（这个地方就可以展开了，如何优化SQL语句?）或者建立相应的索引等）

如何优化SQL语句？

1. SQL语句中索引字段避免计算
2. 索引字段避免使用not
3. 索引字段避免使用 NOT NULL和NULL
4. 索引字段避免使用类型转换
5. 索引字段避免使用函数（比如字符串拼接，时间函数等）
6. 联合索引，要注意最左前缀原则

无关索引的优化

• varchar字段建立索引，要指定长度，因为varchar可能特别长，而一定长度可能就有较大的区分度了。
• select 的时候只select需要的字段
• 海量数据的话使用limit，限制查询条数，只要自己需要的
• 避免长事务操作，（因为牵扯到锁，最好不要特别长）

建立索引的一些建议：

• 表的主键，外键有索引（Innodb默认了吧）
• 数据量大的表应该有索引
• 经常与其他表连接的表的连接字段应该建立索引
• 经常出现在where子句的字段
• 索引应该建立在区分度（选择性）高的字段上。比如建在性别这个字段上意义不大
• varchar字段限制长度
• 联合索引的话选择性高的放在左边。包含字段应尽量少
• 频繁修改的表不建议多建索引。因为索引结构需要维护，会不断修改。代价大

SQL优化器：

• 数据量特别小的话，可能就不走索引，直接全表查询
• 索引的区分度不大的话，比如字段的某一个值占90%，而其他字段只占10%，可能也是全表了，不走索引的

多个单列索引在多条件查询时只会生效第一个索引！所以多条件联合查询时最好建联合索引！

最左前缀原则： 同时存在联合索引和单列索引（字段有重复的），这个时候查询mysql会怎么用索引呢？比如所 联合索引(a,b,c)和三个单独的索引a,索引b，索引c。这个时候MySQL如何使用索引呢？这个时候MySQL本身有查询优化器策略，当有多个索引可走时，MySQL会根据查询语句的成本来选择走哪条索引（当然不是所有满足的索引都执行一遍再选择最优。。。这样还选个锤子。SQL会有相关历史记录，以及其他的选择方式，这个只是估算成本，所以并不一定是最优的）。

使用联合索引时，最好是where的顺序跟联合索引的顺序一致，但是 不一致也可能用到索引，因为SQL的查询优化器会给语句优化成按照顺序来，但是如果你where中缺乏联合索引的某些列，那么联合索引后面的部门肯定是用不到的，跟顺序的是一样的。比如说联合索引(a,b,c)，SQL语句 where a= ? and b =? and c = ？ 这样的话会用到索引，如果sql语句是 where b=? and c=? and a=?这样的话 SQL优化器会优化成 where a= ? and b =? and c = ？ 所以同样可以使用索引。但是如果sqlwhere a=? and c=?，则肯定只能用到a，而b和c用不到。

联合索引的话出现 or可能会导致无法使用索引。比如 还是联合索引(a,b,c),如果SQL语句为where a=? or b=?,这样的话肯定无法使用索引的。

如果只是创建了3个单索引，索引(a),索引(b)和索引(c)，而没有联合索引的话，则where a=? and b=? and c=?只能使用一个索引（a）如果where a=? or b = ?,则a和b都会使用到。

当创建联合索引(a,b,c)时，相当于创建了索引(a),索引(a,b)和索引(a,b,c)