高性能索引B-Tree索引的限制create table peoples ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum('f','m') not null, key(last_name, first_name, dob) );如果不是按照索引的最左列开始查找，则无法使用索引不能跳过索引中的列如果查询中有某个列的范围查询，则其右边的列都无法使用索引查询一句话，B-Tree索引顺序很重要！哈希索引的限制哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引避免读取行哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序哈希索引不支持部分索引列匹配查找哈希索引只支持等值查询 =, in(),<=>,不支持任何范围查询前缀索引计算方式select count(distinct left(idcard, 5)) / count(*); select count(distinct left(idcard, 6)) / count(*);复合索引顺序选择当不需要考虑排序和分组时，将选择性最高的列放在最前面通常是很好的高效索引案例延迟关联-- 存在索引 key(age, name); select * from peoples where age=20 and `name` like '%张三%'; select * from peoples t1 join (select id from peoples where age=20 and `name` like '%张三%') t2 on t1.id=t2.id; -- 第二种方式使用了覆盖索引此方式效率的提高取决于where条件返回的行数，不见得一定会提高效率，要具体分析索引在where与order中的使用create index idx on table1 (c1, c2, c3, c4); # 满足最左前缀原则，且排序的列索引顺序必须一致，排序方向必须一致，不能同时存在asc和desc，查询条件只要存在范围查询，则右面的索引全部失效 # 排序正常使用了索引 select id from table1 order by c1, c2, c3, c4; select id from table1 where c1 = 1 and c2 = 2 order by c3, c4; select id from table1 where c2 = 2 and c1 = 1 order by c3, c4; select id from table1 where c1 = 1 and c2 = 2 order by c3 desc, c4 desc; select id from table1 where c1 = 1 and c2 = 2 order by c3; select id from table1 where c1 = 1 and c2 = 2 order by c3 desc; # 排序未使用索引 select id from table1 order by c2, c3; select id from table1 where c1 = 1 and c2 = 2 order by c3 asc , c4 desc; select id from table1 where c1 = 1 and c2 >= 2 order by c3; select id from table1 where c1 = 1 and c2 = 2 order by c4; select id from table1 where c2 = 2 order by c1;总结 排序使用索引的条件如果存在where条件，所有where条件必须等值查询，且满足最左前缀，顺序可互换排序列的顺序必须与列索引顺序完全一致，且排序方向一致(不可同时出现asc和desc)关联查询时，order by 子句的列必须全部为第一张表的，且满足1，2才能使用索引排序索引案例1. 考虑支持多种过滤条件，而不是随便创建符合条件的索引-- eg 存在如下索引 create index idx_sex_country_age on people(sex, country, age) -- 想查询住在China的20岁的人，需要创建索引 idx_country_age ? no, 可以绕过sex，使用idx_sex_country_age select * from people where sex in (0, 1) and country="China" and age = 20; -- 绕过的选项列表不宜过多2. 尽量将范围查询索引后置年龄列经常被范围查询，age在索引中要尽量放在后面，尽可能的让优化器使用更多的索引。能用IN的尽量用IN，因为IN是等值查询，不受B-tree索引第三条的限制。但是也要考虑IN的值个数。3. 避免多个范围查询维护索引和表检查表check table xxx; repair table xxx; -- innodb 不支持 alter table xxx engine=INNODB; -- 不做任何操作重建InnoDB表更新索引统计信息-- 查看表索引的详细信息 show index from xxxx; -- 或者 select * from information_schema.STATISTICS where TABLE_NAME='xxxx'; -- 更新索引统计信息, 一般系统默认会开启自动更新统计信息 analyze table xxxx;减少索引和数据的碎片optimize table xxxx; -- 不支持优化的引擎可通过如下操作 alter table xxx engine=INNODB; -- 不做任何操作重建InnoDB表总结单行访问是很慢的，如果服务器从存储中读取一个数据块只是为了获取其中的一行，那么就浪费了很多工作。最好读取的块中包含尽可能多所需要的行。使用索引可以创建位置索引用以提升效率。按顺序访问范围数据是很快的，原因有二，其一是不需要多次磁盘寻道，其二，如果服务器能够按需要顺序读取数据，那么就不需要额外的排序操作，并且GROUP BY 查询也无须再做排序和按行按组聚合计算了。索引覆盖是很快的，不需要回表操作，避免了大量单行访问(随机磁盘IO)。
第四讲 索引 (上)总结索引的作用：提高数据查询效率常见索引模型：哈希表、有序数组、搜索树哈希表：键 - 值(key - value)。哈希思路：把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置哈希冲突的处理办法：链表哈希表适用场景：只有等值查询的场景有序数组：按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))有序数组查询效率高，更新效率低有序数组的适用场景：静态存储引擎。二叉搜索树：每个节点的左儿子小于父节点，父节点又小于右儿子二叉搜索树：查询时间复杂度O(log(N))，更新时间复杂度O(log(N))数据库存储大多不适用二叉树，因为树高过高，会适用N叉树InnoDB中的索引模型：B+Tree索引类型：主键索引、非主键索引主键索引的叶子节点存的是整行的数据(聚簇索引)，非主键索引的叶子节点内容是主键的值(二级索引)主键索引和普通索引的区别：主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。从性能和存储空间方面考量，自增主键往往是更合理的选择。没有主键的表，innodb会给默认创建一个Rowid做主键第五讲 索引(下)覆盖索引使用非主键索引查询时，不可避免的有回表操作，使用覆盖索引可取消回表操作，覆盖索引，即索引中的内容覆盖了查询的内容，不需要回表操作。 create table T ( ID int primary key, k int NOT NULL DEFAULT 0, s varchar(16) NOT NULL DEFAULT '', index k(k)) engine=InnoDB; insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg'); select * from where k=2; -- 有回表操作 select ID from where k=2; -- 无回表操作由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。最左前缀原则最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。CREATE TABLE `tuser` ( `id` int(11) NOT NULL, `id_card` varchar(32) DEFAULT NULL, `name` varchar(32) DEFAULT NULL, `age` int(11) DEFAULT NULL, `ismale` tinyint(1) DEFAULT NULL, PRIMARY KEY (`id`), KEY `id_card` (`id_card`), KEY `name_age` (`name`,`age`) ) ENGINE=InnoDB select * from `tuser` where name like '张%'; -- 该语句可使用索引name_age -- name_age 索引包含两个索引 (name, age) 和 (name)建立联合索引时，评估索引复用性。字符串索引占用的空间要比整形索引大。二级索引可以看作是和主键索引的联合索引。索引下推select * from tuser where name like '张%' and age=10 and ismale=1; -- 以上查询语句可用到name索引在 MySQL 5.6 之前，只能先根据name索引找出符合条件的内容，然后到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。可以在索引中比较age，减少回表次数。第六讲 全局锁和表锁全局锁全局加读锁 FTWRL，整个数据库处于只读状态，一般用于不支持可重复读隔离级别的数据库整体备份，确保数据一致性。全局锁主要用在逻辑备份过程中。对于全部是 InnoDB 引擎的库，建议选择使用–single-transaction 参数，对应用会更友好。Flush tables with read lock;\ # 解锁 UNLOCK TABLES; # 第二种让数据库整体只读的方式 不推荐使用 set global readonly=true；建议用FTWRL代替 readonly 一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议你使用。二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高表级锁lock table T1 read; lock table T2 write; # or lock table T1 read, T2 write; # 解锁 unlock tables;MDL（metadata lock)DML： 数据内容的增删改查 select updateDDL： 数据表结构的修改 alter dropDCL： 数据库的控制 revoke grantMDL 不需要显式的使用，作用是保证读写的正确性，对一个表做增删改查，默认开启MDL读锁，修改一个表的结构，开启MDL写锁。MDL主要是解决DML与DDL的冲突。MDL读锁之间不互斥，可以同时对表进行增删改查操作。MDL读锁与写锁，写锁与写锁之间互斥。information_schema 库的 innodb_trx 表可查询当前正在执行的长事务。行锁MyIsam不支持行锁，InnoDB支持两阶段锁在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。死锁及死锁检测当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。两种策略解决死锁问题innodb_lock_wait_timeout 设置超时时间 默认50s开启死锁检测 innodb_deadlock_detect 设置为 on一般使用策略2，策略1如果设置时间过短，可能会误伤到正常的锁等待，时间过长的话，业务无法接受。死锁检测要耗费CPU资源，减少死锁的主要方向，就是控制访问相同资源的并发事务量，降低死锁的检测成本。
第一讲 整体流程学习极客时间 《MySQL实战45讲》# 查看连接状态 show processlist;长连接内存占用大如何解决？定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。第二讲 redo log 与binlog更新操作的流程redo log物理日志 redo log 用于保证 crash-safe 能力binlog归档日志区别：redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。参数设置：# 表示每次事务的 redo log 都直接持久化到磁盘。 innodb_flush_log_at_trx_commit=1 # 表示每次事务的 binlog 都持久化到磁盘。 sync_binlog=1第三讲 事务隔离事务的特性：原子性、一致性、隔离性、持久性多事务同时执行的时候，可能会出现的问题：脏读、不可重复读、幻读事务隔离级别：读未提交、读提交、可重复读、串行化READ-UNCOMMITTED READ-COMMITTED REPEATABLE-READ SERIALIZABLE 配置方法：启动参数 transaction_isolation事务隔离的实现：每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。回滚日志什么时候删除？系统会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除。什么时候不需要了？当系统里么有比这个回滚日志更早的read-view的时候。为什么尽量不要使用长事务。长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还占用锁资源，可能会拖垮库。事务启动方式：显式启动事务语句，begin或者start transaction,提交commit，回滚rollback；set autocommit=0，该命令会把这个线程的自动提交关掉。这样只要执行一个select语句，事务就启动，并不会自动提交，直到主动执行commit或rollback或断开连接。建议使用方法一，如果考虑多一次交互问题，可以使用commit work and chain语法。在autocommit=1的情况下用begin显式启动事务，如果执行commit则提交事务。如果执行commit work and chain则提交事务并自动启动下一个事务。读取异常脏读，一个事务中读取到另一个事务未提交的数据。例如，事务 T1 读取到另一个事务 T2 未提交的数据，如果 T2 回滚，则 T1 相当于读取到了一个被认为不可能出现的值。不可重复读，在一个事务中，当重复读取同一条记录时，发现该记录的结果不同或者已经被删除了；如在事务 T1 中读取了一行，接着 T2 修改或者删除了该行 并提交，那么当 T1 尝试读取新的值时，就会发现改行的值已经修改或者被删除。幻读，通常是指在一个事务中，当重复查询一个结果集时，返回的两个不同的结果集，可能是由于另一个事务插入或者删除了一些记录。例如，事务 T1 读取一个结果集，T2 修改了该结果集中的部分记录 (例如插入一条记录)，T1 再次读取时发现与之前的结果不同 (多出来一条记录)，就像产生幻觉一样。在可重复读隔离级别下，普通查询是快照读，只有当前读情况下，才可能出现幻读这种情况！！幻读仅专指“新插入的行”，读取到行的更新不算幻读！！隔离级别与读现象隔离级别与读现象，性能依次下降。隔离级别脏读不可重复读取幻影数据行隔离表现READ UNCOMMITTEDYESYESYES别人改数据的事务尚未提交，我在我的事务中也能读到。READ COMMITTEDNOYESYES别人改数据的事务已经提交，我在我的事务中才能读到。REPEATABLE READNONOYES别人改数据的事务已经提交，我在我的事务中也不去读。SERIALIZABLENONONO我的事务尚未提交，别人就别想改数据。事务的启动时机start transaction; # 1 并不是事务的起点 update xx set xx=xx; # 2 rollback; #3在上面的语句中，实际上在语句2开始执行的时候，事务才正式启动。事务开始就立刻启动，用下面的语句start transaction with consistent snapshot； # 直接开启事务事务隔离级别总结对于可重复读，查询只承认在事务启动前就已经提交完成的数据；对于读提交，查询只承认在语句启动前就已经提交完成的数据(读提交每次在语句启动前都会生成新的readview)；更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read)；更新时，如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待；事务启动以前所有还没提交的事务，它都不可见。
[TOC]windows环境下安装mysql服务下载https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.19-winx64.zip解压缩，进入 bin 目录安装、启动管理员权限启动 cmd 或者 windows powershell# 安装服务 mysqld install # 启动服务 net start mysql # Mysql服务无法启动，执行以下语句 mysqld --initialize --console net start mysql # 停止服务 net stop mysql修改默认密码set password for 'root'@'localhost'='123456';
忘记mysql的root用户密码的解决办法方法一 --skip-grant-tables首先，关闭实例这里，只能通过kill mysqld进程的方式。注意：不是mysqld_safe进程，也切忌使用kill -9。复制代码# ps -ef |grep mysqld root 6220 6171 0 08:14 pts/0 00:00:00 /bin/sh bin/mysqld_safe --defaults-file=my.cnf mysql 6347 6220 0 08:14 pts/0 00:00:01 /usr/local/mysql57/bin/mysqld --defaults-file=my.cnf --basedir=/usr/local/mysql57 --datadir=/usr/local/mysql57/data --plugin-dir=/usr/local/mysql57/lib/plugin --user=mysql --log-error=slowtech.err --pid-file=slowtech.pid --socket=/usr/local/mysql57/data/mysql.sock --port=3307 root 6418 6171 0 08:17 pts/0 00:00:00 grep --color=auto mysqld # kill 6347 使用--skip-grant-tables参数，重启实例# bin/mysqld_safe --defaults-file=my.cnf --skip-grant-tables --skip-networking & 设置了该参数，则实例在启动过程中会跳过权限表的加载，这就意味着任何用户都能登录进来，并进行任何操作，相当不安全。建议同时添加--skip-networking参数。其会让实例关闭监听端口，自然也就无法建立TCP连接，而只能通过本地socket进行连接。MySQL8.0就是这么做的，在设置了--skip-grant-tables参数的同时会自动开启--skip-networking。修改密码方法一 updateUSE mysql; UPDATE user SET password = PASSWORD('newpasswd') WHERE user = 'dbadmin' AND host = 'localhost'; FLUSH PRIVILEGES;注意：注意：MySQL 5.7.6版本以下，才能使用此方法来修改密码。从MySQL 5.7.6版本起，user表仅使用authentication_string列代替之前版本中的password列来存储密码。USE mysql; UPDATE user SET password = PASSWORD('newpasswd') WHERE user = 'dbadmin' AND host = 'localhost'; FLUSH PRIVILEGES;方法二 set passwordSET PASSWORD FOR 'dbadmin'@'localhost' = PASSWORD('newpasswd2');注意从MySQL 5.7.6版本开始，MySQL不推荐使用此语法，可能会在将来的版本中将其删除。作为一个代替的解决方案，它使用明文密码如下：SET PASSWORD FOR 'dbadmin'@'localhost' = 'newpasswd2';方法三 alter userALTER USER dbadmin@localhost IDENTIFIED BY 'newpasswd3';方法二 --init-file首先关闭实例创建sql文件,sql中的内容为修改密码的三种方式中的任意一种vim init.sql SET password for 'root'@'localhost'=password('123456');启动mysql服务 mysqld_safe --defaults-file=/etc/my.cnf --init-file=/opt/nginx/html/default/init.sql &实例启动成功，密码就修改完毕。
授权操作详解授权五要素要授予的权限【权限列表中的内容】被授予访问权限的数据库或表【授权级别表中的内容】用户名地址【地址列表中的内容，mysql.user表中的host字段】密码【非必须】授权立即生效flush privileges; # 使用flush privileges;立即使权限生效，否则需要重启服务授权取消授权 语句grant 权限 on 授权范围 to username@'地址' [identified by '密码']; # 授权给账户root所有权限，允许任意地址连接，密码为123456 grant all on *.* to root@'%' identified by '123456'; # all privileges 中的 privileges 可省略 flush privileges; # 回收权限 revoke 权限 on 授权范围 from username@'地址'; # 回收root用户的查询权限 revoke select on *.* from root@'%'; flush privileges; 常用操作注意: 用户名@host 才是唯一用户查看账号root@'%'的权限 show grants for root@'%';删除用户root@'%' drop user 'root'@'%';用户改名、修改hostrename user root@'%' to admin@'localhost';修改用户root@'localhost'密码set password for root@'localhost' = password('123456');导出数据表内容select * into outfile '/tmp/outfile.txt' from loadtest; # 上面的语句只能将文件导出到mysql服务所在的主机上 mysql -h192.168.1.1 -uroot -p123456 -P3306 -e"select * from testdb.testtable;" > /tmp/loadtest.txt # 此方式可将数据导出到客户机上 授权指定列 grant select(id, username, password) on testdb.users to root@'localhost';授权存储过程 grant execute on procedure testdb.pr_add to root@'localhost'; 安全性相关删除任何无关的管理员用户和空密码的管理员用户,有需求再开删除test库(5.7没了),因为这个库默认是所有人都有权限读写,存在漏洞 drop database test;删除存放数据库的表信息。 delete from mysql.db;SSL加密设置,用途就是把数据加密,防止被截取后破解,5.6要加ssl指定文件位置配置,5.7自动开,然后执行命令就可以了.#执行命令,在数据目录创建key文件,下面这个命令是封装了整个创建过程,就不用做那么多复杂的事了 #不过这个命令文件是5.7新加入的,5.6中没有这个命令文件,但是可以放到在5.6上面用,还算方便. /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/data/mysql/data/ #然后看看开启了没有 mysql>SHOW VARIABLES LIKE 'have_ssl'; | have_ssl | YES | #进入mysql,授权ssl专用用户 mysql> grant all privileges on *.* to 'sslroot'@'%' identified by '123123' require ssl; #用ssl专用用户登录 mysql -usslroot -p'123123' -h127.0.0.1 #查看当前状态,SSL已启用 mysql> \s Current user: sslroot@127.0.0.1 SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256不过开启SSL需要谨慎,因为数据进行了加密处理,毫无疑问服务器需要消耗资源来校验密文,官方说明可能会下降25%的性能,还是比较高的.对于高并发的环境自然是性能下降明显,除非安全要求非常高,不然不建议开,一般来说只要授权限制访问IP是内网的就已经可以解决安全问题.权限相关参考内容权限列表权限关键字级别说明ALL PRIVILEGES所有授予所有权限CREATE数据库、表或索引创建数据库、表或索引权限DROP数据库或表删除数据库或表权限GRANT OPTION数据库、表或保存的程序赋予权限选项REFERENCES数据库或表ALTER表更改表，添加字段、索引、修改字段等DELETE表删除数据权限INDEX表索引权限INSERT表插入权限SELECT表查询权限CREATE VIEW视图创建视图权限SHOW VIEW视图查看视图权限ALTER ROUTINE存储过程更改存储过程权限CREATE ROUTINE存储过程创建存储过程权限EXECUTE存储过程执行存储过程权限FILE服务器主机上的文件访问文件访问权限CREATE TEMPORARY TABLES服务器管理创建临时表权限LOCK TABLES服务器管理锁表权限CREATE USER服务器管理创建用户权限PROCESS服务器管理查看进程权限RELOAD服务器管理执行flush-hosts,flush-logs,flush-privileges,flush-status,flush-tables,flush-threads,refresh,reload等命令的权限REPLICATION CLIENT服务器管理复制权限REPLICATION SLAVE服务器管理复制权限SHOW DATABASES服务器管理查看数据库权限SHUTDOWN服务器管理关闭数据库权限SUPER服务器管理执行kil线程权限授权级别grant和revoke可以在几个层次上控制访问权限授权范围说明整个服务器使用 *.*整个数据库使用on database.*特定表使用on database.table特定的列特定的存储过程地址列表关键字说明%匹配所有主机localhostlocalhost不会被解析成IP地址，直接通过UNIXsocket连接127.0.0.1会通过TCP/IP协议连接，并且只能在本机访问::1::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1x.x.x.x指定的地址x.x.x.%指定的地址段
在编写SQL时，灵活运用一些技巧，可以大大简化程序逻辑。插入或替换如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。此时，可以使用REPLACE语句，这样就不必先查询，再决定是否先删除再插入：REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);若id=1的记录不存在，REPLACE语句将插入新记录，否则，当前id=1的记录将被删除，然后再插入新记录。插入或更新如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录，此时，可以使用INSERT INTO ... ON DUPLICATE KEY UPDATE ...语句：INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;若id=1的记录不存在，INSERT语句将插入新记录，否则，当前id=1的记录将被更新，更新的字段由UPDATE指定。插入或忽略如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO ...语句：INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);若id=1的记录不存在，INSERT语句将插入新记录，否则，不执行任何操作。快照如果想要对一个表进行快照，即复制一份当前表的数据到一个新表，可以结合CREATE TABLE和SELECT：-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;新创建的表结构和SELECT使用的表结构完全一致。写入查询结果集如果查询结果集需要写入到表中，可以结合INSERT和SELECT，将SELECT语句的结果集直接插入到指定表中。例如，创建一个统计成绩的表statistics，记录各班的平均成绩：CREATE TABLE statistics ( id BIGINT NOT NULL AUTO_INCREMENT, class_id BIGINT NOT NULL, average DOUBLE NOT NULL, PRIMARY KEY (id) );然后，我们就可以用一条语句写入各班的平均成绩：INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果：> select * from statistics; +----+----------+--------------+ | id | class_id | average | +----+----------+--------------+ | 1 | 1 | 86.5 | | 2 | 2 | 73.666666666 | | 3 | 3 | 88.333333333 | +----+----------+--------------+ 3 rows in set (0.00 sec)转载自：https://www.liaoxuefeng.com/wiki/001508284671805d39d23243d884b8b99f440bfae87b0f4000/001541118432466e6ee1a9fdd134026b281bf014f109264000
基本比较总的来说，MyISAM和InnoDB各有优劣，各有各的使用环境。但是InnoDB的设计目标是处理大容量数据库系统，它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。我觉得使用InnoDB可以应对更为复杂的情况，特别是对并发的处理要比MyISAM高效。同时结合memcache也可以缓存SELECT来减少SELECT查询，从而提高整体性能。
mysql优化之主从复制与读写分离部署1. ?读写分离与主从复制 如上图所示，当web server1/2/3要写入数据时，则向mysql db Master(主服务器）发出写入请求（即写入到master），如果要进入读操作时，则只向从服务器 mysql DB Slave1或2或3发出读取请求。如此将原本读写在同一台服务器的工作量分摊到了一台负责写入，N台负责读取（大部分的网站都是读取请求远大于写入请求），从而从一定程度时实现了负载均衡（如果有N台从服务器，则由主从代理系统自动分配某个具体请求从哪一个从服务器读取）。 读写分离是靠主从复制来实现的。即当一个数据写入到主服务器后，主服务器会将写入信息写入到binlog（二进制日志）里，同时同步（或异步或半同步）到从服务器里。从服务器根据主服务器传来的binlog，生成relay-log(中继日志），然后mysql服务器再用relay-log的信息将数据写入到数据库。 如此做的优点除了实现负责均衡之外，还为我们保留了两份实时热备的数据binlog和relay-log。当服务器发生灾难时，我们可以用他们将数据恢复到任何一个时间点。2.?准备在linux上安装好两台mysql服务器（mysql至少5.5以上），且两台服务器最好在一个机房（在一个局域网内，访问速度远远大于公网，无需流量）。假定主的IP为172.16.0.1，从的IP为172.16.0.2。关闭两台服务器的防火墙（稍后再配置）：iptables stop （这是centos6.5关闭方法，其他系统请问度娘）如果你没有创建数据库，则进行如下操作(1)在主服务器上登录mysql# mysql -uroot -p你的密码(2)创建数据库mysql> create database test1;mysql> create database test2;(3)如果已经有历史数据，则将历史数据备份成sql文件上传到主服务器，运行如下命令mysql> source /disk/test1.sql;（'/disk/test1.sql '换成你自己的路径即可）3. ?主服务器配置mysql已经提供了完美的读写分离与主从复制支持，我们只需要作如下设置即可。1、配置my.cnf# vi /etc/my.cnf(1)去掉log-bin=mysql-bin前的“#”号。这一行的意思是允许mysql使用binlog，同时为主从复制打开了大门。这是关键中的关键中的关键。(2)如果有多个数据库，则添加如下行：binlog-do-db=数据库名1binlog-do-db=数据库名2binlog-do-db=数据库名3binlog-ignore-db=mysql(忽略mysql自身用的数据库)(3)查看server-id是后的数字并记住。slave(从服务器)的server-id不能与该值重复。(4)查看expire-log-day。binlog过期时间，默认为10天。你可以根据你硬盘空间大小及每天产生的数据量修改。如果你有冷备，则一般设成2-3天即可。2、操作数据库(1)在linux命令行下登录mysql# mysql -uroot -p(你的root密码)(2)创建并授权一个帐号mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_account'@'172.16.0.2' identified by '123456'这句的意思是，允许从服务器'172.16.0.2 '使用'slave-account'及'123456'这个帐号密码对对主服务器的所有数据库（.)进行主从复制（'REPLICATION SLAVE').(3)重启mysql使上述配置生效 # etc/init.d/mysql restart 或# service mysqld restart(4)查看主服务器binlog状态mysql> show master stauts;记下File及Position下的值。以备在配置从服务器时使用。注：File：当前binlog的文件名，每重启一次mysql，就会生成一个新binlog文件 Position：当前binlog的指针位置4.?从服务器配置1、配置mysql.cnf# vi /etc/my.cnf(1)修改server-id=2（该值不能与主服务器的server-id同。如果有多个从服务器，则该值顺延）(2)添加如下两行：relay-log-index=slave-relay-bin.index (中继日志的索引文件）relay-log=slave-relay-bin （中继日志的文件前缀）(3)重启mysql使上述配置生效# /etc/init.d/mysql restart(4)登录mysql# mysql -uroot -p你的密码(5)停止主从复制服务mysql> stop slave(6)主从关联配置mysql> change master to master_host='172.16.0.1', #主服务器IP master_user='slave_account ', #主服务器访问从服务器的用户，即上述第三条第2小条第2子条所述帐号 master_password='123456', #主服务器访问从的密码，即上述第三条第2小条第2子条所述密码 master_log_file='mysql-bin.000008', #主服务器起始的binlog文件名，即图2的file master_log_pos=107; #主服务器binlog起始位置，即图2的postion上述每行都特别重要，错一个字符都不行(7)启动从服务mysql> start slave(8) 查看从服务的状态，判断从服务是否生效mysql> show slave status\G; （G参数为纵向显示结果）会显示如下结果Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.110 Master_User: slave_account Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.00008 Read_Master_Log_Pos: 107 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.00008 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 264 Relay_Log_Space: 409 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:Replicate_Ignore_Server_Ids: Master_Server_Id: 1如果Slave_IO_Running和Slave_SQL_Running都显示YES，则表示从与主的读写通讯正常、主动复制已经运行，则表明我们主从复制已经设置成功。如果出现故障，则看下节“故障排除”。然后，你再在主服务器插入一条数据，看看从服务器相应数据库里有没有做相应更新。如果有，则大功告成。5.?权限问题虽然某些mysql版本不进行如下操作，可能也能正常运行，但我还是强列建议你设置。否则，必有安全之虑。1、为master分配select、insert、update、delete权限mysql>GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO '数据库的帐号'@'WEB服务器的IP' identified by '密码';注意，虽然master只负责写，但也必须有select权限。原因是使用事务时，需要从master查询，二是update/delete这些命令都需要使用select权限。2、为slave分配select权限 mysql> GRANT SELECT ON *.* TO '数据库的帐号'@'WEB服务器的IP' identified by '密码';这样设置也有一个弊端，就是当主宕机时，读写分离机制分主动将写操作转到从上来，这时，就会出现写不正常的情况。所以，slave要分配哪些权限，还看你自己选择。6.?防火墙配置刚才我们为了调试方便，先关闭了防火墙，但这样做，非常很不安全。特别是数据库服务器，关系公司的身家性命，更不可小视。主从复制的防火墙，还是比较好设置的，如果你只有一台web服务器，则只需要在主、从服务器的iptables里开放2个授权即可。先在主服务器执行如下命令：# iptables INPUT -s 10.121.0.220 -j ACCEPT （允许从服务器访问）# iptables INPUT -s 10.121.2.142 -j ACCEPT (允许web服务器访问）# service iptables save (保存上述规则)# service iptables restart (重启iptables)在从中将第一条换成主的IP即可。设置完后，再在从上用show slave status看看主从复制是否正常。7.?故障排除设置过程中，可能会出现如下几个故障（1） Slave_IO_Running: No（2） Slave_IO_Running: Connect（3）Last_IO_Error: error connecting to master 'slave-account@192.168.0.110:3306' - retry-time: 60 retries: 8640其实上述故障，都是由IO不正常造成的，请从如下几个步骤着手检查。a.配置完主或从的my.cnf后，是否重启了mysql服务。如果重启还不成功，则可以用reboot命令重启服务器后再试b.change master那一步所填写的信息是否正确。（4）Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).如上是说主、从使用了相同的server-id，进入my.cnf检查，改正之，然后重启mysql服务。