Innodb索引以及查询优化的一些见解

聚集索引

索引如果没有特指,一般是指B+TREE,通常意味着所有值都是顺序存放，因此对于范围查询会非常快。InnoDB按照原数据格式进行存储。InnoDB存储引擎表是索引组织表，表中数据按照主键存放(InnoDB会隐式定义一个主键作为聚集索引,切记不能重复定义)。索引的叶节点中存放表的行记录，使叶节点成为数据页。而普通索引仅仅存放键值以及偏移量而已。(ps:MYISAM使用前缀压缩使索引更小,表中数据是通过索引所记录的数据物理的位置，直接引用的。)

Tips1：

范围查询非常快

eg:limit 优化

select * from yanxue8_visit limit 10,10

多次运行，时间保持在0.0004-0.0005之间

Select * From yanxue8_visit Where vid >=(
Select vid From yanxue8_visit Order By vid limit 10,1
) limit 10

Tips2:

在使用索引时需要独立的列

eg:

select * from tablename where id +1 = 5

这样的写法是不能用索引的,这也是为什么很多mysql优化语句中常提到的一点。

Tip3:

可以使用前缀索引

eg:

alter table tablename add key(a(7))

这样可以使索引变小并变快，节约空间，提高索引效率。但会减小索引的选择性。

前缀索引，一种优化索引大小的解决方案: https://yq.aliyun.com/articles/39841

事实上,聚集索引页有些缺点,提高了使用磁盘I/O的次数。并且要严格按照插入顺序插入，否则可能面临数据页分裂,耗用更多空间并降低性能。因此我们都需要一个自增长键作为主键，保证数据顺序写入。若使用UUID情况会变糟糕，所以现在分布式系统基本放弃了原生的UUID寻找替代品。

辅助索引(非聚集索引)

辅助索引只是存取了聚集索引的书签。若想查询需要对辅助索引遍历一遍再在聚集索引遍历一遍，遍历深度由索引树高度决定。

联合索引

联合索引顾名思义是多索引组成的联合索引.
如一张t表中有两列,a与b,其中a为主键,b为普通列
创建:alter table t add key idx_a_b(a,b)

如上图,数据先根据a排放,a值相同则根据b排放。既该索引包括了a与b的笛卡尔积。若where a = xxx and b = xxx 以及 where a = xxx则可以利用该索引,但where b = xxx 就不能利用该索引。(最左前缀)

好处:

可以联合读取多个列
可以做联合排序

Tip1:

利用多列条件做分页操作

创建:alter table tablename add key idx_a_b(a,b)

select * from tablename t1
left join(
select id from tablename
where a = 1 oder by b limit 1000,10) t2
on t1.id = t2.id

但事实上,也不能一棒子拍死。查询结果会非常依赖于选定的具体指,会对其他查询不公平，会使服务器运行不如预期。其顺序必须以选择性高低进行排序。事实上实际开发中，本人是因为某个查询量大的业务才建立联合索引，基本业务也是单个索引(但要注意重复索引,如我创建了(a,b)就得把(a)索引删除)。

哈希表

InnoDB中只用哈希对字典进行查找,事实上,哈希表只需理解有这么个东西就行。InnoDB会自动创建哈希表并维护,并只是对字典查询会变快(where a = ‘xxx’)，不对范围查询起作用。

innodb_adaptive_hash_index 启用/禁用特性
innodb_buffer_pool_size/256=哈希的槽数

使用分析工具

explain

非常强大的工具,分析sql语句的性能以及所运用的索引。可以解决绝大多数sql性能问题。

慢查询日志

超过阈值的SQL语句记录的日志。

show index from tablename

可以分析表中索引,有些字段对于分析该表索引非常有用,如Cardinality。

analyze table

能优化索引存储,使索引更好工作.可每周或每天凌晨运行一次。

值得注意的地方

mysql在查询时，若数据大于整张表的20%,则会放弃索引

参考书籍

高性能MySQL： https://book.douban.com/subject/23008813/

MySQL技术内幕： https://book.douban.com/subject/5373022/