本文详细介绍了MySQL中的索引类型，包括B+Tree数据结构及其在InnoDB和MyISAM存储引擎中的应用。讨论了聚集索引与非聚集索引的区别，强调了主键的重要性，并探讨了索引的最左前缀原则。文章还分析了不同查询场景下的索引使用，如Hash算法、Explain的使用以及分页查询和关联查询的优化策略。最后，提供了MySQL索引最佳实践和树高度的估算方法。
文章目录
数据结构图形化：
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
一、索引是帮助 MySQL 高效获取数据的【排好序】的【数据结构】
二、二叉树数据结构
三、B tree索引结构
叶节点具有相同的深度，叶节点的指针为空
所有索引元素不重复
节点中的数据索引从左到右递增排列
b tree 第一行，每个数据 1Kb , 一个节点 16 KB，那么第一行只能放入 16个
16的N次方 = 2千多万，节点数量会比 b+ tree 多的多，b+ tree 只需要 3 个节点即可
叶子节点之间没有指针，访问效率会比 b+ tree的叶子节点慢一点
四、B+ Tree 数据结构
非叶子节点不存储数据data,只存储索引（冗余）
叶子节点包含所有索引字段，和每行数据
叶子节点之间有指针连接，提高了访问效率
查询出每页（每层）的大小为16KB：
SHOW GLOBAL STATUS LIKE 'Innodb_page_size';
查询出每页的大小为16KB，mysql 每行即每个节点的分配大小默认为 16KB
叶子节点之间有指针连接，提高区间访问性能【在范围查找的时候能够快速拿出找到一定范围内的数据】
假设用bigint 作为索引，占 8 个字节; 空白代表下一个节点的地址 6 个字节；
第一个节点： 16KB/(8+6)B = 1170 存放 1170 * 1170 * 16 = 2千多万条数据
一个节点load到磁盘里面就是做一次磁盘I/O ，查找中最耗费时间的就是 load 节点的过程
根据地址在内存中查找数据，时间忽略不计；
一般建表会用一个自增主键做聚簇索引，没有的话MySQL会默认创建，但是这个主键如果更改代价较高，故建表时要考虑自增ID不能频繁update这点。
自行添加的索引都是辅助索引，辅助索引就是一个为了需找主键索引的二级索引，现在找到主键索引再通过主键索引找数据；
Innobd中的主键索引是一种聚簇索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。
Innodb使用的是聚簇索引，MyISam使用的是非聚簇索引
4.1【聚集索引/聚簇索引】与【非聚集索引/非聚簇索引/辅助索引】的区别？
【聚集索引】叶子节点中存放的就是整张表的行记录数据(叶子节点存放着一整行的数据)
【非聚集索引】叶子节点中存放的是索引和主键的值，如果需要查询非【主键】和【索引】的值得话则需要，找到非聚集索引中的主键索引，再通过主键 回表 到主键索引中查找其他数据；
辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引；
聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引，可以有多个非聚簇索引。
Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。
聚簇索引的优缺点
优点：
1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
2.聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：
1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
五、MyIsam索引文件
innodb
frm 【存储表和视图的结构定义】 ibd 【数据和索引】
myIsam
frm 【存储表和视图的结构定义】 myd 【数据 】 myi 【索引 index】
先找到 myi 找到具体的地址，根据地址去 myd 获取数据
myIsam的主键索引和非主键索引都是如上图的存储方式存储的
六、InnoDb
innoDb
frm 表结构 ibd 数据和索引
6.1 innodb二级索引是非聚集索引，通过二级索引找到 主键的索引，然后【回表】通过主键索引找到整列的值 ，【二级索引 最下面那层，每个格子包含了 二级索引和主键】
6.2 为什么建议InnoDB表必须建主键，并且推荐使用整型自增主键？
InnoDB需要有一个列所有元素都唯一的，去组织这颗B+tree；若没有设置主键的话，它会去找一列所有元素唯一的项目去做索引，若没有找到唯一的列，则会新建一列隐藏列去组织B+ tree
推荐使用 整型 作为自增主键，因为 整型之间 比较大小比较方便。【自增】若非自增的话，往树中插入一个数的时候，节点会分裂，同时树可能做一个平衡的操作
七、索引的最左前缀原理
上图 联合主键 索引
先比较【name】 再比较【age】 最后比较【位置】 从小到大排列好
上图若缺失name，最左前缀失效的，因为 联合索引 会按照最左在 b+ tree 里面排好序
若缺失最左边条件，则 第二个 条件在tree里面就没有排序了，所以 索引就会失效
若有name最左边的条件，不管顺序，mysql 会自动优化将 name 条件放在最左边
若条件中【缺失】 name 这个最左条件，那么联合索引会【失效】
联合索引 index_name_age_email ; 从左到右： name,age,email
EXPLAIN select * from user WHERE age=21 AND name='张三' AND email='test@qq.com'
这里 mysql内部会优化，将 name 排在最左边
EXPLAIN select * from user WHERE name='张三' AND email='test@qq.com';
这里只要有 name 【最左元素】就会用到索引，这里 email 是第三个，所以这里只用到了 name的索引
只有name索引查询
EXPLAIN select * from user WHERE name='张三';
八、Hash算法
hash索引通过对索引的key进行hash运算，快速定位出数据存储的位置
九、Explain使用祥解
Explain 是预估的结果
mysql (5.7)
使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈
| id | id越大执行的优先级越高 |
|---|---|
| select_type | 查询类型: primary 复查查询中最外层的select subquery 包含在select的子查询（不在from子句中） ：select和form表之间 derived 包含在form子句中的子查询（form之后），Mysql会将结果放在一个临时表中，派生表 |
| table | 就是查询的哪一张表 |
| partitions | 分区表 |
| type | 关联类型或访问类型 最优到最差：system>const>eq_ref>ref>range>index>ALL 查询达到 reange级别，最好达到 ref null: mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引 主键 primary_key或unique_key的所有列与常数比较时候，所有表最多有一次匹配，读取一次速度比较快。 eq_ref 表关联，主键关联 ref 用了索引，可能查询出多个结果 range 索引查询范围查询 index 扫描全索引，就能拿到结果，一般是二级索引。如果查找的结果集在 主键索引和二级索引里面【都有】，那么会 【优先选择二级索引】，因为【二级索引比主键索引小，查找快】 ALL 全表扫描 |
| possible_keys | 索引 |
| key | 真正执行的时候用到的索引 |
| key_len | 用到索引列数据占的长度，若是联合索引，列如有两个（索引a,索引b）都是整数的话，若 key_len等于 4 则只用到了 索引a ，若key_len等于8则用到了索引a和索引b 【字符串：3*n(字符长度)+2】 如果字段允许为null的话，则需要1字节记录是否为null byte：8位 一个字节 short：16位 两个字节 int：32位 四个字节 long：64位 八个字节 float:32位 四个字节 double：64位 八个字节 char：32位 四个字节 boolean：8位 一个字节 |
| ref | 在key列记录的索引中，表查找值所有的列或常量 |
| rows | 预估可能要扫描多少行 |
| filtered | |
| extra | **Using index ****表示我们查找的 结果集 都在我们的 索引树 里面的话，不需要回表查询，就是 【覆盖查询】会使用索引 ****Using filesort **将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序【文件排序，不会走索引】 Select tables optimized 不用做任何计划 away 结果是放在内存中的，直接就取出来 |
不要在索引列上面做任何操作(计算or函数)，不会走索引
截取字符后，【索引里面存储的值不是截取的】
索引的有序性问题：
尽量使用覆盖索引（只访问索引的查询），减少select * 语句
mysql 使用 不等于 或者 <> 的时候可能无法使用索引
或<可能走索引，可能不走索引
is null ， is not null 一般情况下也无法使用索引
like以通配符开头（’%test’） mysql 索引失效变成全表扫描查询 【%在前，说明搜索字段前面还可能有其他字段】 索引树里面，跳过了前缀的，索引里面就是无序的
如果非要用 ‘%test%’ 查询，用联合索引优化；用【覆盖索引】的方式优化
覆盖索引：查询的结果都是【索引或索引+主键】
字符串不加单引号索引失效，例如 name = ‘100’ 和 name = 100 ;mysql内部可能会进行类型转换
少用 or 或in,用它查询时候，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
范围查询可能不走索引，范围查询可能会走索引，具体根据mysql内部优化细节
范围查找数据量大了，可能不会走索引；范围小的时候会走索引
十、B+ tree 底层分析常见索引优化原则
强制走索引 force index(idx_name_age_position)
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name>'LiLei'AND age=22 A
ND position='manager';
强制走索引不一定快
‐‐关闭查询缓存
set global query_cache_size=0;
set global query_cache_type=0;
‐‐执行时间0.333s
SELECT * FROM employees WHERE name>'LiLei';
‐‐执行时间0.444s
SELECT * FROM employees force index(idx_name_age_position) WHERE name>'LiLei';
2、覆盖索引优化**【覆盖索引的结果，为索引或索引加主键】**
EXPLAIN SELECT name,age,position FROM employees WHERE name>'LiLei' AND age=22 AND position='manag
er';
什么是索引下推？
对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，
SELECT * FROM employees WHERE name like 'LiLei%' AND age=22 AND position='manager'
这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。
在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是’LiLei’开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。
MySQL5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是’LiLei’开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据【不一定走索引下推】。索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。
mysql最终如何选择索引，我们可以用trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭
mysql> set session optimizer_trace="enabled=on" ,end_markers_in_json=on; ‐‐开启trace
mysql> select * from employees where name>'a' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段：
结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描
mysql>select *from employees where name>'zzz' order by position;
mysql>SELECT* FROM information_schema.OPTIMIZER_TRACE;
查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描
mysql> set session optimizer_trace="enabled=off"; ‐‐关闭trace
in() order by 不会走索引
name > ‘a’ order by name; 没走索引，可能是因为数据量大，mysql判断没发走索引
优化总结：
1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index
效率高，filesort效率低。
2、order by满足两种情况会使用Using index。
1) order by语句使用索引最左前列。
2) 使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group
by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中
的限定条件就不要去having限定了。
面试
Using filesort文件排序原理详解
filesort文件排序方式
单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示<sort_key,additional_fields>或者<sort_key,packed_additional_fields>
双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在sort buffer中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示<sort_key,rowid>
MySQL通过比较系统变量max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用哪种排序模式。
如果字段的总长度小于max_length_for_sort_data，那么使用单路排序模式；
如果字段的总长度大于max_length_for_sort_data，那么使用双路排序模式。
查看下这条sql对应trace结果如下(只展示排序部分)：
mysql>set session optimizer_trace="enabled=on", end_markers_in_json=on;‐‐开启trace
mysql>select * from employees where name='zhuge'order by position;
mysql>select * from information_schema.OPTIMIZER_TRACE;
十一、Mysql索引最佳实践
索引设计原则
1、代码先行，索引后上
2、联合索引尽量覆盖条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、orderby、groupby的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
3、不要在小基数字段上建立索引
引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
4、长字符串我们可以采用前缀索引
对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于KEY
index(name(20),age,position)【前缀索引】。此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。
但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的，group by也是同理。所以这里大家要对前缀索引有一个了解。
5、where与order by冲突时****优先where
般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。
6、基于慢sql查询做优化
可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。
关于慢sql查询不清楚的可以参考这篇文章：https://blog.csdn.net/qq_40884473/article/details/89455740
联合索引中 ： 数据量小的时候 in 一般不会走索引， 数据量大的时候 in 会走索引
核心思想就是，尽量利用 一两个复杂的多字段联合索引 ，抗下80%以上的查询，然后用一两个辅助索引尽量抗下剩余的一些非典型查询，保证这种大数据量表的查询尽可能多的都能充分利用索引，这样就能保证查询速度和性能了！
【尽量用单表完成业务】、【多表查询不利于索引优化】
【联合索引】联合索引建多了，会影响 插入和修改 性能
十二、mysql优化
1.分页查询优化
很多时候我们业务系统实现分页功能可能会用如下sql实现
mysql> select * from employees limit 10000,10;
表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。
前提：根据自增且连续的主键排序的分页查询，在没有删除的情况 【一旦中间某条记录被删除掉则失效】
mysql> select * from employees where id > 10000 limit 10;
2.根据非主键字段排序的分页查询
一个根据非主键字段排序的分页查询，SQL 如下：
前提 name 是联合索引的最左边那个
# 不会走索引，数据量大了；limit会先查询出 90005 条数据，而且会回表
select * from employees ORDER BY name limit 90000,5;
sql优化，覆盖索引+ 链表
先走覆盖索引，再走主键索引，最后将 查询到的 5 条数据 全表扫描【左右5条数据，数据量小，扫描这5条数据忽略其性能消耗】
mysql> select * from employees e inner join (select id from employees order by name limit 90000,5) ed
on e.id = ed.id;
mysql的表关联常见有两种算法
**嵌套循环连接 **Nested-Loop Join 算法
基于块的嵌套循环连接 Block Nested-Loop Join 算法
1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法
*
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。
t1 10000 行 t2 100行
mysql> EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
从执行计划中可以看到这些信息：
驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
当使用left join时，左表是驱动表，右表是被驱动表，
当使用right join时，右表时驱动表，左表是被驱动表，
当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。
使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。
上面sql的大致流程如下：
-
从表 t2 中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）；
-
从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
-
取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
-
重复上面 3 步。
整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)。因此整个过程扫描了 200 行。
如果被驱动表的关联字段没索引**，使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block Nested-Loop Join算法。**
2、 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
优化器一般会优先选择小表做驱动表。
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。
上面sql的大致流程如下：
-
把 t2 的所有数据放入到 join_buffer 中
-
把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
-
返回满足 join 条件的数据
整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) =10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是100 * 10000= 100 万次。
这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。
比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。
被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？
NLJ基于磁盘扫描，BNL基于内存扫描
如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。
很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高
对于关联sql的优化
** 【被驱动表】关联字段加索引**，让mysql做join操作时尽量选择NLJ算法
小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间
id相同时候，从上到下执行：
id 不同时，先执行id 大的；
在知道**【参与关联】****的数据大小的时候，可以用straight_join**强制指定关联表：
straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。
用straight_join写法固定连接驱动方式
比如：select * from** t2** straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。
straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。
in和exsits优化
原则：小表驱动大表，即小的数据集驱动大的数据集
in：当B表的数据集小于A表的数据集时，in优于exists
select * from A where id in (select id from B)
#等价于： 先执行 B 表，在执行 A 表
for(select id from B){
select * from A where A.id = B.id
}
exists：当A表的数据集小于B表的数据集时，exists优于in将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留
select * from A where exists (select 1 from B where B.id = A.id)
#等价于:
for(select * from A){
select * from B where B.id = A.id
}
#A表与B表的ID字段应建立索引
1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别
2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析
count(*)查询优化
‐‐ 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
mysql> set global query_cache_size=0;
mysql> set global query_cache_type=0;
# 以下4条sql只有根据某个字段count不会统计字段为null值的数据行
mysql> EXPLAIN select count(1) from employees;
mysql> EXPLAIN select count(id) from employees;
mysql> EXPLAIN select count(name) from employees;
mysql> EXPLAIN select count(*) from employees;
四个sql的执行计划一样，说明这四个sql执行效率应该差不多
**
字段有索引：count(**)≈count(1)>count(字段)>count(主键 id) //字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)
字段无索引：count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)
count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。
count(****) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count(**)。 count(*)会统计 null 的行，count（列名）不会统计此列为null 值的行
为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)。
1、查询mysql自己维护的总行数【针对没有条件的总行数】
对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算
对于innodb存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制)，查询count需要实时计算
2、show table status
如果只需要知道表总行数的估计值可以用如下sql查询，性能很高
show table status like 'employees'
但是通过这种方法查询出的总数，效率不是很高
3.增加数据库计数表
插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作
优化建议
-
如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
-
建议使用TINYINT代替ENUM、BITENUM、SET。
-
避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。这里是显示宽度，并不是代码 int 范围值得长度。
-
DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。
-
建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
-
整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。
日期时间
优化建议
-
MySQL能存储的最小时间粒度为秒。
-
建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
-
用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
-
当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。
-
TIMESTAMP是UTC时间戳，与时区相关。
-
DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
-
除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。
-
有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它。
优化建议
-
字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
-
CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。
-
尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。
-
BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
-
BLOB和TEXT都不能有默认值。
十三、获取 MySQL innodb B+tree 的高度的方法
前言
MySQL 的 innodb 引擎之所以使用 B+tree 来存储索引，就是想尽量减少数据查询时磁盘 IO 次数。树的高度直接影响了查询的性能。一般树的高度在 3~4 层较为适宜。数据库分表的目的也是为了控制树的高度。那么如何获取树的高度呢？下面使用一个示例来说明如何获取树的高度。
示例数据准备
建表语句如下：
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表中插入100万条数据。数据如下：
mysql> select * from user limit 2\G
*************************** 1. row ***************************
id: 110000
name: ab
age: 100
*************************** 2. row ***************************
id: 110001
name: ab
age: 100
2 rows in set (0.00 sec)
通过查询相关数据表获取树的高度
以 MySQL5.6 版本为例说明如何获取树的高度。
首先获取 page_no
mysql> SELECT b.name, a.name, index_id, type, a.space, a.PAGE_NO FROM information_schema.INNODB_SYS_INDEXES a, information_schema.INNODB_SYS_TABLES b WHERE a.table_id = b.table_id AND a.space <> 0 and b.name='test/user';
+-----------+---------+----------+------+-------+---------+
| name | name | index_id | type | space | PAGE_NO |
+-----------+---------+----------+------+-------+---------+
| test/user | PRIMARY | 22 | 3 | 6 | 3 |
| test/user | name | 23 | 0 | 6 | 4 |
| test/user | age | 24 | 0 | 6 | 5 |
+-----------+---------+----------+------+-------+---------+
3 rows in set (0.00 sec)
page_no 是索引树中Root页的序列号。其它各项的含义可以参照：https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-indexes-table.html
再读取页的大小
mysql> show global variables like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)
最后读取索引树的高度
$ hexdump -s 49216 -n 10 ./user.ibd
000c040 0200 0000 0000 0000 1600
000c04a
可以发现 PAGE_LEVEL 为 0200，表示这棵二级索引树的高度为 3。后面的 1600 是索引的 index_id 值。十六进制的 16 转换为十进制数字是 22。这个 22 正好就是上面主键的 index_id。
上面 hexdump 命令中 49216 是怎么算出来的？公式是 page_no * innodb_page_size + 64。
3*16384+64=49216
我们在用这个方式查看下其他两个索引的高度。
$ hexdump -s 65600 -n 10 ./user.ibd
0010040 0100 0000 0000 0000 1700
001004a
$ hexdump -s 81984 -n 10 ./user.ibd
0014040 0200 0000 0000 0000 1800
001404a
可见，name 索引的高度是 2，age 索引的高度是 3。
根据索引的结构估算
如果你没有数据库服务器的权限。自己也可以根据数据库索引结构进行估算树的高度。
根据 B+Tree 结构，非叶子节点存储的是索引数据，叶子节点存储的是每行的所有数据。
非叶子节点每个索引项的大小是，数据大小+指针大小。假设指针大小为 8 个字节。每页不会被占满，预留1/5的空隙。下面我们估算下 name 和 age 两个索引的高度。
name 索引高度估算
非叶子节点每页存放的索引项数量。每页大小是 16k。name 的值为 ab。占2个字节。每项数据大小是 2+8=10字节。每页能存放的索引项数量是 16384 * 0.8 / 10 = 1310 个。
叶子节点每页存放的索引数量。每页大小是 16k。每项数据大小是 4+2+8=14 个字节。没页能存放的索引数量是 16384 * 0.8 / 14 = 936 个。
两层能存放 1310*936=1226160 个数据记录。可见120万条记录以下，树的高度为2。
age 索引高度估算
非叶子节点每页存放的索引项数量。每页大小是 16k。age 的类型为 int。占4个字节。每项数据大小是 4+8=12字节。每页能存放的索引项数量是 16384 * 0.8 / 12 = 1092 个。
叶子节点每页存放的索引数量。每页大小是 16k。每项数据大小是 4+4+8=16 个字节。没页能存放的索引数量是 16384 * 0.8 / 16 = 819 个。
两层能存放 1092*819=894348 个数据记录。可见90万条记录以下，树的高度为2。100万条为 3 层。
其它工具
还有一个小工具可以查看。InnoDB 表空间可视化工具innodb_ruby
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
