当我们对Mysql 中的表进行大批量的分组，查询、 创建的时候经常会出现这个问题，他翻译过来大概是这个意思，总数已经超过锁定表的大小。一般出现这个原因会有两种可能： 1.MySQL的临时表过小也就是tmp_table_size tmp_table_size：它规定了内部内存临时表的最大值，每个线程都要分配。（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在指定的tmpdir目录下，默认如下图: 那么怎么改变临时表的大小呢？同样有两种方法 第一种是 使用命令行进行修改 先使用 show variables like "%tmp%";查看临时表的大小，我的是64M 然后使用SET GLOBAL tmp_table_size =1024*1024*1024;我是给了1个G 第一种重启电脑后默认的设置会还原，适合临时使用一次的用户。或者我没有找到怎么设置永久的方法，如果有小伙伴们发现请告诉我哦， 第二种是修改mysql 的配置文件 1.关闭mysql 服务，方式是我的电脑——》管理->服务-》找到mysql的服务选择关闭。 2.找到mysql 的配置文件的位置，在前面的章节中我已经详细的介绍了这么找到Linux或者windows配置文件链接如下： https://blog.csdn.net/dingweiye123/article/details/80991341 3.注意配置文件要备份然后找到 tmp_table_size直接修改成tmp_table_size=3G 4.重启服务 2.MySQL的innod缓冲池过小也就是innodb_buffer_pool_size 和修改tmp_table_size的大小一样分为两种办法 1.mysql命令行设置 show variables like "%_buffer%";查看大小 使用SET GLOBAL innodb_buffer_pool_size=67108864;（3*1024*1024*1024，不要忘记;号）设置如下 第二种一样是修改配置文件 1.关闭mysql 服务，方式是我的电脑——》管理->服务-》找到mysql的服务选择关闭。 2.找到mysql 的配置文件的位置，在前面的章节中我已经详细的介绍了这么找到Linux或者windows配置文件链接如下： https://blog.csdn.net/dingweiye123/article/details/80991341 3.修改 innodb_buffer_pool_size的值 innodb_buffer_pool_size=3G 4.重启服务 from:https://blog.csdn.net/dingweiye123/article/details/80994576
View Details脚本 SET dbhost=127.0.0.1 SET dbuser=root SET dbpasswd=xxxx SET dbName=dbnamexx SET sqlpath=./ set sqlfile=statistics_fee_data_sync.sql mysql -h%dbhost% -u%dbuser% -p%dbpasswd% --database=%dbName% < %sqlpath%%sqlfile% --default-character-set=utf8 @ECHO Done! ========================================================================================= 语句 SET dbhost=127.0.0.1 SET dbuser=root SET dbpasswd=xxxx SET dbName=dbnamexx SET sqlpath=./ set sqlfile=statistics_fee_data_sync.sql mysql -h%dbhost% -u%dbuser% -p%dbpasswd% %dbName% -e "select * from cityinfo2;select * from correct2 limit 100;"
View Details在mysql中我们可以直接进行一些参数设置让它成定时为我们执行一些任务了，这个虽然可以使用windows或者linux中的计划任务实现，但是mysql本身也能完成 查看event是否开启 复制代码 代码如下: show variables like '%sche%'; 将事件计划开启 复制代码 代码如下: set global event_scheduler =1; 创建存储过程test 复制代码 代码如下: CREATE PROCEDURE test () BEGIN update examinfo SET endtime = now() WHERE id = 14; END; 创建event e_test 复制代码 代码如下: create event if not exists e_test on schedule every 30 second on completion preserve do call test(); 每隔30秒将执行存储过程test,将当前时间更新到examinfo表中id=14的记录的endtime字段中去. 关闭事件任务 复制代码 代码如下: alter event e_test ON COMPLETION PRESERVE DISABLE; 开户事件任务 复制代码 代码如下: alter event e_test ON COMPLETION PRESERVE ENABLE; 以上测试均成功,测试环境为mysql 5.4.2-beta-community mysql community server(GPL) 以上的相关内容就是对MySQL定时执行的介绍，望你能有所收获。 mysql 计划任务重启后消失 我们只要修改一配置即可 event_scheduler在mysql的config中设置为OFF有关。去mysql中将配置改为ON则就搞定了。 更详细的大家可以往下看 MySQL5.1.x版本中引入了一项新特性EVENT，顾名思义就是事件、定时任务机制，在指定的时间单元内执行特定的任务，因此今后一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。 要查看当前是否已开启事件调度器，可执行如下SQL： SHOW VARIABLES LIKE […]
View Details需求背景：在任务管理系统中，有任务详情表，每个任务下又分子任务节点，每个任务节点都有具体的跟进日期，包括开始时间结束时间，每天的任务完成进度。 有这样一个需求：在任务管理系统中根据任务节点id，展示每天的进度情况。 因为每个任务的时间范围不一样，所以需要用到动态的行转列，将时间周期作为动态的列，进度做为列的值。 任务节点表： quest_node_id：任务子节点id，follow_date:跟进日期 ，grade:进度百分比。 在mysql中创建存储过程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
BEGIN SET @EE=''; SELECT b.* into @EE from ( SELECT @EE:=CONCAT(@EE,'SUM(IF(follow_date=\'',follow_date,'\'',',grade,0)) AS ',follow_date,',') col FROM (SELECT DISTINCT follow_date FROM quest_follow_copy) A) b ORDER BY b.col desc limit 0,1 ; SET @QQ=CONCAT('SELECT quest_follow_copy.quest_node_id AS columnA,',LEFT(@EE,LENGTH(@EE)-1),' FROM quest_follow_copy GROUP BY quest_node_id'); PREPARE stmt2 FROM @QQ; EXECUTE stmt2; END |
注意：我发现follow_date的日期格式（如2018-08-10）会报错，存储过程执行不了，并且数字开头也不可以，所以我把日期改成了varchar类型，并在值里加了前缀。 执行结果如下： 某个节点在某天没有值则补零。 参考文档：mysql 动态行转列 from:https://blog.csdn.net/xiaoxiangzi520/article/details/81480477
View Details创建数据库、表
|
1 2 3 4 5 6 7 8 |
create database tests; use tests; create table t_score( id int primary key auto_increment, name varchar(20) not null, #名字 Subject varchar(10) not null, #科目 Fraction double default 0 #分数 ); |
添加数据
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
INSERT INTO `t_score`(name,Subject,Fraction) VALUES ('王海', '语文', 86), ('王海', '数学', 83), ('王海', '英语', 93), ('陶俊', '语文', 88), ('陶俊', '数学', 84), ('陶俊', '英语', 94), ('刘可', '语文', 80), ('刘可', '数学', 86), ('刘可', '英语', 88), ('李春', '语文', 89), ('李春', '数学', 80), ('李春', '英语', 87); |
方式一:使用if
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
select name as 名字 , sum(if(Subject='语文',Fraction,0)) as 语文, sum(if(Subject='数学',Fraction,0))as 数学, sum(if(Subject='英语',Fraction,0))as 英语, round(AVG(Fraction),2) as 平均分, SUM(Fraction) as 总分 from t_score group by name union select name as 名字 , sum(语文) Chinese,sum(数学) Math,sum(英语) English,round(AVG(总分),2)as 平均分,sum(总分) score from( select 'TOTAL' as name, sum(if(Subject='语文',Fraction,0)) as 语文, sum(if(Subject='数学',Fraction,0))as 数学, sum(if(Subject='英语',Fraction,0))as 英语, SUM(Fraction) as 总分 from t_score group by Subject )t |
方式二:使用case
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
select name as Name, sum(case when Subject = '语文' then Fraction end) as Chinese, sum(case when Subject = '数学' then Fraction end) as Math, sum(case when Subject = '英语' then Fraction end) as English, sum(fraction)as score from t_score group by name UNION ALL select name as Name,sum(Chinese) as Chinese,sum(Math) as Math,sum(English) as English,sum(score) as score from( select 'TOTAL' as name, sum(case when Subject = '语文' then Fraction end) as Chinese, sum(case when Subject = '数学' then Fraction end) as Math, sum(case when Subject = '英语' then Fraction end) as English, sum(fraction)as score from t_score group by Subject)t |
方法三: with rollup
|
1 2 3 4 5 6 7 |
select ifnull(name,'TOll') name, sum(if(Subject='语文',Fraction,0)) as 语文, sum(if(Subject='英语',Fraction,0)) as 英语, sum(if(Subject='数学',Fraction,0))as 数学, sum(Fraction) 总分 from t_score group by name with rollup |
from:https://www.cnblogs.com/weibanggang/p/9679301.html
View Details同事MM之前分享了数据库分区的知识，之前没认真研究过，结果现在自己的数据库资料越来越多，让强迫症的我特别难受，所以就对着同事写的SOP一步一步操作 。这里只介绍Range分区，因为我也是刚用到这一个 . 举个例子：要求将数据库dbemployeeinfo_copy 按ID分区，<3的为一个区，其它的为另一个区.，之后取消分区 原Table 的ID栏位为int格式： 实际生成的table在文件中的存储格式： 具体步骤如下： 1. 进入该table设置页，点击Partioning进行分区： 2.查看C盘该table位置，会发现原来的table已经分成两部分了，之后再增加资料，Part0的大小都不会发生变化了。 有时候会发现当初没考虑好，所以分区分错了，需要重新设置，所以第一反应就是把之前在Partitioning里设置的全部取消，然后提交就好了，但是会发现时无法提交的 这时候只能用指令解决了：alter table test.dbemployeeinfo_copy remove partitioning; 再去检查一下C盘对应文件夹里该table的资料，就没有分区了 from:https://blog.csdn.net/feelingdu/article/details/81914641
View Details一、全局配置 （1）max_connections 最大连接数。默认值是151，最多2000。如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量。但是如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值。 查看最大连接数
|
1 |
mysql> SHOW VARIABLES LIKE 'max_connections'; |
查看响应的连接数
|
1 |
mysql> SHOW STATUS LIKE 'max%connections'; |
max_used_connections / max_connections * 100% （理想值≈85%） 如果max_used_connections跟max_connections相同 那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。 （2）back_log MySQL能暂存的连接数量，默认值是80，最多512，可设置为128。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log。如果等待连接的数量超过back_log，将不被授予连接资源。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用。 （3）key_buffer_size 索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。 通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。
|
1 2 3 4 5 6 7 |
mysql> SHOW STATUS LIKE 'key_read%'; +-------------------+----------+ | Variable_name | Value | +-------------------+----------+ | Key_read_requests | 90585564 | | Key_reads | 97031 | +-------------------+----------+ |
计算索引未命中缓存的概率： key_cache_miss_rate = Key_reads / Key_read_requests * 100%，设置在1/1000左右较好 key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。 默认配置数值是8388608(8M)，主机有4GB内存，可改为268435456(256M) （4）query_cache_size 使用查询缓存(query cache)，MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。 最佳选项是将其从一开始就停用，设为0（现在MySQL 5.6的默认值）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如Redis或Memcached）。 通过检查状态值qcache_*，可以知道query_cache_size设置是否合理
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> SHOW STATUS LIKE 'qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031360 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 10302865 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+----------+ |
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% 如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。 查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100% 查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小，要不就是碎片太多。 查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100% 如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。 与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。 query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。 query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。 query_cache_min_res_unit指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query_cache_min_res_unit。 （5）read_buffer_size 是MySQL读入缓冲区的大小，将对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区，read_buffer_size变量控制这一缓冲区的大小，如果对表的顺序扫描非常频繁，并你认为频繁扫描进行的太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。 默认数值是131072(128K)，可改为16773120(16M) （6）read_rnd_buffer_size 随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。 默认数值是262144(256K)，可改为16777208(16M) （7）sort_buffer_size 每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。 默认数值是10485760(1M)，可改为16777208(16M) （8）join_buffer_size 联合查询操作所能使用的缓冲区大小 read_buffer_size，read_rnd_buffer_size，sort_buffer_size，join_buffer_size为每个线程独占，也就是说，如果有100个线程连接，则占用为16M*100 […]
View Details一、概念 1.为什么要分表和分区？ 日常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。 2.什么是分表？ 分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。 3.什么是分区？ 分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。 4.mysql分表和分区有什么联系呢？ （1）都能提高mysql的性高，在高并发状态下都有一个良好的表现。 （2）分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式（如果merge这种分表方式，不能和分区配合的话，可以用其他的分表试），访问量不大，但是表数据很多的表，我们可以采取分区的方式等。 （3）分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。 （4）表分区相对于分表，操作方便，不需要创建子表。 二、分区 1.分区的类型： （1）Range：把连续区间按范围划分 例：
|
1 2 3 4 5 6 7 8 9 10 11 |
create table user( id int(11), money int(11) unsigned not null, date datetime ) partition by range(YEAR(date))( partition p2014 values less than (2015), partition p2015 values less than (2016), partition p2016 values less than (2017), partition p2017 values less than maxvalue ); |
（2）List：把离散值分成集合，按集合划分，适合有固定取值列的表 例：
|
1 2 3 4 5 6 7 8 |
create table user( a int(11), b int(11) ) partition by list(b)( partition p0 values in (1,3,5,7,9), partition p1 values in (2,4,6,8,0) ); |
（3）Hash：随机分配，分区数固定 例：
|
1 2 3 4 5 6 |
create table user( a int(11), b datetime ) partition by hash(YEAR(b)) partitions 4; |
（4）Key：类似Hash，区别是只支持1列或多列,且mysql提供自身的Hash函数 例：
|
1 2 3 4 5 6 |
create table user( a int(11), b datetime ) partition by key(b) partitions 4; |
2.分区管理 （1）新增分区
|
1 2 |
ALTER TABLE sale_data ADD PARTITION (PARTITION p201710 VALUES LESS THAN (201711)); |
（2）删除分区
|
1 2 |
--当删除了一个分区，也同时删除了该分区中所有的数据。 ALTER TABLE sale_data DROP PARTITION p201710; |
（3）分区的合并 下面的SQL，将p201701 – p201709 合并为3个分区p2017Q1 – p2017Q3
|
1 2 3 4 5 6 7 8 9 |
ALTER TABLE sale_data REORGANIZE PARTITION p201701,p201702,p201703, p201704,p201705,p201706, p201707,p201708,p201709 INTO ( PARTITION p2017Q1 VALUES LESS THAN (201704), PARTITION p2017Q2 VALUES LESS THAN (201707), PARTITION p2017Q3 VALUES LESS THAN (201710) ); |
3.分区应该注意的事项： （1）做分区时，要么不定义主键，要么把分区字段加入到主键中。 （2）分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL 三、分表 1.垂直分表 把原来有很多列的表拆分成多个表，原则是： （1）把常用、不常用的字段分开放 （2）把大字段独立存放在一个表中 2.水平分表 为了解决单表数据量过大的问题，每个水平拆分表的结构完全一致。 例： （1）按时间结构 如果业务系统对时效性较高，比如新闻发布系统的文章表，可以把数据库设计成时间结构，按时间分有几种结构： （a）平板式 表类似：
|
1 2 3 |
article_201701 article_201702 article_201703 |
用年来分还是用月可自定，但用日期的话表就太多了，也没这必要。一般建议是按月分就可以。 这种分法，其难处在于，假设我要列20条数据，结果这三张表里都有2条，那么业务上很有可能要求读三次表。如果时间长了，有几十张表，而每张表是0条，那不就是要读完整个系统的表才行么?另外这个结构，要作分页是比较难实现的。 主键：在这个系统中，主键是13位带毫秒的时间戳，不要用自动编号，否则难以通过主键定位到表，也可以在查询时带上时间，但比较烦琐。 （b）归档式 表类似：
|
1 2 |
article_old article_new |
为了解决平板式的缺点，可以采用时间归档式设计，可以看到这个系统只有两张表。一张是旧文章表，一张是新文章表，新文章表放2个月的信息，每天定期把2 个月中的最早一天的文章归入旧表中。这样一方面可以解决性能问题，因为一般新闻发布系统读取的都是新的内容，旧的内容读取少;第二可以委婉地解决功能问 题，比如平板式所说的问题，在归档式中最多也只需要读2张表就完成了。 归档式的缺点在于旧表容量还是相对比较大，如果业务允许，可对旧表中的超旧内容进行再归档或直接清理掉。 （2）按版块结构 如果按照文章的所属版块进行拆表，比如新闻、体育版块拆表，一方面可以使每个表数据量分离，另一方面是各版块之间相互影响可降到最低。假如新闻版块的数据表损坏或需要维护，并不会影响到体育版块的正常工作，从而降低了风险。版块结构同时常用于bbs这样的系统。 板块结构也有几种分法： （a）对应式 对于版块数量不多，而且较为固定的形式，就直接对应就好。比如新闻版块，可以分出新闻的目录表，新闻的文章表等。
|
1 2 3 4 |
news_category news_article sports_category sports_article |
可看到每一个版块都对应着一组相同的表结构，好处就是一目了然。在功能上，因为版块之间还是有一些隔阂，所以需要联合查询的需求不多，开发上比时间结构的方式要轻松。 主键：依旧要考虑的，在这个系统中，主键是版块+时间戳，单纯的时间戳或自动编号也能用，查询时要记得带上版块用于定位表。 （b）冷热式 对应式的缺点是，如果版块数量很大而且不确定，那要分出的表数量就太多了。举个例子：百度贴吧，如果按一个词条一个表设计，那得有多少张表呢? 用这样的方式吧。
|
1 2 3 4 |
tieba_汽车 tieba_飞机 tieba_火箭 tieba_unite |
这个表汽车、火箭表是属于热门表，定义为新建的版块放在unite表里面，待到其超过一万张主贴的时候才开对应表结构。因为在贴吧这种系统中，冷门版块 肯定比热门版块多得多，这些冷门版块通常只有几张帖子，为它们开表也太浪费了;同时热门版块数量和访问量等，又比冷门版块多得多，非常有特点。 unite表还可以扩展成哈希表，利用词条的md5编码，可以分成n张表，我算了一下，md5前一位可分36张表，两位即是1296张表，足够了。
|
1 2 |
tieba_unite_ab tieba_unite_ac |
（3）按哈希结构 哈希结构通常用于博客之类的基于用户的场合，在博客这样的系统里有几个特点，1是用户数量非常多，2是每个用户发的文章数量都较少，3是用户发文章不定 期，4是每个用户发得不多，但总量仍非常之大。基于这些特点，用以上所说的任何一种分表方式都不合适，一没有固定的时效不宜用时间拆，二用户很多，而且还 偏偏都是冷门，所以也不宜用版块(用户)拆。 哈希结构在上面有所提及，既然按每个用户不好直接拆，那就把一群用户归进一个表好了。
|
1 2 3 |
blog_aa blog_ab blog_ac |
[…]
View Details一、SQL语句优化 （1）使用limit对查询结果的记录进行限定 （2）避免select *，将需要查找的字段列出来 （3）使用连接（join）来代替子查询 （4）拆分大的delete或insert语句 二、选择合适的数据类型 （1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob （2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数 （3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar （4）尽可能使用not null定义字段 （5）尽量少用text，非用不可最好分表 三、选择合适的索引列 （1）查询频繁的列，在where，group by，order by，on从句中出现的列 （2）where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列 （3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好 （4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高：
|
1 |
mysql> SELECT COUNT(DISTINCT column_name) FROM table_name; |
四、使用命令分析 （1）SHOW查看状态 1.显示状态信息
|
1 |
mysql> SHOW [SESSION|GLOBAL] STATUS LIKE '%Status_name%'; |
session（默认）：取出当前窗口的执行 global：从mysql启动到现在 （a）查看查询次数（插入次数com_insert、修改次数com_insert、删除次数com_delete）
|
1 |
mysql> SHOW STATUS LIKE 'com_select'; |
（b）查看连接数(登录次数)
|
1 |
mysql> SHOW STATUS LIKE 'connections'; |
（c）数据库运行时间
|
1 |
mysql> SHOW STATUS LIKE 'uptime'; |
（d）查看慢查询次数
|
1 |
mysql> SHOW STATUS LIKE 'slow_queries'; |
（e）查看索引使用的情况：
|
1 |
mysql> SHOW STATUS LIKE 'handler_read%'; |
handler_read_key：这个值越高越好，越高表示使用索引查询到的次数。 handler_read_rnd_next：这个值越高，说明查询低效。 2.显示系统变量
|
1 |
mysql> SHOW VARIABLES LIKE '%Variables_name%'; |
3.显示InnoDB存储引擎的状态
|
1 |
mysql> SHOW ENGINE INNODB STATUS; |
（2）EXPLAIN分析查询
|
1 |
mysql> EXPLAIN SELECT column_name FROM table_name; |
explain查询sql执行计划，各列含义： table：表名； type：连接的类型 -const：主键、索引； -eq_reg：主键、索引的范围查找； -ref：连接的查找（join） -range：索引的范围查找； -index：索引的扫描； -all：全表扫描； possible_keys：可能用到的索引； key：实际使用的索引； key_len：索引的长度，越短越好； ref：索引的哪一列被使用了，常数较好； rows：mysql认为必须检查的用来返回请求数据的行数； extra：using filesort、using temporary（常出现在使用order by时）时需要优化。 -Using filesort 额外排序。看到这个的时候，查询就需要优化了 -Using temporary 使用了临时表。看到这个的时候，也需要优化 （3）PROFILING分析SQL语句 1.开启profile。查看当前SQL执行时间
|
1 2 |
mysql> SET PROFILING=ON; mysql> SHOW profiles; |
2.查看所有用户的当前连接。包括执行状态、是否锁表等
|
1 |
mysql> SHOW processlist; |
（4）PROCEDURE ANALYSE()取得建议 通过分析select查询结果对现有的表的每一列给出优化的建议
|
1 |
mysql> SELECT column_name FROM table_name PROCEDURE ANALYSE(); |
（5）OPTIMIZE TABLE回收闲置的数据库空间
|
1 |
mysql> OPTIMIZE TABLE table_name; |
[…]
View Details