5分钟了解MySQL5.7的Online DDL雷区

 

Part1:写在最前

Online DDL，当新手听到这个名字的时候，非常高兴，以为无论什么情况下，修改表结构都不会锁表，理想很丰满，现实很骨感！读完本文，教你如何避开这些雷区，安全的修改表结构。话不多说，我们分别来看下MySQL5.6和MySQL5.7在修改表结构上的相同和异同。

 

 

Part2:5.6.25的表现

①首先我们构造数据并进行测试

<span style="color:#333333"><span style="color:black"><code class="language-sql">mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">create</span> <span style="color:#0077aa">database</span> helei<span style="color:#999999">;</span>
Query OK<span style="color:#999999">,</span> <span style="color:#990055">1</span> <span style="color:#0077aa">row</span> affected <span style="color:#999999">(</span><span style="color:#990055">0.01</span> sec<span style="color:#999999">)</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">use</span> helei<span style="color:#999999">;</span>
<span style="color:#0077aa">Database</span> changed

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">create</span> <span style="color:#0077aa">table</span> helei<span style="color:#999999">(</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> id <span style="color:#0077aa">int</span><span style="color:#999999">(</span><span style="color:#990055">10</span><span style="color:#999999">)</span> <span style="color:#0077aa">unsigned</span> <span style="color:#9a6e3a">NOT</span> <span style="color:#990055">NULL</span> <span style="color:#0077aa">AUTO_INCREMENT</span><span style="color:#999999">,</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> c1 <span style="color:#0077aa">int</span><span style="color:#999999">(</span><span style="color:#990055">10</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">NOT</span> <span style="color:#990055">NULL</span> <span style="color:#0077aa">DEFAULT</span> <span style="color:#669900">'0'</span><span style="color:#999999">,</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> c2 <span style="color:#0077aa">int</span><span style="color:#999999">(</span><span style="color:#990055">10</span><span style="color:#999999">)</span> <span style="color:#0077aa">unsigned</span> <span style="color:#0077aa">DEFAULT</span> <span style="color:#990055">NULL</span><span style="color:#999999">,</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> c5 <span style="color:#0077aa">int</span><span style="color:#999999">(</span><span style="color:#990055">10</span><span style="color:#999999">)</span> <span style="color:#0077aa">unsigned</span> <span style="color:#9a6e3a">NOT</span> <span style="color:#990055">NULL</span> <span style="color:#0077aa">DEFAULT</span> <span style="color:#669900">'0'</span><span style="color:#999999">,</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> c3 <span style="color:#0077aa">timestamp</span> <span style="color:#9a6e3a">NOT</span> <span style="color:#990055">NULL</span> <span style="color:#0077aa">DEFAULT</span> <span style="color:#0077aa">CURRENT_TIMESTAMP</span> <span style="color:#0077aa">ON</span> <span style="color:#0077aa">UPDATE</span> <span style="color:#0077aa">CURRENT_TIMESTAMP</span><span style="color:#999999">,</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> c4 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">200</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">NOT</span> <span style="color:#990055">NULL</span> <span style="color:#0077aa">DEFAULT</span> <span style="color:#669900">''</span><span style="color:#999999">,</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">PRIMARY</span> <span style="color:#0077aa">KEY</span><span style="color:#999999">(</span>id<span style="color:#999999">)</span><span style="color:#999999">,</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">KEY</span> idx_c1<span style="color:#999999">(</span>c1<span style="color:#999999">)</span><span style="color:#999999">,</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">KEY</span> idx_c2<span style="color:#999999">(</span>c2<span style="color:#999999">)</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> <span style="color:#999999">)</span><span style="color:#0077aa">ENGINE</span><span style="color:#9a6e3a">=</span><span style="color:#0077aa">InnoDB</span> <span style="color:#999999">;</span>
Query OK<span style="color:#999999">,</span> <span style="color:#990055">0</span> <span style="color:#0077aa">rows</span> affected <span style="color:#999999">(</span><span style="color:#990055">0.02</span> sec<span style="color:#999999">)</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">delimiter</span> $$
mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">drop</span> <span style="color:#0077aa">procedure</span> <span style="color:#0077aa">if</span> <span style="color:#0077aa">exists</span> <span style="color:#999999">`</span>insert_helei<span style="color:#999999">`</span> $$
mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">create</span> <span style="color:#0077aa">procedure</span> <span style="color:#999999">`</span>insert_helei<span style="color:#999999">`</span><span style="color:#999999">(</span><span style="color:#9a6e3a">in</span> row_num <span style="color:#0077aa">int</span> <span style="color:#999999">)</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">begin</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span>  <span style="color:#0077aa">declare</span> i <span style="color:#0077aa">int</span>  <span style="color:#0077aa">default</span> <span style="color:#990055">0</span><span style="color:#999999">;</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span>  <span style="color:#0077aa">while</span> i <span style="color:#9a6e3a"><</span> row_num <span style="color:#0077aa">do</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">insert</span> <span style="color:#0077aa">into</span> helei<span style="color:#999999">(</span>c1<span style="color:#999999">,</span> c2<span style="color:#999999">,</span> c5<span style="color:#999999">,</span>c3<span style="color:#999999">,</span> c4<span style="color:#999999">)</span> <span style="color:#0077aa">values</span><span style="color:#999999">(</span> floor<span style="color:#999999">(</span>rand<span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#9a6e3a">*</span>row_num<span style="color:#999999">)</span><span style="color:#999999">,</span>floor<span style="color:#999999">(</span>rand<span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#9a6e3a">*</span>row_num<span style="color:#999999">)</span><span style="color:#999999">,</span>floor<span style="color:#999999">(</span>rand<span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#9a6e3a">*</span>row_num<span style="color:#999999">)</span><span style="color:#999999">,</span><span style="color:#dd4a68">now</span><span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#999999">,</span><span style="color:#0077aa">repeat</span><span style="color:#999999">(</span><span style="color:#669900">'su'</span><span style="color:#999999">,</span> floor<span style="color:#999999">(</span>rand<span style="color:#999999">(</span><span style="color:#999999">)</span><span style="color:#9a6e3a">*</span><span style="color:#990055">20</span><span style="color:#999999">)</span><span style="color:#999999">)</span><span style="color:#999999">)</span><span style="color:#999999">;</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">set</span> i <span style="color:#9a6e3a">=</span> i<span style="color:#9a6e3a">+</span><span style="color:#990055">1</span><span style="color:#999999">;</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span>  <span style="color:#0077aa">END</span> <span style="color:#0077aa">while</span><span style="color:#999999">;</span>
    <span style="color:#9a6e3a">-</span><span style="color:#9a6e3a">></span> <span style="color:#0077aa">end</span>$$
