select distinct
<select_list>
<left_table> <join_type>
join <right_table> on <join_condition>
where
<where_condition>
group by
<group_by_list>
having
<having_condition>
order by
<order_by_condition>
limit
<limit_number>
而对于mysql服务来说，是从from开始解析的
<left_table>
on <join_condition>
<join_type> join <right_table>
where
<where_condition>
group by
<group_by_list>
having
<having_condition>
select
distinct
<select_list>
order by
<order_by_condition>
limit
<limit_number>
7中join理论
==内连接==：inner用于查交集，即多表中的共有数据
==左连接==：left join则将覆盖左表，可以查左表及交集或剔除同时存在于左右表的数据
==右连接==：right join覆盖右表，可以查右表及交集或剔除同时存在于左右表的数据
==全外连接==：full outer join覆盖左右表，可以查笛卡尔积，左右表所有数据或剔除左右表共有数据
注意：MySQL不支持FULL OUTER JOIN，但是我们可以对左连接和右连接的结果做union操作来实现
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中
多个 SELECT 语句会删除重复的数据（可以用UNION ALL查询重复）
SELECT 列名称 FROM 表名称 UNION [ALL] SELECT 列名称 FROM 表名称 ORDER BY 列名称;
官方对于索引的定义是：索引（index）是帮助mysql高效获取数据的数据结构
可以理解为：一种排好序的高效率查找的数据结构
对于mysql中innodb引擎到底是B树还是B+树，其实网上有很多争议，我们查看mysql开发手册
可以看到，开发手册中提到
Everyone has seen a B-tree and knows that the entries in the root page point to the leaf pages. (I indicate those pointers with vertical '|' bars in the drawing.) But sometimes people miss the detail that leaf pages can also point to each other (I indicate those pointers with a horizontal two-way pointer '<-->' in the drawing). This feature allows InnoDB to navigate from leaf to leaf without having to back up to the root level. This is a sophistication which you won't find in the classic B-tree, which is why InnoDB should perhaps be called a B+-tree instead.
每个人都看到过B树，并且知道根页面中的条目指向叶页面。（我在图形中用垂直“|”条表示这些指针。）但有时人们会忽略页页也可以相互指向的细节（我在图形中用水平双向指针“<-->”表示这些指针）。此功能允许InnoDB从一个叶导航到另一个叶，而无需备份到根级别。这是一种在经典的B-树中找不到的复杂性，这就是为什么InnoDB应该被称为B+树的原因。
mysql的索引同样也是用到的B+树
注：此处存疑，我是5.5的mysql，建立索引后，显示index_type为BTREE，即B树
索引的目的在于：提高查询的效率，可以类比为有目录的书籍或字典
如果没有索引，将会进行全表扫描，在数据量庞大的时候，是非常影响效率的！
索引的优点与缺点
快速查询，提高检索效率，降低了IO成本
对数据排序，降低了数据排序的成本，降低cpu的消耗
索引也是一个文件，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大提高了检索效率，但是会降低更新表的效率
更新表时，mysql不仅要保存数据，还要更新索引文件每次更新添加了索引列的字段
索引是提高效率的一个因素，但是如果mysql中有大量的表时，需要花时间研究建立最符合业务需求的索引或者优化查询
一般来说索引也是非常大的，不可能全部存储在内存当中，因此索引往往以索引文件形式存储在磁盘上
既然索引意义在于排序和查找，那么它就会影响到sql中的where查找和order by排序
一个索引只包含一个列
一个表中可以有多个单值索引
适用于单个列查询需求量大时的情况
复合索引也就是多列索引，由多列组合创建的索引，使用的时候遵循最左前缀原则
最左前缀原则：在查询条件中使用了复合索引的第一个字段，索引才会被使用
索引列的值必须唯一，允许有空值
用关键字UNIQUE把索引定义为唯一索引
主键索引是特殊的唯一索引，**不允许存在空值 **
create [unique] index 索引名 on 表名(列名(长度));
alter table 表名 add index [索引名] (列名(长度));
1、PRIMARY KEY（主键索引）
ALTER TABLE table_name ADD PRIMARY KEY ( column )
2、UNIQUE(唯一索引)
ALTER TABLE table_name ADD UNIQUE (column)
3、INDEX(普通索引)
ALTER TABLE table_name ADD INDEX index_name ( column )
4、FULLTEXT(全文索引)
ALTER TABLE table_name ADD FULLTEXT ( column )
5、多列索引
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
drop index [indexName] on tableName;
show index from tableName
BTree索引
B+树图示
背景板浅蓝色的块我们称之为一个磁盘块
可以看到每个磁盘块包含几个数据项(深蓝色小块)和指针(黄色小块) ,
如磁盘块1包含数据项17和35，包含指针P1、 P2、 P3
B树的特点：每个节点都有数据和指针
一个节点可以存多个元索，多个元索也排序了
每个节点都存储key和data
B+树的特点：数据只存在于叶子节点
拥有B树的特点
非叶子节点上的都是索引指针，叶子节点中存储了所有的元素与索引，并且排好了顺序
叶子节点之间有指针
增加了顺序访问指针 ，每个叶子节点增加一个指向相邻叶子节点的指针
只有叶子节点存储data，叶子节点包含了这棵树的所有索引和数据
在B+树中
真实的数据存在于叶子节点，即5,8,9,10,15,18,20,26,27,28,30,33,35,38,50,56,60,63,65,73,79,80,85,88,90,96,99
非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，例如5、28、65并不真实存在于数据表中
Hash索引
hash索引由于其结构，所以在每次查询的时候直接一次到位，不像b-tree那样一点点的前进。所以hash索引的效率高于b-tree，但hash也有缺点，主要如下：
由于存放的是hash值，所以仅支持<=>以及in操作
hash索引无法通过操作索引来排序，这是因为存放的时候经过hash计算，但是计算的hash值和存放的不一定相等，所以无法排序
在组合所以里，无法对部分使用索引
不能避免全表扫描，只是由于在memory表里支持非唯一值hash索引，就是不同的索引键，可能存在相同的hash值
当存在大量相同hash值得时候，hash索引的效率会变低
full-text索引
full-text在mysql里仅有myisam支持它，而且支持full-text的字段只有char、varchar、text数据类型
full-text主要是用来代替like "%***%"效率低下的问题
R-Tree索引
r-tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。
相对于b-tree，r-tree的优势在于范围查找.
应不应该创建索引
哪些情况需要创建
主键自动创建唯一索引，默认命名为PRIMARY
频繁作为查询条件的字段
与其他表关联，作为查询条件的外键字段
单键索引/复合索引？高并发下推荐复合索引
查询中排序的字段，排序字段通过索引访问将提高排序速度
查询中统计或分组的字段，group by也要排序，所以也推荐建立索引
哪些情况不需要创建
表记录太少，建了也没效果，可能引起索引失效
频繁更新的字段，更新字段的同时也会更新索引，消耗性能、降低效率
where条件中不使用的字段
经常增删改的表
包含许多重复内容的数据列，建立索引没有太大的效果
假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99
一个索引的选择性越接近于1，这个索引的效率就越高
Explain的使用
explain是什么？
使用Explain关键字，可以模拟mysql语句优化器执行优化sql语句，从而知道mysql是如何优化的，以分析sql语句或者表结构的性能瓶颈如何
id type key rows Extra比较重要，记得复习复习
explain怎么用？
explain+sql语句即可
explain select * from t_user;
explain select * from t_user where uid=1;
表的读取顺序
并不是怎么写的就怎么个执行顺序，因为mysql会进行重写排序（有点类似于java的指令重排）
table对应的表名
id为执行等级
id相同时，从上至下执行
id越大的table越先被执行
当table为<derived2>时，表示这个table是由id为2的表执行后衍生出来的虚表（子查询给别名就会出现）
数据读取的操作类型
select_type是查询的类型
用于区分普通查询、联合查询、子查询等的复杂查询
simple
简单的select查询，不包含子查询或者union
primary
查询中若包含任何复杂的子部分，最外层的查询将标记为PRIMARY
subquery
select或where后跟的子查询
derived
在from列表中包含的子查询衍生出的临时虚拟表将标记为DERIVED
union
若第二个select出现在union之后，则被标记为UNION
若UNION包含在from子句的子查询中，外层select将被标记为DERIVED
union result
从UNION表获取结果的select
type是访问类型
显示查询用了哪种类型
从最好到最差依次是
system>const>eq_ref>ref>range>index>ALL（ALL就是全表扫描）
一般来说，至少保证查询要在range级别，最好能达到ref
system
表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计
const
**单表查询情况下通过索引一次就找到了，**const用于比较primary key主键或者unique聚簇索引。
因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换一个常量
eq_ref
多表查询的情况下的唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
常见于主键或唯一索引扫描的联合查询
非唯一性索引扫描，返回匹配某个单独值的所有行
本质上也是一种索引访问，它返回所有匹配某个单独值的行
它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range
只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引
一般就是在where语句中出现了between、<、>、in等的查询
这种范围扫描索引扫描比全索引扫描要好，因为它只需要开始于索引的某一点， 而结束语另一点，不用扫描全部索引（前提是加了索引）
index
Full Index Scan, index 与ALL区别为index类型只遍历索引树
这通常比ALL快，因为索引文件通常比数据文件小
也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的
遍历全表以找到匹配的行
哪些索引可以被使用，哪些索引被实际使用了
possible_keys显示可能引用在这张表的索引，一个或多个
查询涉及到的字段上若存在索引，则该索引将会被列出，但查询时不一定被实际引用！
keys表示实际用到的一个索引，如果为null则没有使用索引
查询中若使用了覆盖索引，则该索引仅出现在key列表中
如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表
类似于sql语句EXPLAIN select * from t_user where uname='yyf' and uid=2
其中uid是默认的主键索引，uname是创建的普通索引
possible_keys为PRIMARY,index_user_uname，表示可能用到的索引有两个
而keys为PRIMARY，表示实际用到的是主键索引
key_len表示索引中使用的字节数
可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_ len 显示的值为索引字段的最大可能长度，并非实际使用长度
即key_ len是 根据表定义计算而得，不是通过表内检索出的
表之间的引用
ref显示索引的哪一列被使用了
如果可能的话，是一个常数（const）
哪些列或常量被用于查找索引列上的值
每张表有多少行被优化器查询了
rows根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
如果是全表扫描，这个值会很大，效率也就很低
extra包含不适合在其他列中显示但十分重要的额外信息
Using filesort
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
filesort是通过相应的排序算法,将取得的数据在内存中进行排序
MySQL中无法利用索引完成的排序操作称为"文件排序"
在MySQL中filesort 的实现算法实际上是有两种
双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序
单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。
表示相应的select操作中使用了覆盖索引(Covering Index)， 避免访问了表的数据行，效率不错
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
Using where
表明使用了where过滤
using join buffer
使用了连接缓存
impossible where
where子句的值总是false，不能用来获取任何元组
select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作
或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
mysql存储过程创建循环
这里我们写一个mysql的循环insert数据，用于测试
因为mysql是不能像java直接while循环的，需要创建并调用存储过程
存储过程是一个代码段，在mysql执行过程中，遇到分号就执行了，怎么去改掉分号呢，让代码继续执行呢，用delimiter
delimiter就是告诉mysql解释器，该段命令已经结束了，可以执行了。
默认情况下，delimiter是分号; 遇到分号就执行。
后面的//符号 就是告诉mysql，遇到//再执行
delimiter // #定义标识符为双斜杠
drop procedure if exists test; #如果存在test存储过程则删除
create procedure test() #创建无参存储过程,名称为test
begin
declare i int default 0; #申明变量并赋值
while i < 1000 do #结束循环的条件: 当i大于10时跳出while循环
insert into test(username,balance) values ("while",i); #往test表添加数据
set i = i + 1; #循环一次,i加一
end while; #结束while循环
// #结束定义语句
delimiter ; #改回原来的标识符
call test(); #调用存储过程
select * from test order by id desc limit 0,1000; #查看test表数据
我们创建一个表，并在表中插入10万条数据，用于测试
CREATE TABLE `t_index` (
`id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
`uid` VARCHAR(255) not NULL,
`username` varchar(255) not NULL,
`password` VARCHAR(255) not null,
`balance` int unsigned zerofill not NULL,
`views` int not null,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
delimiter //
drop procedure if exists test;
create procedure test()
begin
declare i int default 0;
while i < 100000 do
insert into t_index(uid,username,`password`,balance,views)
values (i,"while","while",i,i);
set i = i + 1;
end while;
delimiter ;
call test();
select * from t_index order by id desc limit 0,1000;
select查询语句写的差劲
当业务中发现，sql的执行时间很长，等待时间很长时，有哪些可能引起的因素呢？
下面来分析一下
查询了不需要的记录
像应用程序select * 时其实并不需要全部的数据，却没有去加limit进行限制，从而把全表的数据都捞出来，显然多此一举。
单条查询最后增加 LIMIT 1，如果说找到一条就停止扫描了，无需全表扫描
多表关联时返回全部列
多个表进行关联，像一些没有什么意义的列也全部查出来，也是会影响性能。
总是取出全部的列
像select * 这类查询，取出全部列，不一定会完成索引覆盖这类优化，会触发各种回表查询，为服务器带来额外的IO、内存和CPU消耗。
重复查询相同的数据
对于每次查询都返回同样结果的这类查询，其实查一遍就够了，把结果存到Redis这类缓存中，减轻MySQL的压力。
小表驱动大表
即：先操作小的数据集，再操作大的数据集
in和exists怎么选用？
in先查内表，exists先查外表
所以in里的子集小时，优先用in，反之用exists
exists：
将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE或FALSE)来决定主查询的数据结果是否得以保留
因为只返回结果TRUE/FALSE，所以子查询中不管是select *还是select 1或其他都可以，执行时会忽略字段的
in中的查询只执行一次，它将查询出的所有的数据缓存起来，然后检查外表中查询出的字段在缓存中是否存在，如果存在则将外表的查询数据加入到结果集中，直到遍历完外表中所有的结果集为止
# 先查B表，再放入A表查询
select * from A where id in (select id from B)
# 先查A表，再联系B表查询
select * from A where exists (select 1 from B where B.id = A.id)
我们的宗旨是先操作小的，那么当B数据量比A小的时候，我们应该先操作B表
而in先查内表，即先查B表，所以：B小用in
当A数据量比B小的时候，我们应该先操作A表
而exists先查外表，即先查A表，所以：A小用exists
order by排序出现FileSort
order by字句，尽量使用index排序，即索引的排序，避免使用filesort排序，即文件排序
尽可能的在索引列上完成排序操作（默认的ASC排列）
切记：order by严格按照顺序判断是否索引
使用index排序的两种条件：
order by语句使用索引最左前列原则
使用where字句与order by字句条件列组合满足索引最左前列原则
什么时候会出现filesort呢：
未建立索引
不遵守最左前缀原则
不一致遵守默认的ASC顺序（就是一个使用ASC，一个使用DESC），索引失效
当然只要都是ASC或都是DESC的话，就不会失效
两次扫描磁盘，最终得到数据。比较耗时，效率较低
取出数据的行指针和order by列，对它们进行排序，然后扫描已经排序好的列表，按照表中的值从列表中重新读取对应的数据输出
从磁盘读取排序字段，在sort_buffer进行排序，再从磁盘取其他字段
单路排序（mysql4.1之后）：
是一次性取出满足条件行的所有字段，按照order by列在sort_buffer中进行排序，然后扫描排序后的字段进行输出
它的效率更快一些， 避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在了内存中进行排序
如果一次没取的完就出问题了，就要分批次去执行IO操作取数据，反而效率变低了
增大sort_buffer_size参数，增大缓冲区大小
增大max_length_for_sort_data参数
最好不要用select *，导致查询的数据过大
当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序，所以可以提高max_length_for_sort_data参数
两种算法的数据都有可能超出sort_buffer的容量， 超出之后，会创建tmp文件进行合并排序，导致多次I/O， 但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size参数
小小总结一波
create index test on test(a,b,c);
为a、b、c字段创建联合索引
遵循最左原则：可以使用索引
order by a
order by a,b
order by a,b,c
order by a DESC , b DESC , c DESC
where a = const ORDER BY b , c
where a=const and b=const ORDER BY c
where a=const and b>=const ORDER BY b , c (虽然b是范围，但是遵循了a-b-c，所以有索引)
ORDER BY a DESC , b ASC , c DESC (排序不一致)
ORDER BY b , c (丢失a索引)
where a=const ORDER BY c (丢失b索引)
where a=const ORDER BY b , d (d不是索引的一部分)
where a in (...) ORDER BY b , c (in也是范围查询，索引失效)
group by的情况和order by差不多
group by的本质是：先排序再分组，遵照索引的最左前缀原则
where优先级高于having，所以能写在where的条件就不要写在having了
索引失效的7种条件
索引失效是建立了索引，但是没有用上
可以看一下博文《索引失效和注意事项》，总结的比较全面
下面是可能出现索引失效的情况
索引失效口诀：模型数空运最快
模：模糊查询LIKE以%开头
型：数据类型错误
数：对索引字段使用内部函数
空：索引列是NULL
运：索引列进行四则运算
最：复合索引不按索引列最左开始查找
快：全表查找预计比索引更快
1、单独引用复合索引里非第一位置的索引列
复合索引遵守“最左前缀”原则
即在查询条件中使用了复合索引的第一个字段，索引才会被使用。
因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。
假如有INDEX(a,b,c)，
当条件为 a 或 a,b 或 a,b,c 时都可以使用索引，
但是当条件为 b,c 时将不会使用索引。
因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减
实际上是会去索引文件里查的，type为index
2、对索引列进行运算
运算包括（+、-、*、/、！、<>、!=、%、or、in、exist等），导致索引失效
or导致索引失效：使用union
注意or也是会导致索引失效的，所以要么在业务中进行运算，要么在mysql中使用union
union 用于把来自多个select 语句的结果组合到一个结果集合中
当使用union时，mysql 会把结果集中重复的记录删掉
使用union all时，mysql 会把所有的记录返回，且效率高于union
select * from t_index where uid ='921930193014' or balance = 499010
# ↑改为↓，使用union
select * from t_index where uid ='921930193014'
UNION
select * from t_index where balance = 499010
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
sql 中如果使用了 not in ， not exists ， <> ， ！= 这些不走索引 range类型的查询条件后的字段索引无效
< ， > ， <= ， >= ，这个根据实际查询数据来判断，如果全盘扫描速度比索引速度要快则不走索引
range类型的范围查询字段及其后面的素引无效
什么是range类型的查询条件及其后面的字段索引无效？
我们来看一个例子
t_index表有140w的数据，结构如下
CREATE TABLE `t_index` (
`id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
`uid` varchar(255) NOT NULL,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`balance` int(10) unsigned zerofill NOT NULL,
`views` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1400001 DEFAULT CHARSET=utf8;
CREATE index index_tindex_username_balance_views on t_index(username,balance,views)
EXPLAIN
select * from t_index where username = 'while' and balance >= 499008 ORDER BY views DESC limit 1
建立username,balance,views3个字段的索引，sql查询如上，我们来查看这个sql的效果
可以看见，虽然使用到了3字段索引，可是查询的type还是range级别的，而且extra中是using filesort外部文件排序，没有使用到views的索引排序，当然效率会低一些
type变成了range,这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。
但是我们已经建立了索引,为啥没用索引的排序呢?
这是因为按照BTree索引的工作原理,
先排序username
如果遇到相同的username，则再排序balance，如果遇到相同的balance，则再排序views
当balance字段在联合索引里处于中间位置时,
因balance > 499008 的条件是一个范围值(所谓range)
MySQL无法利用索引再对后面的views部分进行检索，即range类型范围查询字段及其后面的素引无效
那我们如何解决这个问题呢？去掉范围条件字段的索引，即：减去balance的索引列
只添加username和views的索引
DROP index index_tindex_username_balance_views on t_index
CREATE index index_tindex_username_views on t_index(username,views)
EXPLAIN
select * from t_index where username = 'while' and balance >= 499008 ORDER BY views DESC limit 1
可以看到type变为ref，ref显示const常量，extra也没有了using filesort
优化器根据索引顺序优化
当然，mysql的优化器是会根据索引顺序进行对应的优化的
（mysql不完全顺从sql语句的条件顺序，会根据索引来执行最优顺序达到最优结果）
我们来看这样一张表，有字段a ，b， c， d
并为abcd同时创建多列索引
CREATE TABLE `abcd` (
`id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
`a` varchar(255) NOT NULL,
`b` varchar(255) NOT NULL,
`c` varchar(255) NOT NULL,
`d` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `i` (`a`,`b`,`c`,`d`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
# 多执行几次插入用于测试数据
insert into abcd(a,b,c,d) values('a','b','c','d');
CREATE index i on abcd(a,b,c,d)
我们来看一下下面4条sql的执行情况
1、正常顺序a-b-c-d
a='a' and b='b' and c='c' and d='d'
EXPLAIN
select * from abcd where a='a' and b='b' and c='c' and d='d'
那当然是type为ref啦，正常使用索引，ref显示4个const，表示这4个全都走了索引
2、条件倒序d-c-b-a
d='d' and c='c' and b='b' and a='a'
EXPLAIN
select * from abcd where d='d' and c='c' and b='b' and a='a'
和正常顺序没有区别，因为优化器将顺序调整为了和索引一致的a-b-c-d
3、a-b 范围d 再c
上面我们提到，调整为了与索引一致的a-b-c-d顺序，那么我们如果将d提到c前面，并使用范围索引，那会不会导致c的索引失效呢？
结果是：不会的
EXPLAIN
select * from abcd where a='a' and b='b' and d>='d' and c='c'
我们可以看见，ref还有3个const，因为ref显示索引的哪一列被使用了，所以我们可以得出
这条sql一定是被优化成了where a='a' and b='b' and c='c' and d>='d'
所以a、b、c都走了索引，d因为范围查询没有走
4、a-b 范围c 再d
如果还是a-b-c-d的顺序，然后c使用范围查询，d还走索引嘛？
那当然不走了，上文提到了范围查询字段后面的素引无效，所以c和d都不走索引了
那ref应该只剩2个const了，即a和b，让我们来看看结果
EXPLAIN
select * from abcd where a='a' and b='b' and c>='c' and d='d'
结果无误，ref只有2个const，即a和b走索引，c范围查询不走且导致后面的d也不走索引
5、a-b-d 并根据c排序
直接上代码
EXPLAIN
select * from abcd where a='a' and b='b' and d='d' ORDER BY c
此时会用到几个索引？2个、3个还是4个？
答案可以说是3个
可能有人会说这不是只有2个const嘛，其实严格上来说，用到了c字段索引来排序而不是查找，故没统计
（如果c没走索引，应该是显示using filesort，见例6）
而这个const指的是a和b的常量
6、a-b 根据d排序
EXPLAIN
select * from abcd where a='a' and b='b' ORDER BY d
中间c断了，d当然不走索引了，所以只有a和b走了索引
所以在extra中显示了using filesort
7、a 根据c、b排序
我们先说一下where a='a' ORDER BY b,c用到了几个索引？
3个，a、b、c都用到了
根据顺序来的，a用于查找，b、c用于排序，无Using filesort，用到了3个索引，没毛病把
进入正题，下列sql用到了几个索引？
1个，只有a用到了，c和b没有用到，因为不符合顺序，在order by中严格遵守顺序
所以出现了Using filesort
EXPLAIN
select * from abcd where a='a' ORDER BY c,b
3、对索引应用内部函数
这种情况下应该建立基于函数的索引
select * from template t where ROUND(t.logicdb_id) = 1;
此时应该建ROUND(t.logicdb_id)为索引。
4、类型错误
如字段类型为varchar，where条件用number
例：template_id字段是varchar类型。
错误写法：select * from template t where t.template_id = 1
正确写法：select * from template t where t.template_id = ‘1’
值得一提的是，这个类型错误的失效条件并不是类型不同就一定索引失效
我们写一个例子，t_user表的uid的类型为int，student表的id的类型为varchar
分析一下下面两个语句是否使用了索引
EXPLAIN select * from student where id = 1;
# 主键索引
EXPLAIN select * from t_user where uid = '1';
另外提一嘴：
varchar的查询条件为数字时，会变成字符串截取数字来查询
int的查询条件为字符串时，会隐式地将数字转换为字符串来查询，所以会走索引
在mysql中的varchar并不是使用的equals的比较，而是字符串截取了
此时我们存在一条数据id='1'的数据，我们使用where id = 1能不能查询出来数据呢？
如果id='123xxx'的数据，使用where id = 123能不能查询出来呢？
同样是可以的
可是id = 'xx123'使用where id = 123就查询不出来了
存在一个uid = 1，使用where uid = '1'时，是能查询出来的，且能走索引（见前几张带explain查询的图）
5、如果MySQL预计使用全表扫描要比使用索引快，则不使用索引
6、like的模糊查询以%开头，索引失效
like “%aaa%” 不会使用索引
like “aaa%” 可以使用索引
7、索引列没有限制 not null
只要列中包含有NULL值都将不会被包含在索引中
复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的
关联查询太多join：从表索引
可能是开发人员编写sql时设计的缺陷
也可能是业务中不得已的需求导致的
以left join左连接为例
在左连接中，左表会将数据全部访问，所以我们应该为右表建立索引
例如select * from a left join b on a.id = b.id时，我们为b表的id建立索引
right join右连接同理给左表条件字段建立索引
多表查询中也是如此
尽可能减少Join语句中的NestedLoop的循环总次数：永远用小结果集驱动大的结果集
优先优化NestedLoop的内层循环
保证Join语句中被驱动表上Join条件字段已经被索引
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置
所以得出结论：从表建立索引
我们这里就粗略带过，需要的同学可以自己去深入了解
慢查询的开启并捕获
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来-定的性能影响。
慢查询日志支持将日志记录写入文件
开启慢查询日志，设置阈值，比如超过10s的sql就是慢SQL，并将其抓取出来
查看慢查询日志
可以查看是否开启和日志文件路径
show VARIABLES like '%slow_query_log%';
查看慢sql阈值时间，默认10s
mysql判断阈值是当时间大于long_query_time时触发，而不是大于等于
show VARIABLES like '%long_query_time%'
开启慢查询配置
# 开启慢查询日志
set global show_query_log = 1;
# 修改文件路径
set global slow_query_log_file = "IF-slow.log"
# 修改时间阈值
set global long_query_time = 10
使用命令开启后，只对当前数据库生效，重启mysql后会失效
修改阈值等，需要重新建立查询会话再查询才能正常显示修改后的值
如果需要永久生效的话，可以在my.ini配置文件中加入
开启慢查询
指定日志文件及其路径
慢sql查询时间阈值
slow-query-log=1
slow_query_log_file="IF-slow.log"
long_query_time=10
explain+慢SQL分析
我们将阈值设置为3，即set global long_query_time = 3
然后执行select sleep(4)让sql睡4秒，让他成为慢sql被记录到日志中去
然后打开log日志查看
# Time: 211118 12:07:44
# User@Host: root[root] @ [127.0.0.1]
# Query_time: 4.003336 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
use test1;
SET timestamp=1637208464;
select SLEEP(4);
可以看见很多数据
产生记录的时间Time
sql执行时间Query_time
锁时间Lock_time
返回的行记录Rows_sent
扫描的行数Rows_examined
使用了test1表use test1;
执行的sql语句select SLEEP(4);
安装perl
要想执行mysqldumpslow.pl(这是perl程序)，下载perl编译器。
下载地址：pan.baidu.com/s/1i3GLKAp
就是ActivePerl_5.16.2.3010812913.msi，一步一步安装后，将bin增加环境变量path
进入mysql的bin目录执行命令
C:\Program Files\MySQL\MySQL Server 5.5\bin>perl mysqldumpslow.pl --help
能输出参数则安装成功
C:\Program Files\MySQL\MySQL Server 5.5\bin>perl mysqldumpslow.pl --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
-s,排序，c,t,l,r以及ac,at,al,ar各自是依照query次数。时间，lock时间，返回记录排序。加a就是倒序。
-t,top n。跟上数字就是算出top多少条
-g。跟正则表达式。
使用命令查看日志
mysqldumpslow.pl -r -s c -a -t >C:\ProgramData\MySQL\MySQL Server 5.5\data\IF-slow.log
因为本文主要讲索引失效问题，所以其他章节本文草草略过，本文到此结束，谢谢观看！
