数据库优化学习

总结 http://study.163.com/course/courseMain.htm?courseId=1003746011 学习视频内容

1.最左前缀原则

　　创建索引 （a, b, c） 相当创建3个索引 分别为 a， a_b，a_b_c 三个索引可以使用 

　　当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，

　　如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，

　　必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，

　　 这个是非常重要的性质，即索引的最左匹配特性

 

2.limit 优化

　　limit offset num -> 跳过 offset条 数据取 num条数据 

　　取数据时是将整个数据取出， 然后再删除offset 条数据，留下最后num条数据，所以当offset数据量过大时候非常的消耗资源

　　解决方法

　　1.业务逻辑解决，分页最多多少页规定好，多出页不允许查询 或者查询返回最后规定页内容

　　2.通过 > 进行优化查询,此种查询要求数据完整，id为连续的自增涨列，删除操作时候做逻辑删除 设置flag字段判断是否删除

　　　　select id, name, age, class from user where id > 5000000 limit 0, 3 

　　3.联合查询（自连接）

　　user表 id name age class 表量级10000000 ，其中id有索引 进行分页查询

　　select id, name, age, class 

　　from user

　　inner join (select id from user where limit 5000000, 3) as user_temp

　　on user.id = user_temp.id

 

3.MyISAM与InnoDB区别

　　MySQL默认采用的是MyISAM

　　myisam 支持全文检索 innoDB不支持

　　myisam 不支持事务 innoDB支持

　　myisam 不支持外键 innoDB支持

 

4. 聚簇索引和非聚簇索引

　　myisam 与 innoDB 为不同的搜索引擎， 但是同为btree索引

　　myisam 索引文件和数据文件为单独的两份文件

　　myisam 中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

　　通过索引取得data域的值过程叫回行操作。myisam 的索引叫非聚簇索引。

　　在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，

　　因此InnoDB表数据文件本身就是主索引，btree索引叶子节点上包含数据，取的时候没有回行操作，innoDB索引叫聚簇索引。

　　默认情况下innoDB主键默认为聚簇索引

 

5.创建索引原则

　　索引有序，所以创建时候要按照业务逻辑进行创建

　　eg : 商城物品创建索引

　　　　种类_品牌_价格 按照商品属性关注度由高到低进行索引创建

　　查询时候最好用到索引覆盖 ，即查询字段全部被索引字段包含并且满足最左前缀原则