8.28笔记（mysql索引）

基于算法的索引类型（mysql都支持）
B树索引
Hash索引 
R树
Full text
GIS
基于功能（底层算法是b树）的索引类型：
1.普通索引
2.唯一索引
3.主键索引
4.组合索引
BTree树算法
遍历----二叉树----平衡二叉树----balance tree（多路二叉树）
b树方式：
针对底层数据生成上层的数据节点，包含下层的两个节点的起始数据
然后最后生成根节点包含上层节点的所有起始数据。
btree种类
B-tree
B+Tree 在范围查询方面提供了更好的性能(> < >= <= like)
范围类查找做了双向指针，底层数据直接跳转。
B*Tree b+tree的增强版
枝节点增加双向指针。减少io查询。



mysql B+树索引构建过程：
结构：

聚簇索引（innodb独有）
区===簇====64个pages 页===1m
聚簇索引（innodb独有）
区===簇====64个pages 页===1m
前提：
1。建表时指定了主键列。mysql innodb 将会把主键作为聚簇索引，
比如，ID，not null，primary key
2。没有指定主键，自动选择唯一键（unique）的列作为聚簇索引。
3。以上都没有，生成隐藏聚簇索引。
作用：
有了聚簇索引后，将来插入数据行，在同一区内，都会按照ID值顺序，有序在磁盘存储。
mysql innodb聚簇索引组织存储数据表。


辅助索引
在普通列上建立索引。需要人为创建
作用：
弥补聚簇索引欠缺的地方。非聚簇索引查询条件之外的优化。
alter table t1 add index idx(name);
辅助索引会单独查询一个列然后再回查聚簇索引。
辅助索引的类型：
单列：alter table t1 add index idx(name);
联合索引：alter table t1 add index idx(name，addr);
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表
查询
建立索引结构的时候存储索引最左列的值生成枝节点
查询条件必须有最左列，不写最左列是不走索引
建立联合索引时一定要选重复值少的列作为最左列。
例如索引 index(a,b,c) ---a, ab,abc,
select * from t1 where a= ...会走索引。
in也是可以走索引
部分覆盖
a= and b=
a=
a= and c=
a= and b >< >= <= like and c=
a= order by b
不覆盖
bc
c
b
CREATE TABLE department10 (
		dept_id INT,
		dept_name VARCHAR(30) ,
		comment VARCHAR(50),
		INDEX indep(dept_name)
		);
总结：选择联合索引。、
1，最左原则，一定选择查询频繁的列。查询语句中一定有该列。
2，选择重复行少的列作为最左联合索引。
3，查询语句中，如果不包含最左联合索引，会引起，联合索引失效。

前缀索引：
字符串过长，占用过多的索引空间。（索引树过高）
所以可以选择大字段前面部分字符作为索引生成条件。
导致索引应用的时候io次数过多。
MySQL中建议，索引树高度3-4层
alter table city add index idx_di(district(5));
B+Tree索引树高度影响因素：
1.索引字段过长：前缀索引。
2.数据行过多：分区表，归档表。（pt-archive),分布式架构（大企业）
3.数据类型：选择合适的数据类型，减少索引树的高度。
索引的管理命令：
什么时候该创建索引？
按照业务需求创建合适的索引。并不是把所有列创建索引。不是索引越多越好
将索引建立在经常where group by order by join on ...等查询条件上
为什么不能乱建索引？
冗余索引过多，表数据变化的时候会引起索引更新，会阻塞正常业务更新请求。
索引过多，会引起优化器出现偏差。
索引不能完全覆盖的情况下会出现回表，如果回表过多会产生大量I/O 导致iops增大。
随机io增大。
怎么避免或者减少回表？
1.将查询尽可能的id的主键列查询。
2.设置合理的辅助索引或者联合索引。（完全覆盖）（精确的查询条件）
3.
索引维护：
查询索引：desc 表名 key:pri聚簇索引， mul辅助索引 uni唯一索引
show index from 表


insert update delete 语句会引起聚簇索引立即更新
辅助索引不是实时更新。
在innodb的内存结构中，加入了insert buffer（会话），现在版本叫change buffer


建索引：
分析业务语句。
假设：select * ftom city where name="wuhan";
查询较多，用name字段做索引：
alter table city add index inx_na(name);
删除索引:
alter table city drop index inx_na;
修改一般删除重新创建
压力测试：
导入t100w.sql
命令：
mysqlslap --defaults-file=/etc/my.cnf  \
--concurrency=100（人数） --iterations=1 --create-schema='test'  \
--query="select * from test.t100w where k2='780p'" engine=innodb  \
--number-of-queries=200（查询200次） -uroot -p123 -verbose

mysqlslap --defaults-file=/etc/my.cnf  \
--concurrency=100 --iterations=1 --create-schema='test'  \
--query="select * from test.t100w where k2='780p'" engine=innodb  \
--number-of-queries=200 -uroot -p123 -verbose

建立索引：
alter table t100w add index idx_k2(k2);
再次运行进行对比。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++
什么是执行计划？
先对语句进行解析。然后生成执行方案。
分析的是优化器按照内置的cost（资源消耗）计算算法，最终选择后的执行计划。
cost？代价和成本，主要是cpu和io
io等级高于cpu，内存
查看执行计划：
执行语句前面增加desc或者explain

执行计划的显示结果分析：
table: city          ---->查询操作的表    
possible_keys: CountryCode,idx_co_po    ---->可能会走的索引 
key: CountryCode   ---->真正走的索引名字/    
key_len: 索引覆盖长度           
type: ref   ---->查询类型    全表扫描或者索引扫描
rows:  此次数据扫描的行数
Extra: Using index condition    ---->额外信息        
表：
查询多表时，精确到问题表。


