最近由于“噶韭菜市场”行情逐渐向好，公司数据量较大。写了几个处理数据的脚本，经简单统计要查询的数据有1千多万条，需要操作的数据有56万条左右。因此代码及sql需要做优化，用到索引，发现自己对其了解并不深刻，花费几小时时间，又看了下相关文章，并加以整理方便以后查阅。
觉的内容过多，看不下去？那先来个总结：
- 是否建索引？
对于小型表或报表查询处理大多数或所有行的大型表的查询，索引不太重要。当查询需要访问大多数行时，顺序读取比通过索引更快。顺序读取可以最大限度地减少磁盘搜索，即使查询不需要所有行也是如此。只有数据较大，并且需要访问其中一部分数据的时候，索引才会显得比较重要。
- 简单说下何时建索引？
查询频率高、分组、排序、联合（又叫组合、复合）查询。具体情况还是往下读读吧~
本文声明：选择索引的最终目的是为了使查询的速度变快。下面给出的是一些基本的准则，但不能拘泥于以下准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。
###正文### --- 关于索引，需要注意：
1. 对于经常存取的列避免建立索引；
2. 在不同值较少的字段上不必要建立索引，如性别、国籍字段；
3. 当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系
4. MySQL老的版本不支持函数索引（MySQL 5.7引入了Generated Column，MySQL 8.0.13 以及更高版本支持函数索引）；
5. 使用不等于（!= 或者<>）的时候MySQL 无法使用索引，负向查询（not , not in, not like, <>, != ,!>,!< ） 不会使用索引；
6. 过滤字段使用了函数运算后（如abs(column)），MySQL 无法使用索引；
7. Join 语句中Join 条件字段类型不一致的时候MySQL 无法使用索引；
8. 使用LIKE 操作的时候如果条件以通配符开始（ '%abc...'）MySQL 无法使用索引；
9. 使用非等值查询的时候MySQL 无法使用Hash 索引；
10. 定义为text和image和bit数据类型的列不应该增加索引,
11. MyISAM 存储引擎索引键长度总和不能超过1000 字节；
12. BLOB 和TEXT 类型的列只能创建前缀索引；
13. 索引的数目不宜太多
- a、每创建一个索引都会占用相应的物理控件（磁盘空间），索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
- b、过多的索引会导致insert、update、delete语句的执行效率降低；
14. 在经常用作过滤器(查询频率高)的字段上建立索引；
15. 在SQL语句中经常进行GROUP BY(分组)、ORDER BY(排序)、DISTINCT和UNION（联合）的字段上建立索引。(单独order by 用不了索引，索引考虑加where 或加limit)
16. 用于联接的列（主健/外健）上建立索引；
17. 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定；
18. 缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引（又叫聚簇/聚集/聚类索引）
如：含有有限数目（不是很少）唯一的列；进行大范围的查询；充分的利用索引可以减少表扫描I/0的次数，有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中，也取决于DBA的所设计的数据库结构。
19. 若在实际中，需要将多个列设置索引时，可以采用多列索引
如：某个表(假设表名为Student)，存在多个字段(StudentNo, StudentName, Sex, Address, Phone, BirthDate)，其中需要对StudentNo,StudentName字段进行查询，对Sex字段进行分组，对BirthDate字段进行排序
此时可以创建多列索引 CREATE INDEX index_name ON `Student` (`StudentNo`, `StudentName`, `Sex`, `BirthDate`);
index_name为索引名在上面的语句中只创建了一个索引，但是对4个字段都赋予了索引的功能。（创建多列索引，需要遵循BTree类型，即第一列使用时，才启用索引。在上面的创建语句中，只有mysql语句在使用到StudentNo字段时，索引才会被启用。）
如: select * from Student where StudentNo = 1000; ### 使用到了StudentNo字段，索引被启用。
可以使用explain检测索引是否被启用，如:explain select * from Student where StudentNo = 1000;
20. 选择唯一性索引：唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。
21. 尽量使用数据量少（长度短）的索引，如果索引的值很长，那么查询的速度会受到影响。
例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
22. 尽量的扩展索引，不要新建索引。
例如，表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
23. 尽量使用前缀来索引，如果索引字段的值很长，最好使用值的前缀来索引。
例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
24. 删除不再使用或者很少使用的索引。表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。
数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
25. 最左前缀匹配原则，非常重要的原则。
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
26. =和in可以乱序。
例如，a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
27. 尽量选择区分度高的列作为索引。
区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就 是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录
可以了解下sql查询语句执行过程及其运行原理，然后再来看索引加深思考。地址：https://blog.csdn.net/sinat_32176267/article/details/83280206
索引的原理。地址：https://www.cnblogs.com/bypp/p/7755307.html
索引的创建详解、删除、优缺点。地址：https://www.cnblogs.com/chenshishuo/p/5030029.html
查看表是否使用索引。地址：https://www.cnblogs.com/liyasong/p/mysql_index.html
本文参考的文章链接还有：
http://book.51cto.com/art/201012/240955.htm
https://www.cnblogs.com/gaoyuechen/p/8067450.html
https://www.cnblogs.com/helloworldlx/p/8651583.html
*** 原创不易,感谢以上文章的作者整理分享。***
3738
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
