一、为什么需要索引
定义：快速定位记录的数据结构。索引是数据结构。
减少昂贵的IO操作。
减少内存计算(比较、排序)
二、索引的类型
聚簇索引；二级索引
普通索引；唯一索引
单列索引；组合索引
三、B+tree
mysql 索引采用B+tree结构，不同的存储引擎，实现不同，这里只说innodb的实现。
图片来源网络
特点：
父节点不存储数据，只存key
叶子节点只存储data，并且有指针相连
叶子节点是有序的(由小到大)
四、索引的实现
索引属于存储引擎级别的概念，不同的存储引擎对索引的实现方式不同。
聚簇索引
叶子节点包含完整的数据记录，这就是聚簇索引。
innoDB表必需有主键，因为innoDB的数据文件本身是按主键聚集。
一张表只能有一个聚簇索引。
二级索引
叶子节点存储的不是指针，而是主键值。
二级索引查询成本=第二索引+聚簇索引查找成本
小结：
主键尽可能小
显示声明主键bigint，避免mysql自动生成字符串型主键
主键自增，和B+树分裂方向一致
不要更新主键
五、索引设计
1.选择性
count(distinct colname)/count(colname) ->1,尽量将选择性高的列放在索引最前面。
2.避免null
(1) 额外存储，特殊处理
(2)使索引统计和值更加复杂
(3)SQL执行结果不符合预期
3.隔离列
索引列在表达式或者函数中时，不会走索引，应将索引列置于单独一侧。
select id from test where id +1 = 5 (x)
select id,value from test where to_days(now())-to_days(gmt_created) <=10 (x)
select id,value from test where gmt_created >= DATA_SUB(now(),interval 10 day)
select id,value from test where gmt_created >= ’2017-06-01’
4.组合索引
将多个字段建立一个组合索引；一个索引可以被多个query利用；减少索引数量，消除重复索引
5.覆盖索引
包含所有满足查询需要的数据的索引叫覆盖索引。Mysql利用索引返回select的字段，无需再回表查询。
idx_name_age(name,age)
select name,age where name= 'a'
六、索引使用
Mysql会从索引的第一个键值一直向右匹配，直到遇到范围查询(>,<,<>,or,like[%前缀])或者索引键值没有出现在where查询中，停止匹配(最左原则)。
当索引是组合索引，比如(c1,c2,c3)，B+Tree是按照从左到右的顺序建立搜索树的，比如当(value1,value2,value3)这样的数据检索时，b+树优先比较c1来确定下一步搜索方向，如果c1相同再一次比较c2和c3，直到找到目标。
1.索引的有序性
根据最左原则，分析几个小例子
如果建立了(c1,c2,c3)索引
where c1=‘x’order by c3
where c2=‘y’ order by c1
where c1=‘x’ and c3=‘z’
where c1=‘x’ and c2=‘y’ order by c3
一个思考题
如果建立了(c1,c2,c3) (c3)索引
where c1=‘x’order by c3 limit 1 会如何走索引？如果C3没有索引会如何？
2.隐式转化
表及索引(1)
执行sql1
sql1执行结果，扫描行数
执行sql2
sql2执行结果，扫描行数
还有很多具体开发中需要注意的情况，空了再补充。
网友评论