共计 27125 个字符，预计需要花费 68 分钟才能阅读完成。
什么是 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/11…
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/oChangW…
# hash
create table testhash(fname varchar(50) not null,
lname varchar(50) not null,
key using hash(fname)) engine=memory;
# b-tree
CREATE 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: 0
mysql> 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: 0
mysql> create index test01_index on test01(tid) ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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 defined
mysql> 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: 0
mysql> 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: 0
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 | 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: 0
mysql> alter table test_kl add index name_name1_index (name,name1) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> 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: 0
mysql> 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';更多文章请微信搜寻公众号 < 老 A 技术联盟 > 或拜访博主网站易查网
正文完
