序言
在上一篇文章中主要讲了索引的底层实现。主要讨论了为什么加了索引以后，数据库的查询效率会加快？底层怎么实现的？这个问题。
那么本文主要想讨论下以下几个问题
- 怎么建立合适索引，建立索引有哪些需要规避的要点？
- 为什么有时候建立了索引也没啥子用？
以下是自己最近学习和使用的一些想法，希望大家可以互相交流，反正是技术交流哈哈。
首先抛出两个语句干货。大家可以先去自己的数据库执行检查下。
-- 查询冗余索引,看看是否有累赘索引
SELECT * from sys.schema_redundant_indexes;
-- 查询从未被使用到的索引，用都没用过的，留着做啥。
SELECT * from sys.schema_unused_indexes;
怎么建立合适索引，建立索引有哪些需要规避的要点
首先我们要知道，索引并不是越多越好。虽然索引用的很爽。
因为底层是B+树结构，我们在增，删，改操作中都会对B+树结构进行调整。
所以索引过多也会影响此类操作。主要考虑在where和order by 后面涉及的列建立索引。
同时，由于一条mysql在执行的时候，如果有多个索引命中，最终只能选择其中一个索引。
所以个人见解：
要点一：复合索引和单列索引相比，应优先使用复合索引。
针对建立复合索引之前，需要先做一个调研把哪个字段作为最左前缀。
调研可以从这几个方面去判断
1.有哪个字段是经常会使用到的，且大部分sql中都会有值。
举个例子：
user表里有id，name,age,address,sex等字段。
调研发现多数sql中都使用到了name这个字段作为查询条件。因此可建立复合索引idx_name_age_address(name,age,address)
此处使用到的是mysql里最常见的最左原则。为什么会有这个原则，和底层原理有关，在上一篇文章中也叙述了。
2.字段长度小的列放在左侧。因为字段长度小，每一页能存储的数据量就越大，IO性能越好，也就是越快找到目标数据。
3.如果创建的复合索引中某个字段中的值都是不同的，那么他的数据区分度就高，走索引效率就非常明显，考虑放在左侧。
这种复合索引比建立单列索引实用的多。
要点二：值比较稀少的列不建议使用索引。
比如上面例子中的sex字段。换句话说，建了索引也对查询效率起不了作用。那么为啥会有这种论断。
因为系统在执行一条sql的时候，会进行预测走这个索引和全表扫描哪个扫描的行数少。扫描行数越少，I/O操作次数越少。
而我们走索引的时候，会通过sex这个索引先查到主键索引，再通过主键索引来查找数据。也就是会走两次索引。也就是回表。
系统通过索引的区分度来判断，索引上不同的值越多，这个基数越大，那么走索引查询越有优势。
如果我们业务需要强制走某个索引查询的话。可以使用select * from user force idx(age) where address = '中国'
要点三：尽量建立覆盖索引
所谓覆盖索引就是此索引覆盖所有需要查询的字段的值。尤其是查询频繁的语句，优先考虑覆盖索引。上述例子中
建立了复合索引idx_name_age_address(name,age,address)
查询语句:select id,name from user where name = '张三' and age = '20' and address = '中国'
因为B+树的叶子节点存储的是主键+列值，最终还是要回表，就会比较慢。但覆盖索引要查询出的列和索引是对应的，不需要做回表操作。
我们往往会因为偷懒或者想让sql能够复用，而使用select * from 的写法。但这种无法使用覆盖索引，也会消耗更多的CPU和IO
判断建立的索引是否有效
要点一：判断是否符合了最左前缀原则。
以上述例子中为例：
建立了复合索引idx_name_age_address(name,age,address)
查询语句:select * from user where age = '20' and address = '中国'
此语句因为最左前缀为name,但是name没有作为条件查询，无法使用索引。
要点二：查看where 子句左边是否有进行函数，算术运算或者其他表达式运算
select * from user where age+1 = 20 -- 不能使用索引
select * from user where age = 20 -1 -- 能使用索引
要点三：尽量避免在WHERE子句中使用!=或<>操作符，将打算加索引的列设置为 NOT NULL。否则将导致引擎放弃使用索引而进行全表扫描。
要点四：查询语句有多个索引，数据库选错索引。
数据库使用采样的方式来预测各个索引的基数。既然是采样，就有可能失误。
若系统判断当前索引基数过小，就不走索引，直接全表扫描。
所以不要对每个单列建立索引。索引需要的是有效，而不是多。
要点五:联合索引的第一个索引使用了范围查找导致失效
以上述例子中为例：
建立了复合索引idx_name_age_address(name,age,address)
查询语句:select * from user where name in ('张三'，‘小敏’) and age = '20' and address = '中国'
在此sql中,name字段会用到索引，但是后面的age和address索引失效。
因为一个 SQL 只能利用复合索引中的一列进行范围查询。
所以如果有范围查询的字段可以放在复合索引的右边。不要使用not in
not in 通常会让索引失效，可以用left join 或者 not exist 替代。
要点六:排查是否使用了子查询
尽量不使用子查询，可用join替代。
因为子查询的结果集会存储在临时表中，而临时表是没有索引的。同时也会消耗过多的CPU和IO。也是慢sql的一部分原因。
总结
其实mysql使用B+树作为自己的数据结构，它是非常强大的。千万数据量的大表，如果索引能够使用的合适，也完全能够支撑。
索引的重要性不言而喻,甚至在做开发设计评审时，索引也应该作为数据库设计里重要的一项进行评审。
如果文中有说的不正确的地方，希望大家能够互相交流，一起进步。
网友评论