DB问题 mysql 查询索引，实现原理，设计表思想

TIPS:
1:mysql是使用的B+树存储索引和innodb引擎，所以建表的主键id推荐使用自增主键便于范围查询；
2：一个表中的数据最好要小于两千万条（1170 x 1170 x 16 = 21902400 ），这样B+树的高度小于3，查询速度会快，如果比这数据量还大，需要做表的拆分一般来说超过100万的就分表了；
3：只给经常需要查询的字段列添加索引，能少加联合索引就少加，否则索引文件的大小会增长的很快；
4：B-Tree可以对<，<=，=，>，>=，BETWEEN，IN，LIKE ‘张%’，like通配符在右侧的搜索条件按照索引查找；
5：数据量大的表推荐增加索引的列：
①前端用下拉框展示的列；
②前端用级联框展示的列；
③前端用radio框展示的列；
例如：性别，警种，地区代码等；
6：字典表的code尽量使用00,10,20,30，因为后期需要考虑业务的变更，可以再向内插入01,11,21等变更的状态，保持代码的可读性；
7：mysql的表名和字段用一致使用小写字母加下划线（规范）；

B+树：
概念:
1:每一个父节点的元素都存在于子节点中，是子节点的最大或者最小元素
2:根节点的最大元素是整个B+树中的最大元素

字段类型
只写我用过的

类型	描述
int (4byte)	一般作为表的id，自增主键
tinyint(1byte)	一般作为字典表的code，-128<数字范围<127，如果是地区类型的字典则用int
float(4byte)	有效位24，如果设置(12,12)则是12为整数位，12位小数位，一般用来存储地图经纬度
datetime(8byte)	存储YYYY-MM-DD HH:MM:SS 类型的时间
timestamp(4byte)	存储时间YYYY-MM-DD HH:MM:SS 类型的时间
TIPS	datetime能到人类出现之前的时间，到9999年之后，timestamp只能到1970年-2038年，今年2021年;timestamp你存进去什么拿出来不一定是什么,会根据时区给你拿，timestamp存null会存当前;datetime存当前时间需要赋默认值CURRENT_TIMESTAMP
char(255byte)	是定长的字符串(我没用过)
varchar(255byte)	不定长的字符串
TIPS	char和varchar都是设定字段长度为20,则汉字和字母都能存储20个字符,但是实际上的长度是UTF-8下汉字占三个字节,GBK占两个字节,char会填充不足长度的数据(我用LENGTH函数查过好像跟varchar没什么区别??),所以一般特定的业务下才会用char,一般用varchar,超过 255 字符的只能用 varchar
TIPS	当varchar的长度设置超过255之后，varchar可以超过设定的长度，比如设置长度为300，但是可以存3000长度的字符，但是varchar设定的最大长度不能超过这个表的字段长度总和，也就是65535个字节，如果需要存储几万的字符的数据，则分表设计

TIPS
1：对于数据量大的表，推荐使用联合索引，减少数据库操作的读写开销.
2：有以下联合索引(col1,col2,col3)，有SQL语句，select col1,col2,col3 from table where col1 = ‘x’ and col2 = ‘xxx’; 则查询速度会加快，减少了回表，省略了很多随机io操作
3：设计原则：如果该SQL语句中where条件 group by 条件和 order by 条件涉及到不同的列，那么就要考虑创建联合索引
4：因为联合索引符合最左原则，则（col1,col2,col3,col4…）等索引应该按照能筛选数据量从多到少建立，假设有表内10W数据，那么col1条件应该能筛选4W col2:2W,col3:5000…

最左原则：
假设现在有表test，联合索引：a,b,c
SQL语句：select a,b,c,d,e from test where a = ‘xxx’ and b = ‘xxx’ and c = ‘xxx’;
那么用到了三个索引a，b，c，
SQL语句：select a,b,c,d,e from test where c = ‘xxx’;
所有索引失效
就是索引要从第一个开始到最后一个，在where中可以写c=‘xx’ and a = ‘xx’，mysql的sql优化会编译成a索引在前面