Query OK<span style="color:#999999">,</span> <span style="color:#990055">0</span> <span style="color:#0077aa">rows</span> affected <span style="color:#999999">(</span><span style="color:#990055">0.00</span> sec<span style="color:#999999">)</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">delimiter</span> <span style="color:#999999">;</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">call</span> insert_helei<span style="color:#999999">(</span><span style="color:#990055">1000000</span><span style="color:#999999">)</span><span style="color:#999999">;</span>

Query OK<span style="color:#999999">,</span> <span style="color:#990055">1</span> <span style="color:#0077aa">row</span> affected <span style="color:#999999">(</span><span style="color:#990055">18</span> min <span style="color:#990055">52.88</span> sec<span style="color:#999999">)</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">desc</span> helei<span style="color:#999999">;</span>
<span style="color:#9a6e3a">+</span><span style="color:slategray">-------+------------------+------+-----+-------------------+-----------------------------+</span>
<span style="color:#9a6e3a">|</span> Field <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">Type</span>             <span style="color:#9a6e3a">|</span> <span style="color:#990055">Null</span> <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">Key</span> <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">Default</span>           <span style="color:#9a6e3a">|</span> Extra                       <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">+</span><span style="color:slategray">-------+------------------+------+-----+-------------------+-----------------------------+</span>
<span style="color:#9a6e3a">|</span> id    <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">int</span><span style="color:#999999">(</span><span style="color:#990055">10</span><span style="color:#999999">)</span> <span style="color:#0077aa">unsigned</span> <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">NO</span>   <span style="color:#9a6e3a">|</span> PRI <span style="color:#9a6e3a">|</span> <span style="color:#990055">NULL</span>              <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">auto_increment</span>              <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> c1    <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">int</span><span style="color:#999999">(</span><span style="color:#990055">10</span><span style="color:#999999">)</span>          <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">NO</span>   <span style="color:#9a6e3a">|</span> MUL <span style="color:#9a6e3a">|</span> <span style="color:#990055">0</span>                 <span style="color:#9a6e3a">|</span>                             <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> c2    <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">int</span><span style="color:#999999">(</span><span style="color:#990055">10</span><span style="color:#999999">)</span> <span style="color:#0077aa">unsigned</span> <span style="color:#9a6e3a">|</span> YES  <span style="color:#9a6e3a">|</span> MUL <span style="color:#9a6e3a">|</span> <span style="color:#990055">NULL</span>              <span style="color:#9a6e3a">|</span>                             <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> c5    <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">int</span><span style="color:#999999">(</span><span style="color:#990055">10</span><span style="color:#999999">)</span> <span style="color:#0077aa">unsigned</span> <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">NO</span>   <span style="color:#9a6e3a">|</span>     <span style="color:#9a6e3a">|</span> <span style="color:#990055">0</span>                 <span style="color:#9a6e3a">|</span>                             <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> c3    <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">timestamp</span>        <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">NO</span>   <span style="color:#9a6e3a">|</span>     <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">CURRENT_TIMESTAMP</span> <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">on</span> <span style="color:#0077aa">update</span> <span style="color:#0077aa">CURRENT_TIMESTAMP</span> <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span> c4    <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">200</span><span style="color:#999999">)</span>     <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">NO</span>   <span style="color:#9a6e3a">|</span>     <span style="color:#9a6e3a">|</span>                   <span style="color:#9a6e3a">|</span>                             <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">+</span><span style="color:slategray">-------+------------------+------+-----+-------------------+-----------------------------+</span>
<span style="color:#990055">6</span> <span style="color:#0077aa">rows</span> <span style="color:#9a6e3a">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span><span style="color:#990055">0.01</span> sec<span style="color:#999999">)</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">add</span> c6 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">60</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span> <span style="color:#0077aa">default</span> <span style="color:#669900">''</span><span style="color:#999999">;</span>
Query OK<span style="color:#999999">,</span> <span style="color:#990055">0</span> <span style="color:#0077aa">rows</span> affected <span style="color:#999999">(</span><span style="color:#990055">9.66</span> sec<span style="color:#999999">)</span>
Records: <span style="color:#990055">0</span>  Duplicates: <span style="color:#990055">0</span>  <span style="color:#0077aa">Warnings</span>: <span style="color:#990055">0</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">select</span> <span style="color:#dd4a68">count</span><span style="color:#999999">(</span><span style="color:#9a6e3a">*</span><span style="color:#999999">)</span> <span style="color:#0077aa">from</span> helei<span style="color:#999999">;</span>
<span style="color:#9a6e3a">+</span><span style="color:slategray">----------+</span>
<span style="color:#9a6e3a">|</span> <span style="color:#dd4a68">count</span><span style="color:#999999">(</span><span style="color:#9a6e3a">*</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">+</span><span style="color:slategray">----------+</span>
<span style="color:#9a6e3a">|</span>  <span style="color:#990055">1000000</span> <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">+</span><span style="color:slategray">----------+</span>
<span style="color:#990055">1</span> <span style="color:#0077aa">row</span> <span style="color:#9a6e3a">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span><span style="color:#990055">0.14</span> sec<span style="color:#999999">)</span>
mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE<span style="color:#999999">,</span><span style="color:#0077aa">modify</span> c6 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">80</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span> <span style="color:#0077aa">default</span> <span style="color:#669900">''</span><span style="color:#999999">;</span>
ERROR <span style="color:#990055">1846</span> <span style="color:#999999">(</span><span style="color:#990055">0</span>A000<span style="color:#999999">)</span>: <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE <span style="color:#9a6e3a">is</span> <span style="color:#9a6e3a">not</span> supported<span style="color:#999999">.</span> Reason: Cannot change <span style="color:#0077aa">column</span> <span style="color:#0077aa">type</span> INPLACE<span style="color:#999999">.</span> Try <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>COPY<span style="color:#999999">.</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">modify</span> c6 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">80</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span> <span style="color:#0077aa">default</span> <span style="color:#669900">''</span><span style="color:#999999">;</span>
Query OK<span style="color:#999999">,</span> <span style="color:#990055">1000000</span> <span style="color:#0077aa">rows</span> affected <span style="color:#999999">(</span><span style="color:#990055">9.44</span> sec<span style="color:#999999">)</span>
Records: <span style="color:#990055">1000000</span>  Duplicates: <span style="color:#990055">0</span>  <span style="color:#0077aa">Warnings</span>: <span style="color:#990055">0</span></code></span></span>

