- 索引的分类(算法)
B树 默认使用的索引类型
R树
Hash
FullText
GIS 索
建索引是为了加速查询，索引最好建立在区分度高的字段上
在表中有大量数据的情况下创建索引会很慢，因为要扫描所有的数据
索引建好后，查询速度会大幅度加快，但是写入速度会变慢
使用B+树索引时，对占用空间小的字段建索引，单个磁盘块就可以存储更多数据，树的高度会更低，查询时经历更少的IO次数
Btree索引功能上的分类
4.2 聚集索引
(1)MySQL 会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的.
(2)MySQL进行存储数据时,会按照聚集索引列值的顺序,有序存储数据行
(3)聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列向上生成枝和根
聚集索引（primary key）的叶子节点存放的是整条记录
由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。
InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。
而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。
聚集索引的好处之一：它对主键的排序查找？和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录
聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可
4.3 聚集索引和辅助索引的区别
(1) 表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可
(2) 在一张表中,聚集索引只能有一个,一般是主键.
(3) 辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.
(4) 聚集索引,叶子节点存储的是有序的整行数据.
(5) MySQL 的表数据存储是聚集索引组织表
辅助索引（unique,index）的叶子节点存放的是 {索引字段的值： 索引字段所在记录主键的值}
覆盖索引即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。
4.1 辅助索引
(1) 提取索引列的所有值,进行排序
(2) 将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点
(3) 在叶子节点中的值,都会对应存储主键ID
查询索引
desc city;
PRI ==> 主键索引
MUL ==> 辅助索引
UNI ==> 唯一索引
show index from table_name\G
创建索引
alter table t1 add primary key(id); #加主键
create [unique][cluster] index index _name on 表名(字段名);
alter table city add unique index uidx_dis(district); #唯一索引
select count(distinct district) from city; #distinct 去除重复值
前缀索引只能在字符串列上建，不能在数字列上建
mysql> alter table city add index idx_dis(district(5));#取district字段的前面5个字符
drop index index _name on 表名;
alter table city drop index idx_name;
联合索引
create [unique][cluster] index index _name on 表名(字段名1,字段名2,字段名3,.....);
尽量将区分度高的字段放在前面，将范围查询放在最后
联合索引：
-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
索引的最左匹配特性
8.1 未做优化之前测试
测试没有登录到mysql里面去
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='db1' \
--query="select * from db1.t100w where k2='MN89'" engine=innodb \
--number-of-queries=2000 -uroot -poldboy123 -verbose
100个用户，2000次查询，test是库名，t100w 是表名，k2是一个字段
2.3 possible_keys:可能会用到的索引
2.4 key: 真正选择了哪个索引
2.5 key_len 索引覆盖长度(索引列占用的字节数)
有一个额外的字节来标识是否为空，varchar类型还需要额外的两个字节来表示开始和结束
即 key_len(varchar)=字符个数*字节数+1bytes(标识是否为空)+2bytes(表示开始和结束)
如果索引列已经被标识为not null ，就不需要1bytes来标识
一个字符占用的字节数取决于编码方式utf8mb为4字节，utf8为3字节
varchar(20) utf8mb4
-
能存20个任意字符
-
不管存储的是字母,数字,中文,1个字符的最大预留长度都是4个字节
-
1个中文字符占4个字节
-
,1个数字字符或字母实际占用1个字节
select length(列名) from test;
已经建立了索引后，更改表
- 执行计划分析
9.1 作用
将优化器 选择后的执行计划 截取出来.便于管理判断语句的执行效率.
9.2 获取执行计划
desc SQL语句
explain SQL 语句
mysql>
mysql> desc select * from test.t100w where k2='MN89';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | t100w | NULL | ALL | NULL | NULL | NULL | NULL | 1027638 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
9.3 分析执行计划
9.3.1 table
表名
9.3.2 type
查询的类型:
全表扫描 : ALL
索引扫描 : index,range,ref,eq_ref,const(system),NULL
index: 全索引扫描
mysql> desc select countrycode from city;
range: 索引范围扫描(> < >= <= , between and ,or,in,like )
mysql> desc select * from city where id>2000;
mysql> desc select * from city where countrycode like 'CH%';
对于辅助索引来讲,!= 和not in等语句是不走索引的
对于主键索引列来讲,!= 和not in等语句是走range
===
mysql> desc select * from city where countrycode='CHN' or countrycode='USA';
mysql> desc select * from city where countrycode in ('CHN','USA');
一般改写为 union all
desc
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';
ref: 辅助索引等值查询
desc
select * from city where countrycode='CHN'
union all
select * from city where countrycode='USA';
eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件
A join B
on A.x = B.y
desc select b.name,a.name ,a.population
from city as a
join country as b
on a.countrycode=b.code
where a.population<100;
const(system) : 主键或者唯一键的等值查询
mysql> desc select * from city where id=100;
5.1 建立索引的原则（DBA运维规范）
(1) 建表必须要有主键,一般是无关列,自增长
(2) 经常做为where条件列 order by group by join on, distinct 的条件
(3) 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
(7) 小表不建索引
5.2没有用到索引的情况（开发规范）
(1) 没有查询条件，或者查询条件没有建立索引
select * from city;
select * from city where 1=1;
(2) 查询结果集是原表中的大部分数据，应该是25％以上。
(3) 索引本身失效，统计数据不真实
面试题:同一个语句突然变慢?
统计信息过旧,导致的索引失效
(4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
mysql> desc select * from city where id-99=1;
(5) 隐式转换导致索引失效.
举例：telphone_number字段为char类型，select * from city where telphone_number=123;
123没有加引号也能查到结果，因为系统自己使用函数把数字类型转换成了字符类型
(6) <> ，not in 不走索引（辅助索引）
(7) like "%aa" 百分号在最前面不走
(8) 联合索引