MYSQL索引

索引介绍

#查看表存在的索引
show index from table_name

#创建索引
alter table table_name ADD INDEX index_name(key_name);
alter table table_name ADD FULLTEXT index_name(key_name);
alter table table_name ADD INDEX index_name(key_name);

#删除索引
alter table table_name DROP INDEX index_name;
注：还有一种通过create index 和 drop index 语法，原理一样

索引分类
# 按索引实现方式划分
 Btree、Hash

# 按功能划分
普通索引：单纯为了增加快速查询的速度。
全文索引：针对text字段类型加索引。
唯一索引：加快查询速度，且约束该列的值不能重复。
主键索引：和唯一索引功效类似，但是一张表只能有一个主见索引，而唯一索引有多个。

索引相关概念
#聚集索引（聚簇索引）
InnoDB表都有一个称为聚集索引的特殊索引（默认主键，没有则选择唯一索引,都没有则西安则隐式的rowid建）

#二级索引（非聚簇索引）
所有非聚集索引，都城为二级索引。

#单列索引
索引只涉及一个列

#多列索引（复合索引、联合索引、组合索引）
索引设计多个列（最多16列）

#覆盖索引
这不是一种索引，指的这种场景：“查询语句所需的列，被包含在查询条件中的索引列里面”
例：select count(a.user_id) from tb_users where a.user_id < 1000

B树的索引总结
聚集索引/聚簇索引 --主键 所有的列数据
辅助索引/二级索引 --索引列+主键。查其他的列--》回表（聚集索引/聚簇索引）
联合索引/复合索引 --（多个列<左边开始排序>+主键） ->覆盖索引 （不是索引）

高性能的索引创建
索引的作用
索引在查询中的作用
1.一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度。
2.一个select查询语句在执行的过程中一般最多能使用一个二级索引，即使where条件中用了多个二级索引。

高性能的索引创建策略
索引列的类型尽量小（针对主键）

索引列的选择
索引的选择性/离散性：不重复的索引值和数据表的记录总数（N）的壁纸（范围1/N到1）越高查询效率越高

为什么创建索引？ -> 筛选数据

前缀索引
针对blob、text、很长的varchar字段，mysql不支持索引他们的全部长度，需要建立前缀索引。
语法：Alter table table_name add key/index(column(X))
前缀索引的选择(X):
缺点：无法应用与order by 和group by，也无法做索引覆盖

后缀索引
mysql不支持后缀索引
可以通过在表中添加一个新列，用于保存要被建立后缀索引的字段倒排值，然后建立前缀索引。
场景：查询邮箱后缀

只为搜索、排序或者分组的列创建索引

多列索引（复合索引、联合索引、组合索引）
将选择性最高的列放到索引的最前列
根据那些运行频率最高的查询来调整索引列的顺序
优化性能时，需要使用相同的列担顺序不同的索引来满足不同类型的查询需求

三星索引
对于一个查询而言，三星索引可能是其最好的索引。 索引的宽度越窄越好
满足条件如下：
索引将相关的记录放到一起则获得一星 权重（27%）
如果索引中的数据顺序和查找的排列顺序一致则获得二星（排序星） 权重（23%）
如果索引中的列包含了查询中需要的全部列则获得三星（款索引星）权重（50%）

第三颗星：权重（50%）

MYSQL数据库高性能的索引使用策略

###不在索引列上做任何操作
>表达式
>函数

###尽量全匹配
>联合索引

###最佳左前缀法则
>key_len

###范围条件放到最后
>左边的列是精确查找，则右边的列可进行范围查找
>中间有范围查找会导致后面的列全部失效，无法充分利用联合索引

###覆盖索引尽量用
>尽量不要用*

###不等于要慎用
>使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

###Null/Not有影响

###字符类型加引号

###使用or关键字时要注意
>尽量不要使用*，不是索引的列会作为条件回表，非要走索引，用union all拼接

###排序要当心
>ASC、DESC别混用
>排序列包含非同一索引的列，不走索引
>用到联合索引排序列要是索引的顺序，正反都可以

###尽量能按主键顺序插入行
>不按顺序导致B+tree顺序不连续等一系列问题

###优化Count查询
>统计非空字段，值为空的时候导致统计数据不完整

###优化limit分页


###关于Null的特别说明
>尽量少用

MYSQL数据库查询技巧分析

#like
###走索引
>where email like 'onlyoneemail.com'
>where email like 'onlyoneemail.com%'

###不走索引
>where email like '%onlyoneemail.com'
>where email like '%onlyoneemail.com%'

#索引列能不能为空？
###IS NULL 走索引
>where email IS NULL
>where email = 'onlyoneemail.com'

###IS NOT NULL 不走索引
>where email IS NOT NULL

###不为空的字段，不查询数据
>where user_id IS NULL

#函数会不户走索引
>比全表扫描好一点，查询条件用范围，最好不用函数

#类型不一致会不户走索引
>走索引（有些情况优化器会转化）

#where条件顺序怎么写
>多列索引，顺序随便，最左前缀的概念
>不用组合索引，查询多个单列索引 会不会只有一个索引生效？ 索引合并

#要不要用UNION替代OR
>OR成本较高
>UNION成本组合较低

#EXISTS VS IN
>常见的情况下，子查询结果少，用IN，子查询结果多，用EXISTS
>根据实际运行情况分析
>常量查询用IN 

#非等于会不会走索引 != 和 <>
>count语句不查数据走索引
>200w 1999999= edu_159100060138810 ,(Cardinality 基数,数值越小，数据越集中)

#覆盖索引
>合理利用索引覆盖
>用索引字段替换*(用*的话会回表)

#用子查询还是表关联
>注意：第一次查询会变慢，buffer_pool调大一点（80%）
>大多数 可以 表关联
>建议用表关联

#表关联只大表小表
>不纠结根据实际情况来

#分页的玩法
>count(*)标准SQL 
>分页(查你需要的字段，不要*)
>递增ID，连续不中断
>递增ID，不连续，索引覆盖
>无需读取，做表关联，索引覆盖