2 索引
2.1 安装linux下的mysql
- 去官网下载mysql
- 解压并安装
- 压缩文件通过xftp解压并且使用rpm安装依赖
[root@hadoop soft]#tar -xvf mysql-8.0.26-1.el7.x86_64.rpm-bundle.tar -C /home/hadoop/app/
[root@hadoop soft]cd ..
[root@hadoop hadoop]#cd app
[root@hadoop app]# rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm
[root@hadoop app]# yum remove mysql-libs
[root@hadoop app]# rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm
[root@hadoop app]# rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm
[root@hadoop app]# rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpm
[root@hadoop app]# yum install openssl-devel
[root@hadoop app]# rpm -ivh mysql-community-devel-8.0.26-1.el7.x86_64.rpm
- 安装客户端
[root@hadoop app]#rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm
- 安装服务端
[root@hadoop app]#rpm -ivh mysql-community-server-8.0.26-1.el7.x86_64.rpm
- 启动mysql服务
[root@hadoop app]# systemctl start mysqld
- 去日志找下随机生成的mysql的root密码
[root@hadoop app]# vim /var/log/mysqld.log
/password
- 登录mysql
[root@hadoop app]# mysql -u root -p
Enter password:
嫌麻烦练习的时候直接跳过密码算了：(88条消息) Linux系统安装MySQL报错“ Access denied for user ‘root‘@‘localhost‘ (using password: YES)“_二木成林的博客-CSDN博客
- 把密码等级调低并且更改密码
mysql>set global validate_password_policy = LOW;
mysql>set global validate_password.length = 8;
mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxxxxx';
- 创建供远程访问的一个用户
mysql>drop user 'root'@'%';
mysql>flush privileges;
mysql>create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'xxxxxxxx';
表示在所有的主机上都能访问
- 给创建可以远程访问的root用户分配权限
mysql>grant all on *.* to 'root'@'%';
- 用Datagrip连接看看
- 如果连不上看下linux的防火墙有没有开放3306的端口，或者直接关闭防火墙看看
2.2 索引概述
- 含义：是帮助MySQL高效获取数据的数据结构(有序)。在数据结构上实现高级查找算法引用(指向)数据。
- 比较
- 无索引，全表扫描，直至结束。性能极低。
- 有索引
- 假如是二叉查找树:如果select * from user from age = 45，那么45会跟36比较，走右边，然后再跟48比较，走左边，然后就找到45了，只需要匹配3次。效率高。
春华秋实之MySQL进阶-02 索引2 索引
- 假如是二叉查找树:如果select * from user from age = 45，那么45会跟36比较，走右边，然后再跟48比较，走左边，然后就找到45了，只需要匹配3次。效率高。
- 优缺点
- 优点：
- 高效的获取数据，降低数据库获取数据的磁盘IO成本。
- 通过索引对数据进行排序，降低数据排序成本，降低CPU的消耗。
- 缺点：
- 索引也是要占空间滴
- 提高查询效率，但是降低表的更新效率，因为也要维护索引
2.3 索引结构
- 含义：索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构。
- 分类
- 二叉树
- 左边节点小于自身，右侧节点大于自身
- 顺序插入，形成链表，性能降低。针对大量数据，层次深，检索慢。
- 解决办法：左旋右旋形成红黑树(平衡二叉查找树)
- 但是还是因为只有两个节点导致的在大量数据会形成较深的层次的，因此检索慢的问题
- B-Tree(多路平衡查找树)
- 概念:以一颗最大度数(max-degree)为5(5阶)的b-tree为例，度数就是节点的子节点个数，那就表示5阶的b树每个节点最多存储4个key，5个指针
- 看图理解下：
- 5阶表示下面有有5个节点，图中根节点就是5个指针灰色的那边，第一个灰色的指针表示小于20这个key，第二个灰色的指针表示在20这个key和30这个key之间，依次共5个，然后每个指针对应下面每个节点。因此得出结论，如果有n个key就有n+1个指针。
- 来个案例
春华秋实之MySQL进阶-02 索引2 索引
- B+tree
- 概念：以一颗最大度数(max-degree)为4(4阶)的B+Tree为例
- 看图理解
- 与BTree的区别是，所有的数据存放都会出现在叶子的节点，并且形成了单项链表，上面的非叶子节点起到的作用是索引的作用
- 也来个例子
春华秋实之MySQL进阶-02 索引2 索引 春华秋实之MySQL进阶-02 索引2 索引
- MySQL的b+tree索引
- 概念：在原来的B+Tree上进行了优化，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序的B+Tree,提高区间访问的性能。简单说就是把单项链表变成了双向链表
- 看图理解：
- 每个节点存储在页当中，回忆下前面的InnoDB的逻辑存储结构，表空间、段、区、页、行，一页默认16K，底层就是这么存储的。
春华秋实之MySQL进阶-02 索引2 索引
- 每个节点存储在页当中，回忆下前面的InnoDB的逻辑存储结构，表空间、段、区、页、行，一页默认16K，底层就是这么存储的。
- Hash索引
- 概念：哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，存储在hash表中。
- 看图理解
- 背景：id是主键，要为name创造一个哈希索引的数据结构
- 第一步：先计算出每一行数据的哈希值
- 第二步：name字段的所有值根据哈希函数去计算每一个name值应该放在哈希表的哪个槽位上。例如金庸算出来是槽位值005，对应的槽位就会存储金庸这个key，以及金庸这一行对应哈希值58dda，也就是第一步对应的值，怎么理解呢，就理解为58dda是一个引用，指向了金庸所处的那一行的地址
春华秋实之MySQL进阶-02 索引2 索引 - 杨逍和金庸算出来的槽位一样，就叫做哈希冲突，跟java中一样用链表解决就可以
- 特点：
- 只能用于等值
- 无法排序，因为运算结果无序
- 但是查询效率高，通常一次检索匹配就可以了，效率通常高于B+Tree索引
- 支持哈希索引的引擎
- Memory引擎，但是InnoDB中具有的自适应hash功能，就是说有时候可以将B+Tree索引在指定条件下自动构建成哈希索引
- 问：为啥InnoDB存储引擎选择B+Tree索引结构？
- 相对于二叉树，层级更少，搜索效率高
- B-tree，所有节点都会存储数据，导致存key和指针的存储的少
- 相对于Hash索引，支持范围匹配和排序操作
2.4 索引分类
- 分类
- 细节：PK不能为空，unique可以为空值，PK和unique在约束时候自动创建了索引
- 根据存储形式的分类
春华秋实之MySQL进阶-02 索引2 索引
- 规则
- 如果存在主键，主键索引就是聚集索引
- 没有主键，将使用第一个唯一索引作为聚集索引
- 啥都没有咋办，会生成一个rowid所谓隐藏的聚集索引
- 来张图理解下
- 聚集索引叶子挂着数据，二级索引下面挂着聚集索引字段
- 查name = 'Arm'的时候，走的是二级索引，字符是按照字典序序走的索引，找到id后，再到上面的聚集索引走一遍，定位10，再把整个数据返回出来，专业数据叫做回表查询
- 来道题
select * from user where id = 10;
select * from user where name = 'Arm';
备注：id为主键，name字段创建的时候有索引
问：哪个sql语句执行效率较高？
答案：
- 第一句只要去聚集索引中走一遍，然后获取的数据就好啦
- 第二句去二级索引中走一遍，然后再拿着id去聚集索引中再去获取*的数据，即回表查询，扫描了两个字段的索引，效率就不高
- 再来道题
问题：InnoDB主键索引的B+Tree高度为多高？
前提：假设一行数据为1k，一个指针占用6个字节，主键为bigint，占用8个字节。
解答：(n表示key的数量)
- n*8+(n+1)6=161024 非叶的节点 n算出来1170个key，指针就有1171个
- 1171*16 = 18736 指针数乘最大的叶子下面最大的行数，高度为2
- 18736 *1171 = 21939856 高度为3 如下图
2.5 索引的操作语法
- 创建索引
create [unique|fulltext] index index_name on table_name(index_col_name,...)
- unique表示唯一索引，fulltext表示全文索引
- 一个索引可以关联多个字段的，一个字段叫单列索引，多个字段叫做联合索引
- 查看索引
show index from table_name;
- 删除索引
drop index index_name on table_name;
- 练习
- name字段为姓名字段，该字段的值可能会重复，为该字段创建索引
- 看下现在有什么索引先
-
mysql> show index from tb_user\G; *************************** 1. row *************************** Table: tb_user Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 24 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 1 row in set (0.00 sec) - 创建索引
-
mysql> create index idx_user_name on tb_user(name); Query OK, 0 rows affected (0.03 sec)
- phone手机号字段的值，是非空的且唯一的，给他创建唯一索引
mysql> create unique index idx_user_phone on tb_user(phone);
Query OK, 0 rows affected (0.01 sec)
- 为profession、age、status创建联合索引
- 联合索引字段的讲究：使用最频繁的字段放在左侧。根据使用频繁程度从重到轻，要遵循''最左前缀原则''
mysql> create index idx_user_pro_age_status on tb_user(profession,age,status);
Query OK, 0 rows affected (0.01 sec)
- 为email字段建立合适的索引来提升查询效率
mysql> create index idx_user_email on tb_user(email);
Query OK, 0 rows affected (0.01 sec)
- 删除索引
mysql> drop index idx_user_email on tb_user;
Query OK, 0 rows affected (0.01 sec)
2. 6 SQL性能分析
- SQL的执行频率
- 看下是以增删改查什么为主进行操作
- 查看服务器状态状态
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 0 |
| Com_delete | 0 |
| Com_import | 0 |
| Com_insert | 24 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 644 |
| Com_signal | 0 |
| Com_update | 0 |
| Com_xa_end | 0 |
+---------------+-------+
11 rows in set (0.00 sec)
- 慢查询日志
- 含义：记录了所有执行时间超过指定参数的所有SQL语句的日志。
- 查看开启情况
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.01 sec)
- MySQL的慢查询日志默认没有开启，需要在My'SQL的配置文件(/etc/my.cnf)中配置如下信息
[root@hadoop ~]# vi /etc/my.cnf
#开启慢查询日志
slow_query_log=1
#超过两秒即为慢查询
long_query_time=0.1秒
- 重启下服务器
[root@hadoop ~]# systemctl restart mysqld
- 再次查询慢日志情况发现就开了
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
- 去查询一百万条数据
mysql> select count(*) from tb_sku;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (13.35 sec)
- 去查看对应的慢查询日志
[root@hadoop ~]# cd /var/lib/mysql
[root@hadoop mysql]# tail -f hadoop-slow.log
3. profile详情
- 含义：可以帮助我们了解时间都耗费到哪儿去了
- 查看profile操作,yes表示支持
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
- profile默认关闭，可以通过set开启
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
- 通过profile查看下每一条SQL的耗时情况
mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------+
| 1 | 0.00025125 | select @@profiling |
| 2 | 0.00080300 | select * from tb_user |
| 3 | 0.00080375 | select * from tb_user where id = 1 |
| 4 | 0.00077725 | select * from tb_user where name = '白起' |
| 5 | 0.00065025 | select count(*) from tb_sku |
| 6 | 0.00056925 | select count(*) from tb_user |
+----------+------------+---------------------------------------------+
- 查看指定query_id 的SQL语句各个阶段的耗时情况
mysql> show profile for query 6;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000127 |开启时间
| Executing hook on transaction | 0.000009 |执行事务时间
| starting | 0.000017 |
| checking permissions | 0.000013 |检查权限
| Opening tables | 0.000055 |打开表
| init | 0.000011 |进行初始化的操作
| System lock | 0.000015 |
| optimizing | 0.000030 |优化操作
| statistics | 0.000030 |统计操作
| preparing | 0.000034 |
| executing | 0.000137 |执行
| end | 0.000010 |
| query end | 0.000007 |
| waiting for handler commit | 0.000015 |执行提交的操作
| closing tables | 0.000014 |
| freeing items | 0.000023 |
| cleaning up | 0.000024 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.01 sec)
- 查看执行SQL语句CPU 的耗费情况
mysql> show profile cpu for query 6;
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting | 0.000127 | 0.000084 | 0.000032 |
| Executing hook on transaction | 0.000009 | 0.000006 | 0.000002 |
| starting | 0.000017 | 0.000012 | 0.000005 |
| checking permissions | 0.000013 | 0.000009 | 0.000004 |
| Opening tables | 0.000055 | 0.000040 | 0.000015 |
| init | 0.000011 | 0.000008 | 0.000003 |
| System lock | 0.000015 | 0.000011 | 0.000004 |
| optimizing | 0.000030 | 0.000021 | 0.000008 |
| statistics | 0.000030 | 0.000022 | 0.000009 |
| preparing | 0.000034 | 0.000024 | 0.000009 |
| executing | 0.000137 | 0.000100 | 0.000038 |
| end | 0.000010 | 0.000006 | 0.000003 |
| query end | 0.000007 | 0.000005 | 0.000002 |
| waiting for handler commit | 0.000015 | 0.000011 | 0.000004 |
| closing tables | 0.000014 | 0.000010 | 0.000004 |
| freeing items | 0.000023 | 0.000017 | 0.000006 |
| cleaning up | 0.000024 | 0.000017 | 0.000007 |
+--------------------------------+-----------------------------------
- explain执行计划
- 含义：获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程表中如何连接和连接的顺序
- 语法:在任何select 语句中加上关键字explain/desc
explain select 字段列表 from 表名 where 条件；
mysql> explain select * from tb_user where id = 1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+------
| 1 | SIMPLE | tb_user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+------
type连接的类型，possi- ble_keys可能用到的索引，key实际用到的索引，key_len索引的长度，rows扫描记录数
- 参数说明
- id相同，执行顺序从上而下，id不同，值越大，越先执行 (如图3先执行，依次是c，sc，子查询，s)
-
春华秋实之MySQL进阶-02 索引2 索引 - select_type,表示select的类型，常见取值simple，primary，union，subquery(子查询)
- type，表示连接类型，性能由好到差的连接类型为null(一般是不引用表，例如select 'A'),sysytem,const(根据主键和唯一索引一般会出现),eq_ref,ref(使用非唯一索引会出现),range,index,all(全表扫描),实操中尽量往前优化
- possibel key,显示可能应用在这张表上的索引，一个或者多个
- key，实际用到的索引
- key_len,表示索引中使用的字节数
- rows，认为必须要执行的行数
- filtered,表示返回结果的行数占需读取行数的百分比，filtered的值越大越好
- extra，额外信息
2.7 使用规则
- 验证索引效率
- 没用索引
- 用到索引
- 最左前缀使用法则
- 含义：主要针对联合索引的，指的是从索引的最左列开始，并且不跳过索引中的列。如果跳过了，索引将部分失效。
- 案例
- 看下表中索引，发现profession，age，status形成了联合索引
-
mysql> show index from tb_user; +---------+------------+-------------------------+--------------+-------------+-----------+---------- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +---------+------------+-------------------------+--------------+-------------+-----------+---------- | tb_user | 0 | PRIMARY | 1 | id | A | 24 | NULL | NULL | | BTREE | | | YES | NULL | | tb_user | 0 | idx_user_phone | 1 | phone | A | 24 | NULL | NULL | | BTREE | | | YES | NULL | | tb_user | 1 | idx_user_name | 1 | name | A | 24 | NULL | NULL | | BTREE | | | YES | NULL | | tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 16 | NULL | NULL | YES | BTREE | | | YES | NULL | | tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 22 | NULL | NULL | YES | BTREE | | | YES | NULL | | tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 24 | NULL | NULL | YES | BTREE | | | YES | NULL | +---------+------------+-------------------------+--------------+-------------+-----------+---------- - 查询条件写联合索引涉及的3个字段(字段顺序可以变)，并explain看下索引执行情况
-
mysql> explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0'; +----+-------------+---------+------------+------+-------------------------+------------------------- | id | select_type | table | partitions | type | possible_keys | key +----+-------------+---------+------------+------+-------------------------+------------------------- | 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status +----+-------------+---------+------------+------+-------------------------+------------------------- 1 row in set, 1 warning (0.00 sec) - 如果两个字段呢，也是走的联合索引，前提是最左前缀，并且没有跳过字段
-
mysql> explain select * from tb_user where profession = '软件工程' and age = 31； +----+-------------+---------+------------+------+-------------------------+------------------------- | id | select_type | table | partitions | type | possible_keys | key +----+-------------+---------+------------+------+-------------------------+------------------------- | 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status +----+-------------+---------+------------+------+-------------------------+------------------------- 1 row in set, 1 warning (0.00 sec) - 跳过最左列profession来查，就不走索引了,索引全部失效，ref=null走的全表扫描
-
mysql> explain select * from tb_user where age = 31 and status = '0'; +----+-------------+---------+------------+------+---------------+------+---------+------+------+---- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | +----+-------------+---------+------------+------+---------------+------+---------+------+------+---- | 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | +----+-------------+---------+------------+------+---------------+------+---------+------+------+---- 1 row in set, 1 warning (0.00 sec) - 如果缺失中间字段age的索引呢，也走的联合索引，因为最左列在，但是只走了profession的索引，status没走，出现了索引的部分失效
-
mysql> explain select * from tb_user where profession = '软件工程' and status = '0'; +----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status | 47 | const | 4 | 10.00 | Using index condition | +----+-------------+---------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
- 范围查询
- 含义：联合索引中，出现范围查询(>,<),范围查询右侧的列索引失效
- 案例:例如status就失效了，因为在范围查询的右侧
mysql> explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_status | idx_user_pro_age_status | 49
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
1 row in set, 1 warning (0.01 sec)
- 规避方法:业务允许的范围下，尽量使用>=，就可以走联合索引
mysql> explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| id | select_type | table | partitions | type | possible_keys | key |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_status | idx_user_pro_age_status | 54 | NULL | 2 | 10.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
1 row in set, 1 warning (0.00 sec)
- 索引失效
- 索引列在where中进行了运算，索引失效
mysql> explain select * from tb_user where substring(phone,10,2) = '15';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 100.00 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
1 row in set, 1 warning (0.00 sec)
- 字符串不加引号，索引失效
mysql> explain select * from tb_user where phone = 17799990015;
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------
| 1 | SIMPLE | tb_user | NULL | ALL | idx_user_phone | NULL | NULL | NULL | 24 | 10.00
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------
1 row in set, 3 warnings (0.00 sec)
0没有引号，索引长度为49，status失效了
mysql> explain select * from tb_user where profession = '软件工程' and age >= 30 and status = 0;
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_status | idx_user_pro_age_status | 49 | NULL | 2 | 10.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
- 仅仅是尾部模糊匹配，索引不会失效，头部的模糊匹配会失效
这个走
mysql> explain select * from tb_user where profession like '软件%';
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
| 1 | SIMPLE | tb_user | NULL | range | idx_user_pro_age_status | idx_user_pro_age_status | 47 | NULL | 4 | 100.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------------+-------------------------+-----
1 row in set, 1 warning (0.00 sec)
这个不走
mysql> explain select * from tb_user where profession like '%工程';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 24 | 11.11 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
1 row in set, 1 warning (0.00 sec)
- or 前后字段都有索引才会走
age没有索引
mysql> explain select * from tb_user where id = 10 or age = 23;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
| 1 | SIMPLE | tb_user | NULL | ALL | PRIMARY | NULL | NULL | NULL | 24 | 13.75 |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------
1 row in set, 1 warning (0.00 sec)
- 数据分布影响
- 如果MySQL检测使用索引比全表更慢，则不适用索引,包含null
mysql> explain select * from tb_user where phone >= '17799990011';
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------
| 1 | SIMPLE | tb_user | NULL | ALL | idx_user_phone | NULL | NULL | NULL | 24 | 54.17 | Using where |
+----+-------------+---------+------------+------+----------------+------+---------+------+------+----------
- SQL提示
- 含义：就是在SQL语句中g加入一些人为提示来达到优化操作的目的
- use index :选择给profession新增加的索引进行检索
mysql> explain select * from tb_user use index(idx_user_profession) where profession = '软件工程';
+----+-------------+---------+------------+------+----------------+------+---------+------+------+--
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref
+----+-------------+---------+------------+------+---------------------+---------------------+-------
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_profession | idx_user_profession | 47
+----+-------------+---------+------------+------+---------------------+---------------------+-------
1 row in set, 1 warning (0.00 sec)
- ignore index:忽略这个索引
mysql> explain select * from tb_user ignore index(idx_user_profession) where profession = '软件工程';
+----+-------------+---------+------------+------+-------------------------+-------------------------
| id | select_type | table | partitions | type | possible_keys | key
+----+-------------+---------+------------+------+-------------------------+-------------------------
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_pro_age_status | idx_user_pro_age_status
+----+-------------+---------+------------+------+-------------------------+-------------------------
- force index :强制
mysql> explain select * from tb_user force index(idx_user_profession) where profession = '软件工程';
+----+-------------+---------+------------+------+---------------------+---------------------+---------+----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref
+----+-------------+---------+------------+------+---------------------+---------------------+---------+----
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_profession | idx_user_profession | 47 |
+----+-------------+---------+------------+------+---------------------+---------------------+---------+----
- 覆盖索引
- 含义：尽量使用覆盖索引，减少select *
- extra两者差异
- using index condition :查找使用了索引，但是需要回表查询数据
- using where；using index：查找使用了索引，不需要回表查询
- 具体原因：第一条数据联合索引数据属于二级索引叶子节点挂的就是id，所以需要的id就不要，第二条数据有一个name字段，需要根据id再去到聚集索引中回表查询出来
- 思考：一张表，有四个字段(id,username,password,status),由于数据量大，需要对SQL语句：select id,username,password from tb_user where usename = 'itcast';进行优化，该如何进行才是最优方案
- 答案：对username和password建立联合索引，这样查询这条SQL的时候就不需要进行回表查询了
- 前缀索引
- 含义：当字段类型为字符串(varchar,test等)时，使用这样的字段当作索引会让索引变得很大，浪费磁盘io。此时只将字符串的一部分前缀建立索引
- 语法
create index idx_xxxx on table_name(column(n));
- 前缀长度
- 根据索引的选择性来决定，选择性是指不重复的索引值(基数)和数据表的记录总值的比值。
mysql> select count(distinct email)/count(*) from tb_user;
+--------------------------------+
| count(distinct email)/count(*) |
+--------------------------------+
| 1.0000 |
+--------------------------------+
截取邮箱前9个就发现有重复的了
mysql> select count(distinct substring(email,1,9))/count(*) from tb_user;
+-----------------------------------------------+
| count(distinct substring(email,1,9))/count(*) |
+-----------------------------------------------+
| 0.9583 |
+-----------------------------------------------+
- 案例
mysql> create index idx_email_5 on tb_user(email(5));
- 数据结构分析
前面5位去辅助索引查询到对用id再去聚集索引那边找数据，由于采用的是前5位，所以会去看下email是不是全部等于where后面的条件，下一步再去看辅助索引的链表下一个节点中email是不是where的条件，如果是就返回，如果不是就不要
- 单列索引和联合索引选取的规则
- 案例
mysql> explain select id,phone,name from tb_user where phone = '17799990010' and name = '韩信';
phone和name都有单列索引，但是只走了phone的索引，那也会产生回表查询的，因为phone的索引是没有包含name的字段的值
创建一个phone,name的联合索引
mysql> create unique index idx_user_phone_name on tb_user(phone,name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
再次查询走的也不是走联合的
mysql> explain select id,phone,name from tb_user where phone = '17799990010' and name = '韩信';
所以这边指示一下SQL提示，指定联合索引检索，最后的extra中using index不需要回表查询
mysql> explain select id,phone,name from tb_user use index(idx_user_phone_name) where phone = '17799990010' and name = '韩信';
- 结论
- 如在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引。
- 设计原则