mysql
========================= 索引作用：主要用来优化查询 数据库查询（select）方式： 1、全表扫描 2、索引扫描 索引种类： B+tree R+tree hash fulltext B+树索引查找方式： 查找数据的方式，对于每个数据都是公平相等的 B+树索引相对于B树索引在range方面做了优化：双向链表对于范围查找减少遍历索引的次数 B树索引类型： 聚集索引：自动按照主键的顺序，存储数据 叶子节点存储的是按照主键顺序组织的整行数据 聚集索引列特别适合于作为查询条件（范围查询、order by 、join on 的条件） 整行数据存在叶子节点 辅助索引：普通索引，人为创建，按照索引列值排序后，生成索引表。 按照顺序将索引的列值存储到叶子节点，会记录键值所在的真正存储位置的指针。 只有索引那一列数据存在叶子节点上 覆盖索引：一般是联合索引。减少回表查询。 联合索引数据存在叶子节点上 alter table t1 add index idx_name(name); alter table t1 add unique index idx_name(name); alter table t1 add index idx_name(name,age); 开发规范： sql语句优化，避免不走索引sql语句的出现，一下这些语句都尽量避免 1、select * from t1; 查询所有数据，不走索引 2、select * from t1 where 1=1; 查询所有数据，不走索引 3、索引在name列，条件在age列这种就是典型的没走索引 select * from t1 where age>20; 4、<> , not in ,not exists 这几种条件范围太大，也不走索引 5、like '%aa' 这种%符号在前面的也不走索引 6、查询的结果接超25% 7、查询条件有计算、转换函数 select * from t1 where id-1=10; 8、telnum=110 120 119 varchar(200) 电话号码应该存成数字类型，但是存成了varchar类型的 explain select * from t1 where telnum='110'; 这种就是走索引的 explain select * from t1 where telnum=110; 这种就没走索引，因为存储的是varchar，查询的是int，底层要做异步函数转换。 9、联合索引 idx(a,b,c) where a b c where a b where a where a c 部分走索引 任何不以a 作为第一条件列的查询都不走联合索引 10、order by group by select * from t1 where countrycode='chn' order by population desc limit 10; 对于这条语句的查询优化，建立联合索引 alter table t1 add index idx1(countrycode,population); 11、下面都属于range索引的最低级别 or ，in 这俩效率最差，不符合B+树的结构，建议改写 下面这些效率会好一些，属于连续查询，符合B+树的结构 between and > < like 'aa%' ----- 优化下面的查询语句： 优化前的语句，range级别 desc select * from city where countrycode='chn' or countrycode='jpn'; 优化后的语句，变成 ref级别，关键点union all desc select * from city where countrycode='chn' union all select * from city where countrycode='jpn'; ----- explain（desc） 看语句的执行计划，从sql层中的优化器中拿出来，并未真正执行 type: ALL 全盘扫描，没走索引 index 全索引扫描 range（） 范围 ref：普通索引的等值查询 eq_ref ：表连接查询 on条件 有唯一索引或主键索引 const、system ：主键索引或唯一键索引等值查询 between and > < like 'aa%' extra： filesort ：order by 解决：建立联合索引 temp table: union group by 连表查询优化： key_buffer_size= a join b on a.xxx=b.yyy where a.zzz=xx =========================
===================================== 1、获取、上传并解压： linux 中安装mysql-5.7.20 tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar tar xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql 2、修改配置文件 rm -rf /etc/my.cnf* vim /etc/my.cnf [mysqld] basedir=/usr/local/mysql datadir=/data/mysql socket=/tmp/mysql.sock server_id=10 log_bin=/data/mysql/mysql-bin [mysql] socket=/tmp/mysql.sock 3、创建必要目录、该目录权限 mkdir -p /data/mysql useradd mysql chown -R mysql.mysql /data 4、初始化数据 vim /etc/profile export PATH=/usr/local/mysql/bin:$PATH source /etc/profile 初始化数据库，忽略安全认证，否则会生成临时密码，现在就可以直接启动了 mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
mysqladmin -u root -p # 设置root密码（本地）
#设置远程连接
https://blog.csdn.net/hello_world_qwp/article/details/79544162
mysql -uroot -p"123456"
use mysql
select Host,User from user;
#修改成远程可连接
update user set Host='%' where User ='root' limit 3;
update user set Host='192.168.60.128' where User ='root' limit 3;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_password' WITH GRANT OPTION;
flush privileges;
systemctl restart mysqld.service
systemctl status firewalld.service
mysql -h192.168.60.128 -P 3306 -uroot -p"123456"
启动服务端
mysqld_safe &
killall mysqld
客户端直接连接服务端
mysql
mysql -h192.168.60.128 -P 3306 -uroot -p"123456"
========================= 授权 grant all on *.* to root@'localhost' indentified by '123'; grant 权限组 on 权限范围 to 用户 identified by '123'; TCP/IP: IP PORT USER PASSWORD ===================== MySQL 服务器的构成 一：连接层 1、提供链接协议：TCPIP socket 2、用户密码验证 3、提供链接线程（接收发起SQL语句、返回结果） 二：SQL层 *** 1、语法检查 2、权限检查 3、语义检查（DDL、DCL、DML、DQL） 4、专用解析器解析SQL，生成执行计划 （优化关注重点） 5、优化器，选择自己认为的代价最低的执行计划。 （优化关注重点） 6、执行器根据优化器的选择执行SQL query_cache:SQLID+执行结果 Tair ----》memcached 三：存储引擎 表： 表名，列（列名，列属性），额外属性，数据行 =============================== SQL标准： SQL92 SQL99 SQL_MODE：约束mysql语句书写规范 DDL : 数据定义语言 DCL ：数据控制语言 DML ：数据操作语言 DQL ：数据查询语言 一：DDL :数据定义语言 定义元数据（库属性、表） 库： create database drop database alter database 表： create table drop table alter table truncate table ----- 开发规范： 1、建库规范 （1）库名字：一定是小写，不能用数字开头，和功能相关 （2）建库时要加字符集 CREATE DATABASE testdb CHARSET utf8; CREATE DATABASE testdb CHARSET utf8mb4; CREATE DATABASE testdb CHARSET utf8mb3; 2、建表规范 （1）表名字，必须小写，不能用数字开头，功能相关 （2）必须要有主键 （3）尽量非空 （4）数字列，尽量无符号 （5）尽量避免外键 （6）建立唯一性索引 （7）最好减少join表的个数（不要超过3个） （8）数据类型使用合适的 （9）每列需要加注释 create table db1.t1 (id int not null auto_increment primary key comment 'add id') （10）索引列值避免null值出现（两个以上），where xx is not null ----- 二：DCL ：数据控制语言 grant revoke 开发申请生产库权限： 沟通细节： 1、要从哪登录 2、管理哪个库 3、你要做什么 -------------------- 授权： grant all on *.* to root@'localhost' indentified by '123'; grant 权限组 on 权限范围 to 用户 identified by '123'; 权限组： create ,insert ,update ,select ,delete ALL ：除了grant和revoke之外的所有权限 一般开发人员具有的权限：create ,insert ,update ,select 伪删除：加一个状态列，生产环境都会有这种功能 权限范围： *.* db.* db.t1 用户： 用户名@'白名单' root@'localhost' root@'192.168.15.%' -------------------- 回收权限 revoke delete on db.* from root@'xxx'; ----- 三：DML ：数据操作语言 insert insert into t1 values (1),(2),(3); insert into t1(id,name,age) values (1),(2),(3); 大批量录入数据：（类似爬虫类的其实mongodb更适合） 1、在业务不繁忙时 方案一、锁表插入，并且分批插入 方案二：降低隔离级别 update where条件 分布式架构：中间件+多个数据库 （1）分布式存储 （2）热点数据切分 delete ----伪删除 四：DQL ：数据查询语言 show select from where join on group by having order by desc limit ===================================== 常用数据类型： 整型： 浮点型： 字符串： char() 长度基本固定用这个，不需要判断字符长度，分配空间一致，存的时候效率高。 1 浪费内存 2、影响索引的高度，查询的时候效率低 varchar() 长度经常变化用这个，一般用的最多 enum：效率最高 时间类型： ===================================== mysql 数据库版本 5.6.38 5.7.20 SQL 语句书写 join on 子查询 group by +聚合函数 select @@sql_mode; 查看sql模式，主要规范sql语句 ========================= ========================= 存储引擎 1、和“磁盘”打交道的插件式功能模块，类似操作系统的文件系统 2、不同的表可以选择不同的存储引擎 3、MySQL 默认的存储引擎是Innodb Innodb 锁： 粒度:行级锁 控制能力： X锁：排它锁 S锁：共享锁 锁： 乐观锁：没有锁 transaction: 生命周期： begin dml dml dml commit; autocommit=1;
