mysql常用脚本规范整理

索引设计规范

1、索引区分度

索引必须创建在索引选择性（区分度）较高的列上，选择性的计算方式为: selecttivity = count(distinct c_name)/count(*) ; 如果区分度结果小于0.2，则不建议在此列上创建索引，否则大概率会拖慢SQL执行

1	select count(distinct id)/count(*) from bill_check

2、遵循最左前缀

对于确定需要组成组合索引的多个字段，设计时建议将选择性高的字段靠前放。使用时，组合索引的首字段，必须在where条件中，且需要按照最左前缀规则去匹配。

3、禁止使用外键，可以在程序级别来约束完整性

4、Text类型字段如果需要创建索引，必须使用前缀索引

5、单张表的索引数量理论上应控制在5个以内。经常有大批量插入、更新操作表，应尽量少建索引，索引建立的原则理论上是多读少写的场景。

6、ORDER BY，GROUP BY，DISTINCT的字段需要添加在索引的后面，形成覆盖索引

7、正确理解和计算索引字段的区分度，文中有计算规则，区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。

8、正确理解和计算前缀索引的字段长度，文中有判断规则，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。

9、联合索引注意最左匹配原则：必须按照从左到右的顺序匹配，MySQL会一直向右匹配索引直到遇到范围查询(>、<、between、like)然后停止匹配。

如：depno=1 and empname>’’ and job=1 如果建立(depno,empname,job)顺序的索引，job是用不到索引的。

10、应需而取策略，查询记录的时候，不要一上来就使用*，只取需要的数据，可能的话尽量只利用索引覆盖，可以减少回表操作，提升效率。

11、正确判断是否使用联合索引（上面联合索引的使用那一小节有说明判断规则），也可以进一步分析到索引下推（IPC），减少回表操作，提升效率。

12、避免索引失效的原则：禁止对索引字段使用函数、运算符操作，会使索引失效。这是实际上就是需要保证索引所对应字段的”干净度“。

13、避免非必要的类型转换，字符串字段使用数值进行比较的时候会导致索引无效。

14、模糊查询’%value%’会使索引无效，变为全表扫描，因为无法判断扫描的区间，但是’value%’是可以有效利用索引。

15、索引覆盖排序字段，这样可以减少排序步骤，提升查询效率

16、尽量的扩展索引，非必要不新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

创建索引脚本

1	CREATE INDEX TABLE_IDX USING BTREE ON DATABASE.TABLE ();

添加字段脚本

1	alter table user add user_name varchar(20);

添加字段长度脚本

1	alter table user modify column user_name varchar(50)

删除表脚本

1	drop table if exists `user`

新增表脚本

CREATE TABLE `lx_invoice` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `born_date` date DEFAULT NULL COMMENT '出生日期',
  `amount` decimal(20,8) DEFAULT NULL COMMENT '价值',
  `status` tinyint(2) DEFAULT NULL COMMENT '健康状态',
  `remark` text COMMENT '备注',
  `create_use` varchar(20) DEFAULT NULL COMMENT '创建人',
  `create_datetime` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 COMMENT='人研究表';

字符串替换SUBSTRING/REPLACE

SELECT REPLACE('我来自上海804',SUBSTRING('我来自上海804',1,3),'***')
(result:***上海804)

SELECT REPLACE('12345678@qq.com','1234567','******')
(result:******8@qq.com)

SELECT REPLACE('1234567891',SUBSTRING('1234567891',4,11),'********')
(result:123********)

过滤一条记录字段（场景：查询当前项目组人数大于一的所有员工信息

）

1	SELECT * FROM `bill_check`

select * from bill_check
where company in (
select company from bill_check
group by company
having count(1)>1)

四舍五入ROUND函数

SELECT ROUND(150.45648, 2);
*--保留小数点后两位，需要四舍五入*
*--结果：*
150.46000

SELECT ROUND(150.45648, 2, 0);
*--保留小数点后两位，0为默认值，表示进行四舍五入*
*--结果：*
150.46000

SELECT ROUND(150.45648, 2, 1);
*--保留小数点后两位，不需要四舍五入，这里除0以外都是有同样的效果，
--与Oracle的TRUNC函数效果相同*
*--结果：*
150.45000

SELECT ROUND(150.45648, 2, 2);
*--保留小数点后两位，不需要四舍五入，这里除0以外都是有同样的效果，
--与Oracle的TRUNC函数效果相同*
*--结果：*
150.45000