type：
全表扫描：不用任何索引，ALL
从左到右性能依次变好.
all < index <range < ref < eq_ref < system,const
ALL  :  
全表扫描,不走索引
例子:
1. 查询条件列,没有索引
SELECT * FROM t_100w WHERE k2='780P';  
2. 查询条件出现以下语句(辅助索引列)
USE world 
DESC city;
DESC SELECT * FROM city WHERE countrycode <> 'CHN';
DESC SELECT * FROM city WHERE countrycode NOT IN ('CHN','USA');
DESC SELECT * FROM city WHERE countrycode LIKE '%CH%';
注意:对于聚集索引列,使用以上语句,依然会走索引
DESC SELECT * FROM city WHERE id <> 10;

INDEX  :
全索引扫描
1. 查询需要获取整个索引树种的值时:
DESC  SELECT countrycode  FROM city;

2. 联合索引中,任何一个非最左列作为查询条件时:
idx_a_b_c(a,b,c)  ---> a  ab  abc

SELECT * FROM t1 WHERE b 
SELECT * FROM t1 WHERE c  
可能会产生的问题:
(1) 每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
(2) 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
(3) 优化器的负担会很重,有可能会影响到优化器的选择.
percona-toolkit中有个工具,专门分析索引是否有用
删除不再使用或者很少使用的索引(percona toolkit)
pt-duplicate-key-checker

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

大表加索引,要在业务不繁忙期间操作

尽量少在经常更新值的列上建索引
建索引原则
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列  order by  group by  join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期
++++++++++++++++++++++++++++++++++++++++++++++++++
不走索引的情况：
1.没有查询条件，或者查询条件没有建立索引
select * from tab;       全表扫描。
select  * from tab where 1=1;
在业务数据库中，特别是数据量比较大的表。
是没有全表扫描这种需求。
1、对用户查看是非常痛苦的。
2、对服务器来讲毁灭性的。
（1）
select * from tab;
SQL改写成以下语句：
select  * from  tab  order by  price  limit 10 ;    需要在price列上建立索引
（2）
select  * from  tab where name='zhangsan'          name列没有索引
改：
1、换成有索引的列作为查询条件
2、将name列建立索引

2.查询结果集是原表中的大部分数据，应该是25％以上
查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
与数据的预读能力有关。
假如：tab表 id，name    id:1-100w  ，id列有(辅助)索引
select * from tab  where id>500000;
如果业务允许，可以使用limit控制。
怎么改写 ？
结合业务判断，有没有更好的方式。如果没有更好的改写方案
尽量不要在mysql存放这个数据了。放到redis里面。

3.索引本身失效，统计数据不真实
索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
一般是删除重建

现象:
有一条select语句平常查询时很快,突然有一天很慢,会是什么原因
select?  --->索引失效,，统计数据不真实
DML ?   --->锁冲突

4.查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
例子：
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
算术运算
函数运算
子查询

5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
这样会导致索引失效. 错误的例子：
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id    | int(11)    | YES  |    | NULL    |      |
| name  | varchar(20) | YES  |    | NULL    |      |
| telnum | varchar(20) | YES  | MUL | NULL    |      |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

|  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql>

6.<> ，not in 不走索引（辅助索引）
EXPLAIN  SELECT * FROM teltab WHERE telnum  <> '110';
EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN ('110','119');

mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum <> '1555555';

单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in  尽量改成union
EXPLAIN  SELECT * FROM teltab WHERE telnum  IN ('110','119');
改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

7.like "%_" 百分号在最前面不走
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'  走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引
%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
索引的自优化
优化参数：show variable "switch%"
如何修改：
1./etc/my.cnf
2.set global optimizer_switch='xxx参数=on';
3.hints 方式，用/*里面针对某个语句开启单个方法。

mysql的innodb引擎只有btree结构（会自适应hash）会将热点数据生成索引页的索引。
AHI作用：会自评估热点内存索引，page，生成hash，加快innodb快速读取索引页。
类似索引的索引。
change buffer
在做insert update，delete 操作时，辅助索引会临时更新存储到change buffer，
之后在使用的时候，会自动进行merge（合并操作）

ICP
索引下推。
解决了联合索引部分应用的情况。
减少没必要的数据页扫面。
index (a,b,c)假设部分索引符合：
select * from t1 where a=server层已经判断 and c=到引擎层才做判断。
将优化下推到引擎层进行优化。
再sever层先做a列过滤索引优化，再将c列的过滤下推到engine层做过滤，加载数据页。
 
MRR
mmuti range read 关闭mrr_cost_base,开启mrr
辅助索引---回表---聚簇索引
引擎层缓冲区转换为
辅助索引----sort id ---回表---聚簇索引（减少回表次数)
多路读取。


SNLJ
a join b on a.xx=b.yy where ...
伪代码：
for each row in a matching range (
    for each row in b (
	  a.xxx = b.yyy send to client
    )
)	
a叫驱动表，尽量驱动表数据行少些。
以上例子可以通过left join 强制驱动表
逐行a进行循环。

BNLJ
在SNLJ基础上，变成块嵌套循环，a表内数据变成数据块放到join buffer，再进行循环
一次性将驱动表内关联数据值与非驱动表进行匹配。
主要优化了cpu的消耗。减少了io消耗



BKA
在bnlj基础上，用来优化非驱动表的关联列，关联列有辅助索引。
开启方法：
先开启mrr，在开启bka