mysql数据库优化

MySQL操作

①　Insert into table_name [(column [,column ......])] values(value[ ,value....]);
注意事项

1)插入的数据应与字段的数据类型相同。

2)数据的大小应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

3)在values中列出的数据位置必须与被加入的列的排列位置相对应。

4)字符和日期型数据应包含在单引号中。

5)插入空值，不指定或insert into table value(null)

②　Update table_name set col_name1=expr1 [,col_name2=expr2 ....] [where where_definition];

1)Update 语法可以用新值更新原有表行中的各列

2)Set子句只是要修改那些列和要给予哪些值

3)Where子句指定应更新哪些行。如没有where子句，则更新所有行

③　delete from tbl_name     [WHERE where_definition]
注意事项:

1)如果不使用where子句，将删除表中所有数据。

2)Delete语句不能删除某一列的值（可使用update）

3)使用delete语句仅删除记录，不删除表本身。如要删除表，使用drop table语句。

4)同insert和update一样，从一个表中删除记录将引起其它表的参照完整性问题，在修改数据库数据时，头脑中应该始终不要忘记这个潜在的问题。

5)删除表中数据也可使用TRUNCATE TABLE 语句，它和delete有所不同，参看mysql文档。

④　SELECT [DISTINCT] *|{column1, column2. column3..} FROM tablename;

1) Select 指定查询哪些列的数据。

2)column指定列名。

3)*号代表查询所有列。

4)From指定查询哪张表。

5)DISTINCT可选，指显示结果时，是否剔除重复数据

⑤　在select语句中可使用表达式对查询的列进行运算

6)SELECT *|{column1｜expression, column2｜expression，..} FROM tablename;

⑥　在select语句中可使用as语句

7)SELECT columnname as 别名 from 表名;
⑦　使用order by 子句排序查询结果。
8)SELECT column1, column2. column3.. ROM table order by column asc|desc

i. Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的列名。

ii. Asc 升序、Desc 降序

iii. ORDER BY 子句应位于SELECT语句的结尾。

数据库设计

①　数据库设计
②　SQL语句优化
③　数据库参数设置
④　恰当的硬件资源和操作系统 这个顺序也表现了这四个工作对性能影响的大小。

SQL语句优化

①　通过show status 命令了解各种SQL的执行效率
②　定位执行效率最低的 SQL语句（重点select）
③　通过explain分析低效率的SQL语句的执行情况
④　确定问题并采取响应的优化措施

Show status like ‘Com_%’;

①　Com_select , Com_insert ,Com_update , Com_delete 通过该四个参数可以了解到当前应用是已插入更新为主，还是以查询操作为主，以及各类的SQl大致的执行比例是多少。
②　Connections 试图连接MySQL服务器的次数
③　Update：服务器工作的时间
④　Slow_queries : 慢查询的次数（默认是慢查询时间10s）

 

SQL语句优化-explain

Explain select * from emp where ename=“zrlcHd”
会产生如下信息：
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描出的行数(估算的行数)
Extra:执行情况的描述和说明

建立索引

①　Create 【UNIQUE| FULLTEXT】index index_name on tbl_name
②　Alter table table_name ADD INDEX 【index_name】(index_col_name

删除索引

③　DROP INDEX index_name ON　tbl_name’；
④　Alter table table_name drop index index_name;
⑤　删除主键索引：alter table 表名 drop primary key ;

查询索引（查看数据表是否建立索引）

①　Show index(es) from table_name
②　Show keys from table_name;
③　Desc table_name;

MySQL数据库索引类型

①　主键索引，主键自动的为主索引（类型为primary）
②　唯一索引（UNIQUE）
③　普通索引（INDEX）
④　全文索引（FULLTEXT）【适用于MyISAM】

Sphinx+中文分词 coreseek【sphinx的中文版】
综合使用=》复合索引
索引的使用
①　查询要使用索引最重要的条件是查询条件中需要使用索引
②　一下情况有可能使用索引：
1)对于使用like的查询，查询如果是“%aaa”，不会使用到索引；“aaa%”会用到索引
2)对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用
③　下列的表将不使用索引
1)如梭条件中有 or，即使其中有条件带索引也不会使用
2)对于多列索引，不是使用的第一部分，则不会使用索引
3)Like查询是以%开头
4)如果是列类型是字符串，那一定是在条件中将数据使用引号引起来。否则不使用索引。（添加时，字符串必须加引号）
5)如果MySQL估计使用全表扫描要比使用索引快，则不使用索引。

