mysql数据库学习（二）

2022-01-29 06:42:09

 

结构表
mysql数据库学习（二）
CREATE TABLE `dm_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键‘,
  `phone` varchar(255) DEFAULT NULL COMMENT ‘手机号‘,
  `password` varchar(255) DEFAULT NULL COMMENT ‘密码‘,
  `wxUserId` varchar(100) DEFAULT NULL,
  `realName` varchar(255) DEFAULT NULL COMMENT ‘真实姓名‘,
  `nickName` varchar(255) DEFAULT NULL COMMENT ‘昵称‘,
  `sex` int(1) DEFAULT NULL COMMENT ‘性别(0:男,1:女)‘,
  `hobby` varchar(255) DEFAULT NULL COMMENT ‘兴趣爱好‘,
  `idCard` varchar(255) DEFAULT NULL COMMENT ‘身份证号‘,
  `birthday` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘生日‘,
  `createdTime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间‘,
  `updatedTime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改时间‘,
  PRIMARY KEY (`id`),
  KEY `idx_phone_realName_sex` (`phone`,`realName`,`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8 COMMENT=‘用户表‘

#===============================================
CREATE TABLE `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`),
  UNIQUE KEY `idx_city_name_unique` (`city_name`),
  KEY `idx_city_name` (`city_name`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
#===============================================

CREATE TABLE `city_innodb` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`),
  KEY `fk_city_country` (`country_id`),
  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country_innodb` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
#===============================================

CREATE TABLE `city_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `operation` varchar(20) NOT NULL COMMENT ‘操作类型：insert/update/delete‘,
  `operate_time` datetime NOT NULL COMMENT ‘操作时间‘,
  `operate_id` int(11) NOT NULL COMMENT ‘操作表ID‘,
  `operate_params` varchar(500) DEFAULT NULL COMMENT ‘操作参数‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
#===============================================
CREATE TABLE `country` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

#===============================================

CREATE TABLE `country_innodb` (
  `country_id` int(11) NOT NULL AUTO_INCREMENT,
  `country_name` varchar(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
#===============================================

CREATE TABLE `goods_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
View Code

 

一、MySQL的体系结构概览

mysql数据库学习（二）

mysql数据库学习（二）

二、存储引擎

mysql数据库学习（二）

 mysql数据库学习（二）

　　2.1、各种存储引擎的特性

mysql数据库学习（二）

 　　2.2、验证事务（开启事务后，手动提交commit,之后回到了默认自动提交的状态，所以之后再插入一条语句后没有commit，但是其他的会话也是会查询到的）

　　　　 验证外键

CREATE TABLE `country_innodb` (
  `country_id` INT(11) NOT NULL AUTO_INCREMENT,
  `country_name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`country_id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

CREATE TABLE `city_innodb` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_name` varchar(50) NOT NULL,
  `country_id` int(11) NOT NULL,
  PRIMARY KEY (`city_id`),
  KEY `fk_city_country` (`country_id`),
  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country_innodb` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

 mysql数据库学习（二）

 mysql数据库学习（二）

　　2.3、innoDB存储表和索引两种方式

mysql数据库学习（二）

　　2.4、MyISAM存储引擎（不支持事务，支持表锁，不支持外键）、文件结构如下

mysql数据库学习（二）

 

　　2.5、Memory存储引擎

 mysql数据库学习（二）

　　2.5、MERGE存储引擎

mysql数据库学习（二）

　　2.6、存储引擎的选择

mysql数据库学习（二）

 三、优化

 　　3.1、执行频率查询

　　　　>show global status like ‘Innodb_rows_%‘;#Innodb查询数据库

　　　　>show status like ‘Innodb_rows_%‘;#Innodb查询当前会话数据库

　　　　>show global status like ‘com_______‘;#7个占位符

　　　　>show status like ‘com_______‘;

　　　　>show databases;#查看当前SQL有多少数据库

　　　　>use dbname;#切换dbname

　　　　>select datdabase();#查看当前使用的数据库

mysql数据库学习（二）mysql数据库学习（二）

　　3.2、定位低效SQL的两种方式

mysql数据库学习（二）

mysql数据库学习（二）

　　3.3、explain分析执行计划

mysql数据库学习（二）

mysql数据库学习（二）

 　　　　3.3.1、select_type字段说明

mysql数据库学习（二）

mysql数据库学习（二）

　　　　3.3.2、type字段（通常我们需要保证查询至少达到range，最好达到ref）

mysql数据库学习（二）

　　　　3.3.3、key

mysql数据库学习（二）

 

 　　　　3.3.4、extra(出现using filesort和temporary需要考虑进行优化)

mysql数据库学习（二）

 　　3.4、show profile分析SQL语句

　　　　>select @@have_profiling;　　　　　 #查看是否支持profile

　　　　>select @@profiling;　　　　　　　　#默认profiling是关闭的，值为0

　　　　>set profiling=1;　　　　　　　　　　#开启

 

　　　　>show profile for query id;　　　　　  #

　　　　>show profile all  for query id;　　　　#展示所有信息

 mysql数据库学习（二）mysql数据库学习（二）

 

 　　3.5、trace工具

mysql数据库学习（二）

 

 四、索引的使用

　　索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题

　　4.1、避免索引失效

　　　　4.1.1、全值匹配，对索引中所有列都指定具体指，该情况下，索引生效，执行效率高 

CREATE INDEX idx_xxx ON your_table_name(table_col1,table_col2,table_col3);

SELECT *  FROM your_table_name WHERE table_col1=?  AND table_col2=?  AND table_col3=?;

 　　　　4.1.2、最左前缀法则。如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左列开始，并且不跳过索引中的列，才能走索引

　　　 4.1.3、范围查询右边的列不能用索引

　　　 4.1.4、不要在索引列上进行运算操作，否则索引失效

　　　 4.1.5、字符串不加单引号，导致索引失效

 

#从table_cols列上截取字符进行查询，table_cols索引将不起作用
SELECT *  FROM  you_tablename WHERE SUBSTRING(table_cols,3,2);
#字符串不加单引号，导致索引失效
SELECT *  FROM  you_tablename WHERE table_cols=x;
SELECT *  FROM  you_tablename WHERE table_cols=‘x‘;

mysql数据库学习（二）

 

mysql数据库学习（二）　　　

　　　　4.1.6、尽量使用覆盖索引，避免select *

mysql数据库学习（二）

 　　　　4.1.7、or索引失效，如果or前的条件的列有索引，但后面的列中没有索引，那么涉及的索引都不会被用到

mysql数据库学习（二）

　　　　4.1.8、like模糊匹配

 mysql数据库学习（二）

　　　　4.1.9、如果MySQL评估使用索引比全表更慢，则不使用索引

　　　　　　　 比如当一张表10条数据，某一字段（name）有九条数据都是一样的XX，另一条是YY，且这个字段是字段索引，

　　　　　　　 那么当查询name=XX时可能就不走索引，而name=YY又走索引

　　　　4.1.10、is NULL,is NOT NULL有时索引失效，当索引字段值大部分是NULL，那么is NULL全表扫描　　

　　　 4.1.11、in走索引,not in不走索引 

mysql数据库学习（二）

 　　　　4.1.12、单列索引和复合索引

　　　　　　　　尽量使用复合索引，少使用单列索引，当创建（name1+name2）索引，相当于创建了2个索引，name1,name1+name2

　　　　　　　　如果是单列索引，那么在查询条件上数据库只会选择一个最优的索引（最具辨识度）,

 　　　4.1.13、查看索引使用情况

　　　　　　　　>show status like ‘Handler_read%‘;#当前会话的索引使用情况

mysql数据库学习（二）

 mysql数据库学习（二）

五、SQL优化

　　5.1、大批量插入数据，使用load命令

　　　　5.1.1、逐渐顺序插入。Innodb是按照主键顺序保存的

　　　　5.1.2、关闭唯一性校验。在导入数据前如有唯一性校验，关闭>SET UNIQUE_CHECKS=0，在结束后再执行>ET UNIQUE_CHECKS=1恢复

　　　　5.1.3、手动提交事务。执行SET AUTOCOMMIT=0，关闭自动提交，结束后SET AUTOCOMMIT=1，也可提高导入效率

　　>LOAD DATA LOCAL INFILE ‘C://Users//Desktop//inserttest.log‘ INTO TABLE `dm_user` FIELDS TERMINATED BY ‘,‘ LINES 　　　　　　　　　　　　　　　　     TERMINATED BY ‘\n‘;

mysql数据库学习（二）
LOAD DATA  [LOW_PRIORITY] [LOCAL] INFILE ‘file_name txt‘ [REPLACE | IGNORE]
　　into TABLE tbl_name
　　fields
　　[TERMINATED BY‘t‘]
　　[OPTIONALLY] ENCLOSED BY ‘‘]
　　[ESCAPED BY‘\‘ ]]
　　[lines terminated by‘n‘]
　　[ignore number lines]
　　[(col_name,   )]



