索引
索引分类
聚集索引: 主键索引
辅助索引:
普通索引
唯一索引
联合索引:
联合主键索引
联合唯一索引
联合普通索引
索引操作
添加索引的时候要注意, 给字段里面数据大小比较小的字段添加, 给字段里面的数据区分度高的字段添加
聚集索引(主键索引)
创建时添加
create table t1(id int primary key)
create table t1(id int primary key(id))
表创建完之后创建
alter table 表名 add primary key(id)
删除主键索引
alter table 表名 drop primary key
唯一索引
创建时添加
create table t1(id int unique)
create table t1(id int, unique key uni_name (id))
表创建完之后添加
alter table s1 add unique key uni_name(id)
删除唯一索引
alter table s1 drop index uni_name
普通索引
创建时添加
create table t1(id int, index index_name(id))
表添加之后添加
create index index_name on s1(id)
alter table s1 add index index_name(id)
删除
alter table s1 drop index index_name
联合索引
创建时添加
create table t1(id int, name char(10), index index_name(id,name))
创建用户和授权
对新用户增删改
创建用户
# 指定ip 登录
create user 'chao'@'192.118.1.1' identified by '123'
# 指定ip段 登录
create user 'chao'@'192.168.1.%' identified
by '123'
# 指定任意ip登录
create user 'chao'@'%' identified by '123'
删除用户
drop user '用户名'@'ip地址'
修改用户
rename user '用户名'@'ip地址' to '新用户名'@'ip地址'
修改密码
set password for '用户名'@'ip地址'=password('新密码')
对当前用户的授权管理
查看权限
show grants for '用户'@'ip地址'
授权
仅对某文件有查询 插入 更新的操作
grant select,insert,update on db1.t1 to 'chao'@'%'
可对某库下的某文件进行任意操作
grant all privileges on db1.t1 to 'chao'@'%'
可对某库下的任意文件进行任意操作
grant all privileges on db1.* to 'chao'@'%'
可对所有数据库中任意文件进行任意操作
grant all privileges on *.* to 'chao'@'%'
取消授权
取消用户对某库某文件的任意操作
revoke all on db1.t1 from 'chao'@'%'
取消用户对某库的任意文件的所有权限
revoke all on db1.* from 'chao'@'%'
取消用户对任意库的任意文件的所有权限
revoke all on *.* from 'chao'@'%'
锁
表级锁
表级别的锁定是mysql各存储引擎中最大颗粒度的锁定机制. 实现逻辑简单, 系统负面影响最小, 所以获取锁和释放锁的速度很快. 由于表级锁可以避免死锁问题
锁定颗粒度大所带来的的负面影响就是出现锁定资源争用的概率也会最高, 致使并发量大打折扣
使用表级锁定的主要是myisam, memory, csv等一些非事务性存储引擎
MyISAM
兼容性:
对表的读操作, 不会阻塞其他用户对同一表的读请求, 但会阻塞对同一表的写请求
对表的写操作, 会阻塞其他用户对同一表的读写请求
MyISAM表的读操作与写操作之间, 以及写操作之间是串行的. 当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
行级锁
行级锁最大的特点就是锁定对象的颗粒度很小, 目前各大数据库管理软件所实现的锁定颗粒度最小的. 因为锁定颗粒度很小, 所以发生锁定资源争用的概率也最小, 能够给予应用程序尽可能大的并发处理能力, 而提高一些需要高并发应用程序的整体性能
虽然能够在并发处理能力上面有较大的优势, 但是由于颗粒度小, 所以每次获取锁和释放锁也就更多, 带来的小号自然也就更大. 行级锁容易发生死锁
使用行级锁定的主要是InnoDB存储引擎
InnoDB
锁定模式:
当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说InnoDB的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX）
实现方式:
InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。下面通过一些实际例子来加以说明。
（1）在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。
（2）由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。
（4）即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。
页级锁
mysql中比较独特的一种锁定级别, 锁定颗粒介于行级锁定和表级锁定之间, 所以获取锁定需要的资源和并发处理能力也是介于两者之间.
页级锁和行级锁一样, 会发生死锁
使用页级锁定的主要是BerkeleyDB存储引擎
死锁
结合上面对表锁和行锁的分析情况，解除正在死锁的状态有两种方法：
第一种：
1.查询是否锁表
show OPEN TABLES where In_use > 0;
2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）
show processlist
3.杀死进程id（就是上面命令的id列）
kill id
第二种：
1.查看下在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
2.杀死进程id（就是上面命令trx_mysql_thread_id列）
kill 线程ID
例子：
查出死锁进程：
SHOW PROCESSLIST
杀掉进程
KILL 420821;
其它关于查看死锁的命令：
1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
2：查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3：查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
总结
表级锁: 开销小, 加锁快, 不会死锁, 锁定颗粒度大, 发生锁冲突的概率最高, 并发度最低
行级锁: 开销大, 加锁慢, 会死锁, 锁定颗粒度小, 发生锁冲突的概率最低, 并发度也最高
页面锁, 开销和加锁时间介于表锁和行锁之间, 会死锁, 锁定粒度介于表锁和行锁之间, 并发度一般
适用方面
从锁的角度来说
表级锁更适合以查询为主, 只有少量按索引条件更新数据的应用, 如web应用.
行级锁更适用于大量索引条件并发更新少量不同数据, 同时又有并发查询的应用, 如一些在线事务处理(OLTP)系统
事务
事务属性
事务是由一组SQL语句组成的逻辑处理单元，事务具有ACID属性。
原子性（Atomicity）：事务是一个原子操作单元。在当时原子是不可分割的最小元素，其对数据的修改，要么全部成功，要么全部都不成功。
一致性（Consistent）：事务开始到结束的时间段内，数据都必须保持一致状态。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的"独立"环境执行。
持久性（Durable）：事务完成后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
事务常见问题
更新丢失（Lost Update）
原因：当多个事务选择同一行操作，并且都是基于最初选定的值，由于每个事务都不知道其他事务的存在，就会发生更新覆盖的问题。类比github提交冲突。
脏读（Dirty Reads）
原因：事务A读取了事务B已经修改但尚未提交的数据。若事务B回滚数据，事务A的数据存在不一致性的问题。
不可重复读（Non-Repeatable Reads）
原因：事务A第一次读取最初数据，第二次读取事务B已经提交的修改或删除数据。导致两次读取数据不一致。不符合事务的隔离性。
幻读（Phantom Reads）
原因：事务A根据相同条件第二次查询到事务B提交的新增数据，两次数据结果集不一致。不符合事务的隔离性。
事务控制语句
BEGIN或START TRANSACTION 显式地开启一个事务；
COMMIT； 也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改成为永久性的；
ROLLBACK； 有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier； SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多SAVEPOINT；
RELEASE SAVEPOINT identifier； 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier； 把事务回滚到标记点；
SET TRANSACTION； 用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
总的来说:
用 BEGIN, ROLLBACK, COMMIT来实现
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0或者off 禁止自动提交
SET AUTOCOMMIT=1或者on 开启自动提交