成都红酒网站建设,网站下载视频的方法,网站建设技术部奖惩制度,永久个人自助建站chihiro-notes
千寻简笔记 v0.1 内测版 #x1f4d4; 笔记介绍
大家好#xff0c;千寻简笔记是一套全部开源的企业开发问题记录#xff0c;毫无保留给个人及企业免费使用#xff0c;我是作者星辰#xff0c;笔记内容整理并发布#xff0c;内容有误请指出#xff0c;笔…
chihiro-notes
千寻简笔记 v0.1 内测版 笔记介绍
大家好千寻简笔记是一套全部开源的企业开发问题记录毫无保留给个人及企业免费使用我是作者星辰笔记内容整理并发布内容有误请指出笔记源码已开源前往Gitee搜索《chihiro-notes》感谢您的阅读和关注。
作者各大平台直链 GitHub | Gitee | CSDN 文章目录 笔记介绍初级篇应用题表操作创建一个表增加表字段增删改插入一条数据插入一条数据删除一条数据更新一条数据库查询篇查询所有数据条件查询 user_id 123 的数据条件查询查询 user_id 123 或 456 的数据应用题中级篇常用条件查询模糊查询联查关键字UNION ALL关键字DISTINCT【Java代码】xml 循环set数组关键字EXISTS关键字CASE WHEN存储过程利用生成假数据创建存储过程调用存储过程删除存储过程应用题有关时间的语句x日期 - y日期 小于等于 40天计算两个时间相差的天数sql如何计算一个日期某个周期后的日期select语句查询近一周的数据SQL利用Case When Then多条件判断MySQL内连接INNER JOINbetween索引-理论篇存储方式区分B-树索引BTREE哈希索引Hash逻辑区分普通索引INDEX唯一索引UNIQUE主键索引PRIMARY KEY空间索引SPATIAL全文索引FULLTEXT实际使用单列索引多列索引/复合索引删除索引索引-实践篇增删查添加索引查看索引删除索引索引失效一、隐式的类型转换索引失效二、查询条件包含or可能导致索引失效三、like通配符可能导致索引失效四、查询条件不满足联合索引的最左匹配原则五、在索引列上使用mysql的内置函数六、对索引进行列运算如、-、*、/,索引不生效七、索引字段上使用 或者 索引可能失效八、索引字段上使用is null is not null索引可能失效九、左右连接关联的字段编码格式不一样十、优化器选错了索引3.3 索引速度对比初级篇
SQL DML 和 DDL
可以把 SQL 分为两个部分数据操作语言 (DML) 和 数据定义语言 (DDL)。SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。
应用题
表操作
创建表
创建一个表
DROP TABLE IF EXISTS key_value;
CREATE TABLE key_value(_key VARCHAR(255) COMMENT 键 ,_value VARCHAR(255) COMMENT 值
) COMMENT 键值对;增加表字段
ALTER TABLE 给表条件一个字段 ALTER TABLE 表名 ADD 字段名 VARCHAR ( 128 ) COMMENT 备注;ALTER TABLE t_user ADD user_name VARCHAR ( 128 ) COMMENT 用户名称;增删改
查询和更新指令构成了 SQL 的 DML 部分
SELECT - 从数据库表中获取数据
UPDATE - 更新数据库表中的数据
DELETE - 从数据库表中删除数据
INSERT INTO - 向数据库表中插入数据插入一条数据
插入一条数据
INSERT INTO 语句 INSERT INTO 语句用于向表格中插入新的行。 //语法
INSERT INTO 表名称 VALUES (值1, 值2,....)
//我们也可以指定所要插入数据的列
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)INSERT INTO key_value VALUES (1,2222);
INSERT INTO key_value (_key,_value) VALUES (2,键值对);删除一条数据
DELETE 语句 DELETE 语句用于删除表中的行。 //语法:
DELETE FROM 表名称 WHERE 列名称 值DELETE FROM key_value WHERE _key 2;
SELECT * FROM key_value;更新一条数据库
Update 语句 Update 语句用于修改表中的数据。 语法
UPDATE 表名称 SET 列名称 新值 WHERE 列名称 某值UPDATE key_value set _key 我不想做主键 WHERE _key 1;
SELECT * from key_value;查询篇
查询所有数据 现在我们希望从 “Persons” 表中选取所有的列。 请使用符号 * 取代列的名称就像这样 SELECT * FROM Persons条件查询 user_id 123 的数据
SELECTtu.id,tu.user_name
FROMtu.t_user AS tu
WHEREtu.user_id 123;条件查询查询 user_id 123 或 456 的数据
SELECTtu.id,tu.user_name
FROMt_user AS tu
WHEREtu.user_id 123OR tu.user_id 456;应用题
问你怎么快速找出两条相同的数据字段为id
SELECTcid.id,cid.id ,cid.name
FROMchihiro_id AS cid
GROUP BYcid.id HAVING COUNT(cid.id )1;验证是否正确
SELECTcid.id,cid.id ,cid.name
FROMchihiro_id AS cid
WHEREcid.id 34170
OR cid.id 15022
;删除重复的id
DELETE FROM chihiro_id
WHEREid 317021266123 OR id 317021266123
;中级篇
常用条件查询
模糊查询
select * from chihiro_area;
SELECT * FROM chihiro_area WHERE 11 and name LIKE %北;
SELECT name,area_code FROM chihiro_area WHERE 11 and area_code LIKE 11%;
select * from chihiro_area where parent_code LIKE 1100%;
select * from chihiro_area WHERE name LIKE 北京%;联查
SELECT * from sys_user;
SELECT * from sys_dept;select su.dept_id,su.user_name,sd.dept_name,sd.email
from sys_user AS su
INNER JOIN sys_dept AS sd ON su.dept_id sd.dept_id;关键字UNION ALL
多字段查询
-- 用于多字段查询
SELECTlc.id,lc.first_hearing_address AS hearingAddress
FROMt_layer_case AS lc
WHERElc.first_hearing_address !
UNION ALL
SELECTlc.id,lc.second_hearing_address AS hearingAddress
FROMt_layer_case AS lc
WHERElc.second_hearing_address !
UNION ALL
SELECTlc.id,lc.executive_court AS hearingAddress
FROMt_layer_case AS lc
WHERElc.executive_court ! Union all 查询完统计
select a,b,c from (select a, b, c from aaunion all select a1 as a, b1 as b, c1 as c from bb
) a group by c关键字DISTINCT
-- 去重手机号
SELECT DISTINCT first_economics_officer_contact AS economicsOfficerContact,first_economics_officer AS economicsOfficer
FROMt_layer_case
WHEREfirst_economics_officer_contact is not null【Java代码】xml 循环set数组
if testcaseTypeSet ! nullAND lc.case_type INforeach collectioncaseTypeSet itemitem open( separator, close)#{item}/foreach
/if关键字EXISTS
实际场景查询表a中a.id,在表b中是否存在车辆
AND EXISTS(SELECT tpc.types_of_property_clues FROM t_property_clues AS tpc WHERE tpc.types_of_property_clues 车辆 AND tpc.case_id lc.id)
-- 判断
SELECTCOUNT(*) AS number,hearingAddress
FROM(SELECTlc.id,lc.first_hearing_address AS hearingAddress FROMt_layer_case AS lcLEFT JOIN t_entrusted_client AS ec ON ec.id lc.entrusted_client_idLEFT JOIN t_upcoming AS tu ON tu.case_id lc.idWHERE1 1 AND lc.first_hearing_address ! AND EXISTS(SELECT tpc.types_of_property_clues FROM t_property_clues AS tpc WHERE tpc.types_of_property_clues 车辆 AND tpc.case_id lc.id)UNION ALLSELECTlc.id,lc.second_hearing_address AS hearingAddress FROMt_layer_case AS lcLEFT JOIN t_entrusted_client AS ec ON ec.id lc.entrusted_client_idLEFT JOIN t_upcoming AS tu ON tu.case_id lc.idWHERE1 1 AND lc.second_hearing_address ! AND EXISTS(SELECT tpc.types_of_property_clues FROM t_property_clues AS tpc WHERE tpc.types_of_property_clues 车辆 AND tpc.case_id lc.id)UNION ALLSELECTlc.id,lc.executive_court AS hearingAddress FROMt_layer_case AS lcLEFT JOIN t_entrusted_client AS ec ON ec.id lc.entrusted_client_idLEFT JOIN t_upcoming AS tu ON tu.case_id lc.idLEFT JOIN ( SELECT id, case_id, types_of_property_clues FROM t_property_clues WHERE types_of_property_clues 车辆 GROUP BY case_id ) AS tpc ON tpc.case_id lc.id WHERE1 1 AND lc.executive_court ! AND EXISTS(SELECT tpc.types_of_property_clues FROM t_property_clues AS tpc WHERE tpc.types_of_property_clues 车辆 AND tpc.case_id lc.id)) table1
GROUP BYhearingAddress
ORDER BYCOUNT(*) DESC LIMIT 10关键字CASE WHEN
查询结果等于0 就返回一1 其他返回0
SELECTtfm.id AS id,(CASE WHEN SUM(trs.repayment_amount_instalment * (lawyer_fee_proportion/100))-SUM(trs.repayment_amount* (lawyer_fee_proportion/100)) 0 THEN 1 ELSE 0 END) AS fee_clear,
FROMt_financial_management AS tfm存储过程
利用生成假数据
创建存储过程
delimiter //
create procedure batchInsert()
begindeclare num int; set num1;while num1000000 doinsert into key_value(username,password) values(concat(测试用户, num),123456);set numnum1;end while;
end
//
delimiter ; #恢复;表示结束调用存储过程
写好了存储过程就可以进行调用了可以通过命令调用
CALL batchInsert;也可以在数据库工具的中Functions的栏目下找到刚刚创建的存储过程直接执行。
删除存储过程
drop procedure batchInsert; 应用题
有关时间的语句
-- 改成日期的时间戳
SELECT NOW();
SELECT UNIX_TIMESTAMP(NOW());
SELECT UNIX_TIMESTAMP(2022-12-27);x日期 - y日期 小于等于 40天
-- 当前时间大于开庭时间代表已开庭
SELECT tlc.first_hearing_time AS courtDate,CASE WHEN NOW() tlc.first_hearing_time THEN 1 ELSE 0 END AS isOpenACourtSession
FROM t_layer_case AS tlc
WHEREtlc.first_hearing_time IS NOT NULL
ANDABS(DATEDIFF(first_hearing_time,2022-12-27 16:56:13 )) 40;
计算两个时间相差的天数
ABS(DATEDIFF(tpc.appeal_time_of_closure_and_registration,NOW())) AS 累计查封时间,sql如何计算一个日期某个周期后的日期
-- 查询x日期y年后的日期
SELECT DATE_ADD(NOW(),INTERVAL 3 YEAR);select语句查询近一周的数据
select * from table where
DATE_SUB(CURDATE(), INTERVAL 7 DAY) date(column_time);SQL利用Case When Then多条件判断 CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 WHEN 条件3 THEN 结果3 WHEN 条件4 THEN 结果4 … WHEN 条件N THEN 结果N ELSE 结果X END Case具有两种格式。简单Case函数和Case搜索函数。 –简单Case函数 CASE sex WHEN ‘1’ THEN ‘男’ WHEN ‘2’ THEN ‘女’ ELSE ‘其他’ END –Case搜索函数 CASE WHEN sex ‘1’ THEN ‘男’ WHEN sex ‘2’ THEN ‘女’ ELSE ‘其他’ END CASE WHEN bn.endDay 60 THEN 1WHEN bn.endDay 30 THEN 2WHEN bn.endDay 15 THEN 3ELSE不提醒END AS level,MySQL内连接INNER JOIN
SELECTtpc.id,tpc.case_id,tpc.entrusted_client_id,tpc.types_of_property_clues,tpc.property_clue_information,CASE WHEN bn.endDay 60 THEN 1WHEN bn.endDay 30 THEN 2WHEN bn.endDay 15 THEN 3ELSE不提醒END AS level,tlc.defendant_name,tlc.first_case_number,tlc.second_case_number,tlc.execution_case_number
FROMt_property_clues AS tpc
INNER JOIN(SELECTtpcc.id AS id,DATE_ADD(tpcc.appeal_time_of_closure_and_registration,INTERVAL tpcc.appeal_legal_time_of_closure YEAR) AS endTime,ABS(DATEDIFF(DATE_ADD(tpcc.appeal_time_of_closure_and_registration,INTERVAL tpcc.appeal_legal_time_of_closure YEAR),NOW())) AS endDayFROMt_property_clues AS tpcc
)AS bn ON bn.id tpc.id
LEFT JOINt_layer_case AS tlc ON tlc.id tpc.case_id
WHERE11
AND ABS(DATEDIFF(bn.endTime,NOW())) 60
;between
between value1 and value2 (筛选出的条件中包括value1,但是不包括vaule2也就是说
索引-理论篇
存储方式区分
MySQL 索引可以从存储方式、逻辑角度和实际使用的角度来进行分类。根据存储方式的不同MySQL 中常用的索引在物理上分为 B-树索引和HASH索引两类两种不同类型的索引各有其不同的适用范围。
B-树索引BTREE B-树索引又称为 BTREE 索引目前大部分的索引都是采用 B-树索引来存储的。 B-树索引是一个典型的数据结构其包含的组件主要有以下几个。 叶子节点包含的条目直接指向表里的数据行。叶子节点之间彼此相连一个叶子节点有一个指向下一个叶子节点的指针。 分支节点包含的条目指向索引里其他的分支节点或者叶子节点。 根节点一个 B-树索引只有一个根节点实际上就是位于树的最顶端的分支节点。 基于这种树形数据结构表中的每一行都会在索引上有一个对应值。因此在表中进行数据查询时可以根据索引值一步一步定位到数据所在的行。
B-树索引可以进行全键值、键值范围和键值前缀查询也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则要考虑以下几点约束
查询必须从索引的最左边的列开始。查询不能跳过某一索引列必须按照从左到右的顺序进行匹配。存储引擎不能使用索引中范围条件右边的列。
哈希索引Hash 哈希Hash一般翻译为“散列”也有直接音译成“哈希”的就是把任意长度的输入又叫作预映射pre-image通过散列算法变换成固定长度的输出该输出就是散列值。 哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中MEMORY 存储引擎可以支持 B-树索引和 HASH 索引且将 HASH 当成默认索引。 HASH 索引不是基于树形的数据结构查找数据而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快但也存在下面的一些缺点 MySQL 需要读取表中索引列的值来参与散列计算散列计算是一个比较耗时的操作。也就是说相对于 B-树索引来说建立哈希索引会耗费更多的时间。不能使用 HASH 索引排序。HASH 索引只支持等值比较如“”“IN()”或“”。HASH 索引不支持键的部分匹配因为在计算 HASH 值的时候是通过整个索引值来计算的。 逻辑区分
根据索引的具体用途MySQL 中的索引在逻辑上分为以下五类
普通索引INDEX唯一索引UNIQUE主键索引PRIMARY KEY空间索引SPATIAL全文索引FULLTEXT
普通索引INDEX
普通索引是 MySQL 中最基本的索引类型它没有任何限制唯一任务就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。创建普通索引时通常使用的关键字是 INDEX 或 KEY。
基本语法如下
CREATE INDEX index_id
ON my_chihiro(id);唯一索引UNIQUE 唯一索引与普通索引类似不同的是唯一索引不仅用于提高性能而且还用于数据完整性唯一索引不允许将任何重复的值插入表中 唯一索引列的值必须唯一允许有空值。 如果是组合索引则列值的组合必须唯一。 创建唯一索引通常使用 UNIQUE 关键字。
基本语法如下
CREATE UNIQUE INDEX index_id
ON my_chihiro(id);主键索引PRIMARY KEY
主键索引就是专门为主键字段创建的索引也属于索引的一种。主键索引是一种特殊的唯一索引不允许值重复或者值为空。创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。
空间索引SPATIAL
空间索引是对空间数据类型的字段建立的索引使用 SPATIAL 关键字进行扩展。创建空间索引的列必须将其声明为 NOT NULL空间索引只能在存储引擎为 MyISAM 的表中创建。空间索引主要用于地理空间数据类型 GEOMETRY。
基本语法如下my_chihiro 表的存储引擎必须是 MyISAMline 字段必须为空间数据类型而且是非空的。
CREATE SPATIAL INDEX index_line
ON my_chihiro(line);全文索引FULLTEXT
全文索引主要用来查找文本中的关键字只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许在索引列中插入重复值和空值。不过对于大容量的数据表生成全文索引非常消耗时间和硬盘空间。
基本语法如下index_info 的存储引擎必须是 MyISAMinfo 字段必须是 CHAR、VARCHAR 和 TEXT。
CREATE FULLTEXT INDEX index_info
ON my_chihiro(info);实际使用
在实际应用中索引通常分为
单列索引复合索引/多列索引/组合索引
单列索引
单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引单列索引只根据该字段进行索引。单列索引可以是普通索引也可以是唯一性索引还可以是全文索引。只要保证该索引只对应一个字段即可。
基本语法如下address 字段的数据类型为 VARCHAR(20)索引的数据类型为 CHAR(6)查询时可以只查询 address 字段的前 6 个字符而不需要全部查询。
CREATE INDEX index_addr
ON my_chihiro(address(6));多列索引/复合索引
组合索引也称为复合索引或多列索引。相对于单列索引来说组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段可以通过这几个字段进行查询。注意只有查询条件中使用了这些字段中第一个字段时索引才会被使用。
基本语法如下索引创建好了以后查询条件中必须有 name 字段才能使用索引
CREATE INDEX index_na
ON tb_student(name,address); 无论是创建单列索引还是复合索引都应考虑在查询的WHERE子句中可能经常使用的列作为过滤条件。 如果仅使用一列则应选择单列索引如果在WHERE子句中经常使用两个或多个列作为过滤器则复合索引将是最佳选择。 一个表可以有多个单列索引但这些索引不是组合索引。 一个组合索引实质上为表的查询提供了多个索引以此来加快查询速度。比如在一个表中创建了一个组合索引(c1c2c3)在实际查询中系统用来实际加速的索引有三个单个索引(c1)、双列索引(c1c2)和多列索引(c1c2c3)。 删除索引
DROP INDEX命令 可以使用SQL DROP 命令删除索引删除索引时应小心因为性能可能会降低或提高。
基本语法如下
DROP INDEX index_name;索引-实践篇
增删查
添加索引
alter table chihiro_member_info add index idx_name (name);查看索引
SHOW INDEX FROM chihiro_member_info;删除索引
DROP INDEX 索引名 ON 表名
DROP INDEX idx_name ON chihiro_member_info;索引失效 有时候我们明明加了索引了但是索引却不生效。在哪些场景索引会不生效呢主要有以下十大经典场景 一、隐式的类型转换索引失效
我们有一个索引字段name类型为varchar字符串类型如果查询条件传了一个数字去会导致索引失效。
EXPLAIN SELECT * FROM chihiro_member_info WHERE name 1;如果给数字加上’也就是说传的是一个字符串就正常走索引。
EXPLAIN SELECT * FROM chihiro_member_info WHERE name 1;分析为什么第一条语句未加单引号就不走索引了呢这是因为不加单引号时是字符串跟数字的比较它们类型不匹配MySQL会做隐式的类型转换把它们转换为浮点数再做比较。隐式的类型转换索引会失效。 二、查询条件包含or可能导致索引失效
我们在来看一条sql语句name添加了索引但是openid没有添加索引。我们使用or下面的sql是不走索引的。
EXPLAIN SELECT * FROM chihiro_member_info WHERE name 123 or openid 123;分析对于 or没有索引的openid这种情况假设它走 name的索引但是走到 openid查询条件时它还得全表扫描也就是需要三步过程 全表扫描索引扫描合并。如果它一开始就走全表扫描直接一遍扫描就完事。Mysql优化器处于效率与成本考虑遇到 or条件让索引失效。 当 name和role都是索引时使用一张表中的多个索引时mysql会将多个索引合并在一起。
EXPLAIN SELECT * FROM chihiro_member_info WHERE name 123 or role 123;注意如果or条件的列都加了索引**索引可能会走也可能不走**大家可以自己试一试哈。但是平时大家使用的时候还是要注意一下这个or学会用explain分析。遇到不走索引的时候考虑拆开两条SQL。 三、like通配符可能导致索引失效
并不是用了 like通配符索引一定会失效而是 like查询是以 %开头才会导致索引失效。
EXPLAIN SELECT * FROM chihiro_member_info WHERE name LIKE %陈;把 %放到后面索引还是正常走的。
EXPLAIN SELECT * FROM chihiro_member_info WHERE name LIKE 陈%;分析既然 like查询以 %开头会导致索引失效。我们如何优化 使用覆盖索。把 %放后面。 四、查询条件不满足联合索引的最左匹配原则
Mysql建立联合索引时会遵循左前缀匹配原则既最左优先。如果你建立一个a,b,c的联合索引相当于简历了(a)、(a,b)、(a,b,c)。
我们先添加一个联合索引
alter table chihiro_member_info add index idx_name_role_openid (name,role,openid);查看表的索引
SHOW INDEX FROM chihiro_member_info;有一个联合索引idx_name_role_openid我们执行这个SQL查询条件是role索引是无效
EXPLAIN SELECT * FROM chihiro_member_info WHERE role 0;在联合索引中查询条件满足最左匹配原则时索引才正常生效。
EXPLAIN SELECT * FROM chihiro_member_info WHERE name 刘;五、在索引列上使用mysql的内置函数
我们先给创建时间添加一个索引。
ALTER TABLE chihiro_member_info ADD INDEX idx_create_time(create_time);虽然create_time加了索引但是因为使用了mysql的内置函数DATE_ADD()导致直接全表扫描了。
EXPLAIN SELECT * FROM chihiro_member_info WHERE DATE_ADD(create_time,INTERVAL 1 DAY) 2022-10-10 00:00:00;分析一般这种情况怎么优化呢可以把**内置函数的逻辑转移到右边**如下 EXPLAIN SELECT * FROM chihiro_member_info WHERE create_time DATE_ADD(2022-10-10 00:00:00,INTERVAL -1 DAY);六、对索引进行列运算如、-、*、/,索引不生效
给 role字段tinyint添加一个索引。
-- 添加索引
ALTER TABLE chihiro_member_info ADD INDEX idex_role(role);虽然role加了索引但是因为它进行运算索引直接迷路了。如图
EXPLAIN SELECT * FROM chihiro_member_info WHERE role1 1;分析不可以对索引列进行运算可以在代码处理好再传参进去。 七、索引字段上使用 或者 索引可能失效
给 role字段tinyint添加一个索引。
-- 添加索引
ALTER TABLE chihiro_member_info ADD INDEX idex_role(role);注意我在mysql 5.7.26测试测试结果有所不同可以根据mysql版本去测试。 查看mysql版本
SELECT VERSION() FROM DUAL;!正常走的索引。
EXPLAIN SELECT * FROM chihiro_member_info WHERE role ! 2;正常走的索引。
EXPLAIN SELECT * FROM chihiro_member_info WHERE role 2;分析其实这个也是跟mySQL优化器有关如果优化器觉得即使走了索引还是需要扫描很多很多行的哈它觉得不划算**不如直接不走索引。**平时我们用 或者 not in的时候可以先使用 EXPLAIN去看看索引是否生效。 八、索引字段上使用is null is not null索引可能失效
给 role字段tinyint添加一个索引和 name字段varchar添加索引。
-- 添加索引
ALTER TABLE chihiro_member_info ADD INDEX idex_role(role);
ALTER TABLE chihiro_member_info ADD INDEX idex_name(name);单个字段 role字段加上索引查询 role 为空的语句会走索引
EXPLAIN SELECT * FROM chihiro_member_info WHERE role is not null;两字字段用 or链接起来索引就失效了。 分析很多时候也是因为数据量问题导致了MySQL优化器放弃走索引。同时平时我们用explain分析SQL的时候如果typerange需要注意一下因为这个可能因为数据量问题导致索引无效。 九、左右连接关联的字段编码格式不一样
新建两个表一个user一个user_job
CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT,name varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,age int(11) NOT NULL,PRIMARY KEY (id),KEY idx_name (name) USING BTREE
) ENGINEInnoDB AUTO_INCREMENT2 DEFAULT CHARSETutf8;CREATE TABLE user_job (id int(11) NOT NULL,userId int(11) NOT NULL,job varchar(255) DEFAULT NULL,name varchar(255) DEFAULT NULL,PRIMARY KEY (id),KEY idx_name (name) USING BTREE
) ENGINEInnoDB DEFAULT CHARSETutf8;user表的name字段编码是utf8mb4而user_job表的name字段编码为utf8。 执行左外连接查询user_job表还是走全表扫描。 如果把它们的name字段改为编码一致相同的SQL还是会走索引。 分析所以大家在做表关联时注意一下关联字段的编码问题。 十、优化器选错了索引
MySQL 中一张表是可以支持多个索引的。你写SQL语句的时候没有主动指定使用哪个索引的话用哪个索引是由MySQL来确定的。
我们日常开发中不断地删除历史数据和新增数据的场景有可能会导致MySQL选错索引。那么有哪些解决方案呢
使用force index 强行选择某个索引修改你的SQl引导它使用我们期望的索引优化你的业务逻辑优化你的索引新建一个更合适的索引或者删除误用的索引。
3.3 索引速度对比
测试数据量量400万字段包含id、username、password
-- 数据量量400万字段包含id、username、password-- 没有索引下查询
SELECT * FROM key_value;select * from key_value WHERE username 测试用户388888
-- OK
-- 时间: 1.496sselect * from key_value WHERE username 测试用户388888
-- OK
-- 时间: 1.503sselect * from key_value WHERE username 测试用户388888
-- OK
-- 时间: 1.475s-- 创建索引后
SELECT * from key_value WHERE username 测试用户388888;SELECT * from key_value WHERE username 测试用户388888
-- OK
-- 时间: 0.005sSELECT * from key_value WHERE username 测试用户3588828;
-- OK
-- 时间: 0.005s-- 测试查找主键id
-- 主键也是有索引的是所以非常快
SELECT * from key_value WHERE id 123333;
-- OK
-- 时间: 0.004s