Tips:心得

可以看出5.6.25这里并不支持ALGORITHM=INPLACE，而直接对表进行修改的话用了9.44s。

 

 

②接下来我们测试下变更varchar长度是否锁表

<span style="color:#333333"><span style="color:black"><code class="language-sql">mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">modify</span> c6 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">40</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span> <span style="color:#0077aa">default</span> <span style="color:#669900">''</span><span style="color:#999999">;</span>
Query OK<span style="color:#999999">,</span> <span style="color:#990055">1000000</span> <span style="color:#0077aa">rows</span> affected <span style="color:#999999">(</span><span style="color:#990055">9.26</span> sec<span style="color:#999999">)</span>
Records: <span style="color:#990055">1000000</span>  Duplicates: <span style="color:#990055">0</span>  <span style="color:#0077aa">Warnings</span>: <span style="color:#990055">0</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">update</span> helei <span style="color:#0077aa">set</span> c1<span style="color:#9a6e3a">=</span><span style="color:#990055">9999</span> <span style="color:#0077aa">where</span> id<span style="color:#9a6e3a">=</span><span style="color:#990055">1</span><span style="color:#999999">;</span>
Query OK<span style="color:#999999">,</span> <span style="color:#990055">0</span> <span style="color:#0077aa">rows</span> affected <span style="color:#999999">(</span><span style="color:#990055">7.77</span> sec<span style="color:#999999">)</span>
<span style="color:#0077aa">Rows</span> <span style="color:#0077aa">matched</span>: <span style="color:#990055">1</span>  Changed: <span style="color:#990055">0</span>  <span style="color:#0077aa">Warnings</span>: <span style="color:#990055">0</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">show</span> processlist<span style="color:#999999">;</span>
<span style="color:#9a6e3a">+</span><span style="color:slategray">----+------+-----------+-------+---------+------+---------------------------------+-------------------------------------------------------------+</span>
<span style="color:#9a6e3a">|</span> Id <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">User</span> <span style="color:#9a6e3a">|</span> Host      <span style="color:#9a6e3a">|</span> db    <span style="color:#9a6e3a">|</span> Command <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">Time</span> <span style="color:#9a6e3a">|</span> State                           <span style="color:#9a6e3a">|</span> Info                                                        <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">+</span><span style="color:slategray">----+------+-----------+-------+---------+------+---------------------------------+-------------------------------------------------------------+</span>
<span style="color:#9a6e3a">|</span>  <span style="color:#990055">6</span> <span style="color:#9a6e3a">|</span> root <span style="color:#9a6e3a">|</span> localhost <span style="color:#9a6e3a">|</span> helei <span style="color:#9a6e3a">|</span> Query   <span style="color:#9a6e3a">|</span>    <span style="color:#990055">2</span> <span style="color:#9a6e3a">|</span> copy <span style="color:#0077aa">to</span> tmp <span style="color:#0077aa">table</span>               <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">modify</span> c6 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">40</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span> <span style="color:#0077aa">default</span> <span style="color:#669900">''</span> <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span>  <span style="color:#990055">7</span> <span style="color:#9a6e3a">|</span> root <span style="color:#9a6e3a">|</span> localhost <span style="color:#9a6e3a">|</span> helei <span style="color:#9a6e3a">|</span> Query   <span style="color:#9a6e3a">|</span>    <span style="color:#990055">1</span> <span style="color:#9a6e3a">|</span> Waiting <span style="color:#0077aa">for</span> <span style="color:#0077aa">table</span> metadata <span style="color:#0077aa">lock</span> <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">update</span> helei <span style="color:#0077aa">set</span> c1<span style="color:#9a6e3a">=</span><span style="color:#990055">9999</span> <span style="color:#0077aa">where</span> id<span style="color:#9a6e3a">=</span><span style="color:#990055">1</span>                         <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">|</span>  <span style="color:#990055">8</span> <span style="color:#9a6e3a">|</span> root <span style="color:#9a6e3a">|</span> localhost <span style="color:#9a6e3a">|</span> <span style="color:#990055">NULL</span>  <span style="color:#9a6e3a">|</span> Query   <span style="color:#9a6e3a">|</span>    <span style="color:#990055">0</span> <span style="color:#9a6e3a">|</span> init                            <span style="color:#9a6e3a">|</span> <span style="color:#0077aa">show</span> processlist                                            <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">+</span><span style="color:slategray">----+------+-----------+-------+---------+------+---------------------------------+-------------------------------------------------------------+</span>
<span style="color:#990055">3</span> <span style="color:#0077aa">rows</span> <span style="color:#9a6e3a">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span><span style="color:#990055">0.00</span> sec<span style="color:#999999">)</span></code></span></span>

