mysql经典

1.问：什么是索引?
答：
    （1）.索引是一种高效获取数据的存储结构，例：hash、 二叉、 红黑、B+Tree、B Tree。
2.问：mysql索引的实现原理?
答：
    参考博文地址：https://www.cnblogs.com/wlwl/p/9465583.html
    若仅仅是select * from table where id=1,上面三种算法可以轻易实现，但若是select * from table where id>1, 
    就不好使了，它们的查找方式就类似于"全表扫描"，因为他们的高度是不可控的，B+Tree的高度是可控的。

    Myisam引擎(非聚集索引)
        （1）.若以这个引擎创建数据库表Create table user （…..），它实际是生成三个文件：
　　              user.myi   索引文件     user.myd数据文件     user.frm数据结构类型。
        （2）.当我们执行  select * from user where id = 1的时候，它的执行流程。
　　　　        (1)查看该表的myi文件有没有以id为索引的索引树。
　　　　        (2)根据这个id索引找到叶子节点的id值，从而得到它里面的数据地址。(叶子节点存的是索引和数据地址)。
　　　　        (3)根据数据地址去myd文件里面找到对应的数据返回出来。

    Innodb引擎(聚集索引)（mysql默认引擎）
        （1）.若以这个引擎创建数据库表Create table user （…..），它实际是生成两个文件：
              user.ibd   索引文件        user.frm数据结构类型
　　        （2）.因为innodb引擎创建表默认就是以主键为索引，所以不需要myi文件。
        （3）.它与myisam最大的区别是将整条数据存在叶子节点，而不是地址。(叶子节点存的是主键索引和数据信息)
        （4）.如创建索引例如name，它就会另外创建一个以name为索引的索引树，(叶子节点存的是索引和主键索引)。
        （5）.你在执行select * from user where name = ‘叶双文’，他的执行过程如下：
　　　　        (1)找到name索引树
　　　　        (2)根据name的值找到该树下叶子的name索引和主键值
　　　　        (3)用主键值去主键索引树去叶子节点到该条数据信息

    B+Tree的特性
        (1)由图能看出，单节点能存储更多数据，使得磁盘IO次数更少。
        (2)叶子节点形成有序链表，便于执行范围操作。
        (3)聚集索引中，叶子节点的data直接包含数据；非聚集索引中，叶子节点存储数据地址的指针。

3.问：Innodb中，什么是聚集索引，非聚集索引，他们是什么关系？
答：
    参考博文地址：http://blog.51cto.com/9425473/1662626
    数据库中B+树索引分为聚集索引（clustered index）和非聚集索引（secondary index）.这两种索引的共同点是内部都是B+树，
    高度都是平衡的，叶节点存放着所有数据。不同点是叶节点是否存放着一整行数据。

    (1).聚集索引
        Innodb存储引擎表是索引组织表，即表中数据按主键顺序存放。而聚集索引就是按每张表的主键构造一颗B+树。
        并且叶节点存放整张表的行记录数据。每张表只能有一个聚集索引（一个主键）。
        聚集索引的另一个好处是它对于主键的排序查找和范围的速度非常快。叶节点的数据就是我们要找的数据。

    (2).辅助索引
        辅助索引（也称非聚集索引）。叶级别不包含行的全部数据，
        辅助索引的存在并不影响数据再聚集索引中的组织，因此一个表可以有多个辅助索引。当通过辅助索引查找数据时，
        innodb会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键。然后再通过主键索引找到一行完整的数据。

4.插入一条记录，聚集索引和非聚集索引是如何修改？
答：
    参考博文地址：https://www.cnblogs.com/Joe-T/p/4884838.html
    (1).索引修改的大致规则：
        1.对表的任何修改操作(UDI)，总会对表上的非聚集索引执行等价的操作。某些更新操作除外。
        2.对表的任何修改操作，都会先修改堆或者聚集索引，然后再修改非聚集索引。
        3.如果修改的数据行，正是过滤索引过滤掉的行(过滤索引的叶级页不包含的行)，则不会对过滤索引产生任何操作。

    (2).插入数据行
               对于聚集和非聚集索引的插入，新行(不管是数据行还是索引行)所包含的索引键列值就决定了它将被插入的位置。插入操作的可能来源有：
        1.直接的INSERT命令
        2.UPDATE导致的行移动(原来的地方已经容不下被更新后的行)，内部使用先DELETE,再INSERT的UPDATE策略。
        3.UPDATE导致的索引键列变更。索引行是有序的，行的索引键值变更会导致行在索引中的位置变更，从而需要移动到新位置。同样是先DELETE，再INSERT。
        4.如果当前索引的叶级(叶级在聚集索引中是数据页，非聚集索引中是索引页)没有空间存放插入的新行，则索引会发生页拆分(Page Split)。行在索引中的位置是有序的，
        所以当新行将要被插入的某个特定页没有可用空间时，就需要分配新页给索引。会先从已经分配的区中找是未使用的页，如果没有，则会分配一个新的统一区给索引，
        然后再使用新区中的页。

5.建立索引的标准/原则是什么？
答：
    参考博文地址：http://www.cnblogs.com/chenhaoyu/p/8761305.html
    1．选择唯一性索引
        唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
        如果使用姓名的话，可能存在同名现象，从而降低查询速度。

    2．为经常需要排序、分组和联合操作的字段建立索引
        经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

    3．为常作为查询条件的字段建立索引
        如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

    4．限制索引的数目
        索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

    5．尽量使用数据量少的索引
        如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

    6．尽量使用前缀来索引
        如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

    7．删除不再使用或者很少使用的索引
        表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

    8 . 最左前缀匹配原则，非常重要的原则。
        mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，
        d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

    9 .=和in可以乱序。
        比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

    10 . 尽量选择区分度高的列作为索引。
        区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，
        那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条 记录

    11 .索引列不能参与计算，保持列“干净”。
        比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，
        显然成本 太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

    12 .尽量的扩展索引，不要新建索引。 
        比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

    注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

6.查看 SQL 是不是使用了索引？（有什么工具）？
答：EXPLAIN

参考博文地址：https://www.cnblogs.com/xuanzhi201111/p/4175635.html