mysql学习总结——索引(未完)

mysql索引回顾

         回顾下自己上篇博客是时间已经是半年前，当时自己为什么写博客，一方面记录自己学习内容做笔记，更多的是为了简历上可以附上
     博客地址仅此而已。回忆一下当时的想法真的很可笑，老是喜欢搞一些表面的东西，实际上当时的博客对自己的收获微乎其微，纯属为了
     写而写。现在呢，实习快半年了，实习期间做了很多事情，但是回想自己的收获，的确有进步，是什么说不出来。参加实习的这段时间
     以来，每天在公司待到十点学习新的知识，从来不曾懈怠，可是学过一遍留下的不多。现在回想一下问题在哪：没计划，从来都没有认
     真的给自己定过一个学习计划，想到什么学什么。这几天一直想重新开始写自己的博客，每隔几天总结总结收获，梳理一遍，查缺补漏，
     再记录下每天的学习计划。坚持让明天的自己比今天更强。

正式开始。
举个栗子：

 create table test_index(
 	 id int(10) not null  AUTO_INCREMENT PRIMARY KEY,
  	 name varchar(10) not null, 
  	 group_name varchar(10) not null,
     address varchar(10) not null,
	 balabala varchar(10), 
   	 key(name,group_name,address)
   );

这里主要以innodb为主

• 索引类型

  • b+tree索引（b+tree结构，一条sql语句的查询过程，索引匹配规则）
    • 索引匹配规则：
    • 全值匹配:使用索引中的全部列进行匹配
    • 最左前缀匹配：使用索引中第一列进行匹配
    • 匹配列前缀：利用第一列模糊查询
    • 匹配范围值：范围匹配第一列
    • 精准匹配某一列，范围匹配后一列
    • 覆盖索引
  • hash索引
  • 全文索引

  b+tree结构——（待续）
  b+tree索引中的值都是顺序存储，操作的基本单位是页，默认一页大小为16K。b+tree索引会有 聚簇索引与二级索引两种存储方式，聚簇索引会按照数据行的大小顺序存储在页内(最常用的聚簇索引就是主键)，同一页中的数据都是相邻的，但是相邻页在物理地址中的存储可能就相隔甚远。
  innodb如何选择聚簇索引？1.当建表的时候定义了主键，那主键就作为聚簇索引。2.如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引。3。如果前两者都没有，innodb会选择生成一个隐藏的主键来作为聚簇索引。

  • 聚集索引优点：聚集索引代表数据的存储方式，根据主键列的大小进行排序，一个栗子：实现电子邮件，可以通过用户ID聚集数据，通过(userId，time)做为联合主键，这样就可以把同一个用户的邮件聚集在一页中，极大的减少io次数，避免随机io。聚簇索引访问数据时更快，聚簇索引将索引跟数据存在同一个b+tree中。覆盖索引的查询可以直接获取使用页节点中的主键值。
  • 聚集索引缺点：插入的速度依赖插入的顺序，按主键顺序插入是最快的（自增主键），不按主键顺序插入会导致大量的数据移动，出现页分裂的问题造成数据不连续；如果聚集索引较大时会影响二级索引，二级索引中包含主键列。

  二级索引也是按值排序，但是同一页的数据在物理地址中并不相邻，二级索引叶子结点中存的是主键，所以在使用二级索引会先在二级索引上找到指定的主键，再通过主键索引（主键索引中存页指针）查找数据行，存在一个二次查找的问题。

  一条using index sql的查询过程：

• 索引的优点

  • 减少服务器需要扫描的数据行
  • 帮助服务器避免排序和临时表
  • 索引可以讲随机io变为顺序io
    高性能的索引策略

• 建立合适的索引

  1. 在varchar类型的字段上建立索引（text，blob只可以建前缀索引），有时候通过整个字符串建立索引并不是明智的选择，因为字符串较长，一页内可以存放的数据减少，相应的io次数会增加，此时可以建立前缀索引，利用字符串的前n位建索引，前缀的长度n应该满足 count(distinct,left(字段，n))/count(*)约等于count(distinct,字段)/count(*) 索引的基数接近于完整列的基数。
     还有一种办法利用hash索引，innodb不支持hash索引（innodb的自适应性hash索引，由innodb自动创建，开发者无法干预），创建伪hash索引，利用crc32(),sha1(),md5(),作为hash函数。再利用hash列创建索引，通过hash等值比较，等位数据行，避免过大的varchar完整列做索引。
  2. 每个列上都创建索引（误区）。
  3. 合适的索引列顺序，正常状况下选择性高的列排前边。