mysql 的这些坑你踩过吗?快来看看怎么优化 mysql?
什么是mysql?
如果你的回答是关系型数据库,那就会显得有些浅薄。我们平时工作中肯定会用到mysql,但是谈到mysql,就不能只说增删改查。
接下来我们从另一个角度认识一下mysql(其实不仅仅是mysql,对于任何一个产品、服务,我们都应该有一个抽象化的架构,而不能局限于这个产品的某一个区域)
mysql的逻辑分层
连接层:提供客户端的连接功能和权限认证,
服务层: 提供用户使用的接口(curd,主从配置,数据备份等) sql优化器(mysql query optimizer)
# 联合索引 a b c select * from table1 where a=xxx and c=xxx and b=xxx;#经过优化器优化后可以使用索引,复制代码
引擎层 :提供存储数据的方式(innodb myisam archive ,memory,csv,federated ),Mysql在V5.1之前默认存储引擎是MyISAM;在此之后默认存储引擎是InnoDB,myisam 和innodb的区别:https://segmentfault.com/a/1190000021995700
mysql> show engines -> ;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES || MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO || MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO || MyISAM | YES | MyISAM storage engine | NO | NO | NO || CSV | YES | CSV storage engine | NO | NO | NO || ARCHIVE | YES | Archive storage engine | NO | NO | NO || PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO || FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+mysql> show variables like '%storage_engine%';+----------------------------------+--------+| Variable_name | Value |+----------------------------------+--------+| default_storage_engine | InnoDB || default_tmp_storage_engine | InnoDB || disabled_storage_engines | || internal_tmp_disk_storage_engine | InnoDB |+----------------------------------+--------+复制代码
TODO 具体存储引擎和相关使用场景待补充
存储层
mysql的索引类型
Mysql支持的索引类型:我们最常用的是B-TREE索引,但是mysql还有另外一种索引,就是HASH索引https://blog.csdn.net/oChangWen/article/details/54024063
# hashcreate table testhash(fname varchar(50) not null,lname varchar(50) not null,key using hash(fname)) engine=memory;# b-treeCREATE TABLE t( aid int unsigned not null auto_increment, userid int unsigned not null default 0, username varchar(20) not null default ‘’, detail varchar(255) not null default ‘’, primary key(aid), unique key(uid) USING BTREE, key (username(12)) USING BTREE — 此处 uname 列只创建了最左12个字符长度的部分索引)engine=InnoDB;复制代码
使用场景
b-tree 索引原理示意图(二叉树为例) 中序
mysql的这些坑你踩过吗?
创建数据表,插入数据
CREATE TABLE `t_user` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键Id', `name` varchar(30) DEFAULT NULL COMMENT '姓名', `email` varchar(30) DEFAULT NULL COMMENT '邮箱', `age` int(11) DEFAULT NULL COMMENT '年龄', `telephone` varchar(30) DEFAULT NULL COMMENT '电话', `status` tinyint(4) DEFAULT NULL COMMENT '0:正常 1:下线 ', `created_at` datetime DEFAULT CURRENT_TIMESTAMP comment '创建时间', `updated_at` datetime DEFAULT CURRENT_TIMESTAMP comment '更新时间', PRIMARY KEY (`id`), KEY `idx_email` (`email`), KEY `idx_name` (`name`), KEY `idx_telephone` (`telephone`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;# 插入一条数据INSERT INTO test.t_user (name, email, age, telephone, status, created_at, updated_at) VALUES ('jimi', 'ffdsa', 11, '15001262936', 0, DEFAULT, DEFAULT);# 批量插入数据INSERT INTO test.t_user select null, name, email, age, telephone, 0, null, null from t_user;复制代码
字符串转数字,通过以下可以看到,主键id的类型是int,但是 查询的关键字是string,这个时候就会转换
mysql> select * from t_user where id='2424786gafafdfdsa';+---------+------+-------+------+-------------+--------+------------+------------+| id | name | email | age | telephone | status | created_at | updated_at |+---------+------+-------+------+-------------+--------+------------+------------+| 2424786 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL |+---------+------+-------+------+-------------+--------+------------+------------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from t_user where id='2426gafafdfdsa';+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | t_user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 2 warnings (0.00 sec)复制代码
字符串类型的字段0会全匹配
mysql> select * from t_user where email=0 limit 10;+----+------+-------+------+-------------+--------+---------------------+---------------------+| id | name | email | age | telephone | status | created_at | updated_at |+----+------+-------+------+-------------+--------+---------------------+---------------------+| 2 | jimi | ffdsa | 11 | 15001262936 | 0 | 2020-11-27 14:33:57 | 2020-11-27 14:33:57 || 3 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL || 4 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL || 5 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL || 7 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL || 8 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL || 9 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL || 10 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL || 14 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL || 15 | jimi | ffdsa | 11 | 15001262936 | 0 | NULL | NULL |+----+------+-------+------+-------------+--------+---------------------+---------------------+10 rows in set, 10 warnings (0.00 sec)复制代码
大小写敏感问题(造成线上缓存击穿,如语音模块,视频模块已控制)
mysql> select * from t_user where email='ffdsaADFG';+---------+------+-----------+------+-------------+--------+------------+------------+| id | name | email | age | telephone | status | created_at | updated_at |+---------+------+-----------+------+-------------+--------+------------+------------+| 2424786 | jimi | ffdsaADFG | 11 | 15001262936 | 0 | NULL | NULL |+---------+------+-----------+------+-------------+--------+------------+------------+1 row in set (0.00 sec)mysql> select * from t_user where email='ffdsaadfg';+---------+------+-----------+------+-------------+--------+------------+------------+| id | name | email | age | telephone | status | created_at | updated_at |+---------+------+-----------+------+-------------+--------+------------+------------+| 2424786 | jimi | ffdsaADFG | 11 | 15001262936 | 0 | NULL | NULL |+---------+------+-----------+------+-------------+--------+------------+------------+1 row in set (0.00 sec)# 解决大小写问题#utf8_general_ci,表示不区分大小写;utf8_general_cs表示区分大小写;utf8_bin表示二进制比较,也可以比较大小写ALTER TABLE t_user MODIFY COLUMN email VARCHAR(30) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;mysql> select * from t_user where email='ffdsaadfg';Empty set (0.00 sec)复制代码
数字转字符串,但是这种转化是用不上索引的
mysql> select * from t_user where email=123;;+---------+------+--------+------+-------------+--------+------------+------------+| id | name | email | age | telephone | status | created_at | updated_at |+---------+------+--------+------+-------------+--------+------------+------------+| 2424789 | jimi | 123abc | 11 | 15001262936 | 0 | NULL | NULL |+---------+------+--------+------+-------------+--------+------------+------------+1 row in set, 65535 warnings (2.57 sec)mysql> explain select * from t_user where email=123;+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+| 1 | SIMPLE | t_user | NULL | ALL | idx_email | NULL | NULL | NULL | 2090340 | 10.00 | Using where |+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+1 row in set, 3 warnings (0.00 sec)复制代码
作为一个phper,此处也吐槽一下php的弱类型
/** * Notes:布尔类型转换 * User: zhangguofu * Date: 2020/12/1 * Time: 4:35 下午 */ public function test1() { $a = 2; $b = 3; if ($a = 3 || $b = 6) { $a++; $b++; } echo $a . " " . $b;//1 4 } /** * Notes:字符串 数字类型转换 * User: zhangguofu * Date: 2020/11/26 * Time: 8:01 下午 */ public function test2() { $a = 'a'; $b = 'b'; $a++; var_dump($a == $b);//true }/** * Notes:字符串 数字 弱类型对比和转换 * User: zhangguofu * Date: 2020/12/4 * Time: 3:12 下午 */ function test3() { var_dump(md5('240610708') == md5('QNKCDZO')); //true var_dump("admin" == 0); //true var_dump("1admin" == 1); //true var_dump("admin1" == 1);//false var_dump("admin1" == 0);//true var_dump("0e123456" == "0e4456789"); //true var_dump(0 == "a"); // 0 == 0 -> true var_dump("1" == "01"); // 1 == 1 -> true var_dump("10" == "1e1"); // 10 == 10 -> true var_dump(100 == "1e2"); // 100 == 100 -> true }复制代码
怎么优化mysql?Explain 分析查看mysql性能
mysql> explain select * from t_user where email=123;+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+| 1 | SIMPLE | t_user | NULL | ALL | idx_email | NULL | NULL | NULL | 2090340 | 10.00 | Using where |+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-------------+复制代码
id : 编号 select_type :查询类型 table :表 type :类型 possible_keys :预测用到的索引 key :实际使用的索引 key_len :实际使用索引的长度
ref :表之间的引用 rows :通过索引查询到的数据量 Extra :额外的信息
解释: 插入数据
#课程表create table course(cid int(3),cname varchar(20),tid int(3));#教师表create table teacher(tid int(3),tname varchar(20),tcid int(3));# 教师证create table teacherCard(tcid int(3),tcdesc varchar(200));insert into course values(1,'java',1);insert into course values(2,'html',1);insert into course values(3,'sql',2);insert into course values(4,'web',3);insert into teacher values(1,'tz',1);insert into teacher values(2,'tw',2);insert into teacher values(3,'tl',3);insert into teacherCard values(1,'tzdesc') ;insert into teacherCard values(2,'twdesc') ;insert into teacherCard values(3,'tldesc') ;复制代码
id: id值相同,从上往下 顺序执行。id值不同:id值越大越优先查询 (本质:在嵌套子查询时,先查内层 再查外层)
mysql> explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid -> and t.tcid = tc.tcid and c.cid = 2 or tc.tcid=3 ;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+| 1 | SIMPLE | tc | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL || 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where; Using join buffer (Block Nested Loop) || 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+3 rows in set, 1 warning (0.00 sec)复制代码
select_type:查询类型
simple 简单子查询,不包含子查询和union primary 包含union或者子查询,最外层的部分标记为primary subquery 一般子查询中的子查询被标记为subquery,也就是位于select列表中的查询 derived 派生表——该临时表是从子查询派生出来的,位于form中的子查询 union 位于union中第二个及其以后的子查询被标记为union,第一个就被标记为primary如果是union位于from中则标记为derived union result 用来从匿名临时表里检索结果的select被标记为union result dependent union 顾名思义,首先需要满足UNION的条件,及UNION中第二个以及后面的SELECT语句,同时该语句依赖外部的查询 subquery 子查询中第一个SELECT语句 dependent subquery 和DEPENDENT UNION相对UNION一样
mysql> explain select * from teacherCard limit 1;+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE | teacherCard | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+mysql> explain select cr.cname from ( select * from course where tid = 1 union select * from course where tid = 2 ) cr ;+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL || 2 | DERIVED | course | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where || 3 | UNION | course | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where || NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+复制代码
type
system: 只有一条数据的系统表 ;或 衍生表只有一条数据的主查询 const:仅仅能查到一条数据的SQL ,用于Primary key 或unique索引 (类型 与索引类型有关)
mysql> create table test01 -> ( -> tid int(3), -> tname varchar(20) -> );Query OK, 0 rows affected (0.04 sec)mysql>mysql> insert into test01 values(1,'a') ;Query OK, 1 row affected (0.01 sec)mysql> alter table test01 add constraint tid_pk primary key(tid) ;Query OK, 0 rows affected (0.15 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from (select * from test01 )t where tid =1 ;+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | test01 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码
ref:非唯一性索引,对于每个索引键的查询,返回匹配的所有行(0,多)
mysql>mysql> alter table test01 drop primary key ;Query OK, 1 row affected (0.08 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> create index test01_index on test01(tid) ;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from (select * from test01 )t where tid =1 ;+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+| 1 | SIMPLE | test01 | NULL | ref | test01_index | test01_index | 4 | const | 1 | 100.00 | NULL |+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+复制代码
eq_ref:唯一性索引:对于每个索引键的查询,返回匹配唯一行数据(有且只有1个,不能多 、不能0)
mysql> alter table teacherCard add constraint pk_tcid primary key(tcid);ERROR 1068 (42000): Multiple primary key definedmysql> alter table teacher add constraint uk_tcid unique index(tcid) ;ERROR 1061 (42000): Duplicate key name 'uk_tcid'mysql>mysql>mysql> explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+| 1 | SIMPLE | t | NULL | index | uk_tcid | uk_tcid | 5 | NULL | 3 | 100.00 | Using where; Using index || 1 | SIMPLE | tc | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t.tcid | 1 | 100.00 | Using index |+----+-------------+-------+------------+--------+---------------+---------+---------+-------------+------+----------+--------------------------+2 rows in set, 1 warning (0.00 sec)复制代码
range:检索指定范围的行 ,where后面是一个范围查询(between ,> < >=, 特殊:in有时候会失效 ,从而转为 无索引all--5.7以前的版本)
mysql> alter table teacher add index tid_index (tid) ;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain select t.* from teacher t where t.tid in (1,2) ;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t | NULL | ALL | tid_index | NULL | NULL | NULL | 3 | 66.67 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> explain select t.* from teacher t where t.tid <3 ;+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+| 1 | SIMPLE | t | NULL | range | tid_index | tid_index | 5 | NULL | 2 | 100.00 | Using index condition |+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)复制代码
index:查询全部索引中数据,不需要回表查找,黄金索引
mysql> explain select tid from teacher ;+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+| 1 | SIMPLE | teacher | NULL | index | NULL | tid_index | 5 | NULL | 3 | 100.00 | Using index |+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)复制代码
all:查询全部表中的数据,全表扫描
mysql> explain select * from teacher -> ;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE | teacher | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)复制代码
possible_keys :可能用到的索引,是一种预测,不准。
mysql> explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid -> and t.tcid = tc.tcid and c.cname = 'sql' ;+----+-------------+-------+------------+--------+-------------------+-----------+---------+-------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+--------+-------------------+-----------+---------+-------------+------+----------+-------------+| 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where || 1 | SIMPLE | t | NULL | ref | uk_tcid,tid_index | tid_index | 5 | test.c.tid | 1 | 100.00 | Using where || 1 | SIMPLE | tc | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.t.tcid | 1 | 100.00 | NULL |+----+-------------+-------+------------+--------+-------------------+-----------+---------+-------------+------+----------+-------------+3 rows in set, 1 warning (0.00 sec)复制代码
key :实际使用到的索引
key_len :索引的长度 ; 作用:用于判断复合索引是否被完全使用 (a,b,c)。
mysql> create table test_kl -> ( -> name char(20) not null default '' -> );Query OK, 0 rows affected (0.03 sec)mysql> alter table test_kl add index index_name(name) ;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from test_kl where name ='' -> ;+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+| 1 | SIMPLE | test_kl | NULL | ref | index_name | index_name | 80 | const | 1 | 100.00 | Using index |+----+-------------+---------+------------+------+---------------+------------+---------+-------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> show variables like '%char%';+--------------------------+----------------------------------+| Variable_name | Value |+--------------------------+----------------------------------+| character_set_client | latin1 || character_set_connection | latin1 || character_set_database | utf8mb4 || character_set_filesystem | binary || character_set_results | latin1 || character_set_server | utf8mb4 || character_set_system | utf8 || character_sets_dir | /usr/local/mysql/share/charsets/ |+--------------------------+----------------------------------+8 rows in set (0.06 sec)# 字符集utf8mb4 char 20 就是 80,如果有null 则null 占一个字节,如果是varchar 则需要1-2个字节存储值的长度mysql> alter table test_kl add column name1 char(20) ;Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table test_kl add index name_name1_index (name,name1) ;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from test_kl where name1 = '' ;+----+-------------+---------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+| 1 | SIMPLE | test_kl | NULL | index | NULL | name_name1_index | 161 | NULL | 1 | 100.00 | Using where; Using index |+----+-------------+---------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from test_kl where name = '' -> -> ;+----+-------------+---------+------------+------+-----------------------------+------------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+-----------------------------+------------+---------+-------+------+----------+-------+| 1 | SIMPLE | test_kl | NULL | ref | index_name,name_name1_index | index_name | 80 | const | 1 | 100.00 | NULL |+----+-------------+---------+------------+------+-----------------------------+------------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from myTest where b=3 and c=4;+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+| 1 | SIMPLE | myTest | NULL | ALL | NULL | NULL | NULL | NULL | 32893 | 1.00 | Using where |+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+1 row in set, 1 warning (0.00 sec)mysql> explain select * from myTest where a=3 and c=4;+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+| 1 | SIMPLE | myTest | NULL | ref | a | a | 5 | const | 1 | 10.00 | Using index condition |+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+1 row in set, 1 warning (0.01 sec)复制代码
ref 指明当前表所 参照的 字段。
mysql> alter table course add index tid_index (tid) ;Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> explain select * from course c,teacher t where c.tid = t.tid and t.tname ='tw' ;+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+| 1 | SIMPLE | t | NULL | ALL | tid_index | NULL | NULL | NULL | 3 | 33.33 | Using where || 1 | SIMPLE | c | NULL | ref | tid_index | tid_index | 5 | test.t.tid | 1 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+复制代码
rows: 被索引优化查询的 数据个数 (实际通过索引而查询到的 数据个数)
mysql> explain select * from course c,teacher t where c.tid = t.tid -> and t.tname = 'tz' ;+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+| 1 | SIMPLE | t | NULL | ALL | tid_index | NULL | NULL | NULL | 3 | 33.33 | Using where || 1 | SIMPLE | c | NULL | ref | tid_index | tid_index | 5 | test.t.tid | 1 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+-----------+---------+------------+------+----------+-------------+2 rows in set, 1 warning (0.00 sec)复制代码
Extra:
using filesort : 性能消耗大;需要“额外”的一次排序(查询) 。常见于 order by 语句中。 对于单索引, 如果排序和查找是同一个字段,则不会出现using filesort;如果排序和查找不是同一个字段,则会出现using filesort; 怎么避免: where哪些字段,就order by那些字段 where和order by 按照复合索引的顺序使用,不要跨列或无序使用。
mysql> create table test02 -> ( -> a1 char(3), -> a2 char(3), -> a3 char(3), -> index idx_a1(a1), -> index idx_a2(a2), -> index idx_a3(a3) -> );Query OK, 0 rows affected (0.03 sec)mysql>mysql> explain select * from test02 where a1 ='' order by a1 ;+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+| 1 | SIMPLE | test02 | NULL | ref | idx_a1 | idx_a1 | 13 | const | 1 | 100.00 | NULL |+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.01 sec)mysql> explain select * from test02 where a1 ='' order by a2 ;+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+| 1 | SIMPLE | test02 | NULL | ref | idx_a1 | idx_a1 | 13 | const | 1 | 100.00 | Using index condition; Using filesort |+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+---------------------------------------+1 row in set, 1 warning (0.00 sec)复制代码
using temporary:性能损耗大 ,用到了临时表。一般出现在group by 语句中。
避免:查询那些列,就根据那些列 group by .
using index :性能提升; 索引覆盖(覆盖索引)。原因:不读取原文件,只从索引文件中获取数据 (不需要回表查询),只要使用到的列 全部都在索引中,就是索引覆盖using index
using where (需要回表查询)
impossible where : where子句永远为false select * from test02 where a1='x' and a1='y'
关于数据表格式规范
谈谈mysql中utf8和utf8mb4区别
简介
MySQL在5.5.3之后增加了这个utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集,除了将编码改为utf8mb4外不需要做其他转换。当然,为了节省空间,一般情况下使用utf8也就够了。
那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节,如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff,也就是 Unicode 中的基本多文种平面(BMP)。也就是说,任何不在基本多文本平面的 Unicode字符,都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码,常见于 ios 和 android 手机上 ),和很多不常用的汉字,以及任何新增的 Unicode 字符等等。
- 我认为 合理表应该这样设计CREATE TABLE `demo` ( `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键Id', `uuid` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '业务id', `create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间', `update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '更新时间', `status` tinyint(2) NOT NULL DEFAULT 0 COMMENT '状态 0:正常 1:下线', `logical_del` tinyint(2) NOT NULL DEFAULT 0 COMMENT '逻辑删除标识', PRIMARY KEY `id`(`Id`) USING BTREE, INDEX `uuid`(`uuid`) USING BTREE) ENGINE = InnoDB CHARSET=utf8mb4 COMMENT = 'demo';
最后,小编还给大家整理了一份面试宝典,有需要的小伙伴只需要添加小编的vx:mxzFAFAFA即可免费领取!!!
比伯
还未添加个人签名 2020.11.09 加入
还未添加个人简介
评论