可以看到在对c6字段修改表结构时，对c1字段无法更新，被锁

<span style="color:#333333"><span style="color:black"><code class="language-sql">mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">add</span> c7 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">60</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span> <span style="color:#0077aa">default</span> <span style="color:#669900">''</span><span style="color:#999999">;</span>
Query OK<span style="color:#999999">,</span> <span style="color:#990055">0</span> <span style="color:#0077aa">rows</span> affected <span style="color:#999999">(</span><span style="color:#990055">8.30</span> sec<span style="color:#999999">)</span>
Records: <span style="color:#990055">0</span>  Duplicates: <span style="color:#990055">0</span>  <span style="color:#0077aa">Warnings</span>: <span style="color:#990055">0</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">update</span> helei <span style="color:#0077aa">set</span> c1<span style="color:#9a6e3a">=</span><span style="color:#990055">9999</span> <span style="color:#0077aa">where</span> id<span style="color:#9a6e3a">=</span><span style="color:#990055">1</span><span style="color:#999999">;</span>
Query OK<span style="color:#999999">,</span> <span style="color:#990055">0</span> <span style="color:#0077aa">rows</span> affected <span style="color:#999999">(</span><span style="color:#990055">0.00</span> sec<span style="color:#999999">)</span>
<span style="color:#0077aa">Rows</span> <span style="color:#0077aa">matched</span>: <span style="color:#990055">1</span>  Changed: <span style="color:#990055">0</span>  <span style="color:#0077aa">Warnings</span>: <span style="color:#990055">0</span></code></span></span>

