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
二、存储引擎
2.1、各种存储引擎的特性
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
2.3、innoDB存储表和索引两种方式
2.4、MyISAM存储引擎（不支持事务，支持表锁，不支持外键）、文件结构如下
2.5、Memory存储引擎
2.5、MERGE存储引擎
2.6、存储引擎的选择
三、优化
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();#查看当前使用的数据库
3.2、定位低效SQL的两种方式
3.3、explain分析执行计划
3.3.1、select_type字段说明
3.3.2、type字段（通常我们需要保证查询至少达到range，最好达到ref）
3.3.3、key
3.3.4、extra(出现using filesort和temporary需要考虑进行优化)
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; #展示所有信息
3.5、trace工具
四、索引的使用
索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的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';
4.1.6、尽量使用覆盖索引，避免select *
4.1.7、or索引失效，如果or前的条件的列有索引，但后面的列中没有索引，那么涉及的索引都不会被用到
4.1.8、like模糊匹配
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不走索引
4.1.12、单列索引和复合索引
尽量使用复合索引，少使用单列索引，当创建（name1+name2）索引，相当于创建了2个索引，name1,name1+name2
如果是单列索引，那么在查询条件上数据库只会选择一个最优的索引（最具辨识度）,
4.1.13、查看索引使用情况
>show status like 'Handler_read%';#当前会话的索引使用情况
五、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';
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'
>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';
