Centos下学习mysql

Centos下学习mysql

Mysql版本

​ 5.x:

​ 5.0-5.1：早期版本的延续维护升级

​ 5.4-5.x:mysql整合了三分公司的新存储引擎

在centos安装mysql

安装：rpm -ivh rpm软件名

如果安装时以某个软件冲突，则需要将冲突的软件卸载掉

​ yum -y remove xxx

安装完成后需要设置一个密码

/usr/bin/mysqladmin -u root password 'new-password'

启动mysql

service mysql start

关闭mysql

service mysql stop

重启mysql

service mysql restart

注意

在计算机重启后登录mysql可能会报错

–原因：mysql服务没有启动

启动服务：1.手动:/etc/init.d/mysql start

​ 2.开机自启 chkconfig mysql on

检查开机是否自动启动：ntsysv

增加密码

给mysql的root用户增加密码：/usr/bin/mysqladmin -u root password “123456”

登录mysql

mysql -u root -p

然后输入密码

mysql相关目录

查看数据库存放目录命令：ps -ef|grep mysql 可以看到

数据库目录：–datadir=/var/lib/mysql

pid唯一标识目录–pid-file=/var/lib/mysql/localhost.localdomain.pid

mysql核心目录：

/var/lib/mysql：mysql安装目录

/usr/share/mysql:配置文件

/usr/bin:命令目录（mysqladmin、mysqldump等）

Mysql配置文件：

my-huge.cnf 高端服务器 1-2G内存

my-large.cnf 中等规模

my-medium.cnf 一般

my-small.cnf 较小

但是，以上配置文件mysql默认不能识别，默认只能识别/etc/my.cnf

采用my-huge.cnf

cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

注意：mysql5.5默认配置文件/etc/my.cnf;mysql5.6默认配置文件/etc/mysql-default.cnf

mysql字符编码

查看编码：show variables like “%char%”

可以发现部分编码是latin拉丁的，需要统一设置utf-8

打开/etc下的mt.cnf

1.在[mysql]下加default-character-set=utf8

2.在[client]下加default-character-set=utf8

3.在[mysqld]下加character_set_server=utf8和character_set_client=utf8

和collation_server=utf8_general_ci

Mysql逻辑分层：

1.连接层：提供与客户端连接的服务

2.服务层：

1.提供各种用户使用的接口（select）

2.提供sql优化器（Mysql Query Optimizer）

3.引擎层：

1.提供了各种存储数据的方式（InnoDB和MyISAM）

InnoDB:事务优先，适合高并发，行锁

MYISAM：性能优先，表锁

4.存储层:存储数据

查询数据库引擎

1.查询支持哪些引擎：show engines;

2.查看当前引擎: show variables like “%storage_engine%”;

3.使用指定引擎：

在建表时候加上ENGINE=MyISAM

SQL优化：

需要优化原因：性能低、执行时间太长、等待时间太长、sql语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲区，线程数）

SQL：

1.sql:编写过程：

​ select… from…join…on…where…group by…having…order by…limit

2.解析过程：
from…on…join…where…group by…having…select …order by …limit

sql优化主要就是在优化索引

索引：相当于书的目录

官方：index是帮助mysql高效获取数据的数据结构（树）

索引的弊端：

1.索引本身很大，可以存放在内存/硬盘（通常为硬盘）

2.索引部不是所有情况均适用：

少量数据

频繁更新的字段

很少使用的字段

3.索引会降低增删改的效率

索引的优势

1.提供查询效率

2.降低cpu使用率

索引分类

单值索引：单列，一个表可以有多个单值索引

主键索引：不能重复，常用id，不能是null。

唯一索引：不能重复，常用id，可以是null。

复合索引：多个列构成的索引（相当于二级索引）

创建索引：

方式一：

create 索引类型 索引名 on 表(字段 名)

1.创建单值索引

create index dept_index on tb(dept);

2.创建唯一索引：

create unique index name_index on tb(name);

3.创建复合索引：

create index dept_name_index on tb(dept,name);

方式二：

alter table 表名 add 索引类型 索引名(字段名)

1.创建单值索引：

alert table tb add index dept_index(dept);

2.创建唯一索引

alert table tb add unique index name_index (name);

3.创建复合索引

alert table tb add index dept_name_index (dept,name);

注意：如果一个字段是primary key ，则该字段默认就是主键索引

删除索引：

drop index 索引名 on 表名；

查询索引

show index from 表名

SQL性能问题：

1.分析sql的执行计划：explain,可以模拟sql优化器执行SQL语句

2.MYsql查询优化其会干扰我们的优化：

查询执行计划：explain+SQL语句

结果：

id:编号

select_type:查询类型
table：表

type:类型

possible_key：预测用到的索引

key：实际用到的索引

key_len：实际使用索引的长度

ref:表之间的引用

rows:通过索引查询到的数据量

Extra:额外的信息

准备数据:

课程表:

create table course(

cid int (3),

cname varchar (10),

tid int (3)

);

老师表

create table teacher(

tid int(3),

tname varchar(20),

tcid int(3)

);

教师卡表

create table teacherCard(

tcid int(3),

tcdesc varchar(200)

);

插入数据

insert into course values(1,“java”,1);

insert into course values(2,“html”,1);

insert into course values(3,“sql”,2);

insert into course values(4,“web”,3);

insert into teacher values(1,“tz”,1);

insert into teacher values(2,“tw”,2);

insert into teacher values(3,“tl”,3);

insert into teacherCard values(1,“tzdesc”);

insert into teacherCard values(2,“twdesc”);

insert into teacherCard values(3,“tldesc”);

explain+sql语句执行后发现数据量小的表优先查询

索引类型

system>const>eq_ref>ref>range>index>all

其中system、const只是理想情况，实际能到达 ref-range

system（忽略）:只有一条数据的系统表或衍生表只有一条数据的主查询

const：仅仅能查到一条数据的sql，且仅用于主键索引或唯一索引

eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据

ref:非唯一性索引，对于每个所有的查询返回匹配的所有行

range：检索指定范围的行，where后面是一个范围查询(between，>,<,>=, 特殊：in有时候会不是)

index：查询全部索引中数据

all：查询全部表中的数据

索引失效

索引失效原因

复合索引

1.复合索引不要跨列或无序使用

2.复合索引尽量使用全索引匹配

3.复合索引不能使用不等于、is null、is not null，否则自身以及右侧索引全部失效

注意：对于复合索引。如果左边失效，右边全部失效

其他：

1.不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效

2.尽量不要使用类型转换，否则索引失效

3.尽量不要使用or，否则索引失效，甚至左边索引也失效

4.在使用模糊查询*（所有）时，使用百分号开头，索引失效
挽救办法：查询索引字段

锁机制

解决因资源共享而造成的并发问题

锁分类

按操作类型分：

1.读锁（共享锁）：对同一个数据，多个读操作可以同时进行，互不干扰

2.写锁（互斥锁）：如果当前写操作没有完毕。则无法进行其他的读写操作

按操作类型分：

1.表锁：一次性对一张表整体加锁,如myISAM存储引擎使用表锁，开销小，加锁快，无死锁问题，但锁的范围大，容易发生锁冲突，并发度低

2.行锁：一次性对一条数据加锁。如InnoDB储存引擎使用行锁，开销大加锁慢，不容易出现死锁，并发度高