Tips:心得

经测试，增加、删除字段或索引不锁全表，变更字段长度，锁表。

 

 

 

 

 

Part3:5.7.15的表现

<span style="color:#333333"><span style="color:black"><code class="language-sql">mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">select</span> <span style="color:#dd4a68">count</span><span style="color:#999999">(</span><span style="color:#9a6e3a">*</span><span style="color:#999999">)</span> <span style="color:#0077aa">from</span> helei<span style="color:#999999">;</span>
<span style="color:#9a6e3a">+</span><span style="color:slategray">----------+</span>
<span style="color:#9a6e3a">|</span> <span style="color:#dd4a68">count</span><span style="color:#999999">(</span><span style="color:#9a6e3a">*</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">+</span><span style="color:slategray">----------+</span>
<span style="color:#9a6e3a">|</span>  <span style="color:#990055">1000000</span> <span style="color:#9a6e3a">|</span>
<span style="color:#9a6e3a">+</span><span style="color:slategray">----------+</span>
<span style="color:#990055">1</span> <span style="color:#0077aa">row</span> <span style="color:#9a6e3a">in</span> <span style="color:#0077aa">set</span> <span style="color:#999999">(</span><span style="color:#990055">0.11</span> sec<span style="color:#999999">)</span>
mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE<span style="color:#999999">,</span><span style="color:#0077aa">modify</span> c6 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">80</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span> <span style="color:#0077aa">default</span> <span style="color:#669900">''</span><span style="color:#999999">;</span>
Query OK<span style="color:#999999">,</span> <span style="color:#990055">0</span> <span style="color:#0077aa">rows</span> affected <span style="color:#999999">(</span><span style="color:#990055">0.01</span> sec<span style="color:#999999">)</span>
Records: <span style="color:#990055">0</span>  Duplicates: <span style="color:#990055">0</span>  <span style="color:#0077aa">Warnings</span>: <span style="color:#990055">0</span></code></span></span>

Tips:心得

可以看到，这里用时0.01sec，和5.6的9.44sec效率差了相当多吧~

 

Warning:警告1

不过，ALGORITHM用法只对varcahr类型有效哦，比如我们对c1列int型进行变更：

c1原为int(10) 变更为int(11) unsigned则无效

<span style="color:#333333"><span style="color:black"><code class="language-sql">mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE<span style="color:#999999">,</span><span style="color:#0077aa">modify</span> c1 <span style="color:#0077aa">int</span><span style="color:#999999">(</span><span style="color:#990055">11</span><span style="color:#999999">)</span> <span style="color:#0077aa">unsigned</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span><span style="color:#999999">;</span>
ERROR <span style="color:#990055">1846</span> <span style="color:#999999">(</span><span style="color:#990055">0</span>A000<span style="color:#999999">)</span>: <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE <span style="color:#9a6e3a">is</span> <span style="color:#9a6e3a">not</span> supported<span style="color:#999999">.</span> Reason: Cannot change <span style="color:#0077aa">column</span> <span style="color:#0077aa">type</span> INPLACE<span style="color:#999999">.</span> Try <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>COPY<span style="color:#999999">.</span></code></span></span>

 

Tips:心得

只变更int的位数，是可以的，不过这没什么意义，因为无论你int多少，最多都只能存10位，这也就是为什么我们生产库开发规范要定义所有的int都用int(10)。

生产库开发规范详见：

http://suifu.blog.51cto.com/9167728/1792604

 

Warning:警告2

如果字段属性大于并等于varchar(256)（这里的256是指字节（UTF8占用3字节）或者把varchar(80)减少到varchar(70)或者更少)，则仍需要拷贝数据且锁全表。