5：范围查询会使联合索引失效（<,>，between等）
6：优化SQL时，需要什么字段就返回什么字段

mysql建表语句：
以创建一个users表为例 表字段：id，name，age，birthday

描述	语句
删除表	DROP TABLE IF EXISTS users;
创建表	CREATE TABLE users ( id int (11) NOT NULL,name varchar(50) DEFAULT NULL, age tinyint(4) DEFAULT NULL, birthday datetime DEFAULT NULL, PRIMARY KEY (id)) COMMENT=‘用户信息’ ENGINE = InnoDB DEFAULT CHARSET = utf8;
添加主键	ALTER TABLE users ADD PRIMARY KEY ( id );
添加注释(表)	ALTER TABLE users COMMENT ‘修改用户信息表注释’;
添加注释(字段)	ALTER TABLE users MODIFY COLUMN name VARCHAR(100) COMMENT ‘用户姓名’;
添加索引(主键)	ALTER TABLE users ADD PRIMARY KEY ( id );
添加索引(普通)	ALTER TABLE users ADD INDEX users_name ( name );
添加索引(唯一)	ALTER TABLE users ADD UNIQUE ( name );
添加索引(全文)	ALTER TABLE users ADD FULLTEXT ( name );
添加索引(多列)	ALTER TABLE users ADD INDEX users_age_name( age,name );
TIPS	全文索引（mysql v5.7.6版本之后）必须字段类型为char,varchar,text等存储文本的类型，如果该字段存储的本文较长，则在该字段上建立全文索引
TIPS	唯一索引允许为空，创建唯一索引的列存在值之后继续插入重复值则会报错，使用语句INSERT IGNORE INTO users (name,age)VALUES(‘张三’,‘20’);或者REPLACE INTO,区别 INSERT IGNORE 是修改，REPLACE 是删除后新增

mysql函数：

函数名	描述	语句	结果
CONCAT(V1,V2…)	拼接字符串	略	略
CONCAT_WS(S,V1,V2…)	根据分隔符拼接字符串	SELECT CONCAT_WS(’-’,‘1’,‘2’,‘3’)	1-2-3
FORMAT(V,N)	根据N小数点后面四舍五入	SELECT(1234567.125,2);	1,234,567.12
LOCATE(S1,S2)	JS的indexOf	SELECT LOCATE(‘c’, ‘abc’);	3
LPAD/RPAD	同ORACLE	略	略
SUBSTR	同ORACLE	略	略
TRIM(S)	同JS	略	略
ADDDATE(d,n)	计算日期加减	SELECT ADDDATE(“2020-01-01”, INTERVAL 10 DAY);	2020-01-11
ADDTIME(d,n)	计算时间加减	SELECT ADDTIME(‘2020-01-01 00:00:00’, ‘2:10:10’);	2020-01-01 02:10:10
CURRENT_TIMESTAMP()/NOW()/SYSDATE()	当前时间
DATEDIFF(d,d)	计算两个日期差	SELECT DATEDIFF(‘2020-01-10’, ‘2020-01-1’);	9
ADDDATE(d,d)	时间的加减	SELECT ADDDATE(‘2020-01-10’, 1);	2020-01-11
DATEDIFF(d,d)	计算两个日期差	SELECT ADDDATE(‘2020-01-01 00:00:00’, INTERVAL 20 MINUTE)	2020-01-01 00:20:00
DATE_FORMAT(d,f)	格式显示日期	SELECT DATE_FORMAT(‘2020-01-10 00:01:01’, ‘%Y-%m-%d %T’);	2020-01-10 00:01:01
LAST_DAY(d)	返回d月最后一天	SELECT LAST_DAY(‘2020-01-10’);	2020-01-31
QUARTER(d)	返回季	SELECT QUARTER(‘2020-01-10’);	1
STR_TO_DATE(s, f)	特定字符串转日期	略	略
COALESCE(V1, V2, …)	同ORACLE的NVL	SELECT COALESCE(NULL,NULL,1);	1
IF(E,V1,V2)	三目运算：E?V1:V2	略	略
IFNULL(V1,V2)	ORACLE的NVL	略	略

增删改查SQL：略