您的位置:新葡亰496net > 网络数据库 > 创建高性能的索引,mysql索引建立和优化

创建高性能的索引,mysql索引建立和优化

发布时间:2019-09-15 20:15编辑:网络数据库浏览(170)

    一、MySQL数据迁移(由远端主机迁移到本地)

    mysql索引建立和优化

    引子

    对于一条SQL,开发同学最先关心的啥? 我觉得还不到这个SQL在数据库的执行过程,而是这条SQL是否能尽快的返回结果,在SQL的生命周期里,每一个环节都有足够的优化空间,但是我们有没有想过,SQL优化的本质是啥?终极目标又是啥?其实优化本质上就是减少SQL对资源的消耗和依赖,正如数据库优化的终极目的是Do nothing in database一样,SQL优化的终极目的是Consume no resource。

    数据库资源有两个特性:

    • 首先资源是有限的,大家都抢着用就会有瓶颈的,所以SQL的瓶颈可能是由资源紧张产生的。
    • 其次资源是有代价的,并且代价各异,比如内存的时延100ns, SSD100us,SAS盘10ms,网络更高,那么访问CPU L1/L2/L3 cache的代价就比访问内存的要低,访问内存资源的代价要比访问硬盘资源的代价,所以SQL的瓶颈也可能是访问了代价比较高的资源导致的。

    现代计算机体系下,机器上粗粒度的资源就那么几种,无非是CPU,内存,硬盘,和网络。那么我们来看下SQL需要消耗哪些资源:

    • 比较、排序、SQL解析、函数或逻辑运算需要用到CPU;
    • 缓存数据访问,临时数据存放需要用到内存;
    • 冷数据读取,大数据量的排序和关联,数据写入落盘,需要访问硬盘;
    • SQL请求交互,结果集返回需要网络资源。

    那么SQL优化思路自然是减少SQL的解析,减少复杂的运算,减少数据处理的规模,减少对物理IO的依赖,减少服务器和客户端的网络交互, 本文的每一节都解决上面的一两点,索引策略的组合最大化提升SQL优化性能:

    • 独立的列: 减少SQL的解析
    • 前缀索引和索引选择性: 减少数据处理的规模,减少对物理IO的依赖
    • 多列索引:减少对物理IO的依赖
    • 选择和是的索引列顺序: 减少数据处理的规模,减少对物理IO的依赖
    • 聚簇索引: 减少数据处理的规模,减少对物理IO的依赖
    • 覆盖索引: 减少对物理IO的依赖
    • 使用索引扫描来做排序: 减少复杂的运算
    • 返回必要的列: 减少对物理IO的依赖,减少服务器和客户端的网络交互

    在学习MySQL索引之前,最好先学习MySQL索引背后的数据结构及算法原理。

    马上就要到国庆节了,好是期待呀。最近一直忙成狗,急需一个长假调整一下自己的心境和状态

    1、导出数据库
    mysqldump -u root -p db > dump_db_date.sql
    root: 账户
    db: 需要导出的数据库名

    建立索引的几大原则

    最左前缀匹配原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 ,如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整(参考原则2)。但是mysql查询优化器可能通过优化调整顺序从而使用索引,但是写sql语句时还是按照此原则;= 和 in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式;尽量选择区分度高的列作为索引,区分度的公式是count(distinct

    独立的列

    独立的列是指索引列不能是表达式的一部分,也不能是函数的参数。

    例如:下面这个查询无法使用actor_id列的索引:

    mysql> explain select actor_id from actor where actor_id   1 = 5;
     ---- ------------- ------- ------- --------------- --------------------- --------- ------ ------ -------------------------- 
    | id | select_type | table | type  | possible_keys | key                 | key_len | ref  | rows | Extra                    |
     ---- ------------- ------- ------- --------------- --------------------- --------- ------ ------ -------------------------- 
    |  1 | SIMPLE      | actor | index | NULL          | idx_actor_last_name | 137     | NULL |  200 | Using where; Using index |
     ---- ------------- ------- ------- --------------- --------------------- --------- ------ ------ -------------------------- 
    

    凭肉眼容易看出where的表达式其实等价于actor_id=4,但是MySQL无法自动解析这个函数。所以应该简化where条件:始终将索引列单独放在比较符号的一侧,使用索引的正确写法如下,此时使用主键索引:

    mysql> explain select actor_id from actor where actor_id = 4;
     ---- ------------- ------- ------- --------------- --------- --------- ------- ------ ------------- 
    | id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
     ---- ------------- ------- ------- --------------- --------- --------- ------- ------ ------------- 
    |  1 | SIMPLE      | actor | const | PRIMARY       | PRIMARY | 2       | const |    1 | Using index |
     ---- ------------- ------- ------- --------------- --------- --------- ------- ------ ------------- 
    

    下面是另外一个常见的错误:

    mysql>select ...  where to_days(current_date)-to_days(date_col) <=10;
    

    新葡亰496net 1

    2、将导出的dump_db_date.sql文件scp到本地

    col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time)

    前缀索引和索引选择性

    有时候索引很长的字符列,让索引变得大且慢。通常索引开始的部分字符,可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。 索引的选择性是指:不重复的索引值(也称为基数,Cardinality)和数据表的记录总数(#T)的比值,范围是 1/#T ~ 1。索引的选择性越高则查询效率越高,因为选择性高的索引让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。

    一般情况下某个列前缀的选择性如果足够高,也是可以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。如下所示,varchar(4000)类型的comment列最多只能建前缀长度为255的索引。

    mysql> show create table shop;
     ------- ------------------------------------------------------------------------------------------------------------------------------- 
    | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
     ------- ------------------------------------------------------------------------------------------------------------------------------- 
    | shop  | CREATE TABLE `demo` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
      `comment` varchar(4000) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_comment` (`comment`(255))
    ) ENGINE=InnoDB AUTO_INCREMENT=20001 DEFAULT CHARSET=utf8              
    |
     ------- ------------------------------------------------------------------------------------------------------------------------------- 
    

    诀窍在于选择足够长的前缀以保证较高的选择性,同时又不能太长(以便节约空间)。前缀应该足够长,以使得前缀索引的选择性接近于索引整个列。换句话说,前缀的”基数“应该接近于完整列的”基数“。

    为了决定前缀的合适长度,需要找到最常见的值的列表,然后和最常见的前缀列表进行比较。在示例数据Sakila没有合适的例子,所以我们从表city生成一个示例表,生成足够的数据用来演示:

    mysql> CREATE TABLE city_demo (city VARCHAR(50) NOT NULL);
    
    mysql> INSERT INTO city_demo(city) SELECT city from city;
    Records: 600  Duplicates: 0  Warnings: 0
    

    重复执行下面的sql 五次:

    mysql> insert into city_demo(city) select city from city_demo;
    Records: 600  Duplicates: 0  Warnings: 0
    

    执行下面sql 随机分布数据:

    mysql> update city_demo set city = (select city from city order by RAND() limit 1);
    Rows matched: 19200  Changed: 19170  Warnings: 0
    

    示例数据集分布是随机生成的,与你的结果会有所不同,但是对于结论没有影响。首先,我们找到最常见的城市列表:

    mysql> select count(*) as cnt, city from city_demo group by city order by cnt desc limit 10;
     ----- ------------------- 
    | cnt | city              |
     ----- ------------------- 
    |  60 | London            |
    |  49 | Skikda            |
    |  48 | Izumisano         |
    |  47 | Valle de Santiago |
    |  47 | Tegal             |
    |  46 | Goinia            |
    |  46 | Tychy             |
    |  46 | Idfu              |
    |  46 | Clarksville       |
    |  46 | Paarl             |
     ----- ------------------- 
    

    注意到,上面每个值都出现了46-60次,现在查找到最频繁出现的城市前缀,先从3个前缀字母开始:

    mysql> select count(*) as cnt,left(city,3) as pref from city_demo group by pref order by cnt desc limit 10;
     ----- ------ 
    | cnt | pref |
     ----- ------ 
    | 453 | San  |
    | 195 | Cha  |
    | 161 | Tan  |
    | 157 | Sou  |
    | 148 | Shi  |
    | 146 | Sal  |
    | 145 | al-  |
    | 140 | Man  |
    | 137 | Hal  |
    | 134 | Bat  |
     ----- ------ 
    

    每个前缀都比原来的城市出现的次数要多,因此唯一前缀比唯一城市要少得多。然后我们增加前缀长度,直到这个前缀的选择性接近完整列的选择性。经过实验发现前缀长度为7时最为合适:

    mysql> select count(*) as cnt,left(city,7) as pref from city_demo group by pref order by cnt desc limit 10;
     ----- --------- 
    | cnt | pref    |
     ----- --------- 
    |  74 | Valle d |
    |  70 | Santiag |
    |  61 | San Fel |
    |  60 | London  |
    |  49 | Skikda  |
    |  48 | Izumisa |
    |  47 | Tegal   |
    |  46 | Tychy   |
    |  46 | Goinia  |
    |  46 | Idfu    |
     ----- --------- 
    

    计算合适的前缀长度的另外一个方法就是计算完整列的选择性,并使前缀的选择性接近于完整列的选择性。下面展示计算完整列的选择性:

    mysql> select count(distinct city)/count(*) from city_demo;
     ------------------------------- 
    | count(distinct city)/count(*) |
     ------------------------------- 
    |                        0.0312 |
     ------------------------------- 
    

    通常来说(尽管也有例外情况),这个例子中如果前缀的选择性能够接近于0.031,基本上就可用了。可以在一个查询中针对不同的前缀长度进行计算,这对于大表非常有用。下面给出了如何在同一个查询中计算不同前缀长度的选择性:

    mysql> select 
        -> count(distinct left(city,3))/count(*) as sel3,
        -> count(distinct left(city,4))/count(*) as sel4,
        -> count(distinct left(city,5))/count(*) as sel5,
        -> count(distinct left(city,6))/count(*) as sel6,
        -> count(distinct left(city,7))/count(*) as sel7
        -> from city_demo;
     -------- -------- -------- -------- -------- 
    | sel3   | sel4   | sel5   | sel6   | sel7   |
     -------- -------- -------- -------- -------- 
    | 0.0239 | 0.0293 | 0.0305 | 0.0309 | 0.0310 |
     -------- -------- -------- -------- -------- 
    

    查询显示当前缀长度达7的时候,再增加前缀长度,选择性提升的幅度已经很小,但是增加的前缀索引占用空间。

    只看平均选择性是不够的,也有例外的情况,需要考虑最坏情况下的选择性。平均选择性会让你认为前缀长度为4或者5的索引已经足够了,但如果数据分布很不均匀,可能就会有陷阱。如果观察前缀为4的最常出现城市的次数,可以看到明显不均匀:

    mysql> select count(*) as cnt,left(city,4) as pref from city_demo group by pref order by cnt desc limit 5;
     ----- ------ 
    | cnt | pref |
     ----- ------ 
    | 198 | Sant |
    | 186 | San  |
    | 124 | Sout |
    | 106 | Toul |
    | 102 | Chan |
     ----- ------ 
    

    如果前缀是4个字节,则最常出现的前缀的出现次数比最常出现的城市的出现次数要大很多。即这些值的选择性比平均选择性要低。如果有比这个随机生成的示例更真实的数据,就更有可能看到这种现象。例如在真实的城市名上建一个长度为4的前缀索引,对于以“San”和“New”开头的城市的选择性就会非常糟糕,因为很多城市都以这两个词开头。

    在上面的示例中,已经找到了合适的前缀长度,下面演示一下如何创建前缀索引:

    mysql>alter table city_demo add index idx_city(city(7));
    

    前缀索引是一种能使索引更小更快的有效办法,但另一方面也有其缺点:MySQL无法使用前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描

    有时候后缀索引(suffix index)也有用途(例如,找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引,但是可以把字符串反转后存储,并基于此建立前缀索引。可以通过触发器来维护这种索引。

    期待.jpg

    3、在本地机器建立新数据库
    mysql > create database new_db;

    ’2014-05-29’就不能使用到索引,原因很简单,b 树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time

    unix_timestamp(’2014-05-29’);尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可

     

    注意:
    前缀索引在Oder by 和 Group by操作的时候无法使用;
    hash索引不适用于范围查询,例如<,>,<=,>=等操作。如果使用Memory/Heap引擎并且where条件中不使用"="进行索引列,那么不会用到索引。Memory/Heap引擎只有在"="条件下才会使用索引;
    mysql> show create table rentalG *************************** 1. row *************************** Table: rental Create Table: CREATE TABLE `rental` ( `rental_id` int(11) NOT NULL AUTO_INCREMENT, `rental_date` datetime NOT NULL, `inventory_id` mediumint(8) unsigned NOT NULL, `customer_id` smallint(5) unsigned NOT NULL, `return_date` datetime DEFAULT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`rental_id`), UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`), KEY `idx_fk_inventory_id` (`inventory_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `idx_fk_staff_id` (`staff_id`), CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE, CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE, CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

    mysql> alter table rental drop index rental_date; Query OK, 16044 rows affected (0.92 sec) Records: 16044 Duplicates: 0 Warnings: 0
    ql> alter table rental add index idx_rental_date(rental_date ,inventory_id,customer_id); Query OK, 16044 rows affected (0.48 sec) Records: 16044 Duplicates: 0 Warnings: 0
    //匹配全值,对索引中的所有列都执行具体值,即是对索引中的所有列都有等值匹配的条件。 mysql> explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343G(等值查询的话查询条件可以乱序) *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: ref //使用一般索引,所以此时是ref,使用唯一性索引或者primary key进行查询时是const possible_keys: idx_fk_inventory_id,idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 13 ref: const,const,const //显示哪些字段或者常量用来和key配合从表中查询记录出来 rows: 1 Extra: 1 row in set (0.00 sec)
    mysql> alter table rental drop index idx_rental_date; Query OK, 16044 rows affected (0.65 sec) Records: 16044 Duplicates: 0 Warnings: 0
    mysql> alter table rental add unique index idx_rental_date(rental_date ,inventory_id,customer_id); Query OK, 16044 rows affected (0.56 sec) Records: 16044 Duplicates: 0 Warnings: 0
    mysql> explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: const //使用唯一性索引或者primary key进行查询时是const possible_keys: idx_rental_date,idx_fk_inventory_id,idx_fk_customer_id key: idx_rental_date key_len: 13 ref: const,const,const rows: 1 Extra: 1 row in set (0.00 sec)
    注意:等号或者in可以乱序 mysql> explain select * from rental where customer_id = 5 and rental_date = '2006-05-30 10:00:00'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: ref possible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 10 ref: const,const rows: 1 Extra: 1 row in set (0.01 sec) mysql> explain select * from rental where rental_date = '2006-05-30 10:00:00' and customer_id = 5G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: ref possible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 10 ref: const,const rows: 1 Extra: 1 row in set (0.00 sec)

    //匹配值的范围查询,对索引的值能够进行范围查询 mysql> explain select * from rental where customer_id >=373 and customer_id <= 400G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: range possible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 745 Extra: Using where //Using where表示优化器除了根据索引来加速访问之外,还根据索引回表查询数据。 1 row in set (0.00 sec)
    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.

    //匹配最左匹配,仅仅使用索引中的最左边列进行查找。

    mysql> show create table paymentG *************************** 1. row *************************** Table: payment Create Table: CREATE TABLE `payment` ( `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `customer_id` smallint(5) unsigned NOT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `rental_id` int(11) DEFAULT NULL, `amount` decimal(5,2) NOT NULL, `payment_date` datetime NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`payment_id`), KEY `idx_fk_staff_id` (`staff_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `fk_payment_rental` (`rental_id`), CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE, CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`新葡亰496net,staff_id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
    mysql> alter table payment add index idx_payment_date(payment_date,amount,last_update); Query OK, 16049 rows affected (2.85 sec) Records: 16049 Duplicates: 0 Warnings: 0
    mysql> explain select * from payment where payment_date = '2006-02-14 15:16:03' and last_update='2006-02-15 22:12:32'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ref possible_keys: idx_payment_date key: idx_payment_date key_len: 8 ref: const rows: 182 Extra: Using where 1 row in set (0.00 sec)
    mysql> explain select * from payment where amount = 3 and last_update='2006-02-15 22:12:32'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ALL //不是使用最左匹配,全表扫描 possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 16470 Extra: Using where 1 row in set (0.00 sec)
    //仅仅对索引进行查询,当查询的字段在索引的字段中时,查询的效率更高。不必回表数据。 mysql> explain select last_update from payment where payment_date = '2006-02-14 15:16:03' and amount=3.98G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ref possible_keys: idx_payment_date key: idx_payment_date key_len: 11 ref: const,const rows: 8 Extra: Using index //using index表示直接通过访问索引就足够获取所需要的数据,无需通过索引回表,using index也就是常说的覆盖索引扫描。只访问必须访问的数据,在一般情况下,减少不必要的数据访问能够提高效率。 1 row in set (0.04 sec)
    //匹配列前缀 mysql> show create table film_textG *************************** 1. row *************************** Table: film_text Create Table: CREATE TABLE `film_text` ( `film_id` smallint(6) NOT NULL, `title` varchar(255) NOT NULL, `description` text, PRIMARY KEY (`film_id`), FULLTEXT KEY `idx_title_description` (`title`,`description`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> create index idx_title_desc_part on film_text(title(10),description(20)); Query OK, 1000 rows affected (0.40 sec) Records: 1000 Duplicates: 0 Warnings: 0
    mysql> explain select title from film_text where title like 'AFRICAN%'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: film_text type: range possible_keys: idx_title_desc_part,idx_title_description key: idx_title_desc_part //使用列前缀匹配,不适用全局索引idx_title_description key_len: 32 ref: NULL rows: 1 Extra: Using where 1 row in set (0.16 sec)
    //实现索引匹配是部分精确,而其他部分进行范围匹配。 mysql> alter table rental drop index idx_rental_date; Query OK, 16044 rows affected (0.86 sec) Records: 16044 Duplicates: 0 Warnings: 0 mysql> alter table rental add index idx_rental_date(rental_date,customer_id,inventory_id); Query OK, 16044 rows affected (1.43 sec) Records: 16044 Duplicates: 0 Warnings: 0 mysql> explain select inventory_id from rental where rental_date = '2006-02-14 15:16:03' and customer_id >=300 and customer_id <=400G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: range possible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_创建高性能的索引,mysql索引建立和优化。date key_len: 10 ref: NULL rows: 24 Extra: Using where; Using index 1 row in set (0.00 sec) 执行过程是先使用索引的首字段rental_date将符合rental_date = '2006-02-14 15:16:03'的索引过滤,通过IO取出数据(回表,因为还要进行customer_id条件进行过滤),然后通过customer_id>=300 <=400过滤记录。
    注意:将range放在前面mysql查询优化器也会将语句优化为可以使用索引的查询。 mysql> explain select * from rental where customer_id > 5 and rental_date = '2006-05-30 10:00:00'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: range possible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 10 ref: NULL rows: 1 Extra: Using where 1 row in set (0.00 sec)

    //如果列名是索引,那么使用column_name is null 就会使用索引。 mysql> show create table paymentG *************************** 1. row *************************** Table: payment Create Table: CREATE TABLE `payment` ( `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `customer_id` smallint(5) unsigned NOT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `rental_id` int(11) DEFAULT NULL, `amount` decimal(5,2) NOT NULL, `payment_date` datetime NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`payment_id`), KEY `idx_fk_staff_id` (`staff_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `fk_payment_rental` (`rental_id`), KEY `idx_payment_date` (`payment_date`,`amount`,`last_update`), CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE, CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> explain select * from payment where rental_id is nullG *************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ref possible_keys: fk_payment_rental key: fk_payment_rental key_len: 5 ref: const rows: 5 Extra: Using where 1 row in set (0.00 sec)

    不能使用索引的情况:
    以%开头的like查询不能够利用B-TREE索引,一般推荐使用全文索引来解决全文检索问题(模糊查询);
    数据类型出现隐式转换的时候也不会使用索引;特别是字符串常量一定要使用引号引起来,这样才会使用索引。mysql默认会把输入的常量进行转换之后才会进行检索。 mysql> show create table actorG *************************** 1. row *************************** Table: actor Create Table: CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`) ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> explain select * from actor where last_name = 1G //没有使用引号将常量引起来 *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: ALL possible_keys: idx_actor_last_name key: NULL key_len: NULL ref: NULL rows: 200 Extra: Using where 1 row in set (0.00 sec) mysql> explain select * from actor where last_name = '1'G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: ref possible_keys: idx_actor_last_name key: idx_actor_last_name key_len: 137 ref: const rows: 1 Extra: Using where 1 row in set (0.00 sec)
    3 .不满足最左条件不会使用索引。 4.用or分割的条件,如果or前的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。因为后面的列的查询要使用全表扫描,没有必要再使用前面的列进行一次索引扫描。 mysql> explain select * from payment where customer_id = 203 or amount = 3.96G ***************************

    1. row *************************** id: 1 select_type: SIMPLE table: payment type: ALL possible_keys: idx_fk_customer_id key: NULL key_len: NULL ref: NULL rows: 16470 Extra: Using where 1 row in set (0.00 sec)

    mysql> show status like 'Handler_read%'; ----------------------- ------- | Variable_name | Value | ----------------------- ------- | Handler_read_first | 2 | | Handler_read_key | 2 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 33123 | ----------------------- ------- 6 rows in set (0.00 sec)
    如果索引正在工作,Handler_read_key的值将很高,这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引不经常使用。Handler_read_rnd_next表示在数据文件中读下一行的请求数。如果正在进行大量的表扫描,Handler_read_rnd_next的值较高,则通常说明表索引不正确或者写入查询没有用到索引。

    多列索引

    很多人对多列索引的理解都不够。一个常见的错误就是,为每个列创建独立的索引,或者按照错误的顺序创建多列索引。

    先来看第一个问题,为每个列创建独立的索引,从show create table 中很容易看到这种情况:

    create talbe t (
            c1 int,
            c2 int,
            c3 int,
            key(c1),
            key(c2),
            key(c3)
    );
    

    这种索引策略,一般是人们听到一些专家诸如“把where条件里面的列都建上索引”这样模糊的建议导致的。实际上这个建议非常错误。这样一来最好的情况下也只能是“一星”索引(关于三星索引可以参考拙作《高性能MySQL》读后感——B-Tree索引的三星索引说明),其性能比起真正最优的索引可能差几个数量级。有时如果无法设计一个“三星”索引,那么不如忽略掉where子句,集中精力优化索引列的顺序,或者创建一个全覆盖索引。

    在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL 5.0和更新的版本引入了一种叫”索引合并”(index merge)策略,一定程度上可以使用表上的多个单列索引来定位指定的行。

    索引合并策略有时候是一种优化的结果,但大多数时候说明表索引建得很糟糕:

    • 当出现服务器对多个索引做相交操作时(通常有多个AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。
    • 当服务器需要对多个索引做联合操作时(通常有多个OR条件),通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高,需要合并扫描返回的大量数据的时候。
    • 更重要的是,优化器不会把这些计算到“查询成本”(cost)中,优化器只关心随机页面读取。这使得查询的成本被“低估”,导致该执行计划还不如直接走全表扫描。这样做不但消耗更多的CPU和内存资源,还可能影响查询的并发性,但如果是单独运行这样的查询,则往往忽略对并发性的影响。

    如果在explain中看到有索引合并,应该好好检查一下查询和表的结构,看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用ignore index提示让优化器忽略掉某些索引。

    今天我们要说的是索引相关的知识,这也是数据库的一个重点章节。赶紧准备好你的笔,跟着我一起勾画重点吧,听说这里要考哦~~~

    4、导入数据
    mysql -u root -p new_db < dump_db_date.sql

     

    慢查询优化基本步骤

    选择合适的索引列顺序

    我们遇到的最容易引起困惑的问题就是索引列的顺序。正确的顺序依赖于使用该索引的查询,并且同时需要考虑如何更好地满足排序和分组的需要(顺便说明,本节内容适用于B-Tree索引;哈希或者其他类型的索引并不会像B-Tree索引一样按顺序存储数据)。

    在一个多列B-Tree索引中,索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列,等等。所以,索引可以按照升序或者降序进行扫描,以满足精确符合列顺序order by,group by和distinct等子句的查询需求。

    所以多列索引的列顺序至关重要。

    对于如何选择索引的顺序有一个经验法则:将选择性最高的列放到索引最前列。这个建议有用吗?在某些场景可能有帮助,但通常不如避免随机IO和排序那么重要,考虑问题需要更全面(场景不同则选择不同,没有一个放之四海皆准的法则。这里只是说明,这个经验法则可能没有你想象的重要)。

    当不需要考虑排序和分组时,将选择性最高的列放在前面通常是最好的。这时候索引的作用只是用于优化where条件的查找。在这种情况下,这样设计的索引确实能够最快的过滤出需要的行,对于在where子句中使用了索引部分前缀列的查询来说选择性也更高。然而,性能不只是依赖于所有索引列的选择性(整体基数),和查询条件的具体值也有关系,也就是和值的分布有关。这和前面介绍的选择前缀的长度需要考虑的地方一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序,让这种情况下索引的选择性最高。

    以下面的查询为例:

    select * from payment where staff_id=2 and customer_id=584;
    

    是应该创建一个(staff_id,customer_id)索引还是应该颠倒一下顺序?可以跑一些查询来确定在这个表中值的分布情况,并确定哪个列的选择性更高。先用下面的查询预测一下,看看各个where条件的分支对应的数据基数有多大:

    mysql> select sum(staff_id=2),sum(customer_id=584) from payment G
    *************************** 1. row ***************************
         sum(staff_id=2): 7992
    sum(customer_id=584): 30
    

    根据前面的经验法则,应该将索引customer_id放到前面,因为对应条件值的customer_id数量更小。我们再来看看对于这个customer_id的条件值,对应的staff_id列的选择性如何:

    mysql> select sum(staff_id=2) from payment where customer_id=584G
    *************************** 1. row ***************************
    sum(staff_id=2): 17
    

    这样做有一个地方需要注意,查询的结果非常依赖于特定的具体值。如果按上述办法优化,可能对其他一些条件值的查询不公平,服务器的整体性能可能变得更糟,或者其他某些查询的运行变得不如预期。

    如果是从诸如pt-query-digest这样的工具的报告中提取“最差”查询,那么再按上述办法选定的索引顺序往往是非常高效的。如果没有类似的具体查询来运行,那么最好按经验法则来做,因为经验法则考虑的是全局基数和选择性,而不是某个具体查询:

    mysql> select count(distinct staff_id)/count(*) as staff_id_selectivity,
        -> count(distinct customer_id)/count(*) as customer_id_selectivity,
        -> count(*)
        -> from paymentG
    *************************** 1. row ***************************
       staff_id_selectivity: 0.0001
    customer_id_selectivity: 0.0373
                   count(*): 16049
    

    customer_id的选择性更高,所以答案是将其作为索引列的第一列:

    mysql>alter table payment add index idx_cust_staff_id(customer_id,staff_id);
    

    当使用前缀索引的时候,在某些条件值的基数比正常值高的时候,问题就来了。例如,在某些应用程序中,对于没有登录的用户,都将其用户名记录为”guest”,在记录用户行为的会话表和其他记录用户活动的表中”guest”就成为了一个特殊用户ID。一旦查询涉及这个用户,那么和对于正常用户的查询就大不同了,因为通常有很多会话都是没有登录的。系统账号也会导致类似的问题。一个应用通常都有一个特殊的管理员账号,和普通账号不同,它并不是一个具体的用户,系统中所有的其他用户都是这个用户的好友,所以系统往往通过它向网站的所有用户发送状态通知和其他消息。这个账号的巨大的好友列表很容易导致网站出现服务器性能问题。

    索引的作用

    1. 可以快速根据索引查找指定的记录
    2. 可以根据索引对记录进行排序,可以用来order by 和group by
    3. 可以将随机IO转变为顺序IO,索引是有顺序的,先根据索引顺序查询,然后根据查找到的关键值定位记录

     

     

     

    先运行看看是否真的很慢,注意设置SQL_NO_CACHE;
    where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高;
    explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询);
    order by limit 形式的sql语句让排序的表优先查;
    了解业务方使用场景;
    加索引时参照建索引的几大原则;
    观察结果,不符合预期继续从1分析;

    建立索引的几大原则 最左前缀匹配原则,mysql会一直向右匹配直到遇到范围查询(、、between、like)就停止匹配,比如a...

    覆盖索引

    通常大家都会根据查询的where条件来创建合适的索引,不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询,而不单单是where条件部分。索引确实是一种查找数据的高效方式,但是MySQL也可以使用索引来直接获取列的数据,这样就不再索引读取数据行。如果索引的叶子节点中已经包含要查询的数据,那么还有什么必要再回表查询呢?如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”。

    覆盖索引是非常有用的工具,能够极大地提高性能。考虑一下如果查询只需要扫描索引而无须回表,会带来多少好处:

    • 索引条目通常远小于数据行大小,如果只读取索引,那么MySQL访问更少数据量。这对缓存的负载非常重要,因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于IO密集型的应用也有帮助,因为索引比数据还小,更容易全部放入内存中(这对于MyISAM尤其正确,因为MyISAM能压缩索引以变得更小)。
    • 索引按照列值顺序存储(至少在单个页内是如此),对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少得多。
    • 大多数据引擎能更好的缓存索引。比如MyISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此访问数据多一次系统调用。
    • 覆盖索引对于InnoDB表特别有用,因为InnoDB使用聚集索引组织数据。InnoDB的二级索引在叶子节点中保存行的主键值,如果二级索引能够覆盖查询,则可以避免对主键索引的二次查询。

    在所有这些场景中,在索引中满足查询的成本一般比查询行要小得多。

    对于索引覆盖查询(index-covered query),使用EXPLAIN时,在Extra一列中看到“Using index”。例如,在sakila的inventory表中,有一个组合索引(store_id,film_id),对于只需要访问这两列的查询,MySQL就可以使用覆盖索引,如下:

    mysql> EXPLAIN SELECT store_id, film_id FROM inventoryG
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: inventory
             type: index
    possible_keys: NULL
              key: idx_store_id_film_id
          key_len: 3
              ref: NULL
             rows: 5341
            Extra: Using index
    

    在大多数引擎中,只有当查询语句所访问的列是索引的一部分时,索引才会覆盖。但是,InnoDB不限于此,InnoDB的二级索引在叶子节点中存储了primary key的值。例如,sakila.actor表使用InnoDB,而且对于是last_name上有二级索引,所以索引能覆盖那些访问actor_id的查询:

    mysql> EXPLAIN SELECT actor_id, last_name FROM actor WHERE last_name = 'HOPPER'G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: actor
             type: ref
    possible_keys: idx_actor_last_name
              key: idx_actor_last_name
          key_len: 137
              ref: const
             rows: 2
            Extra: Using where; Using index
    
    三星系统
    1. 索引将相关的记录放在一起,获得一星
    2. 如果索引中的数据顺序和查找中的排列顺序一致则获得两星
    3. 如果索引中的列包含查询中全部列则获得三星
      需要注意的是索引并不总是最好的工具,只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时,索引才是有效的
      一般情况下:
      小表:全表扫描
      中表(数据量还不是很大):索引优化
      大表(数据量超级大):高级技术比如分区等

    二、索引

    使用索引扫描来做排序

    MySQL有两种方式生成有序的结果:

    • 通过排序操作,Explain 的Extra 输出“Using filesort”, MySQL使用文件排序;
    • 通过索引顺序扫描,Explain的type列值为index,MySQL使用索引扫描排序(不要和Extra 列的“Using index”混淆)。

    扫描索引本身很快,因为只需从一条记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列,就要每扫描一条索引记录得回表查询一次对应的行。这基本上都是随机IO,因此按索引顺序读取数据的速度通常比顺序的全表扫描慢,尤其是在IO密集型。所以,设计索引时让同一个索引既满足排序,又用于查找行,避免随机IO。

    当索引的列的顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向(倒序和正序)都一样时,MySQL才能使用索引来对结果做排序。如果查询需要关联多张表,则只有ORDER BY子句引用的字段全部为第一个表时,才能使用索引来做排序。ORDER BY子句和Where查询的限制是一样的:需要满足索引的最左前缀的要求。

    当MySQL不能使用索引进行排序时,就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序,如果内存装载不下,它会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个块合并成有序的结果集(实际上就是外排序,使用临时表)。
    对于filesort,MySQL有两种排序算法。
    (1)两次扫描算法(Two passes)
    实现方式是先将需要排序的字段和可以直接定位到相关行数据的指针信息取出,然后在设定的内存(通过参数sort_buffer_size设定)中进行排序,完成排序之后再次通过行指针信息取出所需的Columns。
    注:该算法是4.1之前采用的算法,它需要两次访问数据,尤其是第二次读取操作会导致大量的随机I/O操作。另一方面,内存开销较小。

    (2)一次扫描算法(single pass)
    该算法一次性将所需的Columns全部取出,在内存中排序后直接将结果输出。
    注:从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数,效率较高,但是内存开销也较大。如果我们将并不需要的Columns也取出来,就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中,可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时,MySQL 就会选择使用第一种排序算法,反之,则会选择第二种。为了尽可能地提高排序性能,我们自然更希望使用第二种排序算法,所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。

    当对连接操作进行排序时,如果ORDER BY仅仅引用第一个表的列,MySQL对该表进行filesort操作,然后进行连接处理,此时,EXPLAIN输出“Using filesort”;否则,MySQL必须将查询的结果集生成一个临时表,在连接完成之后进行filesort操作,此时,EXPLAIN输出“Using temporary;Using filesort”。

    当前导列为常量时,ORDER BY子句可以不满足索引的最左前缀要求。例如,Sakila数据库的表rental在列(rental_date,inventory_id,customer_id)上有名为rental_date的索引,如下表所示。

    CREATE TABLE `rental` (
      `rental_id` int(11) NOT NULL AUTO_INCREMENT,
      `rental_date` datetime NOT NULL,
      `inventory_id` mediumint(8) unsigned NOT NULL,
      `customer_id` smallint(5) unsigned NOT NULL,
      `return_date` datetime DEFAULT NULL,
      `staff_id` tinyint(3) unsigned NOT NULL,
      `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`rental_id`),
      UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
      KEY `idx_fk_inventory_id` (`inventory_id`),
      KEY `idx_fk_customer_id` (`customer_id`),
      KEY `idx_fk_staff_id` (`staff_id`),
      CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
      CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
      CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8;
    

    MySQL使用rental_date索引为下面的查询排序,从EXPLAIN中看出没有出现filesort

    mysql> EXPLAIN SELECT rental_id, staff_id FROM rental WHERE rental_date = '2005-05-25' ORDER BY inventory_id, customer_idG
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: rental
             type: ref
    possible_keys: rental_date
              key: rental_date
          key_len: 8
              ref: const
             rows: 1
            Extra: Using where
    

    即使ORDER BY 字句不满足最左前缀索引,也可以用于查询排序,因为索引的第一列被指定为常数。

    下面这个查询可以利用索引排序,是因为查询为索引的第一列提供了常量条件,用第二列进行排序,将两列组合在一起,就形成了索引的最左前缀:
    ... where rental_date = '2005-05-25' order by inventory_id desc;
    下面这个查询也没问题,因为order by使用的就是索引的最左前缀:
    ... where rental_data > '2005-05-25' order by rental_date,inventory_id;

    下面一些不能使用索引做排序的查询:

    • 下面这个查询使用两种不同的排序方向:
      ... where rental_date = '2005-05-25' order by inventory_id desc,customer_id asc;
    • 下面这个查询的order by 子句中引用一个不在索引中的列(staff_id):
      ... where rental_date = '2005-05-25' order by inventory_id,staff_id;
    • 下面这个查询的where 和 order by的列无法组合成索引的最左前缀:
      ... where rental_date = '2005-05-25' order by customer_id;
    • 下面这个查询在索引列的第一列是范围条件,所以MySQL无法使用索引的其余列:
      ... where rental_date > '2005-05-25' order by customer_id;
    • 这个查询在inventory_id列上有多个等于条件。对于排序来说,这也是一种范围查询:
      ... where rental_date = '2005-05-25' and inventory_id in(1,2) order by customer_id;

    索引类型

    mysql中索引类型有很多,索引的实现方式是通过存储引擎实现的,而不是服务器层实现的

    InnoDB引擎本质上是一种B 树结构。传送门:从B树、B 树、B*树谈到R 树。

    压缩(前缀压缩)索引

    MyISAM使用前缀压缩来减少索引的大小,让更多的索引可以放入内存中,这在某些情况下能极大地提高性能。默认只压缩字符串,但通过参数设置也可以对整数做压缩。MyISAM压缩每个索引块的方法是,先完全保存索引块中的第一个值,然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分,把这部分存储起来即可。例如:索引块中的第一个值是“perform”,第二个值是“performance”,那么第二个值的前缀压缩后存储的是类似“7,ance”这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。

    压缩块使用更少的空间,代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值,所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。正序的扫描速度还不错,但是如果是倒序扫描——例如ORDER BY DESC——就不是很好。所以在块中查找某一行的操作平均都需要扫描半个索引块。

    测试表明,对于CPU密集型应用,因为扫描需要随机查找,压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡。压缩索引可能只需要十分之一大小的磁盘空间,如果是IO密集型应用,对某些查询带来的好处会比成本多很多。

    可以在CREATE TABLE 语句中指定pack_keys参数来控制索引压缩的方式。

    B-TREE索引

    一般我们没有特别指明索引类型的时候,说的索引应该就是B-TREE索引,它使用B-TREE数据结构来存储数据的
    因为索引是由存储引擎实现的,所以不同的存储引擎会通过不同的方式来使用B-TREE索引,MYISAM使用前缀压缩技术使得索引更小,同时采用物理位置引用被索引的行(也就是说,通过索引直接就可以找到对应的数据行记录)INNODB则按照原数据格式进行存储,同时根据主键引用被所以的行(也就是说通过索引首先会找到行的主键索引,然后通过主键索引找到具体的行)
    B-TREE索引意味着所有存储的数据记录都是有顺序的

    新葡亰496net 2

    b-tree数据结构.png

    根据表的数据大小,B-TREE树层级深度也将不同,其中每一个节点页都包含了一个值以及左边小于该值的子节点页指针和大于该值的右节点页指针,也就是规定了该值的上线和下限,而叶子页的指针指向的是具体的数据,而不是其他的节点页
    在索引中,顺序是非常重要的一个因素,索引对多个值进行排序的依据就是按照create table语句中定义索引时列的顺序来实现的

    1、聚簇索引

    冗余和重复索引

    MySQL允许在相同列上创建多个索引,MySQL需要单独维护重复的索引,并且优化器在优化查询的时候需要逐个地进行考虑,影响查询性能。

    重复索引是指在相同的列上按照相同的顺序创建相同类型的索引。应该避免这样创建重复索引,发现以后也应该立即移除。

    如下面的代码,创建一个主键,先加上唯一限制,然后再加上索引以供查询使用。事实上,MySQL的唯一限制和主键限制都是通过索引实现的,因此,实际上在相同的列上创建了三个重复的索引。通常并没有理由这样做,除非在同一列上创建不同类型的索引来满足不同的查询需求。

    create table test(
      id int not null primary key,
      a int not null,
      b int not null,
      unique(id),
      index(id)
    ) engine=InnoDB;
    

    冗余索引和重复索引有一些不同。如果创建了索引(a,b),再创建索引(a)就是冗余索引,因为这只是前一个索引的前缀索引。因此索引(a,b)也可以当作索引(a)来使用(这种冗余只是对B-Tree索引来说的)。但是如果再创建索引(b,a),则不是冗余索引,索引(b)也不是,因为b不是索引(a,b)的最左前缀列。另外其他不同类型的索引(例如哈希索引或者全文索引)也不会是B-Tree索引的冗余索引,而无论覆盖的索引列是什么。

    冗余索引通常发生在为表添加新索引的时候。例如,有人可能会增加一个新的索引(a,b)而不是扩展已有的索引(a)。还有一种情况是将一个索引扩展为(a,id),其中id是主键,对于InnoDB来说主键列已经包含在二级索引中了,所以这也是冗余的。

    大多数情况下都不需要冗余索引,应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引,因为扩展已有的索引会导致其变得太大,从而影响其他使用该索引的查询的性能。

    例如:如果在整数列上有一个索引,现在需要额外增加一个很长的varchar列来扩展该索引,那性能可能会急剧下降。特别是有查询把这个索引当作覆盖索引,或者这是MyISAM表并且有很多范围查询(由于MyISAM的前缀压缩)的时候。

    举例,MyISAM引擎,表userinfo有100W行记录,每个state_id值大概2W行,在state_id列有一个索引对下面的查询有用,假设查询名为Q1:

    mysql> select count(*) from userinfo where state_id=5;
    

    查询测试结果:QPS=115。还有一个相关查询检索几个列的值,而不是统计行数,假设名为Q2:

    mysql> select state_id,city,address from userinfo where state_id=5;
    

    查询测试结果:QPS<10。提升该查询的性能可以扩展索引为为(state_id, city, address),让索引覆盖查询:

    mysql> ALTER TABLE userinfo DROP KEY state_id, 
        ->     ADD KEY state_id_2 (state_id, city, address);
    

    如果把state_id索引扩展为(state_id,city,address),那么第二个查询的性能更快了,但是第一个查询却变慢了,如果要两个查询都快,那么就必须要把state_id列索引进行冗余了。但如果是innodb表,不冗余state_id列索引对第一个查询的影响并不明显,因为innodb没有使用索引压缩,

    MyISAM和InnoDB表使用不同索引策略的查询QPS测试结果(以下测试数据仅供参考):

    只有state_id列索引 只有state_id_2索引 同时有state_id和state_id_2
    MyISAM, Q1 114.96 25.40 112.19
    MyISAM, Q2 9.97 16.34 16.37
    InnoDB, Q1 108.55 100.33 107.97
    InnoDB, Q2 12.12 28.04 28.06

    上表结论:

    • 对于MyISAM引擎,把state_id扩展为state_id_2(state_id,city,address),Q2的QPS更高,覆盖索引起作用;但是Q1的QPS下降明显,受MyISAM的前缀压缩影响需要从索引块头开始扫描。
    • 对于InnoDB引擎,把state_id扩展为state_id_2(state_id,city,address),Q2的QPS更高,覆盖索引起作用;但是Q1的QPS下降不明显,因为InnoDB没有使用索引压缩。
    • MyISAM引擎需要建state_id和state_id_2索引,才能保证Q1/Q2性能最佳;而InnoDB引擎只需state_id_2索引就能保证Q1/Q2性能最佳,从这里看出,索引压缩也并不是最好的。

    有两个索引的缺点是索引成本更高,下表是在不同的索引策略时插入InnoDB和MyISAM表100W行数据的速度(以下测试数据仅供参考):

    只有state_id列索引 同时有state_id和state_id_2
    InnoDB, 对有两个索引都有足够的内容的时候 80秒 136秒
    MyISAM, 只有一个索引有足够的内容的时候 72秒 470秒

    可以看到,不论什么引擎,索引越多,插入速度越慢,特别是新增索引后导致达到了内存瓶颈的时候,所以,要避免冗余索引和重复索引。

    在删除索引的时候要非常小心:如果在InnoDB引擎表上有where a=5 order by id 这样的查询,那么索引(a)就会很有用,索引(a,b)实际上是(a,b,id)索引,这个索引对于where a=5 order by id 这样的查询就无法使用索引做排序,而只能使用文件排序(filesort)。
    举例说明,表shop表结构如下:

    CREATE TABLE `shop` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '记录ID',
      `shop_id` int(11) NOT NULL COMMENT '商店ID',
      `goods_id` int(11) NOT NULL COMMENT '物品ID',
      `pay_type` tinyint(1) NOT NULL COMMENT '支付方式',
      `price` decimal(10,2) NOT NULL COMMENT '物品价格',
      `comment` varchar(4000) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `shop_id` (`shop_id`,`goods_id`),
      KEY `price` (`price`),
      KEY `pay_type` (`pay_type`),
      KEY `idx_comment` (`comment`(255))
    ) ENGINE=InnoDB AUTO_INCREMENT=20001 DEFAULT CHARSET=utf8 COMMENT='商店物品表'
    

    如下情况,使用pay_type索引:

    mysql> explain select * from shop where pay_type = 2 order by idG
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: shop
             type: ref
    possible_keys: pay_type
              key: pay_type
          key_len: 1
              ref: const
             rows: 9999
            Extra: Using where
    

    如下情况,虽然使用shop_id索引,但是无法使用索引做排序,EXPLAIN出现filesort:

    mysql> explain select * from shop where shop_id = 2 order by idG
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: shop
             type: ref
    possible_keys: shop_id
              key: shop_id
          key_len: 4
              ref: const
             rows: 1
            Extra: Using where; Using filesort
    

    如下情况,当WHERE 条件覆盖索引shop_id的所有值时,使用索引做排序,EXPLAIN没有filesort:

    mysql> explain select * from shop where shop_id = 2 and goods_id = 2 order by idG
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: shop
             type: const
    possible_keys: shop_id
              key: shop_id
          key_len: 8
              ref: const,const
             rows: 1
            Extra: 
    
    B-TREE索引能使用的类型

    全值匹配:所有列进行匹配
    匹配最左前缀:匹配索引的第一列
    匹配列前缀:匹配某一列的值开头的部分
    匹配范围值:索引第一列范围查找
    精确匹配第一列,范围匹配另外一列
    因为索引树中的节点是有顺序的,所以除了按值查找之外,还可以对数据进行order by排序操作,但是使用B-TREE索引也有一定的限制:
    如果不是按照索引的最左列开始查找,将无法使用索引
    不能跳过索引中的列
    如果查询中有某个列的范围查询,则其后面的列都将无法使用索引进行查询

    聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。索引的顺序就是数据存放的顺序,所以,很容易理解,一张数据表只能有一个聚簇索引。

    索引和锁

    索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行,那么就会锁定更少的行,从两个方面来看这对性能都有好处。首先,虽然InnoDB的行锁效率很高,内存使用也很少,但是锁定行的时候仍然会带来额外开销;其次,锁定超过需要的行会增加锁争用并减少并发性。

    InnoDB只有在访问行的时候才会对其加锁,而索引能够减少InnoDB访问的行数,从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行,那么在InnoDB检索到数据并返回给服务器层以后,MySQL服务器才能应用 where子句。这时已经无法避免锁定行了:InnoDB已经锁住这些行,到适当的时候才释放。在MySQL5.1及更新的版本中,InnoDB可以在服务器端过滤掉行后就释放锁。

    下面的例子再次使用Sakila很好的解释这些情况:

    新葡亰496net 3

    图1 索引和锁(1)

    这条查询只返回2~4行数据,实际上获取1~4行排他锁。InnoDB锁住第1行,因为MySQL为该查询选择的执行计划是索引范围扫描:

    mysql> explain select actor_id from actor where actor_id < 5 and actor_id <> 1 FOR UPDATEG
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: actor
             type: range
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 2
              ref: NULL
             rows: 3
            Extra: Using where; Using index
    

    换句话说,底层存储引擎的操作是“从索引的开头获取满足条件 actor_id < 5 的记录”,服务器并没有告诉InnoDB可以过滤第1行的WHERE 条件。Explain的Extra出现“Using Where”表示MySQL服务器将存储引擎返回行以后再应用WHERE 过滤条件。

    我们来证明第1行确实是被锁定,保持这个终端链接不关闭,然后我们打开另一个终端。如图2,这个查询会挂起,直到第1个事务释放第1行的锁。

    新葡亰496net 4

    图2 索引和锁(2)

    按照这个例子,即使使用索引,InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话,结果会更糟。MySQL会全表扫描并锁住所有的行,而不管是不是需要。

    hash索引

    mysql索引是在存储引擎层实现的,并没有统一的标准,不同的存储引擎实现的索引方式是不同的
    对于hash索引,只能精确匹配所有列的值,因为存储引擎将会把生成hash索引的所有列的值用来构建hash code
    在mysql中,只有memory引擎显示支持hash索引,这也是它默认的索引类型,memory引擎同时也是支持非唯一hash索引的,当出现hash冲突时,通过链表的方式解决冲突问题
    hash索引基于hash表实现的,在它其中并不保存实际的值,而是保存hashcode->行的指针的键值对方式

    新葡亰496net 5

    hash索引数据结构.png

    因此使用hash索引能快速的定位到某一行记录,但是它也存在某些限制:
    hash索引只包含hash值与行指针,而不存储字段值,所以不能使用索引中的值来避免读取行
    hash索引数据并不是按照索引值顺序存储的,也就无法使用排序
    hash索引也不支持部分索引列匹配查找,因为hashcode是通过所有hash列生成出来的
    hash值只支持等值比较查询,包括=,in(),<=>(通过a <=> null,可以得出a为null的记录) 不支持任何范围查询
    访问hash索引的数据非常快,除非有很多hash冲突,当出现冲突时,存储引擎只能逐行进行查找
    如果hash冲突很多时,维护起来代价也很高,应该避免在选择性比较低的列上建立hash索引
    innodb引擎有一个特殊的功能叫做“自适应hash索引”,当innodb注意到某些索引值被频繁的引用,它会在内存中基于B-TREE索引之上再建立一个hash索引
    如果某些存储引擎不支持hash索引,我们需要创建自定义的hash索引,创建一个伪hash索引列,通过CRC32()对需要hash的列值计算hash,并在该列上创建索引
    对于hash索引查找,需要在where条件语句中加上hashcode比较和列值比较,这样是为了解决hash索引带来的冲突

    select url from t_urls where url_code = crc32(‘http://www.baidu.com’) and url = ‘http://www.baidu.com’;
    

    这里如果发生了hash冲突,则根据url列值进行查找
    上面创建伪hashcode索引列采用的是crc32算法,生成一个32位的数字,但是通常64位数字hash冲突会更少,可以自己定义一个算法:

    select conv(right(md5('http://www.baidu.com'), 16), 16, 10);
    

    如果语句中的索引列不是独立的,那么这条语句就不能使用该列索引,也就是说索引列不能作为表达式的一部分或者不能作为函数的参数

    select acter_id from actor where acter_id  1 = 5;
    select ... where to_days(current_date) – to_days(date_col)<= 10
    

    对于长度很长的列,创建索引时可以采用类似hash索引那样的,自己建一个伪hashcode列,手动维护这个列,通过列值计算该列对应的数字值并作为hash索引
    以url列举例,如果直接使用url,则整个列字段的字符串太长,占据太多空间,我们选择为url创建一个url_code,用来计算crc32(url)得到的数字

    create table urls {
        id int unsigned not null auto_increment,
        url varchar(255) not null,
        url_code int unsigned not null default 0
        primary key(id)
    }
    

    在插入或者更新url时,通过触发器重新计算url_code的值

    delimiter //
    create trigger urls_insert_trigger before insert on urls for each row begin 
    set new.url_code = crc32(new.url);
    end;
    //
    create trigger urls_update_trigger before update on urls for each row begin 
    set new.url_code = crc32(new.url);
    end;
    //
    delimiter;
    

    通过伪hashcode列与该列值来精确查询某一条记录

    select * from urls where url_code = crc32(‘http://www.baidu.com’) and url = ‘http://www.baidu.com’;
    

    聚簇索引要比非聚簇索引查询效率高很多,特别是范围查询的时候。具体细节依赖于其实现方式,InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

    全文索引

    全文索引是一种特殊类型的索引,它查找的是文本中的关键字,而不是直接比较索引中的值,它与其他几种类型的索引匹配方式完全不一样,它存在许多需要注意的细节:如停用词、词干、复数、布尔搜索等,更加类似于搜索引擎要干的事情

    新葡亰496net 6

    前缀索引

    通过比较列选择性和索引选择性来决定前缀的长度,对于mysql来说,不允许对text/blob列全值进行索引,但是我们可以通过在查询时指定使用前缀来优化此类查询,比如排序时,避免磁盘临时表排序
    选择性:不重复的索引值和数据表记录总数的比值

    select count(*) as count, city as city from t_city group by city order by city desc limit 10;
    

    上面这条语句记录了每一个城市出现的重复次数

    select count(*) as count, left(city, 3) as pref from t_city group by pref order by pref desc limit 10;
    

    还有一种选择方式:计算列平均选择性,并使前缀选择性接近列选择性

    select count(distinct city) / count(*) from t_city;
    select count(distinct left(city, 3)) / count(*) from t_city
    

     

    前缀索引的创建方式

    alter table sakila.city_demo add key city(7)
    这样就在sakila.city_demo表中创建了一个city前缀索引,索引长度为7个字符,
    使用前缀索引的缺点是:前缀索引不能用来做order by 和group by操作,也无法用于作覆盖扫描

    主键的顺序就是实际数据存放的顺序。因此,按主键进行范围查询的时候,效率会高很多。

    后缀索引

    还有一种是“反向索引”,针对像url这种类型的字符串列而言的,使用后缀来进行索引效果更佳,但是mysql本身并不支持后缀索引这种方式,所以我们可以通过将保存的url字符串反向存入数据库并创建前缀索引的方式来实现所谓的后缀索引

    2、查询效率?

    选择合适的索引顺序

    在B-TREE索引中,索引列的顺序意味着索引从最左列进行排序,经验法则告诉我们可以将选择性高的放在前面,当不需要考虑排序和分组时,将选择性高的索引列放在前面通常是非常好的
    我们需要对多个列计算每个列对应的选择性,然后做出决策

    select count(distinct staff_id) / count(*) as staff_id_selectivity,
    count(distinct custom_id) /count(*) as custom_id_selectivity, count(*) from payment G;
    

    新葡亰496net 7

    列选择性.png

    根据查询结果来看,应该将custom_id放在索引列staff_id前面
    顺序的索引会造成的潜在问题:
    在高并发工作时,innoDB按主键顺序插入可能会引起明显的间隙锁争用

    假设有张表有2个索引,主键作为聚簇索引,还有一个二级索引。聚簇索引的B 树高度为h1,二级索引的B 树高度为h2。

    聚簇索引

    聚簇索引其实是一种数据结构,保存了B-TREE索引和数据行,数据表中的数据记录都保存在叶子页上,但是节点页只包含了索引列
    聚簇表示数据行与相邻的键值紧凑的存储在一起,

    新葡亰496net 8

    聚簇索引数据结构.png

    在innoDB数据库中,通过主键索引列来聚簇数据记录,也就是说,在innoDB聚簇索引中,节点页上保存的是行主键,如果没有主键列,innoDB会选择一个非空索引代替,如果也没有这样的索引,innoDB会创建一个隐式的主键来进行聚簇
    在innodb中,没有被用来做聚簇的索引,被称为是二级索引,在索引中保存的并不是物理行的位置,而是行记录的主键,需要根据二级索引找到行主键之后再到聚簇B-TREE中查找指定的行记录
    myisam引擎主键与其他索引实现相同,主键只是一个名称为PRIMARY的非空索引。
    myisam存储数据就是按照数据的插入顺序保存的,表存储结构的叶子节点上保存了当前索引列值和物理行所在的位置
    innodb通过B-TREE结构保存数据表行的所有列记录,二级索引通过保存主键值,在根据主键值在B-TREE结构中查找物理行数据信息

    聚集的数据有哪些优点

    1. 可以把相关的数据保存在一起,这样在查找记录时可以从磁盘上读取少量的页就能查到结果
    2. 访问数据更快,聚簇索引将索引和数据都保存在同一个B-TREE中,因此从聚簇索引获取数据比非聚簇索引获取数据要快
    3. 使用覆盖索引扫描的查询,可以直接使用页节点的主键值,无需再根据主键查找数据
      聚簇索引的缺点
      聚簇索引最大限度的提高了I/O密集型应用的性能,但如果数据全部都放在内存中,那么访问的顺序就没那么重要了
    4. 插入速度严重依赖于插入顺序,按照主键的顺序插入是加载数据到INNODB表中速度最快的方式
    5. 更新聚簇索引列的代价很高,因为会强制每一个被更新的行移动到新的位置
      4. 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临页分裂的问题
    6. 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏的时候,或者页分裂导致数据存储不连续的时候
    7. 二级索引可能比想象的大,因为二级索引的叶子节点包含了引用行的主键列
    8. 二级索引访问需要两次索引查找,找主键、找数据

    那么通过主键来查询,时间复杂度为O(h1);通过二级索引来查询,时间复杂度为O(h1 h2)。原因在于,在InnoDB引擎中,二级索引的叶子节点中存储的不是行指针,而是主键值

    延迟查询

    对于某些查询,可以通过延迟查询来优化
    explain select * from products where actor = ‘sean carrey’ and title like ‘%apollo%’G
    其中actor 与title 列建立了索引
    这里无法对查询进行索引覆盖,因为查询的列为全部列,不存在任何一个索引可以覆盖所有列
    改为延迟加载,添加索引覆盖列(actor, title, prod_id)

    explain select * from products inner join (
    select prod_id from products where actor = ‘sean carrey’ and title like ‘%apollo%’)as t1 on (t1.prod_id = products.prod_id)
    

    上面子查询采用索引覆盖,过滤prod_id,然后根据prod_id再到记录中查找

    这样做的好处是当移动行的时候无须更新二级索引中的指针,减少了行移动或者数据页分裂时的维护工作。

    覆盖索引

    如果一个索引包含所有需要查询的字段的值,那么我们就称之为覆盖索引

    覆盖索引的好处

    1. 索引条目通常远小于数据行大小,所以如果只需要读取索引,那mysql就会极大的减少数据访问量
    2. 因为索引是按照列值顺序存储的,所以顺序查询会比随机从磁盘读取数据的I/O要少的多
    3. 一些存储引擎如MYISAM在内存中只缓存索引,数据则依赖于具体OS来缓存,因此访问数据意味着还需要一次系统调用,采用覆盖索引则减少了这样的系统调用
    4. 针对INNODB的聚簇索引,覆盖索引可以杜绝二级索引根据主键值查找数据行记录
      覆盖索引必须要存储索引列的值,而hash索引、空间索引、全文索引都不存储索引列的值,所以mysql只能使用b-tree索引做覆盖索引
      当发起一个索引覆盖查询时,通过explain分析语句会看到extra Using index,这里的extra表示的是检索数据的方式,需要与type进行区分,type index表示在对结果集进行排序时使用到了索引
      如果查询的列没有被索引覆盖,也就是无法使用索引覆盖查询时,explain查询分析出来extra Using where
      对于下面这条语句:
    explain select * from products where actor=’seny carrey’ and title like ‘%apollo%’G
    

    存在两个问题导致它无法使用覆盖索引:

    1. 没有任何一个索引能够覆盖这个查询,因为从表中选择了所有列,而没有任何索引覆盖了所有列
    2. mysql不能在索引中执行like操作,只是允许使用左前缀匹配的方式和一些简单的值比较,上面的查询语句可以通过延迟关联来解决:
    select * from product inner join(
        select prod_id from product where actor=’seny carrey’ and title like ‘apollo%’
    ) as t1 on t1.prod_id = product.prod_idG
    

    3、采用自增类型还是uuid作为主键?

    使用索引扫描做排序

    排序有两种方式:直接通过排序、按索引顺序扫描,如果explain出来的结果中的type为index,则表示使用到了索引扫描来做排序
    orderby子句的列顺序必须与索引列定义的顺序完全一致(也就是说按照多个列进行排序,要么都升序,要么都降序),因为mysql是按照索引顺序来组织记录顺序的,而order by 如果打破了这种规则那么就必须使用文件排序
    如果查询关联多张表,则只有当order by子句引用的字段全部为第一个表,才能使用索引做排序
    还有一种情况就是如果索引前导列(where语句或者join子句中包含的索引第一列)设置为常量时,就可以使用索引进行排序,比如:
    (rental_date,inventory_id,customer_id)为一个组合索引,则语句

    select rental_id,staff_id from sakila.rental where rental_date=’2005-05-25’ order by inventory_id,customer_id
    

    可以使用索引进行排序,虽然order by 子句不满足索引的最左前缀要求,也可以用于查询排序,因为索引第一列被设置成为了常量

    下面列出不能使用索引做排序的查询

    1. 使用两种不同的排序方向,但是索引列都是正序排列
      where rental_date=2005-05-25’ order by inventory_id desc,customer_id asc;
    2. 引用不存在与索引中的列
      where rental_date=2005-05-25’ order by inventory_id,staff_id
    3. where与order by中的列无法组合成索引的最左前缀
      where rental_date=’2005-05-25’ order by customer_id
    4. 查询在索引列的第一列为范围查询条件,所以mysql无法使用其他的索引列
      where rental_date > ‘2005-05-25’ order by inventory_id,customer_id
    5. 索引列上存在多个等值条件,对于查询来说其实就相当于范围查询
      where rental_date = ‘2005-05-25’ and inventory_id in(1,2) order by customer_id

    自增类型作为主键的好处:一是长度短,节省空间,尤其是在表中有很多二级索引的情况下;二是索引顺序和实际数据存放顺序一致,insert操作总是插入到索引的最后,和uuid相比,避免了大量的为新的行寻找合适位置插入以及频繁的页分裂操作。既节省空间又速度快。

    压缩(前缀压缩)索引

    myisam使用前缀压缩索引减少索引的大小,从而让更多的索引能放入内存,默认只压缩字符串,但是也可以配置压缩整数
    myisam压缩每个索引块的方法是,先完全保存索引块的第一个值,然后将其他值和第一个值进行比较得到相同的前缀的字节数和不同的后缀,把这部分存储起来即可,比如:索引块中第一个值为perform,第二个值为performance,那么第二个值的前缀压缩后存储的是7,ance这样的形式
    前缀索引无法通过二分查找只能从头开始扫描,正序的扫描速度还不错,但反序就不是很好了

    自增作为主键的坏处:对于高并发工作负载,瞬时插入大量数据,会造成明显的锁竞争,而uuid则不存在这样的问题。另外,在分布式架构中,采用自增作为主键有个主键全局唯一性问题(uuid也有极小概率会冲突)。

    冗余索引和重复索引

    重复索引,具有相同类型、按照相同顺序的索引,应该避免,发现后立即删除
    冗余索引,(A,B)为索引,再创建索引(A)就是冗余索引,因为A索引只是AB索引的前缀索引,因此索引(AB)也可以当做(A)来算
    默认情况下在创建innodb二级索引时,主键索引已经默认添加到该索引上了,例如(A, ID)其中id为主键索引
    冗余索引必须是相同的类型,其他类型的索引,比如hash索引或者全文索引页不会是B-TREE索引的冗余索引

    4、使用索引扫描来做排序的条件

    索引和锁

    索引可以让查询锁定更少的行,innodb只有在访问行的时候才会对其加锁,而索引能够减少innodb访问的行数,从而减少锁的数量,但这只有在存储引擎层过滤掉所有不需要的行时才有效

    1)只有当索引列顺序和order by子句的顺序完全一致,并且所有列的排序方向都一样时;

    支持多种过滤条件

    在有更多不同值的列上创建索引的选择性会更好,在检索时,我们可以将查询用的多的列加入到索引中,对于索引前缀列不需要进行条件过滤时,通过in指定列值,IN的方式对查询检索是有效的,但是对order by则是无效的,比如存在(sex,country)这样的索引,当我们需要使用到该索引时,但又不需要对性别做出限制,那么我们可以通过and sex in (‘m’,’f’)的方式让mysql选择该列索引

    2)如果查询需要关联多张表,则只有当order by子句引用的字段全部为第一张表时;

    避免多个范围条件

    针对这两种查询语句:

    select actor_id from actor where actor_id > 45;
    select actor_id from actor where actor_id in (1,4,49);
    

    这两种查询语句的执行效率是不同的,对于范围查询,mysql是无法使用范围列后面的其他索引列了,但是对于多个等值条件查询,则没有这个限制

    3)需要满足索引的最左前缀的要求,除非前导列为常量。

    维护索引和表

    比如:

    找到并修复索引表

    通过check table来检查是否发生了表损坏,并通过repair table来修复表;但是如果存储引擎不支持该命令,也可以通过alter table 重建表来达到修复目的
    alter table innodb_tbl ENGINE=INNODB

    create table rental(

    更新索引统计信息

    查询优化器通过两个API来了解存储引擎的索引值分布,通过这两个API的结果来决定使用哪个索引进行查询优化
    records_in_range();传入两个边界值计算之间的记录数
    info();返回各种类型的数据包括索引基数(通过show index from table)
    如果统计信息不准确,那么定会影响到查询优化器的优化策略,通过analyze table重新生成统计信息

      ...

    数据碎片类型

    行碎片:数据行被存储在多个地方的多个片段中
    行间碎片:逻辑上顺序的页,在磁盘上不是顺序的
    剩余空间碎片:数据页中大量的空余空间
    通过optimize table 或者导出再导入的方式来重新整理数据,对于不支持该命令的存储引擎,可以通过alter table tablename engine=<engine>来进行优化
    每种存储引擎实现索引统计信息的方式不同,所以需要进行analyze table的频率也不同:

    1. memory引擎根本不存储索引统计信息
    2. myisam引擎将索引统计信息存储在磁盘中,analyze table需要进行一次全索引扫描来计算索引基数
    3. 直到mysql5.5,innodb也不在磁盘存储索引统计信息,而是通过随机的索引访问进行评估,并将估算结果存在内存中

      unique key rental_date(rental_date, inventory_id, customer_id),

    mysql执行状态

    通过show full processlist来查看mysql当前处在哪一个状态
    sleep 线程正等待客户端发起查询请求
    locked 在mysql服务层里,该线程正在等待表锁
    Analyzing and statistics 线程正在搜集存储引擎的统计信息,并生成查询执行计划
    query 线程正在查询
    Copying to tmp table [on disk],线程正在执行查询,并将结果复制到一个临时表中,这种状态要么是在group by操作,要么是在文件排序操作,如果这个状态后面还有on disk ,则表示mysql正在把一个内存临时表放到磁盘
    sorting result 线程正在进行排序
    Sending data 这个状态有多重可能,有可能是线程之间在进行数据传输,或者正在生成结果集,或者向客户端返回数据

    );

    select * from rental order by rental_date, inventory_id;  //符合,满足最左前缀要求

    select * from rental where rental_date='2016-10-30' order by inventory_id, customer_id;  //符合,前导列为常量

    select * from rental order by rental_date, customer_id;  //不符合,不满足最左前缀要求

    select * from rental where rental_date in ('2016-10-28', '2016-10-30') order by inventory_id;  //不符合,前导列为范围

     

    lyhabc的Mysql总结: 

    本文由新葡亰496net发布于网络数据库,转载请注明出处:创建高性能的索引,mysql索引建立和优化

    关键词:

上一篇:SQL存储过程,事务处理

下一篇:没有了