mysql截取查询数据_MySQL查询截取分析
⼀、查询优化
1，mysql的调优⼤纲
mysql删除重复的数据保留一条慢查询的开启并捕获
explain+慢SQL分析
show profile查询SQL在Mysql服务器⾥⾯的执⾏细节和⽣命周期情况
SQL数据库服务器的参数调优
2，⼩表驱动⼤表
mysql的join实现原理是，以驱动表的数据为基础，“嵌套循环”去被驱动表匹配记录。驱动表的索引会失效，⽽被驱动表的索引有效。
#假设 a表10000数据，b表20数据select * from a join b on a.bid =b.id
a表驱动b表为:for20条数据
匹配10000数据(根据on a.bid=b.id的连接条件，进⾏B+树查)
查次数为:20+ log10000
b表驱动a表为for10000条数据
匹配20条数据(根据on a.bid=b.id的连接条件，进⾏B+树查)
查次数为:10000+ log20
3，in和exists
exists的使⽤
EXISTS 语法：EXISTS(subquery) 只返回TRUE或FALSE，因此⼦查询中的SELECT *也可以是SELECT 1或其他，官⽅说法是实际执⾏时会忽略SELECT清单，因此没有区别
SELECT ... FROM table WHERE EXISTS(subquery)
该语法可以理解为：将查询的数据，放到⼦查询中做条件验证，根据验证结果(TRUE或FALSE)来决定主查询的数据结果是否得以保留。
EXISTS⼦查询的实际执⾏过程可能经过了优化⽽不是我们理解上的逐条对⽐，如果担忧效率问题，可进⾏实际检验以确定是否有效率问题。
EXISTS⼦查询往往也可以⽤条件表达式、其他⼦查询或者JOIN来替代，何种最优需要具体问题具体分析
#采⽤in则是，内表B驱动外表Aselect * from A where id in (select id fromB)
#采⽤exists则是，外表A驱动内表Bselect * from A where exists(select 1 from B where B.id = A.id)
结论：
永远记住⼩表驱动⼤表
当 B 表数据集⼩于 A 表数据集时，使⽤ in
当 A 表数据集⼩于 B 表数据集时，使⽤ exist
4，order by
创建表
create tabletblA(
#idint primary key not nullauto_increment,
ageint,
birthtimestamp not null);insert into tblA(age, birth) values(22, now());insert into tblA(age, birth) values(23, now());insert into tblA(age, birth) values(24, now());
#创建复合索引create index idx_A_ageBirth on tblA(age, birth);
order by命中索引的情况
order by未命中索引的情况
结论：
MySQL⽀持两种排序⽅式：Using index和Using filesort。filesort效率较低，⽽要使⽤index⽅式排序需满⾜两种使⽤条件尽可能在索引列上完成排序操作，遵照索引的最佳左前缀
order by语句⾃⾝使⽤索引的最左前列
使⽤where⼦句与order by⼦句条件列组合满⾜最左前列
如果order by不在索引列上，会使⽤filesort算法：双路排序和单路排序
MySQL4.1之前是使⽤双路排序，字⾯意思是两次扫描磁盘，最终得到数据。读取⾏指针和order by列，对他们进⾏排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进⾏排序，然后扫描排序后的列表进⾏输出，它的效率更快⼀些，避免了第⼆次读取数据，并且把随机IO变成顺序IO，但是它会使⽤更多的空间，因为它把每⼀⾏都保存在内存中了。
select * from user where name = "zs" order byage
#双路排序1)从 name 到第⼀个满⾜ name = 'zs'的主键id2)根据主键 id 取出整⾏，把排序字段 age 和主键 id 这两个字段放到 sort buffer(排序缓存) 中3)从name 取下⼀个满⾜ name = 'zs'记录的主键 id4)重复 2、3 直到不满⾜ name = 'zs'
5)对 sort_buffer 中的字段 age 和主键 id 按照字段 age进⾏排序6)遍历排序好的 id 和字段 age ，按照 id 的值回到原表中取出 所有字段的值返回给客户端
#单路排序1)从name到第⼀个满⾜ name ='zs'条件的主键 id2)根据主键 id 取出整⾏，取出所有字段的值，存⼊ sort_buffer(排序缓存)中3)从索引name到下⼀个满⾜ name = 'zs'条件的主键 id4)重复步骤 2、3 直到不满⾜ name = 'zs'
5)对 sort_buffer 中的数据按照字段 age 进⾏排序，返回结果给客户端
单路排序的问题及优化
问题：
由于单路是改进的算法，总体⽽⾔好过双路
在sort_buffer中，⽅法B⽐⽅法A要多占⽤很多空间，因为⽅法B是把所有字段都取出，所以有可能取出的数据的总⼤⼩超出了
sort_buffer的容量，导致每次只能取sort_buffer容量⼤⼩的数据，进⾏排序(创建tmp⽂件，多路合并)，排完再取取sort_buffer容量⼤⼩，再排…… 从⽽会导致多次I/O。
优化策略：
增⼤sort_buffer_size参数的设置
增⼤max_length_for_sort_data参数的设置
注意事项:
Order by时select*是⼀个⼤忌，只Query需要的字段。因为字段越多在内存中存储的数据也就也多，这样就导致每次I/O能加载的数据列越少。
5，group by优化
1)groupby实质是先排序后进⾏分组，遵照索引的最佳左前缀
2)当⽆法使⽤索引列，增⼤max_length_for_sort_data参数的设置+增⼤sort_buffer_size参数的设置
3)where⾼于having，能写在where限定的条件就不要去having限定了
4)其余的规则均和order by ⼀致
⼆、慢查询⽇志
1，慢查询⽇志是什么?
MySQL的慢查询⽇志是MySQL提供的⼀种⽇志记录，它⽤来记录在MySQL中响应时间超过阀值的语句，具体指运⾏时间超过
long_query_time值的SQL，则会被记录到慢查询⽇志中。
long_query_time的默认值为10，意思是运⾏10秒以上的SQL语句会被记录下来
由他来查看哪些SQL超出了我们的最⼤忍耐时间值，⽐如⼀条sql执⾏超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进⾏全⾯分析。
2，慢查询⽇志的开启
默认情况下，MySQL的慢查询⽇志是没有开启的。如果不是调优需要的话，⼀般不建议启动该参数，因为开启慢查询⽇志会影响到性能，慢查询⽇志⽀持将⽇志记录写⼊⽂件。
a)开启慢查询⽇志
#查看是否开启慢⽇志
show variables like'slow_query_log%';
#开启慢查询⽇志，想要永久有效在myf中设置set global slow_query_log = 1;
b)设置慢查询⽇志的阈值
#查看慢查询⽇志的阈值时间 默认为10s
show variables like'long_query_time%';
#设置为3s 重启失效，想要永久有效在myf中设置set global long_query_time = 3#再次查看，需要切换窗⼝查看
show variables like'long_query_time%';
c)持久化慢查询⽇志和时间阈值
[mysqld]
#持久化慢查询⽇志
slow_query_log=1；
slow_query_log_file=/var/lib/mysql/hadoop102-slow.log
long_query_time=3；
log_output=FILE
d)慢查询案例
#查询等待4sselect sleep(4);
#在linux系统中，查看慢查询⽇志
cat/var/lib/mysql/hadoop102-slow.log
e)查看当前系统中存在的慢查询⽇志条数
show global status like '%Slow_queries%';
3，⽇志分析命令mysqldumpslow
a)参数解释
-
s：是表⽰按何种⽅式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
-t：即为返回前⾯多少条的数据
-g：后边搭配⼀个正则匹配模式，⼤⼩写不敏感的
b)常⽤⽅法
#得到返回记录集最多的10个SQL
mysqldumpslow-s r -t 10 /var/lib/mysql/hadoop102-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow-s c -t 10 /var/lib/mysql/hadoop102-slow.log
#得到按照时间排序的前10条⾥⾯含有左连接的查询语句
mysqldumpslow-s t -t 10 -g "left join" /var/lib/mysql/hadoop102-slow.log
#这些命令时结合|和more使⽤
mysqldumpslow-s r -t 10 /var/lib/mysql/hadoop102-slow.log | more
三、批量写数据脚本
1，建表
CREATE TABLEdept
(
deptnoint unsigned primary keyauto_increment,
dnamevarchar(20) not null default '',
locvarchar(8) not null default '')ENGINE=INNODB DEFAULT CHARSET=utf8;CREATE TABLEemp

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。