Mysql索引及优化（持续更新）

为什么80%的码农都做不了架构师？>>>   

当人们谈论索引的时候，如果没有特别指明类型，多半说的是B-Tree索引（Mysql的InnoDB使用的B+Tree）。B-Tree意味着所有的值都是按照顺序存储的，并且每一个叶子页到跟的距离相同。

索引的优点：

索引可以让服务器快速地定位到表的指定位置。但是这不是索引的唯一作用。最常见的B-Tree索引，按照顺序存储数据，所以MySQL可以用来order by 和group by操作。因为数据是有序的，所以B-Tree也就会将相对的列值都存储在一起。

索引的三个优点：

1.索引大大减少了服务器需要扫描的数据量。

2.索引可以帮助服务器避免排序和临时表。

3.索引可以将随机I/O编为顺序I/O。

1. 什么时候是有效的索引：

假设现在有一个人员信息表，字段包括id，姓，名，生日，性别等。

表中的信息有：1,guo，jing，1678-06-24，男

                        2,sima,xiangru,1978-08-06,男

                        3,dongfang，bubai，1579-07-02，男

                        4,sima，yi，1479-08-04，男

                        5,hua，mulan，1728-09-10，女...

建立联合索引：key(姓，名，生日).

       索引有效：

• 全值匹配：全值匹配值得是和索引中的所有列进行匹配，例如查找姓名为司马相如，日期是1978-08-06的人。（where xing='sima' and  ming='xiangru' and date='1978-08-06'）
• 匹配最左前缀：查找所有姓 sima 的人，即只使用索引的第一列。
• 匹配列最前缀：可以匹配某一列的值的开头部分，用于姓开头为sima的，这里也只使用索引的第一列。
• 匹配范围值：查找姓在dongfang和sima之间的人，也只是用了范围索引的第一列。
• 精确匹配某一列并范围查询匹配另外一列：查找所有叫sima的人，名字以“y”开头的人。即索引的第一列全匹配，第二列范围匹配。
• 只访问索引的查询：即查询只查询索引里包含的字段，无需访问额外的数据行。叫做覆盖索引（后面会详细说）

      无效索引：

• 不是按照索引的最左列开始查找，则无法使用索引。（名，生日）（名）（生日）均不能使用到索引
• 不能跳过索引中的列。比如查找姓为sima，生日为1978-08-06的人，不指定名，只能使用索引的第一列。
• 某个列使用范围查询。比如查找姓开头为si，名字为相如，生日为1978-08-06的人，因为姓使用了范围查询，所以只能使用索引的第一列

        高性能的索引策略：

        1.独立的列：指索引列不能是表达式的一部分，也不能是函数的参数。

        例子：

        select actor_id form actor where actor_id +1=5;

        select ... where to_days(first_time)-to_days(end_time) <=7

        2.多列索引：不应该建立多个索引，假如actor_id和film_id分别建立独立索引，当语句进行查询条件均有这两个条件，那么成为“索引合并”，在and条件下，需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。在or条件下，会耗费大量的cpu和内存资源在算法的缓存上、排序和合并操作上。所以尽量避免这种情况。

        如果在explian看到索引合并，Extra：Using union，应该好好检查一下查询和表的结构。

        3.选择合适的索引列顺序：

        将选择性最高的列放在索引的最前列，这个在某些场合是很有用的，但是一定注意，重要性：随机I/O>排序>选择性高的列。当用到索引才是重要的，如果索引都无法用到，何谈选择性。当不需要考虑排序和分组时，将选择型最高的列放在前面通常是很好的。这时候索引的作用只作用于优化where条件查找的。

        4.聚簇索引：这个我还没有理解，当我做好B-Tree以及B+Tree理解时在再补上。

        5.覆盖索引：如果一个列包含（或者说是覆盖）所有需要的查询的字段的值，我们就成为覆盖索引。如果使用explian 查看Extra：Using index 说明使用了覆盖索引。

           

 

转载于:https://my.oschina.net/WEguo/blog/1541358