选项配置
- 中文乱码
①配置字符集
View Code[client] default-character-set=utf8 [mysqld] character_set_server=utf8 character_set_client=utf8
在 MySQL 中查看字符集，字符集跟当前环境有关
View Codemysql> use db01 Database changed mysql> show variables like '%character%'; +--------------------------+------------------------------------------------------------+ | Variable_name | Value | +--------------------------+------------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /mysql/mysql-5.5.62-linux-glibc2.12-x86_64/share/charsets/ | +--------------------------+------------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> use db02 Database changed mysql> show variables like '%character%'; +--------------------------+------------------------------------------------------------+ | Variable_name | Value | +--------------------------+------------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /mysql/mysql-5.5.62-linux-glibc2.12-x86_64/share/charsets/ | +--------------------------+------------------------------------------------------------+ 8 rows in set (0.00 sec)
先建库，修改配置文件时，继续乱码原因。配置了不能解决老库问题。只能解决之后建库的问题。
View Codemysql> show create database db01; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | db01 | CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show create database db03; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | db03 | CREATE DATABASE `db03` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec)
②在不做如上配置时，亦能正确插入中文。
View Codemysql> create database db04 character set utf8; Query OK, 1 row affected (0.00 sec) mysql> use db04 Database changed mysql> show variables like '%character%'; +--------------------------+------------------------------------------------------------+ | Variable_name | Value | +--------------------------+------------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /mysql/mysql-5.5.62-linux-glibc2.12-x86_64/share/charsets/ | +--------------------------+------------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> create table user -> ( -> name varchar(20) -> ); Query OK, 0 rows affected (0.00 sec) mysql> insert into user values('好的'); Query OK, 1 row affected (0.00 sec) mysql> select * from user; +--------+ | name | +--------+ | 好的 | +--------+ 1 row in set (0.00 sec) mysql>
③老库问题
View Code#修改表的字符集 mysql> show create table user; +-------+------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------+ | user | CREATE TABLE `user` ( `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table user convert to character set utf8; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table user; +-------+----------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------+ | user | CREATE TABLE `user` ( `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) #修改数据库字符集 mysql> show create database db01; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | db01 | CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter database db01 character set = utf8; Query OK, 1 row affected (0.00 sec) mysql> show create database db01; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | db01 | CREATE DATABASE `db01` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>
- 索引（检索 + 排序）
创建索引语法
View CodeCREATE [ONLINE | OFFLINE] [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [index_type] ON tbl_name (key_part,...) [index_option] ... key_part: col_name [(length)] [ASC | DESC] index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' index_type: USING {BTREE | HASH}
修改索引语法
View CodeALTER [ONLINE|OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] [partition_options] alter_specification: ADD {INDEX|KEY} [index_name] [index_type] (key_part,...) [index_option] ... | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...) [index_option] ... | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (key_part,...) [index_option] ... | ADD FULLTEXT [INDEX|KEY] [index_name] (key_part,...) [index_option] ... | ADD SPATIAL [INDEX|KEY] [index_name] (key_part,...) [index_option] ... | DROP {INDEX|KEY} index_name key_part: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH} index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string'
删除索引语法
View CodeDROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name
查看索引语法
View CodeSHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
①适合建索引的情况：数据量大、主外键、巡查频繁的字段（单值索引）、where中常常组合的查询条件（组合索引）、排序的字段、统计分组字段
②不适合创建的情况：数据量少、uid操作多的字段、冗余多字段
逻辑架构
- 客户端
jdbc、java、python - 连接层
连接池
：客户端和连接层，完成连接处理、授权认证 - 服务层
SQL解析器、缓存
：sql分析和优化 - 数据库引擎
Memory、InnoDB、MyISAM
：InnoDB，支持事务、行级锁、支持外键、缓存索引和数据
：MyISAM，表锁、仅缓存索引
：Memory，基于 hash表 的内存存储引擎 - 存储层
文件系统交互
Explain
- 解析查询语句
View Code
mysql> explain select empno,deptno from emp; +----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ | 1 | SIMPLE | emp | index | NULL | deptno | 5 | NULL | 14 | Using index | +----+-------------+-------+-------+---------------+--------+---------+------+------+-------------+ row in set (0.00 sec) mysql> explain SELECT * FROM emp e JOIN dept d ON e.`deptno` = d.`deptno`; +----+-------------+-------+------+---------------+--------+---------+---------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+---------------+------+-------------+ | 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 4 | | | 1 | SIMPLE | e | ref | deptno | deptno | 5 | hope.d.deptno | 2 | Using where | +----+-------------+-------+------+---------------+--------+---------+---------------+------+-------------+ rows in set (0.01 sec) mysql> explain select ename,sal from emp e join dept d on e.deptno = d.deptno where e.deptno = '30'; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | d | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | | 1 | SIMPLE | e | ref | deptno | deptno | 5 | const | 6 | Using where | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ 2 rows in set (0.00 sec) mysql> explain select ename,sal from emp e join dept d on e.deptno = d.deptno where d.dname = 'sales'; +----+-------------+-------+------+---------------+--------+---------+---------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+---------------+------+-------------+ | 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where | | 1 | SIMPLE | e | ref | deptno | deptno | 5 | hope.d.deptno | 2 | Using where | +----+-------------+-------+------+---------------+--------+---------+---------------+------+-------------+ 2 rows in set (0.00 sec) mysql> explain select empno,deptno from emp where empno = '7698'; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | emp | const | PRIMARY | PRIMARY | 4 | const | 1 | | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ row in set (0.00 sec) mysql> explain select empno,deptno from emp where empno = '7698' and deptno = '30'; +----+-------------+-------+-------+----------------+---------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+---------+---------+-------+------+-------+ | 1 | SIMPLE | emp | const | PRIMARY,deptno | PRIMARY | 4 | const | 1 | | +----+-------------+-------+-------+----------------+---------+---------+-------+------+-------+ row in set (0.00 sec) mysql> explain select empno,deptno from emp where deptno = '30'; +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | 1 | SIMPLE | emp | ref | deptno | deptno | 5 | const | 6 | Using where; Using index | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ row in set (0.00 sec) mysql> explain select empno,deptno from emp where deptno in '30'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''30'' at line 1 mysql> explain select empno,deptno from emp where deptno in ('30'); +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ | 1 | SIMPLE | emp | ref | deptno | deptno | 5 | const | 6 | Using where; Using index | +----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+ row in set (0.00 sec) mysql> explain select empno,deptno from emp where deptno in ('30','20'); +----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp | range | deptno | deptno | 5 | NULL | 11 | Using where; Using index | +----+-------------+-------+-------+---------------+--------+---------+------+------+--------------------------+ row in set (0.00 sec) mysql> explain select empno,deptno from emp where sal = '3000'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> show index in emp; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | emp | 0 | PRIMARY | 1 | empno | A | 14 | NULL | NULL | | BTREE | | | | emp | 1 | deptno | 1 | deptno | A | 7 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ rows in set (0.00 sec) mysql> show index in dept; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | dept | 0 | PRIMARY | 1 | deptno | A | 4 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ row in set (0.00 sec) mysql>
id：执行顺序
select_type：查询类型，SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT
table：使用的表、衍生表
type：执行效率级别，system、const、eq_ref、ref、range、index、ALL
possible_keys：理论上用到的索引
key：实际上用到的索引
key_len：使用到索引的字节数
ref：用到了哪些索引
rows：预计检索数据的行数
Extra：综合得分， Using filesort（文件内排序）、Using temporary（创建了临时表）、Using index（用到了覆盖索引）
Index优化
- 单表查询：管理岗位薪资过2600，按薪资排序
View Code
mysql> explain select ename,job from emp where job = 'manager' and sal > 2600 order by sal desc; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> create index js on emp(job,sal); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select ename,job from emp where job = 'manager' and sal > 2600 order by sal desc; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | range | js | js | 38 | NULL | 2 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql>
为条件判断创建组合索引。
单表三个条件查询：管理岗位薪资过2600，提成最少的
View Codemysql> drop index js on emp; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index jsc on emp(job,sal,comm); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select ename,job,sal,comm from emp where job = 'manager' and sal > 2600 order by comm asc limit 1; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | emp | range | jsc | jsc | 38 | NULL | 2 | Using where; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> drop index jsc on emp; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index jc on emp(job,comm); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select ename,job,sal,comm from emp where job = 'manager' and sal > 2600 order by comm asc limit 1; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | emp | ref | jc | jc | 33 | const | 3 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql>
优化的过程中，出现了索引失效。
- 双表连接
没有关联表主键的情况下，肯定就ALL了，存在外键关联时，效果如下
View Codemysql> show index from emp; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | emp | 0 | PRIMARY | 1 | empno | A | 14 | NULL | NULL | | BTREE | | | | emp | 1 | deptno | 1 | deptno | A | 7 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> show index from dept; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | dept | 0 | PRIMARY | 1 | deptno | A | 4 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec) mysql> explain select ename,dname from emp e left join dept d on e.deptno = d.deptno; +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | hope.e.deptno | 1 | | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ 2 rows in set (0.00 sec) mysql> desc emp; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | empno | int(10) unsigned | NO | PRI | NULL | auto_increment | | ename | varchar(15) | YES | | NULL | | | job | varchar(10) | YES | | NULL | | | mgr | int(10) unsigned | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(7,2) | YES | | NULL | | | comm | decimal(7,2) | YES | | NULL | | | deptno | int(10) unsigned | YES | MUL | NULL | | +----------+------------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) mysql>
重新建表，不要外键，但是两个表都有主键。连接时链接到主键上。
View Codemysql> CREATE TABLE `dept2` ( `deptno` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '部门编号', `dname` varchar(15) DEFAULT NULL COMMENT '部门名称', `loc` varchar(50) DEFAULT NULL COMMENT '部门所在位置', PRIMARY KEY (`deptno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'; mysql> insert into `dept2`(`deptno`,`dname`,`loc`) values (10,'ACCOUNTING','NEW YORK'),(20,'RESEARCH','DALLAS'),(30,'SALES','CHICAGO'),(40,'OPERATIONS','BOSTON'); mysql> CREATE TABLE `emp2` ( `empno` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '雇员编号', `ename` varchar(15) DEFAULT NULL COMMENT '雇员姓名', `job` varchar(10) DEFAULT NULL COMMENT '雇员职位', `mgr` int(10) unsigned DEFAULT NULL COMMENT '雇员对应的领导的编号', `hiredate` date DEFAULT NULL COMMENT '雇员的雇佣日期', `sal` decimal(7,2) DEFAULT NULL COMMENT '雇员的基本工资', `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金', `deptno` int(10) unsigned DEFAULT NULL COMMENT '所在部门', PRIMARY KEY (`empno`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='雇员表'; mysql> insert into `emp2`(`empno`,`ename`,`job`,`mgr`,`hiredate`,`sal`,`comm`,`deptno`) values (7369,'SMITH','CLERK',7902,'1980-12-17','800.00',NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20','1600.00','300.00',30),(7521,'WARD','SALESMAN',7698,'1981-02-22','1250.00','500.00',30),(7566,'JONES','MANAGER',7839,'1981-04-02','2975.00',NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28','1250.00','1400.00',30),(7698,'BLAKE','MANAGER',7839,'1981-05-01','2850.00',NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09','2450.00',NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-07-13','3000.00',NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17','5000.00',NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08','1500.00','0.00',30),(7876,'ADAMS','CLERK',7788,'1987-07-13','1100.00',NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03','950.00',NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03','3000.00',NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23','1300.00',NULL,10); mysql> explain select ename,dname from emp2 e left join dept2 d on e.deptno = d.deptno; +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | hope.e.deptno | 1 | | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ 2 rows in set (0.00 sec) mysql>
但是，无外键，左右表对调位置，奇迹就出现了（外连接位置与性能有关，内连接无关）
View Codemysql> explain select ename,dname from dept2 d left join emp2 e on e.deptno = d.deptno; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | d | ALL | NULL | NULL | NULL | NULL | 4 | | | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 2 rows in set (0.00 sec) mysql> explain select ename,dname from emp2 e left join dept2 d on e.deptno = d.deptno; +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | hope.e.deptno | 1 | | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ 2 rows in set (0.00 sec) mysql> explain select ename,dname from emp2 e inner join dept2 d on e.deptno = d.deptno; +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | | | 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ 2 rows in set (0.00 sec) mysql> explain select ename,dname from dept2 d inner join emp2 e on e.deptno = d.deptno; +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | | | 1 | SIMPLE | d | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where; Using join buffer | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+ 2 rows in set (0.00 sec)
-
多表连接
不测试内连接，仍然只使用主键不使用外键（salgrade默认没有主键，%……）
View Codemysql> explain select ename,dname,grade from emp2 e left join dept2 d on e.deptno = d.deptno left join salgrade g on e.sal between g.losal and g.hisal; +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | hope.e.deptno | 1 | | | 1 | SIMPLE | g | ALL | NULL | NULL | NULL | NULL | 5 | | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ 3 rows in set (0.00 sec) mysql> create index lh on salgrade(losal,hisal); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select ename,dname,grade from emp2 e left join dept2 d on e.deptno = d.deptno left join salgrade g on e.sal between g.losal and g.hisal; +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | hope.e.deptno | 1 | | | 1 | SIMPLE | g | ALL | lh | NULL | NULL | NULL | 5 | | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------+ 3 rows in set (0.00 sec) mysql> create index glh on salgrade(grade,losal,hisal); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select ename,dname,grade from emp2 e left join dept2 d on e.deptno = d.deptno left join salgrade g on e.sal between g.losal and g.hisal; +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+ | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | hope.e.deptno | 1 | | | 1 | SIMPLE | g | index | lh | glh | 15 | NULL | 5 | Using index | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+ 3 rows in set (0.00 sec) mysql> drop index lh on salgrade; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select ename,dname,grade from emp2 e left join dept2 d on e.deptno = d.deptno left join salgrade g on e.sal between g.losal and g.hisal; +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+ | 1 | SIMPLE | e | ALL | NULL | NULL | NULL | NULL | 14 | | | 1 | SIMPLE | d | eq_ref | PRIMARY | PRIMARY | 4 | hope.e.deptno | 1 | | | 1 | SIMPLE | g | index | NULL | glh | 15 | NULL | 5 | Using index | +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+ 3 rows in set (0.00 sec) mysql>
- 明确字段
字段明确且恰好属于索引项目
View Codemysql> create index jn on emp2(job,ename); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from emp2 where job = 'manager'; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jn | jn | 33 | const | 3 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) #结果刚好是索引项，取值从索引中获取 mysql> explain select ename,job from emp2 where job = 'manager'; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | jn | jn | 33 | const | 3 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) #结果属于索引项，取值从索引中获取 mysql> explain select ename from emp2 where job = 'manager'; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | jn | jn | 33 | const | 3 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select ename,job,sal from emp2 where job = 'manager'; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jn | jn | 33 | const | 3 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
- 索引失效
①没有索引“一号位”，索引失效
View Codemysql> create index njm on emp2(ename,job,mgr); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from emp2 where ename = 'king'; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | emp2 | ref | njm | njm | 48 | const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from emp2 where ename = 'king' and job = 'manger'; +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | njm | njm | 81 | const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from emp2 where ename = 'king' and job = 'manger' and mgr is NULL; +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | njm | njm | 86 | const,const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ 1 row in set (0.00 sec) #没有了索引1号位，索引失效 mysql> explain select * from emp2 where job = 'manger' and mgr is NULL; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) #没有了索引1号位，索引失效 mysql> explain select * from emp2 where mgr is NULL; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) #索引1号位有，生效 mysql> explain select * from emp2 where job = 'manger' and ename = 'king' and mgr is NULL; +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | njm | njm | 86 | const,const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ 1 row in set (0.00 sec) #索引1号位有，生效 mysql> explain select * from emp2 where job = 'manger' and mgr is NULL and ename = 'king'; +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | njm | njm | 86 | const,const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ 1 row in set (0.00 sec)
复合索引中项目有顺序，中间断了后边不生效。
②索引不支持 数学运算、函数运算、比较运算，后边不生效
③使用 模糊查询，非确定字符开头，后边不生效
④使用 is，后边不生效
⑤交叉索引
查询字段与where判断字段不在同一个索引
View Codemysql> show index from emp2; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | emp2 | 0 | PRIMARY | 1 | empno | A | 14 | NULL | NULL | | BTREE | | | | emp2 | 1 | njm | 1 | ename | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | njm | 2 | job | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | njm | 3 | mgr | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | sc | 1 | sal | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | sc | 2 | comm | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | jn | 1 | job | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | jn | 2 | ename | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | cs | 1 | comm | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | cs | 2 | sal | A | 14 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 10 rows in set (0.00 sec) mysql> explain select ename 'manager' from emp2 where job = 'manager' and sal > 2600; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | emp2 | ref | sc,jn | jn | 33 | const | 3 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,job from emp2 where job = "manager" or job = "clerk"; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | range | jn | jn | 33 | NULL | 7 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select ename,job from emp2 where sal > 2600; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | ALL | sc | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select sal,comm from emp2 where sal > 2600; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | range | sc | sc | 5 | NULL | 5 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal,comm from emp2 where sal > 2600; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | ALL | sc | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql>
- 覆盖索引
查询的字段属于索引项目
View Codemysql> show index from emp2; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | emp2 | 0 | PRIMARY | 1 | empno | A | 14 | NULL | NULL | | BTREE | | | | emp2 | 1 | jn | 1 | job | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | jn | 2 | ename | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | hs | 1 | hiredate | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | hs | 2 | sal | A | 14 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 rows in set (0.00 sec) mysql> explain select * from emp2 where job in ('manager','clerk'); +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | ALL | jn | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from emp2 where job = 'manager' or job = 'clerk'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | ALL | jn | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) #使用覆盖索引 mysql> explain select ename,job from emp2 where job in ('manager','clerk'); +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | range | jn | jn | 33 | NULL | 7 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) #使用覆盖索引 mysql> explain select ename,job from emp2 where job = 'manager' or job = 'clerk'; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | range | jn | jn | 33 | NULL | 7 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec)
- 以上索引失效的说法，验证后边索引失效（索引连续）
目标在索引内：
View Codemysql> show index from emp2; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | emp2 | 0 | PRIMARY | 1 | empno | A | 14 | NULL | NULL | | BTREE | | | | emp2 | 1 | jmsd | 1 | job | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | jmsd | 2 | mgr | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | jmsd | 3 | sal | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | jmsd | 4 | deptno | A | 14 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 rows in set (0.00 sec) mysql> explain select job from emp2 where job='clerk'; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 33 | const | 4 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select job from emp2 where job='clerk' and mgr = 7902; +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 38 | const,const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select job from emp2 where job='clerk' and mgr = 7902 and sal = 800; +----+-------------+-------+------+---------------+------+---------+-------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 43 | const,const,const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select job from emp2 where job='clerk' and mgr = 7902 and sal = 800 and deptno = 20; +----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 48 | const,const,const,const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+ 1 row in set (0.00 sec) #没有索引的首项，索引照样生效；目标字段在索引项中； mysql> explain select job from emp2 where mgr = 7902 and sal = 800 and deptno = 20; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | index | NULL | jmsd | 48 | NULL | 14 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select job from emp2 where job='clerk' and sal = 800 and deptno = 20; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 33 | const | 4 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select job from emp2 where job='clerk' and mgr = 7902 and deptno = 20; +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 38 | const,const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ 1 row in set (0.00 sec) ############################################### ##### 一个字段排序的情况 mysql> explain select job from emp2 where mgr = 7902 and sal = 800 and deptno = 20 order by job; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | index | NULL | jmsd | 48 | NULL | 14 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select job from emp2 where job='clerk' and sal = 800 and deptno = 20 order by mgr; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 33 | const | 4 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select job from emp2 where job='clerk' and mgr = 7902 and deptno = 20 order by sal; +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 38 | const,const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select job from emp2 where job='clerk' and mgr = 7902 and sal = 800 order by deptno; +----+-------------+-------+------+---------------+------+---------+-------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 43 | const,const,const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------------+------+--------------------------+ 1 row in set (0.00 sec) ############################################### ##### 一个字段有大小比较 mysql> explain select job from emp2 where job like 'c%' and mgr = 7902 and sal = 800 and deptno = 20; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | range | jmsd | jmsd | 48 | NULL | 4 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select job from emp2 where job='clerk' and mgr > 7000 and sal = 800 and deptno = 20; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | range | jmsd | jmsd | 38 | NULL | 4 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select job from emp2 where job='clerk' and mgr = 7902 and sal > 700 and deptno = 20; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | range | jmsd | jmsd | 43 | NULL | 1 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select job from emp2 where job='clerk' and mgr = 7902 and sal = 800 and deptno in (20); +----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 48 | const,const,const,const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select job from emp2 where job='clerk' and mgr = 7902 and sal = 800 and deptno > 10; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | range | jmsd | jmsd | 48 | NULL | 1 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ 1 row in set (0.01 sec)
目标在索引外：
View Codemysql> show index from emp2; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | emp2 | 0 | PRIMARY | 1 | empno | A | 14 | NULL | NULL | | BTREE | | | | emp2 | 1 | jmsd | 1 | job | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | jmsd | 2 | mgr | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | jmsd | 3 | sal | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | jmsd | 4 | deptno | A | 14 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 rows in set (0.00 sec) mysql> explain select ename from emp2 where job='clerk'; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 33 | const | 4 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename from emp2 where job='clerk' and mgr = 7902; +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 38 | const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) mysql> mysql> explain select ename from emp2 where job='clerk' and mgr = 7902 and sal = 800; +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 43 | const,const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename from emp2 where job='clerk' and mgr = 7902 and sal = 800 and deptno = 20; +----+-------------+-------+------+---------------+------+---------+-------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 48 | const,const,const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+-------------+ 1 row in set (0.01 sec) #没有了首个索引项 mysql> explain select ename from emp2 where mgr = 7902 and sal = 800 and deptno = 20; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) #没有第二个索引项 mysql> explain select ename from emp2 where job='clerk' and sal = 800 and deptno = 20; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 33 | const | 4 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) #没有第三个索引项 mysql> explain select ename from emp2 where job='clerk' and mgr = 7902 and deptno = 20; +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 38 | const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) #首个索引项用来排序而不是比较 #排序字段引入，产生文件内排序 mysql> explain select ename from emp2 where mgr = 7902 and sal = 800 and deptno = 20 order by job; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | emp2 | ALL | NULL | NULL | NULL | NULL | 14 | Using where; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> explain select ename from emp2 where job='clerk' and sal = 800 and deptno = 20 order by mgr; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 33 | const | 4 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.01 sec) mysql> explain select ename from emp2 where job='clerk' and mgr = 7902 and deptno = 20 order by sal; +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 38 | const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.01 sec) mysql> explain select ename from emp2 where job='clerk' and mgr = 7902 and sal = 800 order by deptno; +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 43 | const,const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ 1 row in set (0.00 sec) ############################################### ##### 一个字段有大小比较 mysql> explain select ename from emp2 where job like 'c%' and mgr = 7902 and sal = 800 and deptno = 20; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | range | jmsd | jmsd | 48 | NULL | 4 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename from emp2 where job='clerk' and mgr > 7000 and sal = 800 and deptno = 20; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | range | jmsd | jmsd | 38 | NULL | 4 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename from emp2 where job='clerk' and mgr = 7902 and sal > 700 and deptno = 20; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | range | jmsd | jmsd | 43 | NULL | 1 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> mysql> explain select ename from emp2 where job='clerk' and mgr = 7902 and sal = 800 and deptno in (20); +----+-------------+-------+------+---------------+------+---------+-------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 48 | const,const,const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename from emp2 where job='clerk' and mgr = 7902 and sal = 800 and deptno > 10; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | range | jmsd | jmsd | 48 | NULL | 1 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
目标交叉索引：
View Codemysql> show index from emp2; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | emp2 | 0 | PRIMARY | 1 | empno | A | 14 | NULL | NULL | | BTREE | | | | emp2 | 1 | jmsd | 1 | job | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | jmsd | 2 | mgr | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | jmsd | 3 | sal | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | jmsd | 4 | deptno | A | 14 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 rows in set (0.00 sec) mysql> explain select ename,sal from emp2 where job='clerk'; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 33 | const | 4 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal from emp2 where job='clerk' and mgr = 7902; +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 38 | const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal from emp2 where job='clerk' and mgr = 7902 and sal = 800; +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 43 | const,const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal from emp2 where job='clerk' and mgr = 7902 and sal = 800 and deptno = 20; +----+-------------+-------+------+---------------+------+---------+-------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 48 | const,const,const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+-------------+ 1 row in set (0.00 sec) #???? mysql> explain select ename,sal from emp2 where mgr = 7902 and sal = 800 and deptno = 20; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal from emp2 where job='clerk' and sal = 800 and deptno = 20; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 33 | const | 4 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal from emp2 where job='clerk' and mgr = 7902 and deptno = 20; +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 38 | const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) ###### order by ###### mysql> explain select ename,sal from emp2 where mgr = 7902 and sal = 800 and deptno = 20 order by job; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | emp2 | ALL | NULL | NULL | NULL | NULL | 14 | Using where; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ 1 row in set (0.01 sec) mysql> explain select ename,sal from emp2 where job='clerk' and sal = 800 and deptno = 20 order by mgr; +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 33 | const | 4 | Using where | +----+-------------+-------+------+---------------+------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal from emp2 where job='clerk' and mgr = 7902 and deptno = 20 order by sal; +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 38 | const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal from emp2 where job='clerk' and mgr = 7902 and sal = 800 order by deptno; +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 43 | const,const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal from emp2 where job like 'c%' and mgr = 7902 and sal = 800 and deptno = 20; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | range | jmsd | jmsd | 48 | NULL | 4 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal from emp2 where job='clerk' and mgr > 7000 and sal = 800 and deptno = 20; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | range | jmsd | jmsd | 38 | NULL | 4 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal from emp2 where job='clerk' and mgr = 7902 and sal > 700 and deptno = 20; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | range | jmsd | jmsd | 43 | NULL | 1 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal from emp2 where job='clerk' and mgr = 7902 and sal = 800 and deptno in (20); +----+-------------+-------+------+---------------+------+---------+-------------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+-------------+ | 1 | SIMPLE | emp2 | ref | jmsd | jmsd | 48 | const,const,const,const | 1 | Using where | +----+-------------+-------+------+---------------+------+---------+-------------------------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select ename,sal from emp2 where job='clerk' and mgr = 7902 and sal = 800 and deptno > 10; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | range | jmsd | jmsd | 48 | NULL | 1 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
- order by 可能会产生的“文件内排序”
原则思路：尽量使用index排序，避免使用filesort排序。
覆盖索引情况下（使用*未产生文件内排序，因为只有三列，主键和索引2两列）
View Code##### dept2表 mysql> show index from dept2; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | dept2 | 0 | PRIMARY | 1 | deptno | A | 4 | NULL | NULL | | BTREE | | | | dept2 | 1 | nl | 1 | dname | A | 4 | NULL | NULL | YES | BTREE | | | | dept2 | 1 | nl | 2 | loc | A | 4 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ rows in set (0.00 sec) mysql> explain select * from dept2 order by dname; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | dept2 | index | NULL | nl | 201 | NULL | 4 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ row in set (0.00 sec) mysql> explain select * from dept2 order by dname desc; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | dept2 | index | NULL | nl | 201 | NULL | 4 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ row in set (0.00 sec) #索引首项是常量，order by可以从loc开始 mysql> explain select * from dept2 where dname = 'sales' order by loc; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | dept2 | ref | nl | nl | 48 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select * from dept2 order by loc; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | dept2 | index | NULL | nl | 201 | NULL | 4 | Using index; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ row in set (0.00 sec) mysql> explain select * from dept2 order by loc,dname; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | dept2 | index | NULL | nl | 201 | NULL | 4 | Using index; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ row in set (0.00 sec) #同时升序 mysql> explain select * from dept2 order by dname,loc; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | dept2 | index | NULL | nl | 201 | NULL | 4 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ row in set (0.00 sec) #一降一升 mysql> explain select * from dept2 order by dname desc,loc ; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | dept2 | index | NULL | nl | 201 | NULL | 4 | Using index; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ row in set (0.00 sec) #同时降序 mysql> explain select * from dept2 order by dname desc,loc desc; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | dept2 | index | NULL | nl | 201 | NULL | 4 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ row in set (0.00 sec) #一升一降 mysql> explain select * from dept2 order by dname,loc desc; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | dept2 | index | NULL | nl | 201 | NULL | 4 | Using index; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+ row in set (0.00 sec) ##### emp2表 mysql> show index from emp2; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | emp2 | 0 | PRIMARY | 1 | empno | A | 14 | NULL | NULL | | BTREE | | | | emp2 | 1 | nhs | 1 | ename | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | nhs | 2 | hiredate | A | 14 | NULL | NULL | YES | BTREE | | | | emp2 | 1 | nhs | 3 | sal | A | 14 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ rows in set (0.00 sec) mysql> explain select ename,hiredate,sal from emp2 order by ename; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp2 | index | NULL | nhs | 57 | NULL | 14 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ row in set (0.00 sec) mysql> explain select ename,hiredate,sal from emp2 where sal > 1000 order by ename; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | index | NULL | nhs | 57 | NULL | 14 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ row in set (0.00 sec) #索引首项为常量，order by可以从第二项开始。不至于产生文件内排顺 mysql> explain select ename,hiredate,sal from emp2 where ename = 'scott' order by hiredate; +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | nhs | nhs | 48 | const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) #索引首项、第二项为常量，order by可以从第三项开始。不至于产生文件内排顺 mysql> explain select ename,hiredate,sal from emp2 where ename = 'scott' and hiredate = '1987-07-13' order by sal; +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ | 1 | SIMPLE | emp2 | ref | nhs | nhs | 52 | const,const | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------+------+--------------------------+ 1 row in set (0.00 sec) #同时升序 mysql> explain select ename,hiredate,sal from emp2 where sal > 1000 order by ename,hiredate; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | index | NULL | nhs | 57 | NULL | 14 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ row in set (0.00 sec) #一降一升 mysql> explain select ename,hiredate,sal from emp2 where sal > 1000 order by ename desc,hiredate; +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ | 1 | SIMPLE | emp2 | index | NULL | nhs | 57 | NULL | 14 | Using where; Using index; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ row in set (0.00 sec) #一升一降 mysql> explain select ename,hiredate,sal from emp2 where sal > 1000 order by ename,hiredate desc; +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ | 1 | SIMPLE | emp2 | index | NULL | nhs | 57 | NULL | 14 | Using where; Using index; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ row in set (0.00 sec) #同时降序 mysql> explain select ename,hiredate,sal from emp2 where sal > 1000 order by ename desc,hiredate desc; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | index | NULL | nhs | 57 | NULL | 14 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ row in set (0.00 sec) #索引跳项 mysql> explain select ename,hiredate,sal from emp2 where sal > 1000 order by ename,sal; +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ | 1 | SIMPLE | emp2 | index | NULL | nhs | 57 | NULL | 14 | Using where; Using index; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ row in set (0.00 sec) #降降降 mysql> explain select ename,hiredate,sal from emp2 where sal > 1000 order by ename desc,hiredate desc,sal desc; +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | emp2 | index | NULL | nhs | 57 | NULL | 14 | Using where; Using index | +----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+ row in set (0.00 sec) #降降升 mysql> explain select ename,hiredate,sal from emp2 where sal > 1000 order by ename desc,hiredate desc,sal asc; +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ | 1 | SIMPLE | emp2 | index | NULL | nhs | 57 | NULL | 14 | Using where; Using index; Using filesort | +----+-------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+ row in set (0.00 sec) #不再是覆盖索引查询 mysql> explain select ename,job,hiredate,sal from emp2 where sal > 1000 order by ename; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | emp2 | ALL | NULL | NULL | NULL | NULL | 14 | Using where; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ row in set (0.00 sec) #不再是覆盖索引查询，降降降 mysql> explain select ename,job,hiredate,sal from emp2 where sal > 1000 order by ename desc,hiredate desc,sal desc; +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ | 1 | SIMPLE | emp2 | ALL | NULL | NULL | NULL | NULL | 14 | Using where; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+ row in set (0.00 sec)
filesort排序算法：
①双路排序，有两次磁盘扫描，得到结果。
4之前采用：按照order by排序，排序在buffer中进行；按照目标列从排序结果获取最终结果
②单路排序，从磁盘获取所有需要的列，在buffer中排序，输出最终结果
③单路排序优化：配置选项：sort-buffer-size，每线程默认2M。
View Codemysql> show variables like '%sort_%'; +---------------------------+---------------------+ | Variable_name | Value | +---------------------------+---------------------+ | max_length_for_sort_data | 1024 | | max_sort_length | 1024 | | myisam_max_sort_file_size | 9223372036853727232 | | myisam_sort_buffer_size | 8388608 | | sort_buffer_size | 2097152 | +---------------------------+---------------------+ 5 rows in set (0.00 sec) mysql> show variables like 'sort_buffer_size'; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | sort_buffer_size | 2097152 | +------------------+---------+ 1 row in set (0.00 sec)
- group by
同order by
show profiles
- 慢查询
“慢查询”相关选项：slow-query-log、slow-query-log-file、long-query-time（秒）
View Codemysql> show variables like 'slow_%'; +---------------------+-----------------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------------+ | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /mysql/mysql/data/mysqlAdvance-slow.log | +---------------------+-----------------------------------------+ rows in set (0.00 sec) mysql> show variables like 'long_%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ row in set (0.00 sec)
开启慢查询记录：慢查询像错误日志一样属于全局配置
View Code#慢查询日志阀值 mysql> set global long_query_time=2; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'long_%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ row in set (0.00 sec) mysql> show global variables like 'long_%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 2.000000 | +-----------------+----------+ row in set (0.00 sec) mysql> set long_query_time=2; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'long_%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 2.000000 | +-----------------+----------+ row in set (0.00 sec) #打开开关，默认关闭；不建议生产环境开启 mysql> set slow_query_log = on; ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL mysql> set global slow_query_log = on; Query OK, 0 rows affected (0.06 sec) mysql> show variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | ON | +----------------+-------+ row in set (0.00 sec) mysql> show global variables like 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | ON | +----------------+-------+ row in set (0.00 sec)
分析日志工具的使用：
View Code[root@mysqlAdvance data]# mysqldumpslow --help Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --help write this text to standard output -v verbose -d debug -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time [root@mysqlAdvance data]# mysqldumpslow *.log
- 开启功能
View Code
mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ row in set (0.00 sec) mysql> set profiling=on; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | ON | +---------------+-------+ row in set (0.00 sec) mysql>
MySQL .