什么是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的这些坑你踩过吗？快来看看怎么优化mysql？

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

# 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;
复制代码

mysql的这些坑你踩过吗？快来看看怎么优化mysql？

使用场景

mysql的这些坑你踩过吗？快来看看怎么优化mysql？

b-tree 索引原理示意图（二叉树为例）中序

mysql的这些坑你踩过吗？快来看看怎么优化mysql？

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 字符等等。

mysql的这些坑你踩过吗？快来看看怎么优化mysql？

- 我认为 合理表应该这样设计
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即可免费领取! ! !

mysql的这些坑你踩过吗？快来看看怎么优化mysql？