• pt老虎机开户送体验金pt老虎机开户送体验金【天天存天天送】

  • 发布时间:2016-01-22 14:38 | 作者:yc | 来源:互联网 | 浏览:1200 次
  • pt老虎机开户送体验金pt老虎机开户送体验金【天天存天天送】

    以是这儿没有了filesort,数据准确以是应该是猜想(1)建立

    CREATE TABLE employees (

    emp_no INT(11) NOT NULL,

    birth_date DATE NOT NULL,

    first_name VARCHAR(14) NOT NULL,

    last_name VARCHAR(16) NOT NULL,

    gender ENUM(‘M’,’F’) NOT NULL,

    hire_date DATE NOT NULL,

    PRIMARY KEY (emp_no)

    )

    COLLATE=’utf8’

    ENGINE=InnoDB;

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10001, ‘1953-09-02’, ‘Georgi’, ‘Facello’, ‘M’, ‘1986-06-26’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10002, ‘1964-06-02’, ‘Bezalel’, ‘Simmel’, ‘F’, ‘1985-11-21’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10003, ‘1959-12-03’, ‘Parto’, ‘Bamford’, ‘M’, ‘1986-08-28’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10004, ‘1954-05-01’, ‘Chirstian’, ‘Koblick’, ‘M’, ‘1986-12-01’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10005, ‘1955-01-21’, ‘Kyoichi’, ‘Maliniak’, ‘M’, ‘1989-09-12’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10006, ‘1953-04-20’, ‘Anneke’, ‘Preusig’, ‘F’, ‘1989-06-02’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10007, ‘1957-05-23’, ‘Tzvetan’, ‘Zielinski’, ‘F’, ‘1989-02-10’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10008, ‘1958-02-19’, ‘Saniya’, ‘Kalloufi’, ‘M’, ‘1994-09-15’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (10011, ‘1972-02-29’, ‘He’, ‘Rick’, ‘M’, ‘1991-02-20’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64887, ‘1961-05-15’, ‘Rafols’, ‘Suomi’, ‘F’, ‘1987-06-05’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64888, ‘1962-05-16’, ‘Kristof’, ‘Marchegay’, ‘F’, ‘1988-11-10’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64889, ‘1953-05-08’, ‘Emdad’, ‘Pauthner’, ‘M’, ‘1985-09-07’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64890, ‘1957-08-21’, ‘Leni’,开户送金 ‘Kilgore’, ‘M’, ‘1995-08-31’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64891, ‘1954-09-27’, ‘Ioana’, ‘Pepe’, ‘F’, ‘1990-08-19’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64892, ‘1964-12-05’, ‘Mayumi’, ‘Tyugu’, ‘F’, ‘1988-03-19’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64893, ‘1953-11-27’, ‘Yoshimitsu’, ‘Billawala’, ‘F’, ‘1986-06-07’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64894, ‘1955-09-19’, ‘Mori’, ‘Weedman’, ‘F’, ‘1999-01-29’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64895, ‘1962-02-24’, ‘Dietrich’, ‘Foote’, ‘M’, ‘1988-08-07’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64896, ‘1960-12-31’, ‘Ingemar’, ‘Schieder’, ‘M’, ‘1995-10-25’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64897, ‘1956-04-24’, ‘Aksel’, ‘Denna’, ‘M’, ‘1986-08-31’);

    INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES (64898, ‘1961-04-13’, ‘Sudhanshu’, ‘Hutton’, ‘M’, ‘1996-02-09’);

    1:完成row_number.

    650) this.width=650;" src="http://img.blog.csdn.net/" alt="这儿写图像描画" title="" style="border:none;" />

    2:取前3条:

    650) this.width=650;" src="http://img.blog.csdn.net/" alt="这儿写图像描画" title="" style="border:none;" />

    3:对first_name排序取前三条:

    650) this.width=650;" src="http://img.blog.csdn.net/" alt="这儿写图像描画" title="" style="border:none;" />

    在没有排序时获得的事准确的三条数据,可是排序后获得的是21条(悉数数据),古怪

    猜想:

    (1)实行了where可是在排序时是将统统的数据都拿出来排序的

    (2)没有实行where,直接排序输出了即跳过了where.

    看explain:

    650) this.width=650;" src="http://img.blog.csdn.net/" alt="这儿写图像描画" title="" style="border:none;" />

    MySQL官方手册阐明:

    Using where

    A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.

    Using filesort

    MySQL mu九五至尊娱乐手机版pt老虎机开户送体验金pt老虎机开户送体验金【天天存天天送】st do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause.

    然后看到运用了filesort是要运用外部空间进行排序的,这儿我是这么想的,sql实行是从右到左的以是应该是order by跳过了where,以是猜想是(2).

    因为运用了filesort以是树立first_name索引试下:

    650) this.width=650;" src="http://img.blog.csdn.net/" alt="这儿写图像描画" title="" style="border:none;" />

    MySQL官方手册阐明:

    Using index

    The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

    能够看到树立了first_name索引今后没有filesort了,并且数据也准确了

    在高机能mysql第三版中的6.7.9节中有这么一段话:因为order by引入了文件排序,而where前提是在文件排序操作之前取值的,以是这条查询会回来表中的悉数纪录,处置惩罚这个疑问的措施是让变量的赋值和取值发生在实行查询的同一时代:如图:

    650) this.width=650;" src="http://img.blog.csdn.net/" alt="这儿写图像描画" title="" style="border:none;" />

    可是我仍是支撑做个子查询,这么就不必思虑太多了哈哈,有点懒了

    不过高机能mysql第三版这个阐明和我的主见不同等先放着,下次深化懂得有知道同砚期望评论下

    感谢群里的大年夜白菜和explain

    关于filesort的优化能够在网上找下,这儿有对照好的

    http://www.ccvita.com/169.html

    http://blog.csdn.net/yangyu/article/details/

    http://bbs.chinaunix.net/thread--1-1.html

    高机能mysql第三版书籍下载:http://download.csdn.net/detail/u/

    这篇文章出自 “rick” 博客,请必须保存此出处http://.blog.51cto.com//

  • 相关内容

友情链接: