索引
MySQL官方对索引的定义为:索引 (Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本
质：索引是数据结构。
注意：索引在数据量小的时候，用处不大；但是在数据量很大的时候，区别十分明显。
1、索引的分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值童复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
- 主键索引（primary key）
- 唯一的标识，主键不可重复，只能有一个列做为主键
- 唯一索引（unique key）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为 唯一索引
- 常规索引（key / index）
- 默认的，key、index关键字来设置
- 全文索引（fullText）
- 在特定的数据库引擎中才有
- 快速定位数据
2、索引的使用
-- 索引名命名规范：id_表名_字段名
-- 创建索引
-- create [UNIQUE|FULLTEXT] index 索引名 on 表名(字段1,字段2,...)
create index id_student_name on student(name); -- 增加一个常规索引
-- 查看索引
SHOW INDEX FROM 表名;
-- 删除索引
DROP INDEX 索引名 ON 表名;
-- explain关键字，显示sql执行过程
explain select * from student where name = "张三";
3、索引原则
- 索引不是越多越好
- 不要对经常变动的数据添加索引
- 小数据量无需增加索引
- 索引一般添加在常用于查询的字段
4、SQL性能分析
SQL执行频率
MySQL客户端连接成功后，通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次；
-- show [session | global] status;
show global status like 'com_______';
慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf）中配置如下信息:
# 开启MYSQL慢日志查询开关
slow_query_log = 1;
# 设置慢日志的时间为2秒，sql语句执行时间超过两秒，就会视为慢查询，记录慢查询日志
log_query_time = 2;
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log
profile详情
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了
-- 通过have_profiling参数，查看当前MySQL是否支持profile操作
SELECT @@have_profiling;
-- 查询当前数据库是否开启profile操作 0-未开启 1-开启
SELECT @@profiling;
-- 设置数据库开启profile操作
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;
explain执行计划
EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
-- 只需在语句前面加上关键字"EXPLAIN"
EXPLAIN select * from 表名;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UBezbXXt-1667812733714)(E:\学习笔记\框架\MySQL.assets\image-20221022171649075.png)]
EXPLAIN 执行计划各字段含义:
- ld：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)。
- select_type：表示SELECT的类型，常见的取值有SIMPLE〈简单表，即不使用表连接或者子查询）、PRIMARY(主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询）等
- type：表示连接类型，性能由好到差的连接类型为NLLL、system、const、eq_ref、ref、range、index、all 。
- possible_key：显示可能应用在这张表上的索引，一个或多个
- Key：实际使用的索引，如果为NULL，则没有使用索引。
- Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
- rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。
- filtered：表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。
5、索引失效
最左前缀法则（联合索引中）
如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
如果跳过了最左侧字段，索引失效
如果没有跳过了最左侧字段，跳跃中间某一列，索引将部分失效(后面的字段索引失效)。
范围查询（联合索引中）
联合索引中，出现范围查询(>，<)，范围查询右侧的列索引失效
可以使用 >= , <= 来规避索引失效
索引列运算
在索引列上进行列运算操作，索引将失效
如：select * from user where substring(phone,10,2)= ‘15’;
字符串不加引号
查询条件为字符串类型时，不加引号索引将会失效
如：select * from user where phone = 15788990088;
模糊查询
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
只要是头部加了%号，索引就会失效。
如：
- select * from user where name like ‘馨%’; – 索引生效
- select * from user where name like ‘%馨%’; – 索引失效
- select * from user where name like ‘%馨%’; – 索引失效
or关联
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
索引SQL提示
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
**use index：**explain select * from user use index(idx_user_pro) where profession= ‘软件工程’; （建议使用某个索引）
**ignore index：**explain select * from user ignore index (idx_user_pro) where profession= ‘软件工程’; （忽略使用某个索引）
**force index：**explain select * from user force index(idx_user_pro) where profession= ‘软件工程’; （强制使用某个索引）
覆盖索引
尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select *。
使用explain查看执行计划，最后一列 extra的值：
using index condition：查找使用了索引,但是需要回表查询数据
using where; using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
前缀索引
当字段类型为字符串(varchar , text等),时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘O，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
语法：
create index idx_xxx_xxx on table_name(column(5)); -- 小括号的数字代表给前面几位建立索引
前缀长度：
可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，
唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
-- 求索引选择性公式
select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email, 1,5)) / count(*) from tb_user;
-- 求得截取前五个字符性能最优
-- 创建前缀索引
create index idx_user_email_5 on user(column(5));
单列索引与联合索引
- 单列索引：即一个索引只包含单个列。
- 联合索引：即一个索引包含了多个列。
索引设计原则
-
针对于数据量较大，且查询比较频繁的表建立索引。
-
针对于常作为查询条件（where)、排序(order by)、分组(group by)操作的字段建立索引。
-
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
-
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
-
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
-
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
-
如果索引列不能存储NULL值，请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
