MySQL索引笔记

索引

索引是帮助mysql高效获得数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据
这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
常用的索引结构有：B+树，hash（memory引擎中支持）*；

索引分类：
主键索引、唯一索引、常规索引、全文索引、聚集索引（只能存在一个，必须存在，默认主键为聚集索引）、二级索引。

索引语法

创建索引：
create [unique|fulltext]index index_name on table_name (index_col_name,…);

查看索引：
show index from table_name;

删除索引：
drop index index_name on table_name;

慢查询日志：
慢查询日志是记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10秒）的所有SQL语句的日志
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
1.开启MySQL慢日志查询开关：
slow_query_log=1
2.设置慢日志的时间为2秒，SQL语句执行时间超过两秒，就会视为慢查询，记录慢查询日志：
long_query_time=2

SQL性能分析：
1.profile详情：
show profiles 能够在做SQL优化的时候帮助我们了解时间都耗费到哪里去了。通过having_profiling 参数，
能够看到当前MySQL是否支持profile操作：
语法：select @@have_profiling;
默认profiling是关闭的，可以通过set语句在session/global级别开启profiling;
语法：set profiling =1;

查看每一条SQL的耗时基本情况：
show profiles；

查看指定query_id的SQL语句各个阶段的耗时情况：
show profile for query query_id;

查看指定query_id的SQL语句CPU的使用情况：
show profile cpu for query query_id;

2.explain执行计划：
explain或者desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的程序。
语法：
（直接在select语句之前加上关键字explain/desc）
explain select 字段列表 from 表名 where 条件;

索引使用：
1.最左前缀法则：
如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

2.范围查询：
联合索引中，出现范围查询（>,<），范围右侧的列索引失效；

3.索引列运算：
不要在索引列上进行运算操作，索引将失效；

4.索引使用：
字符串不加引号：
字符串类型字段使用时，不加引号，索引将失效；

5.模糊查询：
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

6.or连接的条件：
用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

7.数据分布影响：
如果MySQL评估使用索引比全表更慢，则不使用索引。

8.SQL提示：
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
use index:使用哪个索引；
ignore index:不使用哪个索引；
force index: 强制使用哪个索引；

9.覆盖索引：
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select*。

10.前缀索引：
当字段类型为字符串（varchar，text）等式，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的
一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率;
语法：
create index idx_xxxx on table_name(column(n));
其中前缀长度可以根据索引的选择性来确定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，
唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
11.单列索引与组合索引：
单列索引：即一个索引只包含一个列；
联合索引：即一个索引包含了多个列；
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引；

12.索引设计原则：
1.针对于数据量较大，且查询比较频繁的表建立索引。
2.针对于常作为查询条件(where）、排序 (order by）、分组(group by）操作的字段建立素引。
3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的宇段，字段的长度较长，可以针对于字段的特点，建立前缀索引1。
5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，維护索引!结构的代价也就越大，会影响增删改的效率。
7.如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包 含NULL值时，它可以更好地确定哪个索引最有效地用于查询。