‘30‘,‘zhzbin@163.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘胖成球‘,‘胖成球1132‘,‘1‘,‘测bugasdasdasdasdasd水电费水电费爽肤水3测试数据的asdasd‘,‘8888888888888‘,‘2019-09-10 15:46:47‘,‘2018-05-24 11:30:49‘,‘2019-09-10 15:46:47‘
‘40‘,‘test1235@test.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘11‘,‘8888888888‘,‘1‘,‘字符串兴趣爱好1111‘,‘888888888‘,‘2018-10-09 06:29:05‘,‘2018-05-24 11:30:49‘,‘2018-10-09 06:29:05‘
‘50‘,‘test555@test.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘嘻嘻‘,‘呵呵列‘,NULL,‘字符串兴趣爱好‘,‘5555555‘,‘2018-10-09 06:29:05‘,‘2018-05-31 08:15:33‘,‘2018-10-09 06:29:05‘
‘60‘,‘test1@test.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘11‘,‘8888888888‘,‘1‘,‘字符串兴趣爱好1111‘,‘888888888‘,‘2018-10-09 06:29:05‘,‘2018-05-24 11:30:49‘,‘2018-10-09 06:29:05‘
‘70‘,‘test12@test.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘11‘,‘8888888888‘,‘1‘,‘字符串兴趣爱好1111‘,‘888888888‘,‘2018-10-09 06:29:05‘,‘2018-05-24 11:30:49‘,‘2018-10-09 06:29:05‘
‘80‘,‘test1234@test.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘11‘,‘8888888888‘,NULL,‘字符串兴趣爱好1111‘,‘888888888‘,‘2018-10-09 06:29:05‘,‘2018-05-31 08:15:33‘,‘2018-10-09 06:29:05‘
‘90‘,‘772602194@qq.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘丰碧琴‘,‘子爱‘,‘2‘,‘看书，打球，看小说\n看电影‘,‘340102199003076236‘,‘2018-10-09 06:29:05‘,‘2018-05-24 11:30:49‘,‘2018-10-09 06:29:05‘
‘100‘,‘491675128@163.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘迟骞信‘,‘友易‘,‘0‘,‘游泳，跑步‘,‘350102199103131433‘,‘2018-10-09 06:29:05‘,‘2018-05-24 11:30:49‘,‘2018-10-09 06:29:05‘
‘110‘,‘538015624@126.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘谏鸿禧‘,‘方方‘,‘0‘,‘旅游，散步‘,‘210102191007072616‘,‘2018-10-09 06:29:05‘,‘2018-05-31 08:15:33‘,‘2018-10-09 06:29:05‘
‘120‘,‘151902456@qq.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘窦彤云‘,‘开朗‘,‘1‘,‘打篮球，打扑克，下象棋‘,‘11010120100807189X‘,‘2018-10-09 06:29:05‘,‘2018-05-24 11:30:49‘,‘2018-10-09 06:29:05‘
‘130‘,‘893297013@qq.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘肇琰琬‘,‘安静‘,‘1‘,‘游泳，健身，散步，购物‘,‘320102199308189174‘,‘2018-10-09 06:29:05‘,‘2018-05-24 11:30:49‘,‘2018-10-09 06:29:05‘
‘140‘,‘wullove_12@126.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘贾思枫‘,‘旺旺‘,‘0‘,‘跑酷，玩电脑游戏，看小说，看电影‘,‘410102199506200516‘,‘2018-10-09 06:29:05‘,‘2018-05-31 08:15:33‘,‘2018-10-09 06:29:05‘
‘150‘,‘320538281@qq.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘由樱花‘,‘游客‘,‘1‘,‘看小说，看电影，散步‘,‘330102199110072239‘,‘2018-10-09 06:29:05‘,‘2018-05-31 08:15:33‘,‘2018-10-09 06:29:05‘
‘160‘,‘236392829@qq.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘乌海蓝‘,‘随风‘,‘0‘,‘看书，学习，直播‘,‘230102199409044570‘,‘2018-10-09 06:29:05‘,‘2018-05-24 11:30:49‘,‘2018-10-09 06:29:05‘
‘170‘,‘164319313@qq.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘赵恨瑶‘,‘流年‘,‘1‘,‘学习，游玩，散步，购物‘,‘360102199609126393‘,‘2018-10-09 06:29:05‘,‘2018-05-24 11:30:49‘,‘2018-10-09 06:29:05‘
‘180‘,‘cj88sdj@163.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘黄芳洁1‘,‘同桌的你‘,‘1‘,‘运动，洗衣服‘,‘310101199010184591‘,‘2018-10-09 06:29:05‘,‘2018-05-31 08:15:33‘,‘2018-10-09 06:29:05‘
‘190‘,‘zhzbin12@163.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘Test‘,‘胖成球‘,‘0‘,‘运动，洗衣服‘,‘310101199010184591‘,‘2018-10-09 06:29:05‘,‘2018-05-31 08:15:33‘,‘2018-10-09 06:29:05‘
‘200‘,‘sdodh366@163.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘董海‘,‘董海测试1‘,‘1‘,‘董海的爱好1‘,‘888888888888888‘,‘2018-10-09 06:29:05‘,‘2018-07-02 03:22:03‘,‘2018-10-09 06:29:05‘
‘620‘,‘test@163.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘oPxiwwqVIwYqkoRKmHeoDM3cDato‘,NULL,‘好心人‘,‘1‘,‘董海的爱好1‘,NULL,‘2018-10-09 06:29:05‘,‘2018-07-03 17:09:11‘,‘2018-10-09 06:29:05‘
‘630‘,‘sdodj366@163.com‘,‘e10adc3949ba59abbe56e057f20f883e‘,NULL,‘董海‘,‘董海测试1‘,‘1‘,‘谢谢小星星‘,‘111111111111111111‘,‘2018-10-09 06:29:05‘,‘2018-07-11 03:11:42‘,‘2018-10-09 06:29:05‘
View Code

 

　　>fields terminated by ‘,‘标示各字段以逗号隔开，各行以换行符隔开

　　5.2、insert语句优化　　

　　　　5.2.1、将多条加入语句合并 insert table_name values(),(),();

 

　　　 5.2.2、有序插入

 

　　　 5.2.3、手动提交事务

　　5.3、order by语句优化，要么都升序要么都降序

　　　　5.3.1、第一种排序是通过返回数据进行排序，也就是通常说的fileSort排序

　　　　5.3.1、第二种是通过有序索引顺序扫描直接返回有序数据，using index，不需要额外排序，操作效率高 

　　　　> show variables like ‘max_length_for_sort_data‘;

　　　　> show variables like ‘sort_buffer_size‘;

 mysql数据库学习（二）

 

 

 mysql数据库学习（二）

mysql数据库学习（二） 

 

 

mysql数据库学习（二）

上一篇：ThinkPad x1 carbon低配和联想小新pro14顶配版哪个好


下一篇：ThinkPad T400 笔记本详细拆机过程 清理风扇(图文教程)