mysql数据库相关

索引概念：索引是对数据库表中的一列或者多列的值进行排序的一种数据结构，如果把数据库中的表比作一本书，索引就是这本书的目录，通过目录可以快速查找到书中指定内容的位置
索引也是一张表，该表中存储着索引的值和这个值的数据所在行的物理地址，使用索引后可以不用扫描全表来定位某行的数据，而是通过索引表来找到该行数据对应的物理地址

mysql数据库索引类型：
1).普通索引：普通索引是最基本的索引，它没有任何限制，允许在定义索引的列中插入重复值和空值
2).唯一索引：索引列的值必须唯一，允许有空值，如果是组合索引，列值的组合必须唯一
3).主键索引：主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在创建表的时候指定主键，主键默认就是主键索引
4).组合索引：多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用
5).全文索引：索引类型为FULLTEXT，允许有重复值和空值，可以在char、varchar、text类型的列上创建，Mysql中MyISAM和InnoDB存储引擎都支持。主要用来查找文本中的关键字，而不是直接与索引中的值比较，它更像是一个搜索引擎，全文索引需要配合match against操作使用，而不是一般的where语句加like
6).空间索引：空间索引是对空间数据类型的字段建立的索引，Mysql中的空间索引类型有4种，GEOMETRY、POINT、LINESTRING、POLYGON，创建空间索引的列，必须将其声明为not null，Mysql中只有MyISAM存储引擎支持创建空间索引

数据库索引的优缺点：
优点：
1.通过创建唯一索引，可以保证数据库每一行数据的唯一性
2.可以大大提高查询速度
3.可以加速表与表的连接
4.可以显著的减少查询中分组和排序的时间。

缺点：
1.创建索引和维护索引需要时间，而且数据量越大时间越长
2.创建索引需要占据磁盘的空间，如果有大量的索引，可能比数据文件更快达到最大文件尺寸
3.当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

详情

1.Mysql创建索引
执行create table语句可以创建索引,也可以单独用create index或alter table来为表增加索引;
对每个表进行索引,能极大加速查询进程,从表1中选择一行,查看此行包含的数据,使用表2上的索引,直接定位表2与表1值匹配到的行,扫描表1的下一行,并重复前面的过程,直到遍历表1的所有行; 利用索引,Mysql加速了Where子句满足条件行的搜索,而在多表连接查询时,在执行连接时加快了与其他表中行匹配的速度;

2.数据库的优化
选用合适的数据属性:
数字:空间越小越好,能用整数就不要用小数;
字符串:能用数字就用数字,长度可固定就固定;
日期类型:用数字(毫秒值);
不要使用text,blob类型;(必要时则独立出一张表,用主键来对应,避免影响其他字段索引效率)
把字段定义为not null并且提供默认值;
将字段很多的表分解成多个表,数据过多纵向拆分.(分库分表,使用mycat中间件操作);
增加中间表,适当的将联合查询改为中间表查询;
适当增加冗余字段,提升查询的速度;
3.数据库的读写分离
读库和写库数据一致。
写数据必须写到写库。
读数据都可以。

当一个事务时，连接只有一个，所以读写都会发生在主库中。

主从同步原理
主库将改变的数据记录到binary log（二进制日志）
从库拷贝日志到中继日志（relay log）
从库读取日志，改变数据

4.Sql语句的优化
添加索引(注意:经常作为查询条件(排序)的字段,不能经常修改)
不在数据库进行运算;
不同类型不可进行比较;
禁止使用反向查询,会导致全表扫描;
尽量不要使用like关键字查询(如果匹配字符串的第一个字符为”%”,索引不起作用);
使用or关键字查询,且or前后的两个条件中的列都是索引时,索引才会生效,否则索引不生效;推荐使用in或者union;
查询条件中尽量不使用函数;
只要一行数据时使用limit1;
关联查询是,永远用小结果集驱动大结果集;
只取出自己需要的结果集,不要select *;
尽量不使用子查询;

5.数据库中char和Varchar的区别
char的长度是不可变的：身份证，电话号码 （效率高，以空间换时间）
varchar的长度是可变的：（效率低，节省空间）

6.Redis的持久化,Redis分布式锁,雪崩,穿透
Redis持久化:
RDB持久化(将redis中的数据定时快照到磁盘的RDB持久化);
AOF持久化(将Redis的操作日志以追加的方式写入文件)
Redis分布式锁:
执行set()方法(条件:Set()加入了NX参数,可以保证如果已有key存在,函数就不会调用成功):当前没有锁,就进行加锁操作,并对锁设置个有效期,同时value表示加锁的客户端;已存在锁,不做任何操作;
雪崩和穿透:
雪崩:由于原有的缓存过期失效,新的缓存还没有缓存进来,导致所有的请求跑到了数据库,导致数据库内存过大,甚至宕机,整个系统崩溃;
解决: 采用加锁技术,或者使用合理的队列数量避免缓存失效对数据库造成太大的压力;
分析用户行为,让失效的时间点均匀分布,避免缓存雪崩的出现;
Redis做主备,集群,防止服务器宕机;
穿透:指用户查询数据,在数据库没有,自然缓存中也不会有,这样就导致用户查询的时候,在缓存中找不到,每次都要去数据库再查询一遍,然后返回空,这样请求就绕过缓存直接查询数据库,这就是经常提到的缓存命中率问题;
解决的办法是: 如果查询数据库也为空.直接设置一个默认值存放到缓存,这样第二次到缓存中获取就有值了,而不会继续访问数据库,这种方法最简单有效;

