一、explain详解
1.explain语法
mysql> explain select * from user where age='30' and money > '1000000' and look='beautiful';
#1.查看中国和美国的城市人口数量
mysql> select name,population from city where countrycode='CHN' or countrycode='USA';
mysql> select name,population from city where countrycode in ('CHN','USA');
mysql> select name,population from city where countrycode='CHN' union all select name,population from city where countrycode='USA';
#2.查看三个命令的执行计划
mysql> explain select name,population from city where countrycode='CHN' or countrycode='USA';
mysql> explain select name,population from city where countrycode in ('CHN','USA');
mysql> explain select name,population from city where countrycode='CHN' union all select name,population from city where countrycode='USA';
#3.查询结果注释
id #执行顺序
table #查询的表
type #查询使用的类型
possible_keys #可能使用的索引列
key #真正实用的索引列
key_len #索引长度，前缀索引的长度
ref #查询级别是否达到ref级别
rows #查询数据的数量
Extra
Using temporary
Using filesort #使用了默认的文件排序（如果使用了索引，会避免这类排序）order by
Using join buffer #使用了 join on
Using index condition #使用了索引
2.group by扩展
#1.插入一个表
mysql> create table jixiao(id int,name varchar(10),money int,product varchar(10));
#2.插入数据
mysql> insert into jixiao values('1','邱导','100000','汽车'),('2','laocai','80000','汽车'),('3','dawei','700000','房地产'),('4','laozhao','800000','房地产');
#3.查询不同行业总绩效
mysql> select sum(money),product from jixiao group by product;
+------------+-----------+
| sum(money) | product |
+------------+-----------+
| 1500000 | 房地产 |
| 180000 | 汽车 |
+------------+-----------+
2 rows in set (0.00 sec)
#4.查询不同行业绩效最高的那个人
3.查询数据的方式
1）全表扫描
#1.什么是全表扫描？
读取整个表的数据，使用explain语句查询执行计划中，type列的值是ALL
#2.什么时候使用全表扫描
1）查询表中所有数据的时候
mysql> explain select * from city;
2）没有走索引的时候
mysql> explain select name,population from city where population='92020';
2）索引扫描
#从上到下查询速度依次越来越快
1.index #全索引扫描
mysql> explain select name from city;
2.range #范围查询使用该级别，但是当查询数据量过大的时候不走索引
mysql> explain select name,population from city where countrycode='CHN' or countrycode='USA';
mysql> explain select name,population from city where population > 3000000;
3.ref #使用精确查询
mysql> explain select name,population from city where countrycode='CHN';
4.eq_ref #使用join on时可能出现该级别
mysql> explain select city.name,city.population,country.name from country join city on city.countrycode=country.code where city.population < 100;
5.const #当查询条件是主键或者唯一键的时候
mysql> explain select * from city where id='1';
6.system #跟const平级，当查询的数据所在表数据量很小的时候，并且查询条件使用主键或者唯一键
7.null #当不用读取数据库数据的时候
mysql> explain select max(population) from city;
二、索引的建立
1.建立索引的原则
1.如果可以建立唯一键索引，就建立唯一键索引
2.为经常需要排序、分组和联合操作的字段建立索引
3.为常作为查询条件的字段建立索引
4.尽量使用前缀索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度
5.限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
6.删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
2.不走索引的情况总结
1）没有查询条件，或者查询条件没有索引
#查询所有数据mysql> explain select * from city;#删除索引，然后查询mysql> alter table city drop index District_key;mysql> explain select * from city where District='heilongjiang';
2）查询结果集是原表中的大部分数据，应该是15％以上
#表中数据一共4079，查询数据539条，走索引 13.2%mysql> explain select * from city where population > 500000;#表中数据一共4079，查询数据737条，不走索引 18%mysql> explain select * from city where population > 400000;
3）索引坏了
反复插入删除容易损坏索引
4）查询条件使用了运算符号
#运算符号如果在等号左边，则不走索引mysql> explain select * from city where id-1=2;#运算符号如果在等号右边，则走索引mysql> explain select * from city where id=2+1;
5）隐式转换
# 1.建表mysql> create table phone(id int,name varchar(10),number varchar(20));#2.建立索引mysql> alter table phone add unique key uni_key(number);#3.插入数据mysql> insert phone values(1,'警察局',110),(2,'消防',119),(3,'医院',120);#4.测试查询数据是否走索引1）不走索引mysql> explain select * from phone where number=120;2）走索引mysql> explain select * from phone where number='120';#因为120存到数据库中的字段是字符类型，那么查询时字符类型必须加引号
6）使用 like + % 的模糊匹配，当条件以%开头时
#1. % 在最前面时不走索引mysql> explain select * from city where countrycode like '%H';mysql> explain select * from city where countrycode like '%H%';#2. % 在后面时走索引mysql> explain select * from city where countrycode like 'H%';#3. % 在中间时也走索引mysql> select * from city where countrycode like 'C%N';
7）联合索引，插叙条件不包含建立联合索引排第一的字段时
#0.查看联合索引mysql> show index from user;+-------+------------+-----------+--------------+-------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name |+-------+------------+-----------+--------------+-------------+| user | 1 | index_all | 1 | sex || user | 1 | index_all | 2 | age || user | 1 | index_all | 3 | money || user | 1 | index_all | 4 | look |+-------+------------+-----------+--------------+-------------+#1.只要包含排第一的字段条件，就走索引mysql> select * from user where sex='fmale' and age='30';mysql> explain select * from user where age='30' and money='100000000' and look='beautiful' and sex='fmale';#2.不包含建立联合索引排第一的字段时，不走索引mysql> explain select * from user where age='30' and money='100000000' and look='beautiful';
8） <> ，not in 不走索引
mysql> explain select * from phone where number not in (110);mysql> explain select * from phone where number <> '110';
三、MySQL存储引擎
1.连接层
2.sql层
3.存储引擎层
1）接收sql层传来的sql语句
2）与磁盘交互获取数据
#存储引擎就是mysql的文件系统
1.MySQL提供的存储引擎
1）MySQL 提供以下存储引擎:
1.InnoDB
数据经常添加、删除、修改、查询，使用该引擎
2.MyISAM
只对数据进行查询和添加
3.MEMORY
支持hash索引
4.ARCHIVE
5.FEDERATED
6.EXAMPLE
7.BLACKHOLE
8.MERGE
9.NDBCLUSTER
10.CSV
2）第三方存储引擎:
1.MySQL当中插件式的存储引擎类型
2.MySQL的两个分支
1）perconaDB
2）mariaDB
3）查看存储引擎
#查看当前MySQL支持的存储引擎类型
mysql> show engines
#查看innodb的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';
#查看myisam的表有哪些
mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';
#查看某个表的信息
mysql> select * from tables where TABLE_NAME='city'\G
*************************** 1. row ***************************
TABLE_CATALOG: def #表的注册信息
TABLE_SCHEMA: world #表所在的库
TABLE_NAME: city #表名字
TABLE_TYPE: BASE TABLE #表的类型
ENGINE: InnoDB #存储引擎
VERSION: 10 #默认版本
ROW_FORMAT: Compact #行模式
TABLE_ROWS: 4188 #查询数据的量
AVG_ROW_LENGTH: 97 #行数据的平均长度
DATA_LENGTH: 409600 #数据长度
MAX_DATA_LENGTH: 0 #最大数据长度
INDEX_LENGTH: 131072 #索引的数据长度
DATA_FREE: 0 #空间碎片
AUTO_INCREMENT: 4080 #自增属性的值到了多少
CREATE_TIME: 2020-10-28 18:27:42 #创建时间
UPDATE_TIME: NULL #修改时间
CHECK_TIME: NULL #检查时间
TABLE_COLLATION: latin1_swedish_ci #表的字符集
CHECKSUM: NULL #检查次数
CREATE_OPTIONS: #建表的参数
TABLE_COMMENT: #表的注释
1 row in set (0.00 sec)
2.innodb和myisam的物理区别
#myisam存储引擎的文件
-rw-rw---- 1 mysql mysql 10684 10月 19 17:09 user.frm #表结构
-rw-rw---- 1 mysql mysql 728 10月 23 20:02 user.MYD #数据库的用户密码
-rw-rw---- 1 mysql mysql 2048 10月 27 08:51 user.MYI #数据库的用户
#innodb存储引擎的文件
-rw-rw---- 1 mysql mysql 8710 10月 28 19:53 city.frm #表结构
-rw-rw---- 1 mysql mysql 2097152 10月 28 19:54 city.ibd #表数据
#查看文件
strings user.MYI
3.innodb的核心特性
MVCC #多版本并发控制
事务 #事务的特性
备份 #mysqldump xtrabackup
故障自动恢复 #CSR
