持续创作，加速成长！这是我参与「掘金日新计划 · 6 月更文挑战」的第7天，点击查看活动详情
一、mysql常用DDL和DML和DQL
1、DDL
（1）、存储过程命名：
1、获取数据库所有的存储过程：show procedure status
2、格式命名：
procedure 过程 + 表名+DML的操作方式【insert、update、select、delete】+定时的时间【minute、hour、day、mon】
proc_mew_w_sts_type_ins_day
proc_mew_w_sts_type_time_ins_mon
proc_ods_006_cd_trans_electronic_waybill_update_day
proc_mew_m_water_site_upd_hour
（2）、索引命名：
1、获取该数据库所有的索引：
select TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME) as INDEX_COLUMN
from
information_schema.statistics
where
table_schema='databaseName'
GROUP BY TABLE_NAME, INDEX_NAME;
2、获取该表所有索引：show index from tableName
3、格式命名：
格式为：index_columnName_columnName
索引前缀+列名+列名
例如：idx_vehicle_no
2、DQL
sql优化：dev.mysql.com/doc/refman/… sql执行顺序：from join on where groupby having select orderby limit 错误
正确：from on join where group by agg_func with/rollup/cube having select distinct orderby limit 正确
（1）、获取当天数据的写法-获取当天时间内的数据
select * from a
where create_time between curcate(curdate()," 00:00:00") and curcate(curdate()," 23:59:59")
#curcate(a,b)拼接a和b 为字符串
#curdate() 获取当前系统日期，没有时分秒
（2）、排序时将查询到的两个字段数值进行总量排序
（3）、FUNCTION smbms.CONCAT does not exist
错误原因有二：
1、函数用错了 例如concat()合并为字符串 写错成 cancat()
2、函数名和括号之间多了一个空格，例如concat() 写错成 concat ()
（4）、where条件过滤和having分组过滤区别
1、聚合函数
where条件后面不允许使用聚合函数即通过执行计算返回单一值得函数例如常用的：max、min、count、sum、avg
having 后面可以使用聚合函数
2、查询结果先后
where是约束条件 再产生结果，group by分组之后，having进行结果之后 再过滤
3、网友建议
Where是一个约束声明，在查询数据库的结果返回之前对数据库中的查询条件进行约束，即在结果返回之前起作用，且where后面不能使用聚合函数
Having是一个过滤声明，所谓过滤是在查询数据库的结果返回之后进行过滤，即在结果返回之后起作用，并且having后面可以使用聚合函数。
4、补充说明：
聚合函数：是对一组值执行计算并返回单一的值的函数，它经常与select语句的group by字句一同使用，
SQL聚合函数有：1、AVG函数；2、COUNT函数；3、MAX函数；4、MIN函数；5、SUM函数；6、GROUPING函数；7、CHECKSUM函数；8、STDEV函数；9、STDEVP函数；10、VAR函数；11、VARP函数等等十四个或者十几个吧。
（5）、正确使用ifnull（）+sum（）函数
#通过条件获取的行中，查询的列在行中没有数据，即为空,则聚合函数sum(null) 则返回的值也为空，而不是0，所以，一定要加ifnull()函数进行判断，例如ifnull(sum(risk_larger_number),0) largerMumber
二、mysql 索引相关
1、索引失效相关
https://www.cnblogs.com/duanxz/p/5244703.html
2、索引类型为range范围查询时，数据量/总数量>20% 索引会失效
mysql range范围查询，当查询的数据量/总数据量 > 20%左右，索引就会失效，组合索引失效
解决办法：
1、对相应的功能做限制，例如只能选择七天之内的数据。
2、使用强制索引
3、修改mysql的配置
解决办法:
强制走索引
6
SELECT *
FROM `table_xxx`
FORCE INDEX ( ind_coll_date_route )
WHERE <br>dep_date
BETWEEN '2017-12-01'
AND '2017-12-30'
虽然走了强制索引，但实际上依然很慢。这主要还是数据量大或者并没有真正走到索引上。
当数据量大，这样的联合索引：ind_coll_date_route (dep_date ,dep,arr) 日期就走的很慢，经过多少发现这是mysql的一个bug，
后将联合索引改成：ind_coll_date_route (dep,arr，dep_date) ，速度快了很多，并且也走了索引。
system（一条数据）>const（匹配了一条）>eq_ref(主键和唯一的等于)>ref（等于）>range（><between范围，数据量大mysql优化器会由range变为all，数据量小则为range）>index(没有where或没起到作用直接select 索引列)>all
MySQL-explain执行计划-type类型索引说明
https://www.cnblogs.com/july-sunny/p/15141195.html
通过explain看key（自己创建的索引名称）用到了，并且type索引类型已经优化到尽头了，查询速度依然慢，则需要看额外的信息：Extra
#Using filesort 没有用到创建索引的排序，自己内部多进行了一次排序并使用，遇到之后，九死一生，记得优化。
#Using temporary 十死无生，必须优化。新建了一个内部的临时表，处理的数据，先拷贝到临时表中再处理，然后，再把临时表删除
#Using index condition; Using where; Using temporary; Using filesort
#Using where; Using index; Distinct
#覆盖索引：select co1,co2,co3 from table 刚好我建立的复合索引中是index_co1_co2_co3,则直接从索引里面获取，不必根据索引再次读取数据文件了，即查询的列要被所建的索引覆盖。
#建议：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*
##Using temporary ，distinct 去重,order by 和 group by都会触发，特别小心它 会使mysql内部建立一个临时表，进行去重，处理数据，再把临时表删除
#Using index 就是使用到了覆盖索引，并且select列表中小于等于建立索引最左原则的字段顺序和数量
#Using where 表明索引被用来执行索引键值的查找即where条件使用到了，即小于等于建立索引最左原则的字段顺序和数量
#如果同时没有出现using where，表明索引只用来了读取数据即select查询了，但是没有执行查找即没有where条件使用，或者where条件写了没起到作用和{条件和没写一样}
官方网站优化Using filesort：
[https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html](https://links.jianshu.com/go?to=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Forder-by-optimization.html)
查询数据库的所有索引：
SELECT a.TABLE_SCHEMA,
a.TABLE_NAME,
a.index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics a
GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name
索引删除
DROP INDEX <索引名> ON <表名>
索引创建
create index idx_字段名_字段名 on table(字段名,字段名)
查看单表的索引
SHOW INDEX FROM <表名> [ FROM <数据库名>]
mysql使用规范-索引规范
(1)单张表中索引数量不超过5个。
(2)单个索引中的字段数不超过5个。
(3)索引名必须全部使用小写。
(4)非唯一索引按照“idx_字段名称[_字段名称]”进用行命名。例如idx_age_name。
(5)唯一索引按照“uniq_字段名称[_字段名称]”进用行命名。例如uniq_age_name。
(6)组合索引建议包含所有字段名,过长的字段名可以采用缩写形式。例如idx_age_name_add。
(7)表必须有主键,推荐使用UNSIGNED自增列作为主键。
(8)唯一键由3个以下字段组成,并且字段都是(整)(形)(时),可使用唯一键作为主键。其他情况下,建议使用自增列或发号器作主键。
(9)禁止冗余索引。
(10)禁止重复索引。
(11)禁止使用外键。
(12)联表查询时,JOIN列的数据类型必须相同,并且要建立索引。
(13)不在低基数列上建立索引,例如“性别”。
(14)选择区分度大的列建立索引。组合索引中,区分度大的字段放在最前。
(15)对字符串使用前缀索引,前缀索引长度不超过8个字符。
(16)不对过长的VARCHAR字段建立索引。建议优先考虑前缀索引,或添加CRC32或MD5伪列并建立索引。
(17)最左原则：合理创建联合索引,(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。
(18)合理使用覆盖索引减少IO,避免排序。
