========================= 索引作用:主要用来优化查询 数据库查询(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;