189 8069 5689
MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类
成都创新互联从2013年成立，是专业互联网技术服务公司，拥有项目成都网站设计、成都网站建设网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元杏花岭做网站,已为上家服务,为杏花岭各地企业和个人服务,联系电话:13518219792
特点：B+树叶子节点存储行数据
一个表中，必须有一个聚集索引，只能有一个聚集索引，Innodb通常把一个表的主键索引作为聚集索引，如果没有主键InnoDB会选择一个唯一索引代替。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚集索引，这个字段为6个字节，类型为长整形。
利用主键索引查找行数据是最快的，建议使用自增主键原因是利于索引树的构建（主键自增写入时新插入的数据不会影响到原有页，插入效率高；但是如果主键是无序的或者随机的，那每次的插入可能会导致原有页频繁的分裂，影响插入效率）
特点：B+树叶子节点存储主键ID
一个表中可以有多个非聚集索引，每个非聚集索引即是一棵B+树
通过非聚集索引查找数据时，需要先在非聚集索引上找到主键ID，再从聚集索引获取行数据，这个过程就称之为回表
B树索引中的B树实际上是B+树，至于为什么使用B+树而不使用B树或者红黑树的原因在另外的文章中有提及。
特点：
特点：类似JDK中的HashMap，但无法支持范围查询
特点：使用的算法仍然是B树索引，不同的就是索引列的值必须唯一
对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索，提升索引性能
另外插入行时会构建该唯一索引，假如索引值重复将插入失败，适合业务上做唯一性检验
通过建立倒排索引，可以极大的提升检索效率,解决判断字段是否包含的问题，但是业务上一般都不采用这种索引，而是使用ES处理全文搜索需求
仅对某个特定字段建立的索引，如(biz_id)
对多个字段建立的索引，如(biz_id,type)
建立索引，要使用离散度（选择度）更高的字段。
我们先来看一个重要的属性列的 离散度，
count(distinct(column_name)) : count(*) -- 列的全部不同值个数：所有数据行行数
数据行数相同的情况下，分子越大，列的离散度就越高。简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。
当字段值比较长的时候，建立索引会消耗很多的空间，搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。
创建一张商户表，因为地址字段比较长，在地址字段上建立前缀索引
create table shop(address varchar(120) not null);
alter table shop add key(address(12)); // 截取12个字符作为前缀索引是最优的吗？
问题是，截取多少呢？截取得多了，达不到节省索引存储空间的目的，截取得少了，重复内容太多，字段的散列度（选择性）会降低。怎么计算不同的长度的选择性呢？
先看一下字段在全部数据中的选择度计算公式：
select count(distinct address) / count(*) from shop;
select count(distinct left(address, n)) / count(*) as subn from shop;
count(distinct left(address,n)) / count(*) 的结果是会随着 n 的变大而变大。举个例子，现在有两个address（东大街长兴小区，东大街福乐小区），那么 distinct(address，2) distinct(address，3）
==所以，截取的长度越长就会越接近字段在全部数据中的选择度
==所以，我们要权衡索引大小和查询速度。
举个例子，通过不同长度去计算，与全表的选择性对比：
SELECT COUNT(DISTINCT(address))/COUNT(*) sub, -- 字段在全部数据中的选择度
COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5, -- 截取前5个字符的选择度
COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7,
COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,
COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10, -- 截取前10个字符的选择度
COUNT(DISTINCT(LEFT(address,11)))/COUNT(*) sub11,
COUNT(DISTINCT(LEFT(address,12)))/COUNT(*) sub12,
COUNT(DISTINCT(LEFT(address,13)))/COUNT(*) sub13,
COUNT(DISTINCT(LEFT(address,15)))/COUNT(*) sub15
FROM shop;
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| sub | sub5 | sub7 | sub9 | sub10 | sub11 | sub12 | sub13 | sub15 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 0.9993 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9914 | 0.9943 | 0.9943 | 0.9958 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
可以看到在截取 11 个字段时 sub11(0.9993) 就已经很接近字段在全部数据中的选择度 sub(0.9958)了，而且长度也相较后面更短一些， 综合考虑比较合适。
ALTER TABLE shop ADD KEY (address(11));
1.索引的个数不要过多（浪费空间，更新变慢）
2.在用于 where 判断 order 排序和 join 的（on）字段上创建索引
3.区分度低的字段，例如性别，不要建索引（离散度太低，导致扫描行数过多）
4.更新频繁的值，不要作为主键或者索引（页分裂）
5.不建议用无序的值作为索引，例如身份证、UUID（在索引比较时需要转为ASCII，并且插入时可能造成页分裂）
6.若在多个字段都要创建索引的情况下，联合索引优于单值索引
7.联合索引把散列性高（区分度高）的值放在前面
CREATE
[UNIQUE]
INDEX
ON
(字段
[ASC|DESC]);
UNIQUE
--确保所有的索引列中的值都是可以区分的。
[ASC|DESC]
--在列上按指定排序创建索引。
(创建索引的准则：
1.如果表里有几百行记录则可以对其创建索引(表里的记录行数越多索引的效果就越明显)。
2.不要试图对表创建两个或三个以上的索引。
3.为频繁使用的行创建索引。
)
示例
create
index
i_1
on
emp(empno
asc);
二叉搜索树、N叉树
页分裂：B+树的插入可能会引起数据页的分裂，删除可能会引起数据页的合并，二者都是比较重的IO消耗，所以比较好的方式是顺序插入数据，这也是我们一般使用自增主键的原因之一。
页分裂逆过程：页合并，当删除数据后，相邻的两个数据页利用率很低的时候会做数据页合并
主键索引：key：主键，value：数据页，存储每行数据
非主键索引：key：非主键索引，value：主键key，导致回表
最左匹配：优先将区分度高的列放到前面，这样可以高效索引，
最左匹配原则遇到范围查询就停止匹配，范围查询(、、between、like)为什么？因为出现范围匹配后，后面的索引字段无法保证有序，局部有序失去，顺序失去则无法提高查询效率
SELECT * FROM table WHERE a IN (1,2,3) and b 1;
如何建立索引？
还是对(a，b)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(a,b)!
索引组织表
索引用页存储：key【10】-point【6】，通过调整key大小，当页大小固定的情况下，通过调整key大小，使得N叉树变化；
如key 10, point 6则单个索引16字节，页大小为16k，则页面总共可以存储1024个索引，即N大小
覆盖索引: 二级索引的信息已经存在想要的列，例如主键
如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。
索引下推优化：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
整理索引碎片，重建表：alter table T engine=InnoDB
首先是看key的大小，另外是数据页的大小，如果需要改变N，则需要从这两个方面做改动；
一个innoDB引擎的表，数据量非常大，根据二级索引搜索会比主键搜索快，文章阐述的原因是主键索引和数据行在一起，非常大搜索慢，我的疑惑是：通过普通索引找到主键ID后，同样要跑一边主键索引，对于使用覆盖索引的情况下，使用覆盖索引可以直接解决问题
MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则：
(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。