文章目录索引索引的实现索引存储约束约束和索引的区别B树bufferpoolinnodb的体系结构最左匹配原则覆盖索引索引下推索引失效索引设置原则出现了慢sql应该怎么做索引索引是一种有序的存储结构按照单个或者多个列的值进行排序用于提升搜索效率主键索引满足非空唯一约束且一个表只能有一个主键索引。一个表最多有一个主键但是主键索引存在联合索引的情况。主键选择innoDB中表是索引组织表每张表中有且仅有一个主键1.显式设置PRIMARY KEY则该设置的key为主键2.没有设置只有一个非空唯一索引该索引为主键有多个非空唯一索引选择声明的第一个3.没有非空唯一索引自动生成一个6字节的_rowid作为主键PRIMARYKEY(key1,key2)唯一索引满足唯一约束,一个表可以有多个唯一索引UNIQUE(key1,key2)普通索引INDEX(key)--OR--KEY(key[,...])组合索引INDEXidx(key1,key2[,..]);UNIQUE(key1,key2[,..]);PRIMARYKEY(key1,key2[,..]);全文索引Elasticsearch的全文索引最常用搜索引擎的实现通过关键字反向索引文章。索引的实现在InnoDB中索引对应的是B树。PRIMARY KEY → #map 在innoDB是B树。KEY → #multimapint,xx索引存储磁盘访问时间寻道时间8-12ms旋转时间7200转/min即半周4ms传输时间(约0.3ms)访问速度磁盘随机IO磁盘顺序io(省去大部分寻道时间)≈内存随机IO内存顺序IO。约束为了实现数据的完整性对于innodb提供了primary key,unique key,foreign key,default,not null外键约束外键用来关联两个表来保证参照的完整性。约束和索引的区别创建主键索引或者唯一索引的时候同时就创建了约束约束是逻辑上的概念索引是一个数据结构既包含逻辑概念也包含物理的存储方式B树多路平衡搜索树其每个节点映射磁盘数据。以页为单位物理磁盘页一般为4KBinnodb默认页大小为16KB对页的一次访问就是磁盘IO到内存大约10msmysql缓存中会缓存经常访问的页。B树的特征①非叶子节点只存储索引信息②叶子节点还存储数据信息③叶子节点之间水平依次连接④节点的大小为16KB映射的是连续的磁盘页。问题①为什么采用多路的数据结构而不是红黑树相较于平衡二叉搜索树这是一个矮胖的结构跳转较少的节点就可以找到需要的数据。问题②为什么非叶子节点只存储索引信息在B树中非叶子节点只有key信息而叶子节点才有key value信息。非叶子结点的16KB能够容纳更多的索引信息树的结构更加矮胖IO次数更少问题③为什么叶子节点彼此相连便于范围查询避免中序遍历回溯总之索引信息和数据信息分层管理便于高效地组织磁盘数据快速实现单点和范围查询。PRIMARY KEY 和KEY对应两种B 树聚集索引B树和辅助索引B树按照主键构造的B树# table id name id为primary keyselect*fromuserwhereid18andid40;除了主键索引之外的索引就是辅助索引。辅助索引的叶子节点不包含除了主键信息的所有的行信息只包含索引的信息主键信息索引信息使用辅助索引查到主键值然后走聚集索引查到所有行。--某个表 包含id name lockyNum ; id是主键索引lockyNum是辅助索引--KEY()select*fromuserwherelockNum33;但是实际操作是很快的好像并没有经过那么多次磁盘iobufferpool所有数据库基本上都自定制了缓存策略不走page cache直接direct io刷到file中。innodb的体系结构经常访问的磁盘数据会缓存在bufferpool中采用LRU算法。changebuffer用于缓存辅助索引的数据变更会将其中的数据异步merge到buffer pool当中redolog确保了缓存中的数据安全相当于redis的aof。redolog undolog会用到page cacheB树中的数据用buffer pool。最左匹配原则索引个数最好不超过6个因为修改一个字段就要维护多个B树。所以需要组合索引对于组合索引从左到右依次匹配遇到 between like 就停止匹配。KEYname_and_cid(name,cid);EXPLANselect*fromuserwherenameflame#看这条语句有没有踩到索引type : ref 踩到索引了 all全表扫秒possible_keys : name_and_cid 索引名KEYname_and_cid(name,cid);EXPLANselect*fromuserwherecid1;#看这条语句有没有踩到索引这条sql语句不会踩索引where后条件没有匹配到 name … 但是wherecid1andnameflame是可以踩到的优化器会自动调整以上语序。但是用了不等号wherecid1andnameflame的type不是ref而是range稍慢于ref但是也踩索引mysql 后续的优化遇到 between like就停止匹配可以改为 0 or 0覆盖索引要查的数据就是辅助索引的信息走辅助索引时就不需要回表type返回值是 use index。所以说不要动不动select *索引下推针对普通索引和联合索引场景。5.6版本后推出减少回表次数减少server层和存储引擎层的交互次数提升查询效率。没有索引下推机制前server层向存储引擎层请求数据在server层根据索引条件进行数据过滤有索引下推机制后将部分索引条件判断推到存储引擎进行过滤由存储引擎汇总返回给server索引失效①不遵循最左匹配原则②索引字段参与运算,作用函数匹配失效③索引字段发生隐式转换字符串和数字比较会将字符串转化为数字索引失效④LIKE模糊查询where name like ‘%某’ 通配符%开头索引失效⑤索引字段使用NOT !索引失效可改为0 or 0索引设置原则①查询频次高的且数据量大的列②索引字段越短越好索引字段占用空间越小节点中容纳的数据就越多磁盘IO就越少。③对于很长的动态字符串考虑使用前缀索引key(name(4))4是怎么呢算区分度该列值相同的越少越好selectcount(distinctleft(name,3))/count(*)assel3,count(distinctleft(name,4))/count(*)assel4,count(distinctleft(name,5))/count(*)assel5,count(distinctleft(name,6))/count(*)assel6fromuser;altertableuseraddkey(name(4));-- 注意前缀索引不能做 order by 和 group by④in 优化为 existinner join⑤尽量扩展索引使用组合索引最多6个列参与索引⑥尽量设置为非空非空判断会让索引失效出现了慢sql应该怎么做①show processlist②开启慢查询日志③分析sql语句where group by order by是否踩了索引④分析sql语句能否把in not in 优化成联合查询⑤尽量减少联合查询拆成多个sql语句⑥不要存储age字段因为这是会变化的给数据库带来不必要的开销https://github.com/0voice