性能下降SQL慢、执行时间长、等待时间长
- 查询语句写的烂
- 索引失效
- 关联查询太多join(设计缺陷或不得已的需求,除非你能干的过你的产品经理)
- 服务器调优及各个参数设置(缓冲、线程数等)
常见通用的Join查询
SQL执行顺序
SELECT DISTINCT <select_list>FROM <left_table> <join_type>JOIN <right_table> ON <join_condition>WHERE <where_condition>GROUP BY <group_by_list>HAVING <having_condition>ORDER BY <order_by_condition>LIMIT <limit_number>
FROM <left_table>ON <join_condition><join_type> JOIN <right_table> WHERE <where_condition>GROUP BY <group_by_list>HAVING <having_condition>SELECT DISTINCT <select_list>ORDER BY <order_by_condition>LIMIT <limit_number>
SQL JOINs
-- 建表和数据SQLCREATE TABLE `tbl_dept` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `deptName` VARCHAR(30) DEFAULT NULL, `locAdd` VARCHAR(40) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `tbl_emp` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) DEFAULT NULL, `deptId` INT(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_dept_id` (`deptId`) #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `tbl_dept` (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
- 练习
- 1、A、B两表共有
- select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;
- 2、A、B两表共有+A的独有
- select * from tbl_emp a left join tbl_dept b on a.deptId = b.id
- 3、A、B两表共有+B的独有
- select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
- 4、A的独有
- select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
- 5、B的独有
- select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
- 6、AB全有
- MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
- left join + union(可去除重复数据)+ right join
- 实现如下面代码
- 7、A的独有 + B的独有
-- 6、AB全有SELECT *FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.idUNION SELECT *FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id;-- 7、A的独有 + B的独有SELECT *FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.idWHERE b.id IS NULLUNIONSELECT *FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.idWHERE a.`deptId` IS NULL;
索引简介
什么是索引
索引的优势
- 类似大学图书馆建书目索引, 提高数据检索的效率 ,降低数据库的IO成本
- 通过索引列对数据进行排序 ,降低数据排序的成本 ,降低了CPU的消耗
索引的劣势
- 实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
- 索引只是提高效率的一个因素,如果你的MySQL有大数据量的表, 就需要花时间研究建立最优秀的索引,或优化查询语句
MySQL索引分类
- 单值索引
- 唯一索引
- 复合索引
- 基本语法
- 创建,两种方式
- CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));
- 如果是CHAR,VARCHAR类型,length 可以小于字段实际长度;
- 如果是 BLOB 和 TEXT 类型,必须指定 length。
- ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
- 删除
- DROP INDEX [indexName] ON mytable;
- 查看
- SHOW INDEX FROM table_name\G
- 使用 Alter 命令
- 有四种方式来添加数据表的索引:
- ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
- 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
- ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
- 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
- ALTER TABLE tbl_name ADD INDEX index_name (column_list)
- ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
- 该语句指定了索引为 FULLTEXT ,用于全文索引。
MySQL索引结构
- BTree索引
- 检索原理
-
- 【初始化介绍】
- 一颗b+树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个 数据项 ( 深蓝色 所示)和 指针 ( 黄色 所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3。 P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。
- 真实的数据存在于叶子节点 即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。
- 非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项 ,如17、35并不真实存在于数据表中。
- 【查找过程】
- 如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。
- 真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。
未完待续
来自:http://www.jianshu.com/p/2717d4fbd474
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。