Mysql优化

对于mysql插入数据的优化
影响数据插入效率，主要是索引，唯一约束，和插入的数据的条数
不同的存储引擎，优化的手段也是不同的
Myisum 非事务安全型的 支持全文搜索，不知持外键
innoDB 事务安全的存储引擎

mysql数据库结构的优化
保存的数据越小越好，越小越省空间
数据量过多的时候，采取分库分表的策略
一张表的字段过多的时候，采用垂直分表

sql语句小结：
主键约束：Primary key auto_increment
唯一约束：unique
非空约束：not null

//创建数据库
Create database 数据库名称
//删除数据库
Drop database 数据库名
//修改数据库的编码集
Alter database 数据库名 cahracter set 字符集
//查看数据库的相关信息
Show create database 数据库名
//切换数据库
Use 数据库名

数据表操作语言小结
//增加列
Alter table 表名 add 变量名 数据类型 约束；
//修改列名
Alter table 表名 change 旧列名 新列名 数据类型 约束；
//修改现有的列的 长度/类型/约束
Alter table 表名 modify 列名 类型 约束;
//修改现有的列
Rename table 旧表名 to 新表名；
//修改表的字符集
alter table 表名 character set 编码集;
//删除表
Drop table 表名；
//查看表结构
Desc 表名；
//查看所有的表
show tables；
//查看见表语句和字符集
show create table 表名： 查看建表语句以及字符集

数据表内容的增删改

//新增数据
Insert into 表名（列名，列名，列名。。。。。。）values （，，，）
//修改列中的数据
Update 表名 set 列名=值【where 条件语句】
//删除表中的数据 删除行 不加条件语句 就是默认删除整张表的数据
Delete from 表名【where 条件语句】
Truncate table 数据表；
//mysql数据库的滤重操作
Select distinct 列名1，列名2 from 表名【条件】
Order by 排序
Select * from 表名 order by desc|asc
注意：select语句关键字的顺序。
select …. from … where … order by …

关于null的问题
如果使用null进行表达式的运算，则结果也是null
mysql中，提供了一个函数 ：
ifnull(列名，默认值)
判断该列是否为null,如果为null，返回默认值，如果不为null，返回实际的值

聚合函数 聚合函数不能嵌套使用
求和 sum() 可以排除null sum仅对数值类型的列起作用，否则会报错。
平均值 avg() 求该列的平均值，null不计入统计的 round()函数，保留几位小数
最大值 max()
最小值 min()
计数 count()
按照列来统计有多少行
select count(列名) from 表名
注意： count在根据指定的列统计的时候，如果这一列中有null 不会被统计在其中。

Round(数值，小数的位数)

分组查询（按照某一列或者某几列，把相同的数据合并输出）
语法
select … from … group by 列名;
注意事项：
1、聚合函数：分组之后进行计算；
2、通常 select的内容：a 被分组的列，b 聚合函数。
3、如果遇到这种情况 按照 每种，每个。 类似的这些语句的时候，通常会使用分组。
4、如果使用group by 对数据进行分组之后还要过滤。这时一般不能使用where，因为where关键字的后面不能跟上面讲解的这些函数。如果需要在过滤的条件中加上述的函数，只能使用having关键字。
5、where 后不能跟 聚合函数，having中可以跟 聚合函数。

//分组查询
1 找表 2 对表中的数据进行分组 3 查询分组后的数据 4过滤条件

一对多关联查询
建表之后添加外键约束
语法 : alter table 从表名称 add foreign key (外键列的名称) references 主表名称(主键)
隐式内连接 select * from a,b where a.列名 = b.列名

显示内链接
多表查询总结
隐式内连接 查询的是两张表都有的数据
Select * from 表A，表B where A.id=B.id;
显示内连接
Select * from 表A inner join 表B on A.id=B.id
左外连接 查询的内容，以左侧的表为主，如果左侧有数据，右侧没有对应的数据，仍然会把左侧数据进行显示。
Select * from A LEFT JOIN B ON A.id=B.id;
右外连接 查询内容以右侧为主，如果左侧有数据，右侧没有数据，仍然会显示右侧数据
Select * from A right join B ON A.ID=B.ID
全外连接 full join on mysql不支持
可以用union替代解决