<span style="color:#333333"><span style="color:black"><code class="language-sql">mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE<span style="color:#999999">,</span><span style="color:#0077aa">modify</span> c6 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">84</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span> <span style="color:#0077aa">default</span> <span style="color:#669900">''</span><span style="color:#999999">;</span>
Query OK<span style="color:#999999">,</span> <span style="color:#990055">0</span> <span style="color:#0077aa">rows</span> affected <span style="color:#999999">(</span><span style="color:#990055">0.01</span> sec<span style="color:#999999">)</span>
Records: <span style="color:#990055">0</span>  Duplicates: <span style="color:#990055">0</span>  <span style="color:#0077aa">Warnings</span>: <span style="color:#990055">0</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE<span style="color:#999999">,</span><span style="color:#0077aa">modify</span> c6 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">85</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span> <span style="color:#0077aa">default</span> <span style="color:#669900">''</span><span style="color:#999999">;</span>
Query OK<span style="color:#999999">,</span> <span style="color:#990055">0</span> <span style="color:#0077aa">rows</span> affected <span style="color:#999999">(</span><span style="color:#990055">0.01</span> sec<span style="color:#999999">)</span>
Records: <span style="color:#990055">0</span>  Duplicates: <span style="color:#990055">0</span>  <span style="color:#0077aa">Warnings</span>: <span style="color:#990055">0</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE<span style="color:#999999">,</span><span style="color:#0077aa">modify</span> c6 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">86</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span> <span style="color:#0077aa">default</span> <span style="color:#669900">''</span><span style="color:#999999">;</span>
ERROR <span style="color:#990055">1846</span> <span style="color:#999999">(</span><span style="color:#990055">0</span>A000<span style="color:#999999">)</span>: <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE <span style="color:#9a6e3a">is</span> <span style="color:#9a6e3a">not</span> supported<span style="color:#999999">.</span> Reason: Cannot change <span style="color:#0077aa">column</span> <span style="color:#0077aa">type</span> INPLACE<span style="color:#999999">.</span> Try <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>COPY<span style="color:#999999">.</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE<span style="color:#999999">,</span><span style="color:#0077aa">modify</span> c6 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">40</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span> <span style="color:#0077aa">default</span> <span style="color:#669900">''</span><span style="color:#999999">;</span>
ERROR <span style="color:#990055">1846</span> <span style="color:#999999">(</span><span style="color:#990055">0</span>A000<span style="color:#999999">)</span>: <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE <span style="color:#9a6e3a">is</span> <span style="color:#9a6e3a">not</span> supported<span style="color:#999999">.</span> Reason: Cannot change <span style="color:#0077aa">column</span> <span style="color:#0077aa">type</span> INPLACE<span style="color:#999999">.</span> Try <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>COPY<span style="color:#999999">.</span>

