海纳百川有容乃大
谢谢各位大牛的分享、感谢各位朋友的访问
mysql消耗资源的点：
- CPU：比较、排序、SQL解析、函数或逻辑运算
- 内存：缓存数据访问，临时数据存放
- 硬盘：冷数据读取、大数据量排序和关联，数据写入落盘
- 网络资源：sql请求交互，结果集返回
这些很多都借助索引减轻自己的‘罪过’，那么索引是什么？
表没有索引会变成什么样，或许循规蹈矩听‘插入’的指挥按顺序一条条的躺在磁盘上又或许缺乏想象力的我想换个话题，可能他也就只有这一种选择了吧
表加了索引，自增主键、立刻注入了新鲜血液 活蹦乱跳变成了树状结构，是不是想起了聚簇索引，聚簇索引：主索引文件和数据文件是同份文件，主键一定是聚簇索引
分类
普通索引，无限制加速查
create index index_name on tableName(field);
alter table tableName add index index_name(field);
1、唯一索引
值唯一，容许空，组合索引则列值的组合必须唯一
create unique index ……
alter table tableName add unique index ……
2、主键索引
特殊唯一索引，一个表只能有一个，不容许空，一般建表是创建
3、组合索引
多个字段上创建的索引，最左前缀原则
语句同普通索引，field为多个，逗号隔开；如有（col1，col2，col3）
叶子节点是：没错 懒得画 拷一份过来 说实话有些丑 ^_^还是谢谢作者，链接自跳
据name字段从辅助聚簇索引定位到哪一叶子节点，据age在上述表格寻找，找出所有符合的数据及其对应的ID，据ID回表查
4、全文索引
查找文本中关键字，fulltext更像搜索引擎，不推荐使用，所以不写了
i、聚簇索引，非聚簇索引
聚簇索引具有唯一性，他的顺序是数据的物理存储顺序(各数据按索引序存放)，索引相邻、对应数据相邻、就这样完美紧凑，和主键自增相契合，如果主键不是自增，为了找相邻的伙伴寻道分页多麻烦！
非聚簇索引的索引顺序和数据物理排列顺序无关，叶子节点上的data是主键：记录的地址可能会变、主键不会
主键索引不一定是聚簇索引，聚簇索引可以是组合索引
i、前缀索引
#先看选择性，咱们程序员做事没别的就是靠谱节省，索引值不值得先看下选择性：
SELECt count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
#是因为那个 4 才决定复制过来的
ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));
#前缀他不能group by ，order by 也不能覆盖扫描
select
-> count(distinct left(city,3))/count(*) as sel3,
-> count(distinct left(city,4))/count(*) as sel4,
-> count(distinct left(city,5))/count(*) as sel5
-> from city_demo;
* 每次给字段加索引，对应的内容就会被复制出来一份https://blog.csdn.net/qq_29373285/article/details/85254407?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param
树
概念及部分
B树小名B-树：多路平衡查找树
阶数：一个结点最多有多少个孩子结点，用m标识
平衡二叉树
基于二分法提高查找速度的二叉树的数据结构
非叶子节点最多两个子节点，左孩子小于当前节点，右孩子大于
左右两边节点层级差<=1,避免树形结构由于删除增加变成线性链表
B树插入流程：https://blog.csdn.net/Fmuma/article/details/80287924
B+树
关键字个数比孩子结点个数小1
B-树、B+树
根节点开始二分查找，找到返对应data、否则对相应区间指针指向的节点递归查找
https://www.jianshu.com/p/71700a464e97
索引：
索引本身很大、不能全存内存中==》索引文件的形式存储在磁盘上
myisam
使用b+tree索引结构，叶节点data域存放的是数据记录的地址，主索引和辅助索引结构上无区别，主索引key唯一http://blog.codinglabs.org/articles/theory-of-mysql-index.html，5/100/170什么的是关键字
innodb
表数据文件是按B+Tree组织的索引结构：数据文件本身是索引文件，叶节点data保完整的数据记录，key为主键、表数据文件本身是主索引，所以一定要有可唯一标识数据记录的列做主键、不存在则为表自动生成6字节长整型的隐含字段为主键
非单调的主键造成插入新纪录时数据文件为维持B+树特性频繁分裂调整、低效
辅助索引
data域存储相应记录的主键值，英文以ASCII为准则，所有辅助索引引用主索引(主键)过长主索引令辅助索引过大
约束
不索引blob、text、很长varchar列的完整长度
排序
explain的extra=using filesort时使用文件排序，type=index使用索引扫描排序
索引列顺序和order by子句顺序一致且all列的排序方向一样，使用索引对结果排序
如查询多张表，order by子句引用字段全为第一个表时，才能使用索引来做排序
自己的排序算法：
在内存对数据排序，内存装不下将磁盘数据分块、对各数据块排序，合并成有序结果集，临时表：
1、两次扫描算法：待排序字段和可直接定位相关行数据的指针信息取出，内存中排序，指针信息取出列
2、一次扫描算法：一次性将所需字段全部取出，内存中排序后将结果输出
当前导列为常量时，ORDER BY子句可以不满足索引的最左前缀要求：
有： UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`)
mysql> EXPLAIN SELECt rental_id, staff_id FROM rental WHERe rental_date = '2005-05-25' ORDER BY inventory_id, customer_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: rental_date
key: rental_date
key_len: 8
ref: const
rows: 1
Extra: Using where
选择和命中：
http://blog.codinglabs.org/articles/theory-of-mysql-index.html
范围查询：
范围列可以用到索引(最左前缀)但范围列后的列无法用到索引，索引max用于一个范围列，有两个范围列则无法全用到索引
以下三列是组合索引：第一个between实际上相当于 IN (多值精确匹配)
EXPLAIN SELECT * FROM employees.titles
WHERe emp_no BETWEEN '10001' AND '10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
填坑列：原查询 缺少组合索引中间字段，打破最左前缀无法使用
加入 缺失的列 值不多：
EXPLAIN SELECT * FROM employees.titles
WHERe emp_no='10001'
AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
AND from_date='1986-06-26';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 7 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
选择性selectivity
指不重复的索引值与表记录数的比值，取值(0,1] 选择性与索引价值成正比，越大越有价值 ，你想想筛子、如果筛子就一个眼/孔那还有什么用，仿佛筛了一场空气
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
回表
通过索引扫描出数据所在行，通过行主键ID取索引未提供的数据：非主键索引的查询需要多扫描一颗索引树：是不是想起了非聚簇索引，小主此处提醒的是两者没有必然的联系
覆盖索引：一棵索引树上就可以获取sql所需所有列的数据，explain的extra=using index 触发了索引覆盖
优化
记录数过少如2000以下还是不要浪费精力去建索引了
https://blog.csdn.net/chai471793/article/details/99563704
https://www.cnblogs.com/ghoster/p/12510691.html
https://www.jianshu.com/p/74b2db536454
https://www.jianshu.com/p/d4646c98f00e
