MySQL性能优化之索引（二）

一、索引的数据结构和磁盘的存取原理

       1》不使用顺序查找，因为顺序查找比较慢，通过特定数据结构的特点来提升查询速度，这种数据结构就是可以理解成索引。

        2》索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作，为了尽量减少磁盘I/O。磁盘往往不是严格按需读取，而是每次都会预读，而且内存和磁盘以页为单位交换数据，所以在读取的数据不存在内存中时，会从磁盘中读取一批数据（页）到内存中。

        3》不管在哪种程序优化上，要想快速提高性能，直接将常用的、少变更的数据直接读取到内存中，使用的时候直接在内存上读取，而不去磁盘上读取，减少I/O操作，这样就能使程序快上10倍以上，但由于内存容量的限制，也不可能将所有的数据都放内存中。

二、建立索引目的：

    1》加快查询速度，使用索引后查询有迹可循。

    2》减少I/O操作，通过索引的路径来检索数据，不是在磁盘中随机检索。

    3》消除磁盘排序，索引是排序的，走完索引就排序完成。

三、MySQL索引分类

        1》普通索引：最基本的索引，没有任何限制

        2》唯一索引：与“普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值

        3》主键索引：它是一种特殊的唯一索引，不允许有空值。

        4》全文索引：仅可用于MyISAM表，针对较大的数据，生成全文索引很耗时耗空间

        5》组合索引：为了更多的提高mysql效率可建立组合索引，遵循‘最左前缀’原则

        6》覆盖索引：就是直接走的索引，直接在内存中就拿到值，不需要查询数据库。如分页就要走覆盖索引，因为性能比较高

        7》聚簇索引：主键就是聚集索引，聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能有一个聚簇索引。因为有存储引擎实现索引，所以并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。

        8》非聚簇索引：二级索引叶子节点保存的不是指行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行。InnoDB对主键建立聚簇索引。如果不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，DBMS都会以聚簇索引的形式来存储实际的数据，它是其它二级索引的基础。

优化注意点：

    1》索引其实就是一种归类方式，当某一个字段属性都不能归类，建立索引后是没什么效果的，或归类就二种（0和1），且各自都数据对半分，建立索引后的效果也不怎么强。

    2主键的索引是不一样的，要区别理解。

    3当时间存储为时间戳保存的可以建立前缀索引。

    4.在什么是字段上建立索引，需要根据查询条件而定，不要一上来就建立索引，浪费内存还有可能用不到。

    5.大字段（blob）不要建立索引，查询也不会索引。

    6.唯一索引一定要小心使用，它带有唯一约束，由于前期需求不明等情况，可能造成我们对于唯一的误判。

    7.写入比较频繁的时候，不能开启MySQL的查询缓存，因为在每一次写入的时候不光要写入磁盘还得更新缓存中的数据。

    8.其实建索引的原理就是将磁盘I/O操作的最小化，不在磁盘中排序，而是在内存中排好序，通过排序的规则去指定磁盘读取就行，也不需要在磁盘上随机读取。

    9.由于磁盘整理磁盘碎片，所有有的时候我们也可以通过建立聚集索引来减少这一类的问题。

    10.当一个表中有100万数据，而经常用到的数据只有40万或40万以下，是不用考虑建立索引的，没什么性能提升。

    11.使用count统计数据量的时候建议使用count(*)而不是count(列)，因为count(*)MySQL是做了优化的。

    12.二次SQL查询区别不大的时候，不能按照二次执行的时间来判断优化结果，没准第一次查询后又保存缓存数据，导致第二次查询速度比第二次快，很多时候我们看到的都是假象。

    13.什么时候开MySQL的查询缓存，系统（写多，读少）、SQL优化测试，建议关闭查询缓存，系统（写少，读多），建议开启查询缓存。

    14.Explain执行计划只能解释SELECT操作。

    15.查询优化可以考虑让查询走索引，走索引能提升查询速度，索引覆盖是最快的

    16.子查询比join快，虽然规律不绝对，但对大表多数有效

    17.where条件多条件一定要按照小结果集排大结果集前面

    18.尽量避免大事务操作，提升系统并发能力，有时无法避免，改用定时器延迟处理

    19.使用UNION ALL替换OR多条件查询并集。

    20.在大数据表删除也是一个问题，避免删除过程数据库奔溃，可以考虑分配删除，一次删除1000条，删完后等一会继续删除

    21

四、常用索引的地方

    1》主键的聚集索引

    2》外键索引

    3》类别只有0和1就不要建索引，没有意义，对性能没有提升，还影响写入性能

    4》用模糊其实是可以走前缀索引

五、不适合建立索引：

    1》频繁更新的字段不适合建立索引

    2》where条件中用不到的字段不适合建立索引，都用不到建立索引没有意义还浪费空间

    3》表数据可以确定比较少的不需要建立索引

    4》数据重复且发布比较均匀的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别

    5》参与列计算的列不适合建立索引

 

使用Explain

https://blog.csdn.net/why15732625998/article/details/80388236