索引原理
一. 什么是索引
mysql官方定义：是帮助MySql高效获取数据的数据结构，即是索引。
二、索引的原理：
MySQL索引是用一种叫做聚簇索引的数据结构实现的，下面我们就来看一下什么是聚簇索引。
聚簇索引：
聚簇索引是一种数据存储方式，它实际上是在同一个结构中保存了B+树索引和数据行，InnoDB表是按照聚簇索引组织的（类似于Oracle的索引组织表）。
InnoDB通过主键聚簇数据，如果没有定义主键，会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义个主键作为聚簇索引。
重点：
- 对于普通的堆组织表来说，表数据和索引是分别存储的，主键索引和二级索引存储上没有任何区别。
- 对于聚簇索引表来说（左图），表数据是和主键一起存储的，主键索引的叶结点存储行数据，二级索引的叶结点存储行的主键值。
- 二级索引的叶节点存储的是主键值，而不是行指针，这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。
注意：聚簇索引的限制
- 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
- 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
- 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
三、索引的类型：
mysql主要有BTREE、HASH、FULLTEXT、RTREE。
1.BTREE
BTREE也叫B+索引，是一种将索引安照B+树的算法定义的，每次查询都是以root开始，一次遍历node节点，获取子节点上的数据。是MySql里默认最常用的索引类型。
B树和B+树：
B树：每个节点都存储key和data，所有节点组成这棵树，并且叶子节点为空？
B+树：只有叶子节点存储data，叶子节点包含了这颗树的所有键值，叶子节点不存储指针。
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往因索引文件的形式存储在磁盘上，因此，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
局部性原理与磁盘预读：
由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：
当一个数据被用到时，其附近的数据也通常会马上被使用。
程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。
预读的长度一般为页（page）的整数倍，页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘储存区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4K）。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出磁盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。
B树和B+树的性能分析：
选择B+树而不是B树的原因主要时因为B+树的查找效率比B树高得多。
根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入，为了达到这个目的，在实际B-Tree还需要使用如下技巧：
1. 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都时安页对齐的，就实现一个node只需要一次I/O。
2. B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。
因此用B-Tree作为索引结构效率时非常高的。
而红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O(h)，效率要明显比B-Tree差很多。
B+Tree更适合外存索引，原因和内节点出度d有关，从上面分析可以看到，d越大索引性能越好，而出度的上限取决于节点内key和data的大小：
dmax=floor(pagesize/(keysize+datasize+pointsize))
floor表示向下取整。由于B+Tree只有在叶节点才有data数据域，因此可以拥有更大的出度，拥有更好的性能。而且B+树中，叶子节点 增加了一个指向相邻叶子节点的指针，极大地提高了区间访问的性能。
在B树中查找给定关键字的方法是：首先把跟节点取来，在更节点所包含的关键字k1，… kj查找给定的关键字（可以用顺序查找或者二分查找法），若找到等于给定值的关键字，则表示查找成功；否则，一定可以确定要查的关键字在某个Ki或Ki+1之间，于是取Pi所指的下一层索引节点块继续查找，直到找到，或指针Pi为空时查找失败。
B+树中有2个头指针，一个是树的根节点，一个是最小关键码节点。
所以 B+树有两种搜索方法：
- 1.按叶节点自己拉起的链表顺序搜索。
- 2.从根节点开始搜索，和B树类似，不过如果非叶节点的关键码等于给定值，搜索并不停止，而是继续沿右指针，一直查到叶节点上的关键码。所以无论搜索是否成功，都将走完树的所有层。
- 3.B+ 树中，数据对象的插入和删除仅在叶节点上进行。
两种处理索引的数据结构的不同之处： - B树中同一键值不会出现很多次，并且它有可能出现在叶节点，也有可能出现在非页节点中。而B+树的键一定会出现在页节点中，并且有可能在非叶节点中页有可能重复出现，以维持B+树的平衡。
- 因为B树键位值不定，且在整个树结构中只出现一次，虽然可以节省存储空间，但使得在插入、删除操作复杂度明显增加，B+树相比来说 是一种较好的折中。
- B树的查询效率与键在树中的位置有关，最大时间复杂度与B+树相同（在叶子节点的时候），最小时间复杂度为1（根节点的时候）。而B+树的时候复杂度对某建成的树是固定的。
MyISAM不同的索引实现： - MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
- MyISAM的索引文件仅仅保存数据记录的地址。
- MyISAM的索引同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
- MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
InnoDB的索引实现：
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
- 第一个重大区别是InnoDB的数据文件本身就是索引文件。从上面可以知道，MyISAM索引文件和数据文件时分离的，索引文件仅保持数据记录的地址。而在InnoDB中，表数据文件本身就时按照B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
- 第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。
- 聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
注意： - 为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
- 使用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
2、（HASH）哈希索引:
哈希索引，顾名思义，就是根据索引的键值计算出响应的hash值，然后根据hash表中的地址来定位数据。
Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些： - hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。 - Hash 索引无法被用来避免数据的排序操作。
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算。 - Hash 索引不能利用部分索引键查询。
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。 - Hash 索引在任何时候都不能避免表扫描。
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。 - Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下
MyISAM和InnoDB引擎的区别：
- MyISAM是非事务安全的，而InnoDB是事务安全的，
- MyISAM锁的粒度是表级的，而InnoDB支持行级锁，
- MyISAM支持全文类型索引，而InnoDB不支持全文索引，
- MyISAM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyISAM，
- MyISAM表保存成文件形式，跨平台使用更加方便，
- MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量select操作可选择，
- InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，可选择。
3、（FULLTEXT）全文索引
目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
4、RTREE
RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
相对于BTREE，RTREE的优势在于范围查找。
四、索引的种类：
a
1.普通索引：最基础的索引，没有任何限制，近加速查询。
2.普通索引：最基础的索引，没有任何限制，近加速查询。
3.唯一索引：和普通索引类似，但是具有唯一性约束，列值可以有null。
4.主键索引：特殊的唯一索引，不允许有空值，列值不可以有null。
5.组合索引：多列值组成一个索引，专门用于组合搜寻，气消了大于索引合并。索引合并时指 使用多个单列索引组合搜索。
6.全文索引：对文本的内容进行分词，进行搜索。
五、操作：
1.基本操作：
首先创建一个表：create table tt(id int primary key, username varchar(20) ,password varchar(20));
-
索引的命名规则一般是：表名_字段名
-
创建联合索引语法：create index 索引名 on 表名 （字段名1，字段名2）；比如：create index tt_username_password on tt(username,password);
-
其中index还可以换成unique，primary key，分别代表唯一索引和主键索引
-
删除索引：drop index tt_username_password on tt;
2、使用场景： -
对于非常小的表，大部分情况下全表扫描效率更高。
-
中到大型表，索引非常有效
-
特大型的表，建立和使用索引的代价会随之增大，可以使用分区技术解决。
3、创建原则：- 最适合创建索引的列是出现在WHERE或ON子句中的列，或连接子句中的列而不是出现在SELECT关键字后的列。
-
索引列的基数越大，数据区分度越高，索引的效果越好。
-
对于索引字符串进行索引，应该指定一个前缀长度，可以节省大量的索引空间。
-
根据情况创建联合索引，联合索引可以提高查询效率。
-
避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
-
主键尽量可以选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。
4、索引的注意事项： -
联合索引遵循前缀原则
KEY(a,b,c)
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b = 2
WHERE a = 1
#以上SQL语句可以用到索引
WHERE b = 2 AND c = 3
WHERE a = 1 AND c = 3
#以上SQL语句用不到索引
- like查询，%不能在前
WHERE name LIKE "%zhang%"
#以上语句用不到索引，可以用外部的ElasticSearch、Lucene等全文搜索引擎替代。
- 列值为空（null）时时可以使用索引的，但MySql难以优化引用了可空的查询，它会使索引、索引统计和值更加复杂。可空列需要更多储存空间，还需要在mysql内部进行特殊处理。
- 如果MySql估计使用索引比全表扫描更慢，会放弃索引，例如：表中只有100条数据，对于SQL语句where id > 1 and id <90,MySql会优先考虑全表扫描。
- 如果关键字or起那么的条件中的列有索引，后面的没有，所有列的索引都不会被用到。
- 列类型是字符串，查询时一定要给值加引号，否则索引失效，例如：列name varchar（20），存储了字符串“100”，使用where name = 100 ；查询时，索引失效。
六、索引优化
1.如何查找查询速度慢的原因
记录查询日志，分析查询日志，不要直接打开慢查询日志进行分析，这样比较浪费时间和精力，可以使用pt-query-digest工具进行分析。
使用show profile
set profiling=1;开启，服务器上所有执行语句会记录执行时间，存到临时表中
show profiles
show profile for query 临时表ID
使用show status
show status 会返回一些计数器，show global status 会查看所有服务器级别的所有计数，有时根据这些计数，可以推测出哪些操作代价比较高或者消耗时间多。
使用show processlist
观察是否有大量线程处于不正常的状态或特征；
mysql> show processlist;
+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 199684 | Waiting on empty queue | NULL |
| 22 | root | localhost:64021 | NULL | Query | 0 | starting | show processlist |
+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
2 rows in set (0.00 sec)
使用explain
EXPLAIN :模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
mysql> EXPLAIN SELECT * FROM test_person;
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | test_person | NULL | ALL | NULL | NULL | NULL | NULL | 201996 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+------+---------+------+--------+----------+-------+
- ID列
- id相同执行属性由上到下
mysql> explain
-> SELECT*FROM tb_order tb1
-> LEFT JOIN tb_product tb2 ON tb1.tb_product_id = tb2.id
-> LEFT JOIN tb_user tb3 ON tb1.tb_user_id = tb3.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+---------------------------+------+-------+
| 1 | SIMPLE | tb1 | ALL | NULL | NULL | NULL | NULL | 1 | NULL |
| 1 | SIMPLE | tb2 | eq_ref | PRIMARY | PRIMARY | 4 | product.tb1.tb_product_id | 1 | NULL |
| 1 | SIMPLE | tb3 | eq_ref | PRIMARY | PRIMARY | 4 | product.tb1.tb_user_id | 1 | NULL |
+
- 如果是子查询，id序号会自增，id值越大优先级就越高，越先被执行。
mysql> EXPLAIN
-> select * from tb_product tb1 where tb1.id = (select tb_product_id from tb_order tb2 where id = tb2.id =1);
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | tb1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | SUBQUERY | tb2 | ALL | NULL | NULL | NULL | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
- id 相同与不同，同时存在
mysql> EXPLAIN
-> select * from(select * from tb_order tb1 where tb1.id =1) s1,tb_user tb2 where s1.tb_user_id = tb2.id;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL |
| 1 | PRIMARY | tb2 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | DERIVED | tb1 | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
- select_type列：数据读取操作的操作类型
- SIMPLE:简单的select 查询，SQL中不包含子查询或者UNION。
- PRIMARY:查询中包含复杂的子查询部分，最外层查询被标记为PRIMARY。
- SUBQUERY:在select 或者WHERE 列表中包含了子查询。
- DERIVED:在FROM列表中包含的子查询会被标记为DERIVED(衍生表)，MYSQL会递归执行这些子查询，把结果集放到零时表中。
- UNION:如果第二个SELECT 出现在UNION之后，则被标记位UNION；如果UNION包含在FROM子句的子查询中，则外层SELECT 将被标记为DERIVED
- UNION RESULT:从UNION表获取结果的select。
- table列：该行数据时关于哪张表
- type列：访问类型，由好到差system>const>eq_ref>ref>range>index>ALL
- system：表只有一条记录（等于系统表），这是const类型的特列，平时业务中不会出现。
- const：通过索引一次查到数据，该类型主要用于比较primary key 或者unique 索引，因为只匹配一行数据，所以很快;如果将主键置于WHERE语句后面，Mysql就能将该查询转换为一个常量。
- eq_ref:唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。
- ref:非唯一索引扫描，返回匹配某个单独值得所有行，本质上是一种索引访问，它返回所有匹配某个单独值的行，就是说它可能会找到多条符合条件的数据，所以他是查找与扫描的混合体。
- range：只检索给定范围的行，使用一个索引来选着行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现between 、< 、> 、in 等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
- index：FUll Index Scan 扫描遍历索引树(扫描全表的索引，从索引中获取数据)。
- ALL 全表扫描 从磁盘中获取数据 百万级别的数据ALL类型的数据尽量优化。
- possible_keys列:显示可能应用在这张表的索引，一个或者多个。查询设计到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。
- key列：实际使用到索引，如果为null。则没有使用所用，查询中如果使用了覆盖索引，则该索引仅出先在key列表中，覆盖索引：select后的字段与我们建立索引的字段个数一致。
- ken_len列：表示索引中使用的字节数，可通过该列计算查询中使用的索引长度，在不损失精确性的情况下，长度越短越好，key-len显示的值为索引字段的最大可能长度，并非实际使用长度，即key-len时根据表定义计算而得，不是通过表内检索出来的。
- ref列：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值。
- rows列：（每张表有多少行被优化器查询）：根据表统计信息及索引选用的情况，大致估算找到所需记录需要读取的行数。
- Extra列：扩展属性，但是很重要的信息。
- Using filesort(文件排序）：mysql无法按照表内既定的索引顺序进行读取。
mysql> explain select order_number from tb_order order by order_money;
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | tb_order | ALL | NULL | NULL | NULL | NULL | 1 | Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
说明: order_number是表内的一个唯一索引列，但是order by 没有使用该索引列排序，索引mysql使用不得不另起一列进行排序。
- Using temporary：Mysql使用了临时表保存中间结果。常见于排序order by和分组查询group by。
mysql> explain select order_number from tb_order group by order_money;
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
| 1 | SIMPLE | tb_order | ALL | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)
- Using index 表示相应的select操作使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现using where 表明索引用来读取数据而非执行查找动作。
mysql> explain select order_number from tb_order group by order_number;
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
| 1 | SIMPLE | tb_order | index | index_order_number | index_order_number | 99 | NULL | 1 | Using index |
+----+-------------+----------+-------+--------------------+--------------------+---------+------+------+-------------+
1 row in set (0.00 sec)
- Using where ：查找
- Using join buffer：表示当前sql使用了连接缓存
- impossible where：where字句总是false，mysql无法获取数据行。
- select tables optimized away：
- distinct：
七：SQL语句优化的方法： - 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by 设计的列上建立索引。
- 2.应尽量避免在where自居中对字段进行null判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null；
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=
- 3.应尽量避免在where子句中使用！=或< >操作符，否则引擎将放弃使用索引而进行全表扫描。
- 4.应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20；
可以这样查询：
select id from t where num=10 union all select id from t where num=20
- 5.in和not in也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
- 6.下面的查询也将导致全表扫描：
select id from t where name like ‘%李%";
若要提高效率可考虑全文检索。
- 7.如果在where子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析全局变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num
- 8.应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num/2 = 100;
应改为：
select id from where num = 100*2;
- 9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引进行全表扫描，如：
select id from t where substring(name,1,3)='adc';
name以abc开头的id应改为：
select id from t where name like 'abc%';
- 10.不要在where子句中的”=“左边进行函数，算术运算或其他表达式运算，否则系统可能无法正确使用索引。
八：慢查询日志
Mysql的慢查询日志是Mysql提供的一种日志记录，它用来记录在MySql中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，是指运行10s以上的语句，默认情况下，MySql数据库并不启动慢查询日志，需要手动设置这个参数，如果不是调优需要的话，一般不建议启动该参数，以为启动慢查询日志会或多或少带来一些性能的影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
1、查看慢日志参数：
mysql> show variables like '%query%';
+------------------------------+----------------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+----------------------------------------------------------------------+
| binlog_rows_query_log_events | OFF |
| ft_query_expansion_limit | 20 |
| have_query_cache | NO |
| long_query_time | 10.000000 |
| query_alloc_block_size | 8192 |
| query_prealloc_size | 8192 |
| slow_query_log | OFF |
| slow_query_log_file | F:\software\mysql8\mysql-8.0.15-winx64\data\CHINA-20190410L-slow.log |
+------------------------------+----------------------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)
2、修改当前配置：
set global slow_query_log='ON';
将 slow_query_log 全局变量设置为“ON”状态 ，临时生效，mysql重启后就会失效
也可以直接打开慢日志配置文件进行修改，但必须重启服务才能生效。
3、 查看MySQL慢日志：
1[root@localhost~]# mysqldumpslow --help
2 Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
3
4 Parse and summarize the MySQL slow query log. Options are
5
6 --verbose verbose
7 --debug debug
8 --help write this text to standard output
9
10 -v verbose
11 -d debug
12 -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
13 al: average lock time
14 ar: average rows sent
15 at: average query time
16 c: count
17 l: lock time
18 r: rows sent
19 t: query time
20 -r reverse the sort order (largest last instead of first)
21 -t NUM just show the top n queries
22 -a don't abstract all numbers to N and strings to 'S'
23 -n NUM abstract numbers with at least n digits within names
24 -g PATTERN grep: only consider stmts that include this string
25 -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
26 default is '*', i.e. match all
27 -i NAME name of server instance (if using mysql.server startup script)
28 -l don't subtract lock time from total time
-s, 是表示按照何种方式排序
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；
比如:
得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
349
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
