MySQL索引

3208519874

什么是索引
简单理解为排好序的方便快速查找的数据结构。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。平时所说的索引如果没有特别指明，都是指B树结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，除了B+树这种类型的索引之外还有哈希索引。
聚集索引
行以与索引相同的顺序物理地存储在磁盘上，一个表中只能拥有一个聚集索引。
非聚集索引
该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
InnoDB下索引的实现

• 在InnoDB存储引擎中，是以主键为索引来组织数据的，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。
• 所以InnoDB要求表必须有主键（MyISAM可以没有），如果在创建表时没有显示的定义主键，则InnoDB存储引擎会按如下方式选择或创建主键：1.首先判断表中是否有非空的唯一索引，如果有，则该列即为主键；2.如果不符合上述条件，生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形； 3.如果有多个非空唯一索引时，InnoDB存储引擎将选择建表时第一个定义的非空唯一索引作为主键。
  联合索引的实现
  
• InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
• 联合索引也是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
• 辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

为什么不建议使用过长的字段作为主键
因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
为什么要用B+树
因为他是一个高度可控的多路搜索树，可以在每次查找数据时把磁盘IO次数控制在一个很小的数量级。
建立索引的常用技巧

• 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
• 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
• 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可，当然要考虑原有数据和线上使用情况。

Explain相关

id：:select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。
id相同时，执行顺序由上至下；
id不同时，如果是子查询，id的序号会递增，id越大优先级越高，越先被执行；
id有相同的有不同的，id相同的可以认为是一组，从上往下执行，在所有组中，id越大优先级越高，越先被执行。
select_type：

table：:显示是关于哪张表的
type：

sql优化（如何避免索引失效）
1.全值匹配
2.最佳左前缀法则
3.模糊查询不以通配符开头
4.避免is null或者is not null
5.避免在列上进行计算或强制类型转化
6.少用or
7.不等于!=或<>导致索引失效
8.使用覆盖索引
9.索引不能使用范围查询后面的列
10.字符串不加单引号导致失效
主从复制：是一种数据备份的方案。
简单来说，是使用两个或两个以上相同的数据库，将一个数据库当做主数据库，而另一个数据库当做从数据库。在主数据库中进行相应操作时，从数据库记录下所有主数据库的操作，使其二者一模一样。
读写分离：是一种让数据库更稳定的的使用数据库的方法。
是在有从数据库的情况下使用，当主数据库进行对数据的增删改也就是写操作时，将查询的任务交给从数据库。
分裤分表
关系型数据库本身比较容易成为系统瓶颈，单机存储容量、连接数、处理能力都有限。
虽然读写分离能分散数据库的读写压力，但并没有分散存储压力，当数据量达到千万甚至上亿时，单台数据库服务器的存储能力会成为系统的瓶颈，主要体现在以下几个方面：
1.数据量太大，读写的性能会下降，即使有索引，索引也会变得很大，性能同样会降下。
2.数据库文件会得很大，数据库备份和恢复需要耗时很长。
3.数据库文件越大，极端情况下丢失数据的风险越高。
此时就要考虑对其进行切分了，切分的目的就在于减少数据库的负担，缩短查询时间。
数据切分就是将数据分散存储到多个数据库中，使得单一数据库中的数据量变小，通过扩充主机的数量缓解单一数据库的性能问题。
数据切分分为两种方式，垂直切分和水平切分
垂直切分常见有垂直分库和垂直分表两种。
垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似，按业务分类进行独立划分。与"微服务治理"的做法相似，每个微服务使用单独的一个数据库。
垂直分表是基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。在字段很多的情况下（例 如一个大表有100多个字段），通过"大表拆小表"，更便于开发与维护，也能避免跨页问题。

当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。

水平切分分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果
库内分表只解决了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很大，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。