常见SQL优化
大批量插入数据（MySQL管理员）
①　对于MyISAM
1)Alter table table_name disable keys;
2)Loading data //insert语句
3)Alter table table_name enable keys;

②　对于Innodb
1)将要导入的数据按照主键排序
2)Set unique_checks=0 //关闭唯一性的校验
3)Set autocommit=0 //关闭自动提交
③　优化group by语句
默认情况，MySQL对所有的group by col1, col2 进行排序
这与在查询中指定的order by col1,col2类似
④　连接查询代替子查询（因为join，MySQL不需要在内存中创建临时表）
⑤　如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用 到索引，如果没有索引，则应该考虑增加索引(与环境相关 讲解)
select * from 表名 where 条件1=‘’ or 条件2=‘tt’
⑥　对于MyISAM存储引擎的表，若经常做删除和修改记录的操作，要定时执行 【optimize table table_name;】 //清除数据表数据删除后遗留的碎片

MySQL数据库选择合适的存储引擎
①　MYISAM:默认的MySQL存储引擎。主要以读和插入操作为主，只有很少的更新和删除操作。都事务的完整性要求不是很高。其优势是访问的速度快。
②　InnoDB:提供了具有提交、回滚和崩溃恢复能力的食物安全。但是对于MyISAM，写的处理效率差一些并且会占用更多的磁盘空间。
③　MyISAM不支持事务、也不支持外键，但其访问速度快，对事 务完整性没有要求

④　InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事 务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

⑤　MEMORY存储引擎使用存在内存中的内容来创建表。 每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉 。 

①如果你的应用是不需要事务，处理的只是基本的CRUD操作，那么MyISAM是不二选择
②一般来说，如果需要事务支持，并且有较高的并发读写 频率，InnoDB是不错的选择。
③Heap 存储引擎就是将数据存储在内存中，由于没有磁盘I./O的等待，速度极快。但由于是内存存储引擎，所做的任何修改在服务器重启后都将消失。(经典用法 用户的在线状态.)
修改存储引擎: ALTER TABLE `表名` ENGINE = 储存引擎;
什么是事务？ 事务是保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。应用：网上转账
事务和锁：当执行事务操作时（dml语句），MySQL会在被作用的表上加锁，防止其他用户修改表结构。
提交事务：当执使用commit语句可以提交事务.当执行了commit语句子后,会确认事务的变化、结束事务、删除保存点、释放锁，当使用commit语句结束事务子后，其它会话将可以查看到事务变化后的新数据
回退事务：在介绍回退事务前，我们先介绍一下保存点(savepoint)的概念和作用.保存点是事务中的一点.用于取消部分事务，当结束事务时，会自动的删除该事务所定义的所有保存点.当执行rollback时，通过指定保存点可以回退到指定的点,这里我们作图说明
 mysql 数据库控制台事务的几个重要操作
1)start transaction //开始一个事务
2)savepoint 保存点名 //设置保存点
3)rollback to 保存点名 //取消部分事务
4)rollback //取消全部事务
5)commit //提交事务.
什么是子查询？嵌套在其他SQL语句中的select语句，也叫嵌套查询
 在多行子查询中使用all操作符
①　请思考:如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
②　select ename,sal,deptno from emp where sal>all (select sal from emp where deptno=30);

③　扩展要求：大家想想还有没有别的查询方法.
④　Select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);
■ 在多行子查询中使用any操作符

①　请思考:如何显示工资比部门30的任意一个员工的工资高的员工的姓名、工资和部 门号
②　select ename,sal,deptno from emp where sal> any(select sal from emp where deptno=30)
扩展要求：
③　大家想想还有没有别的查询方法.
④　select ename,sal,deptno from emp where sal> (select min(sal) from emp where deptno=30)


1.查看当前会话隔离级别
 select @@tx_isolation;
 2.查看系统当前隔离级别
 select @@global.tx_isolation;
 3.设置当前会话隔离级别
 set session transaction isolation level repeatable read;
 4.设置系统当前隔离级别
 set global transaction isolation level repeatable read;
5. mysql 默认的事务隔离级别是 repeatable read ,一般情况下，没有特殊要求，没有必要修改（因为该级别可以满足绝大部分项目需求）



数据库维护的完整性--约束
约束用于确保数据库数据满足特定的商业规则：
MySQL中约束包括：not null, unique ,primary key ,foreign key ,check五种