mysql<span style="color:#9a6e3a">></span> <span style="color:#0077aa">alter</span> <span style="color:#0077aa">table</span> helei <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE<span style="color:#999999">,</span><span style="color:#0077aa">modify</span> c6 <span style="color:#0077aa">varchar</span><span style="color:#999999">(</span><span style="color:#990055">70</span><span style="color:#999999">)</span> <span style="color:#9a6e3a">not</span> <span style="color:#990055">null</span> <span style="color:#0077aa">default</span> <span style="color:#669900">''</span><span style="color:#999999">;</span>
ERROR <span style="color:#990055">1846</span> <span style="color:#999999">(</span><span style="color:#990055">0</span>A000<span style="color:#999999">)</span>: <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>INPLACE <span style="color:#9a6e3a">is</span> <span style="color:#9a6e3a">not</span> supported<span style="color:#999999">.</span> Reason: Cannot change <span style="color:#0077aa">column</span> <span style="color:#0077aa">type</span> INPLACE<span style="color:#999999">.</span> Try <span style="color:#0077aa">ALGORITHM</span><span style="color:#9a6e3a">=</span>COPY<span style="color:#999999">.</span></code></span></span>

Warning:警告3

添加字段alter table时，对该表的增删改查均不会锁表。而在这之前，当该表被访问时，需要等其执行完毕后才可以执行alter table。

 

 

 

 

——总结——

在varchar变更字段长度方面，5.7的新特性ALGORITHM参数可以快速调整varchar类型的字段长度。5.7同5.6一样，增加，删除字段或索引不锁全表，删除主键锁全表。因此，在上线时，一定要执行show processlist命令并观察，此刻是否有某个慢SQL对该表进行操作，以免alter table表时出现锁表现象。由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。