mysql的索引

　　之前一直使用mysql的索引，但是一直听他们说这里面有很多学问，所以花了很长时间来学习这个，发现这里面东西确实不少，值得研究。

　　首先，mysql的存储引擎分多种，其中用的最多的就是InnoDB和MyISAM，所以索引也分多种结构，我研究的是InnoDB的索引，以后也研究下另外一个，索引这个东西的作用是什么呢，大家应该都知道，他最大的作用就是优化查询效率，那如果没有索引的话，查询的效率是很慢的，InnoDB会为每个表创建一个主键索引，这个是每个表都有的，如果是根据主键查询速度很快，他会根据二分查找来查询数据（这个具体的查询我稍后会详细说），但是如果是根据非主键列来查询的话就会一个个的去比对了，那速度就慢死了。

　　在了解索引之前我们先来了解一下基本的东西：

　　　索引其实是一种数据存储方式的简称，他有很多的数据页组成，所谓数据页就是数据存储的一个单元结构，我们平时查询数据的时候都是整页整页的查询的，每个页里的数据结构是由多个数据节点组成的，数据节点其实就是一行数据的记录，他包括record_type（记录的类型），next_type（下一条地址的偏移量），data1,data2,data3...(具体的每列数据)，每页里面有多条这样的记录

　　

如图所示，每页数据包含页号和具体的记录，并且每个记录都是按照索引的值升序排列的，不过这里有个问题，每个页的数据最大16k，显然无法将所有数据放在同一个页，所以，当某页数据存满了，当再次重新插入时会插入新的页中，这里还有很重要的一点，就是上一页的索引值一定大于下一页的索引值，

如上所示，每个页之前有双向箭头标识下页和上页地址，并且从左向右索引值时升序排列的，不过这些数据在物理上其实并不挨着，想要查询效率高，必须要给他们建立目录，这个目录使用每个页中最小的索引值和页号组成，

　　

如上图所示，每页数据都会被目录记录下来，那么问题来了，目录放在哪里呢，mysql的专家想来想去发现还是放在一个页里吧，然后将他的record_type变成1，标识这是目录数据，然后也按照顺序排列就是下面这样

目录数据页也会有多页，然后他还会有一个最顶级目录页，这样的好处就是我们每次查询的时候只需要去顶级目录页查询，然后再查询下一级的目录，最终在最下面一级找到具体的数据，这就是我们平时说的Btree结构，我们查询数据最多查询n次，n就是树的高度，一般来说不会超过4，所以我们只需要4次的IO磁盘读取就可以获取到最终的结果。

现在基础的已经说完了，其实索引我们已经讲完了，这就时索引，使用bTree记录数据信息，然后实现高效的查询，上面的索引有点特殊，他是主键索引，我们平时自己创建的索引，复合索引时什么样子呢，其实是类似的：

　　我们举例说明：

　　现在创建一张表：table_a  有以下字段  a_name  ,  a_age，a_phone ,a_other,a_id 

　　我们建立一个复合索引名为：a_id_name_age  (a_id,a_name,a_age),这个复合索引会创建一个bTree（每个索引都会创建一个bTree结构的数据，所以不要建太多索引），复合索引的结构是这样的

　　

首先会按照a_id,进行排序，如果a_id相同就会使用a_name排序，如果a_name相同就会使用a_phone排序，然后组成最终的bTree数据结构。

上面就是简单的索引的构建过程，我们看看实际使用中的注意点：

　　1.索引的作用：索引可以用来优化查询，排序，分组等操作，因为索引结构的数据都是按照顺序排列的，不过这里的使用是有限制的,以上操作的字段必须是索引字段。

　　2.索引的使用：还是以上面的表为例，我们来看看索引使用时的注意点，

　　　　　　1）当建立的索引很多时，查询时会根据查询的条件自动挑选最合适的索引，对于联合索引，必须满足最左前缀，比如上面的复合索引，只有当查询条件是（a_id),(a_id,a_name),(a_id,a_name,a_phone)中的一个时才会允许使用索引，如果查询条件是(a_id,a_phone)是不会使用索引的，那么他的查询效率就会特别的低效，索引索引的建立和使用都要特别注意。

　　　　　　2）查询的字段条件是like，in，=，>,<,between,and,or时会自动寻找合适的索引建立查询

　　　　　　3）当使用like查询时，通配符绝对不能放在前面，不然查询时不会使用索引，当然需要业务需要这样做也没有办法，不过可以根据具体的数据情况决定是否多查询数据到内存然后在使用代码进行筛选。正确实例：like='as%'  错误实例：like="%qw"

　　　　　　4) 查询时可以匹配某一列并匹配另一列范围，先按照name精确查找，然后按照birthday进行范围查找

SELECT * FROM person_info WHERE name = 'Ashburn' AND birthday > '1980-01-01' AND birthday < '2000-12-31' AND phone_number > '15100000000';

　　　　　　5）用于排序，当查询数据的排列顺序时按照索引的顺序来的，那查询出来的数据就已经排好序了，不需要额外的排序了。

SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

　   3.使用索引的注意点

• 只为用于搜索、排序或分组的列创建索引

• 为列的基数大的列创建索引  （基数就是非重复值次数）

• 索引列的类型尽量小  （）

• 可以只对字符串值的前缀建立索引

• 只有索引列在比较表达式中单独出现才可以适用索引

• 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。

• 定位并删除表中的重复和冗余索引

• 尽量适用覆盖索引进行查询，避免回表带来的性能损耗。

 

　　

 

转载于:https://www.cnblogs.com/zcmzex/p/9080165.html