内部门户网站建设方案,巴南集团网站建设,如何保护我做的网站模板,18年公众号价格MySQL进阶1 - 索引 1. 索引概述2. 索引结构2.1 二叉树2.2 B-Tree(多路平衡查找树)2.3 BTree2.4 Hash 3. 索引分类4. 索引语法5. SQL性能分析5.1 SQL执行频率5.2 慢查询日志5.3 profile5.4 explain执行计划5.3.1 EXPLAIN执行计划各字段含义#xff1a; 6. 索引使… MySQL进阶1 - 索引 1. 索引概述2. 索引结构2.1 二叉树2.2 B-Tree(多路平衡查找树)2.3 BTree2.4 Hash 3. 索引分类4. 索引语法5. SQL性能分析5.1 SQL执行频率5.2 慢查询日志5.3 profile5.4 explain执行计划5.3.1 EXPLAIN执行计划各字段含义 6. 索引使用6.1 最左前缀法则6.2 范围查询6.3 索引列运算6.4 字符串不加引号6.5 模糊匹配6.6 or连接的条件6.7 数据分布影响6.8 SQL提示6.9 覆盖索引6.10 前缀索引6.11 单列索引与联合索引 7. 索引设计原则 1. 索引概述
介绍
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用(指向)数据这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。
优缺点
优势劣势提高数据检索的效率降低数据库的IO成本索引列也是要占用空间的通过索引列对数据进行排序降低数据排序的成本降低CPU的消耗索引大大提高了查询效率同时也降低更新表的速度如对表进行INSERT、UPDATE、DELETE时效率降低
2. 索引结构
MySQL的索引是在存储引擎层实现的不同的存储引擎有不同的结构主要包含以下几种
索引结构描述B Tree结构最常见的索引类型大部分引擎都支持BTree索引Hash索引底层数据结构是用哈希表实现的只有精确匹配索引列的查询才有效不支持范围查询Rtree(空间索引)空间索引是MyISAM引擎的一个特殊索引类型主要用于地理空间数据类型通常使用较少Full - text(全文索引)是一种通过建立倒排索引快速匹配文档的方式类似于LuceneSolrES
索引InnoDBMyISAMMemoryB Tree索引支持支持支持Hash索引不支持不支持支持R - tree索引不支持支持不支持Full - text5.6版本之后支持支持不支持
我们平常说的索引如果没有特别指明都是指Btree结构组织的索引
2.1 二叉树 二叉树缺点顺序插入时会形成一个链表查询性能大大降低。数据量大的情况下层级较深检索速度慢
红黑树数据量大的情况下层级较深检索速度慢
2.2 B-Tree(多路平衡查找树)
以一棵最大度数(max-degree)为5(5阶)的B-Tree为例(每个节点最多存储4个key5个指针) Ps树的度数指的是一个节点的子节点的个数
上图中有4个key分别是20、30、62、89其对应5个指针小于20的对应第一个指针20-30之间对应第二个指针30-62之间对应第三个指针62-89之间对应第四个指针大于89的对应第五个指针
具体可参考B-Tree Visualization在该网站中选择Max. Degree 5插入以下数据 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 效果如下 ● 5阶的B树每一个节点最多存储4个key对应5个指针 ● 一旦节点存储的key数量达到5就会裂变中间元素向上分裂 ● 在B树种非叶子节点和叶子节点都会存放数据
2.3 BTree
以一棵最大度数(max-degree)为4(4阶)的BTree为例 在B Tree Visualization中选择5阶同样插入以上数据效果如下 相对于B-Tree区别
① 所有的数据都会出现在叶子节点 ② 叶子节点形成一个单向链表
MySQL索引数据结构对经典的BTree进行了优化。在原BTree的基础上增加一个指向相邻叶子节点的链表指针就形成了带有顺序指针的BTree提高区间访问的性能 2.4 Hash
哈希索引就是采用一定的Hash算法将键值换算成新的Hash值映射到对应的槽位上然后存储在Hash表中。如果两个(或多个)键值映射到一个相同的槽位上他们就产生了Hash冲突(也称为Hash碰撞)可以通过链表来解决问题
• Hash索引特点
Hash索引只能用于对等比较(, in)不支持范围查询(between, , , …)无法利用索引完成排序操作查询效率高通常只需要一次检索就行了效率通常要高于BTree索引
• 存储引擎支持
在MySQL中支持Hash索引的是Memory引擎而InnoDB中具有自适应Hash功能Hash索引是存储引擎根据BTree索引在指定条件下自动构建的
3. 索引分类
分类含义特点关键字主键索引针对于表中主键创建的索引默认自动创建只能有一个PRIMARY唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE常规索引快速定位特定数据可以有多个全文索引全文索引查找得是文本中得关键词而不是比较索引中的值可以有多个FULLTEXT
在InnoDB存储引擎中根据索引的存储形式又可以分为以下两种
分类含义特点聚集索引(Clustered Index)将数据存储与索引放到了一块索引结构的叶子节点保存了行数据必须有且只有一个二级索引(Secondary Index)将数据与索引分开存储索引结构的叶子节点关联的是对应的主键可以存在多个
聚集索引选取规则
• 如果存在主键主键索引就是聚集索引 • 如果不存在主键将使用第一个唯一(UNIQUE)索引作为聚集索引 • 如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引
4. 索引语法
• 创建索引 CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name, …); • 查看索引 SHOW INDEX FROM table_name; • 删除索引 DROP INDEX index_name ON table_name; • 案例演示
-- 创建表tb_user
create table tb_user(id int auto_increment primary key comment 主键ID,name varchar(10) comment 姓名,phone char(11) comment 手机号,email varchar(30) comment 邮箱,profession varchar(20) comment 专业,age int check ( age 0 and age 100 ) comment 年龄,gender int check ( gender 1 or gender 2 ) comment 性别,status char(1) comment 状态,createtime datetime comment 创建时间
) comment 信息表;-- 添加数据
insert into tb_user(name, phone, email, profession, age, gender, status, createtime)
values(安景辉, 13080354567, anjinghuiexample.com, 软件工程, 23, 1, 6, 2001-02-02 00:00:00),(白佳颖, 13097742368, baijiayingexample.com, 通讯工程, 26, 2, 0, 2001-03-05 00:00:00),(柏凡, 13123456789, bofanexample.com, 英语, 25, 1, 2, 2002-03-02 00:00:00),(陈梦洁, 13245678901, chenmengjieexample.com, 工程造价, 27, 2, 0, 2001-07-02 00:00:00),(曹宇涛, 13356789012, caoyutaoexample.com, 软件工程, 22, 1, 1, 2001-04-22 00:00:00),(董碧云, 13467890123, dongbiyunexample.com, 舞蹈, 22, 2, 0, 2001-02-07 00:00:00),(单景瑞, 13578901234, shanjingruiexample.com, 应用数学, 27, 1, 0, 2001-02-08 00:00:00),(冯彩云, 13689012345, fengcaiyunexample.com, 化工, 28, 2, 5, 2001-05-23 00:00:00),(范石磊, 13790123456, fanshileiexample.com, 金属材料, 30, 1, 0, 2001-09-18-00:00:00),(关海燕, 13801234567, guanhaiyanexample.com, 机械工程及其自动化, 27, 2, 2, 2001-08-16 00:00:00),(高少杰, 13912345678, gaoshaojieexample.com, 无机非金属材料工程, 26, 1, 0, 2001-06-12 00:00:00),(韩楚仪, 13112345678, hanchuyiexample.com, 会计, 23, 2, 0, 2001-05-11 00:00:00),(郝飞羽, 13212345678, haofeiyuexample.com, 工程造价, 32, 1, 1, 2001-04-09 00:00:00),(贺冰倩, 13312345678, hebingqianexample.com, 应用数学, 25, 2, 2, 2001-04-10 00:00:00),(黄浩然, 13412345678, huanghaoranexample.com, 软件工程, 24, 1, 3, 2001-02-12 00:00:00),(黄欣茹, 13512345678, huangxinruexample.com, 软件工程, 24, 2, 0, 2001-01-30 00:00:00),(江逸舟, 13612345678, jiangyizhouexample.com, 工业经济, 35, 1, 0, 2000-05-03 00:00:00),(李佳妮, 13712345678, lijianiexample.com, 化工, 38, 2, 1, 2001-08-08 00:00:00),(康正乐, 13812345678, kangzhengleexample.com, 国际贸易, 30, 1, 0, 2007-03-12 00:00:00),(刘可颐, 13987654321, liukeyiexample.com, 城市规划, 39, 2, 0, 2001-08-15 00:00:00),(李海洋, 13012345678, lihaiyangexample.com, 城市规划, 40, 1, 0, 2000-04-12 00:00:00),(马一鸣, 13187654321, mayimingexample.com, 土木工程, 21, 1, 3, 2002-07-18 00:00:00),(林泽洋, 13287654321, linzeyangexample.com, 城市园林, 20, 1, 0, 2002-03-10 00:00:00),(秦芷妍, 13387654321, qinzhiyanexample.com, 工程造价, 29, 2, 4, 2003-05-26 00:00:00);-- 查询索引
show index from tb_user;
-- 1.name字段为姓名字段该字段的值可能会重复为该字段创建索引
create index idx_user_name on tb_user(name);
-- 2. phone手机号字段的值是非空且唯一的为该字段创建唯一索引
create unique index idx_user_phone on tb_user(phone);
-- 3. 为profession、age、status创建联合索引
create index idx_user_pro_age_sta on tb_user(profession, age, status);
-- 4. 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);-- 删除索引
drop index idx_user_name on tb_user;
drop index idx_user_pro_age_sta on tb_user;
drop index idx_user_phone on tb_user;
drop index idx_user_email on tb_user;查询结果如下 5. SQL性能分析
5.1 SQL执行频率
MySQL客户端连接成功后通过show[session | global] status命令可以提供服务器状态信息。通过如下指令可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT 的访问频次 SHOW GLOBAL STATUS LIKE ‘Com_______’; 其中show session status是查看当前会话的状态信息show global status是查看全局的状态信息如下 5.2 慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long _query_time单位秒默认10秒) 的所有SQL语句的日志。MySQL的慢查询日志默认没有开启查询慢查询日志开关是否开启的指令如下 SHOW VARIABLES LIKE ‘slow_query_log’; 而查询慢查询日志时间的指令如下 SHOW VARIABLES LIKE ‘long_query_time’; Linux(centOS)中需在MySQL的配置文件(etc/my.cnf)中配置如下信息以开启慢查询日志开关 # 开启MySQL慢查询日志查询开关 slow_query_log1 # 设置慢查询日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志 long_query_time2 详细配置及操作如下
① 首先输入以下指令对/etc/my.cnf进行编辑 vi /etc/my.cnf ② 按’i’键进行编辑并在文档末尾添加如下语句 # 开启慢查询日志开关 slow_query_log1 # 设置慢查询日志时间为2秒 long_query_time2 ③ 按’Esc’键退出编辑输入’:x’或’:wq’或’:wq!保存并退出
④ 重启MySQL服务器 systemctl restart mysqld ⑤ 慢查询日志文件存路径/var/lib/mysql/localhost-slow.log 如该位置没有该文件可在MySQL中通过命令show varaiables like ‘%slow_query_log_file%’查看慢查询日志存放位置
⑥ 查看localhost-slow.log内容 cat localhost-slow.log 从中可以看到除了MySQL版本号、端口号等信息外并无其他多余信息 ⑦ 查看慢查询日志文件尾部实时输出的内容的命令(单独新建个窗口以便查看实时输出) tail -f localhost-slow.log ⑧ 在另一窗口执行MySQL指令如查询tb_user表的数据 select * from tb_user; 可以看到由于查询时间未超过2秒所以慢查询日志实时输出并没有出现数据。接下来使数据库睡眠3秒再查看慢日志查询实时输出 select sleep(3); 此时睡眠的指令可以在慢查询日志中看到而Windows端的慢查询日志开启请参考https://www.cnblogs.com/taozihua/p/10331897.html
5.3 profile
show profiles能够在做SQL优化时帮助我们了解时间都花费在哪里。通过have_profiling参数能够看到当前MySQL是否支持profile操作 SELECT have_profiling; profiling默认是关闭的可以通过以下指令查看profiling是否开启 SELECT profiling; 若为0则为关闭状态若为1则为开启状态 若profiling处于关闭状态可通过set语句开启profiling set [global | session] profiling1 开启profiling后执行一系列的业务SQL操作然后通过以下指令查看指令的执行耗时 # 查看每一条SQL的耗时基本情况 show profiles; # 查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id; # 查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id; 案例演示
-- 查询profiling是否打开
select profiling;-- 开启profiling
set profiling 1;-- 执行SQL操作
select * from tb_user;
select * from tb_user where id 15;
select * from tb_user where name 单景瑞;
select count(*) from tb_user;
select * from tb_user where gender 1;
select sleep(3);-- 查看每一条sql的耗时基本情况
show profiles;
此时运行结果如下
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query 11;此时运行结果如下
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query 11;5.4 explain执行计划
EXPLAIN或DESC命令获取MySQL如何执行SELECT语句的信息包括SELECT语句执行过程中表如何连接和连接的顺序
语法 # 直接在select语句之前加上关键字explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件; 演示
explain select * from tb_user where id 8;结果如下
5.3.1 EXPLAIN执行计划各字段含义
id
select 查询的序列号表示查询中执行select子句或是操作表的顺序(id相同执行顺序从上到下id不同值越大越先执行)
select_type
表示SELECT的类型常见的取值有 SIMPLE (简单表即不使用表连接或子查询)、PRIMARY (主查询即外层的查询)、UNION (UNION 中的第二个或者后面的查询语句)SUBQUERY ( SELECT / WHERE 之后包含了子查询)等
type
表示连接类型性能由好到差的连接类型为 NULL、system、const、eq_ref、ref、range、index、all
possible_key
显示可能应用在这张表上的索引一个或多个
key
实际使用的索引如果为NULL则没有使用索引
key_len
表示索引中使用的字节数该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下长度越短越好
rows
MySQL认为必须要执行查询的行数在innodb引擎的表中是一个估计值可能并不总是精确的
filtered
表示返回结果的行数占需读取行数的百分比filtered的值越大越好
案例演示
案例中所演示的表分别为students、courses、student_course以及tb_user在MySQL基础4-多表查询以及本文中可以找到对应的表
id
explain select s.* from students s where s.id in (select sc.student_id from student_course sc where sc.sourse_id (select c.id from courses c where c.name Python));运行结果如下 从图中可以看到SQL语句编写顺序是students、student_course、courses但从执行结果来看id最大的为3
对应为courses表这是最先执行的其次是id为2的student_course表然后id相同的则由上而下顺序执行也就
是先执行subquery 2(第二个子查询)最后才执行students表的查询。
select_type
select_type参考意义不大它只是说明当前SQL语句的查询类型
type
type代表访问的类型如
explain select * from tb_user where id 1;在该查询中连接类型为const而type访问类型中性能由好至差分别为 NULL、system、const、eq_ref、ref、range、
index、all。其中 system 只有访问系统表的时候才会出现根据主键、唯一索引进行访问一般会出现const如
show index from tb_user;
select * from tb_user;
explain select * from tb_user where phone 13689012345;如果使用非唯一性的索引进行查询时就会出现ref如
explain * from tb_user where name 黄浩然;在优化SQL语句的时候尽量把type往前优化尽量不要出现all如果出现all的话代表全表扫描性能会比较低
如果出现index代表用了索引但也会对索引进行扫描虽然比all快但性能也比较低
extra代表额外的信息也就是说在执行查询的过程当中在前面几个字段中没有展现出来的值将会在extra进行展示
关于posssible_key、key、key_len、rows 在此不多做演示
重点关注: type、possible_key、key、ken_len
6. 索引使用
6.1 最左前缀法则
如果索引了多列(联合索引)要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将部分失效(后面的字段索引失效)。
案例演示
本案例中所用的表为本文中创建的tb_user表
explain select * from tb_user where profession 软件工程 and age 24 and status 0;运行结果如下 该查询用到了联合索引索引长度为93profession、age、status这三个字段的索引全用上了符合最左前缀法则且中间并未跳过任何一列
explain select * from tb_user where profession 软件工程 and age 24;运行结果如下 该查询索引长度为88意味着status字段的索引长度为5该查询也满足最左前缀法则中间并未跳过某一列
explain select * from tb_user where profession 软件工程;运行结果如下 该查询语句只查询了profession字段并未跳过某一列所以也满足最左前缀法则且可由前两条查询结果推断出age 的索引长度为5
select * from tb_user where profession 软件工程 and status 0;运行结果如下 该查询索引长度为83意味着profession用到索引而status没用到所以索引长度和单独查询profession字段的索引长度是一样的。原因是profession和status间还有一个字段age被跳过这导致了索引部分失效也就是status字段的索引失效
explain select * from tb_user where age 24 and status 0;运行结果如下 该查询用到的是全表扫描而不是索引因为最左边的profession字段并未存在所以该查询不满足最左前缀法则导致索引失效
explain select * from tb_user where age 24 and status 0 and profession 软件工程;运行结果如下 该查询也用到了索引因为最左前缀法则要求的是最左列必须存在但并不意味着最左列的顺序必须是最左边所以该查询语句满足最左前缀法则
总结 最左前缀法则要求包含索引中最左边的列如果最左边的列不存在索引失效如果跳过了其中某一列该列后面的索引将会失效
6.2 范围查询
联合索引中出现范围查询(, )范围查询右侧的列索引失效
案例演示
explain select * from tb_user where profession 软件工程 and age 23 and status 0;运行结果如下 结合前文来看该查询满足最左前缀法则但索引长度为88说明age后面的那一列也就是status索引失效。而在业务允许的情况下尽量用 或 来代替以规避索引失效的情况
explain select * from tb_user where profession 软件工程 and age 23 and status 0;运行结果如下 6.3 索引列运算
不要在索引列上进行运算操作否则索引将失效
案例演示
-- 查询手机尾号为56的用户信息
select * from tb_user where substring(phone,10,2) 56;-- 查看执行信息
explain select * from tb_user where substring(phone,10,2) 56;运行结果如下 可以看到在索引列phone上引用函数运算substring后索引失效所以在业务中应尽量减少对索引列进行运算的操作
6.4 字符串不加引号
字符串类型字段使用时不加引号索引将失效
案例演示
select * from tb_user where phone 13912345678;explain select * from tb_user where phone 13912345678;运行结果如下 在该查询语句中由于phone字段作为字符串在查询的时候未加引号导致索引失效
6.5 模糊匹配
如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引将会失效
案例演示
-- 尾部模糊匹配
select * from tb_user where profession like 软件%;explain select * from tb_user where profession like 软件%;-- 头部模糊匹配
select * from tb_user where profession like %工程;explain select * from tb_user where profession like %工程;运行结果如下 从中可以看到在进行尾部模糊匹配时索引并未失效但对头部进行模糊匹配时索引失效了
6.6 or连接的条件
用or分割开的条件如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到
案例演示
explain eslect * from tb_user where id 15 or age 24;explain select * from tb_user whre phone 13412345678 or age 24;运行结果如下 由于age字段没有索引只有联合索引而如果仅仅只对age字段进行查询不满足最左前缀法则联合索引不会用到。而正由于联合索引用不到所以即使id、phone有索引索引也会失效所以需要针对age字段也建立索引
-- 建立age字段索引
create index idx_user_age on tb_user(age);-- 再次查询
explain select * from tb_user where id 15 or age 24;explain select * from tb_user where phone 13412345678 or age 24;运行结果如下 此时可以看到在建立age字段的索引后用到的是phone字段及age字段的索引而非联合索引所以该查询索引未失效
6.7 数据分布影响
如果MySQL评估使用索引比全表扫描更慢则不使用索引
** 案例演示**
explain select * from tb_user where phone 13612345678;explain select * from tb_user where phone 13112345678;explain select * from tb_user where phone 13012345678;运行结果如下
从中可以看到在查询phone 13612345678’时用到了索引这是因为MySQL在查询时评估该表中大部分phone值都小于该值用索引查询比全表扫描快。但将phone改为’13112345678’或‘13012345678’时MySQL评估全表大部分phone值都比这俩值大用全表扫描会比用索引快所以才进行全表扫描同理还有
explain select * from tb_user where profession is null;explain select * from tb_user where profession is not null;在该查询中由于tb_user表中的profession字段没有null值所以第一个查询语句用到的是索引而第二个查询语句用到的则是全表扫描因为MySQL评估当查询时需要比较大部分值时会优先使用全表扫描而如果只是一小部分的话则优先使用索引。当将profession字段的值都设置为null后结果将相反这里不做演示
6.8 SQL提示
SQL提示是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些认为的提示来达到优化操作的目的
在查询时如果一个字段有多个索引MySQL查询优化器会自动选择索引如下
-- 创建tb_user表中profession字段的索引
create index idx_user_pro on tb_user(profession);-- 查询
explain select * from tb_user where profession 软件工程;运行结果如下 可以看到在该查询中可能用到的索引分别为’idx_user_pro_age_sta’及’idx_user_pro’而实际用到的索引为’idx_user_pro_age_sta’这就是MySQL查询优化器自动选择的结果如果想指定查询索引可参考以下三条查询语句
use index(告诉数据库用哪个索引) explain select * from tb_user use index(idx_user_pro) where profession ‘软件工程’; 该查询语句只是给MySQL建议用指定查询至于查询结果取决于MySQL比较所指定的索引效率快不快后所给出的
ignore index(告诉数据库不用哪个索引) explain select * from tb_user ignore index(idx_user_pro) where profession ‘软件工程’; force index(告诉数据库必须走哪个索引) explain select * from tb_user force index(idx_user_pro) where profession ‘软件工程’; 6.9 覆盖索引
尽量使用覆盖索引(查询用了索引并且需要返回的列在该索引中已经全部能够找到)减少select *
案例演示
-- 删除多余索引以减少干扰
drop index idx_user_age on tb_user;drop index idx_user_email on tb_user;drop index idx_user_pro on tb_user;-- 测试
explain select id, profession from tb_user where profession 软件工程 and age 24 and status 0;explain select id, profession, age from tb_user where profession 软件工程 and age 24 and status 0;explain select id, profession, age, status from tb_user where profession 软件工程 and age 24 and status 0;explain select id, profession, age, status, name from tb_user where profession 软件工程 and age 24 and status 0;explain select * from tb_user where profession 软件工程 and age 24 and status 0;运行结果如下 从中可以看到Extra中的信息出现不同的地方而其中’Using where; Using index’的语句的性能要高于’Using index contition’还需注意的是如果使用的MySQL版本不同Extra中出现的值其展示的形式也是不同的
Ps: using index condition查找使用了索引但是需要回表查询数据 using where; using index查找使用了索引但是需要的数据都在索引列中能找到所以不需要回表查询数据 6.10 前缀索引
当字段类型为字符串(varchartext等)时有时候需要索引很长的字符串这回让索引变得很大查询时浪费大量的磁盘IO影响查询效率。此时可以只将字符串的一部分前缀建立索引这样可以大大节约索引空间从而提高索引效率
创建前缀索引的语法 create index idx_xxx_xxx on table_name(column(n)); 前缀长度
可以根据索引的选择性来决定而选择性是指不重复的索引值(基数)和数据表的记录总数的比值索引选择性越高则查询效率越高唯一索引的选择性是1这是最好的索引选择性性能也是最好的
选择性计算演示
select count(distinct email) / count(*) from tb_user;select count(substring(email, 1, 5)) / count(*) from tb_user;运行结果如下 创建前缀索引演示
create index idx_email_5 on tb_user(email(5));show index from tb_user;运行结果如下
6.11 单列索引与联合索引
单列索引即一个索引只包含单个列 联合索引即一个索引包含了多个列
在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引而非单列索引
单列索引情况
explain select id, phone, name from tb_user where phone 13012345678 and name 李海洋;多条件联合查询时MySQL优化器会评估哪个字段的索引效率更高会选择该索引完成本次查询
联合索引情况
-- 创建联合索引
create unique index idx_user_phone_name on tb_user(phone, name);explain select id, phone, name from tb_user where phone 13012345678 and name 李海洋;-- 强制使用联合索引
explain select id, phone, name from tb_user force index(idx_user_phone_name) where phone 13012345678 and name 李海洋;运行结果如下
7. 索引设计原则
Ⅰ. 针对数据量较大且查询比较频繁的表建立索引
Ⅱ. 针对常作为查询条件(where)、排序(ordder by)、分组(group by)操作的字段建立索引
Ⅲ. 尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高
Ⅳ. 如果是字符串类型的字段字段的长度较长可以针对字段的特点建立前缀索引
Ⅴ. 尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率
Ⅵ. 要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率
Ⅶ. 如果索引不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询
